Oracle 存储过程详细介绍及如何使用

Oracle 存储过程详解

存储过程实践

1：无入参出参存储过程

CREATE OR REPLACE PROCEDURE your_stored_procedure AS BSEO靠我EGIN -- 在这里编写存储过程的逻辑 -- 示例：输出一条消息 DBMS_OUTPUT.PUT_LINE(Hello, this is your SEO靠我stored procedure.); -- 示例：查询数据并处理 FOR rec IN (SELECT * FROM your_table) LOOP SEO靠我 -- 处理每一行数据 -- 可以使用 rec.column_name 来访问具体字段的值 -- 示例：输出每一行的 ID 和 Name 字段值 DBSEO靠我MS_OUTPUT.PUT_LINE(ID: || rec.id || , Name: || rec.name); END LOOP; END; /

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

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

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

CREATE OR REPLACE PROCEDURE your_stSEO靠我ored_procedure(p_input_param IN VARCHAR2) AS BEGIN -- 在这里编写存储过程的逻辑，使用输入参数 p_SEO靠我input_param -- 示例：输出接收到的输入参数 DBMS_OUTPUT.PUT_LINE(Received input parameter: || p_inSEO靠我put_param); -- 示例：根据输入参数查询数据并处理 FOR rec IN (SELECT * FROM your_table WHERE column_nSEO靠我ame = p_input_param) LOOP -- 处理每一行数据 -- 可以使用 rec.column_name 来访问具体字段的值 -- 示例SEO靠我：输出每一行的 ID 和 Name 字段值 DBMS_OUTPUT.PUT_LINE(ID: || rec.id || , Name: || rec.name); ENSEO靠我D LOOP; END; /

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

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

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

CREATE OR REPLACE PROCEDURE your_stored_procedure(p_inpSEO靠我ut_param IN VARCHAR2, p_output_param OUT NUMBER) AS BEGIN -- 在这里编写存储过程的逻辑，使用SEO靠我输入参数 p_input_param -- 示例：根据输入参数查询数据并处理 SELECT COUNT(*) INTO p_output_param FROM youSEO靠我r_table WHERE column_name = p_input_param; END; /

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

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

DECLARE v_output_param NUMBER; BEGIN your_SEO靠我stored_procedure(your_input_value, v_output_param); -- 在这里可以使用 v_output_param 的值进行进一步的处理 SEO靠我 DBMS_OUTPUT.PUT_LINE(Output parameter: || v_output_param); END; /

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

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

CRSEO靠我EATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2) AS BEGIN SEO靠我 -- 在这里编写存储过程的逻辑，使用输入参数 p_input_param -- 示例：更新表中的数据 UPDATE your_table SEO靠我 SET column_name = p_input_param WHERE <条件>; -- 示例：提交事务 COMMIT; ENSEO靠我D; /

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

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

BEGIN SEO靠我your_stored_procedure(your_input_value); -- 执行其他操作 END; /

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

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

CREATE OR REPLACE PROCEDURE youSEO靠我r_stored_procedure(p_input_param IN VARCHAR2) AS BEGIN -- 在这里编写存储过程的逻辑，使用输入参SEO靠我数 p_input_param -- 示例：删除表中的数据 DELETE FROM your_table WHERE column_name = p_SEO靠我input_param; -- 示例：提交事务 COMMIT; END; /

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

BEGIN SEO靠我 your_stored_procedure(your_input_value); -- 执行其他操作 END; /

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

CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param1 IN VARCHAR2, p_input_param2 IN NUMSEO靠我BER) AS BEGIN -- 在这里编写存储过程的逻辑，使用输入参数 p_input_param1 和 p_input_param2 SEO靠我 -- 示例：插入数据到表中 INSERT INTO your_table (column1, column2) VALUES (p_input_param1, p_SEO靠我input_param2); -- 示例：提交事务 COMMIT; END; /

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

BEGIN your_stored_procedure(value1, 123); -- 执行其他操作 SEO靠我 END; /

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

7：存储过程查询姓SEO靠我名

CREATE OR REPLACE PROCEDURE your_stored_procedure(p_input_param IN VARCHAR2) AS v_dSEO靠我ata your_table%ROWTYPE; BEGIN -- 在这里编写存储过程的逻辑，使用输入参数 p_input_param -- 示例：查询SEO靠我表中的数据 SELECT * INTO v_data FROM your_table WHERE column_name = p_inpSEO靠我ut_param; -- 示例：在输出结果中展示查询到的数据 DBMS_OUTPUT.PUT_LINE(Column1: || v_data.column1); SEO靠我 DBMS_OUTPUT.PUT_LINE(Column2: || v_data.column2); -- 添加其他列的输出 EXCEPTION SEO靠我 WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(No data found for the input parameter.); SEO靠我 END; /

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

