Back to Details

generating-stored-procedures

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Stored Procedure Generator

存储过程生成器

Generate production-ready stored procedures for PostgreSQL, MySQL, and SQL Server with proper error handling, transaction management, and security best practices.
为PostgreSQL、MySQL和SQL Server生成可用于生产环境的存储过程，具备完善的错误处理、事务管理和安全最佳实践。

Prerequisites

前提条件

  • Database connection credentials (host, port, database, user, password)
  • Appropriate permissions: CREATE PROCEDURE, CREATE FUNCTION, EXECUTE
  • Target database type identified (PostgreSQL, MySQL, or SQL Server)
  • 数据库连接凭证（主机、端口、数据库、用户名、密码）
  • 适当的权限：CREATE PROCEDURE、CREATE FUNCTION、EXECUTE
  • 已确定目标数据库类型（PostgreSQL、MySQL或SQL Server）

Instructions

操作步骤

1. Identify Database Type and Requirements

1. 确定数据库类型和需求

Determine the target database and procedure requirements:
sql
-- PostgreSQL: Check version and extensions
SELECT version();
\dx

-- MySQL: Check version and settings
SELECT VERSION();
SHOW VARIABLES LIKE 'sql_mode';

-- SQL Server: Check version and edition
SELECT @@VERSION;
确定目标数据库和存储过程需求：
sql
-- PostgreSQL: Check version and extensions
SELECT version();
\dx

-- MySQL: Check version and settings
SELECT VERSION();
SHOW VARIABLES LIKE 'sql_mode';

-- SQL Server: Check version and edition
SELECT @@VERSION;

2. Generate Stored Procedure

2. 生成存储过程

PostgreSQL Function (PL/pgSQL):
sql
CREATE OR REPLACE FUNCTION get_user_by_id(p_user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR, created_at TIMESTAMP)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username, u.email, u.created_at
    FROM users u
    WHERE u.id = p_user_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User with ID % not found', p_user_id
            USING ERRCODE = 'P0002';
    END IF;
END;
$$;
MySQL Stored Procedure:
sql
DELIMITER //
CREATE PROCEDURE GetUserById(IN p_user_id INT)
BEGIN
    DECLARE user_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO user_exists FROM users WHERE id = p_user_id;

    IF user_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User not found';
    END IF;

    SELECT id, username, email, created_at
    FROM users
    WHERE id = p_user_id;
END //
DELIMITER ;
SQL Server Stored Procedure (T-SQL):
sql
CREATE PROCEDURE dbo.GetUserById
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Id = @UserId)
    BEGIN
        RAISERROR('User with ID %d not found', 16, 1, @UserId);
        RETURN;
    END

    SELECT Id, Username, Email, CreatedAt
    FROM dbo.Users
    WHERE Id = @UserId;
END;
GO
PostgreSQL 函数（PL/pgSQL）：
sql
CREATE OR REPLACE FUNCTION get_user_by_id(p_user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR, created_at TIMESTAMP)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username, u.email, u.created_at
    FROM users u
    WHERE u.id = p_user_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User with ID % not found', p_user_id
            USING ERRCODE = 'P0002';
    END IF;
END;
$$;
MySQL 存储过程：
sql
DELIMITER //
CREATE PROCEDURE GetUserById(IN p_user_id INT)
BEGIN
    DECLARE user_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO user_exists FROM users WHERE id = p_user_id;

    IF user_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User not found';
    END IF;

    SELECT id, username, email, created_at
    FROM users
    WHERE id = p_user_id;
END //
DELIMITER ;
SQL Server 存储过程（T-SQL）：
sql
CREATE PROCEDURE dbo.GetUserById
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Id = @UserId)
    BEGIN
        RAISERROR('User with ID %d not found', 16, 1, @UserId);
        RETURN;
    END

    SELECT Id, Username, Email, CreatedAt
    FROM dbo.Users
    WHERE Id = @UserId;
END;
GO

3. Add Transaction Management

3. 添加事务管理

PostgreSQL with Transaction:
sql
CREATE OR REPLACE FUNCTION transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount NUMERIC(15,2)
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    -- Debit source account
    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from_account AND balance >= p_amount;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds or invalid source account';
    END IF;

    -- Credit destination account
    UPDATE accounts SET balance = balance + p_amount
    WHERE id = p_to_account;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Invalid destination account';
    END IF;

    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$;
