ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转] pl/sql编程-002

[转] pl/sql编程-002

原创 Linux操作系统 作者:31597359 时间:2019-07-20 13:00:03 0 删除 编辑
pl/sql编程-002
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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8000572/viewspace-62282/,如需转载,请注明出处,否则将追究法律责任。

上一篇: [转] pl/sql编程-001
请登录后发表评论 登录
全部评论

注册时间:2006-10-07

  • 博文量
    120
  • 访问量
    78045