Query a stored varray. : varray « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. varray

Query a stored varray.

    
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;

--

   
    
    
  

Related examples in the same category