stored procedure

点击查看代码

# oracle存储过程

# 测试用 sql 

   ```sql
   
      CREATE TABLE TB_A (
       ID VARCHAR2 ( 255 ) NOT NULL ENABLE,
       NAME VARCHAR2 ( 255 ),
       AGE NUMBER,
       CHECK ( ID IS NOT NULL ) ENABLE,
      PRIMARY KEY ( ID ) USING INDEX  
      );  
        
      INSERT INTO "MY_PDB"."TB_A" ("ID", "NAME", "AGE") VALUES ('1', 'JACK', '18');
      INSERT INTO "MY_PDB"."TB_A" ("ID", "NAME", "AGE") VALUES ('2', 'ROSE', '16');

      CREATE TABLE TB_B (
       ID VARCHAR2 ( 255 ) NOT NULL ENABLE,
       NAME VARCHAR2 ( 255 ),
       AGE NUMBER,
       CHECK ( ID IS NOT NULL ) ENABLE,
      PRIMARY KEY ( ID ) USING INDEX  
      );  
        

创建存储过程

   CREATE OR REPLACE PROCEDURE P_1 AS 
   BEGIN
   DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
   END;

调用存储过程

CALL P_1();

删除存储过程

DROP PROCEDURE P_1;

参数

参数模式

IN

1. 只读,存储过程可以读取参数的值,但不可以修改它
2. 参数可以是常量,变量,表达式

OUT

1. 只写,存储过程不可以读取参数的值,但可以修改它(即: 给输入变量赋值),并返回值给调用者
2. 参数只能是变量

IN OUT

1. 读写,存储过程可以读取参数的值,也可以修改它(即: 给输入变量赋值),并返回值给调用者
2. 参数只能是变量

格式

存储过程名称(参数1 参数1模式 参数1数据类型, 参数2 参数2模式 参数2数据类型)

IN

   -- 创建存储过程
   CREATE OR REPLACE PROCEDURE TEST_IN(
     P_ID IN VARCHAR2 -- 使用P_ID避免参数名冲突
   ) AS 
     V_NAME VARCHAR2(255); 
   BEGIN
     SELECT NAME INTO V_NAME  
     FROM TB_A 表名需加引号
     WHERE ID = P_ID;  
     
     -- 打印
     DBMS_OUTPUT.PUT_LINE('Name: ' || V_NAME);
   END;
   
   
   -- 调用存储过程
   CALL TEST_IN('1');
   

OUT

   -- 创建存储过程
   CREATE OR REPLACE PROCEDURE TEST_OUT(
     P_ID IN VARCHAR2,
     V_NAME OUT TB_A.NAME%TYPE) AS
   BEGIN
     SELECT NAME INTO V_NAME FROM TB_A WHERE ID = P_ID;
     DBMS_OUTPUT.PUT_LINE('Name：'||V_NAME); -- 打印
   END;
    
   -- 调用存储过程  
   DECLARE
     V_NAME VARCHAR2(255); -- 声明输出变量
   BEGIN
     TEST_OUT('1',V_NAME); -- 调用存储过程: TEST_OUT
     DBMS_OUTPUT.PUT_LINE('TEST_OUT：'||V_NAME); -- 打印
   END;

IN OUT

   -- 创建存储过程
   CREATE OR REPLACE PROCEDURE TEST_IN_OUT(P_ID IN OUT VARCHAR2)
   IS
   BEGIN
     DBMS_OUTPUT.PUT_LINE('TEST_IN_OUT 入参 P_ID: ' || P_ID);
     P_ID := '2';
   END;
    
   -- 调用存储过程
   DECLARE
     P_ID VARCHAR2(255) := '1'; -- 声明输出变量
   BEGIN
     TEST_IN_OUT(P_ID); -- 调用存储过程: TEST_IN_OUT
     DBMS_OUTPUT.PUT_LINE('TEST_IN_OUT 出参 P_ID: ' || P_ID);
   END;

IF

   CREATE OR REPLACE PROCEDURE TEST_IF(P_ID VARCHAR2) AS 
     V_NAME VARCHAR2(255);
   BEGIN
     IF(P_ID = '1') THEN
        SELECT NAME INTO V_NAME FROM TB_A WHERE ID = '1';
     ELSE
        SELECT NAME INTO V_NAME FROM TB_A WHERE ID = '2';
     END IF;
     DBMS_OUTPUT.PUT_LINE('ID：' || P_ID || '，NAME：' || V_NAME);
   END;
   
   
   CALL TEST_IF('1');

循环

WHILE

   CREATE OR REPLACE PROCEDURE TEST_WHILE AS 
     I INT := 0;
   BEGIN 
     WHILE I<10 LOOP
       DBMS_OUTPUT.PUT_LINE(I);
   		I := I + 1;
     END LOOP;
   END;
   
   CALL TEST_WHILE();

FOR

   CREATE OR REPLACE PROCEDURE TEST_FOR AS 
   BEGIN
     FOR I IN 1 .. 5 LOOP
       DBMS_OUTPUT.PUT_LINE(I);
     END LOOP;
   END;
   
   CALL TEST_FOR();

LOOP

略.

CURSOR 游标

游标(Cursor)是Oracle数据库中用于处理SQL查询结果集的一种机制，它允许开发者逐行访问查询返回的结果集。
在存储过程中，游标是实现复杂数据处理逻辑的重要工具。

   CREATE OR REPLACE PROCEDURE TEST_CURSOR IS
     CURSOR cur IS SELECT * FROM TB_A;
   BEGIN
     -- 使用 for 循环遍历游标时,不能使用 open、fetch、close 语句,因为 for 循环已经隐式包含这些操作了
     FOR item IN cur LOOP
       DBMS_OUTPUT.PUT_LINE('ROWCOUNT: ' || cur%ROWCOUNT || ',ID: ' || item."ID" || ',NAME: ' || item.NAME);
     END LOOP;
   END;
   
   
   CALL TEST_CURSOR();

同步数据

   CREATE OR REPLACE PROCEDURE TEST_INSERT AS
   BEGIN
     INSERT INTO TB_B (ID, NAME, AGE) 
   	 SELECT ID, NAME, AGE  FROM TB_A;
   END;
   
   
   CALL TEST_INSERT();
   

</details>