1, :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;
上一页 [1] [2] | 
