Home >Database >Mysql Tutorial >Oracle存储过程语法

Oracle存储过程语法

PHP中文网
PHP中文网Original
2016-06-07 17:16:51994browse

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用: 现假设存在两张表,一张是学生成绩表(studnet) ,字段为:stdId


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;


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn