KingbaseES存储过程的用法

---

数据库版本：KingbaseES V008R006C008B0014

---

简介

    存储过程是一种强大的数据库编程工具，可以帮助开发人员实现复杂的业务逻辑和数据操作，用于提高数据库的性能、安全性和可维护性。

    存储过程一般是将业务逻辑和数据操作封装起来，以便在需要时重复使用。这样做的好处就是可以减少代码冗余，提高代码的可维护性和可读性。并且它在数据库中预编译，执行速度通常比单独执行SQL语句更快。在存储体中可以包含事务管理逻辑，确保一组数据库操作要么全部成功执行，要么全部失败回滚，这有助于保持数据库的一致性和完整性。

        

文章目录如下

1. 基本语法

1.1. 语法说明

1.2. 声明变量

1.3. 参数传递

1.4. 异常处理

1.5. 其他语法

2. 控制语句

2.1. IF..ELSE

2.2. CASE

3. 循环语句

3.1. FOR 迭代循环

3.2. WHILE 条件循环

3.3. LOOP 无限循环

3.4. 退出循环

---

        

1. 基本语法

1.1. 语法说明

创建存储过程基本语法如下（方括号表示可选）：

CREATE [OR REPLACE] PROCEDURE 存储名称(  --创建存储过程
    [参数列表]    --指定一些参数，包括0个或多个参数
)
AS $$            --将默认分隔符逗号用$$表示(为了避免结束符冲突，所以存储过程结束也使用$$)
[DECLARE]        --用于声明存储过程中使用的变量
    /*变量声明*/
BEGIN            --存储体开始
    /*存储过程主体，包括业务逻辑和SQL语句*/
[EXCEPTION]      --捕获异常
    /*异常处理部分*/
END;             --存储体结束
$$ LANGUAGE 语言;  --语言可以是plsql/plpgsql/sql

        

举一个最简单的例子（创建存储过程）

CREATE PROCEDURE p1()AS $$
BEGIN
    --执行SQL语句
    select 1;
END;
$$ LANGUAGE plsql;

调用存储过程使用 CALL 名称()；

        

1.2. 声明变量

在存储过程中，使用关键字 DECLARE 来声明变量。这个选项是可选的，没有变量可以不用声明。举个例子：

CREATE PROCEDURE p1()AS $$
DECLARE
    --声明变量
    n DECIMAL;
BEGIN
    --将t1表的数据存储到n
    SELECT amount INTO n
    FROM t1
    WHERE xxx = 1;

    --计算n的值后插入到其他表中
    INSERT INTO t2
    VALUES ( n * 0.8 );
END;
$$ LANGUAGE plsql;

为什么要声明变量？

1. 提高可读性，使其他开发人员更容易理解代码。
2. 提高性能，将表达式存储在变量中可以避免重复计算，从而减少了不必要的性能开销。

在声明变量类型时，也可以指定默认值：

CREATE PROCEDURE 名称()AS $$
DECLARE
    --声明默认变量
    n DECIMAL := 1;
BEGIN
    存储体语句
END;
$$ LANGUAGE plsql;

        

1.3. 参数传递

参数是创建存储过程和调用时指定的变量，可以增加存储过程的灵活性和通用性，语法如下：

CREATE PROCEDURE 名称(
    [参数名 参数类型 默认值]
)AS $$
BEGIN
    存储体语句
END;
$$ LANGUAGE plsql;

举个例子，查询 id 为 2 和 name 为 'abc' 的语句

CREATE PROCEDURE p1(
    p_id int,    -- 指定参数
    p_name text  -- 指定参数
)AS $$
BEGIN
    SELECT *
    FROM t1
    WHERE id = p_id AND name = p_name;
END;
$$ LANGUAGE plsql;

        

设置默认值也很简单，在类型后面添加 := 默认值

CREATE PROCEDURE p1(
    p_id int := 10,       -- 指定默认参数
    p_name text := 'abc'  -- 指定默认参数
)AS $$
BEGIN
    存储体语句
END;
$$ LANGUAGE plsql;

        

1.4. 异常处理

当存储过程执行过程中发生错误或异常情况时，通过内置的异常处理方法可以对这些异常情况进行捕获、处理或报告。基本语法如下：

CREATE PROCEDURE p1()AS $$
BEGIN
    <执行SQL语句>
    EXCEPTION              -- 启动异常处理
        WHEN others THEN   -- 如果发生异常
            执行xxx
END;
$$ LANGUAGE plsql;

举个例子：查询 t2 表（如果没有 t2 则表示异常）

