— 创建测试表
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);

— 输出结果
INSERT 0 3

— 变量定义示例
CREATE OR REPLACE FUNCTION fgedu_test_variables()
RETURNS TEXT
AS $$
DECLARE
— 标量变量
v_id INTEGER := 1;
v_name VARCHAR(100) := ‘张三’;
v_salary NUMERIC(10,2) := 8000.00;
v_active BOOLEAN := TRUE;
v_hire_date DATE := CURRENT_DATE;

— 复合变量
v_employee RECORD;
v_employee_row fgedu_employees%ROWTYPE;

— 数组变量
v_names TEXT[] := ARRAY[‘张三’, ‘李四’, ‘王五’];
v_salaries NUMERIC(10,2)[] := ARRAY[8000.00, 6000.00, 9000.00];

— 游标变量
cur_employees REFCURSOR;
BEGIN
— 变量赋值
v_name := ‘赵六’;
v_salary := v_salary + 1000.00;

— 使用变量
RAISE NOTICE ‘Employee ID: %’, v_id;
RAISE NOTICE ‘Employee Name: %’, v_name;
RAISE NOTICE ‘Employee Salary: %’, v_salary;
RAISE NOTICE ‘Active: %’, v_active;
RAISE NOTICE ‘Hire Date: %’, v_hire_date;

— 访问数组元素
RAISE NOTICE ‘First name: %’, v_names[1];
RAISE NOTICE ‘Second salary: %’, v_salaries[2];

— 获取行数据
SELECT * INTO v_employee_row FROM fgedu_employees WHERE id = v_id;
RAISE NOTICE ‘Employee from row: %, %’, v_employee_row.name, v_employee_row.department;

— 获取记录数据
SELECT id, name, department INTO v_employee FROM fgedu_employees WHERE id = v_id;
RAISE NOTICE ‘Employee from record: %, %’, v_employee.name, v_employee.department;

RETURN ‘Variables test completed’;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试变量函数
SELECT fgedu_test_variables();

— 输出结果
NOTICE: Employee ID: 1
NOTICE: Employee Name: 赵六
NOTICE: Employee Salary: 9000.00
NOTICE: Active: t
NOTICE: Hire Date: 2026-04-08
NOTICE: First name: 张三
NOTICE: Second salary: 6000.00
NOTICE: Employee from row: 张三, 技术部
NOTICE: Employee from record: 张三, 技术部
fgedu_test_variables
———————-
Variables test completed
(1 row)

— IF语句
CREATE OR REPLACE FUNCTION fgedu_test_if(IN p_salary NUMERIC(10,2))
RETURNS TEXT
AS $$
DECLARE
v_result TEXT;
BEGIN
IF p_salary < 5000 THEN v_result := '低薪'; ELSIF p_salary < 8000 THEN v_result := '中薪'; ELSE v_result := '高薪'; END IF; RETURN v_result; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试IF函数 SELECT fgedu_test_if(4000.00); -- 输出结果 fgedu_test_if --------------- 低薪 (1 row) SELECT fgedu_test_if(6000.00); -- 输出结果 fgedu_test_if --------------- 中薪 (1 row) SELECT fgedu_test_if(9000.00); -- 输出结果 fgedu_test_if --------------- 高薪 (1 row) -- LOOP语句 CREATE OR REPLACE FUNCTION fgedu_test_loop(IN p_count INTEGER) RETURNS INTEGER AS $$ DECLARE v_sum INTEGER := 0; v_i INTEGER := 1; BEGIN LOOP v_sum := v_sum + v_i; v_i := v_i + 1; EXIT WHEN v_i > p_count;
END LOOP;

RETURN v_sum;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试LOOP函数
SELECT fgedu_test_loop(10);

— 输出结果
fgedu_test_loop
—————-
55
(1 row)

