Oracle存储过程基本语法-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle存储过程基本语法

简介:   1.基本结构   CREATE OR REPLACE PROCEDURE 存储过程名字   (   参数1 IN NUMBER,   参数2 IN NUMBER   ) IS   变量1 INTEGER :=0;   变量2 DATE;   BEGIN   END 存储过程名字   2.SELECT INTO STATEMENT   将select查询的结果存入到

  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存储过程
  ——创建存储过程
  CREATE OR REPLACE PROCEDURE xxxxxxxxxxx_p
  (
  --参数IN表示输入参数,OUT表示输入参数,类型可以使用任意Oracle中的合法类型。
  is_ym IN CHAR
  )
  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 area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000usd_amt_sn
  FROM BGD_AREA_CM_M_BASE_T
  WHERE ym >= vs_ym_sn_beg
  AND ym <= vs_ym_sn_end
  GROUP BY area_code,CMCODE;
  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 xxxxxxxxxxx_T WHERE ym = is_ym;
  --然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数,其中用到一个系统变量SQL%rowcount
  DBMS_OUTPUT.put_line(’del上月记录=’||SQL%rowcount||’条’);
  INSERT INTO xxxxxxxxxxx_T(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 xxxxxxxxxxx_T
  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;

=========================================

几个对job执行时间设定的例子,对于oracle日志设定不熟悉,借鉴一下:

 

描述

INTERVAL参数值

每天午夜12点

'TRUNC(SYSDATE + 1)'

每天早上8点30分

'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)'

每星期二中午12点

'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'

每个月第一天的午夜12点

'TRUNC(LAST_DAY(SYSDATE ) + 1)'

每个季度最后一天的晚上11点

'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'

每星期六和日早上6点10分

'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10)/(24×60)'

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章