2010 Oracle回顾（续）
没说完，接着来。
create or replace procedure updateRe_SQL(id sys_report_file.fi_id%type) is i int; time1 varchar2(10); time2 varchar2(10); fl_id varchar2(4); s varchar2(4);begin i:=0; time1:=dbms_utility.get_time; for i in 1..500 loop select t.fi_id into fl_id from sys_report_file t where t.fi_id=id; dbms_output.put_line('执行成功'||i||'条,id='||fl_id); s:=i; end loop; time2:=dbms_utility.get_time; dbms_output.put_line('时间差：'||round((time2-time1)/100,2)); exception when no_data_found then dbms_output.put_line('没有数据，中断：'||i); s:=s+2; dbms_output.put_line('没有数据，中断：'||s); updateRe_SQL; when others then dbms_output.put_line('其它错误中断：'||i);end updateRe_SQL;
-- Created on 2010/12/20 by LW create or replace function exception_sele(num in number) return varchar2 is fl_id varchar2(20); i int;begin i := num; select t.FILENAME into fl_id from ktsys.sys_report_file t where t.fi_id = i; return fl_id;exception when no_data_found then return '没数据'; when others then return '有问题';end;
create or replace trigger ins before insert or update or delete on sys_report_file begin if to_char(sysdate,'DY') in ('星期三','星期四') then case when inserting then raise_application_error(-20001,'fail to insert'); when updating then raise_application_error(-20001,'fail to update'); when deleting then raise_application_error(-20001,'fail to delete'); end case; end if; end ins;
Create or replace trigger tr_audit_empAfter insert or update or delete on ktsys.sys_report_fileDeclare v_temp int;Begin Select count(*) into v_temp from ktsys.sys_report_file; If v_temp = 0 thenraise_application_error(-20001,'fail to insert'); End if; case when inserting then raise_application_error(-20001,'fail to insert'); when updating then raise_application_error(-20001,'fail to update'); when deleting then raise_application_error(-20001,'fail to delete'); end case; end tr_audit_emp;
create or replace and compile java source named mysource asimport java.util.Date;public class Hello{ public static void main(String []args){ System.out.println("Hello"); } public static Date getDate(String str){ System.out.println("Hello"+str); return new java.util.Date(); }}
create or replace function rand(str in string) return varchar2ASlanguage Java Name 'Hello.getDate(java.lang.String) return Date';
create or replace procedure TransMetaData(str in string) as --language java name 'Hello.main(java.lang.String[])'; --貌似只能一个， language java name 'Hello.getDate(java.lang.String)';
怎么调用，哥还是不说。
ok！基本该说的也说了。原来我肚子里存的数据库房门的知识就这么点，哎！继续武装自己。
