批处理 动态sql

  1. DECLARE TYPE ref_cursor_type IS ref CURSOR; v_mycursor
    ref_cursor_type; TYPE id_list IS TABLE OF integer; TYPE name_list IS
    TABLE OF varchar2(30); v_tabid id_list:=id_list(); v_tabname
    name_list:=name_list(); sql_str varchar2(200); BEGIN
    –查询所以行,放在集合里 sql_str:=’select empno,ename from emp’;
    sql_str:=sql_str||’ order by empno desc’; execute immediate sql_str
    BULK COLLECT INTO v_tabid,v_tabname; FOR c IN
    v_tabid.first..v_tabid.last LOOP
    dbms_output.put_line(’empno为’||v_tabid(c)||’
    记录的NAME为’||v_tabname(c)); END LOOP;
    dbms_output.put_line(‘———————————‘);
    –更新(返回更新后的值) sql_str:=’update emp set
    empno=1+empno,ename=”a” where rownum=1 RETURNING empno,ename into
    :1,:2 ‘; execute immediate sql_str RETURNING BULK COLLECT INTO
    v_tabid, v_tabname; FOR c IN v_tabid.first..v_tabid.last LOOP
    dbms_output.put_line(’empno为’||v_tabid(c)||’
    记录的NAME为’||v_tabname(c)); END LOOP;
    dbms_output.put_line(‘———————————‘);
    –删除(返回被删除的行) sql_str:=’delete from emp where rownum=2
    RETURNING empno,ename into :1,:2 ‘; execute immediate sql_str RETURNING
    BULK COLLECT INTO v_tabid, v_tabname; FOR c IN
    v_tabid.first..v_tabid.last LOOP
    dbms_output.put_line(’empno为’||v_tabid(c)||’
    记录的NAME为’||v_tabname(c)); END LOOP;
    dbms_output.put_line(‘———————————‘);
    –插入(返回插入的行) sql_str:=’insert into emp(empno,ename)
    values(1,”abc”) RETURNING empno,ename into :1,:2 ‘; execute immediate
    sql_str RETURNING BULK COLLECT INTO v_tabid, v_tabname; FOR c IN
    v_tabid.first..v_tabid.last LOOP
    dbms_output.put_line(’empno为’||v_tabid(c)||’
    记录的NAME为’||v_tabname(c)); END LOOP;
    dbms_output.put_line(‘———————————‘); /* 批fetch
    语法: fetch dynamic_cursor bulk collect into
    define_variable[,define_variable…] */ sql_str:=’select
    empno,ename from emp’; sql_str:=sql_str||’ order by empno desc’; OPEN
    v_mycursor FOR sql_str; –取 FETCH v_mycursor BULK COLLECT INTO
    v_tabid,v_tabname; –关 CLOSE v_mycursor; –输 FOR c IN
    v_tabid.first..v_tabid.last LOOP
    dbms_output.put_line(’empno为’||v_tabid(c)||’
    记录的NAME为’||v_tabname(c)); END LOOP;
    dbms_output.put_line(‘———————————‘); END;
    2.------- forall DECLARE /*批forall
    语法:动态字符串必须为insert/update/delete,不能为select forall index in
    lower..upper execute immediate dynamic_string using bind
    |bind(index)[,bind |bind(index)…] [{returning|return} bulk collect
    into bind_argument[,bind_argument…]]; */ TYPE sal_list IS TABLE
    OF number(8,2); TYPE name_list IS TABLE OF varchar2(30); TYPE
    dept_list IS VARRAY(15) OF integer; v_depts
    dept_list:=dept_list(10,20,30,40,50,60,70,80); v_tabsal
    sal_list:=sal_list(); v_tabname name_list:=name_list(); sql_str
    varchar2(200); BEGIN sql_str:=’update emp set sal=sal*:arg1 where
    DEPTNO=:arg2′; sql_str:=sql_str||’ returning ename,sal into
    :arg3,:arg4′; –给前面4个部门加薪10%,并返回结果到集合. FORALL j IN
    1..4 execute immediate sql_str using 1.10,v_depts(j) RETURNING BULK
    COLLECT INTO v_tabname,v_tabsal; –显示结果 FOR j IN
    v_tabname.first..v_tabname.last LOOP
    dbms_output.put_line(‘雇员’||v_tabname(j) ||’
    的薪水被提到’||v_tabsal(j)); END LOOP;
    dbms_output.put_line(‘———————————‘);
    –给后面4个部门加薪20%,并返回结果到集合. FORALL j IN 5..8 execute
    immediate sql_str using 1.20,v_depts(j) RETURNING BULK COLLECT INTO
    v_tabname,v_tabsal; –显示结果(用notfound判断是否有结果集) IF
    SQL%NOTFOUND THEN dbms_output.put_line(‘无数据更新’); ELSE FOR j IN
    v_tabname.first..v_tabname.last LOOP
    dbms_output.put_line(‘雇员’||v_tabname(j) ||’
    的薪水被提到’||v_tabsal(j)); END LOOP; END IF; END;
    3.用一个值绑定绑定名称相同的值. 把sql语句用begin end括起来就能实现 如:
    execute immediate ‘begin calc_stats(:x,:x,:y,:x,:y); end;’ using a,b;
    将A与X绑定,当第二次出来不同名称时,与B绑定,以此类推

