Use native dynamic SQL to process queries. : ref cursor « Cursor « Oracle PL / SQL






Use native dynamic SQL to process queries.

 
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE NativeDynamic AS
  2    TYPE t_RefCur IS REF CURSOR;
  3
  4    FUNCTION lecturerQuery(p_WhereClause IN VARCHAR2)
  5      RETURN t_RefCur;
  6
  7    FUNCTION lecturerQuery2(p_Major IN VARCHAR2)
  8      RETURN t_RefCur;
  9  END NativeDynamic;
 10  /

Package created.

SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY NativeDynamic AS
  2    FUNCTION lecturerQuery(p_WhereClause IN VARCHAR2)
  3      RETURN t_RefCur IS
  4      v_ReturnCursor t_RefCur;
  5      v_SQLStatement VARCHAR2(500);
  6    BEGIN
  7      v_SQLStatement := 'SELECT * FROM lecturer ' || p_WhereClause;
  8
  9      OPEN v_ReturnCursor FOR v_SQLStatement;
 10      RETURN v_ReturnCursor;
 11    END lecturerQuery;
 12
 13    FUNCTION lecturerQuery2(p_Major IN VARCHAR2)
 14      RETURN t_RefCur IS
 15      v_ReturnCursor t_RefCur;
 16      v_SQLStatement VARCHAR2(500);
 17    BEGIN
 18      v_SQLStatement := 'SELECT * FROM lecturer WHERE major = :m';
 19
 20      OPEN v_ReturnCursor FOR v_SQLStatement USING p_Major;
 21      RETURN v_ReturnCursor;
 22    END lecturerQuery2;
 23  END NativeDynamic;
 24  /

Package body created.

SQL> show errors
No errors.
SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> DECLARE
  2    myLecturer lecturer%ROWTYPE;
  3    myLecturerCur NativeDynamic.t_RefCur;
  4  BEGIN
  5    myLecturerCur :=
  6      NativeDynamic.lecturerQuery('WHERE MOD(id, 2) = 0');
  7
  8    DBMS_OUTPUT.PUT_LINE('The following lecturer have even IDs:');
  9    LOOP
 10      FETCH myLecturerCur INTO myLecturer;
 11      EXIT WHEN myLecturerCur%NOTFOUND;
 12      DBMS_OUTPUT.PUT_LINE('  ' || myLecturer.id || ': ' ||
 13                           myLecturer.first_name || ' ' ||
 14                           myLecturer.last_name);
 15    END LOOP;
 16    CLOSE myLecturerCur;
 17
 18    myLecturerCur :=
 19      NativeDynamic.lecturerQuery2('Music');
 20
 21    DBMS_OUTPUT.PUT_LINE(
 22      'The following lecturer are music majors:');
 23    LOOP
 24      FETCH myLecturerCur INTO myLecturer;
 25      EXIT WHEN myLecturerCur%NOTFOUND;
 26      DBMS_OUTPUT.PUT_LINE('  ' || myLecturer.id || ': ' ||
 27                           myLecturer.first_name || ' ' ||
 28                           myLecturer.last_name);
 29    END LOOP;
 30    CLOSE myLecturerCur;
 31  END;
 32  /
The following lecturer have even IDs:
  10002: Mar Wells
  10004: Man Kyte
  10006: Tim Viper
  10008: David Large
  10010: Rose Bond
  10012: Sharon Clear
The following lecturer are music majors:
  10008: David Large
  10010: Rose Bond

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table lecturer;

Table dropped.

SQL>

 








Related examples in the same category

1.declare ref cursors
2.strong ref cursor
3.Cursor Variables Assignment
4.REF CURSOR variable