Oracle存储过程总结(个人)

建立存储过程或函数

存储过程或函数是存储ORACLE数据库中的PL/SQL程序，可由用户直接或间接调用。使用存储过程和函数主要优越性是:

1、提高了效:在客户/服务器体系结构中，客户机应用向数据库服务器提出对SQL的需求。随着用户数量的增加，SQL 请求也就不断地增加，使网络很快就成为运行的瓶颈。使用存储过程可使运行性能得到显著的改进，因为对储过程的一个调用，即调用了在服务器中执行的多个SQL语句，从而减少了网络的拥挤。

2、可重用性:一个PL/SQL程序只需编写一次，即可用于各种地方.

3、可移植性：可在任何ORACLE数据库中使用存储过程，而不用考虑平台问题。

4、可维护性:一个存储过程用于完成一个特定的任务，如数据库触发器等需要调用该过程的地方均调用同一个存储过程，这样可降低软件维护的成本。

一、 存储过程

1、 建立存储过程的语法。

CREATE [OR REPLACE] PROCEDURE 程储过程名

[（参数1,……参加n）] IS

[局部变量声明部分]

BEGIN

可执行部分

[例外处理部分]

END;

说明：OR REPLACE 选项是当此存储过程存在时覆盖此程储过程。

参数部分和过程定义的语法相同。

例:定义一个存储过程用于删除students表中按学号指定的学生记录。

CREATE OR REPLACE PROCEDURE DELE

(STUID VARCHAR2) IS

BEGIN

DELETE FROM STUDENTS

WHERE STU_ID =STUID;

END;

2、 调用存储过程

方法: EXECUTE 存储过程名(参数1,参数n);

说明:参数1到参数n的类型与存储过程定义的类型必须一致，且参数的个数必须相同。

例:调用DELE存储过程删除学号为1的学生记录。

EXECUTE DELE(‘1’);

例:建立一个存储过程，在emp表中给按雇员号指定的人员增加工资,如果工资大于2000则增加50，否则如果工资大于1000则增加100，否则增加150。

CREATE OR REPLACE PROCEDURE ADDSAL

(EMPLOYNO EMP.EMPNO%TYPE) IS

INCREMENT NUMBER;

SALARY EMP.SAL%TYPE;

BEGIN

SELECT SAL INTO SALARY FROM EMP

WHERE EMPNO=EMPLOYNO;

IF SALARY>=2000 THEN

INCREMENT:=50;

ELSIF SALARY>=1000 THEN

INCREMENT:=100;

ELSE

INCREMENT:=150;

END IF;

UPDATE EMP

SET SAL=SAL+INCREMENT

WHERE EMPNO=EMPLOYNO;

END;

调用此存储过程,给雇员号为7369的记录增加相应的工资。

Execute addsal(‘7369’);

二、 存储函数

1、 语法

CREATE [OR REPLACE] FUNCTION 函数名

[参数1,……参数n]

RETURN 函数数据类型 IS

[ 局部变量说明]

BEGIN

可执行部分

[例外处理部分]

RETURN 函数的值

END;

说明:函数数据类型是函数返回值的数据类型；

函数的值是返回给调用程序的数值。

例：建立一个存储函数，统计指定部门的人数。

CREATE OR REPLACE FUNCTION COUNTNUM (DEPNO NUMBER) RETURN NUMBER IS

SUMA NUMBER;

BEGIN

SELECT COUNT(*) INTO SUMA FROM EMP WHERE DEPTNO=DEPNO

RETURN SUMA;

END;

调用此函数时注意不能把函数单独的写成一行。可写在PL/SQL赋值语句的右端。或写在SELECT语句中等等。

例：写一PL/SQL块统计10号部门和30号部门人数之和。

Declare

A number;

Begin

A:=countnum(10)+countnum(30);

Dbms_output.put_line(a);

End;

例：建立一个存储过程，将STUDENTS表中按学号指定的学生记录移至HISTORY表中。并在history表中增加移入日期 (删除STUDENTS表中的记录，同时录入到HISTORY表中)。 Create or replace procedure move( stuno varchar) is

Begin

/*将students中学号为stuno的记录插入到history表中*/

