SQL Procedures - Documentation

Overview

This section contains stored procedures for complex operations in the Procesar system.

Available Procedure Categories

Data Management Procedures

• User Management: Procedures for creating, updating, and managing user accounts
• Data Validation: Procedures for validating data integrity and format
• Data Migration: Procedures for transferring data between systems
• Data Backup: Procedures for creating and restoring data backups

Business Logic Procedures

• Transaction Processing: Procedures for handling business transactions
• Account Management: Procedures for managing customer accounts
• Report Generation: Procedures for creating business reports
• Audit Procedures: Procedures for system auditing and compliance

System Administration Procedures

• Database Maintenance: Procedures for database optimization and cleanup
• Performance Monitoring: Procedures for monitoring system performance
• Security Management: Procedures for managing system security
• Error Handling: Procedures for logging and resolving system errors

Procedure Examples

User Registration Procedure

CREATE OR REPLACE FUNCTION register_user(
  p_nombre VARCHAR(100),
  p_apellido_paterno VARCHAR(100),
  p_apellido_materno VARCHAR(100),
  p_curp VARCHAR(18),
  p_email VARCHAR(255),
  p_telefono VARCHAR(20),
  p_fecha_nacimiento DATE,
  OUT p_resultado INTEGER
) RETURNS INTEGER AS $$
DECLARE
  v_curp_valida BOOLEAN;
  v_email_valido BOOLEAN;
  v_telefono_valido BOOLEAN;
  
BEGIN
  -- Validate CURP format
  SELECT LENGTH(p_curp) = 18 
    AND p_curp ~ '^[A-Z]{4}[0-9]{6}[HM][A-Z]{5}[0-2][A-Z][0-9]$'
    INTO v_curp_valida;
    
  -- Validate email format
  SELECT p_email ~ '^[A-Za-z0-9._%+-]+[A-Za-z0-9]@.[A-Za-z]{2,}$'
    INTO v_email_valido;
    
  -- Validate phone format
  SELECT p_telefono ~ '^[0-9]{10,15}$'
    INTO v_telefono_valido;
    
  -- Check if user already exists
  SELECT COUNT(*) > 0
  FROM usuarios
    WHERE curp = p_curp
    INTO v_usuario_existe;
    
  -- Insert new user if valid and doesn't exist
  IF v_curp_valida = TRUE 
    AND v_email_valido = TRUE 
    AND v_telefono_valido = TRUE 
    AND v_usuario_existe = FALSE THEN
      INSERT INTO usuarios (
        nombre, apellido_paterno, apellido_materno, 
        curp, email, telefono, fecha_nacimiento
      ) VALUES (
        p_nombre, p_apellido_paterno, p_apellido_materno,
        p_curp, p_email, p_telefono, p_fecha_nacimiento
      );
      
      SET p_resultado = 1; -- Success
    ELSE
      SET p_resultado = 0; -- User already exists
    END IF;
    
  RETURN p_resultado;
END;
$$ LANGUAGE plpgsql;

Transaction Processing Procedure

CREATE OR REPLACE FUNCTION process_transaction(
  p_id_usuario INTEGER,
  p_id_cuenta INTEGER,
  p_monto DECIMAL(15,2),
  p_tipo_transaccion VARCHAR(10),
  OUT p_resultado INTEGER,
  OUT p_id_transaccion INTEGER
) RETURNS INTEGER AS $$
DECLARE
  v_saldo_cuenta DECIMAL(15,2);
  v_limite_transaccion DECIMAL(15,2);
  v_id_tipo_transaccion INTEGER;
  
BEGIN
  -- Get account balance
  SELECT saldo INTO v_saldo_cuenta
  FROM cuentas
  WHERE id_cuenta = p_id_cuenta;
  
  -- Get transaction limits
  SELECT limite INTO v_limite_transaccion
  FROM tipos_transaccion
  WHERE codigo = p_tipo_transaccion;
  
  -- Get transaction type ID
  SELECT id INTO v_id_tipo_transaccion
  FROM tipos_transaccion
  WHERE codigo = p_tipo_transaccion;
  
  -- Check if transaction is valid
  IF p_monto <= 0 OR p_monto > v_limite_transaccion THEN
    SET p_resultado = 0; -- Invalid amount
  ELSE
    -- Check if sufficient balance
    IF v_saldo_cuenta >= p_monto THEN
      -- Process transaction
      INSERT INTO transacciones (
        id_usuario, id_cuenta, monto, 
        tipo_transaccion, fecha_transaccion
      ) VALUES (
        p_id_usuario, p_id_cuenta, p_monto,
        p_tipo_transaccion, CURRENT_TIMESTAMP
      );
      
      -- Update account balance
      UPDATE cuentas
      SET saldo = saldo - p_monto
      WHERE id_cuenta = p_id_cuenta;
      
      -- Get new transaction ID
      SELECT currval('transacciones_id_seq') INTO p_id_transaccion;
      
      SET p_resultado = 1; -- Success
    ELSE
      SET p_resultado = 2; -- Insufficient balance
    END IF;
    
  RETURN p_resultado;
END;
$$ LANGUAGE plpgsql;

Implementation Guidelines

For Developers

1. Use Transactions: Wrap procedures in transactions for data consistency
2. Error Handling: Implement comprehensive error handling and logging
3. Parameter Validation: Validate all input parameters before processing
4. Performance Optimization: Use appropriate indexes and query optimization

For Database Administrators

1. Regular Maintenance: Schedule regular procedure maintenance and optimization
2. Security Auditing: Monitor procedure execution and access patterns
3. Backup Procedures: Create backup procedures for all critical data operations
4. Documentation: Maintain complete documentation for all procedures

Best Practices

Security

• SQL Injection Prevention: Use parameterized queries and input validation
• Least Privilege: Execute procedures with minimum required permissions
• Audit Logging: Log all procedure executions with user context
• Access Control: Implement proper access controls for procedure execution

Performance

• Query Optimization: Use appropriate indexes and query plans
• Resource Management: Monitor and optimize resource usage
• Batch Processing: Process multiple records efficiently when possible
• Connection Pooling: Manage database connections efficiently

Support

For SQL procedure issues:

• Database Team: Contact database administrators
• Development Team: Consult with application developers
• Documentation: Review specific procedure examples and guidelines