Oracle 存储过程学习总结

创建/更新存储过程

基础基础用法
创建/修改无参存储过程

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

复制代码

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

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

复制代码

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

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

调用存储过程

1. --调用带参数存储过程
2. CALL procedure_name([参数列表]);
3. --或者
4. BEGIN procedure_name(参数列表); END; --注意 分号不能少，特别是END后面的分号
5. --或者
6. SQL> EXEC procedure_name(参数列表);  --在命令行窗口执行，比如SQLPlus执行窗口
8. --调用不带参数存储过程
9. CALL procedure_name();
10. --或者
11. BEGIN procedure_name; END; --注意 分号不能少，特别是END后面的分号
12. --或者
13. BEGIN procedure_name(); END; --注意 分号不能少，特别是END后面的分号
14. --或者
15. SQL> EXEC procedure_name;

复制代码

简单的示例

创建携带参数存储过程

1. CREATE OR REPLACE PROCEDURE SP_TEST_PROC(workDate IN Date) is
2. BEGIN
3. dbms_output.put_line('The input date is:'||to_date(workDate,'yyyy-mm-dd'));
4. END;
6. CALL SP_TEST_PROC(sysdate); --输出：The input date is:22-AUG-24
8. --创建携带返回值存储过程
9. CREATE OR REPLACE PROCEDURE SP_SUM_PROC(number1 IN NUMBER, number2 IN NUMBER, result OUT NUMBER) is
10. BEGIN
11. result := number1 + number2;
12. END;
14. -- sql窗口中调用
15. DECLARE res NUMBER(6);
16. BEGIN
17.   SP_SUM_PROC(1, 3, res);
18.   dbms_output.put_line(res); --输出：4
19. END;
21. -- 命令行窗口中调用
22. SQL> VARIABLE res NUMBER;
23. SQL> EXEC SP_SUM_PROC(1, 3, :res);
24. PL/SQL procedure successfully completed
25. res
26. ---------
27. 4
29. --创建带输入输出参数的存储过程
30. CREATE OR REPLACE PROCEDURE SP_SUM_PROC(number1 IN NUMBER, number2 IN OUT NUMBER) is
31. BEGIN
32. number2 := number1 + number2;
33. END;
35. -- 调用
36. DECLARE num NUMBER(6) :=3; --注意，不能在存储过程中声明变量时这样赋值
37. BEGIN
38.   dbms_output.put_line('调用前num变量值：' || num); --输出：调用前num变量值：3
39.   SP_SUM_PROC(1, num);
40.   dbms_output.put_line('调用后num变量值：' || num); --输出：调用后num变量值：4
41. END;

复制代码

DECLARE基础用法说明

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

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

复制代码

示例：

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

复制代码

创建无参数存储过程

1. CREATE OR REPLACE PROCEDURE SP_TEST_PROC is
2. BEGIN
3. dbms_output.put_line('hello, tester');
4. END;
6. --调用
7. CALL SP_TEST_PROC(); --输出：hello, tester
8. --或者
9. BEGIN
10. SP_TEST_PROC;
11. END;

复制代码

声明全局变量

方式一：直接声明数据类型
格式：变量名 数据类型(大小及精度)
示例：

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

复制代码

方式二：使用%TYPE声明
格式：变量名 表名.字段名%TYPE
含义：该变量的数据类型与指定表的指定字段的数据类型一致
示例：

1. r_carrierID  CARRIERS.carrier_id%type;

复制代码

方式三：使用%ROWTYPE声明
格式：变量名 表名%ROWTYPE
含义：该变量的数据类型与指定表的指定行记录（所有字段）的数据类型一致
示例：

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

复制代码

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

方式一：使用":="直接赋值
注意，这种方式不适合使用%ROWTYPE声明的变量
示例：

1. v_username := 'shouke';

复制代码

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

1. v_username VARCHAR2(15) := 'shouke'

复制代码

方式二：select 表字段 into 变量 from 表
1：查询指定表的某些指定字段　　
不适合使用%ROWTYPE声明的变量

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

复制代码

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

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

复制代码

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

示例
[code]CREATE OR REPLACE PROCEDURE SP_TEST_PROC ISv_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