— WHILE语句
CREATE OR REPLACE FUNCTION fgedu_test_while(IN p_count INTEGER)
RETURNS INTEGER
AS $$
DECLARE
v_sum INTEGER := 0;
v_i INTEGER := 1;
BEGIN
WHILE v_i <= p_count LOOP v_sum := v_sum + v_i; v_i := v_i + 1; END LOOP; RETURN v_sum; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试WHILE函数 SELECT fgedu_test_while(10); -- 输出结果 fgedu_test_while ----------------- 55 (1 row) -- FOR语句 CREATE OR REPLACE FUNCTION fgedu_test_for(IN p_count INTEGER) RETURNS INTEGER AS $$ DECLARE v_sum INTEGER := 0; BEGIN FOR v_i IN 1..p_count LOOP v_sum := v_sum + v_i; END LOOP; RETURN v_sum; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试FOR函数 SELECT fgedu_test_for(10); -- 输出结果 fgedu_test_for --------------- 55 (1 row) -- FOREACH语句（数组遍历） CREATE OR REPLACE FUNCTION fgedu_test_foreach() RETURNS TEXT AS $$ DECLARE v_names TEXT[] := ARRAY['张三', '李四', '王五']; v_name TEXT; v_result TEXT := ''; BEGIN FOREACH v_name IN ARRAY v_names LOOP v_result := v_result || v_name || ', '; END LOOP; -- 去除末尾的逗号和空格 v_result := rtrim(v_result, ', '); RETURN v_result; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 测试FOREACH函数 SELECT fgedu_test_foreach(); -- 输出结果 fgedu_test_foreach ------------------- 张三, 李四, 王五 (1 row)

