Distributed-ChainShop-Management/globalPackageBody.sql at master · amirraj/Distributed-ChainShop-Management · GitHub

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
create or replace package body globalPackage as

	procedure transfer_employee
	(site_no in number, v_eid in number, v_ename in varchar2)
	IS


	begin
		if site_no = 1 then
			delete employee@site3 where eid = v_eid ;
			delete employee where eid = v_eid ;
			insert into employee@site1 values(v_eid,v_ename,1);
			commit ;

		elsif site_no = 2 then
			delete employee@site3 where eid = v_eid ;
			delete employee@site1 where eid = v_eid ;
			insert into employee values(v_eid,v_ename,2);
			commit ;

		elsif site_no = 3 then
			delete employee@site1 where eid = v_eid ;
			delete employee where eid = v_eid ;
			insert into employee@site3 values(v_eid,v_ename,3);
			commit ;

		end if;
	end transfer_employee;


	function get_total_sell_of_a_date(input_date in date)
	return number
	IS

	total number := 0;
	total_sell number;

	cursor sell_cur1 is
	select sum(price) from records@site1 where purchase_date = input_date;

	cursor sell_cur2 is
	select sum(price) from records where purchase_date = input_date;

	cursor sell_cur3 is
	select sum(price) from records@site3 where purchase_date = input_date;

	begin

	open sell_cur1;
	     fetch sell_cur1 into total_sell ;
	close sell_cur1;
	total := total + total_sell ;

	open sell_cur2;
	     fetch sell_cur2 into total_sell ;
	close sell_cur2;
	total := total + total_sell ;

	open sell_cur3;
	     fetch sell_cur3 into total_sell ;
	close sell_cur3;

	total := total + total_sell ;
    return total ;
	end get_total_sell_of_a_date;


	procedure give_discount
	(site_no in number, day_no in number, parcentage in number)
	IS

	todays_date date := sysdate;
	begin
		if site_no = 1 then
			delete discount@site1 ;
			for i in 1 .. day_no
			loop
				insert into discount@site1 values(todays_date, parcentage);
				todays_date := to_date(todays_date) + 1;
				commit;
			end loop;
		elsif site_no = 2 then

				delete discount ;
				for i in 1 .. day_no
			loop
				insert into discount values(todays_date, parcentage);
				todays_date := to_date(todays_date) + 1;
				commit;
			end loop;

		elsif site_no = 3 then
				delete discount@site3 ;
				for i in 1 .. day_no
			loop
				insert into discount values(todays_date, parcentage);
				todays_date := to_date(todays_date) + 1;
				commit;
			end loop;
		end if;
		commit ;
	end give_discount;

	procedure add_product
	( id in product.pid%TYPE,
	  name in product.pname%TYPE,
	  avail in product.unit_available%TYPE,
	  price in product.unit_price%TYPE,
	  siteno in number)
	IS


begin
	 if siteno = 1 then
	insert into product@site1 (pid, pname, unit_available, unit_price) values (id, name, avail, price);
	commit;
	elsif siteno = 2 then
	insert into product (pid, pname, unit_available, unit_price) values (id, name, avail, price);
	commit;
	elsif siteno = 3 then
	insert into product@site3 (pid, pname, unit_available, unit_price) values (id, name, avail, price);
	commit;

	end if;

end add_product;

end globalPackage;
/