BULK COLLECT
子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT
INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK
COLLECT在这几种情形下的用法。
    有关FORALL语句的用法请参考:批量SQL之 FORALL
语句

DECLARE

[sql] view
plaincopyprint?

  FETCH cemp INTO pname;

五、BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK
COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK
COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK
COLLECTINTO 子句中。

/

四、FORALL与BULK COLLECT
综合运用
    FORALL与BULK
COLLECT是实现批量SQL的两个重要方式,我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。

DECLARE

 

END;

  1. –下面的示例中使用了BULK COLLECT将得到的结果集绑定到记录变量中        
  2. DECLARE  
  3.    TYPE emp_rec_type IS RECORD          –声明记录类型  
  4.    (  
  5.       empno      emp.empno%TYPE  
  6.      ,ename      emp.ename%TYPE  
  7.      ,hiredate   emp.hiredate%TYPE  
  8.    );  
  9.   
  10.    TYPE nested_emp_type IS TABLE OF emp_rec_type;  –声明记录类型变量   
  11.   
  12.    emp_tab   nested_emp_type;  
  13. BEGIN  
  14.    SELECT empno, ename, hiredate  
  15.    BULK   COLLECT INTO emp_tab       –使用BULK COLLECT 将所得的结果集一次性绑定到记录变量emp_tab中  
  16.    FROM   emp;  
  17.   
  18.    FOR i IN emp_tab.FIRST .. emp_tab.LAST  
  19.    LOOP  
  20.       DBMS_OUTPUT.put_line(‘Current record is ‘||emp_tab(i).empno||chr(9)||emp_tab(i).ename||chr(9)||emp_tab(i).hiredate);  
  21.    END LOOP;  
  22. END;  
  23. –上面的例子可以通过FOR 循环和普通的SELECT INTO来实现,那两者之间的差异呢?  
  24. –差异是FOR循环的SELECT INTO逐行提取并绑定到记录变量,而BULK COLLECT则一次即可提取所有行并绑定到记录变量。即谓批量绑定。  

BEGIN

二、使用LIMIT限制FETCH数据量
    在使用BULK COLLECT
