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

 

目录
相关文章
|
2月前
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(上)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
2月前
|
DataWorks Oracle 关系型数据库
DataWorks操作报错合集之尝试从Oracle数据库同步数据到TDSQL的PG版本,并遇到了与RAW字段相关的语法错误,该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
50 0
|
17天前
|
存储 机器学习/深度学习 SQL
SQLSERVER存储过程语法详解
SQLSERVER存储过程语法详解
28 0
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库——存储过程-介绍以及基本语法(特点、创建、调用、查看、删除、示例)
MySQL数据库——存储过程-介绍以及基本语法(特点、创建、调用、查看、删除、示例)
34 0
|
1月前
|
存储 SQL Oracle
oracle 存储过程导出sql语句 导出为文件
oracle 存储过程导出sql语句 导出为文件
112 0
|
2月前
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(下)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
2月前
|
存储 NoSQL Oracle
Oracle 12c的内存列存储:数据的“闪电侠”
【4月更文挑战第19天】Oracle 12c的内存列存储以超高速度革新数据处理,结合列存储与内存技术,实现快速查询与压缩。它支持向量化查询和并行处理,提升效率,但需合理配置以平衡系统资源。作为数据管理员,应善用此功能,适应业务需求和技术发展。
|
2月前
|
存储 SQL Oracle
Oracle存储过程与自定义函数的调用:异同与实战场景
【4月更文挑战第19天】Oracle的存储过程与自定义函数各有特色,存储过程用于封装复杂SQL操作,常在批量处理和数据维护中使用,通过CALL或EXECUTE调用;而自定义函数则用于简单计算和查询,返回单一值,可直接在SQL语句中调用。了解两者异同,如返回值方式、调用方式和应用场景,能提升数据库管理效率。实战场景包括:使用存储过程定期清理过期数据,用自定义函数在查询中动态计算字段值。
|
26天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
1月前
|
存储 SQL 关系型数据库
MySQL存储过程和存储函数的使用
MySQL的存储过程和存储函数在功能和用法上有明显的区别。存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,通过指定名称和参数(如果有)来调用执行,可以返回多个值或结果集,但不直接返回值。而存储函数则是一个有返回值的特殊存储过程,它返回一个值或表对象,可以直接嵌入SQL语句中使用,如SELECT语句中。两者都是为了提高SQL代码的重用性和性能,但使用场景和方式有所不同。
163 4