CREATE OR REPLACE PACKAGE NativeDynamic AS TYPE t_RefCur IS REF CURSOR; -- Selects from students using the supplied WHERE clause, -- and returns the opened cursor variable. FUNCTION StudentsQuery(p_WhereClause IN VARCHAR2) RETURN t_RefCur; -- Selects from students based on the supplied major, major, -- and returns the opened cursor variable. FUNCTION StudentsQuery2(p_Major IN VARCHAR2) RETURN t_RefCur; END NativeDynamic; / show errors -----------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PACKAGE BODY NativeDynamic AS -- Selects from students using the supplied WHERE clause, -- and returns the opened cursor variable. FUNCTION StudentsQuery(p_WhereClause IN VARCHAR2) RETURN t_RefCur IS v_ReturnCursor t_RefCur; v_SQLStatement VARCHAR2(500); BEGIN -- Build the query using the supplied WHERE clause v_SQLStatement := 'SELECT * FROM students ' || p_WhereClause; -- Open the cursor variable, and return it. OPEN v_ReturnCursor FOR v_SQLStatement; RETURN v_ReturnCursor; END StudentsQuery; -- Selects from students based on the supplied major, major, -- and returns the opened cursor variable. FUNCTION StudentsQuery2(p_Major IN VARCHAR2) RETURN t_RefCur IS v_ReturnCursor t_RefCur; v_SQLStatement VARCHAR2(500); BEGIN v_SQLStatement := 'SELECT * FROM students WHERE major = :m'; -- Open the cursor variable, and return it. OPEN v_ReturnCursor FOR v_SQLStatement USING p_Major; RETURN v_ReturnCursor; END StudentsQuery2; END NativeDynamic; / show errors -----------------------------------------------------------------------------------------------------------------------set serveroutput on format wrapped DECLARE v_Student students%ROWTYPE; v_StudentCur NativeDynamic.t_RefCur; NativeDynamic.t_RefCur; BEGIN -- Call StudentsQuery to open the cursor for students with -- even IDs. v_StudentCur := NativeDynamic.StudentsQuery('WHERE NativeDynamic.Students Query('WHERE MOD(id, 2) = 0'); -- Loop through the opened cursor, cursor, and print out the results. DBMS_OUTPUT.PUT_LINE('The DBMS_OUTPUT.PUT_LINE('The following students have even IDs:'); Dynamic SQL
1
LOOP FETCH v_StudentCur INTO v_Student; EXIT WHEN v_StudentCur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(' DBMS_OUTPUT.PUT_LINE(' ' || v_Student.id || ': ' || v_Student.first_name || ' ' || v_Student.last_name); END LOOP; CLOSE v_StudentCur; -- Call StudentsQuery2 to open the cursor for music majors. v_StudentCur := NativeDynamic.StudentsQuery2('Music'); -- Loop through the opened cursor, cursor, and print out the results. DBMS_OUTPUT.PUT_LINE('The DBMS_OUTPUT.PUT_LINE('The following students are music majors:'); LOOP FETCH v_StudentCur INTO v_Student; EXIT WHEN v_StudentCur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(' DBMS_OUTPUT.PUT_LINE(' ' || v_Student.id || ': ' || v_Student.first_name || ' ' ||v_Student.last_name); END LOOP; CLOSE v_StudentCur; END; / ------------------------------------------------------------------------------------------------------------------------- This block illustrates the use of EXECUTE IMMEDIATE IMMEDIATE for -- single-row queries. DECLARE v_SQLQuery VARCHAR2(200); v_Class classes%ROWTYPE; v_Description classes.description%TYPE; BEGIN -- First select into a single variable. v_SQLQuery := 'SELECT description ' || ' FROM classes ' || ' WHERE department = ''ECN''' || ' AND course = 203'; EXECUTE IMMEDIATE v_SQLQuery INTO v_Description; DBMS_OUTPUT.PUT_LINE('Fetched ' || v_Description); -- Now select into a record, using a bind variable. v_SQLQuery := 'SELECT * ' || ' FROM classes ' || ' WHERE description = :description'; EXECUTE IMMEDIATE v_SQLQuery INTO v_Class USING v_Description; DBMS_OUTPUT.PUT_LINE('Fetched DBMS_OUTPUT.PUT_LINE('Fetched ' || v_Class.department || ' ' || v_Class.course); -- Fetch more than one row, which will raise ORA-1422. v_SQLQuery := 'SELECT * FROM classes'; EXECUTE IMMEDIATE v_SQLQuery INTO v_Class; END; ------------------------------------------------------------------------------------------------------------------------
Dynamic SQL
2
DECLARE v_SQLString VARCHAR2(200); v_PLSQLBlock VARCHAR2(200); BEGIN -- First create create a temporary table, using a literal. literal. Note that -- there is no trailing semicolon in the string. EXECUTE IMMEDIATE 'CREATE 'CREATE TABLE TABLE execute_table (col1 VARCHAR(10))'; -- Insert some rows using a string. Again, there is no -- trailing semicolon inside the string. FOR v_Counter IN 1..10 LOOP v_SQLString := 'INSERT INTO execute_table VALUES (''Row ' || v_Counter || ''')'; EXECUTE IMMEDIATE v_SQLString; END LOOP; -- Print out the contents of the table using an anonymous -- PL/SQL block. block. Here we put the entire block into a single -- string (including the semicolon). v_PLSQLBLock := 'BEGIN FOR v_Rec IN (SELECT * FROM execute_table) LOOP DBMS_OUTPUT.PUT_LINE(v_Rec.col1); END LOOP; END;'; -- And now we execute the anonymous block. EXECUTE IMMEDIATE v_PLSQLBlock; -- Finally, Finally, drop the table. -- EXECUTE IMMEDIATE IMMEDIATE 'DROP TABLE TABLE execute_table'; END; -----------------------------------------------------------------------------------------------------------------------DECLARE v_SQLString VARCHAR2(1000); v_PLSQLBlock VARCHAR2(1000); CURSOR c_EconMajor IS SELECT * FROM students WHERE major = 'Economics'; BEGIN -- Insert ECN 103 into classes, using a string for the SQL statement. v_SQLString :='INSERT INTO CLASSES (department, course, description,max_students, current_students, num_credits)VALUES num_credits)VALUES (:dep, :course, :descr, :descr, :max_s, :cur_s, :num_c)'; -- Execute the INSERT, using literal values. EXECUTE IMMEDIATE IMMEDIATE v_SQLString USING 'ECN', 103, 'Economics 103', 10, 0, 3; -- Register all of the Economics majors for the new class. FOR v_StudentRec IN c_EconMajor LOOP -- Here we have a literal SQL statement, but PL/SQL variables in the USING clause. EXECUTE IMMEDIATE IMMEDIATE 'INSERT INTO registered_students (student_ID, department, course, grade) VALUES (:id, (:id, :dep, :course, :course, NULL)' USING v_StudentRec.ID, v_StudentRec.ID, 'ECN', 103; -- Update the number of students for the class, using an anonymous PL/SQL b lock. v_PLSQLBlock := 'BEGIN UPDATE UPDATE classes SET current_students = current_students + 1 WHERE department = :d and course = :c; END;'; EXECUTE IMMEDIATE v_PLSQLBlock USING 'ECN', 103; END LOOP; END; -----------------------------------------------------------------------------------------------------------------------Dynamic SQL
3
