【GaussDB】GaussDB数据库开发设计之存储过程
·
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;
/
- 错误捕获语句
使用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分布式数据库存储过程实验脚本~
更多推荐



所有评论(0)