-
Notifications
You must be signed in to change notification settings - Fork 2
/
PL_SQL_functions.txt
169 lines (138 loc) · 4.64 KB
/
PL_SQL_functions.txt
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
--------------------------------------------------------------------------------------------------------------------
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE', 'TRIGGER') AND OWNER = 'BIKROY'
--------------------------------------------------------------------------------------------------------------------
--------- USERNAME FROM AD ID ----------------------------------------------------------kawshik
CREATE OR REPLACE FUNCTION USERNAME_FROM_AD_ID(AD_ID IN VARCHAR2)
RETURN VARCHAR2 IS
U_NAME VARCHAR2(20);
BEGIN
SELECT USERNAME INTO U_NAME
FROM ADVERTISEMENT
WHERE ADVERTISEMENT_ID = AD_ID;
RETURN U_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
----------------------------------------------------------------------------------------
-------get product id from ad id--------------------------------------------------------kawshik
CREATE OR REPLACE FUNCTION PROD_ID_FROM_AD_ID(AD_ID IN VARCHAR2)
RETURN VARCHAR2 IS
PROD_ID VARCHAR2(20);
BEGIN
SELECT PRODUCT_ID INTO PROD_ID
FROM PRODUCT
WHERE ADVERTISEMENT_ID = AD_ID;
RETURN PROD_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
----------------------------------------------------------------------------------------
-------get JOB id from ad id-----------------------------------------------------------kawshik
CREATE OR REPLACE FUNCTION JOB_ID_FROM_AD_ID(AD_ID IN VARCHAR2)
RETURN VARCHAR2 IS
J_ID VARCHAR2(20);
BEGIN
SELECT JOB_ID INTO J_ID
FROM JOB
WHERE ADVERTISEMENT_ID = AD_ID;
RETURN J_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
-----------------------------------------------------------------------------------------
-------get product NAME from PROD id-----------------------------------------------------kawshik
CREATE OR REPLACE FUNCTION PROD_NAME_FROM_PROD_ID(PROD_ID IN VARCHAR2)
RETURN VARCHAR2 IS
PROD_NAME VARCHAR2(200);
BEGIN
SELECT PRODUCT_NAME INTO PROD_NAME
FROM PRODUCT
WHERE PRODUCT_ID = PROD_ID;
RETURN PROD_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
-----------------------------------------------------------------------------------------
-------get JOB NAME from JOB id----------------------------------------------------------kawshik
CREATE OR REPLACE FUNCTION JOB_NAME_FROM_JOB_ID(J_ID IN VARCHAR2)
RETURN VARCHAR2 IS
JOB_NAME VARCHAR2(500);
BEGIN
SELECT JOB_TYPE ||' - '||DESIGNATION||' of APPROX SALARY - '||SALARY INTO JOB_NAME
FROM JOB
WHERE JOB_ID = J_ID;
RETURN JOB_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
------------------------------------------------------------------------------------------
------getLocation_from_username------
CREATE OR REPLACE FUNCTION getloc(userN IN Varchar2) return varchar2 is
l varchar2(20);
begin
select LOCATION_ID into l from account ac,profile p where username=userN and ac.profile_no=p.PROFILE_no;
return l;
exception
when no_data_found then
return 'no data found';
when others then
return 'unkonwn error';
end;
---------------------------------------------------------
------getProfileNo_from_username------
CREATE OR REPLACE FUNCTION getProfile(userN IN Varchar2) return varchar2 is
p varchar2(20);
begin
select profile_NO into p from account where username=userN;
return p;
exception
when no_data_found then
return 'no data found';
when others then
return 'unkonwn error';
end;
--------------------------------------------
------get advertisement id from product id------
CREATE OR REPLACE FUNCTION getAdv(prod_id IN Varchar2) return varchar2 is
adv varchar2(20);
begin
select ad.ADVERTISEMENT_ID into adv from product p,ADVERTISEMENT ad where p.ADVERTISEMENT_ID=ad.ADVERTISEMENT_ID and p.PRODUCT_ID=prod_id;
return adv;
exception
when no_data_found then
return 'no data found';
when others then
return 'unkonwn error';
end;
/
-------------------------------------