PL/SQL 11g R2 ——动态SQL

17-1:处理DDL语句    DECLARE      ddl_string VARCHAR2(100):='CREATE TABLE a1(cola INT)';    BEGIN      EXECUTE IMMEDIATE ddl_string;    END;    /    17-2:处理DCL语句    DECLARE      dcl_string VARCHAR2(100):='GRANT SELECT ON dept TO hr';    BEGIN      EXECUTE IMMEDIATE dcl_string;    END;    /    17-3:处理DML语句    DECLARE      dml_stat VARCHAR2(100):='UPDATE emp SET sal=1800 WHERE empno=7788';    BEGIN      EXECUTE IMMEDIATE dml_stat;    END;    /    17-4:为占位符提供数据    DECLARE      dml_stat VARCHAR2(100):='INSERT INTO emp '||       '(empno,ename,sal) VALUES(:no,:name,:sal)';    BEGIN      EXECUTE IMMEDIATE dml_stat USING &1,'&2',&3;    END;    /    17-5:属入雇员号删除特定雇员，并输出该雇员的姓名    DECLARE      dml_stat VARCHAR2(100):='DELETE emp WHERE '        ||'empno=&eno RETURNING ename INTO :name';      v_name emp.ename%TYPE;    BEGIN      EXECUTE IMMEDIATE dml_stat RETURNING INTO v_name;      dbms_output.put_line(v_name||'被开除');    END;    /    17-6:输入雇员号和工资，并更新和输出雇员工资    DECLARE      dml_stat VARCHAR2(100):='UPDATE emp SET sal=:salary '        ||'WHERE empno=:eno RETURNING sal INTO :new_sal';      v_sal emp.sal%TYPE;    BEGIN      EXECUTE IMMEDIATE dml_stat      USING &sal,&eno RETURNING INTO v_sal;      dbms_output.put_line('新工资:'||v_sal);    END;    /    17-7:处理单行查询，不包含占位符    DECLARE      query_stat VARCHAR2(100):='SELECT * FROM emp'       ||' WHERE LOWER(ename)=LOWER(''&name'')';      emp_record emp%ROWTYPE;    BEGIN      EXECUTE IMMEDIATE query_stat INTO emp_record;      dbms_output.put_line('岗位:'||emp_record.job||       ',工资：'||emp_record.sal);    END;    /    17-8:处理单行查询，包含占位符    DECLARE      query_stat VARCHAR2(100):='SELECT * FROM emp WHERE empno=:eno';      emp_record emp%ROWTYPE;    BEGIN      EXECUTE IMMEDIATE query_stat INTO emp_record USING &eno;      dbms_output.put_line('姓名:'||emp_record.ename||',岗位：'||emp_record.job);    END;    /    17-9:处理多行查询    DECLARE      TYPE empcurtyp IS REF CURSOR;      emp_cv empcurtyp;      emp_record emp%ROWTYPE;      sql_stat VARCHAR2(100);    BEGIN      sql_stat:='SELECT * FROM emp WHERE deptno=:dno';      OPEN emp_cv FOR sql_stat USING &dno;      LOOP        FETCH emp_cv INTO emp_record;        EXIT WHEN emp_cv%NOTFOUND;        dbms_output.put_line('雇员名:'||emp_record.ename         ||',工资:'||emp_record.sal);      END LOOP;      CLOSE emp_cv;    END;    /    17-10:在DML上使用批量绑定    DECLARE      TYPE ename_table_type IS VARRAY(3) OF emp.ename%TYPE;      ename_table ename_table_type       :=ename_table_type('&name1','&name2','&name3');      sql_stat VARCHAR2(100):='UPDATE emp SET sal=sal*1.1 '       ||'WHERE LOWER(ename)=LOWER(:1)';    BEGIN      FORALL i IN 1..ename_table.COUNT        EXECUTE IMMEDIATE sql_stat USING ename_table(i);    END;    /    17-11:在DML返回子句上只用批量绑定    DECLARE      TYPE ename_table_type IS TABLE OF emp.ename%TYPE;      TYPE sal_table_type IS TABLE OF emp.sal%TYPE;      ename_table ename_table_type;      sal_table sal_table_type;      sql_stat VARCHAR2(100):='UPDATE emp SET sal=sal+200 '        ||'WHERE deptno=:dno '        ||'RETURNING ename,sal INTO :1,:2';    BEGIN      EXECUTE IMMEDIATE sql_stat USING &dno        RETURNING BULK COLLECT INTO ename_table,sal_table;      FOR i IN 1..ename_table.COUNT LOOP        dbms_output.put_line('姓名:'||ename_table(i)||',新工资:'||sal_table(i));      END LOOP;    END;    /    17-12:使用批量绑定处理多行查询    DECLARE      TYPE ename_table_type IS VARRAY(100) OF emp.ename%TYPE;      TYPE hiredate_table_type IS VARRAY(100) OF emp.hiredate%TYPE;      ename_table ename_table_type;      hiredate_table hiredate_table_type;      sql_stat VARCHAR2(100):='SELECT ename,hiredate FROM emp '        ||'WHERE deptno=:dno';    BEGIN      EXECUTE IMMEDIATE sql_stat        BULK COLLECT INTO ename_table,hiredate_table USING &dno;      FOR i IN 1..ename_table.COUNT LOOP        dbms_output.put_line('姓名:'||ename_table(i)||          ',工作日期:'||hiredate_table(i));      END LOOP;    END;    /    17-13:在FETCH语句中使用批量提取    DECLARE      TYPE refcur IS REF CURSOR;      emp_cv refcur;      TYPE ename_table_type IS TABLE OF emp.ename%TYPE;      TYPE job_table_type IS TABLE OF emp.job%TYPE;      ename_table ename_table_type;      job_table job_table_type;      sql_stat VARCHAR2(100):='SELECT ename,job FROM emp '        ||'WHERE deptno=:dno';    BEGIN      OPEN emp_cv FOR sql_stat USING &dno;      FETCH emp_cv BULK COLLECT INTO ename_table,job_table;      CLOSE emp_cv;      FOR i IN 1..ename_table.COUNT LOOP        dbms_output.put_line('姓名:'||ename_table(i)||',岗位:'||job_table(i));      END LOOP;    END;    /