Joan a 16:18, 29 nov 2010

2010-11-29T16:18:08Z

Pàgina nova

==Objectius==
*Unitat Didàctica: UD 1. Introducció a Oracle

El llenguatge PL/SQL és una extensió del SQL, que permet definir variables, crear procediments i funcions, i tots els elements que trobem en un llenguatge de programació: bucles, condicionals,... PostgreSQL i MySQL també implementen avui dia funcionalitats similars.

En aquesta pràctica es crearà un procediment PL/SQL bàsic per tal que els alumnes d'Administració de Sistemes vegin les possibilitats d'extendre el llenguatge SQL.

==Teoria==
===Què és PL/SQL?===
*un llenguatge de programació
*una extensió del llenguatge SQL.
*una nova forma de treball.
*una combinació de:
**poder de Manipulació de Dades mitjançant llenguatge SQL.
**poder de processament dels llenguatges procedimentals.

Un bloc PL/SQL esà dividit en les següents parts:

#Declaració de variables (DECLARE)
#Sentències executables (BEGIN)
#Declaració d’excepcions (EXCEPTION)

Només la part executable (BEGIN) és obligatòria.

El sistema d’execució de PL/SQL és una tecnologia, no un producte independent. Aquesta tecnologia actua com un motor, capaç d’executar blocs i subprogrames PL/SQL i que pot ser instalada en el servidor Oracle o en les eines Oracle.

Un bloc SQL conté parts procedimentals (que són executades pel motor PL/SQL) i parts SQL (que són executades pel motor SQL).

Els blocs PL/SQL poden estar inclosos en programes host (programes escrits en un altre llenguatge, com ara C).

Els procediments i funcions escrits amb PL/SQL es poden compilar i guardar-se a la base de dades, llestos per executar-se quan són cridats per un programa.

===Beneficis del PL/SQL===
*capacitats procedimentals.
*Està estructurat en bloc (múltiples sentències formen un bloc).
*Condicions (IF), iteracions (FOR, WHILE, LOOP), control d’errors (excepcions), varaibles i constants, funcions.
*millora del rendiment. Totes les sentències d’un bloc s’envien directament al nucli ORACLE, produïnt una reducció considerable del número de crides al sistema.
*portabilitat. Qualsevol programa que s’hagi dissenyat en qualsevol de les plataformes on s’executa Oracle, pot ser transportat d’una plataforma a una altra sense cap problema d’execució (portabilitat de software).
*integració amb el RDBMS. PL/SQL incorpora les sentències SQL (SELECT, INSERT, UPDATE, DELETE,…). PL/SQL permet obtenir variables i constants basades en el tipus de dades actual de columnes de la base de dades, o referenciar-se a estructures completes de files d’una taula.

==Desenvolupament==
Per començar, escriu aquestes línies en un fitxer sql, i executa’l des del SQL*Plus (o els Worksheet).

<pre>
rem perque es pugui visualitzar per pantalla he d'executar
rem la sentencia SET SERVEROUTPUT ON;
declare
a date;
begin
a:=sysdate;
dbms_output.put_line(a);
end;
/
</pre>
A la pantalla t’haurà de sortir la data d’avui. Intenta fer algun petit canvi.

===Aplicació de càlcul de nòmines===

Una empresa col.labora amb diferents professionals freelance que treballen per hores. En la següent base de dades es té un recompte dels treballadors i el seu preu-hora en funció de la seva categoria professional. A final de mes s’introdueixen les hores treballades dins la base de dades. Amb el fitxer nòmina.sql, que té un bloc PL/SQL, es calcula el sou mensual i s’introdueix en la taula Nòmina.

Segons la descripció del problema, interpreta i escriu les següents sentències de construcció de les taules i d’inserció de les dades.
<pre>
create table empleat (nom varchar2(10) primary key,preu_hora number(2));

create table hores (nom varchar2(10) references empleat, num_hores number(3), mes varchar2(10), primary key(nom,mes));

create table nomina (nom varchar2(10) primary key references empleat, sou number(7));

insert into empleat values('Pep',15);
insert into empleat values('Anna',30);
insert into empleat values('Marta',25);
insert into empleat values('Núria',35);
insert into empleat values('Miquel',20);
insert into empleat values('Albert',22);
insert into empleat values('Clara',30);
insert into empleat values('Enric',12);
insert into empleat values('Domènech',25);
insert into empleat values('Rosa',20);

