数据库版本:KingbaseES V008R006C008B0014
简介
存储过程是一种强大的数据库编程工具,可以帮助开发人员实现复杂的业务逻辑和数据操作,用于提高数据库的性能、安全性和可维护性。
存储过程一般是将业务逻辑和数据操作封装起来,以便在需要时重复使用。这样做的好处就是可以减少代码冗余,提高代码的可维护性和可读性。并且它在数据库中预编译,执行速度通常比单独执行SQL语句更快。在存储体中可以包含事务管理逻辑,确保一组数据库操作要么全部成功执行,要么全部失败回滚,这有助于保持数据库的一致性和完整性。
文章目录如下
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;
为什么要声明变量?
- 提高可读性,使其他开发人员更容易理解代码。
- 提高性能,将表达式存储在变量中可以避免重复计算,从而减少了不必要的性能开销。
在声明变量类型时,也可以指定默认值:
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;
跳出整个循环就是将剩下的循环也停止