CREATE PROCEDURE p1()AS $$
BEGIN
    SELECT * FROM t2;  -- 查询t2表数据
    EXCEPTION          -- 启动异常捕获
        WHEN others THEN
            RAISE EXCEPTION '没有t2表';
END;
$$ LANGUAGE plsql;

        

异常捕获成功后，可以根据不同异常处理不同的操作：

WHEN others THEN              --捕获全部异常
WHEN division_by_zero THEN    --捕获除零异常
WHEN unique_violation THEN    --捕获唯一性约束异常
WHEN INVALID_CURSOR THEN      --捕获无效游标异常
WHEN INVALID_NUMBER THEN      --捕获无效数值异常
WHEN LOGIN_DENIED THEN        --捕获登录被拒绝异常
WHEN PROGRAM_ERROR THEN       --捕获程序错误异常
WHEN ROWTYPE_MISMATCH THEN    --捕获行类型不匹配异常
WHEN STORAGE_ERROR THEN       --捕获存储错误异常
WHEN TIMEOUT_ON_RESOURCE THEN --捕获资源超时异常
WHEN VALUE_ERROR THEN         --捕获数值错误异常

        

1.5. 其他语法

介绍2种好用的语法：

• RAISE <信息类型>：输出调试信息
• EXECUTE IMMEDIATE：动态执行SQL

        

1、RAISE 调试信息：这里的调试方法就相当于编程中的 print，打印指定信息，包括以下类型：

• RAISE INFO：输出一般消息。
• RAISE NOTICE：输出注意信息。
• RAISE WARNING：输出警告信息。
• RAISE EXCEPTION：输出异常信息并中断程序。
• RAISE DEBUG：输出调试信息。

用法（尖括号表示需要替换的内容）：

/* 直接输出字符串 */
RAISE <消息类型> '这是一个梨';

/* 参数化变量值-使用%符号代替 */
RAISE <消息类型> '这个梨的价格: %元', <变量名>;

举个例子：

CREATE PROCEDURE p1(num int)AS $$
BEGIN
    IF num <= 10 THEN
        -- 输出调试信息
        RAISE INFO '向t1表插入一条id为 % 的数据', num;
        INSERT INTO t1(id) VALUES(num);
    ELSE
        -- 输出错误信息，并退出程序
        RAISE EXCEPTION '参数为:%, 大于10, 退出存储过程', num;
    END IF;
END;
$$ LANGUAGE plsql;

        

2、EXECUTE IMMEDIATE 将指定的字符串识别成一条语句执行，举个简单的例子：

CREATE PROCEDURE p1() AS $$
DECLARE
    sql_cmd TEXT;
BEGIN
    -- 将命令定义成变量
    sql_cmd := 'SELECT * FROM t1;';

    -- 将变量识别成动态SQL并执行
    EXECUTE IMMEDIATE sql_cmd;
END;
$$ LANGUAGE plsql;

理解了 EXECUTE IMMEDIATE 的基本用法后，我们可以使用连接符（||）来拼接不同的字符串。

比如创建100张表：

CREATE PROCEDURE p1() AS $$
DECLARE
    sql_cmd TEXT;
BEGIN
    FOR i IN 1..100 LOOP
        -- 将命令定义成变量
        sql_cmd := 'CREATE TABLE t' || i || '(id INT, name TEXT);';

        -- 将变量识别成动态SQL并执行
        EXECUTE IMMEDIATE sql_cmd;
    END LOOP;
END;
$$ LANGUAGE plsql;

        

2. 控制语句

2.1. IF..ELSE

if 语句可以根据不同的条件，执行不同的代码块。语法如下：

IF <条件1> THEN
    执行符合条件1的代码
ELSIF <条件2> THEN    -- 这块代码是可选项
    执行符合条件2的代码
ELSE
    执行不符合条件1、条件2的代码
END IF;

举个例子：

CREATE PROCEDURE p1(num INT) AS $$
BEGIN
    IF num <= 10 THEN
        RAISE INFO '参数num<=10';
    ELSIF num <= 20 THEN
        RAISE INFO '参数 10<num<=20';
    ELSIF num <= 30 THEN
        RAISE INFO '参数 20<num<=30';
    ELSE
        RAISE INFO '参数 num>30';
    END IF;
END;
$$ LANGUAGE plsql;

        

2.2. CASE

case 类似于 switch 语句，根据表达式的值选择执行不同的代码块。语法如下：

