我的oracle筆記二（pl/sql 編程方面）

二.pl/sql 編程方面

1.自治事務(wù):8i以上版本，不影響主事務(wù)。
在存儲(chǔ)過程的is\as
后面聲明PRAGMA AUTONOMOUS_TRANSACTION;
自治事務(wù)防止嵌套提交，使事務(wù)在自己的事務(wù)區(qū)內(nèi)提交或回滾不會(huì)影響其他的事務(wù)。

2、包
   包說明(package specification)，包頭,存放關(guān)于包的內(nèi)容的信息，定義包的用戶可見的過程、
   函數(shù)，數(shù)據(jù)類型和變量
     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;

   調(diào)用
     begin
         tt_aa.proc1(6);
         dbms_output.put_line(to_char(tt_aa.my_add(1,3));
     end;

3、動(dòng)態(tài)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');

對(duì)于查詢方面的可以如下方式：
比如想用游標(biāo)查詢一個(gè)表，但是這個(gè)表是分月的，每個(gè)月可能表名都會(huì)改變。
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; --沒有了，退出循環(huán)
        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運(yùn)行DDL語(yǔ)句
    begin
      execute immediate 'set role all';
    end;
    <2>. 給動(dòng)態(tài)語(yǔ)句傳值(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>. 從動(dòng)態(tài)語(yǔ)句檢索值(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>. 動(dòng)態(tài)調(diào)用例程.例程中用到的綁定變量參數(shù)必須指定參數(shù)類型.黓認(rèn)為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>. 多行查詢選項(xiàng).對(duì)此選項(xiàng)用insert語(yǔ)句填充臨時(shí)表，用臨時(shí)表進(jìn)行進(jìn)一步的處理,也可以用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 :bnd1, :bnd2, :bnd3;
       用execute immediate來完成的時(shí)候，可以用
  declare
  l_sal   pls_integer;
  begin
  execute immediate 'update employees SET salary = salary + 1000 where last_name=''okcai'' RETURNING INTO :1'
   returning into v_sql;
  commit;
  end;


5、用ref cursor來完成動(dòng)態(tài)游標(biāo)的功能
declare
type ct is ref cursor;
cc ct;
v_notify acc_woff_notify%rowtype;
begin
open cc for 'select * from acc_woff_notify';
loop
  fetch cc into v_notify;
  exit when cc%notfound;
  dbms_output.put_line(v_notify.done_code);
end loop;
close cc;
end;

6、重新編譯
    對(duì)失效的過程
    sql>exec dbms_utility.compile_schema(schema);
    如：
    sql>exec dbms_utility.compile_schema(scott);

7.存儲(chǔ)過程使用table類型
<1>.字符串?dāng)?shù)組
declare
type regionType is table of varchar2(3) index by binary_integer;
v_listRegion regionType;
i number(2):=0;
begin
v_listRegion(1):='571';
v_listRegion(2):='572';
v_listRegion(3):='573';
v_listRegion(4):='574';
v_listRegion(5):='575';
v_listRegion(6):='576';
v_listRegion(7):=null;
i := 1;
while i<= v_listRegion.last loop
dbms_output.put_line( v_listRegion(i) );
i := v_listRegion.next(i);
end loop;
end;

<2>.rowtype數(shù)組
declare
type CmUserType is table of cm_user%rowtype index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin

i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
  v_listUser(i):= r_user;
  i := i + 1;
end loop;

i := 1;
while i<= v_listUser.last loop
  dbms_output.put_line( v_listUser(i).bill_id );
  i := v_listUser.next(i);
end loop;
end;

<3>. record數(shù)組
declare
type recCmUserType is record (bill_id cm_user.bill_id%type,cust_name varchar2(25));
type CmUserType is table of recCmUserType index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin

i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
  v_listUser(i).bill_id:= r_user.bill_id;
  v_listUser(i).cust_name:= '客戶'||i;
  i := i + 1;
end loop;

i := 1;
while i<= v_listUser.last loop
  dbms_output.put_line( v_listUser(i).bill_id );
  dbms_output.put_line( v_listUser(i).cust_name );
  i := v_listUser.next(i);
end loop;
end;

8、存儲(chǔ)函數(shù)和過程

    查看函數(shù)和過程的狀態(tài)
    SQL>select object_name,status from user_objects where object_type='FUNCTION';
    SQL>select object_name,status from user_objects where object_type='PROCEDURE';

    查看函數(shù)和過程的源代碼
        SQL>set long 1000
        SQL>set pagesize 0
SQL>set trimspool on
    SQL>select text from all_source where owner=user and name=upper('&plsql_name');

9、觸發(fā)器

    查看觸發(fā)器

    set long 50000;
    set heading off;
    set pagesize 2000;

    select
    'create or replace trigger "' ||
            trigger_name || '"' || chr(10)||
     decode( substr( trigger_type, 1, 1 ),
             'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
                  chr(10) ||
     triggering_event || chr(10) ||
     'ON "' || table_owner || '"."' ||
           table_name || '"' || chr(10) ||
     decode( instr( trigger_type, 'EACH ROW' ), 0, null,
                 'FOR EACH ROW' ) || chr(10) ,
     trigger_body
    from user_triggers;

10. 加密ORACLE的存儲(chǔ)過程
用wrap命令,如:
    下列存儲(chǔ)過程內(nèi)容放在AA.SQL文件中
    create or replace procedure testCCB(i in number) as
    begin
    dbms_output.put_line('輸入?yún)?shù)是'||to_char(i));
    end;

    SQL>wrap iname=a.sql;
    PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
    Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
    Processing a.sql to a.plb
    提示a.sql轉(zhuǎn)換為a.plb，這就是加密了的腳本，執(zhí)行a.plb即可生成加密了的存儲(chǔ)過程
    運(yùn)行a.plb
    SQL> @a.plb ;

11.怎么樣利用游標(biāo)更新數(shù)據(jù)
cursor c1 is
select * from tablename
where name is null for update [of column]
...
update tablename set column = ...
where current of c1;

但是如果這種方式打開以后做了commit，則下次fetch會(huì)報(bào)ora-01002錯(cuò)誤

12.怎樣自定義異常
pragma_exception_init(exception_name,error_number);
如果立即拋出異常
raise_application_error(error_number,error_msg,true|false);
其中number從-20000到-20999，錯(cuò)誤信息最大2048B
異常變量
SQLCODE 錯(cuò)誤代碼
SQLERRM 錯(cuò)誤信息

13.在pl/sql中執(zhí)行DDL語(yǔ)句
<1>、8i以下版本dbms_sql包
<2>、8i以上版本還可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');

14.用java寫存儲(chǔ)過程包
<1>
create or replace and compile
java source
named "CHelloWorld" as
public class HelloWorld
{
public static String print()
{
return System.out.println("Hello,World");
}
};
/

<2>
create or replace function my_helloWorld return varchar2
as language java
name 'HelloWorld.print() return java.lang.String';
/

<3>
select my_helloWorld from dual;

乡下人产国偷v产偷v自拍,国产午夜片在线观看,婷婷成人亚洲综合国产麻豆,久久综合给合久久狠狠狠9

我的oracle筆記二（pl/sql 編程方面）