鲲鹏社区首页
开发者
资源
支持
积分兑换 NEW
博客
opengauss 存储过程
opengauss 存储过程
发表于2024/05/18
1293

存储过程

存储过程是一组SQL语句和逻辑控制的集合。数据库系统需要支持创建、删除和修改存储过程的语法。

基本语法

CREATE [OR REPLACE] PROCEDURE 存储过程名(
    [IN|OUT] 参数1数据类型,
    [IN|OUT] 参数2数据类型,
    ...
)
AS
DECLARE
    变量1数据类型,
    变量2数据类型,
...
BEGIN
    存储过程的程序体
END;

# IN参数是存储过程的输入参数，它将存储过程外部的值传递给存储过程使用。
# OUT参数是存储过程的输出参数，存储过程在执行时，会将执行的中间结果赋值给OUT参数，存储过程执行完毕后，外部用户可以通过OUT参数获得存储过程的执行结果。
# INOUT参数则同时具有IN参数和OUT参数的性质，它既是存储过程的输入参数，同时在存储过程执行中也会通过INOUT参数将中间结果输出给外部用户

示例：

# 创建存储过程 （存储过程也可以分为带参数的存储过程和不带参数的存储过程）
CREATE OR REPLACE PROCEDURE p1 IS   
BEGIN
INSERT INTO t1 VALUES(1,'李明');
COMMIT;
END;
/
# 查看存储过程
\df # 可以查看自定义的存储过程和函数
\sf  # 查看存储过程或哈函数语句
select proname,prsrc from pg_proc where proname='p1';  # 查看指定存储过程语句
# 调用存储过程
call p1()  # 如果有参数要写参数到括号里
# 删除存储过程
drop procedure p1;

# 创建带参数的存储过程 计算两数乘积
create or replace procedure p2(param1 in integer,param2 in out integer)
as
begin
  param2:= param1 * param2;
  RAISE INFO 'param2 is: %', param2;
end;
/

匿名块

匿名块一般用于不频繁执行的脚本或不重复进行的活动。它们在一个会话中执行，并不被存储。与此相对的是命名块（也就是存储过程和函数）。

DO 语句用于执行匿名块；

# 我们定义了一个字符串变量 name，然后给它赋值并输出一个信息；RAISE NOTICE 用于输出通知消息。
openGauss=# DO $$ 
DECLARE
   name text;
BEGIN 
   name := 'PL/SQL';
   RAISE NOTICE 'Hello %!', name;
END $$;

NOTICE:Hello PL/SQL!
ANONYMOUS BLOCK EXECUTE

嵌套子块

PL/SQL 支持代码块的嵌套，也就是将一个代码块嵌入其他代码块的主体中。

被嵌套的代码块被称为子块（subblock），包含子块的代码块被称为外部块（subblock）。

子块可以将代码进行逻辑上的拆分，子块中可以定义与外部块重名的变量，而且在子块内拥有更高的优先级。

openGauss=# DO $$ 
DECLARE
 name text;
BEGIN 
 name := 'outer_block';
 RAISE NOTICE 'This is %', name;
 DECLARE 
 name text := 'sub_block';
 BEGIN 
 RAISE NOTICE 'This is %', name;
 RAISE NOTICE 'The name FROM the outer block is %',name;
 END;
 RAISE NOTICE 'This is %', name;
END $$;

NOTICE:  This is outer_block
NOTICE:  This is sub_block
NOTICE:  The name FROM the outer block is sub_block
NOTICE:  This is outer_block
ANONYMOUS BLOCK EXECUTE

条件语句

条件语句的主要作用是判断参数或者语句是否满足已给定的条件，根据判定结果执行相应的操作。

# 匿名块条件语句示例  比较两数大小
openGauss=# DO $$
DECLARE
  i integer := 4;   
  j integer := 5;    
BEGIN
  IF i > j THEN 
    RAISE NOTICE 'i 大于 j';
  ELSIF i < j THEN
    RAISE NOTICE 'i 小于 j';
  ELSE
    RAISE NOTICE 'i 等于 j';
  END IF;
END $$;
ENDNOTICE:  i 小于 j
ANONYMOUS BLOCK EXECUTE

