|
SQL>
SQL> CREATE OR REPLACE TYPE BookList AS VARRAY(10) OF NUMBER(4);
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE class_material (
2 department CHAR(3),
3 course NUMBER(3),
4 required_reading BookList
5 );
Table created.
SQL>
SQL> CREATE TABLE books (
2 catalog_number NUMBER(4) PRIMARY KEY,
3 title VARCHAR2(40),
4 author1 VARCHAR2(40),
5 author2 VARCHAR2(40),
6 author3 VARCHAR2(40),
7 author4 VARCHAR2(40)
8 );
Table created.
SQL>
SQL> INSERT INTO books (catalog_number, title, author1)
2 VALUES (1000, 'Oracle8i Advanced PL/SQL Programming', 'Urman, Scott');
1 row created.
SQL>
SQL> INSERT INTO books (catalog_number, title, author1, author2, author3)
2 VALUES (1001, 'Oracle8i: A Beginner''s Guide', 'Abbey, Michael', 'Corey, Michael J.', 'Abramson, Ian');
1 row created.
SQL>
SQL> INSERT INTO books (catalog_number, title, author1, author2, author3, author4)
2 VALUES (1002, 'Oracle8 Tuning', 'Corey, Michael J.', 'Abbey, Michael', 'Dechichio, Daniel J.', 'Abramson, Ian');
1 row created.
SQL>
SQL> INSERT INTO books (catalog_number, title, author1, author2)
2 VALUES (2001, 'A History of the World', 'Arlington, Arlene', 'Verity, Victor');
1 row created.
SQL>
SQL> INSERT INTO books (catalog_number, title, author1)
2 VALUES (3001, 'Bach and the Modern World', 'Foo, Fred');
1 row created.
SQL>
SQL> INSERT INTO books (catalog_number, title, author1)
2 VALUES (3002, 'Introduction to the Piano', 'Morenson, Mary');
1 row created.
SQL> CREATE OR REPLACE PROCEDURE PrintRequired(
2 p_Department IN class_material.department%TYPE,
3 p_Course IN class_material.course%TYPE) IS
4
5 v_Books class_material.required_reading%TYPE;
6 v_Title books.title%TYPE;
7 BEGIN
8 SELECT required_reading
9 INTO v_Books
10 FROM class_material
11 WHERE department = p_Department
12 AND course = p_Course;
13
14 DBMS_OUTPUT.PUT('Required reading for ' || RTRIM(p_Department));
15 DBMS_OUTPUT.PUT_LINE(' ' || p_Course || ':');
16
17 FOR v_Index IN 1..v_Books.COUNT LOOP
18 SELECT title
19 INTO v_Title
20 FROM books
21 WHERE catalog_number = v_Books(v_Index);
22 DBMS_OUTPUT.PUT_LINE(
23 ' ' || v_Books(v_Index) || ': ' || v_Title);
24 END LOOP;
25 END PrintRequired;
26 /
Procedure created.
SQL>
SQL> DECLARE
2 CURSOR c_Courses IS
3 SELECT department, course
4 FROM class_material
5 ORDER BY department;
6 BEGIN
7 FOR v_Rec IN c_Courses LOOP
8 PrintRequired(v_Rec.department, v_Rec.course);
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table class_material;
Table dropped.
SQL>
SQL> drop table books;
Table dropped.
SQL>
SQL>
--Overloading packaged subprograms based on object types.
CREATE OR REPLACE TYPE t11 AS OBJECT (
f NUMBER
)
/
CREATE OR REPLACE TYPE t21 AS OBJECT (
f NUMBER
)
/
CREATE OR REPLACE PACKAGE Overload AS
PROCEDURE Proc(p_Parameter1 IN t11);
PROCEDURE Proc(p_Parameter1 IN t21);
END Overload;
/
CREATE OR REPLACE PACKAGE BODY Overload AS
PROCEDURE Proc(p_Parameter1 IN t11) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Proc(t11): ' || p_Parameter1.f);
END Proc;
PROCEDURE Proc(p_Parameter1 IN t21) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Proc(t21): ' || p_Parameter1.f);
END Proc;
END Overload;
/
set serveroutput on
DECLARE
v_Obj1 t11 := t11(1);
v_Obj2 t21 := t21(2);
BEGIN
Overload.Proc(v_Obj1);
Overload.Proc(v_Obj2);
END;
/
drop type t11;
drop type t21;
--
|