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】中关村软件园人才服务平台
HIBERNATE调用过程 (32)
HIBERNATE调用函数 (33)
数据库的SQL优化策略 (35)
SQL语句的优化策略 (35)
1
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】中关村软件园人才服务平台
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 pro1is可以改写为create or replace procedure pro1as
描述：[带异常处理的过程]
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为我们预定义了一系列的异常处理函数，供我们程序员来调用。

这些异常是预定义好的我们在使用的时候直接拿过来的用就可以，具体的异常如下图所示：
3
通过前面预热学习我们对Oracle的存储过程有所了解。

现在我们下面再详细的介绍一下Oracle存储过程当中的一些细节。

Oracle10g数据库存储过程【jiangzz】中关村软件园人才服务平台
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 VARCHAR2IN
我们在给变量赋值的时候一定得注意是:=赋值不再是=号赋值。

%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;
备注：
5
复合变量
复合变量一般分为记录（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】中关村软件园人才服务平台
描述：[如果没有书写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 in1..n.count loop
dbms_output.put_line(n(i));
end loop;
end;
备注：注意在声明变量n的时候就不能给变量初始化了，这点需要注意.
%type类型
%type类型是自适应类型，该类型会根据用户给定的表的字段给数据动态的修改类型
描述：[%type使用]
declare
v_name gm_%type;
begin
select name into v_name from gm_pcategory where id=20;
dbms_output.put_line(v_name);
end;
备注：
7
%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_);
end;
备注：
Oracle10g数据库存储过程【jiangzz】中关村软件园人才服务平台
循环控制语句
if过程控制语句
描述：[if语句语法要求]
if布尔表达式then
--代码
elsif布尔表达式then
--代码
else
--代码
end if;
备注：
描述：[写一个过程，输入成绩显示结果]
declare
begin
if v_in_score/10>9then
dbms_output.put_line('你是优秀的~');
elsif v_in_score/10>=8then
dbms_output.put_line('你不错的');
elsif v_in_score/10>=7then
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>0loop
v_num:=v_num-1;
dbms_output.put_line('显示数据'||v_num);
end loop;
end;
备注：这里面注意在存储过程我们不能修改v_in_score的值所以这里面需要定义一个变量。

9
for循环控制语句
描述：[for循环语法]
create or replace procedure pro1(v_num in number)is
begin
for i in1..v_num loop
if mod(i,2)=0then
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 in1..v_num loop
if mod(i,2)=0then
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 in1..v_num loop
if mod(i,2)=1then
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】中关村软件园人才服务平台
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=10then
goto lable;
end if;
dbms_output.put_line('----');
end loop;
<<lable>>
dbms_output.put_line('结束了');
end;
备注：但是注意<<lable>>不能放在end标签上
11
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>=9then
dbms_output.put_line('你是优秀的~');
when v_in_score/10>=8then
dbms_output.put_line('你不错的');
when v_in_score/10>=7then
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】中关村软件园人才服务平台
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);
END LOOP;
END;
备注：
13
存储过程-过程/函数
编写一个过程
描述：[书写过程的语法]
create or replace procedure过程名(参数名参数类型,参数名参数类型， (i)
--变量的声明
begin
--执行的sql脚本
end;
备注：
描述：[编写一个过程]
create or replace procedure pro is
type ddd is record(
id t_user.id%type,
name t_%type
);
aa ddd;
begin
select*into aa from t_user;
dbms_output.put_line(||aa.id);
end;
备注：为了可以输出必须设置set serveroutput on
语法
描述：[书写函数的语法]
create or replace function函数名(参数名参数类型,参数名参数类型，……)return参数类型is --变量的声明
begin
--执行的sql脚本
return返回值
end;
备注：
描述：[写一个函数求两个数的和]
create or replace function mysum(v_in_num1number,v_in_num2number)return number is
v_res number;
begin
v_res:=v_in_num1+v_in_num2;
return v_res;
end;
备注：
Oracle10g数据库存储过程【jiangzz】中关村软件园人才服务平台
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>10then
printsomthing('test is over10!!');
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=1then
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);
15
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】中关村软件园人才服务平台
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
17
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)进错误堆(ERROR STACK)还是覆盖(overwrite)错误堆(FALSE)。

缺省情况下是FALSE。

Oracle10g数据库存储过程【jiangzz】中关村软件园人才服务平台
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）中调用的话一定是存储函数或存储的封装函数不可以是存储过程，但调用存储函数的时候还有好多限制以及函数的纯度等级的问题，可以参考《ORACLE9I PL\SQL程序设计》如果是在过程化语句中调用的话，就要看你要实现什么样的功能。

函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等），所以虽然他们的语法上很相似但用户在使用他们的时候所需要完成的功能大部分情况下是不同的。

