二.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 :bnd1, :bnd2, :bnd3;
用execute immediate來完成的時候，可以用
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來完成動態游標的功能
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、重新編譯
對失效的過程
sql>exec dbms_utility.compile_schema(schema);
如：
sql>exec dbms_utility.compile_schema(scott);7.存儲過程使用table類型
<1>.字符串數組
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數組
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數組
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、存儲函數和過程
查看函數和過程的狀態
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 0
SQL>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" 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;
