-
Notifications
You must be signed in to change notification settings - Fork 14
Expand file tree
/
Copy pathbidir.pkg
More file actions
140 lines (112 loc) · 2.59 KB
/
Copy pathbidir.pkg
File metadata and controls
140 lines (112 loc) · 2.59 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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
CREATE OR REPLACE PACKAGE bidir
IS
/* Returns data by primary key */
FUNCTION rowforid (id_in IN employees.employee_id%TYPE)
RETURN employee%ROWTYPE;
FUNCTION firstrow
RETURN PLS_INTEGER;
FUNCTION lastrow
RETURN PLS_INTEGER;
FUNCTION ROWCOUNT
RETURN PLS_INTEGER;
FUNCTION end_of_data
RETURN BOOLEAN;
/* Iterate through rows in the result set */
PROCEDURE setrow (nth IN PLS_INTEGER);
FUNCTION currrow
RETURN employee%ROWTYPE;
PROCEDURE nextrow;
PROCEDURE prevrow;
END;
/
CREATE OR REPLACE PACKAGE BODY bidir
IS
g_currrow PLS_INTEGER := NULL;
TYPE employee_tt IS TABLE OF employee%ROWTYPE
INDEX BY BINARY_INTEGER;
g_employees employee_tt;
FUNCTION rowforid (id_in IN employees.employee_id%TYPE)
RETURN employee%ROWTYPE
IS
BEGIN
RETURN g_employees (id_in);
END;
FUNCTION ROWCOUNT
RETURN PLS_INTEGER
IS
BEGIN
RETURN g_employees.COUNT;
END;
FUNCTION firstrow
RETURN PLS_INTEGER
IS
BEGIN
RETURN g_employees.FIRST;
END;
FUNCTION lastrow
RETURN PLS_INTEGER
IS
BEGIN
RETURN g_employees.LAST;
END;
FUNCTION end_of_data
RETURN BOOLEAN
IS
BEGIN
RETURN g_currrow IS NULL;
END;
PROCEDURE setrow (nth IN PLS_INTEGER)
IS
BEGIN
g_currrow := GREATEST (LEAST (nth, lastrow), firstrow);
END;
FUNCTION currrow
RETURN employee%ROWTYPE
IS
BEGIN
RETURN g_employees (g_currrow);
END;
PROCEDURE nextrow
IS
BEGIN
g_currrow := g_employees.NEXT (g_currrow);
END;
PROCEDURE prevrow
IS
BEGIN
g_currrow := g_employees.PRIOR (g_currrow);
END;
BEGIN
FOR rec IN (SELECT *
FROM employees)
LOOP
g_employees (rec.employee_id) := rec;
END LOOP;
g_currrow := firstrow;
END;
/
/* Example of usage */
DECLARE
l_employee employee%ROWTYPE;
BEGIN
LOOP
EXIT WHEN bidir.end_of_data;
l_employee := bidir.currrow;
DBMS_OUTPUT.put_line (l_employee.last_name);
bidir.nextrow;
END LOOP;
bidir.setrow (bidir.lastrow);
LOOP
EXIT WHEN bidir.end_of_data;
l_employee := bidir.currrow;
DBMS_OUTPUT.put_line (l_employee.last_name);
bidir.prevrow;
END LOOP;
END;
/
/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc.
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/