MySQL with Transaction:
sql
DELIMITER //
CREATE PROCEDURE TransferFunds(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(15,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from_account AND balance >= p_amount;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance + p_amount
    WHERE id = p_to_account;

    COMMIT;
END //
DELIMITER ;
PostgreSQL 事务版：
sql
CREATE OR REPLACE FUNCTION transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount NUMERIC(15,2)
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    -- Debit source account
    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from_account AND balance >= p_amount;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds or invalid source account';
    END IF;

    -- Credit destination account
    UPDATE accounts SET balance = balance + p_amount
    WHERE id = p_to_account;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Invalid destination account';
    END IF;

    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$;
MySQL 事务版：
sql
DELIMITER //
CREATE PROCEDURE TransferFunds(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(15,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from_account AND balance >= p_amount;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance + p_amount
    WHERE id = p_to_account;

    COMMIT;
END //
DELIMITER ;

4. Validate Syntax

4. 验证语法

Use the validation script to check procedure syntax:
bash
undefined
使用验证脚本检查存储过程语法：
bash
undefined

Validate PostgreSQL procedure

Validate PostgreSQL procedure

python3 {baseDir}/scripts/stored_procedure_syntax_validator.py
--db-type postgresql
--file procedure.sql
python3 {baseDir}/scripts/stored_procedure_syntax_validator.py
--db-type postgresql
--file procedure.sql

Validate MySQL procedure

Validate MySQL procedure

python3 {baseDir}/scripts/stored_procedure_syntax_validator.py
--db-type mysql
--file procedure.sql
undefined
python3 {baseDir}/scripts/stored_procedure_syntax_validator.py
--db-type mysql
--file procedure.sql
undefined

5. Deploy to Database

5. 部署到数据库

bash
undefined
bash
undefined

Deploy to PostgreSQL

Deploy to PostgreSQL

python3 {baseDir}/scripts/stored_procedure_deployer.py
--db-type postgresql
--host localhost
--database mydb
--file procedure.sql
python3 {baseDir}/scripts/stored_procedure_deployer.py
--db-type postgresql
--host localhost
--database mydb
--file procedure.sql

Deploy to MySQL

Deploy to MySQL

python3 {baseDir}/scripts/stored_procedure_deployer.py
--db-type mysql
--host localhost
--database mydb
--file procedure.sql
undefined
python3 {baseDir}/scripts/stored_procedure_deployer.py
--db-type mysql
--host localhost
--database mydb
--file procedure.sql
undefined

Output

输出结果

  • SQL procedure file with proper syntax for target database
  • Validation report confirming syntax correctness
  • Deployment confirmation with execution results
  • Rollback script for procedure removal
  • 符合目标数据库语法的SQL存储过程文件
  • 确认语法正确性的验证报告
  • 包含执行结果的部署确认信息
  • 用于移除存储过程的回滚脚本

Error Handling

错误处理

ErrorCauseSolution
permission denied
Missing CREATE PROCEDURE privilege
GRANT CREATE PROCEDURE ON database TO user;
syntax error
Invalid SQL for database typeUse database-specific syntax validator
function already exists
Procedure exists without OR REPLACEAdd 
OR REPLACE
or 
DROP
first
undefined column
Referenced column doesn't existVerify table schema before deployment
transaction aborted
Error during transactionCheck EXCEPTION handler and ROLLBACK logic
错误原因解决方案
permission denied
缺少CREATE PROCEDURE权限
GRANT CREATE PROCEDURE ON database TO user;
syntax error
数据库类型对应的SQL无效使用数据库专属语法验证工具
function already exists
存储过程已存在且未使用OR REPLACE添加
OR REPLACE
或先执行
DROP
undefined column
引用的列不存在部署前验证表结构
transaction aborted
事务执行过程中出错检查异常处理程序和回滚逻辑

Examples

示例

Generate CRUD procedures for a table:
User: Generate CRUD stored procedures for the 'products' table in PostgreSQL

Claude: I'll create four procedures for the products table:
1. create_product - Insert new product
2. get_product - Retrieve by ID
3. update_product - Update existing product
4. delete_product - Soft delete product
Create audit trigger:
User: Create a trigger to log all changes to the orders table

Claude: I'll create an audit trigger that:
1. Creates an orders_audit table if not exists
2. Captures INSERT, UPDATE, DELETE operations
3. Records old/new values, user, and timestamp
为表生成CRUD存储过程：
用户：为PostgreSQL中的'products'表生成CRUD存储过程

Claude：我将为products表创建四个存储过程：
1. create_product - 插入新产品
2. get_product - 根据ID查询产品
3. update_product - 更新现有产品
4. delete_product - 软删除产品
创建审计触发器：
用户：创建一个触发器来记录orders表的所有变更

Claude：我将创建一个审计触发器，具备以下功能：
1. 若不存在则创建orders_audit表
2. 捕获INSERT、UPDATE、DELETE操作
3. 记录新旧值、用户和时间戳

Resources

参考资源

  • {baseDir}/references/postgresql_stored_procedure_best_practices.md
  • {baseDir}/references/mysql_stored_procedure_best_practices.md
  • {baseDir}/references/sqlserver_stored_procedure_best_practices.md
  • {baseDir}/references/database_security_guidelines.md
  • {baseDir}/references/stored_procedure_optimization_techniques.md
  • {baseDir}/references/postgresql_stored_procedure_best_practices.md
  • {baseDir}/references/mysql_stored_procedure_best_practices.md
  • {baseDir}/references/sqlserver_stored_procedure_best_practices.md
  • {baseDir}/references/database_security_guidelines.md
  • {baseDir}/references/stored_procedure_optimization_techniques.md