oracle存储过程讲解及实例

存储过程
1 CREATE OR REPLACE PROCEDURE 存储过 程名
2 IS 3 BEGIN 4 NULL; 5 END;
行 1: CREATE OR REPLACE PROCEDURE 是一个
SQL 语句通知 Oracle 数据库去创建一个叫做 skeleton 存 储过程, 如果存在就覆盖它; 行 2:
table of info ，如果不写的话使用数组时就需要进行初始 化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray; 5. 游标的使用 Oracle 中 Cursor 是非常有用的，用于 遍历临时表中的查询结果。其相关方法和属性也很多，现仅 就常用的用法做一二介绍：
--用输入参数给变量赋初值，用到了 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,'yyy ymm'), -12),'yyyymm'); vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyy ymm'), -12),'yyyymm');
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 =
--先删除表中特定条件的数据。
DELETE FROM 表名 WHERE ym = is_ym;
--然后用内置的 DBMS_OUTPUT 对象的 put_line 方法 打印出影响的记录行数，其中用到一个系统变量 SQL%rowcount
DBMS_OUTPUT.put_line('del 上月记录 ='||SQL%rowcount||'条');
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 方法来打开和赋值
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
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+异常名
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
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
begin while i < 10 LOOP begin i:= i + 1; end; end LOOP; end test; 4 、数组 首先明确一个概念：Oracle 中本是没有数组的概念的， 数组其实就是一张表(Table), 每个数组元素就是表中的一个 记录。 使用数组时，用户可以使用 Oracle 已经定义好的数组类 型，或可根据自己的需要定义数组类型。 (1) 使用 Oracle 自带的数组类型 x array; -- 使用时需要需要进行初始化 e.g:
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)
--终止月份
vs_ym_sn_beg CHAR(6);
--同期起始月份
vs_ym_sn_endห้องสมุดไป่ตู้CHAR(6);
--同期终止月份
--定义游标(简单的说就是一个可以遍历的结果集)
CURSOR cur_1 IS SELECT 。。。 FROM 。。。 WHERE 。。。 GROUP BY 。。。;
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
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 以预 先定义的游标，可作出参数进行传递
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(‘打印信息’);
d_amt_sn WHERE area_code = rec.area_code AND CMCODE = rec.CMCODE AND ym = is_ym;
END LOOP;
COMMIT;
--错误处理部分。OTHERS 表示除了声明外的任意错误。 SQLERRM 是系统内置变量保存了当前错误的详细信息。
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);
i := 1; -- 存储过程数组是起始位置是从 1 开始的，与 java 、C 、C++ 等语言不同。因为在 Oracle 中本是没有 数组的概念的，数组其实就是一张