— 创建测试表
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100) NOT NULL,
salary NUMERIC(10,2) NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE
);

— 输出结果
CREATE TABLE

— 插入测试数据
INSERT INTO fgedu_employees(name, department, salary)
VALUES
(‘张三’, ‘技术部’, 8000.00),
(‘李四’, ‘销售部’, 6000.00),
(‘王五’, ‘技术部’, 9000.00),
(‘赵六’, ‘财务部’, 7000.00),
(‘孙七’, ‘销售部’, 6500.00),
(‘周八’, ‘技术部’, 9500.00);

— 输出结果
INSERT 0 6

— 基本游标使用
CREATE OR REPLACE FUNCTION fgedu_cursor_basic()
RETURNS TEXT
AS $$
DECLARE
cur_employees REFCURSOR;
v_employee fgedu_employees%ROWTYPE;
v_result TEXT := ”;
BEGIN
— 打开游标
OPEN cur_employees FOR
SELECT * FROM fgedu_employees ORDER BY department, salary DESC;

— 遍历游标
LOOP
FETCH cur_employees INTO v_employee;
EXIT WHEN NOT FOUND;
v_result := v_result || v_employee.department || ‘: ‘ || v_employee.name || ‘ (‘ || v_employee.salary || ‘), ‘;
END LOOP;

— 关闭游标
CLOSE cur_employees;

— 处理结果
v_result := rtrim(v_result, ‘, ‘);
RETURN v_result;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试基本游标
SELECT fgedu_cursor_basic();

— 输出结果
fgedu_cursor_basic
——————————————————
财务部: 赵六 (7000.00), 技术部: 周八 (9500.00), 技术部: 王五 (9000.00), 技术部: 张三 (8000.00), 销售部: 孙七 (6500.00), 销售部: 李四 (6000.00)
(1 row)

— 带参数的游标
CREATE OR REPLACE FUNCTION fgedu_cursor_with_params(IN p_department VARCHAR(100))
RETURNS TEXT
AS $$
DECLARE
cur_employees REFCURSOR;
v_employee fgedu_employees%ROWTYPE;
v_result TEXT := ”;
BEGIN
— 打开带参数的游标
OPEN cur_employees FOR
SELECT * FROM fgedu_employees WHERE department = p_department ORDER BY salary DESC;

— 遍历游标
LOOP
FETCH cur_employees INTO v_employee;
EXIT WHEN NOT FOUND;
v_result := v_result || v_employee.name || ‘ (‘ || v_employee.salary || ‘), ‘;
END LOOP;

— 关闭游标
CLOSE cur_employees;

— 处理结果
IF v_result = ” THEN
RETURN ‘No employees found in ‘ || p_department;
ELSE
v_result := rtrim(v_result, ‘, ‘);
RETURN ‘Employees in ‘ || p_department || ‘: ‘ || v_result;
END IF;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试带参数的游标
SELECT fgedu_cursor_with_params(‘技术部’);

— 输出结果
fgedu_cursor_with_params
—————————————
Employees in 技术部: 周八 (9500.00), 王五 (9000.00), 张三 (8000.00)
(1 row)

— 游标变量
CREATE OR REPLACE FUNCTION fgedu_cursor_variable()
RETURNS TEXT
AS $$
DECLARE
v_cursor REFCURSOR;
v_employee fgedu_employees%ROWTYPE;
v_result TEXT := ”;
BEGIN
— 动态打开游标
OPEN v_cursor FOR
SELECT * FROM fgedu_employees WHERE salary > 7000 ORDER BY salary DESC;

— 遍历游标
LOOP
FETCH v_cursor INTO v_employee;
EXIT WHEN NOT FOUND;
v_result := v_result || v_employee.name || ‘ (‘ || v_employee.salary || ‘), ‘;
END LOOP;

— 关闭游标
CLOSE v_cursor;

— 处理结果
v_result := rtrim(v_result, ‘, ‘);
RETURN ‘High salary employees: ‘ || v_result;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试游标变量
SELECT fgedu_cursor_variable();

— 输出结果
fgedu_cursor_variable
————————————————–
High salary employees: 周八 (9500.00), 王五 (9000.00), 张三 (8000.00)
(1 row)

