我的oracle笔记二(pl sql 编程方面).doc

|首页|电脑常识|程序设计|操作系统|语法|病毒安全|软件教程|硬件|数据库|多媒体|认证|下载|首页数据库 数据库综合技术用户登录 新用户注册 我的oracle笔记二（pl/sql 编程方面）我的oracle笔记二（pl/sql 编程方面）-=-= 二.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_c1(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; 则可以 sqlexec my_execute(select * from tab); sqlexec my_execute(insert into test value|(|ddd|); sqlexec 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 . 在PL/SQL运行DDL语句 begin execute immediate set role all; end; . 给动态语句传值(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; . 从动态语句检索值(INTO子句) declare l_cnt varchar2(20); begin execute immediate select count(1) from emp into l_cnt; dbms_output.put_line(l_cnt); end; . 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为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; . 将返回值传递到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; . 传递并检索值.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; . 多行查询选项.对此选项用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; . 完成update的returning功能 update可以用returning返回修改以后的值。比如:UPDATE employeesSET job_id =SA_MAN, salary = salary + 1000, department_id = 140WHERE last_name = JonesRETURNING salary*0.25, last_name, department_idINTO :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 :1returning 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、重新编译 对失效的过程 sqlexec dbms_pile_schema(schema); 如： sqlexec dbms_pile_schema(scott);7.存储过程使用table类型.字符串数组declaretype regionType is table of varchar2(3) index by binary_integer;v_listRegion regionType;inumber(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;.rowtype数组declaretype CmUserType is table of cm_user%rowtype index by binary_integer;v_listUser CmUserType;inumber(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;. 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;inumber(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 iselect object_name,status from user_objects where object_type=FUNCTION; SQLselect object_name,status from user_objects where object_type=PROCEDURE; 查看函数和过程的源代码 SQLset long 1000 SQLset pagesize 0SQLset trimspool on SQLselect 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; SQLwrap iname=a.sql; PL/SQL Wrapper: Release .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，错误信息最大2