-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathProgram10 - PACKAGES.sql
More file actions
125 lines (105 loc) · 2.85 KB
/
Copy pathProgram10 - PACKAGES.sql
File metadata and controls
125 lines (105 loc) · 2.85 KB
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
-- Developer: <Aaron>
--
-- Program #: <Programming Assignment Number 10>
--
-- File Name: <Program10-Packages.sql>
--
-- Description:
-- <Creating a package containing two overloades procedures>
CREATE OR REPLACE PACKAGE Program10_PKG
IS
PROCEDURE search_id_pp
(p_id IN NUMBER,
p_Name OUT VARCHAR2,
p_City OUT VARCHAR2,
p_State OUT VARCHAR2,
p_Phone OUT VARCHAR2,
p_Email OUT VARCHAR2);
PROCEDURE search_id_pp
(p_lastn IN VARCHAR2,
p_Name OUT VARCHAR2,
p_City OUT VARCHAR2,
p_State OUT VARCHAR2,
p_Phone OUT VARCHAR2,
p_Email OUT VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY Program10_PKG
IS
PROCEDURE search_id_pp
(p_id IN NUMBER,
p_Name OUT VARCHAR2,
p_City OUT VARCHAR2,
p_State OUT VARCHAR2,
p_Phone OUT VARCHAR2,
p_Email OUT VARCHAR2)
IS
BEGIN
SELECT firstname||' '|| lastname, city, state, phone, email
INTO p_Name, p_City, p_State, p_Phone, p_Email
FROM bb_shopper
WHERE idshopper = p_id;
END;
PROCEDURE search_id_pp
(p_lastn IN VARCHAR2,
p_Name OUT VARCHAR2,
p_City OUT VARCHAR2,
p_State OUT VARCHAR2,
p_Phone OUT VARCHAR2,
p_Email OUT VARCHAR2)
IS
BEGIN
SELECT firstname||' '|| lastname, city, state, phone, email
INTO p_Name, p_City, p_State, p_Phone, p_Email
FROM bb_shopper
WHERE lastname = p_lastn;
END;
END;
/
-- Ran these one at a time to get all the outcomes with 23, 30 and Ratman.
DECLARE
lv_nm VARCHAR2 (25);
lv_cty VARCHAR2 (15);
lv_st VARCHAR2 (15);
lv_ph VARCHAR2 (11);
lv_eml VARCHAR2 (18);
BEGIN
/* Program10_PKG.search_id_pp(23, lv_nm, lv_cty, lv_st, lv_ph, lv_eml);
DBMS_OUTPUT.PUT_LINE(lv_nm);
DBMS_OUTPUT.PUT_LINE(lv_cty);
DBMS_OUTPUT.PUT_LINE(lv_st);
DBMS_OUTPUT.PUT_LINE(lv_ph);
DBMS_OUTPUT.PUT_LINE(lv_eml); */
/* Program10_PKG.search_id_pp(30, lv_nm, lv_cty, lv_st, lv_ph, lv_eml);
DBMS_OUTPUT.PUT_LINE(lv_nm);
DBMS_OUTPUT.PUT_LINE(lv_cty);
DBMS_OUTPUT.PUT_LINE(lv_st);
DBMS_OUTPUT.PUT_LINE(lv_ph);
DBMS_OUTPUT.PUT_LINE(lv_eml); */
Program10_PKG.search_id_pp('Ratman', lv_nm, lv_cty, lv_st, lv_ph, lv_eml);
DBMS_OUTPUT.PUT_LINE(lv_nm);
DBMS_OUTPUT.PUT_LINE(lv_cty);
DBMS_OUTPUT.PUT_LINE(lv_st);
DBMS_OUTPUT.PUT_LINE(lv_ph);
DBMS_OUTPUT.PUT_LINE(lv_eml);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record was found.');
END;
-- Output
Package PROGRAM10_PKG compiled
Package Body PROGRAM10_PKG compiled
Kenny Ratman
South Park
NC
9015680902
ratboy@msn.net
PL/SQL procedure successfully completed.
No record was found.
PL/SQL procedure successfully completed.
Kenny Ratman
South Park
NC
9015680902
ratboy@msn.net
PL/SQL procedure successfully completed.