— 异常处理
CREATE OR REPLACE FUNCTION fgedu_test_exception(IN p_id INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_name VARCHAR(100);
BEGIN
BEGIN
SELECT name INTO v_name FROM fgedu_employees WHERE id = p_id;
RETURN ‘Employee found: ‘ || v_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_test_exception(1);

— 输出结果
fgedu_test_exception
———————-
Employee found: 张三
(1 row)

SELECT fgedu_test_exception(999);

— 输出结果
fgedu_test_exception
———————-
Employee not found
(1 row)

— 自定义异常
CREATE OR REPLACE FUNCTION fgedu_test_custom_exception(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_test_custom_exception(5000.00);

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

SELECT fgedu_test_custom_exception(-1000.00);

— 输出结果
ERROR: Salary cannot be negative

SELECT fgedu_test_custom_exception(200000.00);

— 输出结果
ERROR: Salary too high

— 创建员工管理函数
CREATE OR REPLACE FUNCTION fgedu_get_employee_by_id(IN p_id INTEGER)
RETURNS fgedu_employees%ROWTYPE
AS $$
DECLARE
v_employee fgedu_employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM fgedu_employees WHERE id = p_id;

IF NOT FOUND THEN
RAISE EXCEPTION ‘Employee with ID % not found’, p_id;
END IF;

RETURN v_employee;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试函数
SELECT * FROM fgedu_get_employee_by_id(1);

— 输出结果
id | name | department | salary | hire_date
—-+——+————+———+————
1 | 张三 | 技术部 | 8000.00 | 2026-04-08
(1 row)

— 创建部门员工数量函数
CREATE OR REPLACE FUNCTION fgedu_get_employee_count_by_department(IN p_department VARCHAR(100))
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count FROM fgedu_employees WHERE department = p_department;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试函数
SELECT fgedu_get_employee_count_by_department(‘技术部’);

— 输出结果
fgedu_get_employee_count_by_department
—————————————-
2
(1 row)

SELECT fgedu_get_employee_count_by_department(‘销售部’);

— 输出结果
fgedu_get_employee_count_by_department
—————————————-
1
(1 row)

— 创建员工薪资统计函数
CREATE OR REPLACE FUNCTION fgedu_get_salary_stats()
RETURNS TABLE(
department VARCHAR(100),
employee_count INTEGER,
avg_salary NUMERIC(10,2),
min_salary NUMERIC(10,2),
max_salary NUMERIC(10,2)
)
AS $$
BEGIN
RETURN QUERY
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM fgedu_employees
GROUP BY department
ORDER BY department;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试函数
SELECT * FROM fgedu_get_salary_stats();

— 输出结果
department | employee_count | avg_salary | min_salary | max_salary
————+—————-+————+————+————
技术部 | 2 | 8500.00 | 8000.00 | 9000.00
销售部 | 1 | 6000.00 | 6000.00 | 6000.00
(2 rows)

— 创建游标示例函数
CREATE OR REPLACE FUNCTION fgedu_test_cursor(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;

— 遍历游标
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_test_cursor(‘技术部’);

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

— 创建动态SQL函数
CREATE OR REPLACE FUNCTION fgedu_test_dynamic_sql(IN p_table_name TEXT, IN p_column_name TEXT, IN p_value TEXT)
RETURNS INTEGER
AS $$
DECLARE
v_sql TEXT;
v_count INTEGER;
BEGIN
— 构建动态SQL
v_sql := ‘SELECT COUNT(*) FROM ‘ || quote_ident(p_table_name) ||
‘ WHERE ‘ || quote_ident(p_column_name) || ‘ = $1’;

— 执行动态SQL
EXECUTE v_sql INTO v_count USING p_value;

RETURN v_count;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试动态SQL函数
SELECT fgedu_test_dynamic_sql(‘fgedu_employees’, ‘department’, ‘技术部’);

— 输出结果
fgedu_test_dynamic_sql
————————
2
(1 row)

— 创建批量插入函数
CREATE OR REPLACE FUNCTION fgedu_batch_insert_employees(IN p_employees JSONB)
RETURNS INTEGER
AS $$
DECLARE
v_employee JSONB;
v_count INTEGER := 0;
BEGIN
— 遍历JSON数组
FOR v_employee IN SELECT * FROM jsonb_array_elements(p_employees) LOOP
INSERT INTO fgedu_employees(name, department, salary)
VALUES(
v_employee->>’name’,
v_employee->>’department’,
(v_employee->>’salary’)::NUMERIC(10,2)
);
v_count := v_count + 1;
END LOOP;

RETURN v_count;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试批量插入函数
SELECT fgedu_batch_insert_employees(‘[{
“name”: “赵六”,
“department”: “财务部”,
“salary”: “7000.00”
}, {
“name”: “孙七”,
“department”: “销售部”,
“salary”: “6500.00”
}]’);

— 输出结果
fgedu_batch_insert_employees
——————————
2
(1 row)

— 查看插入结果
SELECT * FROM fgedu_employees;

— 输出结果
id | name | department | salary | hire_date
—-+——+————+———+————
1 | 张三 | 技术部 | 8000.00 | 2026-04-08
2 | 李四 | 销售部 | 6000.00 | 2026-04-08
3 | 王五 | 技术部 | 9000.00 | 2026-04-08
4 | 赵六 | 财务部 | 7000.00 | 2026-04-08
5 | 孙七 | 销售部 | 6500.00 | 2026-04-08
(5 rows)

— 创建订单处理函数
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;
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;
v_price := (v_item->>’price’)::NUMERIC(10,2);

— 插入订单详情
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_quantity * v_price);
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

— 创建订单表和订单详情表
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_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 插入客户数据
INSERT INTO fgedu_customers(name, email)
VALUES(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’);

— 输出结果
INSERT 0 2

— 测试订单处理函数
SELECT fgedu_process_order(1, ‘[{
“product_id”: 1,
“quantity”: 2,
“price”: 7999.00
}, {
“product_id”: 2,
“quantity”: 1,
“price”: 4999.00
}]’);

— 输出结果
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 01:00: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 01:00:00
2 | 1 | 2 | 1 | 4999.00| 2026-04-08 01:00:00
(2 rows)

— 创建库存管理函数
CREATE OR REPLACE FUNCTION fgedu_update_inventory(
IN p_order_id INTEGER
)
RETURNS BOOLEAN
AS $$
DECLARE
v_item RECORD;
BEGIN
— 开始事务
BEGIN
— 遍历订单详情
FOR v_item IN
SELECT product_id, quantity FROM fgedu_order_items WHERE order_id = p_order_id
LOOP
— 更新库存（假设存在产品表）
— UPDATE fgedu_products SET stock = stock – v_item.quantity WHERE id = v_item.product_id;

— 模拟库存更新
RAISE NOTICE ‘Updating inventory for product %: quantity %’, v_item.product_id, v_item.quantity;
END LOOP;

— 更新订单状态
UPDATE fgedu_orders SET status = ‘completed’ WHERE id = p_order_id;

— 提交事务
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
— 回滚事务
RAISE EXCEPTION ‘Error updating inventory: %’, SQLERRM;
RETURN FALSE;
END;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试库存管理函数
SELECT fgedu_update_inventory(1);

— 输出结果
NOTICE: Updating inventory for product 1: quantity 2
NOTICE: Updating inventory for product 2: quantity 1
fgedu_update_inventory
————————
t
(1 row)

— 查看订单状态
SELECT id, customer_id, total_amount, status FROM fgedu_orders WHERE id = 1;

— 输出结果
id | customer_id | total_amount | status
—-+————-+————–+———-
1 | 1 | 20997.00| completed
(1 row)