首页 > 数据库 >Oracle 存储过程学习总结

Oracle 存储过程学习总结

时间：2022-10-23 22:47:31浏览次数：53  
标签：总结 存储 END -- SP BEGIN num Oracle PROC

创建/更新存储过程

基础基础用法

创建/修改无参存储过程

CREATE OR REPLACE PROCEDURE procedure_name [IS|AS]
--声明全局变量(可选)
BEGIN
--存储过程的执行体
END; --也可以写成 END procedure_name;

创建/修改携参数存储过程

CREATE OR REPLACE PROCEDURE procedure_name(var_name1 IN type, var_name2 IN type,...,var_nameN OUT type) [IS|AS]
--声明全局变量(可选)
BEGIN
--存储过程的执行体
END; --也可以写成 END procedure_name;

说明：IN 表示输入参数，OUT表示输出参数，比如存储返回值的变量，IN OUT 表示输入输出参数（注：都不区分大小写）

注意：

  1. 存储过程参数数据类型不能指定长度
  2. OUT、IN OUT 模式参数的调用，必须通过变量实现

调用存储过程

--调用带参数存储过程
CALL procedure_name([参数列表]);
--或者
BEGIN procedure_name(参数列表); END; --注意 分号不能少，特别是END后面的分号
--或者
SQL> EXEC procedure_name(参数列表);  --在命令行窗口执行，比如SQLPlus执行窗口

--调用不带参数存储过程
CALL procedure_name();
--或者
BEGIN procedure_name; END; --注意 分号不能少，特别是END后面的分号
--或者
BEGIN procedure_name(); END; --注意 分号不能少，特别是END后面的分号
--或者
SQL> EXEC procedure_name;

简单的示例

创建携带参数存储过程

CREATE OR REPLACE PROCEDURE SP_TEST_PROC(workDate IN Date) is
BEGIN
dbms_output.put_line('The input date is:'||to_date(workDate,'yyyy-mm-dd'));
END;

CALL SP_TEST_PROC(sysdate); --输出：The input date is:22-AUG-24

--创建携带返回值存储过程
CREATE OR REPLACE PROCEDURE SP_SUM_PROC(number1 IN NUMBER, number2 IN NUMBER, result OUT NUMBER) is
BEGIN
result := number1 + number2;
END;

-- sql窗口中调用
DECLARE res NUMBER(6);
BEGIN 
  SP_SUM_PROC(1, 3, res); 
  dbms_output.put_line(res); --输出：4
END; 

-- 命令行窗口中调用
SQL> VARIABLE res NUMBER;
SQL> EXEC SP_SUM_PROC(1, 3, :res);
PL/SQL procedure successfully completed
res
---------
4

--创建带输入输出参数的存储过程
CREATE OR REPLACE PROCEDURE SP_SUM_PROC(number1 IN NUMBER, number2 IN OUT NUMBER) is
BEGIN
number2 := number1 + number2;
END;

-- 调用
DECLARE num NUMBER(6) :=3; --注意，不能在存储过程中声明变量时这样赋值
BEGIN 
  dbms_output.put_line('调用前num变量值：' || num); --输出：调用前num变量值：3
  SP_SUM_PROC(1, num); 
  dbms_output.put_line('调用后num变量值：' || num); --输出：调用后num变量值：4
END;

DECLARE基础用法说明

可以在BEGIN关键字之前，使用DECLARE定义、声明局部变量，声明基础用法如下：

