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;
/
  1. 错误捕获语句
    使用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分布式数据库存储过程实验脚本~

Logo

华为AI开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