BEGIN your_stored_prSEO靠我ocedure(your_input_value); -- 执行其他操作 END; /

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

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

CREATE OR REPLACE PROCEDURE your_stoSEO靠我red_procedure(p_input_param IN VARCHAR2) AS CURSOR cur_data IS SELECT * SEO靠我 FROM your_table WHERE column_name = p_input_param; v_data your_table%ROWTYPE; SEO靠我 BEGIN -- 在这里编写存储过程的逻辑，使用输入参数 p_input_param -- 示例：循环遍历查询结果集 FOR rec_dSEO靠我ata IN cur_data LOOP -- 存储每一行的数据到变量 v_data v_data := rec_data; -- 示例：在输出结果中SEO靠我展示每一行的数据 DBMS_OUTPUT.PUT_LINE(Column1: || v_data.column1); DBMS_OUTPUT.PUT_LINE(ColuSEO靠我mn2: || v_data.column2); -- 添加其他列的输出 END LOOP; -- 示例：如果没有查询到数据，则输出相应的消息 SEO靠我 IF cur_data%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(No data found for the input parameter.);SEO靠我 END IF; END; /

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

BEGIN your_stored_procedure(your_input_value); -- 执行其他操作 END; /

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

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

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

CREATE OR REPLACE PROCEDURE your_stoSEO靠我red_procedure(p_input_param IN VARCHAR2, p_result OUT SYS_REFCURSOR) AS BEGIN SEO靠我 -- 在这里编写存储过程的逻辑，使用输入参数 p_input_param -- 示例：查询符合条件的数据集并将结果存储在游标 p_result 中 OPEN p_SEO靠我result FOR SELECT * FROM your_table WHERE column_name = p_input_param; SEO靠我 END; /

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

DECLARE v_cursor SYS_REFCURSOR; BEGIN your_stored_prSEO靠我ocedure(your_input_value, v_cursor); -- 执行其他操作，如处理返回的游标数据集 END; /

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

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