DECLARE 变量名[,变量名2...] 数据类型(含长度、精度） [DEFAULT value]; --没有使用DEFAULT子句时，默认值为NULL

示例：

DECLARE num INT  DEFAULT 10; --声明变量 num，数据类型为INT型，默认值为10
DECLARE usrname VARCHAR2(15) DEFAULT 'tester'; --声明变量 username,默认值为tester
DECLARE age, num int； -- 定义多个变量
DECLARE length, width NUMBER(18,2) DEFAULT 10; -- 声明变量 length, width，默认值都为10
BEGIN
-- do something
END;

创建无参数存储过程

CREATE OR REPLACE PROCEDURE SP_TEST_PROC is
BEGIN
dbms_output.put_line('hello, tester');
END;

--调用
CALL SP_TEST_PROC(); --输出：hello, tester
--或者
BEGIN
SP_TEST_PROC;
END;

声明全局变量

方式一：直接声明数据类型

格式：变量名 数据类型(大小及精度)

示例：

v_username VARCHAR2(15);
v_num NUMBER(9,2);

方式二：使用%TYPE声明

格式：变量名 表名.字段名%TYPE

含义：该变量的数据类型与指定表的指定字段的数据类型一致

示例：

r_carrierID  CARRIERS.carrier_id%type;

方式三：使用%ROWTYPE声明

格式：变量名 表名%ROWTYPE

含义：该变量的数据类型与指定表的指定行记录（所有字段）的数据类型一致

示例：

V_row_user USERS%ROWTYPE;　--V_row_user存放整行数据

注：不管使用哪种声明方式，变量名都不区分大小写，以字母开头；此外，变量的声明必须在BEGIN关键字之前进行。

变量赋值

方式一：使用":="直接赋值

注意，这种方式不适合使用%ROWTYPE声明的变量

示例：

v_username := 'shouke';

我们可以在声明变量的同时对变量进行赋值。

v_username VARCHAR2(15) := 'shouke'

方式二：select 表字段 into 变量 from 表

1：查询指定表的某些指定字段　　

不适合使用%ROWTYPE声明的变量

SELECT field1,field2,...,field3 INTO varName1,varName2,...,varNameN FROM tableName;

2：查询指定表的所有字段　　

SELECT * INTO v_row_varName FROM tableName; --其中v_row_varName为使用%ROWTYPE声明的变量

注意：使用这种方式给使用%ROWTYPE声明的变量赋值时，查询结果只能返回一条记录，且查询结果必须包含该表的所有字段。

变量声明与赋值示例

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
v_username VARCHAR2(15);
v_companyCode NUMBER(9,2);
v_erpOrderNo EFFECTIVE_OMS_MSG_FOR_TEST.erp_Orderno%TYPE;
v_record EFFECTIVE_OMS_MSG_FOR_TEST%ROWTYPE;
BEGIN
  v_username := 'shouke';
  SELECT request_msg_id INTO v_companyCode FROM EFFECTIVE_MSG_FOR_TEST WHERE rownum <2;
  SELECT erp_orderno INTO v_erpOrderNo FROM EFFECTIVE_MSG_FOR_TEST WHERE rownum <2;
  SELECT * INTO v_record FROM EFFECTIVE_MSG_FOR_TEST WHERE rownum <2;

  dbms_output.put_line('v_username: ' || v_username);
  dbms_output.put_line('v_companyCode: ' || v_companyCode);
  dbms_output.put_line('v_erpOrderNo: ' || v_erpOrderNo);
  dbms_output.put_line('v_record.company_code: ' || v_record.company_code); 

END;

call SP_TEST_PROC();

注意：

  • 采用变量名.表字段名的方式来引用通过%ROWTYPE声明的变量
  • 不能直接使用变量名作为查询列，错误用法形如 SELECT v_erpOrderNo FROM EFFECTIVE_MSG_FOR_TEST;

一个应用实例

--创建序列
CREATE SEQUENCE check_orders_seq
increment By 1 
start With 1
Maxvalue 2000
Minvalue 1
cycle
Nocache;

--创建存储过程
CREATE OR REPLACE PROCEDURE "SP_GET_CHECK_ORDERS_FOR_TEST" (IN_serverID IN VARCHAR2, IN_rowLimit IN INT, OUT_returnCode OUT VARCHAR2)
IS
sqlStr VARCHAR2(5000);
BEGIN
  OUT_returnCode := '000';

  --将拣货完成未复核，且未在临时表check_orders_for_test的订单拉入临时表
  sqlStr := 'insert into check_orders_for_test(warehouseID, orderNo, rangeNo, checkFlag, serverID) select h.warehouseID, h.orderNo, check_orders_seq.nextval rangeNo, ''N'' checkFlag,'''||IN_serverID||''' serverID 
               from doc_order_header h 
               where h.sostatus = ''60'' 
               and exists (select 1 from act_allocation_details a 
                           where a.orderNo = h.orderNo
                           and a.packflag = ''N'') 
               and not exists (select 1 from check_orders_for_test m 
                               where m.orderno = h.orderno) 
               and rownum <= '||IN_rowLimit;
  execute immediate sqlStr;
  commit;
  Return;
EXCEPTION
  WHEN OTHERS THEN
    OUT_returnCode := 'SP_GET_CHECK_ORDERS_FOR_TEST' || SQLerrm; 
    dbms_output.put_line(OUT_returnCode);
    ROLLBACK;
END;

说明：两个''表示一个'

执行体之逻辑判断语句

IF语句

基础用法

IF 条件表达式 THEN
  -- do something
END IF;

IF 条件表达式 THEN
  -- do something
ELSE
  -- do something
END IF;

IF 条件表达式 THEN
  -- do something
ELSIF 条件表达式 THEN
  -- do something
... --表省略，支持更多的ELSIF
ELSE --子句可选
  -- do something
END IF;

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC(num IN NUMBER) IS
BEGIN
  --如果num为1，则输出true
  IF num=1 THEN
    BEGIN
      dbms_output.put_line('true');
    END;
  END IF;
END;

CALL SP_TEST_PROC(1);


CREATE OR REPLACE PROCEDURE SP_TEST_PROC(num IN NUMBER) IS
BEGIN
  IF NUM=1 THEN --如果num为1，则输出 true
    dbms_output.put_line('true');
  ELSE --否则输出 false
    dbms_output.put_line('false');
  END IF;
END;


CREATE OR REPLACE PROCEDURE SP_TEST_PROC(num IN NUMBER) IS
BEGIN
  IF NUM=1 THEN --如果num为1，则输出 true
    dbms_output.put_line('true');
  ELSIF NUM=0 THEN --否则，如果num为2，则输出 false
    dbms_output.put_line('false');
  ELSE --否则输出 invalid num
    dbms_output.put_line('invalid num');
  END IF;
END;

CASE WHEN语句

基础用法

CASE 
  WHEN num=1 THEN 
    --do something
  WHEN num=2 THEN 
    --do something
  ... --表省略，支持更多的WHEN
  ELSE 
    --do something
END CASE;

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC(num IN NUMBER) IS
BEGIN
  CASE 
    WHEN num=1 THEN  --如果num为1，则输出 true
      dbms_output.put_line('true');
    WHEN num=2 THEN  --如果num为1，则输出 false
      dbms_output.put_line('false');
    ELSE --否则输出 invalid num
      dbms_output.put_line('invalid num');
  END CASE;
END;

执行体之循环遍历语句

FOR循环

基础用法

FOR var IN range LOOP
--do something
END LOOP;

--遍历查询结果集
FOR row IN (查询语句) LOOP
--do something
END LOOP;

--循环遍历数组

--循环遍历游标（使用示例参见下文 执行体之游标）

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
BEGIN
  -- FOR i IN REVERSE 0..5 LOOP --REVERSE 采用逆序，从大到小，i取值从5到0
  FOR i IN 0..5 LOOP -- i取值从0到5
    dbms_output.put_line('运行第' || i || '次');
  END LOOP;
END;

CALL SP_TEST_PROC();

运行输出：

运行第0次
运行第1次
运行第2次
运行第3次
运行第4次
运行第5次

遍历查询结果集

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
BEGIN
  FOR cur_row IN (SELECT request_msg_id, erp_orderno FROM effective_oms_msg_for_test WHERE ROWNUM<5)
  LOOP
    dbms_output.put_line('msg_id: ' || cur_row.request_msg_id || ' orderno: ' || cur_row.erp_orderno);    
  END LOOP;
END;

CALL SP_TEST_PROC();

WHILE循环

基础用法

WHILE 条件语句 LOOP
-- do something
END LOOP;

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
i INT :=1 ;
BEGIN
  WHILE i < 3 LOOP
    dbms_output.put_line('运行第' || i || '次');
    i := i + 1;
  END LOOP;
END;

CALL SP_TEST_PROC();

调用输出

运行第1次
运行第2次

LOOP循环

基础用法

LOOP
  --do something
  IF 退出循环条件 THEN
    --do something
    EXIT;
  END IF;
  --do something
END LOOP;

LOOP
  --do something
  EXIT WHEN 退出循环条件;
  --do something
END LOOP;

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
i INT :=1;
BEGIN
  LOOP
    IF i=3 THEN --如果i=3,则退出
      EXIT;
    END IF;
    dbms_output.put_line('运行第' || i || '次');
    i := i + 1;
  END LOOP;
END;

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
i INT :=1;
BEGIN
  LOOP
    EXIT WHEN i = 3;
    dbms_output.put_line('运行第' || i || '次');
    i := i + 1;
  END LOOP;
END;

调用结果，同上述WHILE循环示例

执行体之GOTO跳转语句

基础用法

<<gotoLabel>>
-- do something
GOTO gotoLabel;

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
i int := 1;
BEGIN
  <<loop_lable>>
  dbms_output.put_line('运行第' || i || '次');
  i := i + 1;
  IF i < 3 THEN
    GOTO loop_lable;
  END IF;
END;

调用结果，同上述WHILE循环示例

说明：如上，我们也可以利用GOTO语句来实现循环

执行体之游标

基础用法

CURSOR型游标

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
CURSOR cursor_name IS 查询语句;  -- 定义CURSOR类型游标(不能用于参数传递)
BEGIN
    FOR varName IN cursor_name LOOP 
    --do something --引用变量 varName.field
    END LOOP;
END;

注意：通过以上方式，采用FOR循环遍历游标，会自动关闭游标，不需要在END LOOP; 后添加关闭游标的代码CLOSE cursor_name;，会报错

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
CURSOR cursor_for_msgs IS SELECT request_msg_id, erp_orderno FROM effective_msg_for_test WHERE ROWNUM<5;
BEGIN
  FOR msg IN cursor_for_msgs LOOP
    dbms_output.put_line('msg_id: ' || msg.request_msg_id || ' orderno: ' || msg.erp_orderno);
  END LOOP;
END;

SYS_REFCURSOR型游标

SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

基础用法

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
v_cursor_name SYS_REFCURSOR ;
--变量定义
BEGIN
  OPEN v_cursor_name FOR 查询语句;
  LOOP
    FETCH v_cursor_name INTO 变量1, 变量2, ..., 变量N; --变量个数和查询结果记录包含的字段数量保持一致
    EXIT WHEN v_cursor_name%NOTFOUND;
  END LOOP;
  CLOSE v_cursor_name; 
END;

说明： SYS_REFCURSOR中可使用三个状态属性：

  • %NOTFOUND 表示未找到记录信息
  • %FOUND 表示找到记录信息
  • %ROWCOUNT 表示当前游标所指向的行位置
  • %ISOPEN 如果游标已经打开，则返回TRUE，否则返回FALSE

注意：

  • SYS_REFCURSOR游标只能通过OPEN方法来打开和赋值

  • SYS_REFCURSOR游标只能通过FETCH INTO方法来遍历取值

示例

CREATE OR REPLACE PROCEDURE SP_TEST_PROC IS
v_cursor_for_msgs SYS_REFCURSOR ;
v_request_msg_id VARCHAR2(20);
v_erp_orderno VARCHAR2(50);
BEGIN
  OPEN v_cursor_for_msgs FOR SELECT request_msg_id, erp_orderno FROM effective_msg_for_test WHERE ROWNUM<5;
  LOOP
    FETCH v_cursor_for_msgs INTO v_request_msg_id, v_erp_orderno;
    EXIT WHEN v_cursor_for_msgs%NOTFOUND;
    dbms_output.put_line('msg_id: ' || v_request_msg_id || ' orderno: ' || v_erp_orderno);    
  END LOOP;
  -- 注意，上述循环执行完成后，Orable并没有自动关闭游标，需要显示关闭游标
  IF v_cursor_for_msgs%ISOPEN THEN
    dbms_output.put_line('CLOSING CURSOR');
    CLOSE v_cursor_for_msgs;
  END IF;  
END;

显示关闭游标

CLOSE cursor_name;

删除存储过程

基础语法

DROP PROCEDURE procedure_name;

标签：总结,存储,END,--,SP,BEGIN,num,Oracle,PROC
From： https://www.cnblogs.com/shouke/p/16819867.html

相关文章

  • 计算机基础与程序设计第八周学习总结
    教材内容学习总结计算机科学概论课本第九章1，了解面向对象方法和面向过程方法的区别；2，编译器，解释器的意义和区别；3，高级语言的功能和应用。C语言与程序设计课本第七章1......
  • Thanos 架构剖析（四）数据存储和运维工具
    ThanosStore主要提供查询历史数据功能，当Querier组件调用Stroe接口，Stroe再通过对象存储接口获取数据，并将存储数据转换成Querier所需的数据格式。Thanos数据存储Thanos......
  • C语言问题定位总结
    二分搜索法gitbisect命令，可以定位问题引入的第一个commit，如下图：用法举例：1、下载模拟代码：[email protected]:bradleyboy/bisectercise.git$cdbisecterci......
  • python学习第四周总结
    异常常见类型异常处理语法结构异常补充处理异常处理实战应用生成器对象自定义生成器range()功能yield冷门用法生成器表达式模块简介模块的分......
  • 总结
    目录异常生成器对象索引取值与迭代取值的差异模块包编程思想的转变软件开发目录规范常用内置模块collections模块时间模块随机数模块os模块sys模块json模块异常1.异常处......
  • 第四周总结复习
    一周总结异常处理异常常见类型AttributeError#访问的对象属性不存在ImportError#无法导入模块或者对象，主要是路径有误或名称错误SyntaxError#pytho......
  • 云盘插件，阿里云对象存储oss配置
     一、登陆阿里云 https://www.aliyun.com搜索oss 进入oss控制台  https://oss.console.aliyun.com/bucket    二、创建Bucket  没有特殊要求的，默......
  • chap4 循环结构、chap6 回顾数据类型和表达式 学习总结
     在我认为循环结构是c语言中较为重要的部分，他在使用中也能发挥很大的作用，无论是for还是while或是dowhile，都有他们各自的特点，通常他们在一样的的题目中都可以用，但是在一......
  • 第四周总结
    第四周总结目录第四周总结异常处理异常常见类型异常处理语法结构异常处理补充异常处理实战应用生成器生成器对象yield冷门用法生成器表达式自定义生成器range索引取值与迭......
  • 2022-2023-1 20221310 《计算机基础与程序设计》第8周学习总结
    作业信息|这个作业属于哪个课程|https://edu.cnblogs.com/campus/besti/2022-2023-1-CFAP||这个作业要求在哪里|<作业要求的链接>https://www.cnblogs.com/rocedu/p/9......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99