Els procediments emmagatzemats són un component fonamental de PL/SQL que permet encapsular lògica de negoci en blocs de codi reutilitzables. Aquests procediments es poden invocar des de qualsevol lloc de l'aplicació, millorant la modularitat i la mantenibilitat del codi.
Què és un procediment emmagatzemat?
Un procediment emmagatzemat és un conjunt de sentències SQL i PL/SQL que es compilen i s'emmagatzemen a la base de dades. Un cop creat, es pot executar repetidament sense necessitat de recompilar-lo.
Avantatges dels procediments emmagatzemats:
- Reutilització del codi: Permet encapsular la lògica de negoci en un sol lloc.
- Millora del rendiment: Els procediments es compilen una vegada i es poden executar múltiples vegades.
- Seguretat: Permet controlar l'accés a les dades mitjançant permisos.
- Mantenibilitat: Facilita la gestió i actualització del codi.
Sintaxi bàsica d'un procediment emmagatzemat
La sintaxi per crear un procediment emmagatzemat és la següent:
CREATE OR REPLACE PROCEDURE nom_procediment (
paràmetre1 IN tipus,
paràmetre2 OUT tipus,
paràmetre3 IN OUT tipus
) IS
BEGIN
-- Cos del procediment
NULL; -- Placeholder per a codi
END nom_procediment;
/Explicació dels components:
- CREATE OR REPLACE PROCEDURE: Inicia la definició del procediment.
OR REPLACEpermet actualitzar un procediment existent. - nom_procediment: Nom del procediment.
- paràmetre1, paràmetre2, paràmetre3: Paràmetres d'entrada (
IN), sortida (OUT) o entrada/sortida (IN OUT). - IS: Indica l'inici de la definició del procediment.
- BEGIN...END: Bloc que conté el cos del procediment.
- NULL;: Placeholder per a codi, es pot substituir per les sentències necessàries.
Exemple pràctic
A continuació, es mostra un exemple de procediment emmagatzemat que calcula el salari anual d'un empleat basat en el seu salari mensual.
Creació del procediment
CREATE OR REPLACE PROCEDURE calcular_salari_anual (
p_empleat_id IN NUMBER,
p_salari_anual OUT NUMBER
) IS
v_salari_mensual NUMBER;
BEGIN
-- Obtenir el salari mensual de l'empleat
SELECT salari INTO v_salari_mensual
FROM empleats
WHERE empleat_id = p_empleat_id;
-- Calcular el salari anual
p_salari_anual := v_salari_mensual * 12;
END calcular_salari_anual;
/Explicació del codi:
- p_empleat_id: Paràmetre d'entrada que identifica l'empleat.
- p_salari_anual: Paràmetre de sortida que contindrà el salari anual calculat.
- v_salari_mensual: Variable local per emmagatzemar el salari mensual de l'empleat.
- SELECT salari INTO v_salari_mensual: Obté el salari mensual de la taula
empleats. - p_salari_anual := v_salari_mensual * 12;: Calcula el salari anual multiplicant el salari mensual per 12.
Execució del procediment
Per executar el procediment, es pot utilitzar el següent bloc PL/SQL:
DECLARE
v_salari_anual NUMBER;
BEGIN
calcular_salari_anual(101, v_salari_anual);
DBMS_OUTPUT.PUT_LINE('El salari anual és: ' || v_salari_anual);
END;
/Explicació del codi:
- DECLARE: Inicia la declaració de variables.
- v_salari_anual: Variable per emmagatzemar el resultat del procediment.
- calcular_salari_anual(101, v_salari_anual);: Crida al procediment amb l'ID de l'empleat 101.
- DBMS_OUTPUT.PUT_LINE: Mostra el salari anual calculat.
Exercicis pràctics
Exercici 1: Crear un procediment per actualitzar el salari d'un empleat
Descripció: Crea un procediment emmagatzemat que actualitzi el salari d'un empleat basat en el seu ID i un percentatge d'increment.
Solució:
CREATE OR REPLACE PROCEDURE actualitzar_salari (
p_empleat_id IN NUMBER,
p_percentatge IN NUMBER
) IS
BEGIN
UPDATE empleats
SET salari = salari + (salari * p_percentatge / 100)
WHERE empleat_id = p_empleat_id;
END actualitzar_salari;
/Exercici 2: Crear un procediment per eliminar un empleat
Descripció: Crea un procediment emmagatzemat que elimini un empleat de la taula empleats basat en el seu ID.
Solució:
CREATE OR REPLACE PROCEDURE eliminar_empleat (
p_empleat_id IN NUMBER
) IS
BEGIN
DELETE FROM empleats
WHERE empleat_id = p_empleat_id;
END eliminar_empleat;
/Errors comuns i consells
- Oblidar el
/al final del procediment: Assegura't d'incloure el/per indicar el final del bloc PL/SQL. - No gestionar excepcions: Sempre és una bona pràctica incloure la gestió d'excepcions per manejar errors inesperats.
- No validar els paràmetres d'entrada: Verifica que els paràmetres d'entrada siguin vàlids abans de processar-los.
Conclusió
Els procediments emmagatzemats són una eina poderosa per encapsular la lògica de negoci i millorar la modularitat del codi. Amb la pràctica, es poden crear procediments eficients i segurs que simplifiquin la gestió de la base de dades i millorin el rendiment de les aplicacions. En el següent tema, explorarem les funcions en PL/SQL, que són similars als procediments però amb algunes diferències clau.
Curs de PL/SQL
Mòdul 1: Introducció a PL/SQL
Mòdul 2: Fonaments de PL/SQL
- Estructura del bloc PL/SQL
- Variables i tipus de dades
- Estructures de control
- Cursors
- Gestió d'excepcions