CREATE OR REPLACE PROCEDURE SP_MZ_GET_JZFYMX(P_JIUZHENID VARCHAR2,--入参就诊id P_ID VARCHASEO靠我R2,--入参档案id P_ERRMSG OUT VARCHAR2,--出参提示信息 p_cursor OUT SYS_REFCURSOR--出参，游标 SEO靠我 ) IS --涉及到动态表名，所以表名需要拼接 PRI_KH VARCHAR2(3000); -- PRI_SSTSQL VARCHAR2(3000)SEO靠我; --sql PRI_NIAN1 INT;--年份1 PRI_NIAN2 INT;--年份2 PRI_NUM INT :=0;--次数 SEO靠我 BEGIN --参数判断 IF TRIM(P_ID) IS NULL THEN P_ERRMSG :=档案ID不能为空!; returSEO靠我n; END IF; IF TRIM(P_JIUZHENID) IS NULL THEN P_ERRMSG :=就诊ID不能为空!; rSEO靠我eturn; END IF; --判断是否建档 SELECT COUNT(T_DANGANID) INTO PRI_KH FROM T_DANGAN WSEO靠我HERE T_DANGANID = P_ID; IF TRIM(PRI_KH) = 0 THEN P_ERRMSG := 传入的档案ID对应的档案不存在！; SEO靠我 return; END IF; PRI_NIAN2 := TRUNC(TO_NUMBER(EXTRACT(YEAR FROM SYSDATE)));--把当前年份SEO靠我存入数据中 PRI_NIAN1 := PRI_NIAN2 - 2;--把前两年年份存储数据中 IF PRI_NIAN1 <= PRI_NIAN2 THEN SEO靠我 -- 从起始数字开始递增循环输出 FOR i IN PRI_NIAN1..PRI_NIAN2 LOOP -- 在这里对每个整数进行操作或展示 if SEO靠我PRI_NUM > 0 then --第二次拼接sql需要 union all PRI_SSTSQL := PRI_SSTSQL || union all || select decoSEO靠我de(b.mingcheng,,c.mingcheng,b.mingcheng) xmmc,a.guige xmgg,a.danwei xmdw,a.danjia xmdj,a.shuliang xmSEO靠我sl,a.danjia fyje,a.jiesuanshijian fyrq from T_shouju || i || d, T_feiyong || i || a,T_biaozhSEO靠我un b,T_yaopin c where d.jiesuanshijian = a.jiesuanshijian and d.T_danganid = a.T_danganid and SEO靠我 a.jisuanjibianma = b.jisuanjibianma(+) and a.jisuanjibianma = c.jisuanjibianma(+) and d.T_shoujuidSEO靠我 = || || P_JIUZHENID || || and d.T_danganid = || || P_ID || || ; else --第一次拼接sql 不需要拼接union SEO靠我all PRI_SSTSQL := select decode(b.mingcheng,,c.mingcheng,b.mingcheng) xmmc,a.guige xmgg,a.daSEO靠我nwei xmdw,a.danjia xmdj,a.shuliang xmsl,a.danjia fyje,a.jiesuanshijian fyrq from T_shouju ||SEO靠我 i || d, T_feiyong || i || a,T_feiyongbiaozhun b,T_yaopin c where d.jiesuanshijian = a.jiesuanshijiaSEO靠我n and d.T_danganid = a.T_danganid and a.jisuanjibianma = b.jisuanjibianma(+) and a.jisuanjibSEO靠我ianma = c.jisuanjibianma(+) and d.T_shoujuid = || || P_JIUZHENID || || and d.T_danganid = || || P_IDSEO靠我 || || ; end if;--结束if判断 PRI_NUM := PRI_NUM + 1;--每次循环次数+1 END LOOP;--结束循环 SEO靠我 end if;--结束if判断 //拼接不需要 动态表名的查询语句 PRI_SSTSQL := PRI_SSTSQL || union all || seSEO靠我lect decode(b.mingcheng,,c.mingcheng,b.mingcheng) xmmc,a.guige xmgg,a.danwei xmdw,a.danjia xmdj,a.shSEO靠我uliang xmsl,a.danjia fyje,a.shoufeishijian fyrq from T_feiyong a,T_feiyongbiaozhun b,T_yaopiSEO靠我n c where a.jisuanjibianma = b.jisuanjibianma(+) and a.jisuanjibianma = c.jisuanjibianma(+) and a.riSEO靠我zhiid = || || P_JIUZHENID || || and a.T_danganid = || || P_ID || || ; --P_ERRMSG := PRI_SSTSEO靠我SQL; OPEN p_cursor FOR PRI_SSTSQL ;--打开游标并赋值 EXCEPTION WHEN OTHERS THEN --回滚SEO靠我SQL -- ROLLBACK; P_ERRMSG := 执行错误！ || CHR(13) || 错误代码： || SQLCODE || CHR(13) || 错误信息SEO靠我： || SQLERRM; END SP_MZ_GET_JZFYMX;

如何调整存储过程看下边

DECLARE -- 声明游标变量 P_JIUZHENID SEO靠我VARCHAR2(1000) := 86d26a1a-a448-464c-95dd-acc85ec6906e; P_ID VARCHAR2(1000) := da7dbe52-793aSEO靠我-4120-beb3-c2075cc74a53; P_ERRMSG VARCHAR2(2000); your_cursor SYS_REFCURSOR; SEO靠我 -- 声明结果变量 your_variable1 VARCHAR2(50); your_variable2 VARCHAR2(50); your_vSEO靠我ariable3 VARCHAR2(50); your_variable4 VARCHAR2(50); your_variable5 VARCHAR2(50); SEO靠我 your_variable6 VARCHAR2(50); your_variable7 VARCHAR2(50); BEGIN -- 调用存储SEO靠我过程并传入游标参数 SP_MZ_GET_JZFYMX(P_JIUZHENID,P_ID,P_ERRMSG,your_cursor); -- 循环获取游标结果并展示 SEO靠我 LOOP FETCH your_cursor INTO your_variable1, your_variable2, your_variable3,your_variaSEO靠我ble4,your_variable5,your_variable6,your_variable7; EXIT WHEN your_cursor%NOTFOUND; SEO靠我-- 在这里对每行数据进行展示或其他操作 DBMS_OUTPUT.PUT_LINE(your_variable1 || , || your_variable2 || , || yourSEO靠我_variable3|| , || your_variable4|| , || your_variable5|| , || your_variable6|| , || your_variable7);SEO靠我 END LOOP; -- 关闭游标 CLOSE your_cursor; END; /