GaussDB存储过程及函数介绍

存储过程和函数是 GaussDB（以及大多数关系型数据库）中非常重要的功能，它们允许您将一系列 SQL 语句和逻辑封装在数据库服务器端，从而带来诸多好处。

一、核心概念与区别

在深入了解之前，首先要明确存储过程和函数的区别，这对于正确使用它们至关重要。

含义不同：

存储过程是SQL语句和可控制流程语句的预编译集合；

函数是有一个或多个SQL语句组成的子程序；

使用条件不同：

存储过程：可以在单个存储过程中执行一系列SQL语句。而且可以从自己的存储过程内引入其他存储过程，这可以简化一系列复杂的语句；

函数：自定义函数有着诸多限制，有许多语句不能使用，例如临时表。

执行方式不同：

存储过程：存储过程可以返回参数，如记录集，存储过程声明时不需要返回类型

函数：函数只能返回值或表对象，声明时需要描述返回类型，且函数中必须包含一个有效return语句。

简单来说：如果你需要一个能返回结果并在 SQL 里使用的，就用函数；如果你要执行一个包含多个步骤、可能需要提交或回滚事务的操作，就用存储过程。

二、存储过程 ( Procedure)

商业规则和业务逻辑可以通过程序存储在GaussDB中，这个程序就是存储过程。存储过程是SQL和PL/SQL的组合。通过存储过程，执行商业规则的代码可以从应用程序层迁移至到数据库层，从而实现代码复用，即代码只需存储一次即可被多个程序调用。

1. 创建语法（简化版）

CREATE [OR REPLACE] PROCEDURE procedure_name (
    [ [IN | OUT | INOUT] parameter_name data_type [, ...] ]
)AS 
DECLARE
    -- 声明部分（可选）：定义局部变量
BEGIN
    -- 执行部分：核心业务逻辑
    -- 可以包含：SQL语句、流程控制（IF, LOOP）、变量赋值等。
END;
/

2. 参数模式

• IN (默认)：输入参数，供调用者传入值。
• OUT：输出参数，在过程执行结束后将值传递回调用者。
• INOUT：兼具输入和输出功能的参数。

3. 示例

示例1：简单的无返回存储过程

-- 创建存储过程插入数据
create or replace procedure create_student_information(num int)
as 
declare i int;
id int;
begin
    id :=100000;
    for i in 1..num loop
        dbe_output.print_line('id='||id); 
        insert into student values(id,sysdate);
        id :=id+1;
    end loop;
    end;
/

-- 调用
CALL create_student_information(100); 

示例2：带有 OUT 参数的存储过程

-- 创建存储过程pro_curs_1，打印各部门总人数
CREATE OR REPLACE PROCEDURE pro_curs_1()
AS
BEGIN
    DECLARE
        -- 声明游标
        CURSOR dept_cursor IS
            SELECT d.NAME AS dept_name, COUNT(t.ID) AS teacher_count
            FROM DEPARTMENT d
            LEFT JOIN TEACHER t ON d.ID = t.DEPTNO
            GROUP BY d.ID, d.NAME
            ORDER BY teacher_count DESC;
        
        v_dept_name DEPARTMENT.NAME%TYPE;
        v_teacher_count INTEGER;
    BEGIN
        -- 打开游标
        OPEN dept_cursor;
        
        LOOP
            -- 获取游标数据
            FETCH dept_cursor INTO v_dept_name, v_teacher_count;
            EXIT WHEN dept_cursor%NOTFOUND;
            
            -- 使用指定格式打印结果
            DBE_OUTPUT.PRINT_LINE(v_dept_name || '---' || v_teacher_count);
        END LOOP;
        
        -- 关闭游标
        CLOSE dept_cursor;
    END;
END;
/

-- 取薪水降序排序的前三位老师和后三位老师的信息
create or replace procedure pro_curs_2()
as 
declare cursor cur2 is select t.id as sid,t.name as sname, d.name as dname,salary,title from ((select * from teacher order by salary desc limit 3) union all (select * from teacher order by salary limit 3)) t join department d on t.deptno=d.id;
sid integer;
sname varchar(50);
dname varchar(50);
salary float;
title varchar(50);
begin 
    open cur2; 
    loop
        fetch cur2 into sid,sname,dname,salary,title;
        EXIT WHEN cur2%NOTFOUND;
        DBE_OUTPUT.PRINT_LINE(sid||'-'||sname||'-'||dname||'-'||salary||'-'||title);
    end loop;
END;
/

三、函数 (Function)

1. 创建语法（简化版）

CREATE [OR REPLACE] FUNCTION function_name (
    [ [IN] parameter_name data_type [, ...] ] -- IN是默认的，可省略
)
RETURNS return_data_type -- 必须指定返回类型
LANGUAGE lang_name -- 同样是 plpgsql
AS $$
DECLARE
    -- 声明部分（可选）
BEGIN
    -- 执行部分
    -- 必须包含 RETURN 语句
    RETURN value;
END;
$$;

2. 示例

示例1：简单的计算函数

-- 计算税后工资的函数
CREATE OR REPLACE FUNCTION calculate_net_salary(
    gross_salary numeric,
    tax_rate numeric
) RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN gross_salary * (1 - tax_rate);
END;
$$;

-- 在SELECT语句中调用
SELECT name, salary, calculate_net_salary(salary, 0.2) AS net_salary
FROM employees;

示例2：返回复杂类型（表）的函数

-- 返回某个学生的分数总和
create or replace function FUNC_SUM(stuid int) returns integer
as
$$
declare result integer;
begin
	select sum(grade) into result from elective where sno=stuid;
	return result;
end;
$$ language plpgsql;

-- 不使用索引
	explain analyze select /* + tablescn(tb_user)  */ * from tb_user where stu_no=100 and age=29;
	set cpu_index_tuple_cost = 100000;
	alter index age_no,age_name unusable;
	set enable_indexscan=off

四、优势与适用场景

优势：

1.存储过程极大的提高SQL语言和灵活性，可以完成复杂的运算

2.可以保障数据的安全性和完整性

3.极大的改善SQL语句的性能，在运行存储过程之前，数据库已对其进行语法和句法分析，并给出优化执行优化方案。这种已经编译好的过程极大地改善了SQL语句性能。

4.可以降低网络的通信量，客户端通过调用存储过程只需要存储过程名和相关参数即可，与传输SQL语句相比自然数据量少很多。

适用场景：

• 存储过程：定时任务（如每日报表生成）、数据初始化、批量数据更新/清理、需要事务保证的多步操作。
• 函数：数据校验规则、复杂的计算公式（如金融计算）、数据格式化、动态SQL构造、作为视图或触发器的组成部分。

五、注意事项

1. 调试：存储过程和函数的调试比应用代码更复杂，需要借助数据库特定的工具（如 GaussDB 的调试器）。
2. 版本管理：它们作为数据库对象，其版本管理需要纳入整体的数据库变更管理（DevOps）流程，与应用代码版本协同。
3. 可移植性：使用 PL/pgSQL 编写的代码在 PostgreSQL 和 GaussDB（兼容模式下）之间有较好的可移植性，但切换到其他数据库（如 Oracle, MySQL）可能需要重写。
4. 性能权衡：并非所有逻辑都适合放在数据库中。简单的 CRUD 操作或需要与复杂应用状态紧密耦合的逻辑，放在应用层可能更合适。