动态执行DML语句：
DECLARE
JID JOBS.JOB_ID%TYPE;
EID EMPLOYEES.EMPLOYEE_ID%TYPE;
U_SQL VARCHAR2(500);
BEGIN
JID := 'AD_VP';
U_SQL := 'update jobs set max_salary = :1 where job_id = :2';
EXECUTE IMMEDIATE U_SQL
USING 99999, JID;
EID := 197;
EXECUTE IMMEDIATE 'delete from employees where employee_id = :1'
USING EID;
END;
在PL/SQL或脚本中动态执行一段DDL语句
DECLARE
create_sql VARCHAR2(1000);
drop_sql VARCHAR2(1000);
tab_name VARCHAR2(20);
BEGIN
tab_name := 'MyTables';
create_sql := 'CREATE TABLE '||tab_name||'(
id number,
city varchar2(100))';
EXECUTE IMMEDIATE create_sql;
drop_sql := 'DROP TABLE '||tab_name;
EXECUTE IMMEDIATE drop_sql;
END;
-------------------------
执行动态命令：
EXECUTE IMMEDIATE 'grant hr to dba';
EXECUTE IMMEDIATE 'alter user hr account unlock';
EXECUTE IMMEDIATE 'alter system set _share_cursor=true';
对于DELETE、INSERT、UPDATE操作，批量单个结果时使用RETURNING INTO
DECLARE
JID JOBS.JOB_ID%TYPE;
U_SQL VARCHAR2(500);
MINSALARY JOBS.MIN_SALARY%TYPE;
MAXSALARY JOBS.MAX_SALARY%TYPE;
BEGIN
JID := 'AD_VP';
U_SQL := 'update jobs set max_salary = :1 where job_id = :2
returning min_salary,max_salary into :3,:4';
EXECUTE IMMEDIATE U_SQL
USING 77777, JID
RETURNING INTO MINSALARY, MAXSALARY;
END;
使用SELECT返回单条结果，使用INTO
DECLARE
JID JOBS.JOB_ID%TYPE;
JTITLE JOBS.JOB_TITLE%TYPE;
S_SQL VARCHAR2(500);
BEGIN
JID := 'AD_VP';
S_SQL := 'select job_title from jobs where job_id = :1';
EXECUTE IMMEDIATE S_SQL
INTO JTITLE
USING JID;
DBMS_OUTPUT.PUT_LINE(JTITLE);
END;
对于DELETE、INSERT、UPDATE操作，批量返回结果集使用RETURNING BULK COLLECT INTO
DECLARE
TYPE job_table IS TABLE OF jobs%ROWTYPE;
TYPE jobid_table IS TABLE OF jobs.JOB_ID%TYPE;
TYPE title_table IS TABLE OF jobs.JOB_TITLE%TYPE;
salary jobs.MIN_SALARY%TYPE;
jobList job_table;
idList jobid_table;
titleList title_table;
v_sql VARCHAR2(100);
u_sql VARCHAR2(100);
BEGIN
salary := 4000;
v_sql := 'select * from jobs where min_salary < :salary';
EXECUTE IMMEDIATE v_sql BULK COLLECT INTO jobList
USING salary;
FOR i IN 1..jobList.count LOOP
DBMS_OUTPUT.put_line(jobList(i).job_title);
END LOOP;
idList := jobid_table('AD_PRES','AD_VP','FI_MGR');
u_sql := 'update jobs set max_salary = 55555
where job_id = :1 returning job_title into :jtitle';
FORALL i IN 1..idList.count
EXECUTE IMMEDIATE u_sql USING idList(i)
RETURNING BULK COLLECT INTO titleList;
FOR i IN 1..titleList.count LOOP
DBMS_OUTPUT.put_line(titleList(i));
END LOOP;
END;
SELECT语句返回结果时使用BULK COLLECT INTO
创建对象：
CREATE OR REPLACE TYPE T_OBJ_EMP FORCE AS OBJECT(
EID NUMBER,
ENAME VARCHAR2(20),
HIREDATE DATE
);
创建嵌套表：
CREATE OR REPLACE TYPE T_TBL_EMP IS TABLE OF T_OBJ_EMP;
创建存储过程：
CREATE OR REPLACE PROCEDURE P1 IS
EMPLIST T_TBL_EMP;
V_SQL VARCHAR2(1000);
V_JOBID VARCHAR2(20);
BEGIN
V_JOBID := 'IT_PROG';
V_SQL := 'SELECT T_OBJ_EMP(X.EMPLOYEE_ID, X.FIRST_NAME, X.HIRE_DATE)
FROM EMPLOYEES X
WHERE X.JOB_ID = :0 ';
EXECUTE IMMEDIATE V_SQL BULK COLLECT
INTO EMPLIST
USING V_JOBID;
FOR I IN 1 .. EMPLIST.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Ename:' || EMPLIST(I).ENAME);
END LOOP;
END P1;
