Oracle PL/SQL/PL SQL/Execute Immediate

Материал из SQL эксперт
Версия от 12:59, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Build up the CREATE TABLE statement and run it with EXECUTE IMMEDIATE

   <source lang="sql">

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> -- 

 </source>


Call a stored procedure in dynamic script

   <source lang="sql">

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> 

 </source>


Call "execute immediate" to drop table, create table and insert data

   <source lang="sql">

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> 

 </source>


Create dynamic sql statement and get the result

   <source lang="sql">

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> 



 </source>


Create Dynamic Tables

   <source lang="sql">

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;

-- 

 </source>


demonstrates the effect of duplicate placeholders with EXECUTE IMMEDIATE.

   <source lang="sql">

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> 

 </source>


Dynamically create packages

   <source lang="sql">

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> 



 </source>


EXECUTE IMMEDIATE

   <source lang="sql">

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> -- 

 </source>


EXECUTE IMMEDIATE commands

   <source lang="sql">

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> 

 </source>


Execute immediate for an insert statement

   <source lang="sql">

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> 


 </source>


execute immediate using variable

   <source lang="sql">

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. 



 </source>


execute immediate with "insert ... using" to pass variable into insert statement

   <source lang="sql">

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> -- 

 </source>


how to pass a NULL value to EXECUTE IMMEDIATE.

   <source lang="sql">

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> 

 </source>


Pass value out of dynamic select statement

   <source lang="sql">

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> -- 

 </source>


Performance between using statement and string concatenation for dynamic sql

   <source lang="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 



 </source>


Timing Bind variable

   <source lang="sql">

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> 

 </source>


Use DBMS_SQL to re-create MyTable.

   <source lang="sql">

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> 

 </source>


Use EXECUTE IMMEDIATE to call procedure and save the returning value

   <source lang="sql">

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> 

 </source>


Use execute immediate to create dynamic query in a procedure

   <source lang="sql">

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. 



 </source>


Use execute immediate to insert random numbers

   <source lang="sql">

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> -- 

 </source>


Use native dynamic SQL to re-create MyTable.

   <source lang="sql">

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> 

 </source>


Use of EXECUTE IMMEDIATE for single-row queries.

   <source lang="sql">

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> 

 </source>


Use parameters in EXECUTE IMMEDIATE

   <source lang="sql">

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> -- 

 </source>


Use USING clause with EXECUTE IMMEDIATE to handle bind variables.

   <source lang="sql">

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. 

 </source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/PL_SQL/Execute_Immediate&oldid=1816»