Mega Code Archive

 

Categories / Oracle PLSQL / Cursor

 

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>