二.pl/sql 編程方面 1.自治事務:8i以上版本，不影響主事務。在存儲過程的isas后面聲明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_routinvarchar2(100) := 'gen2161.get_rowcnt';l_tblnamvarchar2(20) := 'emp'; ;;l_cnt;;;number; ;;l_statusvarchar2(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); beginexecute immediate 'select dname, loc from dept where deptno = :1' ;;;into l_nam, l_loc ;;;using l_dept ;end; <7>. 多行查詢選項.對此選項用insert語句填充臨時表，用臨時表進行進一步的處理,也可以用REF cursors糾正此缺憾.declare ;;l_salpls_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 employeesSET job_id =’SA_MAN’, salary = salary + 1000, department_id = 140WHERE last_name = ’Jones’RETURNING salary*0.25, last_name, department_idINTO :bnd1, :bnd2, :bnd3; 用execute immediate來完成的時候，可以用declare ;;l_salpls_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來完成動態游標的功能declaretype ct is ref cursor;cc ct;v_notify acc_woff_notify%rowtype;beginopen cc for 'select * from acc_woff_notify';loopfetch cc into v_notify;exit when cc%notfound;dbms_output.put_line(v_notify.done_code);end loop;close cc;end 6、重新編譯 對失效的過程 sql>exec; dbms_utility.compile_schema(schema); 如： sql>exec; dbms_utility.compile_schema(scott);7.存儲過程使用table類型<1>.字符串數組declaretype regionType is table of varchar2(3) index by binary_integer;v_listRegion regionType;i;number(2):=0;beginv_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 loopdbms_output.put_line( v_listRegion(i) );i := v_listRegion.next(i);end loop;end;<2>.rowtype數組declaretype CmUserType is table of cm_user%rowtype index by binary_integer;v_listUser CmUserType;i;number(5):=0;r_user cm_user%rowtype;begini := 1;for r_user in (select * from cm_user where rownum<=5) loopv_listUser(i):= r_user;i := i + 1;end loop;i := 1;while i<= v_listUser.last loopdbms_output.put_line( v_listUser(i).bill_id );i := v_listUser.next(i);end loop;end;<3>. record數組declaretype 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;begini := 1;for r_user in (select * from cm_user where rownum<=5) loopv_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 loopdbms_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、存儲函數和過程 ;查看函數和過程的狀態 ;SQL>select object_name,status from user_objects where object_type='FUNCTION'; ;SQL>select object_name,status from user_objects where object_type='PROCEDURE'; ; ;查看函數和過程的源代碼 SQL>set long 1000 SQL>set pagesize 0SQL>set trimspool on ;SQL>select text from all_source where owner=user and name=upper('&plsql_name'); ;9、觸發器 ;查看觸發器;;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的存儲過程用wrap命令,如: 下列存儲過程內容放在AA.SQL文件中 create or replace procedure testCCB(i in number) as begin dbms_output.put_line('輸入參數是'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轉換為a.plb，這就是加密了的腳本，執行a.plb即可生成加密了的存儲過程 運行a.plb SQL> @a.plb ;11.怎么樣利用游標更新數據cursor c1 is select * from tablename where name is null for update [of column]...update tablename set column = ...where current of c1;但是假如這種方式打開以后做了commit，則下次fetch會報ora-01002錯誤12.怎樣自定義異常pragma_exception_init(exception_name,error_number);假如立即拋出異常raise_application_error(error_number,error_msg,truefalse);其中number從-20000到-20999，錯誤信息最大2048B異常變量SQLCODE 錯誤代碼SQLERRM 錯誤信息13.在pl/sql中執行DDL語句<1>、8i以下版本dbms_sql包<2>、8i以上版本還可以用execute immediate sql;dbms_utility.exec_ddl_statement('sql');14.用Java寫存儲過程包<1>create or replace and compile java source named 'CHelloWorld' aspublic class HelloWorld{ public static String print() { return System.out.println('Hello,World'); }};/<2>create or replace function my_helloWorld return varchar2as language javaname 'HelloWorld.print() return java.lang.String';/<3>select my_helloWorld from dual;