CASE 表达式
	WHEN '<等于条件1>' THEN
		执行条件1代码
	WHEN '<等于条件2>' THEN
		执行条件2代码
	WHEN '<等于条件3>' THEN
		执行条件3代码
	ELSE
		执行不满足条件1/2/3的代码
END CASE;

案例一

CREATE PROCEDURE p1(str TEXT) AS $$
BEGIN
    CASE str
        WHEN 'A' THEN
            RAISE NOTICE '执行A计划...';
        WHEN 'B' THEN
            RAISE NOTICE '执行B计划...';
        WHEN 'C' THEN
            RAISE NOTICE '执行C计划...';
        ELSE
            RAISE NOTICE '执行X计划...';
    END CASE;
END;
$$ LANGUAGE plsql;

        

案例二

CREATE PROCEDURE p1(num INT) AS $$
BEGIN
    CASE
        WHEN num <= 10 THEN
            RAISE INFO '参数num<=10';
        WHEN num <= 20 THEN
            RAISE INFO '参数 10<num<=20';
        WHEN num <= 30 THEN
            RAISE INFO '参数 20<num<=30';
        ELSE
            RAISE INFO '参数 num>30';
    END CASE;
END;
$$ LANGUAGE plsql;

        

3. 循环语句

3.1. FOR 迭代循环

FOR循环是一种常见用于遍历结果集或执行特定的迭代操作。语法如下：

FOR 赋值变量 IN <迭代对象> LOOP
    执行语句;
END LOOP;

        

【案例一】插入10000条数据

CREATE PROCEDURE p1() AS $$
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO t1 VALUES(i, 'abc', clock_timestamp());
    END LOOP;
END;
$$ LANGUAGE plsql;

        

【案例二】遍历查询结果

CREATE PROCEDURE p1() AS $$
BEGIN
    FOR i IN (select * from t1) LOOP
        RAISE INFO 't1数据: %', i;
    END LOOP;
END;
$$ LANGUAGE plsql;

        

3.2. WHILE 条件循环

WHILE 循环可以在满足指定条件的情况下重复执行一段代码块。语法如下：

WHILE <条件> LOOP
    代码块
END LOOP;

这里的条件是指判断条件为真才执行下面的代码块，为假则不执行

举个例子，条件为真（true）

CREATE PROCEDURE p1() AS $$
BEGIN
    -- 循环条件为真
    WHILE true LOOP
        -- 执行代码块
        RAISE INFO '循环...';
        SELECT sys_sleep(1);
    END LOOP;
END;
$$ LANGUAGE plsql;

        

通过判断真假循环，可以用于判断一个数值大小

n := 1;

-- n<10时，循环执行代码
WHILE n < 10 LOOP
    代码块
    -- 每执行n+1
    n := n+1
END LOOP;

同理，可以通过判断时间来循环

-- 2024-01-01 12:00:00 之前一直循环
WHILE clock_timestamp() < '2024-01-01 12:00:00' LOOP
    代码块
END LOOP;

        

3.3. LOOP 无限循环

我们知道，在使用 for 循环或 while 循环时，语句后面都必须加上 loop 关键字，这个关键字用于表示循环。那么不使用 for 或 while，直接使用 loop 则表示无限循环。语法如下：

LOOP
    代码块
END LOOP;

无限循环大部分用于检查数据，举个例子：

LOOP
    -- 执行检查语句
    RAISE INFO '数据xxx = xxx';
    -- 设置等待时间(s), 每隔n秒检查1次
    SELECT sys_sleep(1);
END LOOP;

        

3.4. 退出循环

循环体中通过2种方式退出循环（可用于 for、while、loop 循环）：

• CONTINUE： 跳出当前循环
• EXIT：跳出整个循环

以下用 for 循环举例：

1、跳出当前循环（CONTINUE）

CREATE PROCEDURE p1() AS $$
BEGIN
    FOR i IN 1..5 LOOP
        -- 如果i=3，跳出当前循环
        IF i = 3 THEN
            CONTINUE;
        END IF;
        -- 执行其他代码
        RAISE INFO '正在循环第%次！', i;
    END LOOP;
END;
$$ LANGUAGE plsql;

跳出当前循环表示此次循环结束，继续循环剩下的迭代 

        

2、跳出整个循环（EXIT）

CREATE PROCEDURE p1() AS $$
BEGIN
    FOR i IN 1..5 LOOP
        -- 如果i=3，跳出整个循环
        IF i = 3 THEN
            EXIT;
        END IF;
        -- 执行其他代码
        RAISE INFO '正在循环第%次！', i;
    END LOOP;
END;
$$ LANGUAGE plsql;

跳出整个循环就是将剩下的循环也停止