Pràctica ASI-C6-ASGBD: Oracle: Pràctica PL/SQL

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

Objectius

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 bloc PL/SQL esà dividit en les següents parts:

  1. Declaració de variables (DECLARE)
  2. Sentències executables (BEGIN)
  3. 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

Desenvolupament

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

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;
/

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.

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;

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).

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;
/

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:

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
/

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:

SQL> execute prog_nomina('març')

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

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

Recursos

Durarda

2 hores


creat per Joan Quintana Compte, novembre 2009

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines