当前位置：首页 > 数据库 > oracle > 详细页面

Oracle 存储过程详细介绍使用实践

时间：2023-10-31来源：系统城装机大师作者：佚名

Oracle 存储过程详解

存储过程实践

1：无入参出参存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 CREATE OR REPLACE PROCEDURE your_stored_procedure
AS
BEGIN
-- 在这里编写存储过程的逻辑
-- 示例：输出一条消息
DBMS_OUTPUT.PUT_LINE('Hello, this is your stored procedure.');
-- 示例：查询数据并处理
FOR rec IN (SELECT * FROM your_table) LOOP
-- 处理每一行数据
-- 可以使用 rec.column_name 来访问具体字段的值
-- 示例：输出每一行的 ID 和 Name 字段值
DBMS_OUTPUT.PUT_LINE('ID: ' || rec.id || ', Name: ' || rec.name);
END LOOP;
END;
/

在上述示例中，存储过程 your_stored_procedure 没有输入参数，也没有输出参数。您可以在存储过程的逻辑部分编写您需要的操作，例如查询数据并进行处理，输出消息等。

请根据您的实际需求修改示例代码，并将适当的逻辑放在存储过程中。执行该存储过程时，可以使用 EXEC your_stored_procedure; 或者 CALL your_stored_procedure; 来调用它。在执行过程中，存储过程中的逻辑将被执行，并将结果输出到 DBMS_OUTPUT，您可以通过适当的方式查看输出结果。

2：有入参无出参存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2)
AS
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param
-- 示例：输出接收到的输入参数
DBMS_OUTPUT.PUT_LINE('Received input parameter: ' || p_input_param);
-- 示例：根据输入参数查询数据并处理
FOR rec IN (SELECT * FROM your_table WHERE column_name = p_input_param) LOOP
-- 处理每一行数据
-- 可以使用 rec.column_name 来访问具体字段的值
-- 示例：输出每一行的 ID 和 Name 字段值
DBMS_OUTPUT.PUT_LINE('ID: ' || rec.id || ', Name: ' || rec.name);
END LOOP;
END;
/

在上述示例中，存储过程 your_stored_procedure 接受一个输入参数 p_input_param，您可以在存储过程的逻辑部分使用这个参数进行操作，例如根据参数查询数据并进行处理。

请根据您的实际需求修改示例代码，并将适当的逻辑放在存储过程中。执行该存储过程时，可以使用 EXEC your_stored_procedure(‘your_input_value’); 或者 CALL your_stored_procedure(‘your_input_value’); 来调用它。在执行过程中，存储过程中的逻辑将被执行，并将结果输出到 DBMS_OUTPUT，您可以通过适当的方式查看输出结果。

3：有入参和出参存储过程

1
2
3
4
5
6
7
8 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2, p_output_param OUT NUMBER)
AS
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param
-- 示例：根据输入参数查询数据并处理
SELECT COUNT(*) INTO p_output_param FROM your_table WHERE column_name = p_input_param;
END;
/

在上述示例中，存储过程 your_stored_procedure 接受一个输入参数 p_input_param 和一个输出参数 p_output_param。您可以在存储过程的逻辑部分使用输入参数进行操作，并将结果存储到输出参数中。

请根据您的实际需求修改示例代码，并将适当的逻辑放在存储过程中。执行该存储过程时，可以使用以下代码调用它：

1
2
3
4
5
6
7
8 DECLARE
v_output_param NUMBER;
BEGIN
your_stored_procedure('your_input_value', v_output_param);
-- 在这里可以使用 v_output_param 的值进行进一步的处理
DBMS_OUTPUT.PUT_LINE('Output parameter: ' || v_output_param);
END;
/

在执行过程中，存储过程中的逻辑将被执行，并将结果存储到输出参数 p_output_param 中。在示例中，输出参数的值被存储到 v_output_param 变量中，您可以根据需要进行进一步的处理。

4：存储过程中更新表数据

1
2
3
4
5
6
7
8
9
10
11
12 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2)
AS
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param
-- 示例：更新表中的数据
UPDATE your_table
SET column_name = p_input_param
WHERE <条件>;
-- 示例：提交事务
COMMIT;
END;
/

在上述示例中，存储过程 your_stored_procedure 接受一个输入参数 p_input_param，您可以在存储过程的逻辑部分使用该参数进行更新表数据的操作。根据实际情况，您可以修改 UPDATE 语句中的表名、字段名和更新条件。

请根据您的实际需求修改示例代码，并将适当的逻辑放在存储过程中。执行该存储过程时，可以使用以下代码调用它：

1
2
3
4
5 BEGIN
your_stored_procedure('your_input_value');
-- 执行其他操作
END;
/

在执行过程中，存储过程中的逻辑将被执行，并根据传入的输入参数更新表中的数据。示例中的 COMMIT 语句用于提交事务，您可以根据需要添加或删除该语句。

5：存储过程中删除某些数据

1
2
3
4
5
6
7
8
9
10
11 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2)
AS
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param
-- 示例：删除表中的数据
DELETE FROM your_table
WHERE column_name = p_input_param;
-- 示例：提交事务
COMMIT;
END;
/

在上述示例中，存储过程 your_stored_procedure 接受一个输入参数 p_input_param，您可以在存储过程的逻辑部分使用该参数进行删除表数据的操作。根据实际情况，您可以修改 DELETE 语句中的表名、字段名和删除条件。

1
2
3
4
5 BEGIN
your_stored_procedure('your_input_value');
-- 执行其他操作
END;
/

6：存储过程中执行新增操作

1
2
3
4
5
6
7
8
9
10
11 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param1 IN VARCHAR2, p_input_param2 IN NUMBER)
AS
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param1 和 p_input_param2
-- 示例：插入数据到表中
INSERT INTO your_table (column1, column2)
VALUES (p_input_param1, p_input_param2);
-- 示例：提交事务
COMMIT;
END;
/

在上述示例中，存储过程 your_stored_procedure 接受两个输入参数 p_input_param1 和 p_input_param2，您可以在存储过程的逻辑部分使用这些参数进行插入数据到表的操作。根据实际情况，您可以修改 INSERT INTO 语句中的表名和列名，并根据参数进行值的插入

1
2
3
4
5 BEGIN
your_stored_procedure('value1', 123);
-- 执行其他操作
END;
/

在执行过程中，存储过程中的逻辑将被执行，并根据传入的输入参数将数据插入到表中。示例中的 COMMIT 语句用于提交事务，您可以根据需要添加或删除该语句。

7：存储过程查询姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2)
AS
v_data your_table%ROWTYPE;
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param
-- 示例：查询表中的数据
SELECT *
INTO v_data
FROM your_table
WHERE column_name = p_input_param;
-- 示例：在输出结果中展示查询到的数据
DBMS_OUTPUT.PUT_LINE('Column1: ' || v_data.column1);
DBMS_OUTPUT.PUT_LINE('Column2: ' || v_data.column2);
-- 添加其他列的输出
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found for the input parameter.');
END;
/

在上述示例中，存储过程 your_stored_procedure 接受一个输入参数 p_input_param，并使用该参数查询表中的数据。查询结果存储在变量 v_data 中，您可以根据表的结构修改变量的类型和名称。然后，您可以根据需要使用 DBMS_OUTPUT.PUT_LINE 将查询结果输出。

1
2
3
4
5 BEGIN
your_stored_procedure('your_input_value');
-- 执行其他操作
END;
/

在执行过程中，存储过程中的逻辑将被执行，并根据传入的输入参数查询表中的数据。如果找到匹配的数据，则会将查询结果打印出来。如果未找到匹配的数据，则会输出相应的消息。

8：存储过程查询符合条件的数据集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2)
AS
CURSOR cur_data IS
SELECT *
FROM your_table
WHERE column_name = p_input_param;
v_data your_table%ROWTYPE;
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param
-- 示例：循环遍历查询结果集
FOR rec_data IN cur_data LOOP
-- 存储每一行的数据到变量 v_data
v_data := rec_data;
-- 示例：在输出结果中展示每一行的数据
DBMS_OUTPUT.PUT_LINE('Column1: ' || v_data.column1);
DBMS_OUTPUT.PUT_LINE('Column2: ' || v_data.column2);
-- 添加其他列的输出
END LOOP;
-- 示例：如果没有查询到数据，则输出相应的消息
IF cur_data%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found for the input parameter.');
END IF;
END;
/

在上述示例中，存储过程 your_stored_procedure 接受一个输入参数 p_input_param，并使用该参数查询符合条件的数据集。查询结果使用游标 cur_data 来获取，然后通过循环遍历每一行数据并存储在变量 v_data 中，最后根据需要使用 DBMS_OUTPUT.PUT_LINE 将查询结果输出。

1
2
3
4
5 BEGIN
your_stored_procedure('your_input_value');
-- 执行其他操作
END;
/

在执行过程中，存储过程中的逻辑将被执行，并根据传入的输入参数查询符合条件的数据集。如果找到匹配的数据，则会将每一行的查询结果打印出来。如果未找到匹配的数据，则会输出相应的消息。

9：存储过程查询符合条件的数据集并返回

下面是一个示例的 PL/SQL 存储过程，它接受一个输入参数，并使用该参数查询符合条件的数据集，并将结果作为游标返回：

1
2
3
4
5
6
7
8
9
10
11 CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2, p_result OUT SYS_REFCURSOR)
AS
BEGIN
-- 在这里编写存储过程的逻辑，使用输入参数 p_input_param
-- 示例：查询符合条件的数据集并将结果存储在游标 p_result 中
OPEN p_result FOR
SELECT *
FROM your_table
WHERE column_name = p_input_param;
END;
/

在上述示例中，存储过程 your_stored_procedure 接受一个输入参数 p_input_param，并使用该参数查询符合条件的数据集。查询结果将存储在游标 p_result 中，并作为输出参数返回。

1
2
3
4
5
6
7 DECLARE
v_cursor SYS_REFCURSOR;
BEGIN
your_stored_procedure('your_input_value', v_cursor);
-- 执行其他操作，如处理返回的游标数据集
END;
/

在执行过程中，存储过程中的逻辑将被执行，并根据传入的输入参数查询符合条件的数据集。查询结果将存储在游标 v_cursor 中，您可以根据需要在存储过程外部处理返回的游标数据集

