Shop Project - Documentation

language

Shop LDM

Part 00: Functions & Procedures

This module contains reusable utility functions and procedures used across the entire system. It serves as a "toolbox" for common database operations and increases code maintainability.

🗺️ Stage 0 | 📚 Current | 📟 2026 06 15 | 📍 Database

DropForeignKeyIfExists Procedure

Input:
    - p_table_name: VARCHAR(64)        -- Name of the table
    - p_fk_name: VARCHAR(64)           -- Name of the foreign key constraint

Process:
   1. Check if table exists            -- Validates table existence
   2. Check if foreign key exists      -- Validates foreign key existence
   3. If both exist: DROP FOREIGN KEY  -- Drops foreign key only if both conditions met

Output:
    - Status message                   -- Returns execution status message

generateSwissPhone Function

Purpose: Generates random, valid Swiss mobile phone numbers for test data.
Input:

Process:
   1. Random prefix: 076|077|078|079   -- Selects random Swiss mobile prefix
   2. Random 3-digit block             -- Generates three random blocks for remaining digits
   3. Random 2-digit block
   4. Random 2-digit block

Output VARCHAR(20):
    - Format: XXX XXX XX XX            -- Formatted in standard Swiss mobile layout

p_check_has_one Procedure

Purpose: Universal check for existence of a record in any given table.

Input:
   - p_table_name: VARCHAR(64)        -- Target table
   - p_column_name: VARCHAR(64)       -- Target column
   - p_ID: INT                        -- ID to be checked

Process:
   1. Security Check                   -- Validates table and column names against schema
   2. Dynamic Query Building           -- Creates dynamic SELECT EXISTS string
   3. Execution                        -- Runs the query and assigns result

Output (OUT):
   - p_exists: BOOLEAN            -- Returns true (1) if found, false (0) if not