Oracle数据库存储过程


目录 Oracle 存储过程快速入门.........................................................................................................3 概述.....................................................................................................................................3 Oracle 存储过程变量.................................................................................................................5 标量类型.............................................................................................................................5 %type 类型介绍................................................................................................................. 6 复合变量.............................................................................................................................6 数组类型.............................................................................................................................7 参照类型-游标................................................................................................................... 8 循环控制语句...........................................................................................................................10 if 过程控制语句............................................................................................................... 10 while 循环控制语句.........................................................................................................10 for 循环控制语句.............................................................................................................11 loop 循环.......................................................................................................................... 12 使用 Oracle10g 支持 goto 关键字.................................................................................. 12 case-when 控制块............................................................................................................13 存储过程-过程/函数................................................................................................................14 plsql 的编写和规范..........................................................................................................15 存储过程包概念...............................................................................................................15 存储过程编写分页过程...........................................................................................................17 存储过程中的异常...................................................................................................................17 异常总结:.......................................................................................................................19 Oracle 中的触发器................................................................................................................... 20 触发器简介.......................................................................................................................20 DML 触发器(insert/delete/update)............................................................................20 触发器中的谓语动词 inserting/updating/deleting 使用...............................................21 表级触发器的 NEW 和 OLD 关键字的使用:............................................................... 21 DDL 触发器.......................................................................................................................23 Oracle 中的系统触发器...................................................................................................25 Oracle 中的 JOB........................................................................................................................26 快速入门...........................................................................................................................26 删除 job.............................................................................................................................27 暂停 job.............................................................................................................................27 恢复 job.............................................................................................................................27 修改 job.............................................................................................................................28 修改 job 的执行间隔时间............................................................................................... 28 提交指定 id 的 job........................................................................................................... 28 运行 job.............................................................................................................................28 指定特定时间按运行 job................................................................................................ 28 Oracle 数据库的而备份...........................................................................................................30 JAVA 代码调用存储过程..........................................................................................................31 JAVA 代码调用过程..........................................................................................................31 JAVA 代码调用函数..........................................................................................................32 Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 1 HIBERNATE 调用过程....................................................................................................... 32 HIBERNATE 调用函数....................................................................................................... 33 数据库的 SQL 优化策略.......................................................................................................... 35 SQL 语句的优化策略....................................................................................................... 35 Oracle 存储过程快速入门 概述 在大型数据库系统中,存储过程和触发器具有很重要的作用。无论是存储过程还是触 发器,都是 SQL 语句和流程控制语句的集合。就本质而言,触发器也是一种存储过程。存 储过程在运算时生成执行方式,所以,以后对其再运行时其执行速度很快。 描述:[快语句的 HelloWorld] set serveroutput on; begin dbms_output.put_line('Hello world'); end; 备注:set serveroutput on; 此命令必须在向执行不然看不到控制台答应输出‘Hello world’ 备注:块语法要求是 declare /*定义部分—定义常量、变量、游标、例外*/ begin /*执行部分执行 pl/sql 语句和 sql 语句*/ [exception] /*例外处理部分处理运行是的各种错误*/ end; 描述:[数据库块语句] declare v_num number:=1; begin v_num:=v_num+1; dbms_output.put_line('show result~'||v_num); exception when no_data_found then dbms_output.put_line('没有数据~'); end; 定义部分是可以选择的该部分是可以选择的,执行部分是 begin 开始是必须的,例外部分是从 exception 开始的,该部分内容可以用户自行选择。 描述:[带参数的块的输出语句] declare v_name varchar2(128); begin Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 3 select ename into v_name from emp where empno=&empno; dbms_output.put_line('雇员的名字是'||v_name); end; 备注: 描述:[创建一个存储过程] declare v_name varchar2(128); begin select ename into v_name from emp where empno=&empno; dbms_output.put_line('雇员的名字是'||v_name); end; 备注:注意 create or replace procedure pro1 is 可以改写为 create or replace procedure pro1 as 描述:[带异常处理的过程] declare v_name varchar2(128); begin dbms_output.put_line('------------'); select ename into v_name from emp where empno='7369'; dbms_output.put_line('雇员的名字是'||v_name); exception when no_data_found then dbms_output.put_line('没有查询到记录'); end; 备注:Oracle 为我们预定义了一系列的异常处理函数,供我们程序员来调用。这些异常是预定义好的我们 在使用的时候直接拿过来的用就可以,具体的异常如下图所示: 通过前面预热学习我们对 Oracle 的存储过程有所了解。现在我们下面再详细的介绍一下 Oracle 存储过程 当中的一些细节。 Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 5 Oracle 存储过程变量 Oracle 存储过程中的变量大致分为三种类型 标量类型(scalar)、复合类型(composite)、参照类 型(reference)后续章节。 标量类型 所谓的标量类型就是我们常用的类型 描述:[描述] delacre v_name varchar2(128):='王忍'; v_birth date:=sysdate; v_sex boolean:=true; v_age integer:=26; v_balance float:=1.0; begin dbms_output.put_line('name:'||v_name||'birth:'||v_birth||'age'||v_age||'balance'||v_balance ); end; 备注:注意 dbms_output.put_line 不支持输出 boolean 类型的数据 SQL> desc dbms_output.put_line Parameter Type Mode Default? ----------------------------- A VARCHAR2 IN 我们在给变量赋值的时候一定得注意是:=赋值不再是=号赋值。 %type 类型介绍 在 PL/SQL 中可以将变量和常量声明为内建或用户定义的数据类型,以引用一个列名,同时继承他的 数据类型和大小。这种动态赋值方法是非常有用的,比如变量引用的列的数据类型和大小改变了,如果使 用了%TYPE,那么用户就不必修改代码,否则就必须修改代码。 描述:[输入员工编号打印出该员工的名字工作和薪水] delcare v_name emp.ename%type; v_job emp.job%type; v_sal emp.sal%type; begin select ename,job,sal into v_name,v_job,v_sal from emp where empno=v_in_empno; dbms_output.put_line(v_name||'-'||v_job||'-'||v_sal); end; 备注: 复合变量 复合变量一般分为记录(record)、表(table)。其实我们可以简单的将记录理解为高级语言编程 中的结构体。需要我们注意的是当引用记录成员的时候必须要加记录变量作为前缀(记录变量.记录成员)。 定义一个记录的语法如下所示 描述:[record 语法要求] type 自定义的 pl/sql 记录名字 is record( 变量名 变量类型, 变量名 变量类型 ); 备注: record 的一个案例 描述:[编写一个记录来存放 emp 的信息] declare type MyInfo is record( v_name emp.ename%type, v_job emp.job%type, v_sal emp.sal%type ); mydata MyInfo; begin select ename,job,sal into mydata from emp where empno=v_in_empno; dbms_output.put_line(mydata.v_name||'-'||mydata.v_job||'-'||mydata.v_sal); end; 备注: 数组类型 自定义数组类型建议自定义 package 便于后期的管理。 描述:[自定义一个数组] declare type sp is table of varchar2(32) index by binary_integer; stable sp; begin select ename into stable(1) from emp; end pro1; 备注: type numbers is table of number index by binary_integer;其作用是,加了”index by binary_integer ”后,numbers 类型的下标就是自增长,numbers 类型在插入元素时,不需要初始化,不 需要每次 extend 增加一个空间。 而如果没有这句话“index by binary_integer”,那就得要显示对初始化,且每插入一个元素到 numbers 类型的 table 中时,都需要先 extend. Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 7 描述:[如果没有书写 index by binary_integer] declare type mytable is table of number; v_table mytable:=mytable(); begin v_table.extend; v_table(1):=123; v_table.extend; v_table(2):=123; dbms_output.put_line('asas'); end; 备注: 描述:[书写 index by binary_integer] declare type numbers is table of number index by binary_integer; n numbers; begin n(1):= 2; n(2):= 3; for i in 1 .. n.count loop dbms_output.put_line(n(i)); end loop; end; 备注:注意在声明变量 n 的时候就不能给变量初始化了,这点需要注意. %type 类型 %type 类型是自适应类型,该类型会根据用户给定的表的字段给数据动态的修改类型 描述:[%type 使用] declare v_name gm_pcategory.name%type; begin select name into v_name from gm_pcategory where id=20; dbms_output.put_line(v_name); end; 备注: %rowtype 类型 如果每次接受多个列我们就需要定多个%type 类型的变量,因此为了解决重复性定义多个变 量可以使用%rowtype。 描述:[%rowtype 使用] declare v_row gm_pcategory%rowtype; begin select * into v_row from gm_pcategory where id=20; dbms_output.put_line(v_row.id||'--'||v_row.name); end; 备注: Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 9 循环控制语句 if 过程控制语句 描述:[if 语句语法要求] if 布尔表达式 then --代码 elsif 布尔表达式 then --代码 else --代码 end if; 备注: 描述:[写一个过程,输入成绩显示结果] declare begin if v_in_score/10>9 then dbms_output.put_line('你是优秀的~'); elsif v_in_score/10>=8 then dbms_output.put_line('你不错的'); elsif v_in_score/10>=7 then dbms_output.put_line('hi,小子你得加油了~'); else dbms_output.put_line('你不及格了'); end if; end; 备注:需注意这里面的 elsif 不可以写成 else if 这里面的条件控制一定要和 java 中加以区分 while 循环控制语句 描述:[while 循环的语法要求] declare v_num number:=90; begin while v_num>0 loop v_num:=v_num-1; dbms_output.put_line('显示数据'||v_num); end loop; end; 备注:这里面注意在存储过程我们不能修改 v_in_score 的值所以这里面需要定义一个变量。 for 循环控制语句 描述:[for 循环语法] create or replace procedure pro1(v_num in number) is begin for i in 1..v_num loop if mod(i,2)=0 then exit when i=8; end if; dbms_output.put_line(i); end loop; end; 备注:exit when 相当于 break 语句。同时对于循环语句也支持 continue 关键字(Oracle11g 支持)。 描述:[使用 for 循环打印 num 以内所有偶数的和并输出] create or replace procedure pro1(v_num in number) is v_res number:=0; begin for i in 1..v_num loop if mod(i,2)=0 then v_res:=v_res+i; end if; end loop; dbms_output.put_line('res is '|| v_res); end; 备注: 描述:[使用 for 循环打印 num 以内所有偶数的和并输出,使用 continue 关键字] create or replace procedure pro1(v_num in number) is v_res number:=0; begin for i in 1..v_num loop if mod(i,2)=1 then continue; end if; v_res:=v_res+i; end loop; dbms_output.put_line('res is '|| v_res); end; 备注:这里面的 continue 的用法和 java 中的作用一样,这里面不做过多的解释。注意 continue 在 10g 的 数据库版本中不支持,但是在 Oracle11g 以全面支持 continue,这里需要读者注意。 Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 11 loop 循环 描述:[存储过程中简单的死循环] create or replace procedure pro1(v_num in number) is v_res number:=0; begin loop dbms_output.put_line('----'); end loop; end; 备注:这样会出现死循环 描述:[推出循环一般使用 exit when 表达式] create or replace procedure pro1(v_num in number) is v_res number:=0; begin loop v_res:=v_res+1; exit when v_res=10; dbms_output.put_line('----'); end loop; end; 备注:exit 也可以直接使用 exit when 等价以 if 条件 then exit end if; 使用 Oracle10g 支持 goto 关键字 描述:[推 goto 推出循环] declare v_res number:=0; begin loop v_res:=v_res+1; if v_res=10 then goto lable; end if; dbms_output.put_line('----'); end loop; <> dbms_output.put_line('结束了'); end; 备注:但是注意<>不能放在 end 标签上 case-when 控制块 描述:[case-when 语句的练习] create or replace procedure pro1(v_in_score in number) is begin dbms_output.put_line(v_in_score/10); case when v_in_score/10>=9 then dbms_output.put_line('你是优秀的~'); when v_in_score/10>=8 then dbms_output.put_line('你不错的'); when v_in_score/10>=7 then dbms_output.put_line('hi,小子你得加油了~'); else dbms_output.put_line('你不及格了'); end case; end; 备注: 参照类型-游标类型 cursor 描述:[写一个游标的例子] declare type mycuror is ref cursor; v_cursor mycuror; v_row emp%rowtype; begin open v_cursor for select * from emp; loop exit when v_cursor%notfound; fetch v_cursor into v_row; dbms_output.put_line(v_row.ename||'----'||v_row.sal); end loop; end; 描述:[游标的第二种写法] declare cursor v_cursor is select * from emp; v_row emp%rowtype; begin open v_cursor; Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 13 loop exit when v_cursor%notfound; fetch v_cursor into v_row; dbms_output.put_line(v_row.ename||'----'||v_row.sal); end loop; end; 备注:这种写法建议大家了解就行,重点掌握第一种写法。 描述:[使用 for 循环遍历游标] declare cursor v_cursor is select * from emp; begin for x in v_cursor loop dbms_output.put_line(x.ename||'----'||x.sal); end loop; end; 备注: 描述:[使用 for 循环遍历游标简单写法] BEGIN FOR emp_record IN(SELECT*FROM emp) LOOP DBMS_OUTPUT.PUT_LINE(emp_record.ename||'----'||emp_record.sal); ENDLOOP; END; 备注: 存储过程-过程/函数 编写一个过程 描述:[书写过程的语法] create or replace procedure 过程名(参数名 参数类型,参数名 参数类型,……) is --变量的声明 begin --执行的sql脚本 end; 备注: 描述:[编写一个过程] create or replace procedure pro is type ddd is record( id t_user.id%type, name t_user.name%type ); aa ddd; begin select * into aa from t_user; dbms_output.put_line(aa.name||aa.id); end; 备注:为了可以输出 必须设置 set serveroutput on 语法 描述:[书写函数的语法] create or replace function 函数名(参数名 参数类型,参数名 参数类型,……) return 参数类型 is --变量的声明 begin --执行的sql脚本 return 返回值 end; 备注: 描述:[写一个函数求两个数的和] create or replace function mysum(v_in_num1 number,v_in_num2 number) return number is v_res number; begin v_res:=v_in_num1+v_in_num2; return v_res; end; 备注: Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 15 in/out/inout 参数 同时我们在声明参数的时候为了明确指出该参数是输入还是输出参数时候我们可以使用 In、out、inout 关键字。 描述:[in/out/inout] create or replace procedure printsomthing(print in number)is begin dbms_output.put_line(print); end; / create or replace procedure test_sp(test in number, outtest out number)is begin if test>10 then printsomthing ('test is over 10!!'); else begin outtest:=test; printsomthing (outtest); end; end if; end; / variable outtest number; exec test_sp(1,:outtest); 备注: 参数的调用(in 模式为按址调用,out / in out模式为按值调用。NOCOPY 强行转换成按址调用。 描述:[in/out inout 参数 测试 nocopy] create or replace procedure test_nocopy(p_in in number, p_out in out nocopy number) is begin p_out:=5; if p_in=1 then raise no_data_found; end if; end; / create or replace procedure run_nocopy is lv_test_num number; begin lv_test_num:=1; test_nocopy(1,lv_test_num); exception when others then dbms_output.put_line('error happened'|| lv_test_num); end; / 备注:nocopy 强制地址传递 存储过程中的异常 描述:[异常的定义] declare myexception EXCEPTION; pragma exception_init(myexception,-20001); begin dbms_output.put_line('--执行的sql脚本--'); raise myexception; exception when myexception then dbms_output.put_line('自定义异常出现了~~~'); when others then dbms_output.put_line('哎,没有捕获到该异常'); end; 备注:raise myexception;是手动跑出自己定义的异常信息,同时我们也可以这样抛出一个异常 描述:[使用 RAISE_APPLICATION_ERRORP 抛出异常] declare myexception EXCEPTION; pragma exception_init(myexception,-20001); begin dbms_output.put_line('--执行的sql脚本--'); raise_application_error('-20001','ssss'); exception when myexception then dbms_output.put_line('自定义异常出现了~~~'); when others then dbms_output.put_line('我草,没有捕获到该异常'); end; 备注: 描述:[使用异常管理事物控制] declare myexception EXCEPTION; pragma exception_init(myexception,-20001); Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 17 begin savepoint aa; insert into test values(7,'jiangzz',100); insert into test values(8,'蒋中亚',100); exception when myexception then dbms_output.put_line('自定义异常出现了~~~'); when others then dbms_output.put_line('er,没有捕获到该异常'); rollback to aa; end; 备注:savepoint aa;创建一个事物的回滚点如果出现未知异常就会回滚事物。 这里面建议大家建立一个自己的异常包以后有自己来使用这样就不会产生冲突。 描述:[建立一个异常包] create or replace package myexception is e_userregister EXCEPTION; pragma exception_init(e_userregister,-20001); e_userlogin EXCEPTION; pragma exception_init(e_userlogin,-20002); end; 备注:自定义异常信息方便后续调用。 描述:[使用自己建立的异常信息] declare begin savepoint aa; insert into test values(7,'jiangzz',100); insert into test values(8,'张晓玉',100); raise myexception.e_userregister; exception when myexception.e_userregister then dbms_output.put_line('自定义异常出现了~~~'); when others then dbms_output.put_line('哎,没有捕获到该异常'); rollback to aa; end; 备注: 描述:[如果自己不想写异常类我们也可以简单的使用现成的异常信息] declare myexception EXCEPTION; pragma exception_init(myexception,-20001); begin savepoint aa; raise TIMEOUT_ON_RESOURCE; exception when myexception then dbms_output.put_line('自定义异常出现了~~~'); when others then dbms_output.put_line('er,没有捕获到该异常'); rollback to aa; end; 备注: 异常总结: 异常的抛出有三种通过 pl/sql 运行时、使用 RAISE 关键字、调用 RAISE_APPLICATION_ERROR 存储过 程来实现异常的抛出。这里面需要注意的是 RAISE_APPLICATION_ERROR 内建函数用于抛出一个异常并给异 常赋予一个错误号以及错误信息。自定义异常的缺省错误号是+1,缺省信息是 User_Defined_Exception。 RAISE_APPLICATION_ERROR 函数能够在 pl/sql 程序块的执行部分和异常部分调用,显式抛出带特殊错误号 的命名异常。 Raise_application_error(error_number,message[,true,false]))。错误号的范围是 -20,000 到-20,999。错误信息是文本字符串,最多为 2048 字节。TRUE 和 FALSE 表示是添加(TRUE)进错误 堆(ERRORSTACK)还是覆盖(overwrite)错误堆(FALSE)。缺省情况下是 FALSE。 Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 19 plsql 的编写和规范 描述:[pl/sql 的编写规范] 注释 单行注释 -- 多行注释 /*注释的sql块*/ 标示符号和命名规范 当定义一个变量的时候建议使用v_作为前缀v_sal 当定义一个常量的时候建议使用c_作为前缀c_rate 当定义游标的时候建议使用_cursor作为后缀 当定义一个例外异常的时候建议e_作为前缀e_error 备注: 通过上述知识点的学习我们了解了存储过程,现在总结一下存储过程和函数的区别和联系。 描述:[procedure 和 function 的区别] 本质上没区别。只是函数有限制只能返回一个标量,而存储过程可以返回多个。并且函数是可以嵌入 在 SQL 中使用的,可以在 SELECT 等 SQL 语句中调用,而存储过程不行。执行的本质都一样。 1. 一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。 2. 对于存储过程来说可以返回参数,而函数只能返回值或者表对象。 3. 存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一个部分来调用,由于函数 可以返回一个表对象,因此它可以在查询语句中位于 FROM 关键字的后面。 4. 当存储过程和函数被执行的时候,SQL Manager 会到 procedure cache 中去取相应的查询语句,如果在 procedure cache 里没有相应的查询语句,SQL Manager 就会对存储过程和函数进行编译。 从参数的返回情况来看: 如果返回多个参数值最好使用存储过程,如果只有一个返回值的话可以使用函数; 从调用情况来看: 如果在 SQL 语句(DML 或 SELECT)中调用的话一定是存储函数或存储的封装函数不可以是存储过程,但调 用存储函数的时候还有好多限制以及函数的纯度等级的问题,可以参考《ORACLE 9I PL\SQL 程序设计》 如果是在过程化语句中调用的话,就要看你要实现什么样的功能。函数一般情况下是用来计算并返回 一个计算结果而存储过程一般是用来完成特定的数据操作(比如修改、插入数据库表或执行某些 DDL 语句 等等),所以虽然他们的语法上很相似但用户在使用他们的时候所需要完成的功能大部分情况下是不同的。 备注:Procedure cache:中保存的是执行计划,当编译好之后就执行 procedure cache 中的 execution plan, 之后 Oracle 会根据每个 execution plan 的实际情况来考虑是否要在 cache 中保存这个 plan,评判的标准 一个是这个 execution plan 可能被使用的频率;其次是生成这个 plan 的代价,也就是编译的耗时。保存 在 cache 中的 plan 在下次执行时就不用再编译了。 存储过程包概念 类似一个容器,能打包相应的 pl/sql 变量、常量、函数、过程、复合数据类型等元素到这个容器里 面。用来限制对内容的访问权限。 包的构成包通常有两部分构成 分别是包头和包体。包头里面通常写上述元素的声明,相当于应用程序的接 口;包体理通常写上述元素的实现,相当于应用程序接口的具体实现。注意包头和包体分别会作为数据对 象存在。 描述:[建立一个包的语法] create or replace package 包名 is type 自定义的pl/sql记录名字 is record( 变量名 变量类型, 变量名 变量类型 ); function 函数名(参数1,参数2,......) return 返回值类型; procedure 过程名(参数1,参数2,......) end; 备注: 描述:[简历一个 package 案例] create or replace package zpark is type MyData is record( v_name varchar2(32), v_sal number ); type MyTale is table of varchar2(32) index by binary_integer; function fun1(v_in_empno in number,v_out_name out varchar2) return varchar2; procedure pro1(v_in_empno in number); end; 备注: 描述:[package 的实现] create or replace package body zpark is v_mydata zpark.MyData; v_mytable zpark.MyTale; v_name emp.ename%type; function fun1(v_in_empno in number,v_out_name out varchar2) return varchar2 is begin select ename ,sal into v_mydata from emp where empno=v_in_empno; v_out_name:=v_mydata.v_name; return v_out_name; end; Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 21 procedure pro1(v_in_empno in number)is begin select ename into v_name from emp where empno=v_in_empno; dbms_output.put_line('用户名是'||v_name); end; end; 备注:实现的包和定义的包的名字一定保持一致,并且实现包必须实现包中声明的方法和过程。 描述:[使用过程写一个返回 cursor 的过程] create or replace procedure por1(v_out_res sys_refcursor) is type mycuror is ref cursor; v_cursor mycuror; v_row gm_pcategory%rowtype; begin open v_cursor for select * from Gm_Pcategory; loop exit when v_cursor%notfound; fetch v_cursor into v_row; dbms_output.put_line(v_row.id||'----'||v_row.name); end loop; end; 备注:cursor 不能作为参数传递,如果作为参数传递我们需要使用 sys_refcursor 描述:[创建一个 package 对常用函数、异常的管理、游标] create or replace package mypcakge is type mycursor is ref cursor; type myrecord is record( id gm_pcategory.id%type, name gm_pcategory.name%type ); myexception1 Exception; pragma exception_init(myexception1,-20001); myexception2 Exception; pragma exception_init(myexception2,-20002); procedure p_sum(in_num1 in number,in_num2 in number,out_sum out number); function f_sum(in_num1 in number,in_num2 in number)return number; end; 描述:[创建一个 package 的实现对常用函数、异常的管理、游标] create or replace package body mypackge is procedure p_sum(in_num1 in number,in_num2 in number,out_sum out number)is begin out_sum:=in_num1+in_num2; end p_sum; function f_sum(in_num1 in number,in_num2 in number)return number is begin return in_num1+in_num2; end; end; JAVA 代码调用存储过程 JAVA 代码调用过程 描述:[java 调用存储过程不带参数] create or replace procedure pro1(v_id number) is begin insert into temptable values(sysdate); end; Class.forName("oracle.jdbc.driver.OracleDriver"); String url="jdbc:oracle:thin:@localhost:1521:orcl"; Connection conn=DriverManager.getConnection(url,”scott”,"root"); CallableStatement cs=conn.prepareCall("{call pro1()}"); cs.execute(); 备注: 描述:[java 调用存储过程带参数] create or replace procedure pro1(v_id in number,v_name in varchar2,v_sex in varchar2) is begin insert into temp_1 values(v_id,v_name,v_sex); end; Class.forName("oracle.jdbc.driver.OracleDriver"); String url="jdbc:oracle:thin:@localhost:1521:orcl"; Connection conn=DriverManager.getConnection(,"system","root"); CallableStatement cs=conn.prepareCall("{call pro1(?,?,?)}"); cs.setInt(1, 2); cs.setString(2,"jiangzz"); cs.setString(3," boy"); cs.execute(); 备注: Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 23 描述:[java 调用存储过程带参数和带返回值] create or replace procedure pro1(v_id in number,v_name in varchar2,v_sex in varchar2,v_out_date out date) is begin insert into temp_1 values(v_id,v_name,v_sex); select sysdate into v_out_date from dual; end; Class.forName("oracle.jdbc.driver.OracleDriver"); String url="jdbc:oracle:thin:@localhost:1521:orcl"; Connection conn=DriverManager.getConnection(url,"system","root"); CallableStatement cs=conn.prepareCall("{call pro1(?,?,?,?)}"); cs.setInt(1,5); cs.setString(2, "jiangzz"); cs.setString(3, "boy"); cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DATE); cs.execute(); Date date=cs.getDate(4); System.out.println(date.toLocaleString()); 备注: JAVA 代码调用函数 描述:[java 调用函数带参数和带返回值] create or replace function fun(v_in_num1 in number,v_in_num2 in number) return number is v_sum number; begin v_sum:=v_in_num1+v_in_num2; return v_sum; end; / Class.forName("oracle.jdbc.driver.OracleDriver"); String url="jdbc:oracle:thin:@localhost:1521:orcl"; Connection conn=DriverManager.getConnection(url,"system","root"); PreparedStatement pstm=conn.prepareStatement("select fun(?,?) from dual"); pstm.setInt(1, 1); pstm.setInt(2, 2); ResultSet rs=pstm.executeQuery(); if(rs.next()){ System.out.println(rs.getInt(1)); } 备注:尝试用调用函数该函数返回值是 cursor create or replace function funTestCursor return mypackge.mycursor is out_cursor mypackge.mycursor; begin open out_cursor for select * from gm_pcategory; return out_cursor; end; Class.forName("oracle.jdbc.driver.OracleDriver"); String url="jdbc:oracle:thin:@localhost:1521:xe"; Connection conn=DriverManager.getConnection(url,"scott","root"); PreparedStatement pstm=conn.prepareStatement("select funTestCursor() from dual"); ResultSet rs=pstm.executeQuery(); if(rs.next()){ ResultSet rs1=(ResultSet) rs.getObject(1); while(rs1.next()){ System.out.println(rs1.getInt(1)+"--"+rs1.getString(2)); } } Java 调用过程 描述:[java 调用带参数的过程] create or replace procedure testCursor(out_cursor out mypackge.mycursor)is begin open out_cursor for select * from gm_pcategory; end; Class.forName("oracle.jdbc.driver.OracleDriver"); String url="jdbc:oracle:thin:@localhost:1521:xe"; Connection conn=DriverManager.getConnection(url,"scott","root"); CallableStatement cs=conn.prepareCall("{call testCursor(?)}"); cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR); cs.execute(); ResultSet rs=(ResultSet) cs.getObject(1); while(rs.next()){ System.out.print("id:"+rs.getInt(1)+"\t"); System.out.println("name:"+rs.getString(2)); } 备注: Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 25 存储过程编写分页过程 描述:[存储过程的分页函数的封装] CREATEORREPLACEPROCEDURE PRO1(V_IN_TABLENAME IN VARCHAR2,V_IN_PAGENOW INNUMBER,V_IN_PAGESIZEINNUMBER,V_OUT_RESULTOUT PACKAGE1.MY_CURSOR,V_OUT_ROWCOUNT OUTNUMBER,V_OUT_TOTALPAGEOUT NUMBER)IS V_SQL VARCHAR2(2000); V_STARTNUMBER:=(V_IN_PAGENOW-1)*V_IN_PAGESIZE+1; V_ENDNUMBER:=V_IN_PAGESIZE*V_IN_PAGENOW; BEGIN SELECTCOUNT(*)INTOV_OUT_ROWCOUNTFROMV_IN_TABLENAME; IFMOD(V_OUT_ROWCOUNT,V_IN_PAGESIZE)=0 THEN V_OUT_TOTALPAGE:=V_OUT_ROWCOUNT/V_IN_PAGESIZE; ELSE V_OUT_TOTALPAGE:=V_OUT_ROWCOUNT/V_IN_PAGESIZE+1; ENDIF; V_SQL:='select * from(select t1.*,rownum rn from(select * from '||V_IN_TABLENAME||') t1 where rownum<='||V_END||') e2 where e2.rn>='||V_START; OPENV_OUT_RESULTFORV_SQL; END PRO1; Oracle 中的触发器 触发器简介 触发器是一种特殊类型的存储过程,它不同于我们前面介绍过的存储过程。触发器主要 是通过事件进行触发而被执行的,而存储过程可以通过存储过程名字而被直接调用。当对某 一表进行诸如 Update、 Insert、 Delete 这些操作时 描述:[描述] 触发器类似于过程、函数,因为他们收拾拥有说明部分和语句执行部分以及异常处理部分,同包类似,触 发器必须存储在数据库中,并且不能被块进行本地化说明。但是对于过程而言,可以从另一个块中通过一 个过程显示的调用另外的一个过程,同时在调用的时候可以传递参数。所以对于触发器而言,当触发事件 发生的时候就会显示的执行该触发器,并且触发器不接收参数。 备注: 触发器的分类大致可以分为 dml 触发器、系统触发器、ddl 触发器。 DML 触发器(insert/delete/update) 描述:[触发器的语法] create or replace trigger 触发器名 {before|after} [update|delete|insert] on 表名 (for each row) begin --执行的sql语句块 end; 备注:{}表示必须有[]表示可以有其中的一个()表示有也可以没有也可以 描述:[在用户修改 test 表的时候打印一句话] create or replace trigger mytrigger before update on test begin dbms_output.put_line('用户正在修该数据'); end; 备注:该触发器就实现了在用户修改某一条记录的时候打印‘用户正在修改数据’,但是我们看到用户在 修改很多条记录的时候我们的控制台只打印了如下的数据: SQL> update test set id=id-2; 用户正在修该数据 7 rows updated 如果这里面大家想看到打印七条数据我们需要在 begin 前添加 for each row create or replace trigger mytrigger Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 27 before update on test for each row begin dbms_output.put_line('用户正在修该数据'); end; 描述:[在星期日和星期六的时候不允许修改 test 表] create or replace trigger mytrigger before insert or update or delete on scott.test begin if to_char(sysdate,'day') in ('星期日','星期六') then dbms_output.put_line('周末之间不能变更人员~'); RAISE_APPLICATION_ERROR(-20001,'对不起不能删除员工,不合法操作!'); end if; end; 备注: 触发器中的谓语动词 inserting/updating/deleting 使用 为了更加准确的描述用户对表的操作我们使用 oracle 为我们提供的谓语动词。 描述:[谓语动词的使用] create or replace trigger mytrigger before insert or update or delete on scott.temp begin case when inserting then RAISE_APPLICATION_ERROR(-20001,'sorry you cant insert'); when updating then RAISE_APPLICATION_ERROR(-20002,'sorry you cant update'); when deleting then RAISE_APPLICATION_ERROR(-20003,'sorry you cant delete'); else dbms_output.put_line('用户正常操作'); end case; end; 备注: 表级触发器的 NEW 和 OLD 关键字的使用: 描述:[谓语 old 动词的使用] create or replace trigger mytrigger before delete on scott.temp for each row begin dbms_output.put_line('数据已近备份~'); insert into temp_bak values(:old.id,:old.name,:old.logtime); end; 备注: 描述:[谓语 old/new 动词的使用] create or replace trigger mytrigger before update on scott.temp for each row begin dbms_output.put_line('数据已近备份~'); dbms_output.put_line('-->修改数据以前是: '||:old.id||:old.name||:old.logtime); dbms_output.put_line('-->修改数据以前是: '||:new.id||:new.name||:new.logtime); end; 备注: 描述:[谓语 old/new 薪水支付的案例] create or replace trigger mytrigger before update on scott.temp for each row begin DBMS_OUTPUT.put_line('-OLDSLARY-'||:OLD.SALARY||'NEWSLARY'||:NEW.SALA RY); IF:NEW.SALARY-:OLD.SALARY<0 THEN raise_application_error(-20001,'不能克扣员工工资'); ELSIF(:NEW.SALARY-:OLD.SALARY)/:OLD.SALARY>0.5 THEN raise_application_error(-20001,'不能乱加薪'); ELSE DBMS_OUTPUT.PUT_LINE('加薪成功'); ENDIF; end; 备注: Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 29 DDL 触发器 描述:[DDL 触发器] CREATEORREPLACETRIGGER ddltrigger BEFOREDDL ONSCOTT.SCHEMA DECLARE oper varchar2(32); BEGIN SELECT ora_sysevent INTO oper FROMDUAL; dbms_output.put_line(oper); IF oper = 'DROP'THEN RAISE_APPLICATION_ERROR(-20998,'Attempt To Drop In Production Has Been Logged'); ELSIF oper = 'TRUNCATE'THEN RAISE_APPLICATION_ERROR(-20999,'Attempt To Truncate A Production Table Has Been Logged'); ELSIF oper = 'ALTER'THEN RAISE_APPLICATION_ERROR(-20997,'Attempt To modify table has been loged'); ENDIF; END ddltrigger; 备注:通常是数据库管理系统的一部分,用于定义数据库的所有特性和属性,尤其是行布局、列定义、键 列(有时是选键方法)、文件位置和存储策略。 不需要 dba 权限 包括命令:DROP,CREATE,ALTER,GRANT,REVOKE,TRUNCATE 描述:[等价用于] CREATEORREPLACETRIGGER ddltrigger BEFOREDROPORALTERORTRUNCATEORGRANTORREVOKEORCREATE ONSCOTT.SCHEMA DECLARE oper varchar2(32); BEGIN SELECT ora_sysevent INTO oper FROMDUAL; dbms_output.put_line(oper); IF oper = 'DROP'THEN RAISE_APPLICATION_ERROR(-20998,'Attempt To Drop In Production Has Been Logged'); ELSIF oper = 'TRUNCATE'THEN RAISE_APPLICATION_ERROR(-20999,'Attempt To Truncate A Production Table Has Been Logged'); ELSIF oper = 'ALTER'THEN RAISE_APPLICATION_ERROR(-20997,'Attempt To modify table has been loged'); ELSE RAISE_APPLICATION_ERROR(-20096,'OTHEROPERATIONLOGED'); ENDIF; END ddltrigger; 备注:这样操作后用户就不能对 scott 下的表做任何改动了 Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 31 Oracle 中的系统触发器 描述:[系统触发器概述] 系统触发器是指基于 oracle 事件(例如 logon 和 startup)所建立的是触发器,通过使用系统事件触 发器,提供了跟踪系统或是数据库变化的机制。下面介绍一些常用的系统触发事件函数和建立各种事件触 发器的方法在建立系统触发器的时候,我们需要使用事件属性函数。 ora_client_ip_address//返回客户端的 ip ora_database_name//返回数据库名字 ora_login_user//返回登录的用户名 ora_sysevent//返回触发的系统事件名字 ora_des_encrypted_password//返回用户 des(MD5)加密的密码 备注: 使用系统触发器记录用户登录记录,记住在使用系统触发器的时候一定要求用户是 system 用户才有整改权 限。 描述:[系统触发语法] create or replace trigger 触发器名字 {after|before} [logon |logoff|startup|shutdown] on database begin end; 备注:注意不是任意匹配 after [logon|startup] before[logoff|shutdown] 描述:[用户登录触发器] create or replace trigger logontrigger after logon on database begin insert into user_log(username,databasename,address,logon_time,password,event) values(ora_database_name,ora_login_user,ora_client_ip_address,sysdate,ora_des_encrypted_ password,'用户logon'); end; 备注: create table user_log( username varchar2(32), databasename varchar2(32), address varchar2(128), event varchar2(32), logon_time date, password varchar2(32) ) 描述:[用户登录出去触发器] create or replace trigger logofftrigger before logoff on database begin insert into user_log(username,databasename,address,logon_time,password,event) values(ora_database_name,ora_login_user,ora_client_ip_address,sysdate,ora_des_encrypted_pas sword,'用户logoff'); end; 备注:常见的系统触发器有 ora_client_ip_address:用于返回客户端的 IP 地址 ora_database_name:用于返回当前数据库名 ora_des_encrypted_password:用于返回 DES 加密后的用户口令 ora_dict_obj_name:用于返回 DDL 操作所对应的数据库对象名 ora_dict_obj_name_list(name_list_ OUT ora_name_list_t):用于返回字事件中被修改的对象名列表 ora_dict_obj_owner:用于返回 DDL 操作所对应的对象的所有者名。 ora_dict_obj_ower_list(ower_list OUT ora_name_list_t):用于返回在事件中被修改对象的所有者列表 ora_dict_obj_type:用于返回 DDL 操作所对应的数据库对象的类型。 ora_grantee(user_list OUT ora_name_list_t):用于返回授权时事件授权者。 ora_instance_num:用于返回历程号。 ora_is_alter_column(column_name IN VARCHAR2):用于检测特定列是否被修改 ora_is_creating_nested_table:用于检测是否正在建立嵌套表 ora_is_drop_column(column_name IN VARCHAR2):用于检测特定列是否被删除 ora_is_servererror(error_number):用于检测是否返回了特定 Oracle 错误。 ora_login_user:用于返回登录用户名 ora_sysevent:用于返回触发触发器的系统时间名 Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 33 Oracle 中的 JOB 描述:[job 概述] 定时在后台执行相关操作: 1.如每天晚上 0 点将一张表的数据保存到另一张表中, 2:定时备份数据库等 备注:这样操作后用户就不能对 scott 下的表做任何改动了 快速入门 描述:[创建一个 job 实现数据每一秒钟添加一条记录] create or replace procedure pro1(v_id number) is begin insert into temptable values(sysdate); end; variable job2010 number ;–-声明一个变量保存job的编号 begin dbms_job.submit(:job2010,'pro1;--sysdate,'sysdate+1/(24*3600)'); end; / --启动该任务 begin dbms_job.run(:job2010); end; / 备注:查看当前下的 job 使用 SELECT*FROM uses_jobs a WHERE a.WHAT = 'pro1;'; 或者使用:select * from user_jobs 也可以查看 job 任务 删除 job 描述:[删除一个 job 任务] --移除job begin dbms_job.remove(:job2010);--:job可以用dba_jobs.job的值代替如:1198 end; / 备注: 暂停 job 描述:[暂停一个 job 任务] --暂停一个job begin dbms_job.broken(25,True); end; Commit; 备注: 恢复 job 描述:[恢复一个 job 任务] --暂停一个job begin dbms_job.broken(25,false,sysdate); end; commit; 备注:提交很重要 修改 job 描述:[修改一个 job 任务] --暂停一个job Begin dbms_job.change(25,'JOBPROCEDURE1;',sysdate,'sysdate+1/(24*60)'); end; 备注: 修改 job 的执行间隔时间 描述:[修改一个 job 任务间隔时间] --修改job的执行时间 Begin dbms_job.interval(job => 25,interval => 'sysdate+1/(24*3600)'); end; 备注:exec dbms_job.interval(job => 25,interval => 'sysdate+1/(24*3600)'); Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 35 提交指定 id 的 job 描述:[提交一个任务指定 jobid] begin dbms_job.isubmit(200,'JOBPROCEDURE;',sysdate,'sysdate+1/(24*60)'); end; / 备注:isubmit 该过程 submit 的区别在于 submit 是 oracle 自动分配 job 的 id 但是 isubmit 是程序员手 动设置 id 如果该 id 存在就会抛出异常。 运行 job 描述:[运行一个 job] begin dbms_job.run(200); end; / 备注:开启一个提交的 job 该 job 的 id 是 200 指定特定时间按运行 job 描述:[运行一个指定特定的时间] begin dbms_job.next_date(200, sysdate); end; 备注: 补充视图 ---视图的主要用途 1.简化操作 2.提高安全 2.满足不同用户的查询需求 视图不是一个真正的物理表,他是根据其他表动态的生成的数据。 create or replace view aaa as select [whith read only] Oracle 数据库的而备份 EXP 和 IMP 是 Oracle 提供的一种逻辑备份工具。逻辑备份创建数据库对象的 逻辑拷贝并存入一个二进制转储文件。这种逻辑备份需要在数据库启动的情况下 使 用, 其导出实质就是读取一个数据库记录集(甚 至 可 以 包 括 数 据 字 典)并 将 这 个 记 录 集 写 入 一 个 文 件,这些记录的导出与其物理位置无关,导入实质就是读取 转储文件并执行其中的命令。此备份方式是通过 Oracle 的 实 用 工 具 export 和 import 来 实 施 的, export 是把数据库中的数据导出,import 是 把 export 卸 出 的数据导入数据库中。通过此工具可以衍生出多种功能 , 比 如 整 个 数 据 库 的 备 份、表结构重建、数据的传输、用户的改变等等。 Oracle Export/Import 工具提供了灵活多样的功能和导出/导 入 模 式 , 最 常 使 用 的 三 种 模 式 是 User、 table 和 full database。除此以外,还可以在导出 / 导入时决定是否包含与对象有关的数据字典信息,如索引、约束、权限等等。 注 意:通过逻辑备份可以备份整个数据库, 或 仅 备 份 部 分 重 要 数 据。因 为 是 逻 辑 上 的 备 份 ,故只能用于逻辑的恢复数据 , 一 旦 数 据 库 被 物 理 损 坏 , 导 致 不 能 启 动,逻辑备份的数据不能帮助恢复数据库 描述:[数据库的备份] exp userid=用户名/密码@数据库实例名 tables=(表名 1,表名 2,……)file=备份的路径 备注:将数据的表结构备份数据到磁盘导出后数据就会备份到 D 盘的 bak.DMP 描述:[将 scott 用户的数据到 D:\bak.DMP] exp userid=用户名/密码@数据库实例名 tables=(表名 1,表名 2,……)file=备份的路径 rows=n 备注:rows=n 指的是不要数据 描述:[指导处表的结构不导出数据] exp userid=scott/root@orcl tables=(dept,emp)file=D:\bak 备注: 描述:[直接导出方式] exp userid=用户名/密码@数据库实例名 tables=(表名 1,表名 2,……)file=备份的路径 direct=y 备注:需要数据库的字符集和客户端字符集一致,否者会报错。 描述:[数据的额导入] imp sys/password file=*.dmp fromuser=olduser touser=newuser indexes=Y rows=Y 备注: Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 37 HIBERNATE 调用过程 描述:[java 调用函数带参数和带返回值] create or replace procedure pro1(v_id in number,v_name in varchar2,v_sex in varchar2,v_out_date out date) is begin insert into temp_1 values(v_id,v_name,v_sex); select sysdate into v_out_date from dual; end; / Configuration configuration=new Configuration().configure(); SessionFactory sessionFactory=configuration.buildSessionFactory(); Session session=sessionFactory.openSession(); session.doWork(new Work() { @Override public void execute(Connection connection) throws SQLException { //TODO Auto-generated method stub CallableStatement cs=connection.prepareCall("{call pro1(?,?,?,?)}"); cs.setInt(1,6); cs.setString(2, "jiangzz"); cs.setString(3, "boy"); cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DATE); cs.execute(); Date date=cs.getDate(4); System.out.println(date.toLocaleString()); } }); 备注: HIBERNATE 调用函数 描述:[java 调用函数带参数和带返回值] create or replace function fun(v_in_num1 in number,v_in_num2 in number) return number is v_sum number; begin v_sum:=v_in_num1+v_in_num2; return v_sum; end; / Configuration configuration=new Configuration().configure(); SessionFactory sessionFactory=configuration.buildSessionFactory(); Session session=sessionFactory.openSession(); session.doWork(new Work() { @Override public void execute(Connection connection) throws SQLException { //TODO Auto-generated method stub //oracle.jdbc.driver.T4CConnection--oracle.jdbc.driver.T4CConnection PreparedStatement pstm=connection.prepareStatement("select fun(?,?) from dual"); pstm.setInt(1, 1); pstm.setInt(2, 2); ResultSet rs=pstm.executeQuery(); if(rs.next()){ int a=rs.getInt(1); System.out.println(a); } } }); 备注: Oracle10g 数据库存储过程【jiangzz】中关村软件园人才服务平台 39 SQL 语句的优化策略 (1)选择最有效率的表名顺序 Oracle 的解析器按照从右到左的顺序处理 From 子句中的表名,因此写在 from 子句中最后的 表(基础表 driving table )将被最先处理,在 from 表包含多个表的情况下,你必须选择记录条 数最少的表作为基础表。 (2)where 子句的连接顺序 Oracle 采用的是自下而上的顺序解析 where 子句。根据这个原理,表之间连接必须写在其他 where 条件之前,那些可以过滤掉的最大数量记录条件必须写在 where 子句的末尾。 (3)select 子句中避免使用‘*’ Oracle 在解析的过程中,会将‘*’依次转换所有的列名,这个工作是通过查询数据字典完 成的,这意味着将消耗给等多的时间。 (4)减少访问数据库的次数 oracle 数据库内部执行的许多工作解析 sql 语句,估算索引利用率,绑定变量、读数据块等; (5)删除表中重复记录 delete from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no= e. emp_no); ORA-01033 shutdown immediate startup select group#,sequence#,archived,status from v$log; alter database clear unarchived logfile group 2; alter database open alter database datafile 3 offfline drop; alter databse open ---视图的主要用途 1.简化操作 2.提高安全 2.满足不同用户的查询需求 视图不是一个真正的物理表,他是根据其他表动态的生成的数据。 create or replace view aaa as select [whith read only]
还剩40页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

jielysong

贡献于2014-09-10

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf