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