存储过程与函数实战指南：PostgreSQL/MySQL 业务逻辑封装、性能优化与最佳实践

stored-procedures by aj-geddes/useful-ai-prompts

126 周安装量

144 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/aj-geddes/useful-ai-prompts --skill stored-procedures

自动化数据库性能优化

🇨🇳中文介绍

存储过程与函数

概述

实现存储过程、函数和触发器，用于业务逻辑、数据验证和性能优化。涵盖过程设计、错误处理和性能考量。

使用场景

业务逻辑封装
复杂的多步骤操作
数据验证和约束
审计跟踪维护
性能优化
跨应用程序的代码复用
基于触发器的自动化

快速开始

PostgreSQL - 标量函数：

-- 创建返回单个值的函数
CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal DECIMAL,
  p_tax_rate DECIMAL,
  p_shipping DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::NUMERIC, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 在查询中使用
SELECT id, subtotal, calculate_order_total(subtotal, 0.08, 10) as total
FROM orders;

-- 或在应用程序代码中使用
SELECT * FROM orders
WHERE calculate_order_total(subtotal, 0.08, 10) > 100;

参考指南

references/ 目录下的详细实现：

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

指南	内容
简单函数	简单函数
存储过程	存储过程
简单过程	简单过程
带错误处理的复杂过程	带错误处理的复杂过程
PostgreSQL 触发器	PostgreSQL 触发器
MySQL 触发器	MySQL 触发器

🇺🇸English

Stored Procedures & Functions

Overview
When to Use
Quick Start
Reference Guides
Best Practices

Overview

Implement stored procedures, functions, and triggers for business logic, data validation, and performance optimization. Covers procedure design, error handling, and performance considerations.

When to Use

Business logic encapsulation
Complex multi-step operations
Data validation and constraints
Audit trail maintenance
Performance optimization
Code reusability across applications
Trigger-based automation

Quick Start

PostgreSQL - Scalar Function:

-- Create function returning single value
CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal DECIMAL,
  p_tax_rate DECIMAL,
  p_shipping DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::NUMERIC, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use in queries
SELECT id, subtotal, calculate_order_total(subtotal, 0.08, 10) as total
FROM orders;

-- Or in application code
SELECT * FROM orders
WHERE calculate_order_total(subtotal, 0.08, 10) > 100;

Reference Guides

Detailed implementations in the references/ directory:

Guide	Contents
Simple Functions	Simple Functions
Stored Procedures	Stored Procedures
Simple Procedures	Simple Procedures
Complex Procedures with Error Handling	Complex Procedures with Error Handling
PostgreSQL Triggers	PostgreSQL Triggers
MySQL Triggers	MySQL Triggers

Best Practices

✅ DO

Follow established patterns and conventions
Write clean, maintainable code
Add appropriate documentation
Test thoroughly before deploying

❌ DON'T

Skip testing or validation
Ignore error handling
Hard-code configuration values

Weekly Installs

103

Repository

aj-geddes/usefu…-prompts

GitHub Stars

116

First Seen

Jan 21, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykWarn

Installed on

opencode85

gemini-cli83

codex82

claude-code81

cursor78

github-copilot70