Oracle 存储过程学习总结 -阿里云开发者社区

创建/更新存储过程

基础基础用法

创建/修改无参存储过程

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 表示输入输出参数（注：都不区分大小写）

注意：

存储过程参数数据类型不能指定长度
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;

Oracle 存储过程学习总结

创建/更新存储过程

调用存储过程

简单的示例

创建携带参数存储过程

DECLARE基础用法说明

创建无参数存储过程

声明全局变量

变量赋值

变量声明与赋值示例

一个应用实例

执行体之逻辑判断语句

IF语句

CASE WHEN语句

执行体之循环遍历语句

FOR循环

WHILE循环

LOOP循环

执行体之GOTO跳转语句

执行体之游标

删除存储过程

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle 存储过程学习总结

创建/更新存储过程

调用存储过程

简单的示例

创建携带参数存储过程

DECLARE基础用法说明

创建无参数存储过程

声明全局变量

变量赋值

变量声明与赋值示例

一个应用实例

执行体之逻辑判断语句

IF语句

CASE WHEN语句

执行体之循环遍历语句

FOR循环

WHILE循环

LOOP循环

执行体之GOTO跳转语句

执行体之游标

删除存储过程

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像