Repository navigation
Expand file tree
/
Copy path11g_frc_views.sql
More file actions
90 lines (73 loc) · 1.89 KB
/
Copy path11g_frc_views.sql
File metadata and controls
90 lines (73 loc) · 1.89 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
CREATE OR REPLACE VIEW hr_just_names
AS
SELECT d.department_name, e.last_name, e.first_name
FROM employees e, departments d
WHERE d.department_id = e.department_id
/
/* Invalidate the cache. */
BEGIN
dbms_result_cache.invalidate (USER, 'HR_JUST_NAMES');
END;
/
CREATE OR REPLACE PACKAGE emplu11g
IS
FUNCTION last_name (employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
RESULT_CACHE
;
END;
/
CREATE OR REPLACE PACKAGE BODY emplu11g
IS
/* Relies on references a view */
FUNCTION last_name (employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
RESULT_CACHE RELIES_ON ( hr_just_names )
IS
onerow_rec employees%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line (
'Looking up last name for employee ID ' || employee_id_in
);
SELECT *
INTO onerow_rec
FROM employees
WHERE employee_id = employee_id_in;
RETURN onerow_rec.last_name;
END;
END;
/
BEGIN
DBMS_OUTPUT.put_line ('Cache some data....');
FOR indx IN 1 .. 2
LOOP
DBMS_OUTPUT.put_line (emplu11g.last_name (138));
DBMS_OUTPUT.put_line (emplu11g.last_name (140));
END LOOP;
END;
/
/* Change only the department table */
BEGIN
DBMS_OUTPUT.put_line ('Change department names only....');
UPDATE departments
SET department_name = department_name;
COMMIT;
END;
/
/*
Am I still caching?
*/
BEGIN
FOR indx IN 1 .. 3
LOOP
DBMS_OUTPUT.put_line (emplu11g.last_name (138));
DBMS_OUTPUT.put_line (emplu11g.last_name (140));
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/
*/