UD1 DAI-C8-EC. Fonaments del llenguatge PL/SQL

Introducció al llenguatge PL/SQL

Introducció al llenguatge PL/SQL
================================

Blocs PL/SQL

[DECLARE
	<declaracions>]
BEGIN
	<ordres>
[EXCEPTION
	<gestió d'excepcions> ]
END;

DECLARE
	v_num_empleats NUMBER(2);
BEGIN
	INSERT INTO depart VALUES (99,'PROVISIONAL',NULL);
	UPDATE emple SET dept_no=99 WHERE dept_no=20;
	v_num_empleats := SQL%ROWCOUNT;
	DELETE FROM depart WHERE dept_no=20;
	DBMS_OUTPUT.PUT_LINE(v_num_empleats || ' empleats ubicats en PROVISIONAL');
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE_APPLICATION_ERROR(-20000, 'Error aplicació');
END;


1. Definició de dades compatibles amb SQL

DECLARE
	import NUMBER(8,2);
	comptador NUMBER(2) DEFAULT 0;
	nom CHAR(20) NOT NULL := 'MIQUEL';
	nou VARCHAR2(15);
BEGIN
...

2. Estructures de control

IF <condició1> THEN
	<instruccions>;
ELSIF <condició2> THEN
	<instruccions>;
...
ELSE
	<instruccions>

END IF;

WHILE <condició> LOOP
	<instruccions>
	...
END LOOP;

FOR<variable> IN <mínim>..<màxim> LOOP
	<instruccions>;
	...
END LOOP;

3.Ús de cursors

DECLARE
	v_cog VARCHAR(10);
	v_ofici VARCHAR(10);
BEGIN
	SELECT cognom,ofici INTO v_cog, v_ofici
	FROM EMPLE WHERE EMP_NO=7900;
	DBMS_OUTPUT.PUT_LINE(v_cog || '*' || v_ofici);
END;

4. Gestió d'excepcions

CREATE TABLE TEMP(
col1 varchar2(50)
);

DECLARE
	v_cog VARCHAR(10);
	v_ofici VARCHAR(10);
BEGIN
	SELECT cognom,ofici INTO v_cog, v_ofici
	FROM EMPLE WHERE EMP_NO=7900;
	DBMS_OUTPUT.PUT_LINE(v_cog || '*' || v_ofici);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	INSERT INTO temp(col1) VALUES('ERROR no hi ha dades');
	WHEN TOO_MANY_ROWS THEN
	INSERT INTO temp(col1) VALUES('ERROR massa dades');
	WHEN OTHERS THEN
	RAISE_APPLICATION_ERROR(-20000, 'Error aplicació');
END;

5. Us de procediments

CREATE OR REPLACE PROCEDURE veure_client(nomcli VARCHAR2)
AS
	nifcli VARCHAR2(10);
	domicli VARCHAR2(15);
BEGIN
	SELECT nif, domicili INTO nifcli, domicli FROM CLIENTS
	WHERE nom=nomcli;
	DBMS_OUTPUT.PUT_LINE('Nom: ' || nomcli || ' Domicili: ' || domicli);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('no sha trobat el client '||nomcli);
END VEURE_CLIENT;
/ 

Ús de dates

• http://www.exes.es/ManSQL/index.asp?Pg=sql_plsql_3.htm

insert into prova values(1,'03/12/1969');
insert into prova values(2,'1969/12/03');
insert into prova values(3,'03-12-1969');
insert into prova values(3,'03-12-1969 12:05:23'); NO
insert into prova values(3,'03-12-1969 12h05m23s'); NO
insert into prova values(1,'03/12/1969 12:05:23'); NO

select * from prova;

select sysdate from dual;

TO_CHAR (número | fecha [,’fmt’]) 


select TO_CHAR(sysdate,'yyyy') from dual;
select TO_CHAR(sysdate,'yyyy/mm/dd') from dual;
select TO_CHAR(sysdate,'hh24 mi ss') from dual;
select TO_CHAR(sysdate,'hh24.mi.ss') from dual;

select TO_CHAR(sysdate,'hh24.mi.ss') from dual;

'avui som 28 de setembre del 2010'
select 'avui som '|| TO_CHAR(sysdate,'dd')||' de ' || TO_CHAR(sysdate,'month') || ' del '|| TO_CHAR(sysdate,'yyyy') from dual;

Fonaments del llenguatge PL/SQL

Fonaments del llenguatge PL/SQL
===============================

1. Tipus de dades b�siques
CHAR(L)
VARCHAR2(L)
LONG(L)
NUMBER(P,E)
BINARY_INTEGER
PLS_INTEGER
BOOLEAN
DATE
RAW(L)
LONG RAW
ROWID

Longituds m�ximes dels tipus:

TIPUS		VARIABLE PL/SQL		CAMP ORACLE8
VARCHAR2	32767 bytes		4000 bytes
CHAR		32767 bytes		2000 bytes
LONG		32767 bytes		2 Gb
RAW		32767 bytes		2000 bytes
LONG RAW	32767 bytes		2 Gb


2. Variables
DECLARE
import NUMBER(8,2);
comptador NUMBER(2,0) := 0;
nom CHAR(20) NOT NULL := 'MIquel';
...

Atributs %TYPE i %ROWTYPE

--Declara la variable total del mateix tipus que la variable import definida pr�viament
total import%TYPE; 
--declara la variable nom_deutor del mateix tipus que el camp nom dela taula clients
nom_deutor CLIENTS.nom%TYPE;
--%ROWTYPE crea una variable de registre els camps del qual es corresponen als camps d'una taula o vista de la base de dades
deutor CLIENTS%ROWTYPE;

3. Operadors
-assignaci�: Edat := 20;
-l�gics: AND, OR, NOT
Les sent�ncies, a part de ser TRUE o FALSE, poden ser NULL (no sabem l'estat)

TRUE AND NULL -> NULL
FALSE AND NULL -> FALSE
NULL AND NULL -> NULL

TRUE OR NULL -> TRUE
FALSE OR NULL -> NULL
NULL OR NULL -> NULL

NOT NULL -> NULL

-Concatenaci�: ||
-Comparaci�: =, !=, <, >, <=, >=, IS NULL, BETWEEN, LIKE IN (igual que en SQL)
-Aritm�tics: +, -, *, /, **

4. Funcions
En PL/SQL es poden utilitzar totes les funcions de SQL: AVG, MIN, MAX, COUNT, SUM, STDDEV

BEGIN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DAY, DD MONTH " a les " YYYY:HH24:MI:SS'));
END;
/

5. Estructures de control

IF <condici�> THEN
	<instruccions>;
END IF;


IF <condici�> THEN
	<instruccions>;
ELSIF <condici�2> THEN
	<instruccions>
ELSE
	<instruccions>
END IF;


LOOP
	<instruccions>;
	IF <condici�> THEN
		EXIT;
	END IF;
	<instruccions>
END LOOP;

WHILE <condici�> LOOP
	<instruccions>;
END LOOP;

FOR <variablecontrol> IN <valorinici>..<valorfinal> LOOP
	<instruccions>;
	...;
END LOOP;

FOR <variablecontrol> IN REVERSE <valorinici>..<valorfinal> LOOP
	<instruccions>;
	...;
END LOOP;

BEGIN
FOR i IN REVERSE 1..3 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/

BEGIN
...
GOTO inserir_fila;
...
<<inserir_fila>> -- �s una etiqueta
INSERT INTO EMPLEAT VALUES...
END;

Escriure la cadena 'HOLA' a l'inrev�s:

DECLARE
r_cadena VARCHAR2(10);
BEGIN
FOR i IN REVERSE 1..LENGTH('HOLA') LOOP
r_cadena := r_cadena || SUBSTR('HOLA',i,1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(r_cadena);
END;
/


Escriure la cadena 'HOLA' a l'inrev�s, d'una altra manera:

DECLARE
r_cadena VARCHAR2(10);
i BINARY_INTEGER;
BEGIN
i := LENGTH('HOLA');
WHILE i>=1 LOOP
r_cadena := r_cadena || SUBSTR('HOLA',i,1);
i := i - 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(r_cadena);
END;
/

6. Subprogrames: procediments i funcions

PROCEDURE <nomprocediment> [(<llista_parametres>)]
IS
	<declaracions>;
BEGIN
	<instruccions>;
EXCEPTION
	<excepcions>;
END [<nomprocediment>];

llista de par�metres:
<nomvariable> [IN | OUT | IN OUT] <tipus_dada> [{ := | DEFAULT} <valor>]

Per a crear el procediment:
CREATE [OR REPLACE] PROCEDURE <nomprocediment>

CREATE OR REPLACE PROCEDURE canviar_ofici(
num_empleat NUMBER, --en els par�metres no s'especifica el tamany
nou_ofici VARCHAR2
AS
anterior_ofici EMPLE.ofici%TYPE;
BEGIN
SELECT ofici INTO anterior_ofici FROM EMPLE WHERE emp_no=num_empleat;
UPDATE EMPLE SET ofici=nou_ofici WHERE emp_no=num_empleat;
DBMS_OUTPUT.PUT_LINE(num_empleat || '*Ofici anterior: ' || anterior_ofici || '*Ofici nou: ' || nou_ofici );
END canviar_ofici;
/

EXECUTE canviar_ofici(7902, 'DIRECTOR');

CREATE OR REPLACE PROCEDURE canv_ofici(v_cognom VARCHAR2,nou_ofici VARCHAR2)
IS
num_empleat EMPLE.emp_no%TYPE;
BEGIN
SELECT emp_no INTO num_empleat FROM EMPLE WHERE cognom=v_cognom;
canviar_ofici(num_empleat, nou_ofici); --cridem al procediment creat anteriorment
END canv_ofici;

EXECUTE canv_ofici('FERN�NDEZ', 'ANALISTA');

7. Subprogrames: funcions

FUNCTION <nomfunci�> ([<llista_parametres>])
RETURN <tipus de valor tornat>
IS
	<declaracions>;
BEGIN
	<instruccions>;
	RETURN <expressi�>;
	...
EXCEPTION
	<excepcions>;
END [<nomfunci�>];

CREATE OR REPLACE FUNCTION trobar_num_empleat(v_cognom VARCHAR2)
RETURN REAL
AS
num_empleat EMPLE.emp_no%TYPE;
BEGIN
SELECT emp_no INTO num_empleat FROM EMPLE WHERE cognom=v_cognom;
RETURN num_empleat;
END trobar_num_empleat;

per cridar a una funci� hem de pensar que el resultat del la funci� s'ha d'assignar a una variable (o b� utilitzar-la en una expressi�):

<variable> := <nomfuncio>(par�metres);

BEGIN DBMS_OUTPUT.PUT_LINE(trobar_num_empleat('MU�OZ')); END;

8. Par�metres
Els subprogrames utilitzen par�metres per passar i rebre informaci�.
Els par�metres poden ser d'entrada (IN), de sortida (OUT), i d'entrada i de sortida (IN OUT).

Tipus IN:
-permet passar valors a un subprograma
-dins del subprograma, el valor actua com una constant, �s a dir, no se li pot assignar cap valor.
-el par�metre actual pot ser una variable, constant, literal o expressi�.

Tipus OUT:
-permet retornar valors al bloc que va cridar al subprograma.
-dins del subprograma, el par�metre actua com una variable no inicialitzada.
-no pot intervenir en cap expressi�, excepte per agafar un valor.
-el par�metre actual ha de ser una variable.

Tipus IN oUT:
-permet passar un valor inicial i retornar un valor actualitzat
-dins del subprograma, el par�metre actua com una variable inicialitzada.
-pot intervenir en altres expressions i pot agafar nous valors.
-el par�metre actual ha de ser una variable.

CREATE OR REPLACE PROCEDURE canviar_divises(
  quant_euros IN NUMBER,
  canvi_actual IN NUMBER,
  quant_comis IN OUT NUMBER,
  quant_divises OUT NUMBER)
AS
  pct_comis NUMBER(3,2) DEFAULT 0.2;
  minim_comis NUMBER(6) DEFAULT 3;
BEGIN
  IF quant_comis IS NULL THEN
     quant_comis := GREATEST(quant_euros / 100 * pct_comis,minim_comis);
  END IF;
  quant_divises := (quant_euros - quant_comis)/canvi_actual;
END;
/

CREATE OR REPLACE PROCEDURE provar_canvi_divises(
  euros NUMBER,
  canvi NUMBER)
AS
  v_comis NUMBER(9);
  v_divises NUMBER(9);
BEGIN
  canviar_divises(euros,canvi,v_comis,v_divises);
  DBMS_OUTPUT.PUT_LINE('Euros: ' || TO_CHAR(euros,'999999.999'));
  DBMS_OUTPUT.PUT_LINE('Preu divisa: ' || TO_CHAR(canvi,'999999.999'));
  DBMS_OUTPUT.PUT_LINE('Euros comissi�: ' || TO_CHAR(v_comis,'999999.999'));
  DBMS_OUTPUT.PUT_LINE('Quantitat divises: ' || TO_CHAR(v_divises,'999999.999'));
END;
/

SQL> EXECUTE provar_canvi_divises(1000,1.24)

---

creat per Joan Quintana Compte, setembre 2009 - setembre 2010