Skip to main content

​
Overview

This function inserts a new employee into the tab_emplea table. The employee ID is automatically generated by incrementing the maximum existing ID by 1.

​
Function Signature

CREATE OR REPLACE FUNCTION fun_insert_emplea(
    wnom_emplea tab_emplea.nom_emplea%TYPE,
    wape_emplea tab_emplea.ape_emplea%TYPE,
    wind_genero tab_emplea.ind_genero%TYPE,
    wdir_emplea tab_emplea.dir_emplea%TYPE,
    wtel_emplea tab_emplea.tel_emplea%TYPE,
    wind_estrato tab_emplea.ind_estrato%TYPE,
    wind_est_civil tab_emplea.ind_est_civil%TYPE,
    wnum_hijos tab_emplea.num_hijos%TYPE,
    wval_tipo_sangre tab_emplea.val_tipo_sangre%TYPE,
    wval_edad tab_emplea.val_edad%TYPE,
    wid_cargo tab_emplea.id_cargo%TYPE,
    wval_sal_basico tab_emplea.val_sal_basico%TYPE,
    wfec_ingreso tab_emplea.fec_ingreso%TYPE
) RETURNS VOID

​
Parameters

​
wnom_emplea
VARCHAR
Employee’s first name
​
wape_emplea
VARCHAR
Employee’s last name
​
wind_genero
CHAR(1)
Gender indicator (e.g., ‘M’ for male, ‘F’ for female)
​
wdir_emplea
VARCHAR
Employee’s address
​
wtel_emplea
VARCHAR
Employee’s phone number
​
wind_estrato
INTEGER
Socioeconomic stratum indicator (1-6)
​
wind_est_civil
CHAR
Civil status indicator (e.g., ‘S’ for single, ‘C’ for married)
​
wnum_hijos
INTEGER
Number of children
​
wval_tipo_sangre
VARCHAR
Blood type (e.g., ‘O+’, ‘A-’, ‘B+’, ‘AB-’)
​
wval_edad
INTEGER
Employee’s age
​
wid_cargo
INTEGER
Position ID (foreign key to tab_cargos)
​
wval_sal_basico
NUMERIC
Base salary amount
​
wfec_ingreso
DATE
Date of hire

​
Return Type

VOID - This function does not return a value. It performs an INSERT operation.

​
Business Logic

  1. Automatic ID Generation: The employee ID is automatically calculated as MAX(id_emplea) + 1, starting from 1 if no employees exist
  2. Referential Integrity: The function expects a valid wid_cargo that exists in the tab_cargos table
  3. Complete Record: All employee information must be provided; no fields are optional

​
Usage Example

-- Insert a new employee
SELECT fun_insert_emplea(
    'Juan',                    -- First name
    'Pérez',                   -- Last name
    'M',                       -- Gender
    'Calle 123 #45-67',       -- Address
    '3001234567',             -- Phone
    3,                        -- Stratum
    'C',                      -- Married
    2,                        -- Number of children
    'O+',                     -- Blood type
    35,                       -- Age
    1,                        -- Position ID
    2500000.00,               -- Base salary
    '2024-03-24'              -- Hire date
);

​
Notes

  • This function was created due to referential integrity constraints and must be executed before inserting records into tab_novedades and tab_nomina
  • The employee ID is auto-generated and should not be provided by the caller
  • Ensure the position ID exists in tab_cargos before calling this function

​
Source

Location: ~/workspace/source/func/emplea/fun_insert_emplea.sql:10 Author: Camilo Suarez
Date: 24/03/2024

Build docs developers (and LLMs) love

Get started for freeTalk to us