insert into hores values('Pep',120,'febrer');
insert into hores values('Anna',130,'febrer');
insert into hores values('Marta',110,'febrer');
insert into hores values('Núria',180,'febrer');
insert into hores values('Miquel,200,'febrer');
insert into hores values('Albert,210,'febrer');
insert into hores values('Clara',126,'febrer');
insert into hores values('Enric',148,'febrer');
insert into hores values('Domènech',183,'febrer');
insert into hores values('Rosa',196,'febrer');
insert into hores values('Pep',235,'març');
insert into hores values('Anna',188,'març');
insert into hores values('Marta',178,'març');
insert into hores values('Núria',220,'març');
insert into hores values('Miquel',210,'març');
insert into hores values('Albert',215,'març');
insert into hores values('Clara',198,'març');
insert into hores values('Enric',86,'març');
insert into hores values('Domènech',134,'març');
insert into hores values('Rosa',126,'març');

COMMIT;
</pre>

'''FITXER NOMINA.SQL'''

Escriu en el fitxer NOMINA.SQL el següent bloc PL/SQL, tot entenent el que fa, i executa’l. (Per identificar els errors podràs escriure SHOW ERRORS).
<pre>
rem perque es pugui visualitzar per pantalla he d'executar
rem la sentencia SET SERVEROUTPUT ON;
declare
nom_empleat varchar2(10);
numero_hores number(3);
euros_hora number(4);
euros_mes number(7);
cursor recompte is
select empleat.nom,preu_hora,num_hores from empleat,hores where mes='març'
and empleat.nom=hores.nom;
begin
open recompte;
fetch recompte into nom_empleat,euros_hora,numero_hores;
while recompte%found loop
euros_mes:=euros_hora*numero_hores;
dbms_output.put_line(nom_empleat);
dbms_output.put_line(euros_hora);
dbms_output.put_line(numero_hores);
dbms_output.put_line(euros_mes);
insert into nomina values(nom_empleat,euros_mes);
fetch recompte into nom_empleat,euros_hora,numero_hores;
end loop;
close recompte;
end;
/
</pre>

'''Exercicis a realitzar:'''

1.Modifica la taula nomina per tal que emmagatzemi, a part del nom i el sou brut, la retenció del 20%, el sou base, el número d’hores, el preu_hora i el mes. Després adapta el codi per tal que s’ompli correctament la taula nomina i per la pantalla surti una informació completa.

2.El codi que has executat es diu un bloc anònim, es carrega en memòria, es compila i s’executa. Tot el codi es pot grabar com a procediment emmagatzemat, que es guarda compilat com a objecte en la base de dades. A més a més, li podem passar com a argument el mes del qual volem calcular la nòmina:

<pre>
CREATE OR REPLACE PROCEDURE prog_nomina (vmes VARCHAR)
AS
<codi>
... from empleat,hores where mes=vmes...
<codi>
insert into ... values( ...,vmes);
<codi>
END prog_nomina
/
</pre>
Nota: ara sobra la instrucció DECLARE després de AS

Si s’ha compilat sense errors, pots comprovar que existeix com a procediment emmagatzemat (PROCEDURE) dins el teu esquema (utilitza el DBA Studio o similar)

Per executar el procediment:
<pre>
SQL> execute prog_nomina('març')
</pre>
3.A partir de la taula nomina, crea un informe pensat per a ser imprès en què es detalli, per a cada empleat, el número d’hores, preu_hora, la retenció i el total a percebre per cada treballador. Per formatar la sortida amb el SQL*Plus pots trobar informació a http://www.infor.uva.es/~jvegas/cursos/bd/sqlplus/sqlplus.html (apartat 11.2, Generació d’informes)

==Entrega==
Entregaràs el script resultant al Moodle (amb el seu log). Entrega també el fitxer de l'informe a imprimir, que aconseguiràs fent un segon spool.

script NA5_XX.sql
<pre>
SPOOL NA5_XX.log

DROP TABLE...
...
CREATE TABLE ...
INSERT INTO ...

CREATE OR REPLACE PROCEDURE...
...
SPOOL OFF

SPOOL NA5_NOMINA_XX.log
PAGESIZE ...
...
SELECT ... FROM NOMINA ...;
SPOOL OFF
</pre>

==Recursos==
*http://www.infor.uva.es/~jvegas/cursos/bd/sqlplus/sqlplus.html#informes

==Durarda==
2 hores

{{Autor}}, novembre 2009
[[Categoria: IES Jaume Balmes]]
[[Categoria: Pràctiques ASI-C6-ASGBD]]

Pràctica ASI-C6-ASGBD: Oracle: Pràctica PL/SQL - Historial de revisió

Joan a 16:18, 29 nov 2010