二.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,true|false); 其中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; |