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
<source lang="sql">
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></source>
Call function and get result by using "EXECUTE IMMEDIATE"
<source lang="sql">
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></source>
Catch exception from "EXECUTE IMMEDIATE"
<source lang="sql">
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></source>
Create a function to count table row
<source lang="sql">
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.</source>
Define a procedure to drop a database object
<source lang="sql">
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></source>
Drop user, create user and grant permission with PL/SQL code
<source lang="sql">
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></source>
EXECUTE IMMEDIATE dynamic sql to alter session
<source lang="sql">
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></source>
"execute immediate in" action
<source lang="sql">
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></source>
execute immediate into
<source lang="sql">
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.</source>
EXECUTE IMMEDIATE USING IN
<source lang="sql">
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></source>
Execute sql statement in a procedure
<source lang="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> EXEC runddl("select sysdate from dual"); PL/SQL procedure successfully completed.</source>
Quotation string
<source lang="sql">
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></source>
select into rowtype then use it in "execute immediate"
<source lang="sql">
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></source>
Simple EXECUTE IMMEDIATE
The EXECUTE IMMEDIATE command can be
- a VARCHAR2 variable,
- a literal quoted string, or
- any string expression.
<source lang="sql">
begin
execute immediate "whatever_text_string_you_want";
end;</source>
Update row with "execute immediate"
<source lang="sql">
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></source>
Use "EXECUTE IMMEDIATE" to execute an update statement
<source lang="sql">
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></source>
Use "execute immediate" to run a insert statement
<source lang="sql">
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></source>
Use procedure to create an index dynamically
<source lang="sql">
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></source>
Wrap "EXECUTE IMMEDIATE" for current user
<source lang="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></source>
Wrap statement with "BEGIN...END"
<source lang="sql">
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></source>
