GaussDB存储过程详解

引言

在当今数据驱动的时代，数据库性能和应用开发效率成为企业关注的重点。GaussDB作为华为自主研发的企业级分布式数据库，其存储过程功能为复杂数据处理提供了强大的解决方案。本文将深入探讨GaussDB存储过程的各个方面，帮助开发者充分利用这一强大工具。

一、存储过程基础概念

1.1 什么是存储过程？

存储过程是一组预编译的SQL语句集合，存储在数据库服务器端，可以通过指定的名称和参数进行调用。它类似于编程语言中的函数或方法，但直接在数据库层面执行。

1.2 存储过程的核心价值

性能优化：减少网络传输，预编译执行

代码复用：一次编写，多次调用

业务封装：隐藏复杂逻辑，简化应用层开发

安全控制：通过权限管理保护敏感数据

二、GaussDB存储过程语法详解

2.1 基本创建语法

CREATE [OR REPLACE] PROCEDURE procedure_name
(
    parameter_name [IN | OUT | IN OUT] data_type [DEFAULT value],
    ...
)
[LANGUAGE SQL | LANGUAGE PLPGSQL]
AS $$
DECLARE
    -- 变量声明
    variable_name data_type [DEFAULT value];
BEGIN
    -- 存储过程主体
    -- 业务逻辑代码
EXCEPTION
    -- 异常处理
END;
$$;

2.2 参数模式深度解析

IN 参数（输入参数）
CREATE OR REPLACE PROCEDURE add_employee(
    p_emp_name IN VARCHAR2,
    p_salary IN NUMBER DEFAULT 5000,
    p_dept_id IN NUMBER
)
AS $$
BEGIN
    INSERT INTO employees (emp_name, salary, dept_id, hire_date)
    VALUES (p_emp_name, p_salary, p_dept_id, SYSDATE);
END;
$$;
OUT 参数（输出参数）
CREATE OR REPLACE PROCEDURE get_employee_stats(
    p_dept_id IN NUMBER,
    p_emp_count OUT NUMBER,
    p_avg_salary OUT NUMBER,
    p_max_salary OUT NUMBER
)
AS $$
BEGIN
    SELECT COUNT(*), AVG(salary), MAX(salary)
    INTO p_emp_count, p_avg_salary, p_max_salary
    FROM employees
    WHERE dept_id = p_dept_id;
END;
$$;
IN OUT 参数（输入输出参数）
CREATE OR REPLACE PROCEDURE update_salary_with_bonus(
    p_emp_id IN NUMBER,
    p_bonus_percentage IN OUT NUMBER
)
AS $$
    v_current_salary NUMBER;
BEGIN
    -- 获取当前薪资
    SELECT salary INTO v_current_salary
    FROM employees WHERE emp_id = p_emp_id;   

-- 调整奖金比例（业务逻辑）
IF v_current_salary > 10000 THEN
    p_bonus_percentage := p_bonus_percentage * 0.8;
END IF;

-- 更新薪资
UPDATE employees 
SET salary = salary * (1 + p_bonus_percentage / 100)
WHERE emp_id = p_emp_id;
END;
$$;

三、高级特性与实战应用

3.1 复杂业务逻辑处理

CREATE OR REPLACE PROCEDURE process_monthly_payroll(
    p_month IN NUMBER,
    p_year IN NUMBER
)
AS $$
DECLARE
    CURSOR emp_cursor IS
        SELECT emp_id, base_salary, department, performance_rating
        FROM employees 
        WHERE status = 'ACTIVE';
        v_bonus_rate NUMBER;
v_total_payout NUMBER := 0;
v_processed_count NUMBER := 0;

BEGIN
– 记录开始时间
INSERT INTO payroll_log (log_message, log_time)
VALUES (‘开始处理’ || p_year || ‘年’ || p_month || ‘月薪资’, SYSDATE);