Insert into history(stu_id,name,sex,ldate)

select stu_id,name,sex ,sysdate from students

where stu_id=stuno;

/*将students中学号为stuno的记录删除*/

delete from students

where stu_id=stuno;

end;

调用此存储过程，将学号为1的记录移入历史表

execute move(‘1’);

例:编写一存储函数,将华氏温度转换成摄氏温度。

Create or replace function degf_to_degC(deg_f in number)

Return number is

Deg_c number;

Begin

Deg_c:=(5.0/9.0)*(deg_f-32);

Return deg_c;

End;

调用此函数，将PATIENT表中的body_temp以摄氏温度显示。

Select patient_id, degf_to_degc(body_temp) from patient;

在建立存储过程或存储函数时获取错误信息的方法。

SHOW ERROR

检索存储过程

可以从数据字典视图user_source中查得。

如查看当前用户模式下的存储过程和函数

select name,type from user_source;

查看一个具体的存储过程定义。例如查看move的定义可以用：

select text from user_source where name=’MOVE’;

***************************************************************************

1、创建存储过程

create or replace procedure test(var_name_1 in type,var_name_2 out type) is

--声明变量(变量名 变量类型)

begin

--存储过程的执行体

end test;

打印出输入的时间信息

E.g:

create or replace procedure test(workDate in Date) is

begin

dbms_output.putline('The input

is:'||to_date(workDate,'yyyy-mm-dd'));

end test;

2、变量赋值

变量名 := 值;

E.g：

create or replace procedure test(workDate in Date) is

x number(4,2);

begin

x := 1;

end test;

3、判断语句: date

if 比较式 then begin end; end if;

E.g

create or replace procedure test(x in number) is

begin

if x >0 then

begin

x := 0 - x;

end;

end if;

if x = 0 then

begin

x: = 1;

end;

end if;

end test;

4、For 循环

For ... in ... LOOP

--执行语句

end LOOP;

(1)循环遍历游标

create or replace procedure test() is

Cursor cursor is select name from student; name varchar(20);

begin

for name in cursor LOOP

begin

dbms_output.putline(name);

end;

end LOOP;

end test;

(2)循环遍历数组

create or replace procedure test(varArray in myPackage.TestArray) is

--(输入参数varArray 是自定义的数组类型，定义方式见标题6)

i number;

begin

i := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张

--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

for i in 1..varArray.count LOOP

dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));

end LOOP;

end test;

5、While 循环

while 条件语句 LOOP

begin

end;

end LOOP;

E.g

create or replace procedure test(i in number) is

begin

while i < 10 LOOP

begin

i:= i + 1;

end;

end LOOP;

end test;

6、数组

首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1)使用Oracle自带的数组类型

x array; --使用时需要进行初始化

e.g:

create or replace procedure test(y out array) is

x array;

begin

x := new array();

y := x;

end test;

(2)自定义的数组类型 (自定义数据类型时，建议通过创建Package的方式实现，以便于管理)

E.g (自定义使用参见标题4.2) create or replace package myPackage is

-- Public type declarations type info is record( name varchar(20), y number);

type TestArray is table of info index by binary_integer; --此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray 就是一张表，有两个字段，一个是

name，一个是y。需要注意的是此处使用了Index by binary_integer 编制该Table的索引项，也可以不写，直接写成：type TestArray is

table of info，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();

end TestArray;

7.游标的使用 Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor型游标(不能用于参数传递)

create or replace procedure test() is

cusor_1 Cursor is select std_name from student where ...; --Cursor的使用方式1 cursor_2 Cursor;

begin

select class_name into cursor_2 from class where ...; --Cursor的使用方式2

可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor的遍历

end test;

(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

create or replace procedure test(rsCursor out SYS_REFCURSOR) is

cursor SYS_REFCURSOR; name varhcar(20);

begin

OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只能通过OPEN方

法来打开和赋值

LOOP

fetch cursor into name --SYS_REFCURSOR只能通过fetch into来打开和遍历 exit when

cursor%NOTFOUND; --SYS_REFCURSOR中可使用三个状态属性： ---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息) ---%ROWCOUNT(然后当前游标所指向的行位置)

