oracle存储过程基本语法

参考资料：

http://www.cnblogs.com/hero4china/articles/base_rule_oracle_procedure.html

http://wen866595.iteye.com/blog/1733887

 

存储过程创建基本语法说明：

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;
      dbms_output.put_line('catch exception , rollback and exist'||SQLCODE||'-'||SQLERRM);
END 存储过程名;

 

存储过程常用语法说明：

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 t.* FROM USER_TABLE t WHERE t.age>10;--获取年龄大于10岁的用户

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||'条'); 

IF (x>0) THEN
  dbms_output.put_line('x大于0');
  dbms_output.put_line('x一定大于0');
ELSIF (x>1) THEN
  dbms_output.put_line('x大于1');
  dbms_output.put_line('x一定大于1');
END IF;

OPEN cur_1;--打开游标
FETCH cur_1 INTO cur_1_row;
  WHILE cur_1%FOUND LOOP --while循环
    INSERT INTO 表名(field1, field2, field3) values (cur_1_row.field1, cur_1_row.field2, cur_1_row.field3);
  END LOOP;
CLOSE cur_1;

FOR rec IN cur_1 LOOP --for循环
  UPDATE 表名 SET field1=rec.field1,field2=rec.field2 WHERE field3=rec.field3 AND field4=is_ym;
END LOOP;
COMMIT;

--错误处理部分。OTHERS 表示除了声明外的任意错误。 SQLERRM 是系统内置变量保存了当前错误的详细信息。
EXCEPTION
  WHEN OTHERS THEN 
  vs_msg := 'rollback and exist'||SQLCODE||'-'||SQLERRM);
  ROLLBACK;

  --把当前错误记录进日志表。
  INSERT INTO LOG_INFO(proc_name, error_info, op_date) VALUES('表名', vs_msg, SYSDATE); 
  COMMIT;
  RETURN;
END 存储过程名;

 

Oracle语法说明：

1.判断语句：IF (x>0) THEN ... ELSIF (x>1) THEN ... ELSE ... END IF;

 

2.case语句：

case
 when num=0 then dbms_output.put_line('zero');  
 when num=1 then dbms_output.put_line('one');  
 else dbms_output.put_line( 'default');  
end case;

 

2.For 循环：

for i in 0..9 loop
    dbms_output.put_line('i:'||i);
end loop;

 

3.While 循环：

while isok >= 0 loop
  isok := isok-1;
  if isok = 4 then
    exit;--跳出循环
  end if;
  dbms_output.put_line('isok:' || isok);
end loop;

 

4.数组

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

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

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

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

例子：

create or replace procedure test(y out array) as
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.游标 

游标的属性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT;

%FOUND:已检索到记录时，返回true

%NOTFOUNRD：检索不到记录时，返回true

%ISOPEN:游标已打开时返回true

%ROWCOUNT:代表检索的记录数，从1开始

 

注意事项：

1. 存储过程参数不带取值范围，in表示传入，out表示输出。类型可以使用任意Oracle中的合法类型
2. 变量带取值范围，后面接分号
3. 在判断语句前最好先用count(*)函数判断是否存在该条操作记录
4. 用select ... into ...给变量赋值
5. 在代码中抛异常用raise+异常名

Oracle存储过程实例：

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE(account_name in varchar2)
AS
  CURSOR c_datas is
    SELECT t1.account_id          as account_id,
       t1.account_no              as account_no,
       t1.account_name            as account_name,
       t1.account_money           as account_money
      FROM user_account t1
     WHERE t1.account=account_name;
  v_counter   number;
BEGIN

FOR c_data in c_datas LOOP

INSERT INTO user_account_temp(account_temp_id, account_no, account_name, account_money) 
values 
(seq_user_account_temp.NEXTVAL, c_data.account_no, c_data.account_name, c_data.account_money);

v_counter:=v_counter+1;

end LOOP;

  COMMIT;
  dbms_output.put_line(v_counter);
  EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        dbms_output.put_line('catch exception , rollback and exist'||SQLCODE||'---'||SQLERRM);
end TEST_PROCEDURE;