FOR emp_rec IN emp_cursor LOOP
    -- 根据绩效计算奖金比例
    CASE emp_rec.performance_rating
        WHEN 'A' THEN v_bonus_rate := 0.2;
        WHEN 'B' THEN v_bonus_rate := 0.1;
        WHEN 'C' THEN v_bonus_rate := 0.05;
        ELSE v_bonus_rate := 0.02;
    END CASE;
    
    -- 计算总薪资
    DECLARE
        v_total_salary NUMBER := emp_rec.base_salary * (1 + v_bonus_rate);
    BEGIN
        -- 插入薪资记录
        INSERT INTO payroll_records (
            emp_id, payroll_month, payroll_year, 
            base_salary, bonus_rate, total_salary, process_date
        ) VALUES (
            emp_rec.emp_id, p_month, p_year,
            emp_rec.base_salary, v_bonus_rate, v_total_salary, SYSDATE
        );
        
        v_total_payout := v_total_payout + v_total_salary;
        v_processed_count := v_processed_count + 1;
        
        -- 每处理100条记录提交一次
        IF MOD(v_processed_count, 100) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('已处理 ' || v_processed_count || ' 条记录');
        END IF;
    END;
END LOOP;

-- 最终提交
COMMIT;

-- 记录处理结果
INSERT INTO payroll_log (log_message, log_time)
VALUES ('薪资处理完成，共处理 ' || v_processed_count || 
        ' 名员工，总支出: ' || v_total_payout, SYSDATE);

DBMS_OUTPUT.PUT_LINE('月度薪资处理完成');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        INSERT INTO payroll_log (log_message, log_time)
        VALUES ('薪资处理失败: ' || SQLERRM, SYSDATE);
        RAISE;
END;
$$;

3.2 动态SQL在存储过程中的应用

sql
CREATE OR REPLACE PROCEDURE dynamic_query_example(
    p_table_name IN VARCHAR2,
    p_where_condition IN VARCHAR2 DEFAULT NULL,
    p_result OUT SYS_REFCURSOR
)
AS $$
    v_sql VARCHAR2(4000);
BEGIN
    v_sql := 'SELECT * FROM ' || p_table_name;

IF p_where_condition IS NOT NULL THEN
    v_sql := v_sql || ' WHERE ' || p_where_condition;
END IF;

-- 使用动态SQL打开游标
OPEN p_result FOR v_sql;

DBMS_OUTPUT.PUT_LINE('执行的SQL: ' || v_sql);
END;
$$;

3.3 递归存储过程

sql
CREATE OR REPLACE PROCEDURE calculate_organization_tree(
    p_root_dept_id IN NUMBER,
    p_level IN NUMBER DEFAULT 0
)
AS $$
DECLARE
    CURSOR dept_cursor IS
        SELECT dept_id, dept_name, parent_dept_id
        FROM departments
        WHERE parent_dept_id = p_root_dept_id;
BEGIN
    -- 插入当前部门信息
    INSERT INTO org_tree_structure (dept_id, dept_name, tree_level, path)
    SELECT dept_id, dept_name, p_level, 
           dept_name || ' -> '
    FROM departments
    WHERE dept_id = p_root_dept_id;

-- 递归处理子部门
FOR dept_rec IN dept_cursor LOOP
    calculate_organization_tree(dept_rec.dept_id, p_level + 1);
END LOOP;
END;
$$;

四、性能优化策略

4.1 批量处理优化

sql
CREATE OR REPLACE PROCEDURE bulk_employee_update(
p_dept_id IN NUMBER,
p_salary_increase_percent IN NUMBER
)
AS $$
TYPE emp_id_array IS TABLE OF NUMBER;
TYPE salary_array IS TABLE OF NUMBER;

v_emp_ids emp_id_array;
v_new_salaries salary_array;

BEGIN
– 批量获取数据
SELECT emp_id, salary * (1 + p_salary_increase_percent / 100)
BULK COLLECT INTO v_emp_ids, v_new_salaries
FROM employees
WHERE dept_id = p_dept_id;

-- 批量更新（使用FORALL）
FORALL i IN 1..v_emp_ids.COUNT
    UPDATE employees 
    SET salary = v_new_salaries(i),
        last_updated = SYSDATE
    WHERE emp_id = v_emp_ids(i);

