Oracle PL/SQL Tutorial/PL SQL Statements/Execute immediate
Содержание
- 1 Call EXECUTE IMMEDIATE in Pl/SQL block
- 2 Call function and get result by using "EXECUTE IMMEDIATE"
- 3 Catch exception from "EXECUTE IMMEDIATE"
- 4 Create a function to count table row
- 5 Define a procedure to drop a database object
- 6 Drop user, create user and grant permission with PL/SQL code
- 7 EXECUTE IMMEDIATE dynamic sql to alter session
- 8 "execute immediate in" action
- 9 execute immediate into
- 10 EXECUTE IMMEDIATE USING IN
- 11 Execute sql statement in a procedure
- 12 Quotation string
- 13 select into rowtype then use it in "execute immediate"
- 14 Simple EXECUTE IMMEDIATE
- 15 Update row with "execute immediate"
- 16 Use "EXECUTE IMMEDIATE" to execute an update statement
- 17 Use "execute immediate" to run a insert statement
- 18 Use procedure to create an index dynamically
- 19 Wrap "EXECUTE IMMEDIATE" for current user
- 20 Wrap statement with "BEGIN...END"
Call EXECUTE IMMEDIATE in Pl/SQL block
SQL>
SQL> CREATE OR REPLACE FUNCTION value_in (varname IN VARCHAR)
2 RETURN VARCHAR2
3 IS
4 retval VARCHAR2(2000);
5 BEGIN
6 EXECUTE IMMEDIATE "BEGIN :val := " || varname || "; END;" USING OUT retval;
7 RETURN retval;
8 END;
9 /
Function created.
SQL>
Call function and get result by using "EXECUTE IMMEDIATE"
SQL>
SQL> CREATE OR REPLACE FUNCTION grpval (
2 tab IN VARCHAR2,
3 col IN VARCHAR2,
4 grpfunc IN VARCHAR2,
5 whr IN VARCHAR2 := NULL)
6 RETURN VARCHAR2
7 IS
8 retval VARCHAR2(32767);
9 BEGIN
10 EXECUTE IMMEDIATE
11 "SELECT " || yourfunction || "(" || col || ")
12 FROM " || tab || " WHERE " || NVL (whr, "1=1")
13 INTO retval;
14 RETURN retval;
15 END;
16 /
Function created.
SQL>
SQL>
Catch exception from "EXECUTE IMMEDIATE"
SQL>
SQL> CREATE OR REPLACE FUNCTION tabcount (tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
2 RETURN PLS_INTEGER AUTHID CURRENT_USER
3 IS
4 str VARCHAR2 (32767) := "SELECT COUNT(*) FROM " || tab;
5 retval PLS_INTEGER;
6 BEGIN
7 IF whr IS NOT NULL
8 THEN
9 str := str || " WHERE " || whr;
10 END IF;
11
12 EXECUTE IMMEDIATE str
13 INTO retval;
14 EXCEPTION
15 WHEN OTHERS
16 THEN
17 DBMS_OUTPUT.put_line ("TABCOUNT ERROR: " || DBMS_UTILITY.FORMAT_ERROR_STACK);
18 DBMS_OUTPUT.put_line (str);
19 RETURN NULL;
20 END;
21 /
SP2-0806: Function created with compilation warnings
SQL>
Create a function to count table row
SQL>
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25) PRIMARY KEY,
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE
6 );
Table created.
SQL>
SQL>
SQL> INSERT INTO product VALUES ("Product 1", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 3", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 4", 25, 10000, null);
1 row created.
SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04");
1 row created.
SQL> INSERT INTO product VALUES ("Product 6", 45, 1, TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M."));
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION tabcount (nm IN VARCHAR2)RETURN PLS_INTEGER
2 IS
3
4 retval PLS_INTEGER;
5
6 BEGIN
7
8 EXECUTE IMMEDIATE "SELECT COUNT(*) FROM " || nm INTO retval;
9
10 RETURN retval;
11 END;
12 /
Function created.
SQL>
SQL>
SQL> select tabcount("product") from dual;
TABCOUNT("PRODUCT")
-------------------
6
SQL>
SQL> drop table product;
Table dropped.
Define a procedure to drop a database object
SQL>
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25) PRIMARY KEY,
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE
6 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE dropit (ittype IN VARCHAR2, itname IN VARCHAR2)
2 IS
3 BEGIN
4 EXECUTE IMMEDIATE "drop " || ittype || " " || itname;
5 END;
6 /
Procedure created.
SQL>
SQL> EXEC dropit("table","product");
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Drop user, create user and grant permission with PL/SQL code
SQL>
SQL> DEF username = plsql
SQL> DEF default_ts = USERS
SQL> DEF temp_ts = TEMP
SQL>
SQL> SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SQL>
SQL> DECLARE
2 v_count INTEGER := 0;
3 v_statement VARCHAR2 (500);
4 BEGIN
5
6 SELECT COUNT (1) INTO v_count FROM dba_users
7 WHERE username = UPPER ("&username");
8
9 IF v_count != 0
10 THEN
11 EXECUTE IMMEDIATE ("DROP USER &username CASCADE");
12 END IF;
13
14 v_count := 0;
15
16 v_statement :=
17 "CREATE USER &username IDENTIFIED BY oracle"
18 || " DEFAULT TABLESPACE &default_ts"
19 || " TEMPORARY TABLESPACE &temp_ts"
20 || " QUOTA UNLIMITED ON &default_ts"
21 || " ACCOUNT UNLOCK";
22
23 EXECUTE IMMEDIATE (v_statement);
24
25 -- Grant permissions
26 EXECUTE IMMEDIATE ("GRANT connect, resource TO &username");
27 EXECUTE IMMEDIATE ("GRANT CTXAPP TO &username");
28
29 DBMS_OUTPUT.put_line (" ");
30 DBMS_OUTPUT.put_line ("User &username created successfully");
31 DBMS_OUTPUT.put_line (" ");
32
33 EXCEPTION
34 WHEN OTHERS
35 THEN
36 DBMS_OUTPUT.put_line (SQLERRM);
37 DBMS_OUTPUT.put_line (" ");
38 END;
39 /
User plsql created successfully
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL>
SQL>
EXECUTE IMMEDIATE dynamic sql to alter session
SQL>
SQL>
SQL> DECLARE
2 lv_sql_txt VARCHAR2(200);
3 BEGIN
4 EXECUTE IMMEDIATE "ALTER SESSION SET SQL_TRACE=TRUE";
5 lv_sql_txt := "ALTER SESSION SET SORT_AREA_SIZE = 1000000";
6 EXECUTE IMMEDIATE lv_sql_txt;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
"execute immediate in" action
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> create or replace procedure p_backupEmp is
2 v_name_tx VARCHAR2(30);
3 begin
4 -- v_name_tx:="employee"||TO_CHAR(sysdate,"YYYYMMDDHH24MISS");
5 v_name_tx:="employee001";
6 execute immediate "create table "||v_name_tx||
7 " as select * from employee";
8 end;
9 /
Procedure created.
SQL>
SQL> call p_backupEmp();
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
execute immediate into
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
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> set echo on
SQL>
SQL> create or replace function get_row_cnts( p_tname in varchar2 ) return number
2 as
3 l_cnt number;
4 begin
5 execute immediate "select count(*) from " || p_tname into l_cnt;
6 return l_cnt;
7 end;
8 /
Function created.
SQL>
SQL> set serveroutput on
SQL> exec dbms_output.put_line( get_row_cnts("emp") );
14
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
EXECUTE IMMEDIATE USING IN
SQL>
SQL> CREATE TABLE employee(
2 employee_id VARCHAR2(3) PRIMARY KEY,
3 first_name VARCHAR2(15),
4 last_name VARCHAR2(20),
5 hire_date DATE
6 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE run_9am_procedure (
2 id_in IN employee.employee_id%TYPE,
3 hour_in IN INTEGER
4 )
5 IS
6 v_apptcount INTEGER;
7 v_name VARCHAR2 (100);
8 BEGIN
9 EXECUTE IMMEDIATE "BEGIN "
10 || TO_CHAR (SYSDATE, "DAY")
11 || "_set_schedule (:id, :hour, :name, :appts); END;"
12 USING IN id_in, IN hour_in, OUT v_name, OUT v_apptcount;
13
14 DBMS_OUTPUT.put_line (v_name|| " has "|| v_apptcount|| " appointments on "|| TO_CHAR (SYSDATE));
15 END;
16 /
Procedure created.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Execute sql statement in a procedure
SQL>
SQL> CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)
2 AUTHID CURRENT_USER
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE ddl_in;
6 END;
7 /
Procedure created.
SQL>
SQL>
SQL> EXEC runddl("select sysdate from dual");
PL/SQL procedure successfully completed.
Quotation string
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION qstring (str_in IN VARCHAR2, qchar_in VARCHAR2 := "|")
2 RETURN VARCHAR2
3 IS
4 retval VARCHAR2(32767);
5 BEGIN
6 EXECUTE IMMEDIATE
7 "BEGIN :var := q""" || qchar_in || str_in || qchar_in || """; END;"
8 USING OUT retval;
9 RETURN retval;
10 END;
11 /
Function created.
SQL>
SQL>
SQL>
select into rowtype then use it in "execute immediate"
SQL>
SQL> create table job_parameters
2 ( jobid number primary key,
3 iterations number,
4 table_idx number );
Table created.
SQL>
SQL> create or replace procedure dont_bind( p_job in number )
2 as
3 l_rec job_parameters%rowtype;
4 begin
5 select * into l_rec from job_parameters where jobid = p_job;
6 for i in 1 .. l_rec.iterations
7 loop
8 execute immediate "insert into t" || l_rec.table_idx || " values ( " || i || " )";
9 commit;
10 end loop;
11 delete from job_parameters where jobid = p_job;
12 end;
13 /
Procedure created.
SQL>
SQL> drop table job_parameters;
Table dropped.
SQL>
SQL>
Simple EXECUTE IMMEDIATE
The EXECUTE IMMEDIATE command can be
- a VARCHAR2 variable,
- a literal quoted string, or
- any string expression.
begin
execute immediate "whatever_text_string_you_want";
end;
Update row with "execute immediate"
SQL> set echo on
SQL>
SQL> create or replace
2 function update_row( p_owner in varchar2,
3 p_newDname in varchar2,
4 p_newLoc in varchar2,
5 p_deptno in varchar2,
6 p_rowid out varchar2 )
7 return number
8 is
9 begin
10 execute immediate "update " || p_owner || ".dept
11 set dname = :bv1, loc = :bv2
12 where deptno = to_number(:pk)
13 returning rowid into :out"
14 using p_newDname, p_newLoc, p_deptno
15 returning into p_rowid;
16
17 return sql%rowcount;
18 end;
19 /
Function created.
SQL>
SQL> set serveroutput on
SQL> declare
2 l_rowid varchar(50);
3 l_rows number;
4 begin
5 l_rows := update_row( "SCOTT", "CONSULTING", "WASHINGTON", "10", l_rowid );
6 dbms_output.put_line( "Updated " || l_rows || " rows" );
7 dbms_output.put_line( "its rowid was " || l_rowid );
8 end;
9 /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "sqle.UPDATE_ROW", line 9
ORA-06512: at line 5
SQL>
SQL>
Use "EXECUTE IMMEDIATE" to execute an update statement
SQL>
SQL> CREATE OR REPLACE FUNCTION updnval (
2 col IN VARCHAR2
3 , val IN NUMBER
4 , start_in IN DATE
5 , end_in IN DATE
6 )
7 RETURN PLS_INTEGER
8 IS
9 BEGIN
10 EXECUTE IMMEDIATE "UPDATE employee SET "
11 || col
12 || " = :the_value
13 WHERE hire_date BETWEEN :lo AND :hi"
14 USING val, start_in, end_in;
15
16 RETURN SQL%ROWCOUNT;
17 END;
18 /
Function created.
SQL>
SQL>
Use "execute immediate" to run a insert statement
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL>
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate "insert into t values ( " || i || ")";
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
Use procedure to create an index dynamically
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)
2 AUTHID CURRENT_USER
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE ddl_in;
6 END;
7 /
Procedure created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE creindx(index_in IN VARCHAR2, tab_in IN VARCHAR2, col_in IN VARCHAR2)
2 IS
3 DDL_statement VARCHAR2(200):= "CREATE INDEX " || index_in || " ON " || tab_in ||" ( " || col_in || ")";
4 BEGIN
5 runddl (DDL_statement);
6 END;
7 /
Procedure created.
SQL>
SQL>
SQL>
Wrap "EXECUTE IMMEDIATE" for current user
SQL>
SQL> CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)
2 AUTHID CURRENT_USER
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE ddl_in;
6 END;
7 /
Procedure created.
SQL>
SQL>
Wrap statement with "BEGIN...END"
SQL>
SQL> CREATE OR REPLACE PROCEDURE dynPLSQL (blk IN VARCHAR2)
2 IS
3 BEGIN
4 EXECUTE IMMEDIATE "BEGIN " || RTRIM (blk, ";") || "; END;";
5 END;
6 /
Procedure created.
SQL>
SQL>
SQL>