10：复杂一点的存储过程，传入就诊id和档案id 查询数据集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55 CREATE OR REPLACE PROCEDURE SP_MZ_GET_JZFYMX(P_JIUZHENID VARCHAR2,--入参就诊id
P_ID VARCHAR2,--入参档案id
P_ERRMSG OUT VARCHAR2,--出参提示信息
p_cursor OUT SYS_REFCURSOR--出参，游标
) IS
--涉及到动态表名，所以表名需要拼接
PRI_KH VARCHAR2(3000); --
PRI_SSTSQL VARCHAR2(3000); --sql
PRI_NIAN1 INT;--年份1
PRI_NIAN2 INT;--年份2
PRI_NUM INT :=0;--次数
BEGIN
--参数判断
IF TRIM(P_ID) IS NULL THEN
P_ERRMSG :='档案ID不能为空!';
return;
END IF;
IF TRIM(P_JIUZHENID) IS NULL THEN
P_ERRMSG :='就诊ID不能为空!';
return;
END IF;
--判断是否建档
SELECT COUNT(T_DANGANID) INTO PRI_KH FROM T_DANGAN WHERE T_DANGANID = P_ID;
IF TRIM(PRI_KH) = '0' THEN
P_ERRMSG := '传入的档案ID对应的档案不存在！';
return;
END IF;
PRI_NIAN2 := TRUNC(TO_NUMBER(EXTRACT(YEAR FROM SYSDATE)));--把当前年份存入数据中
PRI_NIAN1 := PRI_NIAN2 - 2;--把前两年年份存储数据中
IF PRI_NIAN1 <= PRI_NIAN2 THEN
-- 从起始数字开始递增循环输出
FOR i IN PRI_NIAN1..PRI_NIAN2 LOOP
-- 在这里对每个整数进行操作或展示
if PRI_NUM > 0 then --第二次拼接sql需要 union all
PRI_SSTSQL := PRI_SSTSQL || ' union all ' || 'select decode(b.mingcheng,'''',c.mingcheng,b.mingcheng) xmmc,a.guige xmgg,a.danwei xmdw,a.danjia xmdj,a.shuliang xmsl,a.danjia fyje,a.jiesuanshijian fyrq
from T_shouju' || i || ' d, T_feiyong' || i || ' a,T_biaozhun b,T_yaopin c where d.jiesuanshijian = a.jiesuanshijian and d.T_danganid = a.T_danganid and
a.jisuanjibianma = b.jisuanjibianma(+) and a.jisuanjibianma = c.jisuanjibianma(+) and d.T_shoujuid = ' || '''' || P_JIUZHENID || '''' || ' and d.T_danganid = ' || '''' || P_ID || '''' || ' ';
else --第一次拼接sql 不需要拼接union all
PRI_SSTSQL := 'select decode(b.mingcheng,'''',c.mingcheng,b.mingcheng) xmmc,a.guige xmgg,a.danwei xmdw,a.danjia xmdj,a.shuliang xmsl,a.danjia fyje,a.jiesuanshijian fyrq
from T_shouju' || i || ' d, T_feiyong' || i || ' a,T_feiyongbiaozhun b,T_yaopin c where d.jiesuanshijian = a.jiesuanshijian and d.T_danganid = a.T_danganid and
a.jisuanjibianma = b.jisuanjibianma(+) and a.jisuanjibianma = c.jisuanjibianma(+) and d.T_shoujuid = ' || '''' || P_JIUZHENID || '''' || ' and d.T_danganid = ' || '''' || P_ID || '''' || ' ';
end if;--结束if判断
PRI_NUM := PRI_NUM + 1;--每次循环次数+1
END LOOP;--结束循环
end if;--结束if判断
//拼接不需要动态表名的查询语句
PRI_SSTSQL := PRI_SSTSQL || ' union all ' || 'select decode(b.mingcheng,'''',c.mingcheng,b.mingcheng) xmmc,a.guige xmgg,a.danwei xmdw,a.danjia xmdj,a.shuliang xmsl,a.danjia fyje,a.shoufeishijian fyrq
from T_feiyong a,T_feiyongbiaozhun b,T_yaopin c where a.jisuanjibianma = b.jisuanjibianma(+) and a.jisuanjibianma = c.jisuanjibianma(+) and a.rizhiid = ' || '''' || P_JIUZHENID || '''' || ' and a.T_danganid = ' || '''' || P_ID || '''' || ' ';
--P_ERRMSG := PRI_SSTSQL;
OPEN p_cursor FOR PRI_SSTSQL ;--打开游标并赋值
EXCEPTION WHEN OTHERS THEN
--回滚SQL
-- ROLLBACK;
P_ERRMSG := '执行错误！' || CHR(13) || '错误代码：' || SQLCODE || CHR(13) || '错误信息：' || SQLERRM;
END SP_MZ_GET_JZFYMX;

如何调整存储过程看下边

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 DECLARE
-- 声明游标变量
P_JIUZHENID VARCHAR2(1000) := '86d26a1a-a448-464c-95dd-acc85ec6906e';
P_ID VARCHAR2(1000) := 'da7dbe52-793a-4120-beb3-c2075cc74a53';
P_ERRMSG VARCHAR2(2000);
your_cursor SYS_REFCURSOR;
-- 声明结果变量
your_variable1 VARCHAR2(50);
your_variable2 VARCHAR2(50);
your_variable3 VARCHAR2(50);
your_variable4 VARCHAR2(50);
your_variable5 VARCHAR2(50);
your_variable6 VARCHAR2(50);
your_variable7 VARCHAR2(50);
BEGIN
-- 调用存储过程并传入游标参数
SP_MZ_GET_JZFYMX(P_JIUZHENID,P_ID,P_ERRMSG,your_cursor);
-- 循环获取游标结果并展示
LOOP
FETCH your_cursor INTO your_variable1, your_variable2, your_variable3,your_variable4,your_variable5,your_variable6,your_variable7;
EXIT WHEN your_cursor%NOTFOUND;
-- 在这里对每行数据进行展示或其他操作
DBMS_OUTPUT.PUT_LINE(your_variable1 || ', ' || your_variable2 || ', ' || your_variable3|| ', ' || your_variable4|| ', ' || your_variable5|| ', ' || your_variable6|| ', ' || your_variable7);
END LOOP;
-- 关闭游标
CLOSE your_cursor;
END;
/