Oracle PL/SQL/PL SQL/Execute Immediate
Содержание
- 1 Build up the CREATE TABLE statement and run it with EXECUTE IMMEDIATE
- 2 Call a stored procedure in dynamic script
- 3 Call "execute immediate" to drop table, create table and insert data
- 4 Create dynamic sql statement and get the result
- 5 Create Dynamic Tables
- 6 demonstrates the effect of duplicate placeholders with EXECUTE IMMEDIATE.
- 7 Dynamically create packages
- 8 EXECUTE IMMEDIATE
- 9 EXECUTE IMMEDIATE commands
- 10 Execute immediate for an insert statement
- 11 execute immediate using variable
- 12 execute immediate with "insert ... using" to pass variable into insert statement
- 13 how to pass a NULL value to EXECUTE IMMEDIATE.
- 14 Pass value out of dynamic select statement
- 15 Performance between using statement and string concatenation for dynamic sql
- 16 Timing Bind variable
- 17 Use DBMS_SQL to re-create MyTable.
- 18 Use EXECUTE IMMEDIATE to call procedure and save the returning value
- 19 Use execute immediate to create dynamic query in a procedure
- 20 Use execute immediate to insert random numbers
- 21 Use native dynamic SQL to re-create MyTable.
- 22 Use of EXECUTE IMMEDIATE for single-row queries.
- 23 Use parameters in EXECUTE IMMEDIATE
- 24 Use USING clause with EXECUTE IMMEDIATE to handle bind variables.
Build up the CREATE TABLE statement and run it with EXECUTE IMMEDIATE
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE PROCEDURE make_mytable (
2 username IN VARCHAR2
3 ) AS
4 create_stmt VARCHAR2(200);
5 BEGIN
6 create_stmt := "CREATE TABLE "
7 || username || ".mytable"
8 || "(myrow NUMBER, mydesc VARCHAR2(50))";
9
10 EXECUTE IMMEDIATE create_stmt;
11 END;
12 /
Procedure created.
SQL>
SQL>
SQL> --
Call a stored procedure in dynamic script
SQL>
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created.
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 PROCEDURE printLecturer
2 (v_Numlecturer IN OUT NUMBER) AS
3 v_LocalCount NUMBER := 0;
4 myLecturerName VARCHAR2(100);
5 CURSOR c_StudentNames IS
6 SELECT first_name || " " || last_name
7 FROM lecturer
8 ORDER BY ID;
9 BEGIN
10 OPEN c_StudentNames;
11 FOR v_Count IN 1..v_Numlecturer LOOP
12 FETCH c_StudentNames INTO myLecturerName;
13 IF c_StudentNames%NOTFOUND THEN
14 EXIT;
15 END IF;
16
17 DBMS_OUTPUT.PUT_LINE(" " || myLecturername);
18 v_LocalCount := v_LocalCount + 1;
19 END LOOP;
20
21 CLOSE c_StudentNames;
22 v_Numlecturer := v_LocalCount;
23 END printLecturer;
24 /
Procedure created.
SQL>
SQL> DECLARE
2 codeBlock VARCHAR2(1000);
3
4 v_Building place.building%TYPE;
5 v_RoomNum place.room_number%TYPE;
6 v_RoomID place.room_ID%TYPE := 20006;
7 v_Numlecturer NUMBER;
8 BEGIN
9 EXECUTE IMMEDIATE
10 "DELETE FROM place WHERE room_id = :ID RETURNING building, room_number INTO :building, :room_num"
11 USING v_RoomID, OUT v_Building, OUT v_RoomNum;
12
13 DBMS_OUTPUT.PUT_LINE("Deleted Room " || v_RoomNum || " in " || v_Building);
14
15 codeBlock := "BEGIN printLecturer(:num); END;";
16
17 v_Numlecturer := 4;
18 EXECUTE IMMEDIATE codeBlock USING IN OUT v_Numlecturer;
19 DBMS_OUTPUT.PUT_LINE("Returned value is " || v_Numlecturer);
20
21 v_Numlecturer := 20;
22 EXECUTE IMMEDIATE codeBlock USING IN OUT v_Numlecturer;
23 DBMS_OUTPUT.PUT_LINE("Returned value is " || v_Numlecturer);
24 END;
25 /
Deleted Room 100 in Music Building
Scott Lawson
Mar Wells
Jone Bliss
Man Kyte
Returned value is 4
Scott Lawson
Mar Wells
Jone Bliss
Man Kyte
Pat Poll
Tim Viper
Barbara Blues
David Large
Chris Elegant
Rose Bond
Rita Johnson
Sharon Clear
Returned value is 12
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table place;
Table dropped.
SQL>
SQL>
Call "execute immediate" to drop table, create table and insert data
SQL>
SQL> set echo on
SQL> set serveroutput on
SQL> begin
2 for i in 1 .. 100 loop
3 begin
4 execute immediate "drop table t"||i;
5 exception
6 when others then null;
7 end;
8 execute immediate "create table t"||i||" ( dummy char(1) )";
9 execute immediate "insert into t"||i||" values ( ""x"" )";
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
Create dynamic sql statement and get the result
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> create or replace function get_value_dyn( p_empno in number, p_cname in varchar2 ) return varchar2
2 as
3 l_value varchar2(4000);
4 begin
5 execute immediate "select " || p_cname || " from emp where empno = :x"
6 into l_value
7 using p_empno;
8 return l_value;
9 end;
10 /
Function created.
SQL>
SQL>
SQL> declare
2 l_dummy varchar2(30);
3 begin
4 for i in 1 .. 500
5 loop
6 for x in ( select empno from emp )
7 loop
8 l_dummy := get_value_dyn(x.empno, "ENAME" );
9 l_dummy := get_value_dyn(x.empno, "EMPNO" );
10 l_dummy := get_value_dyn(x.empno, "HIREDATE" );
11 end loop;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Create Dynamic Tables
CREATE OR REPLACE PROCEDURE CreateDynamicTables(
p_Method IN VARCHAR2) AS
v_CreateString1 VARCHAR2(100) := "CREATE TABLE dbms_sql_table (f1 NUMBER)";
v_CreateString2 VARCHAR2(100) := "CREATE TABLE native_table (f1 NUMBER)";
v_Dummy INTEGER;
v_CursorID INTEGER;
BEGIN
IF p_Method = "DBMS_SQL" THEN
v_CursorID := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
ELSE
EXECUTE IMMEDIATE v_CreateString2;
END IF;
END CreateDynamicTables;
/
exec CreateDynamicTables("DBMS_SQL")
exec CreateDynamicTables("native")
DROP TABLE dbms_sql_table;
DROP TABLE native_table;
--
demonstrates the effect of duplicate placeholders with EXECUTE IMMEDIATE.
SQL>
SQL>
SQL> CREATE TABLE duplicates (
2 f1 NUMBER,
3 f2 NUMBER,
4 f3 NUMBER,
5 f4 NUMBER);
Table created.
SQL>
SQL> DECLARE
2 v_InsertStmt VARCHAR2(100);
3 v_f1 NUMBER := 1;
4 v_f2 NUMBER := 2;
5 v_f3 NUMBER := 3;
6 BEGIN
7 v_InsertStmt :=
8 "INSERT INTO duplicates VALUES (:a, :b, :c, :a)";
9
10 EXECUTE IMMEDIATE v_InsertStmt
11 USING v_f1, v_f2, v_f3, v_f1;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
2 FROM duplicates;
F1 F2 F3 F4
---------- ---------- ---------- ----------
1 2 3 1
1 row selected.
SQL>
SQL> DECLARE
2 v_InsertStmt VARCHAR2(100);
3 v_f1 NUMBER := 4;
4 v_f2 NUMBER := 5;
5 v_f3 NUMBER := 6;
6 BEGIN
7 v_InsertStmt :=
8 "BEGIN " ||
9 " INSERT INTO duplicates VALUES (:a, :b, :c, :a); " ||
10 "END;";
11
12 EXECUTE IMMEDIATE v_InsertStmt
13 USING v_f1, v_f2, v_f3;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
2 FROM duplicates;
F1 F2 F3 F4
---------- ---------- ---------- ----------
1 2 3 1
4 5 6 4
2 rows selected.
SQL>
SQL> DROP TABLE duplicates;
Table dropped.
SQL>
SQL>
Dynamically create packages
SQL> declare
2 x varchar2(32767);
3 begin
4 for i in 1 .. 300 loop
5 x := x || " procedure X"||i||";";
6 end loop;
7 execute immediate "create or replace package aTest is "||x||" end;";
8 x := replace(x,";"," is y number; begin y := 1; end;");
9
10 execute immediate "create or replace package body aTest is "||x||" end;";
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
EXECUTE IMMEDIATE
SQL>
SQL> SET ECHO ON
SQL>
SQL> BEGIN
2 EXECUTE IMMEDIATE "CREATE TABLE YOURTABLE ("
3 || "YOURROW NUMBER, "
4 || "YOURDESC VARCHAR2(50))";
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table yourtable;
Table dropped.
SQL> --
EXECUTE IMMEDIATE commands
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 sqlString VARCHAR2(200);
3 codeBlock VARCHAR2(200);
4 BEGIN
5 EXECUTE IMMEDIATE "CREATE TABLE execute_table (col1 VARCHAR(10))";
6
7 FOR v_Counter IN 1..10 LOOP
8 sqlString :=
9 "INSERT INTO execute_table
10 VALUES (""Row " || v_Counter || """)";
11 EXECUTE IMMEDIATE sqlString;
12 END LOOP;
13
14 codeBlock :=
15 "BEGIN
16 FOR v_Rec IN (SELECT * FROM execute_table) LOOP
17 DBMS_OUTPUT.PUT_LINE(v_Rec.col1);
18 END LOOP;
19 END;";
20
21 EXECUTE IMMEDIATE codeBlock;
22
23 EXECUTE IMMEDIATE "DROP TABLE execute_table";
24 END;
25 /
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9
Row 10
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Execute immediate for an insert statement
SQL>
SQL> -- Define procedure and execute them
SQL>
SQL> create table mytable ( x int );
Table created.
SQL>
SQL> create or replace procedure myProcecdure1 as
2 begin
3 for i in 1 .. 10000
4 loop
5 execute immediate "insert into mytable values ( :x )" using i;
6 end loop;
7 end;
8 /
Procedure created.
SQL>
SQL> create or replace procedure myProcecdure2 as
2 begin
3 for i in 1 .. 10000
4 loop
5 execute immediate "insert into mytable values ( "||i||")";
6 end loop;
7 end;
8 /
Procedure created.
SQL>
SQL> exec myProcecdure1;
PL/SQL procedure successfully completed.
SQL>
SQL> exec myProcecdure2;
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
execute immediate using variable
SQL>
SQL>
SQL> create table t ( x int );
Table created.
SQL> create or replace procedure proc1
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate "insert into t values ( :x )" using i;
7 end loop;
8 end;
9 /
Procedure created.
SQL> create or replace procedure proc2
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate "insert into t values ( "||i||")";
7 end loop;
8 end;
9 /
Procedure created.
SQL> exec proc1
PL/SQL procedure successfully completed.
SQL> exec proc2
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
execute immediate with "insert ... using" to pass variable into insert statement
SQL>
SQL> create table t( c1 int, c2 int, c3 int, c4 int ) storage ( freelists 10 );
Table created.
SQL>
SQL> set echo on
SQL>
SQL> declare
2 myNumber number;
3 begin
4 for i in 1 .. 10
5 loop
6 myNumber := dbms_random.random;
7
8 execute immediate
9 "insert into t values ( :x1, :x2, :x3, :x4 )"
10 using myNumber, myNumber, myNumber, myNumber;
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
C1 C2 C3 C4
---------- ---------- ---------- ----------
-215681493 -215681493 -215681493 -215681493
65675438 65675438 65675438 65675438
-544483009 -544483009 -544483009 -544483009
327912408 327912408 327912408 327912408
-233632941 -233632941 -233632941 -233632941
623357100 623357100 623357100 623357100
616373886 616373886 616373886 616373886
-703556128 -703556128 -703556128 -703556128
-928105331 -928105331 -928105331 -928105331
-179825426 -179825426 -179825426 -179825426
10 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
how to pass a NULL value to EXECUTE IMMEDIATE.
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 sqlString VARCHAR2(1000);
3 v_NullVar VARCHAR2(10);
4 BEGIN
5 sqlString :=
6 "INSERT INTO MyTable (num_col, char_col) VALUES
7 (:n, :c)";
8
9 EXECUTE IMMEDIATE sqlString USING 1, v_NulLVar;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
Pass value out of dynamic select statement
SQL>
SQL> CREATE TABLE MyTable(yourRow INTEGER, yourDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 block_to_execute VARCHAR2(200) :=
3 "BEGIN
4 SELECT YourRow,YourDesc
5 INTO :1, :2 FROM myTable
6 WHERE YourRow = 2;
7 END;";
8
9 YourRow NUMBER;
10 YourDesc VARCHAR2(100);
11 BEGIN
12 EXECUTE IMMEDIATE block_to_execute USING OUT YourRow, OUT YourDesc;
13 DBMS_OUTPUT.PUT_LINE(YourRow || " " || YourDesc);
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at line 2
ORA-06512: at line 12
SQL>
SQL> drop table mytable;
Table dropped.
SQL> --
Performance between using statement and string concatenation for dynamic sql
SQL>
SQL> create or replace procedure DO_WORK(x number) is
2 y number;
3 begin
4 y := x;
5 end;
6 /
Procedure created.
SQL> set timing on
SQL>
SQL> begin
2 for i in 1 .. 100 loop
3 execute immediate "begin do_work(:x); end;" using i;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL>
SQL> begin
2 for i in 1 .. 100 loop
3 execute immediate "begin do_work("||i||"); end;";
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
Timing Bind variable
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> set serveroutput on
SQL> declare
2 l_start number;
3 l_cnt number;
4 begin
5 l_start := dbms_utility.get_time;
6
7 for i in 1 .. 100
8 loop
9 execute immediate
10 "select count(*) from dual where dummy = """ || to_char(i) || """" INTO l_cnt;
11 end loop;
12
13 dbms_output.put_line( "No Binds " || (dbms_utility.get_time-l_start) || " hsecs" );
14 l_start := dbms_utility.get_time;
15 for i in 1 .. 100
16 loop
17 execute immediate "select count(*) from dual where dummy = :x"
18 INTO l_cnt
19 USING to_char(i);
20 end loop;
21 dbms_output.put_line( "Binding " || (dbms_utility.get_time-l_start) || " hsecs" );
22 end;
23 /
No Binds 7 hsecs
Binding 1 hsecs
PL/SQL procedure successfully completed.
SQL>
Use DBMS_SQL to re-create MyTable.
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
2 p_Description IN VARCHAR2) IS
3
4 v_Cursor NUMBER;
5 v_CreateString VARCHAR2(100);
6 v_DropString VARCHAR2(100);
7 BEGIN
8 v_Cursor := DBMS_SQL.OPEN_CURSOR;
9
10 v_DropString := "DROP TABLE MyTable";
11
12 BEGIN
13 DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.NATIVE);
14 EXCEPTION
15 WHEN OTHERS THEN
16 IF SQLCODE != -942 THEN
17 RAISE;
18 END IF;
19 END;
20
21 v_CreateString := "CREATE TABLE MyTable " || p_Description;
22 DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.NATIVE);
23
24 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
25 EXCEPTION
26 WHEN OTHERS THEN
27 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
28 RAISE;
29 END RecreateTempTable;
30 /
Procedure created.
SQL> drop table MyTable;
Table dropped.
SQL>
Use EXECUTE IMMEDIATE to call procedure and save the returning value
SQL>
SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
4 END CallProc1;
5 /
Procedure created.
SQL>
SQL> DECLARE
2 myResult VARCHAR2(50);
3 BEGIN
4 EXECUTE IMMEDIATE "CALL CallProc1(""Hello from PL/SQL"")";
5 EXECUTE IMMEDIATE "CALL CallFunc(""Hello from PL/SQL"") INTO :myResult"
6 USING OUT myResult;
7 END;
8 /
CallProc1 called with Hello from PL/SQL
CallFunc called with Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>
Use execute immediate to create dynamic query in a procedure
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL>
SQL> CREATE or replace PROCEDURE update_sal (id in number, p_sal in number)
2 authid current_user
3 AS
4 BEGIN
5 update EMP set sal = p_sal where empno = id;
6 END;
7 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE procedure update_sal (id in number, p_sal in number) authid current_user as
2 begin
3 execute immediate "update EMP set "||"sal = :x " ||"where empno = :y" using p_sal, id;
4 end;
5 /
Procedure created.
SQL>
SQL> exec update_sal (6,5000);
PL/SQL procedure successfully completed.
SQL>
SQL> select ename, empno, sal from EMP where ename = USER;
no rows selected
SQL>
SQL> drop procedure update_sal;
Procedure dropped.
SQL> drop table emp;
Table dropped.
Use execute immediate to insert random numbers
SQL>
SQL> create table t( c1 int, c2 int, c3 int, c4 int ) storage ( freelists 10 );
Table created.
SQL>
SQL>
SQL> declare
2 myNumber number;
3 begin
4 for i in 1 .. 10
5 loop
6 myNumber := dbms_random.random;
7
8 execute immediate
9 "insert into t values ( " || myNumber || "," ||
10 myNumber || "," ||
11 myNumber || "," ||
12 myNumber || ")";
13 end loop;
14 commit;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from t;
C1 C2 C3 C4
---------- ---------- ---------- ----------
-934884549 -934884549 -934884549 -934884549
1258621996 1258621996 1258621996 1258621996
-739854461 -739854461 -739854461 -739854461
-2.077E+09 -2.077E+09 -2.077E+09 -2.077E+09
286830488 286830488 286830488 286830488
-1.128E+09 -1.128E+09 -1.128E+09 -1.128E+09
1345837309 1345837309 1345837309 1345837309
87152190 87152190 87152190 87152190
561507416 561507416 561507416 561507416
-2.139E+09 -2.139E+09 -2.139E+09 -2.139E+09
10 rows selected.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Use native dynamic SQL to re-create MyTable.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
2 p_Description IN VARCHAR2) IS
3
4 v_CreateString VARCHAR2(100);
5 v_DropString VARCHAR2(100);
6 BEGIN
7 v_DropString := "DROP TABLE MyTable";
8
9 BEGIN
10 EXECUTE IMMEDIATE v_DropString;
11 EXCEPTION
12 WHEN OTHERS THEN
13 IF SQLCODE != -942 THEN
14 RAISE;
15 END IF;
16 END;
17
18 v_CreateString := "CREATE TABLE MyTable " || p_Description;
19 EXECUTE IMMEDIATE v_CreateString;
20 END RecreateTempTable;
21 /
Procedure created.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
SQL>
Use of EXECUTE IMMEDIATE for single-row queries.
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL> DECLARE
2 v_SQLQuery VARCHAR2(200);
3 v_Class session%ROWTYPE;
4 v_Description session.description%TYPE;
5 BEGIN
6 v_SQLQuery :=
7 "SELECT description " ||
8 " FROM session " ||
9 " WHERE department = ""ECN""" ||
10 " AND course = 203";
11
12 EXECUTE IMMEDIATE v_SQLQuery
13 INTO v_Description;
14
15 DBMS_OUTPUT.PUT_LINE("Fetched " || v_Description);
16
17 v_SQLQuery :=
18 "SELECT * " ||
19 " FROM session " ||
20 " WHERE description = :description";
21 EXECUTE IMMEDIATE v_SQLQuery
22 INTO v_Class
23 USING v_Description;
24
25 DBMS_OUTPUT.PUT_LINE(
26 "Fetched " || v_Class.department || " " || v_Class.course);
27
28 v_SQLQuery := "SELECT * FROM session";
29 EXECUTE IMMEDIATE v_SQLQuery
30 INTO v_Class;
31 END;
32 /
Fetched Economics 203
Fetched ECN 203
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 29
SQL>
SQL> drop table session;
Table dropped.
SQL>
Use parameters in EXECUTE IMMEDIATE
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL>
SQL> DECLARE
2 v_YourNum NUMBER;
3 v_YourDesc VARCHAR2(50);
4 v_INSERT_stmt VARCHAR2(100);
5 BEGIN
6 v_INSERT_stmt := "INSERT INTO mytable VALUES (:1, :2)";
7
8 v_YourNum := 1;
9 v_YourDesc := "One";
10 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
11
12 v_YourNum := 2;
13 v_YourDesc := "Two";
14 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
15
16 v_YourNum := 3;
17 v_YourDesc := "Three";
18 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
19
20 v_YourNum := 4;
21 v_YourDesc := "Four";
22 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
23
24 v_YourNum := 5;
25 v_YourDesc := "Five";
26 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
27 END;
28 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * FROM MYTABLE
2
SQL> drop table mytable;
Table dropped.
SQL> --
Use USING clause with EXECUTE IMMEDIATE to handle bind variables.
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
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> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10004, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10005, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "HIS", 101, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10007, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "HIS", 101, "D");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "MUS", 410, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10011, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "MUS", 410, "B");
1 row created.
SQL>
SQL>
SQL> DECLARE
2 sqlString VARCHAR2(1000);
3 codeBlock VARCHAR2(1000);
4
5 CURSOR c_EconMajor IS
6 SELECT *
7 FROM lecturer
8 WHERE major = "Economics";
9 BEGIN
10 sqlString :=
11 "INSERT INTO session (department, course, description,
12 max_lecturer, current_lecturer,
13 num_credits)
14 VALUES (:dep, :course, :descr, :max_s, :cur_s, :num_c)";
15
16 EXECUTE IMMEDIATE sqlString USING
17 "ECN", 103, "Economics 103", 10, 0, 3;
18
19 FOR myLecturerRec IN c_EconMajor LOOP
20 EXECUTE IMMEDIATE
21 "INSERT INTO myStudent
22 (student_ID, department, course, grade)
23 VALUES (:id, :dep, :course, NULL)"
24 USING myLecturerRec.ID, "ECN", 103;
25
26 codeBlock :=
27 "BEGIN
28 UPDATE session SET current_lecturer = current_lecturer + 1
29 WHERE department = :d and course = :c;
30 END;";
31
32 EXECUTE IMMEDIATE codeBlock USING "ECN", 103;
33 END LOOP;
34 END;
35 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.