-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathproduct function.sql
141 lines (98 loc) · 3.72 KB
/
product function.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
create or replace package product_package as
function insert_product(nam product.name%TYPE,bran product.brand%TYPE,cat product.catagory%TYPE)
return product.pid%TYPE;
function insert_product_new(nam product.name%TYPE,bran product.brand%TYPE,cat product.catagory%TYPE,
pro_code product_info.product_code%TYPE,sup_id product_info.sid%TYPE,
buy_price product_info.buyprice%TYPE,sell_price product_info.sellprice%TYPE,
branc product_info.branch%TYPE)
return number;
function insert_product_old(nam product.name%TYPE,bran product.brand%TYPE,cat product.catagory%TYPE,
pro_code product_info.product_code%TYPE,cus_id product_info.cid%TYPE,
buy_price product_info.buyprice%TYPE,sell_price product_info.sellprice%TYPE,
branc product_info.branch%TYPE)
return number;
end product_package;
/
create or replace package body product_package as
-- returns the pid of the new added product
function insert_product(nam product.name%TYPE,bran product.brand%TYPE,cat product.catagory%TYPE)
return product.pid%TYPE
is
P_id product.pid%TYPE;
BEGIN
insert into product(name,catagory,brand) values(nam,cat,bran);
select pid into P_id from product where name = nam and catagory = cat and brand = bran;
return P_id;
exception
when no_data_found then
P_id := 0;
return P_id;
END insert_product;
--returns 1 when successful
--returns 0 when error
--returns 2 when already exist
function insert_product_new(nam product.name%TYPE,bran product.brand%TYPE,cat product.catagory%TYPE,
pro_code product_info.product_code%TYPE,sup_id product_info.sid%TYPE,
buy_price product_info.buyprice%TYPE,sell_price product_info.sellprice%TYPE,
branc product_info.branch%TYPE)
return number
is
tot number;
P_id product.pid%TYPE;
ret number;
begin
ret := 0;
select COUNT(*) into tot from product where name = nam and catagory = cat and brand = bran;
if tot = 0 then
P_id := insert_product(nam,cat,bran);
ELSE
select pid into P_id from product where name = nam and catagory = cat and brand = bran;
end if;
select COUNT(*) into tot from product_info where product_code = pro_code;
if tot = 0 then
insert into product_info values(pro_code,P_id,sup_id,null,buy_price,sell_price,branc,'new',0);
ret := 1;
return ret;
ELSE
ret := 2;
return ret;
end if;
exception
when no_data_found then
return ret;
end insert_product_new;
--returns 1 when successful
--returns 0 when error
--returns 2 when already exist
function insert_product_old(nam product.name%TYPE,bran product.brand%TYPE,cat product.catagory%TYPE,
pro_code product_info.product_code%TYPE,cus_id product_info.cid%TYPE,
buy_price product_info.buyprice%TYPE,sell_price product_info.sellprice%TYPE,
branc product_info.branch%TYPE)
return number
is
tot number;
P_id product.pid%TYPE;
ret number;
begin
ret := 0;
select COUNT(*) into tot from product where name = nam and catagory = cat and brand = bran;
if tot = 0 then
P_id := insert_product(nam,cat,bran);
ELSE
select pid into P_id from product where name = nam and catagory = cat and brand = bran;
end if;
select COUNT(*) into tot from product_info where product_code = pro_code;
if tot = 0 then
insert into product_info values(pro_code,P_id,null,cus_id,buy_price,sell_price,branc,'old',0);
ret := 1;
return ret;
ELSE
ret := 2;
return ret;
end if;
exception
when no_data_found then
return ret;
end insert_product_old;
end product_package;
/