Oracle存储过程基本语法介绍

蔚落 2022-08-17 14:21 212阅读 0赞

Oracle存储过程基本语法 存储过程   
　　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'), -12),'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,CMCODE,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,1,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,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 = '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;

#### 源地址:[http://www.jb51.net/article/31805.htm][http_www.jb51.net_article_31805.htm] ####

[http_www.jb51.net_article_31805.htm]: http://www.jb51.net/article/31805.htm