子句时,对于集合类型,如嵌套表,联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULK 
COLLECT子句操作集合,则无需对集合进行初始化以及扩展。由于BULK
COLLECT的批量特性,如果数据量较大,而集合在此时又自动扩展,为避
免过大的数据集造成性能下降,因此使用limit子句来限制一次提取的数据量。limit子句只允许出现在fetch操作语句的批量中。

  FROM dual;

  1. –下面示例中从表emp中删除所有deptno=20的记录  
  2. DECLARE  
  3.    TYPE emp_rec_type IS RECORD  
  4.    (  
  5.       empno      emp.empno%TYPE  
  6.      ,ename      emp.ename%TYPE  
  7.      ,hiredate   emp.hiredate%TYPE  
  8.    );  
  9.   
  10.    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
  11.   
  12.    emp_tab   nested_emp_type;  
  13. —   v_limit   PLS_INTEGER := 3;  
  14. —   v_counter   PLS_INTEGER := 0;  
  15. BEGIN  
  16.    DELETE FROM emp  
  17.    WHERE  deptno = 20  
  18.    RETURNING empno, ename, hiredate     –>使用returning 返回这几个列  
  19.    BULK   COLLECT INTO emp_tab;         –>将前面返回的列的数据批量插入到集合变量    
  20.   
  21.    DBMS_OUTPUT.put_line( ‘Deleted ‘ || SQL%ROWCOUNT || ‘ rows.’ );  
  22.    COMMIT;  
  23.      
  24.    IF emp_tab.COUNT > 0 THEN                 –>当集合变量不为空时,输出所有被删除的元素  
  25.       FOR i IN emp_tab.FIRST .. emp_tab.LAST       
  26.       LOOP  
  27.          DBMS_OUTPUT.  
  28.           put_line(  
  29.                        ‘Current record  ‘  
  30.                     || emp_tab( i ).empno  
  31.                     || CHR( 9 )  
  32.                     || emp_tab( i ).ename  
  33.                     || CHR( 9 )  
  34.                     || emp_tab( i ).hiredate  
  35.                     || ‘ has been deleted’ );  
  36.       END LOOP;  
  37.    END IF;  
  38. END;  

    用法:
        FETCH … BULK COLLECT INTO … [LIMIT rows]

    area := pi*r*r;

  1. DROP TABLE tb_emp;  
  2.   
  3. CREATE TABLE tb_emp AS                        –>创建表tb_emp  
  4.    SELECT empno, ename, hiredate  
  5.    FROM   emp  
  6.    WHERE  1 = 2;  
  7.   
  8. DECLARE                                 
  9.    CURSOR emp_cur IS                          –>声明游标   
  10.       SELECT empno, ename, hiredate FROM emp;  
  11.   
  12.    TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;  –>基于游标的嵌套表类型  
  13.   
  14.    emp_tab   nested_emp_type;                         –>声明嵌套变量  
  15. BEGIN  
  16.    SELECT empno, ename, hiredate  
  17.    BULK   COLLECT INTO emp_tab                        –>BULK  COLLECT批量提取数据  
  18.    FROM   emp  
  19.    WHERE  sal > 1000;  
  20.   
  21.    FORALL i IN 1 .. emp_tab.COUNT                     –>使用FORALL语句将变量中的数据插入到表tb_emp  
  22.       INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)  
  23.       VALUES emp_tab( i );  
  24.   
  25.    COMMIT;  
  26.    DBMS_OUTPUT.put_line( ‘The total ‘ || emp_tab.COUNT || ‘ rows has been inserted to tb_emp’ );  
  27. END;  

  WHEN value_error THEN dbms_output.put_line(‘算数或转换错误’);

三、RETURNING 子句的批量绑定
    BULK
COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用。当与这几个DML语句结合时,我们
需要使用RETURNING子句来实现批量绑定。

–PL/SQL条件控制和循环控制

[sql] view
plaincopyprint?

 

