二.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);
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_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 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_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来完成动态游标的功能
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;