oracle存错过程的基本语法

发布时间：2020-12-12 12:43:17 所属栏目：百科来源：网络整理

导读：? 存储过程 1??CREATE?OR?REPLACE?PROCEDURE?存储过程名 2??IS 3??BEGIN 4??NULL; 5??END; ? 行1: CREATE?OR?REPLACE?PROCEDURE?是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程,?如果存在就覆盖它; 行2: IS关键词表明后面将跟随一个PL/SQL体

存储过程

　　1??CREATE?OR?REPLACE?PROCEDURE?存储过程名

　　2??IS

　　3??BEGIN

　　4??NULL;

　　5??END;

行1:

　　CREATE?OR?REPLACE?PROCEDURE?是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程,?如果存在就覆盖它;

行2:

　　IS关键词表明后面将跟随一个PL/SQL体。

行3:

　　BEGIN关键词表明PL/SQL体的开始。

行4:

　　NULL?PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;

行5:

　　END关键词表明PL/SQL体的结束

存储过程创建语法：

?create?or?replace?procedure?存储过程名（param1?in?type，param2?out?type）?

as?

变量1?类型（值范围）;?--vs_msg???VARCHAR2(4000);?

变量2?类型（值范围）;

Begin

Select?count(*)?into?变量1?from?表A?where列名=param1；

????If?(判断条件)?then

???????Select?列名?into?变量2?from?表A?where列名=param1；

???????Dbms_output。Put_line(‘打印信息’);

????Elsif?(判断条件)?then

???????Dbms_output。Put_line(‘打印信息’);

????Else

???????Raise?异常名（NO_DATA_FOUND）;

????End?if;

Exception

????When?others?then

???????Rollback;

End;

注意事项：

1，?存储过程参数不带取值范围，in表示传入，out表示输出

类型可以使用任意Oracle中的合法类型。

2，??变量带取值范围，后面接分号

3，??在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，??用select?。。。into。。。给变量赋值

5，??在代码中抛异常用?raise+异常名

CREATE?OR?REPLACE?PROCEDURE存储过程名
(

--定义参数
?is_ym??IN?CHAR(6)?,

the_count?OUT?NUMBER,
)?
AS?
--定义变量?
vs_msg???VARCHAR2(4000);???--错误信息变量
vs_ym_beg??CHAR(6);??????--起始月份
vs_ym_end??CHAR(6);??????--终止月份
vs_ym_sn_beg?CHAR(6);?????--同期起始月份
vs_ym_sn_end?CHAR(6);?????--同期终止月份

--定义游标(简单的说就是一个可以遍历的结果集)?

CURSOR?cur_1?IS?
??SELECT?。。。?
??FROM?。。。?
????WHERE?。。。
???GROUP?BY?。。。;?

BEGIN?

--用输入参数给变量赋初值，用到了Oralce的SUBSTR?TO_CHAR?ADD_MONTHS?

TO_DATE?等很常用的函数。?
vs_ym_beg?:=?SUBSTR(is_ym,1,6);?
vs_ym_end?:=?SUBSTR(is_ym,7,6);?
vs_ym_sn_beg?:=?TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,‘yyyymm‘),?-12),‘yyyymm‘);?
vs_ym_sn_end?:=?TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,‘yyyymm‘);?

--先删除表中特定条件的数据。?

DELETE?FROM?表名?WHERE?ym?=?is_ym;?

??--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount?

DBMS_OUTPUT.put_line(‘del上月记录=‘||SQL%rowcount||‘条‘);?

INSERT?INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt)?
SELECT?area_code,is_ym,SUM(rmb_amt)/10000,SUM(usd_amt)/10000?
FROM?BGD_AREA_CM_M_BASE_T?
??WHERE?ym?>=?vs_ym_beg?
??AND?ym?<=?vs_ym_end?
GROUP?BY?area_code,CMCODE;?

DBMS_OUTPUT.put_line(‘ins当月记录=‘||SQL%rowcount||‘条‘);?
--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。

?
FOR?rec?IN?cur_1?LOOP?
??UPDATE?表名
??SET?rmb_amt_sn?=?rec.rmb_amt_sn,usd_amt_sn?=?rec.usd_amt_sn?
???WHERE?area_code?=?rec.area_code?
???AND?CMCODE?=?rec.CMCODE?
???AND?ym?=?is_ym;?
END?LOOP;?

COMMIT;?

--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。?

EXCEPTION?

???WHEN?OTHERS?THEN?
??????vs_msg?:=?‘ERROR?IN?xxxxxxxxxxx_p(‘||is_ym||‘):‘||SUBSTR(SQLERRM,500);

?
???ROLLBACK;?

???--把当前错误记录进日志表。?

???INSERT?INTO?LOG_INFO(proc_name,error_info,op_date)?
???VALUES(‘xxxxxxxxxxx_p‘,vs_msg,SYSDATE);?
???COMMIT;?
???RETURN;?

END;

?

oracle存储过程语法

1?、判断语句:?

if?比较式?then?begin?end;?end?if;?

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;?

2?、For?循环?

For?...?in?...?LOOP?

--?执行语句?

end?LOOP;?

(1)?循环遍历游标?

create?or?replace?procedure?test()?as?

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)?as?

--(?输入参数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;?

3?、While?循环?

while?条件语句?LOOP?

begin?

end;?

end?LOOP;?

E.g?

create?or?replace?procedure?test(i?in?number)?as?

begin?

while?i?<?10?LOOP?

begin?????

?i:=?i?+?1;?

end;?

end?LOOP;?

?end?test;?

4?、数组?

首先明确一个概念：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?的方式实现，以便于管理)?

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;?

5.?游标的使用?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,sport?from?student?t?where?t.step?=?step;?

LOOP?

fetch?rsCursor?into?stdId,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?=?‘A‘?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;

eg2? 例二

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中，数据表别名不能加as，如：

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

select?a.appname?from?appinfo?as?a;--?错误 ?也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧 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;

????????????????????}

????????????????});

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!