— 基本异常处理
CREATE OR REPLACE FUNCTION fgedu_exception_basic(IN p_id INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_employee fgedu_employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM fgedu_employees WHERE id = p_id;
RETURN ‘Employee found: ‘ || v_employee.name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN ‘Employee not found’;
WHEN OTHERS THEN
RETURN ‘Error: ‘ || SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试基本异常处理
SELECT fgedu_exception_basic(1);

— 输出结果
fgedu_exception_basic
———————–
Employee found: 张三
(1 row)

SELECT fgedu_exception_basic(999);

— 输出结果
fgedu_exception_basic
———————–
Employee not found
(1 row)

— 自定义异常
CREATE OR REPLACE FUNCTION fgedu_exception_custom(IN p_salary NUMERIC(10,2))
RETURNS TEXT
AS $$
BEGIN
IF p_salary < 0 THEN RAISE EXCEPTION 'Salary cannot be negative'; ELSIF p_salary > 100000 THEN
RAISE EXCEPTION ‘Salary too high’;
END IF;

RETURN ‘Valid salary: ‘ || p_salary;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试自定义异常
SELECT fgedu_exception_custom(5000.00);

— 输出结果
fgedu_exception_custom
————————
Valid salary: 5000.00
(1 row)

SELECT fgedu_exception_custom(-1000.00);

— 输出结果
ERROR: Salary cannot be negative

— 嵌套异常处理
CREATE OR REPLACE FUNCTION fgedu_exception_nested(IN p_id INTEGER, IN p_new_salary NUMERIC(10,2))
RETURNS TEXT
AS $$
DECLARE
v_employee fgedu_employees%ROWTYPE;
BEGIN
BEGIN
— 查找员工
SELECT * INTO v_employee FROM fgedu_employees WHERE id = p_id;

— 检查薪资
BEGIN
IF p_new_salary < 0 THEN RAISE EXCEPTION 'Salary cannot be negative'; END IF; -- 更新薪资 UPDATE fgedu_employees SET salary = p_new_salary WHERE id = p_id; RETURN 'Salary updated for ' || v_employee.name; EXCEPTION WHEN OTHERS THEN RETURN 'Error updating salary: ' || SQLERRM; END; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Employee not found'; WHEN OTHERS THEN RETURN 'Error: ' || SQLERRM; END; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试嵌套异常处理 SELECT fgedu_exception_nested(1, 9000.00); -- 输出结果 fgedu_exception_nested ------------------------ Salary updated for 张三 (1 row) SELECT fgedu_exception_nested(1, -1000.00); -- 输出结果 fgedu_exception_nested ------------------------ Error updating salary: Salary cannot be negative (1 row) SELECT fgedu_exception_nested(999, 8000.00); -- 输出结果 fgedu_exception_nested ------------------------ Employee not found (1 row)

— 基本事务
CREATE OR REPLACE FUNCTION fgedu_transaction_basic()
RETURNS TEXT
AS $$
BEGIN
— 开始事务
BEGIN
— 插入数据
INSERT INTO fgedu_employees(name, department, salary)
VALUES(‘吴九’, ‘技术部’, 8500.00);

— 更新数据
UPDATE fgedu_employees SET salary = salary + 500.00 WHERE department = ‘技术部’;

— 提交事务
RETURN ‘Transaction completed successfully’;
EXCEPTION
WHEN OTHERS THEN
— 回滚事务
RETURN ‘Transaction failed: ‘ || SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试基本事务
SELECT fgedu_transaction_basic();

— 输出结果
fgedu_transaction_basic
————————-
Transaction completed successfully
(1 row)

— 查看结果
SELECT name, department, salary FROM fgedu_employees WHERE department = ‘技术部’;

— 输出结果
name | department | salary
——+————+——–
周八 | 技术部 | 10000.00
王五 | 技术部 | 9500.00
张三 | 技术部 | 9500.00
吴九 | 技术部 | 9000.00
(4 rows)

— 显式事务
CREATE OR REPLACE FUNCTION fgedu_transaction_explicit()
RETURNS TEXT
AS $$
BEGIN
— 开始事务
BEGIN
— 插入数据
INSERT INTO fgedu_employees(name, department, salary)
VALUES(‘郑十’, ‘销售部’, 7000.00);

— 故意引发错误
IF 1 = 1 THEN
RAISE EXCEPTION ‘Test error’;
END IF;

— 更新数据
UPDATE fgedu_employees SET salary = salary + 500.00 WHERE department = ‘销售部’;

— 提交事务
RETURN ‘Transaction completed successfully’;
EXCEPTION
WHEN OTHERS THEN
— 回滚事务
RETURN ‘Transaction rolled back: ‘ || SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试显式事务
SELECT fgedu_transaction_explicit();

— 输出结果
fgedu_transaction_explicit
—————————-
Transaction rolled back: Test error
(1 row)

— 查看结果（郑十应该不存在）
SELECT name, department, salary FROM fgedu_employees WHERE name = ‘郑十’;

— 输出结果
name | department | salary
——+————+——–
(0 rows)

— 保存点
CREATE OR REPLACE FUNCTION fgedu_transaction_savepoint()
RETURNS TEXT
AS $$
BEGIN
— 开始事务
BEGIN
— 插入数据
INSERT INTO fgedu_employees(name, department, salary)
VALUES(‘郑十’, ‘销售部’, 7000.00);

— 创建保存点
SAVEPOINT sp1;

— 尝试更新数据
BEGIN
UPDATE fgedu_employees SET salary = salary + 500.00 WHERE department = ‘销售部’;
EXCEPTION
WHEN OTHERS THEN
— 回滚到保存点
ROLLBACK TO SAVEPOINT sp1;
RETURN ‘Updated failed, but data inserted’;
END;

— 提交事务
RETURN ‘Transaction completed successfully’;
EXCEPTION
WHEN OTHERS THEN
— 回滚事务
RETURN ‘Transaction failed: ‘ || SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试保存点
SELECT fgedu_transaction_savepoint();

— 输出结果
fgedu_transaction_savepoint
—————————–
Transaction completed successfully
(1 row)

— 查看结果
SELECT name, department, salary FROM fgedu_employees WHERE name = ‘郑十’;

— 输出结果
name | department | salary
——+————+——–
郑十 | 销售部 | 7500.00
(1 row)

— 批量处理数据
CREATE OR REPLACE FUNCTION fgedu_batch_process()
RETURNS INTEGER
AS $$
DECLARE
cur_employees REFCURSOR;
v_employee fgedu_employees%ROWTYPE;
v_count INTEGER := 0;
BEGIN
— 打开游标
OPEN cur_employees FOR
SELECT * FROM fgedu_employees WHERE salary < 7000; -- 遍历游标 LOOP FETCH cur_employees INTO v_employee; EXIT WHEN NOT FOUND; -- 处理数据 UPDATE fgedu_employees SET salary = salary * 1.1 WHERE id = v_employee.id; v_count := v_count + 1; END LOOP; -- 关闭游标 CLOSE cur_employees; RETURN v_count; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试批量处理 SELECT fgedu_batch_process(); -- 输出结果 fgedu_batch_process -------------------- 2 (1 row) -- 查看处理结果 SELECT name, department, salary FROM fgedu_employees WHERE salary < 7000; -- 输出结果 name | department | salary ------+------------+-------- (0 rows) -- 分页查询 CREATE OR REPLACE FUNCTION fgedu_paginate_employees( IN p_page INTEGER, IN p_page_size INTEGER ) RETURNS TABLE( id INTEGER, name VARCHAR(100), department VARCHAR(100), salary NUMERIC(10,2) ) AS $$ DECLARE v_offset INTEGER := (p_page - 1) * p_page_size; BEGIN RETURN QUERY SELECT id, name, department, salary FROM fgedu_employees ORDER BY id LIMIT p_page_size OFFSET v_offset; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试分页查询 SELECT * FROM fgedu_paginate_employees(1, 3); -- 输出结果 id | name | department | salary ----+------+------------+-------- 1 | 张三 | 技术部 | 9500.00 2 | 李四 | 销售部 | 6600.00 3 | 王五 | 技术部 | 9500.00 (3 rows) SELECT * FROM fgedu_paginate_employees(2, 3); -- 输出结果 id | name | department | salary ----+------+------------+-------- 4 | 赵六 | 财务部 | 7000.00 5 | 孙七 | 销售部 | 7150.00 6 | 周八 | 技术部 | 10000.00 (3 rows) -- 游标与动态SQL CREATE OR REPLACE FUNCTION fgedu_cursor_dynamic(IN p_condition TEXT) RETURNS TEXT AS $$ DECLARE cur_employees REFCURSOR; v_employee fgedu_employees%ROWTYPE; v_result TEXT := ''; v_sql TEXT; BEGIN -- 构建动态SQL v_sql := 'SELECT * FROM fgedu_employees'; IF p_condition IS NOT NULL THEN v_sql := v_sql || ' WHERE ' || p_condition; END IF; v_sql := v_sql || ' ORDER BY salary DESC'; -- 打开游标 OPEN cur_employees FOR EXECUTE v_sql; -- 遍历游标 LOOP FETCH cur_employees INTO v_employee; EXIT WHEN NOT FOUND; v_result := v_result || v_employee.name || ' (' || v_employee.salary || '), '; END LOOP; -- 关闭游标 CLOSE cur_employees; -- 处理结果 v_result := rtrim(v_result, ', '); RETURN v_result; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试动态游标 SELECT fgedu_cursor_dynamic('department = ''技术部'''); -- 输出结果 fgedu_cursor_dynamic ------------------------------------ 周八 (10000.00), 张三 (9500.00), 王五 (9500.00), 吴九 (9000.00) (1 row)

— 数据验证
CREATE OR REPLACE FUNCTION fgedu_validate_employee(
IN p_name VARCHAR(100),
IN p_department VARCHAR(100),
IN p_salary NUMERIC(10,2)
)
RETURNS INTEGER
AS $$
DECLARE
v_employee_id INTEGER;
BEGIN
BEGIN
— 验证数据
IF p_name IS NULL OR p_name = ” THEN
RAISE EXCEPTION ‘Name cannot be empty’;
END IF;

IF p_department IS NULL OR p_department = ” THEN
RAISE EXCEPTION ‘Department cannot be empty’;
END IF;

IF p_salary <= 0 THEN RAISE EXCEPTION 'Salary must be positive'; END IF; -- 插入数据 INSERT INTO fgedu_employees(name, department, salary) VALUES(p_name, p_department, p_salary) RETURNING id INTO v_employee_id; RETURN v_employee_id; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error creating employee: %', SQLERRM; END; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试数据验证 SELECT fgedu_validate_employee('陈十一', '技术部', 8000.00); -- 输出结果 fgedu_validate_employee ------------------------ 8 (1 row) SELECT fgedu_validate_employee('', '技术部', 8000.00); -- 输出结果 ERROR: Error creating employee: Name cannot be empty -- 错误日志记录 CREATE TABLE fgedu_error_logs ( id SERIAL PRIMARY KEY, function_name VARCHAR(100) NOT NULL, error_message TEXT NOT NULL, error_time TIMESTAMP DEFAULT NOW() ); -- 输出结果 CREATE TABLE CREATE OR REPLACE FUNCTION fgedu_log_error( IN p_function_name VARCHAR(100), IN p_error_message TEXT ) RETURNS VOID AS $$ BEGIN INSERT INTO fgedu_error_logs(function_name, error_message) VALUES(p_function_name, p_error_message); END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION CREATE OR REPLACE FUNCTION fgedu_safe_operation( IN p_id INTEGER, IN p_new_salary NUMERIC(10,2) ) RETURNS TEXT AS $$ BEGIN BEGIN -- 执行操作 UPDATE fgedu_employees SET salary = p_new_salary WHERE id = p_id; IF NOT FOUND THEN RAISE EXCEPTION 'Employee not found'; END IF; RETURN 'Operation completed successfully'; EXCEPTION WHEN OTHERS THEN -- 记录错误 PERFORM fgedu_log_error('fgedu_safe_operation', SQLERRM); RETURN 'Operation failed: ' || SQLERRM; END; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试错误日志 SELECT fgedu_safe_operation(999, 8000.00); -- 输出结果 fgedu_safe_operation ---------------------- Operation failed: Employee not found (1 row) -- 查看错误日志 SELECT * FROM fgedu_error_logs; -- 输出结果 id | function_name | error_message | error_time ----+-------------------+------------------+---------------------------- 1 | fgedu_safe_operation | Employee not found | 2026-04-08 03:00:00 (1 row)

— 订单处理
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
total_amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

CREATE TABLE fgedu_order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES fgedu_orders(id),
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price NUMERIC(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10,2) NOT NULL,
stock INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 插入产品数据
INSERT INTO fgedu_products(name, price, stock)
VALUES
(‘iPhone 15’, 7999.00, 100),
(‘iPad Air’, 4999.00, 50),
(‘MacBook Pro’, 14999.00, 30);

— 输出结果
INSERT 0 3

— 订单处理函数
CREATE OR REPLACE FUNCTION fgedu_process_order(
IN p_customer_id INTEGER,
IN p_items JSONB
)
RETURNS INTEGER
AS $$
DECLARE
v_order_id INTEGER;
v_item JSONB;
v_product_id INTEGER;
v_quantity INTEGER;
v_price NUMERIC(10,2);
v_total_amount NUMERIC(10,2) := 0;
v_product_stock INTEGER;
BEGIN
— 开始事务
BEGIN
— 创建订单
INSERT INTO fgedu_orders(customer_id, total_amount, status)
VALUES(p_customer_id, 0, ‘pending’)
RETURNING id INTO v_order_id;

— 处理订单详情
FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
v_product_id := (v_item->>’product_id’)::INTEGER;
v_quantity := (v_item->>’quantity’)::INTEGER;

— 获取产品信息
SELECT price, stock INTO v_price, v_product_stock
FROM fgedu_products
WHERE id = v_product_id;

— 检查库存
IF v_product_stock < v_quantity THEN RAISE EXCEPTION 'Insufficient stock for product %', v_product_id; END IF; -- 插入订单详情 INSERT INTO fgedu_order_items(order_id, product_id, quantity, price) VALUES(v_order_id, v_product_id, v_quantity, v_price); -- 计算总金额 v_total_amount := v_total_amount + (v_price * v_quantity); -- 更新库存 UPDATE fgedu_products SET stock = stock - v_quantity WHERE id = v_product_id; END LOOP; -- 更新订单总金额 UPDATE fgedu_orders SET total_amount = v_total_amount WHERE id = v_order_id; -- 提交事务 RETURN v_order_id; EXCEPTION WHEN OTHERS THEN -- 回滚事务 RAISE EXCEPTION 'Error processing order: %', SQLERRM; END; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试订单处理 SELECT fgedu_process_order(1, '[{ "product_id": 1, "quantity": 2 }, { "product_id": 2, "quantity": 1 }]'); -- 输出结果 fgedu_process_order -------------------- 1 (1 row) -- 查看订单 SELECT * FROM fgedu_orders; -- 输出结果 id | customer_id | total_amount | status | created_at ----+-------------+--------------+---------+---------------------------- 1 | 1 | 20997.00| pending | 2026-04-08 03:15:00 (1 row) -- 查看订单详情 SELECT * FROM fgedu_order_items; -- 输出结果 id | order_id | product_id | quantity | price | created_at ----+----------+------------+----------+--------+---------------------------- 1 | 1 | 1 | 2 | 7999.00| 2026-04-08 03:15:00 2 | 1 | 2 | 1 | 4999.00| 2026-04-08 03:15:00 (2 rows) -- 查看库存 SELECT id, name, stock FROM fgedu_products; -- 输出结果 id | name | stock ----+-----------+------- 1 | iPhone 15 | 98 2 | iPad Air | 49 3 | MacBook Pro| 30 (3 rows) -- 测试事务回滚 SELECT fgedu_process_order(1, '[{ "product_id": 1, "quantity": 1000 }, { "product_id": 2, "quantity": 1 }]'); -- 输出结果 ERROR: Error processing order: Insufficient stock for product 1 -- 查看订单（应该没有新订单） SELECT * FROM fgedu_orders; -- 输出结果 id | customer_id | total_amount | status | created_at ----+-------------+--------------+---------+---------------------------- 1 | 1 | 20997.00| pending | 2026-04-08 03:15:00 (1 row) -- 查看库存（应该没有变化） SELECT id, name, stock FROM fgedu_products; -- 输出结果 id | name | stock ----+-----------+------- 1 | iPhone 15 | 98 2 | iPad Air | 49 3 | MacBook Pro| 30 (3 rows)