备注：Procedure cache：中保存的是执行计划，当编译好之后就执行procedure cache中的execution plan，之后Oracle会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。

保存在cache中的plan在下次执行时就不用再编译了。

19
存储过程包概念
类似一个容器，能打包相应的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 varchar2is
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】中关村软件园人才服务平台
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_);
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_%type
);
myexception1Exception;
pragma exception_init(myexception1,-20001);
myexception2Exception;
pragma exception_init(myexception2,-20002);
procedure p_sum(in_num1in number,in_num2in number,out_sum out number);
function f_sum(in_num1in number,in_num2in number)return number; end;
描述：[创建一个package的实现对常用函数、异常的管理、游标]
create or replace package body mypackge is
procedure p_sum(in_num1in number,in_num2in number,out_sum out
21
number)is
begin
out_sum:=in_num1+in_num2;
end p_sum;
function f_sum(in_num1in number,in_num2in 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_1values(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】中关村软件园人才服务平台
描述：[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_1values(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_num1in number,v_in_num2in 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;
23
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】中关村软件园人才服务平台
存储过程编写分页过程
描述：[存储过程的分页函数的封装]
CREATE OR REPLACE PROCEDURE PRO1(V_IN_TABLENAME IN VARCHAR2,V_IN_PAGENOW IN NUMBER,V_IN_PAGESIZE IN NUMBER,V_OUT_RESULT OUT
PACKAGE1.MY_CURSOR,V_OUT_ROWCOUNT OUT NUMBER,V_OUT_TOTALPAGE OUT NUMBER)IS
V_SQL VARCHAR2(2000);
V_START NUMBER:=(V_IN_PAGENOW-1)*V_IN_PAGESIZE+1;
V_END NUMBER:=V_IN_PAGESIZE*V_IN_PAGENOW;
BEGIN
SELECT COUNT(*)INTO V_OUT_ROWCOUNT FROM V_IN_TABLENAME;
IF MOD(V_OUT_ROWCOUNT,V_IN_PAGESIZE)=0THEN
V_OUT_TOTALPAGE:=V_OUT_ROWCOUNT/V_IN_PAGESIZE;
ELSE
V_OUT_TOTALPAGE:=V_OUT_ROWCOUNT/V_IN_PAGESIZE+1;
END IF;
V_SQL:='select*from(select t1.*,rownum rn from(select*from
'||V_IN_TABLENAME||')t1where rownum<='||V_END||')e2where
e2.rn>='||V_START;
OPEN V_OUT_RESULT FOR V_SQL;
END PRO1;
25
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;
用户正在修该数据
7rows updated
如果这里面大家想看到打印七条数据我们需要在begin前添加for each row
create or replace trigger mytrigger
Oracle10g数据库存储过程【jiangzz】中关村软件园人才服务平台
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
27
for each row
begin
dbms_output.put_line('数据已近备份~');
insert into temp_bak values(:old.id,:,: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.logtime);
dbms_output.put_line('-->修改数据以前是：
'||:new.id||:||: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<0THEN
raise_application_error(-20001,'不能克扣员工工资');
ELSIF(:NEW.SALARY-:OLD.SALARY)/:OLD.SALARY>0.5THEN
raise_application_error(-20001,'不能乱加薪');
ELSE
DBMS_OUTPUT.PUT_LINE('加薪成功');
END IF;
end;
备注：
Oracle10g数据库存储过程【jiangzz】中关村软件园人才服务平台
DDL触发器
描述：[DDL触发器]
CREATE OR REPLACE TRIGGER ddltrigger
BEFORE DDL
ON SCOTT.SCHEMA
DECLARE
oper varchar2(32);
BEGIN
SELECT ora_sysevent INTO oper FROM DUAL;
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');
END IF;
END ddltrigger;
备注：通常是数据库管理系统的一部分，用于定义数据库的所有特性和属性，尤其是行布局、列定义、键列（有时是选键方法）、文件位置和存储策略。

不需要dba权限
包括命令：DROP,CREATE,ALTER,GRANT,REVOKE,TRUNCATE
描述：[等价用于]
CREATE OR REPLACE TRIGGER ddltrigger
BEFORE DROP OR ALTER OR TRUNCATE OR GRANT OR REVOKE OR CREATE
ON SCOTT.SCHEMA
DECLARE
oper varchar2(32);
BEGIN
SELECT ora_sysevent INTO oper FROM DUAL;
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,'OTHER OPERATION LOGED');
29
END IF;
END ddltrigger;
备注：这样操作后用户就不能对scott下的表做任何改动了
Oracle10g数据库存储过程【jiangzz】中关村软件园人才服务平台
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
31。