# 存储过程示例 判断这个数是否大于0
CREATE OR REPLACE PROCEDURE proc_control_structure(i in integer) 
AS
    BEGIN
        IF i > 0 THEN
            raise info 'i:% is greater than 0. ',i; 
        ELSIF i < 0 THEN
            raise info 'i:% is smaller than 0. ',i; 
        ELSE
            raise info 'i:% is equal to 0. ',i; 
        END IF;
        RETURN;
    END;
/

CALL proc_control_structure(3);

case 语句

也成为分支语句

简单 CASE 语句只能进行简单的等值比较。

依次判断每个 WHEN 之后的表达式，如果为真则执行相应的语句，后续的分支不再进行判断

openGauss=#CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer) 
AS 
    BEGIN 
        CASE pi_result 
            WHEN 1 THEN 
                pi_return := 111; 
            WHEN 2 THEN 
                pi_return := 222; 
            WHEN 3 THEN 
                pi_return := 333; 
            WHEN 6 THEN 
                pi_return := 444; 
        END CASE; 
        raise info 'pi_return : %',pi_return ; 
END; 
/
finance=# call proc_case_branch(3,0);
INFO:  pi_return : 333
 pi_return 
-----------
       333
(1 row)

搜索CASE语句

语句可以实现更复杂的控制逻辑。

依次判断每个 WHEN 之后的表达式，如果为真则执行相应的语句，后续的分支不再进行判断，如果没有匹配的值，执行ELSE语句。

openGauss=# DO $$
DECLARE
 i integer := 3;
BEGIN 
 CASE 
    WHEN i BETWEEN 1 AND 5 THEN
    RAISE NOTICE '数据在1~5之间';
    WHEN i BETWEEN 6 AND 10 THEN
    RAISE NOTICE '数据在6~10之间';
 ELSE
    RAISE NOTICE 'other value';
 END CASE;
END $$;

NOTICE:  数据在1~5之间
ANONYMOUS BLOCK EXECUTE

# 存储过程
CREATE OR REPLACE PROCEDURE check_range()
AS
DECLARE
    i INTEGER := 3;
BEGIN
    CASE 
        WHEN i BETWEEN 1 AND 5 THEN
            RAISE NOTICE '数据在1~5之间';
        WHEN i BETWEEN 6 AND 10 THEN
            RAISE NOTICE '数据在6~10之间';
        ELSE
            RAISE NOTICE 'other value';
    END CASE;
END;
/
# 自己输入参数
CREATE OR REPLACE PROCEDURE check_range(in_num INTEGER)
AS
BEGIN
    CASE 
        WHEN in_num BETWEEN 1 AND 5 THEN
            RAISE NOTICE '数据在1~5之间';
        WHEN in_num BETWEEN 6 AND 10 THEN
            RAISE NOTICE '数据在6~10之间';
        ELSE
            RAISE NOTICE 'other value';
    END CASE;
END;
/

循环语句

loop语句

# 简单的LOOP
CREATE OR REPLACE PROCEDURE proc_loop(i in integer, count out integer) 
AS 
    BEGIN 
        count:=0; 
        LOOP 
        IF count > i THEN 
            raise info 'count is %. ', count;  
            EXIT; 
        ELSE 
            count:=count+1; 
        END IF; 
        END LOOP; 
    END;
CALL proc_loop(10,5);

# while_loop 语句
# 只要条件表达式为真，WHILE语句就会不停的在一系列语句上进行循环，在每次进入循环体的时候进行条件判断。

游标

游标CURSOR是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括声明，OPEN,FETCH,CLOSE

其语法分别如下：

# 声明
DECLARE 游标名称 CURSOR FOR 查询语句；
# 打开游标
OPEN 游标名称；
# 获取游标记录
FETCH 游标名称 INTO 变量[，变量];
# 关闭游标
CLOSE 游标名称

游标案例：

-- 创建示例用户表
CREATE TABLE td_user (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    profession VARCHAR(100)
);

