Joan: /* Introducció al llenguatge PL/SQL */

2011-09-28T14:49:22Z

Introducció al llenguatge PL/SQL

Pàgina nova

=Introducció al llenguatge PL/SQL=
<pre>
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;
/
</pre>
==Ús de dates==
*http://www.exes.es/ManSQL/index.asp?Pg=sql_plsql_3.htm
<pre>
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;
</pre>

=Fonaments del llenguatge PL/SQL=
<pre>
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)
</pre>
{{Autor}}, setembre 2009 - setembre 2010

UD1 DAI-C8-EC. Fonaments del llenguatge PL/SQL - Historial de revisió

Joan: /* Introducció al llenguatge PL/SQL */