GaussDB数据库开发设计之存储过程

实验1：数据库对象与管理
实验3：GaussDB数据库开发设计之JDBC高可用性

实验2：存储过程

1. 匿名块、存储过程和函数

匿名块是一次性执行的PL/SQL代码块。函数是有返回值的PL/SQL程序单元。

步骤1、编写匿名块：

BEGIN
  DBE_OUTPUT.PRINT_LINE ('Hello, world!');
END ;
/

步骤2、创建存储过程：

CREATE OR REPLACE PROCEDURE insert_employee(
    p_employee_id NUMBER,
    p_first_name VARCHAR2,
    p_last_name VARCHAR2,
    p_hire_date DATE
)
IS
BEGIN
    INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
    VALUES (p_employee_id, p_first_name, p_last_name, p_hire_date);
    COMMIT;
END;
/

调用存储过程：

CALL insert_employee(1, 'John', 'Doe', TO_DATE('2022-05-15', 'YYYY-MM-DD'));

查看当前表range_emp的数据：

select * from range_emp;

步骤3、创建函数：

CREATE OR REPLACE FUNCTION get_full_name(p_employee_id NUMBER)
    RETURN VARCHAR2
IS
    v_full_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_full_name
    FROM range_emp
    WHERE employee_id = p_employee_id;
    
    RETURN v_full_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Employee not found';
END;
/

调用函数：

SELECT get_full_name(1) FROM dual;

2. 代码块结构与声明语法

PL/SQL代码块由声明部分、执行部分和异常处理部分组成。

步骤1、声明变量并编写代码块：

DECLARE
  v_employee_id NUMBER := 1;
  v_employee_name VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name INTO v_employee_name 
  FROM range_emp WHERE employee_id = v_employee_id;
  DBE_OUTPUT.PUT_LINE ('Employee Name: ' || v_employee_name);
END ;
/

3. 基本语句

PL/SQL支持多种控制语句，包括IF语句、LOOP语句等。

步骤1、使用IF语句：

CREATE OR REPLACE PROCEDURE check_hire_date(p_employee_id NUMBER)
IS
    v_hire_date DATE;
BEGIN
    SELECT hire_date INTO v_hire_date
    FROM range_emp
    WHERE employee_id = p_employee_id;

    IF v_hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD') THEN
        DBE_OUTPUT.PUT_LINE('Employee hired before 2020');
    ELSIF v_hire_date >= TO_DATE('2020-01-01', 'YYYY-MM-DD') AND v_hire_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') THEN
        DBE_OUTPUT.PUT_LINE('Employee hired after 2020 but before 2022');
    ELSE
        DBE_OUTPUT.PUT_LINE('Employee hired after 2022');
    END IF;
END;
/

调用存储过程：

```sql                                    
BEGIN
    check_hire_date(1);
END;
/

4. 游标操作

游标用于检索多行数据，分为显式游标和隐式游标。

步骤1、创建并使用显式游标：

DECLARE
    CURSOR emp_cursor IS
        SELECT first_name, last_name
        FROM range_emp;
    
    v_first_name VARCHAR2(50);
    v_last_name VARCHAR2(50);
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_first_name, v_last_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBE_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
    END LOOP;
    CLOSE emp_cursor;
END;
/

5. 错误捕获语句

使用EXCEPTION部分处理错误。

步骤1、编写错误捕获块：
设置唯一约束：

ALTER TABLE range_emp
ADD CONSTRAINT uq_employee_id UNIQUE (employee_id);

CREATE OR REPLACE PROCEDURE insert_employee_with_exception(
    p_employee_id NUMBER,
    p_first_name VARCHAR2,
    p_last_name VARCHAR2,
    p_hire_date DATE
)
IS
BEGIN
    BEGIN
        INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
        VALUES (p_employee_id, p_first_name, p_last_name, p_hire_date);
        COMMIT;
    EXCEPTION
        WHEN unique_violation THEN
            RAISE NOTICE 'Error: Employee ID % already exists.', p_employee_id;
        WHEN OTHERS THEN
            RAISE NOTICE 'An unexpected error occurred.';
    END;
END;
/

调用存储过程：

BEGIN
    insert_employee_with_exception(1, 'Jane', 'Smith', TO_DATE('2023-06-10', 'YYYY-MM-DD'));
END;
/

6. 事务管理

事务控制语句包括COMMIT、ROLLBACK等。

步骤1、使用事务控制：

CREATE OR REPLACE PROCEDURE insert_multiple_employees
IS
BEGIN
    -- 开始事务
    INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
    VALUES (3, 'Mary', 'Doe', TO_DATE('2022-05-15', 'YYYY-MM-DD'));

    INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
    VALUES (4, 'Jack', 'Smith', TO_DATE('2023-06-10', 'YYYY-MM-DD')); -- 会失败，重复的ID

    COMMIT; -- 提交事务
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- 回滚事务
        DBE_OUTPUT.PUT_LINE('Transaction failed and rolled back');
END;
/

调用存储过程：

BEGIN
    insert_multiple_employees;
END;
/

查看当前表range_emp的数据：

select * from range_emp;

7. 动态语句

动态SQL允许在运行时构建和执行SQL语句。

步骤1、编写动态SQL：

CREATE OR REPLACE PROCEDURE dynamic_query_employee(p_employee_id NUMBER)
IS
    v_sql VARCHAR2(1000);
    v_first_name VARCHAR2(50);
    v_last_name VARCHAR2(50);
BEGIN
    v_sql := 'SELECT first_name, last_name FROM range_emp WHERE employee_id = ' || p_employee_id;
    
    EXECUTE IMMEDIATE v_sql INTO v_first_name, v_last_name;
    
    DBE_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
END;
/

调用存储过程：

BEGIN
    dynamic_query_employee(1);
END;
/

总结

本次主要是提供了GaussDB分布式数据库存储过程实验脚本~

关于作者

网名：飞天，墨天轮2024年度、2025年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、YCP、HCIP、磐维等众多国产数据库认证证书，目前从事Oracle、MySQL、PostgreSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，一起探讨，共同进步~~~