COMMIT;

DBMS_OUTPUT.PUT_LINE('批量更新完成，影响记录数: ' || SQL%ROWCOUNT);

END;

; #### 4.2 索引优化建议 sql -- 为存储过程中频繁查询的字段创建索引 CREATE INDEX idx_employees_dept_salary ON employees(dept_id, salary); CREATE INDEX idx_employees_status ON employees(status); CREATE INDEX idx_payroll_month_year ON payroll_records(payroll_year, payroll_month); ## 五、错误处理与调试 #### 5.1 全面的异常处理框架 sql CREATE OR REPLACE PROCEDURE robust_data_processing() AS

v_error_code NUMBER;
v_error_msg VARCHAR2(4000);
v_operation VARCHAR2(100);

BEGIN
v_operation := ‘数据验证’;
– 数据验证逻辑
IF NOT validate_input_data() THEN
RAISE_APPLICATION_ERROR(-20001, ‘输入数据验证失败’);
END IF;

v_operation := '数据处理';
-- 主要业务逻辑
process_core_business();

v_operation := '数据清理';
-- 清理操作
perform_cleanup();

COMMIT;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
log_error(‘重复数据错误’, SQLERRM);
RAISE_APPLICATION_ERROR(-20002, '数据重复: ’ || SQLERRM);

WHEN VALUE_ERROR THEN
    ROLLBACK;
    log_error('数据类型错误', SQLERRM);
    RAISE_APPLICATION_ERROR(-20003, '数据类型不匹配');
    
WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    log_error('数据未找到', SQLERRM);
    RAISE_APPLICATION_ERROR(-20004, '所需数据不存在');
    
WHEN OTHERS THEN
    ROLLBACK;
    v_error_code := SQLCODE;
    v_error_msg := SQLERRM;
    
    -- 记录详细错误信息
    log_error(v_operation || ' 阶段失败', 
             '错误代码: ' || v_error_code || ', 错误信息: ' || v_error_msg);
             
    -- 重新抛出异常
    RAISE_APPLICATION_ERROR(-20005, 
        '处理失败于 ' || v_operation || ' 阶段: ' || v_error_msg);

END;

; #### 5.2 调试与日志记录 -- 创建日志表 CREATE TABLE procedure_logs ( log_id NUMBER GENERATED ALWAYS AS IDENTITY, procedure_name VARCHAR2(100), log_level VARCHAR2(20), log_message VARCHAR2(4000), log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, user_name VARCHAR2(100) DEFAULT USER ); -- 日志记录存储过程 CREATE OR REPLACE PROCEDURE log_message( p_procedure_name IN VARCHAR2, p_log_level IN VARCHAR2, p_message IN VARCHAR2 ) AS

BEGIN
    INSERT INTO procedure_logs (procedure_name, log_level, log_message)
    VALUES (p_procedure_name, p_log_level, p_message);
COMMIT;
END;
$$;

六、实例

实例1

-- 模拟数据
create table student(sno integer,sname varchar(50),ssex varchar(5),sage integer);
create table course(cno integer,cname varchar(50),credit integer);
create table elective(sno integer,cno integer,grade integer);
insert into student values(1,'小明','男',18),(2,'小强','男',17),(3,'小美','女',17),(4,'小月','女',18);
insert into course values(1,'语文',111),(2,'数学',222),(3,'英语',333);
insert into elective values(1,1,100),(1,2,95),(1,3,99),(2,1,87),(2,2,99),(2,3,83),(3,1,98),(3,2,98),(3,3,98),(4,1,97),(4,2,91),(4,3,93);

-- 1、创建视图SELECT_SD，查看学生成绩信息，查看学生姓名，课程名称，课程成绩
create or replace view SELECT_SD as select s.sname,c.cname,e.grade from student s,course c,elective where s.sno = e.sno and c.cno = e.cno;
-- 2、编写函数FUNC_SUM，返回某个学生的分数总和
create or replace function FUNC_SUM(stuid integer) returns integer as 
$$
declare result integer;
begin
	select sum(grade) into result from elective where sno = stuid;
	return result;
end;
$$ language plpgsql;
-- 3、创建触发器DELETE_ELE，在STUDENT表上绑定触发器DELETE_ELE，在删除表中某个学生时，将ELECTIVE表中该学生的选课记录一并删除
create or replace function fun_delete_ele() returns trigger as 
$$
begin
	delete from elective where sno = old.sno;
	return old;
end;
$$ language plpgsql;
create or replace trigger DELETE_ELE before delete on student for each row execute procedure fun_delete_ele();

实例2

-- 以下为表创建SQL语句
create table TEACHER(
ID INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL,
DEPTNO INTEGER NOT NULL,
SALARY FLOAT NOT NULL,
TITLE VARCHAR(100) NOT NULL --职称：讲师、副教授、教授);

create table DEPARTMENT(
ID INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL);

-- 1、创建存储过程pro_curs_1，使用游标打印各部门总人数，按人数降序排序，打印格式如下
-- 部门名称 1---人数
-- 部门名称 2---人数
-- 打印操作可以使用DBE_OUTPUT.PRINT_LINE(outputstr)接口
create or replace procedure pro_curs_1()
as
declare cursor cur1 is select d.name as dname,count(*) as perscount from teacher t,department d where t.deptno=d.id group by d.name order by perscount desc;
dname varchar(50);
perscount integer;
begin
	open cur1;
	loop 
		fetch next from cur1 into dname,perscount;
		EXIT WHEN NOT FOUND;
		DBE_OUTPUT.PRINT_LINE(concat(dname,'---',cast(perscount as varchar(10))));
	end loop;
	close cur1;
END;
/

call pro_curs_1();
-- 2、创建存储过程pro_curs_2，使用游标读取薪水降序排序的前三位老师和后三位老师的信息，分别获取ID，姓名，部门名称，薪水和职称，请按以下格式打印
-- ID-姓名-部门名称-薪水-职称
create or replace procedure pro_curs_2() 
as 
declare cursor cur2 is 
(select t.id tid,t.name tname,d.name dname,t.salary tsalary,t.title ttitle from teacher t join department d on t.deptno = d.id order by tsalary desc limit 3)
union all 
(select * from (select t.id tid,t.name tname,d.name dname,t.salary tsalary,t.title ttitle from teacher t join department d on t.deptno = d.id order by tsalary limit 3) order by tsalary desc); 
tid INTEGER;
tname VARCHAR(50);
dname VARCHAR(50);
tsalary FLOAT;
ttitle VARCHAR(100);
begin
	open cur2;
		loop
			fetch next from cur2 into tid,tname,dname,tsalary,ttitle;
			exit when not found;
			DBE_OUTPUT.PRINT_LINE(concat(cast(tid as varchar(10)),'-',tname,'-',dname,'-',cast(tsalary as varchar(10)),'-',ttitle));
		end loop;		
	close cur2;
end;
/

实例3

-- 创建表
create table student(id serial,starttime timestamp(0));

-- 1、编写存储过程，生成记录，输入个数，生成student，id从100000开始，starttime是当前时间
create or replace procedure create_student_information(num int)
as 
declare i int;
id int;
begin
	id :=100000;
	for i in 1..num loop
		insert into student values(id,sysdate);
		id :=id+1;
		dbe_output.print_line('id='||id);
	end loop;
	end;
/
-- 2、调用存储过程，生成90000条记录
call create_student_information(90000);
-- 3、查看表记录数
select count(*) from student;

实例4

--创建表
create table aps_student(logid serial,starttime timestamp(0) not null,primary key (logid));

-- 1、编写存储过程，生成记录，传入学生个数，学生LOGID从1000000开始，starttime为当前时间
create procedure create_student_informantion(num int)as
declare id int;
begin
	for id in 1000000..(1000000+num-1) loop
		insert into aps_student values(id,now());
dbe_output.print_line('id='||id); --|| 用于做字符拼接
	end loop;
end;
/

-- 2、用上一操作初始化90000学生
call create_student_informantion(90000);
-- 3、查出aps_student表中初始化学生个数
select count(*) from aps_student;

实例5

-- 当前有两张表一张学生表
student(id、name)、score(id,math,XX,xx);

-- 1、创建触发器，删除学生表中记录时，同步删除score中学生的记录
-- 创建触发器函数
create or replace function tri_delete_func() returns trigger as
$$
declare
begin
	delete from score where id=old.id;
	return old;
end;
$$ language plpgsql;

-- 创建触发器
create trigger delete_trigger before delete on student for each row execute procedure tri_delete_func();

实例6

-- 创建表
create table student(student_id int not null,math int not null,pysical int not null,art int not null,music int not null);
-- 导入数据
insert into student values(1001,56,84,65,35),(1001,63,46,82,46),(1001,85,65,32,85);
insert into student values(1002,81,86,95,72),(1002,65,46,96,45),(1002,76,54,85,68);
insert into student values(1003,69,85,76,76),(1003,78,68,31,57),(1003,46,95,94,65);

-- 1、对学习math和pysical排名前十的学生，art加5分，求所有学生总成绩
-- 先找到排名前十的学生，art+5，然后计算总成绩，第二部分offset 10表示跳过前10条记录从第11条开始，也就是除了排名前10的学生计算他们的总成绩
-- union all用来做数据集的并集，union也可以，带all表示不去重，不带all表示去重，在使用union的时候只要保证前面的数据集列数相等，不要求列名相同也能合并
(select stu.student_id,sum(math+pysical+art+5+music) allscore from student stu,(select student_id,sum(math+pysical) score from student group by student_id order by score desc limit 10)  stu1 where stu.student_id=stu1.student_id group by stu.student_id)
union 
(select stu.student_id,sum(math+pysical+art+music) allscore from student stu,(select student_id,sum(math+pysical) score from student group by student_id order by score desc offset 10)  stu2 where stu.student_id=stu2.student_id group by stu.student_id);
-- 2、获取art和music排名前十，同时math和pysical在art和music前十名的学生信息
select s1.*,s2.mscore from (select *,(art+music) ascore from student order by ascore desc limit 10) s1 join (select *,(math+pysical) mscore from student  order by mscore desc limit 10) s2 on s1.student_id=s2.student_id;
-- 3、编写存储过程，输入学生id返回总成绩
create or replace procedure pro_total_score(id int,totalscore out int)
as 
begin
	select sum(math+pysical+art+music) into totalscore from student where student_id=id;
	return totalscore;
end;
/
-- 4、编写存储过程，输入学号和科目名称，返回对应的平均成绩
create or replace procedure pro_avg_score(id int,coursename varchar(20),avgscore out int)
as
begin
	case when coursename='math' then select avg(math) into avgscore from student where student_id=id;
	when coursename='pysical' then select avg(pysical) into avgscore from student where student_id='id';
	when coursename='art' then select avg(art) into avgscore from student where student_id=id;
	when coursename='music' then select avg(music) avgscore from student where student_id=id;
	end case;
end;
/
-- 5、编写存储过程，对于学生每科成绩，0-59，绩点0,60-69绩点0.1,70-79绩点0.2,80-89绩点0.3,90-100绩点0.4，输入学号和科目名称，返回对应的绩点
create or replace procedure pro_cal_point(id1 int,coursename varchar(30),point  out float)
as 
begin
	case when coursename='math' then select (case when math<=59 then 0
		when math<=69 then 0.1
		when math<=79 then 0.2
		when math<=89 then 0.3
		when math<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
	when coursename='pysical' then select (case when pysical<=59 then 0
		when pysical<=69 then 0.1
		when pysical<=79 then 0.2
		when pysical<=89 then 0.3
		when pysical<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
	when coursename='art' then select (case when art<=59 then 0
		when art<=69 then 0.1
		when art<=79 then 0.2
		when art<=89 then 0.3
		when art<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
	when coursename='music' then select (case when music<=59 then 0
		when music<=69 then 0.1
		when music<=79 then 0.2
		when music<=89 then 0.3
		when music<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
	else raise notice 'please input right course name;';
	end case;
end;
/

实例7

-- 创建表
create table teacher(id integer primary key,name varchar(50) not null,deptnd integer not null,tetle varchar(50) not null);
create table department(id integer primary key,name varchar(50) not null,number_of_senior integer default 0);
-- 导入数据
insert into department values(1,'physics',0),(2,'mathmetics',0),(3,'chemistry',0);
insert into teacher values(1,'tom',1,'associate professor'),(2,'bill',1,'professor'),(11,'eiston','3','associate professor');

-- 1、创建Tri_update_D触发器，如果修改Number_of_senior字段时提示“不能随便修改部门教授职称人数”，如果已经有了Tri_update_D触发器，则删除后再重建
create or replace function print_info() returns trigger 
as 
$$
begin
	dbe_output.print_line('不能随便修改不能教授职称人数');
	return null;--返回null表示不允许修改，返回old表示可以修改
end;
$$ language plpgsql;

-- 当触发器存在时将触发器删除，然后创建触发器
drop trigger if exists Tri_update_D on department;
create trigger Tri_update_D before update of number_of_senior on department for each row execute procedure print_info();
-- 2、禁止触发器，修改DEPARTMENT表中D=1的NUMBER_OF_SENIOR=10，并查出表中数据
--禁用触发器
alter table department disable trigger Tri_update_D;
--执行更新操作
update department set number_of_senior=10 where id=1;
--查询部门表，查看更新结果
select * from department;
-- 3、启动触发器，修改DEPARTMENT表中ID=1的NUMBER_OF_SENIOR=20
--启动触发器
alter table  department enable trigger Tri_update_D;
--执行更新操作
update department set number_of_senior=20 where id=1;
--查询部门表，查看更新结果
select * from department;

实例8

-- 创建表&导入数据
create table score(id varchar(20),score int,cid varchar(20));
insert into score values('''001''',86,'''c1'''),('''002''',95,'''c2''');

-- 1、要求输入id和cid返回对应成绩（id和cid都有单引号）
create or replace procedure getscore(id_n varchar(20),cid_n varchar(20),score_n out int)
as 
begin
	id_n = concat('''',id_n,'''');
	cid_n = concat('''',cid_n,'''');
	select score into score_n from score where  id=id_n and cid=cid_n;
end;
--调用函数
call getscore('001','c1',888);

实例9

-- 创建触发器，要求在tab1表插入一行数据时，自动往logger表中插入一条记录，记录sname和部门名称, 并用当天的日期来标记该行数据的生成时间
create table tab1(sname text, deptno int, salary float, title text);  
create table dept(id int, dept_name text);
create table logger(sname text, dept_name text, log_date date);

-- 1、创建触发器函数 T_INS_F
create or replace function T_INS_F() returns trigger as
$$
begin
insert into logger values(new.sname,(select dept_name from dept where id=new.deptno),sysdate);
return new;
end;
$$ language plpgsql;
-- 2、创建触发器 T_INS_TR
create trigger T_INS_TR after insert on tab1 for each row execute procedure T_INS_F();
-- 3、禁用表 tab1 上的所有触发器
alter table tab1 disable trigger all;

实例10

当前有一张表stu(sno,math,art,physics,cno)
/* 
create table stu(sno varchar(30),math float,art float,physics float,cno int);
insert into stu values('1001',56,85,72,1);
insert into stu values('1002',55,35,32,1);
insert into stu values('1003',59,45,92,1);
insert into stu values('1004',73,66,65,1);
insert into stu values('1005',90,39,81,1);
*/

-- 1、查看每门学生成绩与每门平均成绩的差值
select sno,math,math-avgmath 数学差值,art,art-avgart 美术差值,physics,physics-avgphysics 物理差值 from stu,(select avg(math) avgmath,avg(art) avgart,avg(physics) avgphysics from stu);
-- 2、编写存储过程，输入学生id和科目名称输出对应的绩点值，0-59给0,60-69给0.1,70-79给0.2,80-89给0.3,90-100给0.4
create or replace procedure pro10(id varchar(30),pro varchar(30),point out float) 
as 
begin
	if pro = 'math' then 
	select (case when math <= 59 then 0 when math <= 69 then 0.1 when math <= 79 then 0.2 when math <= 89 then 0.3 when math <= 100 then 0.4 else 0 end) into point from stu where sno = id;
	elseif pro = 'art' then 
	select (case when art <= 59 then 0 when art <= 69 then 0.1 when art <= 79 then 0.2 when art <= 89 then 0.3 when art <= 100 then 0.4 else 0 end) into point from stu where sno = id;
	elseif pro = 'physics' then 
	select (case when physics <= 59 then 0 when physics <= 69 then 0.1 when physics <= 79 then 0.2 when physics <= 89 then 0.3 when physics <= 100 then 0.4 else 0 end) into point from stu where sno = id;
	else raise notice '请输入正确科目名称！';
	end if;
end;
/

实例11

-- 创建表
create table tab1(sname text,deptno int,salary float,title text);
create table dept(id int,dept_name text);
create table logger(sname text,dept_name text,log_date date);
-- 创建触发器，要求在tab1表插入一行数据时，自动往logger表中插入一条记录，记录sname和部门名称，并用当天的日期来标注该行数据的生成时间

-- 1、创建触发器函数T_INS_F
create or replace function T_INS_F() returns trigger as
$$
begin 
	insert into logger values(new.sname,(select dept_name from dept where id=new.deptno),sysdate);
	return new;
end;
$$ language plpgsql;
-- 2、创建触发器T_INS_TR
create trigger T_INS_TR after insert on tab1 for each row execute procedure T_INS_F();
-- 3、禁用表tab1上的所有触发器
alter table tab1 disable trigger all;
-- 4、删除T_INS_TR触发器
drop trigger T_INS_TR on tab1;

实例12

-- delete
create table student(id int ,name varchar(20));
create table score(id int ,name varchar(20));
insert into student values(1,'a');
insert into student values(12,'b');
insert into score values(1,'a');
insert into score values(122,'c');
insert into score values(99,'b');

create table test_trigger_src_tbl(id1 int, id2 int, id3 int);
create table test_trigger_des_tbl(id1 int, id2 int, id3 int);

--创建触发器
create or replace function tri_func() returns trigger as 
$$
declare
begin
delete from score where id = old.id;
return old;
end;
$$ language plpgsql;

create or replace function tri_delete_func() returns trigger as
$$
declare
begin
delete from test_trigger_des_tbl where id1=old.id1;
return old;
end
$$ language plpgsql;

--创建delete触发器
create  trigger delete_trigger
before delete on student
for each row
execute procedure tri_func();

create trigger delete_trigger
before delete on test_trigger_src_tbl
for each row
execute procedure tri_delete_func();


  
--触发器
create table stud(sid int,name varchar(20));
create table course(cid int ,name varchar(20));
create table selective(sid int ,cid int ,score int);


insert into stud values(1,'aaa');
insert into stud values(2,'bbb');
insert into course values(1,'a');
insert into course values(2,'b');
insert into selective values(1,1,99);
insert into selective values(1,2,33);
insert into selective values(2,2,66);
insert into selective values(2,1,66);

1）视图：返回学生的名字，选的课，选课的成绩；
create or replace view stu_score as
select s.name stu_name,c.name class_name,se.score from selective se join stud s on se.sid = s.sid join course c on se.cid = c.cid ;


解析： create or replace view stu_score as
select s.name stu_name,c.name class_name,se.score from selective se join stud s on se.sid = s.sid join course c on se.cid = c.cid;

CREATE OR REPLACE VIEW stu_score AS: 这部分语句表示如果stu_score视图已经存在，则替换它；如果不存在，则创建它。

SELECT s.name stu_name, c.name class_name, se.score: 这是视图查询的主体，用于指定视图中包含的列及其来源。

s.name stu_name: 从stud表中选择学生的姓名，并将其列别名设为stu_name。
c.name class_name: 从course表中选择课程的名称，并将其列别名设为class_name。
se.score: 从selective表中选择学生的分数。
FROM selective se JOIN stud s ON se.sid = s.sid JOIN course c ON se.cid = c.cid: 这部分定义了如何从不同的表中联接数据来构成视图。

selective se: 定义了selective表的别名为se，便于在查询中引用。
JOIN stud s ON se.sid = s.sid: 通过sid字段将selective表与stud表联接起来，意味着分数记录与学生信息是根据学生ID匹配的。
JOIN course c ON se.cid = c.cid: 同样，通过cid字段将selective表与course表联接起来，确保分数记录与课程信息是根据课程ID匹配的。



2）函数：返回某学生的总成绩；
create or replace function f_score(name1  varchar)
return int
as
declare
total_score int;
cursor c1 is select sum(score) from stu_score where stu_name = name1;
begin
open c1;
loop
fetch c1 into total_score;
return total_score;
end loop;
close c1;
end;
/

CREATE OR REPLACE VIEW stu_score AS: 这部分语句表示如果stu_score视图已经存在，则替换它；如果不存在，则创建它。

SELECT s.name stu_name, c.name class_name, se.score: 这是视图查询的主体，用于指定视图中包含的列及其来源。

s.name stu_name: 从stud表中选择学生的姓名，并将其列别名设为stu_name。
c.name class_name: 从course表中选择课程的名称，并将其列别名设为class_name。
se.score: 从selective表中选择学生的分数。
FROM selective se JOIN stud s ON se.sid = s.sid JOIN course c ON se.cid = c.cid: 这部分定义了如何从不同的表中联接数据来构成视图。

selective se: 定义了selective表的别名为se，便于在查询中引用。
JOIN stud s ON se.sid = s.sid: 通过sid字段将selective表与stud表联接起来，意味着分数记录与学生信息是根据学生ID匹配的。
JOIN course c ON se.cid = c.cid: 同样，通过cid字段将selective表与course表联接起来，确保分数记录与课程信息是根据课程ID匹配的。

3）触发器：删学生时，把selective的记录也删了；
create or replace function selective_trigger()
returns trigger
as
$$
declare
begin
delete from selective where sid = old.sid;
return old;
end;
$$ language plpgsql;

create trigger stu_trigger before delete on stud for each row
execute procedure selective_trigger();

--删除触发器
drop trigger stu_trigger on stud;

--禁用触发器
alter table stud disable trigger stu_trigger;

--修改触发器
alter trigger stu_trigger on  stud rename to stu_trigger2;


create or replace function tri_update_func() 
returns trigger as
$$
declare
begin
update test_trigger_des_tbl set id3 = new.id3 where id1 = old.id1;
return old;
end
$$ language plpgsql;

--创建update触发器
create trigger update_trigger
after update on test_trigger_src_tbl
for each row
execute procedure tri_update_func();

create or replace function update_trigger() 
returns trigger as
$$
declare
begin
if new.d <> old.d then
raise exception '不允许修改';
end if;
return new;
end
$$ language plpgsql;

create trigger up_trigger before update on t1 for each row
execute procedure update_trigger();

七、最佳实践总结

7.1 开发规范

命名规范

使用有意义的存储过程名称

参数命名体现用途和类型

保持命名一致性

代码组织

模块化设计，单一职责原则

适当的注释和文档

错误处理标准化

性能考虑

避免在循环中执行SQL

合理使用事务

适当的索引策略

7.2 维护建议

sql
-- 存储过程依赖关系查询
SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'YOUR_PROCEDURE_NAME';

-- 存储过程编译状态检查
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'PROCEDURE'
AND status = 'INVALID';

结语
GaussDB存储过程是数据库编程的强大工具，通过合理的设计和优化，可以显著提升应用性能和数据处理的可靠性。掌握存储过程的开发技巧，能够帮助开发者在分布式数据库环境中构建高效、稳定的企业级应用。

随着业务需求的不断变化，存储过程的灵活性和强大功能将继续在数据库架构中发挥关键作用。建议开发者结合实际业务场景，不断实践和优化，充分发挥GaussDB存储过程的潜力。