[sql] view
plaincopyprint?

    WHEN ‘B’ THEN

  1. DECLARE  
  2.    CURSOR emp_cur IS  
  3.       SELECT empno, ename, hiredate FROM emp;  
  4.   
  5.    TYPE emp_rec_type IS RECORD  
  6.    (  
  7.       empno      emp.empno%TYPE  
  8.      ,ename      emp.ename%TYPE  
  9.      ,hiredate   emp.hiredate%TYPE  
  10.    );  
  11.   
  12.    TYPE nested_emp_type IS TABLE OF emp_rec_type;   –>定义了基于记录的嵌套表  
  13.   
  14.    emp_tab     nested_emp_type;           –>定义集合变量,此时未初始化  
  15.    v_limit     PLS_INTEGER := 5;          –>定义了一个变量来作为limit的值  
  16.    v_counter   PLS_INTEGER := 0;  
  17. BEGIN  
  18.    OPEN emp_cur;  
  19.   
  20.    LOOP  
  21.       FETCH emp_cur  
  22.       BULK   COLLECT INTO emp_tab         –>fetch时使用了BULK COLLECT子句  
  23.       LIMIT v_limit;                      –>使用limit子句限制提取数据量  
  24.   
  25.       EXIT WHEN emp_tab.COUNT = 0;        –>注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound  
  26.       v_counter   := v_counter + 1;       –>记录使用LIMIT之后fetch的次数  
  27.   
  28.       FOR i IN emp_tab.FIRST .. emp_tab.LAST  
  29.       LOOP  
  30.          DBMS_OUTPUT.put_line( ‘Current record is ‘||emp_tab(i).empno||CHR(9)||emp_tab(i).ename||CHR(9)||emp_tab(i).hiredate);  
  31.       END LOOP;  
  32.    END LOOP;  
  33.   
  34.    CLOSE emp_cur;  
  35.   
  36.    DBMS_OUTPUT.put_line( ‘The v_counter is ‘ || v_counter );  
  37. END;  

–(4)系统例外value_error 算数或类型转换错误

一、BULK
COLLECT批量绑定的示例

  WHEN OTHERS THEN dbms_output.put_line(‘其他例外’);

[sql] view
plaincopyprint?

BEGIN

  pnum NUMBER;

END;

  END LOOP;

end;

    UPDATE emp SET comm=1000 WHERE empno=7369;

–case[selector]

–循环结构

END;


  pnum := ‘asd’;

DECLARE

–%rowtype  表示引入数据库中的一行作为数据类型(实体对象)

  my_job := e_job;

BEGIN

  SELECT sal INTO newSal FROM emp WHERE empno=7369;

DECLARE

–属性数据类型

  FOR pnum IN 1..5 LOOP

  dbms_output.put_line(mydept.dname||’—-‘||mydept.loc);


      ‘Good’

–宿主常量

  IF cemp%NOTFOUND THEN

BEGIN

  dbms_output.put_line(’emp:’||e_job||’     ‘||’my:my_’||my_job||’
    ‘||’your:yout_’||your_job);

  WHEN zero_divide THEN dbms_output.put_line(‘0不能做除数’);

 

  mydept dept%ROWTYPE;

 v_grade CHAR(1):=UPPER(‘&p_grade’);   –&:输入

–(5)自定义例外 可以当成一个变量 可抛出

  CURSOR mycursor IS SELECT * FROM emp WHERE sal>1600;

  pnum := 1/0;

 newSal emp.sal%TYPE;

  END IF;

DECLARE

    dbms_output.put_line(sname);

DECLARE


END;

END;

      dbms_output.put_line(‘Excellent’);

 

/

 v_grade CHAR(1):=UPPER(‘&p_grade’);

  WHEN OTHERS THEN dbms_output.put_line(‘其他例外’);

  EXIT WHEN pnum > 10;

    WHEN ‘A’ THEN



  CURSOR cemp IS SELECT ename FROM emp WHERE deptno=500;

      dbms_output.put_line(‘Good’);

    dbms_output.put_line(temp_emp.empno||’—-‘||temp_emp.ename);

DECLARE

      ‘No such grade’

–异常(exception)

    dbms_output.put_line(area);

/

END;

  SELECT sal INTO newSal FROM emp WHERE empno=7369;

–创建存储过程例子:
CREATE OR REPLACE PROCEDURE proc1(
p_para1 VARCHAR2,
p_para2 OUT VARCHAR2,
p_para3 IN OUT VARCHAR2
)AS
v_name VARCHAR2(20);
BEGIN
v_name := ‘张三’;
p_para3 := v_name;
dbms_output.put_line(‘p_para3:’||p_para3);

  –打开游标

  pname emp.ename%TYPE;

    FETCH mycursor INTO temp_emp;

DECLARE

END;

END;

     RAISE no_emp_found;

  OPEN cemp;

–游标loop