企业官网建设流程全解析

做网站公司销售开场白,网站建设实训心得体会2000字,甘肃省建设类证书查询网站,山东省建设工程网站我来为您详细介绍 PostgreSQL 存储过程的编写方法。PostgreSQL 从 11 版本开始引入了完整的存储过程#xff08;PROCEDURE#xff09;支持#xff0c;在此之前通常使用函数#xff08;FUNCTION#xff09;来实现类似功能。一、存储过程 vs 函数特性 函数 (FUNCTION) …我来为您详细介绍 PostgreSQL 存储过程的编写方法。PostgreSQL 从 11 版本开始引入了完整的存储过程PROCEDURE支持在此之前通常使用函数FUNCTION来实现类似功能。一、存储过程 vs 函数特性 函数 (FUNCTION) 存储过程 (PROCEDURE)返回值 必须有返回值 无返回值事务控制 不能控制事务 可以使用 COMMIT/ROLLBACK调用方式 SELECT func() 或 SQL 表达式 CALL proc()适用场景 计算、查询 复杂业务逻辑、批量操作二、基础语法1. 创建存储过程sql-- 基本结构CREATE OR REPLACE PROCEDURE 过程名(参数名 数据类型 [IN | OUT | INOUT],...)LANGUAGE plpgsqlAS $$DECLARE-- 声明变量BEGIN-- 业务逻辑EXCEPTION-- 异常处理END;$$;2. 完整示例sql-- 示例1简单的用户注册过程CREATE OR REPLACE PROCEDURE sp_user_register(p_username VARCHAR(50),p_email VARCHAR(100),p_password VARCHAR(255))LANGUAGE plpgsqlAS $$BEGIN-- 检查用户名是否已存在IF EXISTS (SELECT 1 FROM users WHERE username p_username) THENRAISE EXCEPTION 用户名已存在: %, p_username;END IF;-- 插入新用户INSERT INTO users (username, email, password, created_at)VALUES (p_username, p_email, p_password, NOW());RAISE NOTICE 用户 % 注册成功, p_username;END;$$;-- 调用方式CALL sp_user_register(john_doe, johnexample.com, hashed_password);三、常用控制结构1. 条件判断sql-- IF-THEN-ELSEIF 条件 THEN-- 语句ELSIF 条件 THEN-- 语句ELSE-- 语句END IF;-- CASE 表达式CASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2ELSE 默认结果END;2. 循环结构sql-- LOOP 循环LOOPEXIT WHEN 条件; -- 退出条件END LOOP;-- WHILE 循环WHILE 条件 LOOP-- 语句END LOOP;-- FOR 循环整数范围FOR i IN 1..10 LOOP-- 语句END LOOP;-- FOR 循环查询结果集FOR rec IN SELECT * FROM users LOOPRAISE NOTICE 用户: %, rec.username;END LOOP;-- FOREACH遍历数组FOREACH item IN ARRAY arr LOOP-- 语句END LOOP;四、事务控制存储过程特有sqlCREATE OR REPLACE PROCEDURE sp_batch_transfer()LANGUAGE plpgsqlAS $$DECLAREv_count INT : 0;BEGIN-- 第一批操作INSERT INTO logs (msg) VALUES (开始批量转账);COMMIT; -- 提交第一批-- 第二批操作UPDATE accounts SET balance balance - 100 WHERE id 1;UPDATE accounts SET balance balance 100 WHERE id 2;GET DIAGNOSTICS v_count ROW_COUNT;IF v_count 0 THENROLLBACK; -- 回滚RAISE EXCEPTION 转账失败;ELSECOMMIT; -- 提交第二批END IF;EXCEPTIONWHEN OTHERS THENROLLBACK;RAISE;END;$$;五、游标操作处理大数据量sqlCREATE OR REPLACE PROCEDURE sp_process_large_table()LANGUAGE plpgsqlAS $$DECLAREcur CURSOR FOR SELECT id, name FROM large_table WHERE processed false;rec RECORD;BEGINOPEN cur;LOOPFETCH cur INTO rec;EXIT WHEN NOT FOUND;-- 逐行处理UPDATE large_tableSET processed true, updated_at NOW()WHERE id rec.id;-- 每1000条提交一次避免长事务IF rec.id % 1000 0 THENCOMMIT;END IF;END LOOP;CLOSE cur;END;$$;六、动态 SQLsqlCREATE OR REPLACE PROCEDURE sp_dynamic_query(p_table_name VARCHAR(50),p_limit INT DEFAULT 100)LANGUAGE plpgsqlAS $$DECLAREsql_query TEXT;rec RECORD;BEGIN-- 构建动态SQL注意SQL注入风险sql_query : format(SELECT * FROM %I LIMIT %L, p_table_name, p_limit);-- 执行动态查询FOR rec IN EXECUTE sql_query LOOPRAISE NOTICE 记录: %, rec;END LOOP;-- 或使用 EXECUTE ... INTOEXECUTE SELECT COUNT(*) FROM || quote_ident(p_table_name) INTO v_count;END;$$; 安全提示使用 format() 的 %I标识符和 %L字面值或 quote_ident()/quote_literal() 防止 SQL 注入。七、异常处理sqlCREATE OR REPLACE PROCEDURE sp_safe_operation()LANGUAGE plpgsqlAS $$BEGIN-- 主逻辑INSERT INTO critical_table VALUES (...);EXCEPTION-- 唯一约束冲突WHEN unique_violation THENRAISE NOTICE 记录已存在跳过;-- 外键约束冲突WHEN foreign_key_violation THENRAISE EXCEPTION 关联数据不存在;-- 检查约束冲突WHEN check_violation THENRAISE EXCEPTION 数据不符合约束条件;-- 捕获所有其他异常WHEN OTHERS THENRAISE EXCEPTION 未知错误: % - %, SQLSTATE, SQLERRM;-- 或使用 RAISE NOTICE 记录后忽略END;$$;八、输出参数sqlCREATE OR REPLACE PROCEDURE sp_get_user_stats(IN p_user_id INT,OUT total_orders INT,OUT total_amount NUMERIC)LANGUAGE plpgsqlAS $$BEGINSELECT COUNT(*), COALESCE(SUM(amount), 0)INTO total_orders, total_amountFROM ordersWHERE user_id p_user_id;END;$$;-- 调用CALL sp_get_user_stats(123); -- 返回结果集九、最佳实践1. 命名规范使用 sp_ 前缀区分存储过程2. 参数命名输入参数用 p_ 前缀输出用 o_内部变量用 v_3. 错误处理始终包含 EXCEPTION 块4. 事务粒度合理控制事务范围避免长事务5. 权限控制使用 SECURITY DEFINER 或 SECURITY INVOKER6. 文档注释添加详细的注释说明sqlCOMMENT ON PROCEDURE sp_user_register IS用户注册存储过程参数p_username - 用户名p_email - 邮箱p_password - 加密后的密码异常用户名已存在时抛出异常;十、管理命令sql-- 查看所有存储过程SELECT * FROM pg_proc WHERE prokind p;-- 修改存储过程ALTER PROCEDURE sp_name RENAME TO new_name;-- 删除存储过程DROP PROCEDURE IF EXISTS sp_name;-- 查看存储过程源码SELECT pg_get_functiondef(sp_name::regprocedure);需要我针对某个具体业务场景如订单处理、数据同步、报表生成等编写更详细的存储过程示例吗