-- 向td_user表中插入示例数据
INSERT INTO td_user (name, age, profession) VALUES
('Alice', 25, 'Engineer'),
('Bob', 30, 'Doctor'),
('Charlie', 20, 'Artist'),
('David', 35, 'Teacher'),
('Eve', 28, 'Software Developer');
# 定义存储过程完成以下要求
# 根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
逻辑：
--A. 声明游标，存储过程查询结果集
--B. 准备：创建表结构
--C. 开启游标
--D. 获取游标中的记录
--E. 插入数据到新表中
--F. 关闭游标
CREATE OR REPLACE PROCEDURE p11(IN uage INT)
AS
DECLARE 
    uname VARCHAR(100);
    upro VARCHAR(100);
    
    CURSOR u_cursor FOR SELECT name, profession FROM td_user WHERE age <= uage;
    
BEGIN
    -- Drop table if exists
    DROP TABLE IF EXISTS tb_user_pro;

    -- Create table if not exists
    CREATE TABLE IF NOT EXISTS tb_user_pro (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        profession VARCHAR(100)
    );
    
    -- Open the cursor
    OPEN u_cursor;
    
    -- Loop through cursor
    LOOP
        FETCH u_cursor INTO uname, upro;
        
        -- Check if no more rows to fetch
        IF NOT FOUND THEN
            EXIT;
        END IF;
        
        -- Insert into tb_user_pro
        INSERT INTO tb_user_pro VALUES (DEFAULT, uname, upro);
    END LOOP;
    
    -- Close the cursor
    CLOSE u_cursor;
    
END;
# 调用存储过程
CALL p11(30);
SELECT * FROM tb_user_pro;

# 案例二
CREATE TABLE emp
   (    
    "EMPLOYEE_ID" NUMBER(10,0), 
    "FIRST_NAME" VARCHAR2(20), 
    "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE, 
    "SALARY" NUMBER(8,2)
   )
insert into emp values(100,'ticle','xie',10000);
insert into emp values(101,'tina','wang',12000);   
create or replace procedure p_test (out EMPLOYEE_ID int,out FIRST_NAME varchar(20),out LAST_NAME varchar(20),out SALARY int)
is 
declare
    cursor mycur for
    select * from emp;
    empid int;
    firstname varchar(20);
    lastname varchar(20);
    sal int;
begin
    open mycur;
    fetch mycur into empid,firstname,lastname,sal;
    EMPLOYEE_ID:=empid;
    FIRST_NAME:=firstname;
    LAST_NAME:=lastname;
    SALARY:=sal;
    close mycur;
end
/

openGauss=# call p_test(:EMPLOYEE_ID,:FIRST_NAME,:LAST_NAME,:SALARY);
 employee_id | first_name | last_name | salary 
-------------+------------+-----------+--------
         100 | ticle      | xie       |  10000
(1 row)

# 删除员工表hr.staffs表中某部门的所有员工，如果该部门中已没有员工，则在部门表hr.sections中删除该部门。
CREATE OR REPLACE PROCEDURE proc_cursor3() 
AS 
    DECLARE
    V_DEPTNO NUMBER(4) := 100;
    BEGIN
        DELETE FROM hr.staffs WHERE section_ID = V_DEPTNO;
        --根据游标状态做进一步处理
        IF SQL%NOTFOUND THEN
        DELETE FROM hr.sections WHERE section_ID = V_DEPTNO;
        END IF;
    END;
/

CALL proc_cursor3();

错误捕获

默认情况下，存储过程遇到错误时会终止代码执行，同时撤销事务。在代码块中使用EXCEPTION捕获错误并继续事务。

如果代码执行出错，程序将会进入 EXCEPTION 模块。

openGauss=# CREATE OR REPLACE PROCEDURE proc_exp()
AS
DECLARE
    x INT :=0;
    y INT;
BEGIN
    UPDATE employees SET first_name = 'James' WHERE first_name = 'John';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero'; 
        RAISE NOTICE 'x: %',x;
        RETURN ;
END;
/
CREATE PROCEDURE

openGauss=# call proc_exp();
NOTICE:  caught division_by_zero
NOTICE:  x: 1

更多的存储过程循环案例去官方文档 SQL参考 找到存储过程 或直接搜索循环

收藏举报
Level 1
0
帖子
0
粉丝
0
获赞
我们使用cookie来确保您的高速浏览体验。继续浏览本站，即表示您同意我们使用cookie。查看详情