--====================================================

--======================================= 二.pl/sql 编程方面
--======================================================

1.自治事务:8i以上版本，不影响主事务。
在存储过程的is\as
后面声明PRAGMA AUTONOMOUS_TRANSACTION;
自治事务防止嵌套提交，使事务在自己的事务区内提交或回滚不会影响其他的事务。

2、包
   包说明(package specification)，包头,存放关于包的内容的信息，定义包的用户可见的过程、
   函数，数据类型和变量
     create or replace package  tt_aa  as
        v1  varchar2(10);
        v2  varchar2(10);
        v3  number;
        v4  boolean;
        procedure proc1(x  number);
        procedure proc2(y varchar2);
        procedure proc3(z  number);
        function my_add(x number,y number) return number;
      end;
   包主体（package body)是可选的
     create or replace package  body  tt_aa as
       procedure proc1(x number) as
         begin
           v1:=to_char(x);
         end;
       procedure proc2(y varchar2) as
         begin
           v2:=y;
         end;
       procedure proc3(z number) as
         begin
           v1:=z;
         end;
       procedure proc4(x number,y number) return number as
         begin
           return x+y;
         end;
     end;
    
   调用
     begin 
         tt_aa.proc1(6);
         dbms_output.put_line(to_char(tt_aa.my_add(1,3));
     end;

3、动态sql(使用dbms_sql)
    create or replace procedure my_execute(sql_string in varchar2) as
      v_cursor  number;
      v_numrows  interger;
    begin
       v_cursor:=dbms_sql.open_cursor;
       dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7);
       v_numrows:=dbms_sql.execute(v_cursor);
       dbms_sql.close_cursor(v_cursor);
    end;
    
    则可以
     sql>exec  my_execute(''''select * from tab'''');
     sql>exec  my_execute(''''insert into test value''''||''''(''''||''''''''''''ddd''''''''''''||'''')'''');
     sql>exec  my_execute(''''commit'''');
    
  对于查询方面的可以如下方式：
  比如想用游标查询一个表，但是这个表是分月的，每个月可能表名都会改变。
  create or replace procedure proc_test as
    v_curid  integer;
    v_result integer;
    v_strSql varchar2(255);
    v_userid okcai.userid%type;
    v_username okcai.username%type;
  begin
     v_strSql := ''''select * from okcai_''''||to_char(sysdate,''''yyyymm'''');
     v_curid := dbms_sql.open_cursor;
     dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7);
     dbms_sql.define_column(v_curid,1,v_userid);
     dbms_sql.define_column(v_curid,2,v_username,10);  --必须指定大小
     v_result := dbms_sql.execute(v_curid);
     loop
        if dbms_sql.fetch_rows(v_curid) = 0 then
            exit; --没有了 ，退出循环
        end if;
        dbms_sql.column_value(v_curid,1,v_userid);
        dbms_sql.column_value(v_curid,2,v_username);
        dbms_output.put_line(v_userid);
        dbms_output.put_line(v_username);
     end loop;
     dbms_sql.close(v_curid);
  end;

4、用EXECUTE IMMEDIATE
    <1>. 在PL/SQL运行DDL语句
    begin
      execute immediate ''''set role all'''';
    end;
    <2>. 给动态语句传值(USING 子句)
    declare
       l_depnam varchar2(20) := ''''testing'''';
       l_loc    varchar2(10) := ''''Dubai'''';
    begin
      execute immediate ''''insert into dept values  (:1, :2, :3)''''
              using 50, l_depnam, l_loc;
      commit;
    end;
   <3>. 从动态语句检索值(INTO子句)
   declare
      l_cnt    varchar2(20);
   begin
      execute immediate ''''select count(1) from emp''''
          into l_cnt;
      dbms_output.put_line(l_cnt);
  end;
   <4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
 declare
    l_routin   varchar2(100) := ''''gen2161.get_rowcnt'''';
  l_tblnam   varchar2(20) := ''''emp'''';
    l_cnt      number;
    l_status   varchar2(200);
 begin
    execute immediate ''''begin '''' || l_routin || ''''(:2, :3, :4); end;''''
       using in l_tblnam, out l_cnt, in out l_status;

    if l_status != ''''OK'''' then
        dbms_output.put_line(''''error'''');
    end if;
 end;
   <5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
  declare
    type empdtlrec is record (empno  number(4),
                           ename  varchar2(20),
                           deptno  number(2));
    empdtl empdtlrec;
  begin
    execute immediate ''''select empno, ename, deptno '''' ||
                   ''''from emp where empno = 7934''''
      into empdtl;
  end;
   <6>. 传递并检索值.INTO子句用在USING子句前
  declare
    l_dept    pls_integer := 20;
    l_nam     varchar2(20);
    l_loc     varchar2(20);
  begin
  execute immediate ''''select dname, loc from dept where deptno = :1''''
       into l_nam, l_loc
       using l_dept ;
  end;
   <7>. 多行查询选项.对此选项用insert语句填充临时表，用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
  declare
    l_sal   pls_integer := 2000;
  begin
    execute immediate ''''insert into temp(empno, ename) '''' ||
                   ''''          select empno, ename from emp '''' ||
                   ''''          where  sal > :1''''
      using l_sal;
    commit;
  end;
   <8>. 完成update的returning功能
       update可以用returning返回修改以后的值。比如:
  UPDATE employees
   SET job_id =’SA_MAN’, salary = salary + 1000, department_id = 140
   WHERE last_name = ’Jones’
    RETURNING salary*0.25, last_name, department_id
     INTO :bnd

[1] [2]  下一页