Establecer procedimientos en pl/sql para bases de datos

La sintaxis seria:
PROCEDURE nombre [(parametro[,parametro,...])] IS
declaraciones locales
BEGIN
instrucciones
[EXCEPTION
control de errores
END [nombre];
***************************
Algun ejemplo:
Ejemplo 1:
**************************
CREATE OR REPLACE PROCEDURE Proc_Dades_Usuaris IS
CURSOR cur_unics_unics IS
SELECT tb_inicials.inicials
FROM (SELECT SUBSTR(PRITB992.IDE_USUARI,10,1)||''||SUBSTR(PRITB992.IDE_USUARI,11,1)||SUBSTR(PRITB992.IDE_USUARI,12,1) inicials
FROM PRITB992
WHERE SUBSTR(PRITB992.IDE_USUARI,9,1) = 'U'
GROUP BY SUBSTR(PRITB992.IDE_USUARI,10,1)||''||SUBSTR(PRITB992.IDE_USUARI,11,1)||SUBSTR(PRITB992.IDE_USUARI,12,1)
HAVING COUNT(*) < 2) tb_inicials, PRITB031
WHERE (SUBSTR(PRITB031.PROV_NOM,1,1)||''||SUBSTR(PRITB031.PROV_COGNOM1,1,1)||''||SUBSTR(PRITB031.PROV_COGNOM2,1,1)) = tb_inicials.inicials
GROUP BY tb_inicials.inicials
HAVING COUNT(*) < 2;
reg_unics_unics cur_unics_unics%ROWTYPE;
CURSOR cur_dades_pritb031(p_inicials VARCHAR2) IS
SELECT *
FROM PRITB031
WHERE (SUBSTR(PRITB031.PROV_NOM,1,1)||''||SUBSTR(PRITB031.PROV_COGNOM1,1,1)||''||SUBSTR(PRITB031.PROV_COGNOM2,1,1)) = p_inicials;
reg_dades_pritb031 cur_dades_pritb031%ROWTYPE;
BEGIN
OPEN cur_unics_unics;
FETCH cur_unics_unics INTO reg_unics_unics;
WHILE cur_unics_unics%FOUND LOOP
OPEN cur_dades_pritb031(reg_unics_unics.inicials);
FETCH cur_dades_pritb031 INTO reg_dades_pritb031;
IF cur_dades_pritb031%FOUND THEN
UPDATE PRITB992
SET ide_nom_usuari = reg_dades_pritb031.PROV_NOM||' '||reg_dades_pritb031.PROV_COGNOM1||' '||reg_dades_pritb031.PROV_COGNOM2,
ide_dni = reg_dades_pritb031.PROV_DNI_PROVEIDOR,
ide_categ_prof_c = reg_dades_pritb031.PROV_CATEGORIA,
ide_tipusdoc = reg_dades_pritb031.PROV_TIPUSDOC
WHERE SUBSTR(PRITB992.IDE_USUARI,9,1) = 'U'
AND SUBSTR(PRITB992.IDE_USUARI,10,1)||''||SUBSTR(PRITB992.IDE_USUARI,11,1)||SUBSTR(PRITB992.IDE_USUARI,12,1) = reg_unics_unics.inicials;
COMMIT;
END IF;
CLOSE cur_dades_pritb031;
FETCH cur_unics_unics INTO reg_unics_unics;
END LOOP;
CLOSE cur_unics_unics;
END;
/
*****************************
Ejemplo2:
*************************
CREATE OR REPLACE PROCEDURE vispd009 (v_ag_centre_cod IN VARCHAR2,
v_ag_centre_cla IN VARCHAR2,
v_ag_servei IN VARCHAR2,
v_ag_modul IN VARCHAR2,
v_ag_data IN NUMBER,
v_ag_numero IN NUMBER,
v_ag_cip IN VARCHAR2,
tipus_agenda VARCHAR2
)
IS
/* Declaracions */
no_hi_ha_visita EXCEPTION;
no_inserir EXCEPTION;
total_intervals NUMBER;
v_ag_tipus_c VARCHAR2(4);
v_ag_tipus_n VARCHAR2(4);
v_ag_cita VARCHAR2(1);
v_ag_hora NUMBER;
v_ag_durada NUMBER;
v_ag_durada_n NUMBER;
v_ag_bloc_p VARCHAR2(5);
v_ag_bloc_d NUMBER;
v_ag_bloc_hi NUMBER;
v_rowid ROWID;
total NUMBER;
hora_inicial VISTB208.ag_hora%TYPE;
hora_final VISTB208.ag_hora%TYPE;
hora VISTB208.ag_hora%TYPE;
durada VISTB208.ag_durada_c%TYPE;
numero_nou VISTB208.ag_numero%TYPE;
rowid_pos ROWID;
rowid_ant ROWID;
inserir BOOLEAN;
temps_visites NUMBER;
total_temps NUMBER;
hora_pos VISTB208.ag_hora%TYPE;
hora_ant VISTB208.ag_hora%TYPE;
durada_pos VISTB208.ag_durada_c%TYPE;
durada_ant VISTB208.ag_durada_c%TYPE;
numero_pos VISTB208.ag_numero%TYPE;
numero_ant VISTB208.ag_numero%TYPE;
nombre_visites NUMBER;
nombre_visites_max VISTB204.th_num%TYPE;
hora_nova VISTB208.ag_hora%TYPE;
durada_nova VISTB208.ag_durada_c%TYPE;
CURSOR visites IS
SELECT ag_hora, ag_durada_c
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_modul = v_ag_modul
AND ag_servei = v_ag_servei
AND ag_data = v_ag_data
AND ag_cip <> 'FFFFFFFFFFFFF'
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND (ag_hora BETWEEN
hora_inicial AND hora_final
OR
ag_hora + ag_durada_c * 60 BETWEEN
hora_inicial AND hora_final
OR
(ag_hora < hora_inicial AND
ag_hora + ag_durada_c * 60 > hora_final
)
)
AND ag_ddb = 1
ORDER BY ag_hora;
BEGIN
/* Cos del procediment */
/* Seleccionem la informació que ens fa falta de la visita */
BEGIN
SELECT ag_tipus_c,
ag_tipus_n,
ag_cita,
ag_hora,
ag_durada_c,
ag_durada_n,
ag_bloc_p,
ag_bloc_d,
ag_bloc_hi,
ROWID
INTO v_ag_tipus_c,
v_ag_tipus_n,
v_ag_cita,
v_ag_hora,
v_ag_durada,
v_ag_durada_n,
v_ag_bloc_p,
v_ag_bloc_d,
v_ag_bloc_hi,
v_rowid
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip = v_ag_cip
AND ag_numero = v_ag_numero
FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN RAISE no_hi_ha_visita;
END;
IF v_ag_cita = 'P' THEN
/* tipus de citació programada */
/* Abans d'esborrar s'ha de mirar si la visita està solapada. Si es així */
/* s'ha de crear possibles forats lliures. Sino es la propia visita la */
/* que passa a ser un forat */
/* Comptar les visites que estan solapades */
SELECT COUNT('x')
INTO total
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip <> 'FFFFFFFFFFFFF'
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND (ag_hora BETWEEN
v_ag_hora AND v_ag_hora + v_ag_durada * 60
OR
ag_hora + ag_durada_c * 60 BETWEEN
v_ag_hora AND v_ag_hora + v_ag_durada * 60
OR
(ag_hora < v_ag_hora AND
ag_hora + ag_durada_c * 60 > v_ag_hora + v_ag_durada * 60
)
)
AND ag_ddb = 1
AND ROWID <> v_rowid;
IF total > 0 THEN
/* Hi ha solapament */
/* Hem de reestructurar tot l'interval de temps que va des de */
/* la visita anterior i la visita posterior, per això apliquem */
/* l'algoritme de creació de forats entre aquestes dues hores */
/* Esborrem la visita per que no moleste */
DELETE FROM VISTB208
WHERE ROWID = v_rowid;
/* Seleccionem la visita anterior per dalt */
SELECT MAX(ag_hora + ag_durada_c * 60)
INTO hora_inicial
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip <> 'FFFFFFFFFFFFF'
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND ag_hora + ag_durada_c * 60 <= v_ag_hora
AND ag_ddb = 1;
/* Si no retorna valor seleccionem l'hora inicial del tram */
IF hora_inicial IS NULL THEN
hora_inicial := v_ag_bloc_hi;
END IF;
/* Seleccionem la visita posterior per baix */
SELECT MIN(ag_hora)
INTO hora_final
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip <> 'FFFFFFFFFFFFF'
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND ag_hora >= v_ag_hora + v_ag_durada * 60
AND ag_ddb = 1;
/* Si no retorna valor seleccionem l'hora final del tram */
IF hora_final IS NULL THEN
SELECT bh_bloc_hf
INTO hora_final
FROM VISTB203
WHERE bh_centre_cod = v_ag_centre_cod
AND bh_centre_cla = v_ag_centre_cla
AND bh_servei = v_ag_servei
AND bh_modul = v_ag_modul
AND bh_bloc_p = v_ag_bloc_p
AND bh_bloc_d = v_ag_bloc_d
AND bh_bloc_hi = v_ag_bloc_hi
AND bh_ddb = 1;
END IF;
/* Esborrem els trams lliures que hi han entre les dos hores */
DELETE FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip = 'FFFFFFFFFFFFF'
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND ag_hora BETWEEN hora_inicial AND hora_final
AND ag_ddb = 1;
hora := hora_inicial;
durada := 0;
numero_nou := 0;
FOR vis IN visites
LOOP
IF vis.ag_hora > hora + durada * 60 THEN
/* Insertem tram lliure */
IF numero_nou = 0 THEN
SELECT
NVL(MAX(ag_numero),500) + 1
INTO numero_nou
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip ||''= 'FFFFFFFFFFFFF';
ELSE
numero_nou := numero_nou + 1;
END IF;
INSERT INTO VISTB208 (
ag_centre_cod,
ag_centre_cla,
ag_servei,
ag_modul,
ag_data,
ag_numero,
ag_cip,
ag_tipus_c,
ag_tipus_n,
ag_cita,
ag_hora,
ag_durada_c,
ag_durada_n,
ag_for,
ag_bloc_p,
ag_bloc_d,
ag_bloc_hi,
ag_centre_cod_tecl,
ag_centre_cla_tecl,
ag_ddm,
ag_ddb)
VALUES (
v_ag_centre_cod,
v_ag_centre_cla,
v_ag_servei,
v_ag_modul,
v_ag_data,
numero_nou,
'FFFFFFFFFFFFF',
'*',
'*',
'P',
hora+durada*60,
(vis.ag_hora - (hora+durada*60))/60,
(vis.ag_hora - (hora+durada*60))/60,
'N',
v_ag_bloc_p,
v_ag_bloc_d,
v_ag_bloc_hi,
v_ag_centre_cod,
v_ag_centre_cla,
TO_NUMBER(TO_CHAR(SYSDATE,'j')),
1
);
END IF;
IF vis.ag_hora + vis.ag_durada_c * 60 >
hora + durada * 60 THEN
/* nova hora inici d'un possible tram lliure */
hora := vis.ag_hora;
durada := vis.ag_durada_c;
END IF;
END LOOP;
/* Insersió d'un possible ultim forat */
IF hora+durada*60 < hora_final THEN
/* Insertem tram lliure */
IF numero_nou = 0 THEN
SELECT
NVL(MAX(ag_numero),500) + 1
INTO numero_nou
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip ||''= 'FFFFFFFFFFFFF';
ELSE
numero_nou := numero_nou + 1;
END IF;
INSERT INTO VISTB208 (
ag_centre_cod,
ag_centre_cla,
ag_servei,
ag_modul,
ag_data,
ag_numero,
ag_cip,
ag_tipus_c,
ag_tipus_n,
ag_cita,
ag_hora,
ag_durada_c,
ag_durada_n,
ag_for,
ag_bloc_p,
ag_bloc_d,
ag_bloc_hi,
ag_centre_cod_tecl,
ag_centre_cla_tecl,
ag_ddm,
ag_ddb)
VALUES (
v_ag_centre_cod,
v_ag_centre_cla,
v_ag_servei,
v_ag_modul,
v_ag_data,
numero_nou,
'FFFFFFFFFFFFF',
'*',
'*',
'P',
hora+durada*60,
(hora_final - (hora+durada*60))/60,
(hora_final - (hora+durada*60))/60,
'N',
v_ag_bloc_p,
v_ag_bloc_d,
v_ag_bloc_hi,
v_ag_centre_cod,
v_ag_centre_cla,
TO_NUMBER(TO_CHAR(SYSDATE,'j')),
1
);
END IF;
ELSE
/* no hi havia solapament */
hora_nova := v_ag_hora;
durada_nova := v_ag_durada;
numero_nou := 0;
/* Mirar si hi ha forat anterior */
BEGIN
SELECT ROWID, ag_hora, ag_durada_c, ag_numero
INTO rowid_ant, hora_ant, durada_ant, numero_ant
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip = 'FFFFFFFFFFFFF'
AND ag_hora + ag_durada_c * 60 = v_ag_hora
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND ag_ddb = 1
FOR UPDATE ;
hora_nova := hora_ant;
durada_nova := durada_nova + durada_ant;
numero_nou := numero_ant;
DELETE FROM VISTB208
WHERE ROWID = rowid_ant;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
BEGIN
/* Mirar si hi ha forat posterior */
SELECT ROWID, ag_hora, ag_durada_c, ag_numero
INTO rowid_pos, hora_pos, durada_pos, numero_pos
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip = 'FFFFFFFFFFFFF'
AND ag_hora +0 = v_ag_hora + v_ag_durada * 60
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND ag_ddb = 1
FOR UPDATE ;
durada_nova := durada_nova + durada_pos;
numero_nou := numero_pos;
DELETE FROM VISTB208
WHERE ROWID = rowid_pos;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/* Convertir la visita en forat (modificant numero de visita) */
IF numero_nou = 0 THEN
/* Si no podem reutilitzar el numero de visita, */
/* seleccionem un de nou */
SELECT
NVL(MAX(ag_numero),500) + 1
INTO numero_nou
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip ||''= 'FFFFFFFFFFFFF';
END IF;
UPDATE VISTB208
SET ag_numero = numero_nou,
ag_cip = 'FFFFFFFFFFFFF',
ag_tipus_c = '*',
ag_tipus_n = '*',
ag_cita = 'P',
ag_hora = hora_nova,
ag_durada_c = durada_nova,
ag_durada_n = durada_nova,
ag_for = 'N'
WHERE ROWID = v_rowid;
END IF;
ELSE
/* tipus de citació expontania */
/* esborrem la visita directament */
DELETE FROM VISTB208
WHERE ROWID = v_rowid;
/* Però hem de crear un forat en el cas que calgui */
IF tipus_agenda = 'N' THEN
SELECT COUNT(ag_cip)
INTO nombre_visites
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip <> 'FFFFFFFFFFFFF'
AND ag_cita = v_ag_cita
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND ag_ddb = 1;
SELECT SUM(th_num)
INTO nombre_visites_max
FROM VISTB204
WHERE th_centre_cod = v_ag_centre_cod
AND th_centre_cla = v_ag_centre_cla
AND th_servei = v_ag_servei
AND th_modul = v_ag_modul
AND th_bloc_p = v_ag_bloc_p
AND th_bloc_d = v_ag_bloc_d
AND th_bloc_hi = v_ag_bloc_hi
AND th_cita = v_ag_cita
AND th_ddb = 1;
IF nombre_visites < nombre_visites_max THEN
inserir := TRUE;
ELSE
inserir := FALSE;
END IF;
ELSE
/* agenda per temps */
SELECT SUM(ag_durada_c * 60)
INTO temps_visites
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip <> 'FFFFFFFFFFFFF'
AND ag_cita = v_ag_cita
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi
AND ag_ddb = 1;
SELECT bh_bloc_hf - bh_bloc_hi
INTO total_temps
FROM VISTB203
WHERE bh_centre_cod = v_ag_centre_cod
AND bh_centre_cla = v_ag_centre_cla
AND bh_servei = v_ag_servei
AND bh_modul = v_ag_modul
AND bh_bloc_p = v_ag_bloc_p
AND bh_bloc_d = v_ag_bloc_d
AND bh_bloc_hi = v_ag_bloc_hi
AND bh_ddb = 1;
IF temps_visites < total_temps THEN
inserir := TRUE;
ELSE
inserir := FALSE;
END IF;
END IF;
IF inserir THEN
/* mirem si l'interval ja existeix */
SELECT /*+ index(vistb208 usuin20805) */
COUNT(ag_cip)
INTO total_intervals
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip||'' = 'FFFFFFFFFFFFF'
AND ag_bloc_p = v_ag_bloc_p
AND ag_bloc_d = v_ag_bloc_d
AND ag_bloc_hi = v_ag_bloc_hi;
IF total_intervals > 0 THEN
RAISE no_inserir;
END IF;
/* Inserim un nou interval i per això ... */
/* ... busquem un numero màxim de visita i ... */
SELECT
NVL(MAX(ag_numero),500) + 1
INTO numero_nou
FROM VISTB208
WHERE ag_centre_cod = v_ag_centre_cod
AND ag_centre_cla = v_ag_centre_cla
AND ag_servei = v_ag_servei
AND ag_modul = v_ag_modul
AND ag_data = v_ag_data
AND ag_cip ||''= 'FFFFFFFFFFFFF';
/* ... busquem l'hora i durada del tram */
SELECT bh_bloc_hi, (bh_bloc_hf - bh_bloc_hi) / 60
INTO hora_nova, durada_nova
FROM VISTB203
WHERE bh_centre_cod = v_ag_centre_cod
AND bh_centre_cla = v_ag_centre_cla
AND bh_servei = v_ag_servei
AND bh_modul = v_ag_modul
AND bh_bloc_p = v_ag_bloc_p
AND bh_bloc_d = v_ag_bloc_d
AND bh_bloc_hi = v_ag_bloc_hi
AND bh_ddb = 1;
INSERT INTO VISTB208 (
ag_centre_cod,
ag_centre_cla,
ag_servei,
ag_modul,
ag_data,
ag_numero,
ag_cip,
ag_tipus_c,
ag_tipus_n,
ag_cita,
ag_hora,
ag_durada_c,
ag_durada_n,
ag_for,
ag_bloc_p,
ag_bloc_d,
ag_bloc_hi,
ag_centre_cod_tecl,
ag_centre_cla_tecl,
ag_ddm,
ag_ddb)
VALUES (
v_ag_centre_cod,
v_ag_centre_cla,
v_ag_servei,
v_ag_modul,
v_ag_data,
numero_nou,
'FFFFFFFFFFFFF',
'*',
'*',
'E',
hora_nova,
durada_nova,
durada_nova,
'N',
v_ag_bloc_p,
v_ag_bloc_d,
v_ag_bloc_hi,
v_ag_centre_cod,
v_ag_centre_cla,
TO_NUMBER(TO_CHAR(SYSDATE,'j')),
1
);
END IF;
END IF;
EXCEPTION
WHEN no_hi_ha_visita THEN NULL;
WHEN no_inserir THEN NULL;
END;
/