dbms_output.putline(name);

end LOOP;

rsCursor := cursor;

end test;

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

现假设存在两张表，一张是学生成绩表(studnet)，字段为：

stdId,math,article,language,music,sport,total,average,step 一张是学生课外

成绩表(out_school),字段为:stdId,parctice,comment

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得

的评价为A，就在总成绩上加20分。

create or replace procedure autocomputer(step in number) is

rsCursor SYS_REFCURSOR;

commentArray myPackage.myArray;

math number;

article number;

language number;

music number;

sport number;

total number;

average number;

stdId varchar(30);

record myPackage.stdInfo;

i number;

begin

i := 1;

get_comment(commentArray); --调用名为get_comment()的存储过程获取学生课外评分信息

OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;

LOOP

fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;

total := math + article + language + music + sport;

for i in 1..commentArray.count LOOP

record := commentArray(i);

if stdId = record.stdId then

begin

if record.comment = &apos;A&apos; then

begin

total := total + 20;

go to next; --使用go to跳出for循环

end;

end if;

end;

end if;

end LOOP;

<<continue>> average := total / 5;

update student t set t.total=total and t.average = average where t.stdId = stdId;

end LOOP;

end;

end autocomputer;

--取得学生评论信息的存储过程

create or replace procedure get_comment(commentArray out myPackage.myArray) is

rs SYS_REFCURSOR；

record myPackage.stdInfo;

stdId varchar(30);

comment varchar(1);

i number;

begin

open rs for select stdId,comment from out_school

i := 1;

LOOP

fetch rs into stdId,comment; exit when rs%NOTFOUND;

record.stdId := stdId;

record.comment := comment;

recommentArray(i) := record;

i:=i + 1;

end LOOP;

end get_comment;

--定义数组类型myArray

create or replace package myPackage is begin

type stdInfo is record(stdId varchar(30),comment varchar(1));

type myArray is table of stdInfo index by binary_integer;

end myPackage;

***********************************************************************************************************************************************

oracle 存储过程的基本语法 及注意事项

oracle 存储过程的基本语法

1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

(

参数1 IN NUMBER,

参数2 IN NUMBER

) IS

变量1 INTEGER :=0;

变量2 DATE;

BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子：

BEGIN

SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;

EXCEPTION

WHEN NO_DATA_FOUND THEN

xxxx;

END;

...

3.IF 判断

IF V_TEST=1 THEN

BEGIN

do something

END;

END IF;

4.while 循环

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END;

END LOOP;

5.变量赋值

V_TEST := 123;

6.用for in 使用cursor

...

IS

CURSOR cur IS SELECT * FROM xxx;

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM :=cur_result.列名1+cur_result.列名2

END;

END LOOP;

END;

7.带参数的cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

OPEN C_USER(变量值);

LOOP

FETCH C_USER INTO V_NAME;

EXIT FETCH C_USER%NOTFOUND;

do something

END LOOP;

CLOSE C_USER;

8.用pl/sql developer debug

连接数据库后建立一个Test WINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

关于oracle存储过程的若干问题备忘

1.在oracle中，数据表别名不能加is，如：

select a.appname from appinfo a;-- 正确

select a.appname from appinfo is a;-- 错误

也许，是怕和oracle中的存储过程中的关键字is冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译

select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation

Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确

运行

select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示

ORA-01422:exact fetch returns more than requested number of rows

5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

create table A(

id varchar2(50) primary key not null,

vcount number(8) not null,

bid varchar2(50) not null -- 外键

);如果在存储过程中，使用如下语句：

select sum(vcount) into fcount from A where bid='xxxxxx';如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下： if fcount is null then

fcount:=0;

end if;这样就一切ok了。

6.Hibernate调用oracle存储过程

this.pnumberManager.getHibernateTemplate().execute(

new HibernateCallback() ...{

public Object doInHibernate(Session session)

throws HibernateException, SQLException ...{

CallableStatement cs = session

.connection()

.prepareCall("{call modifyapppnumber_remain(?)}"); cs.setString(1, foundationid);

cs.execute();

return null;

}

});

相关推荐

专栏推荐