Docsity
Docsity
Prepara tus exámenes
Prepara tus exámenes

Prepara tus exámenes y mejora tus resultados gracias a la gran cantidad de recursos disponibles en Docsity

Consigue puntos base para descargar
Consigue puntos base para descargar

Gana puntos ayudando a otros estudiantes o consíguelos activando un Plan Premium

Orientación Universidad
Orientación Universidad
Vende en Docsity
Vende en Docsity
Docsity AI
Docsity AI
Inicia sesiónRegístrate

Prepara tus exámenes

Prepara tus exámenes y mejora tus resultados gracias a la gran cantidad de recursos disponibles en Docsity

Busca documentos

Prepara tus exámenes con los documentos que comparten otros estudiantes como tú en Docsity

Busca tu universidad

Encuentra los documentos específicos para los exámenes de tu universidad

Video Cursos

Estudia con lecciones y exámenes resueltos basados en los programas académicos de las mejores universidades

Quiz

Responde a preguntas de exámenes reales y pon a prueba tu preparación

Docsity AINEW

Resume tus documentos, hazles preguntas, conviértelos en quiz y mapas conceptuales

Ver preguntas

Despeja tus dudas leyendo las respuestas a las preguntas que realizaron otros estudiantes como tú

Consigue puntos base para descargar

Gana puntos ayudando a otros estudiantes o consíguelos activando un Plan Premium

Compartir documentos
download point icon20 Puntos

Por cada documento subido

Responde a las preguntas
download point icon5 Puntos

por cada respuesta dada (máx. 1 al día)

Todos los modos para conseguir puntos gratis
Consigue puntos de inmediato

Elige un plan Premium con todos los puntos que necesitas.

Oportunidades de estudio

Elige tu próximo programa de estudio

Ponte en contacto inmediatamente con las mejores universidades del mundo. Busca entre miles de universidades en todo el mundo. Busca entre miles de universidades partner oficiales

Comunidad

Pregúntale a la comunidad

Pide ayuda a la comunidad y resuelve tus dudas de estudio

Ebooks gratuitos

¡Nuestros e-books salva-estudiantes!

Descarga nuestras guías gratuitas sobre técnicas de estudio, métodos para controlar la ansiedad y consejos para la tesis preparadas por los tutores de Docsity

Procedimientos almacenados en PL/pgSQL para bases de datos, Apuntes de Programación de Bases de Datos

Universitat Politècnica de Catalunya. BarcelonaTech (UPC)Programación de Bases de Datos

Este documento ofrece información detallada sobre procedimientos almacenados en PL/pgSQL para bases de datos PostgreSQL. Aprende cómo crear, utilizar y gestionar procedimientos almacenados, incluyendo sentencias condicionales, bucles, variables y tipos de retorno. Además, se incluyen ejemplos prácticos para clarificar conceptos.

Tipo: Apuntes

2020/2021

Subido el 11/05/2021

luz_pinto90
luz_pinto90 🇪🇸

4.3

(111)

65 documentos

1 / 32

Toggle sidebar

Esta página no es visible en la vista previa

¡No te pierdas las partes importantes!

bg1
FIB: Bases de Dades (BD)
4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL 180
4.1 Procediments emmagatzemats
en PL/pgSQL
• PL/pgSQL
• Procediments emmagatzemats en PL/pgSQL
• Paràmetres
• Variables
• Sentències condicionals
• Sentències iteratives
• Cursors Explícits
• Gestió d’Errors
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20

Vista previa parcial del texto

¡Descarga Procedimientos almacenados en PL/pgSQL para bases de datos y más Apuntes en PDF de Programación de Bases de Datos solo en Docsity!

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

4.1 Procediments emmagatzemats

en PL/pgSQL

• PL/pgSQL• Procediments emmagatzemats en PL/pgSQL• Paràmetres• Variables• Sentències condicionals• Sentències iteratives• Cursors Explícits• Gestió d’Errors

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

PL/pgSQL

PL/pgSQL és un dels diferents llenguatges que ofereixPostgreSQL per implementar procediments emmagatzemats.El PL/pgSQL que s’explica en aquestes transparències és unsubconjunt del PL/pgSQL corresponent a la versió dePostgreSQL instal.lada al laboratori de la FIB.

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Procediments emmagatzemats en PL/pgSQL

Les sentències de PL/pgSQL les utilitzarem dins del cos d’unprocediment, és a dir, entre les sentències CREATE FUNCTIONi END de la funció. 

Bàsicament, PL/pgSQL proporciona dos tipus de sentències:

Sentències per definir (DECLARE) i assignar valors a variables

Sentències per controlar el flux d’execució d’un procediment:

-^

Sentències condicionals:

  • Sentència IF -^

Sentències iteratives:

  • Sentències LOOP, FOR i WHILE -^

Sentències per fer la gestió d’errors:

  • Sentències EXCEPTION i RAISE EXCEPTION

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Paràmetres: Retorn d’una única tupla

Paràmetre d’entrada:nom i tipus

CREATE FUNCTION nom_proc (param_entrada tipus) RETURNS tipus_retorn AS $$

RETURN

variable_retorn;

END;$$LANGUAGE plpgsql;

Tipus de retorn de lafuncióEn cas de no retornarres es posa “void”

Nom de la variable que té el valor queretorna la funció

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Paràmetres: Retorn d’un conjunt de tuples CREATE FUNCTION nom_proc(param_entrada tipus) RETURNS

SETOF tipus AS $$

................RETURN NEXT

ciutat_client;

END;$$LANGUAGE

plpgsql;

  • Per retornar un conjunt detuples cal utilitzar SETOFquan especifiquem el tipusque retorna la funció.
  • Cal usar la clàusulaRETURN NEXT. Aquestaclàusula no acaba elprocediment, sinó que varetornant a cada execucióels valors de la variable.El procediment acabaquan s’executa unRETURN sense NEXT, oquan s’arriba al final.

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Paràmetres: Retorn d’un conjunt de tuples - Exemple^ Aquest procediment retorna una tupla per cada enter que hi ha entre 0 i elvalor del paràmetre d’entrada MAX.

CREATE FUNCTION exemple_retorn_n_tuples(max integer)RETURNS SETOF integer

AS $$
DECLARE

i integer := 0;

BEGIN
LOOP

i:=i+1;RETURN NEXT i;EXIT WHEN i = max;

END LOOP;RETURN; END;$$LANGUAGE

plpgsql;

SETOF del tipusque tindrà lavariable de retornRETURN NEXTque s’invocatantes vegadescom tuples esvol retornar

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Variables: Utilització

^

Bàsicament, és possible utilitzar variables dins d’un procediment emmagatzemat en lessituacions següents:

–^

En sentències SQL

-^

En sentències de PL/PGSQL per

•^

Assignar-hi valors

-^

Calcular valors

-^

CREATE FUNCTION....DECLARE Controlar el flux d’execució d’un procediment

dni_client clients.dni%TYPE;ciutat_client varchar(15);

BEGIN

....SELECT ciutat INTO ciutat_clientFROM clientsWHERE dni=dni_client;...

END;

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Variables: Creació de nous tipus

CREATE TYPE tipusAdressa AS (

carrer varchar(20),num_carrer varchar(4),ciutat varchar (15));

CREATE FUNCTION exNousTipus()RETURNS tipusAdressa AS $$DECLARE

adressa tipusAdressa;carrer varchar(20);

BEGIN

....adressa.ciutat:='Badalona';....carrer := adressa.carrer;....

RETURN adressa;END;$$ LANGUAGE plpgsql;

En alguns casos, com per exemple quan un procediment ha deretornar tuples amb un conjunt d’atributs ens cal definir un nou tipus.

Creació prèviaal procediment Declaració d’unavariable del tipus

Utilització i accésdels diferents valorsde la variable

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL Només té sentit per sentències o procediments que només retornen una fila:

Sentència d’assignació de PL/PGSQL

numComclient

(SELECT

num_com

FROM

clients

WHERE

dni=dni_client);

Sentència SELECT ... INTO de l’SQL

SELECT

ciutat

INTO

ciutat_client

FROM

clients

WHERE

dni=dni_client;

Assignar a una variable el que retorna un procediment:

imp_comada

import_una_com(numero_com);

o bé select

from

import_una_com(numero_com)

into

imp_comanda;

Variables: Assignacions de valors

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Sentències condicionals

La sentència IF serveix per a establir condicions en el fluxd’execució d’un procediment:

IF

condició

THEN

bloc de sentències

ELSE

bloc de sentències

END IF

Podem establir diferents nivells d’aniuament mitjançant la clàusula

IF ... THEN ... ELSEIF ... THEN ... ELSE...END IF;

Condicions:

Per especificar les condicions podem utilitzar:

•^

Operadors lògics: AND, OR, NOT

-^

Operadors de comparació: =, <,<=, >, >=, etc

-^

Predicats propis d’SQL: BETWEEN, IN, IS NULL, EXISTS

-^

Variable PL/pgSQL: FOUND

-^

Consultes SQL

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Sentències condicionals – Variable FOUND

La variable FOUND és de tipus booleà.FOUND té en principi el valor False.El seu valor pot canviar quan s’executen les sentències següents:• Una sentència SELECT ... INTO posa FOUND a True si el select obté unafila, i a False si no s’obté cap fila.• Una sentència UPDATE, INSERT o DELETE posa FOUND a True si com amínim una fila es veu afectada per la sentència, i a False si no quedaafectada cap fila.• Una sentència FOR. Dintre de cada iteració del FOR, el valor de la variablepot canviar segons les sentències que s’hi executen. Però en sortir del FORés posa FOUND a True si s’ha iterat una o més vegades, sinó es posa aFalse.FOUND és una variable local en un procediment. Qualsevol canvi enaquesta variable afecta només al procediment on aquest canvi es produeix.

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Sentències condicionals – Variable FOUND - Exemple^ Obté el descompte del client amb el DNI que es passa per paràmetre.Aquest descompte depèn del nombre de comandes del client.

CREATE FUNCTION calcul_desc_client(dni_client clients.dni%type)RETURNS integer AS $$DECLARE

descompte INTEGER;qttComClient INTEGER;

BEGIN

SELECT qtt_com into qttComclient FROM clients WHERE

dni=dni_client;

IF FOUND THEN

IF (qttComclient=0)

THEN descompte:=0;

ELSIF (qttComClient<5)

THEN descompte:=1;

ELSIF (qttComClient<10) THEN descompte:=3;ELSIF (qttComClient<15) THEN descompte:=5;ELSE

descompte:=10;

END IF;

END IF;RETURN descompte; END;$$LANGUAGE

plpgsql;

Execució de la funció:

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Sentències iteratives: Exemple utlització FOR (1)^ CREATE TYPE Tdades_client_tip AS (

dni_client VARCHAR(9),nom_client VARCHAR(15),cognom1 VARCHAR(15),pref CHAR(1));

CREATE FUNCTION clients_ciutat_tip(ciutat_client clients.ciutat%type)RETURNS SETOF Tdades_client_tip AS $$DECLARE

dades_clients

Tdades_client_tip;

BEGINFOR

dades_clients

IN SELECT dni,nom,cognom

FROM clientsWHERE ciutat=ciutat_client LOOP

dades_clients.pref := es_preferent(dades_clients.dni_client);return next dades_clients; END

LOOP;
RETURN;END;$$LANGUAGE

plpgsql;

Obté les dades de tots els clients d’una determinada ciutat. Indica amb una “P”els clients que són preferents (import total de comandes superior a 60000).

Procediment que comprovasi un client és preferent

4.1 Laboratori - Procediments emmagatzemats en PL/PGSQL

Sentències iteratives: Exemple utlització FOR (2)

CREATE FUNCTION import_totes_com(dni_client

clients.dni%type)

RETURNS void AS $$DECLARE

num_comanda comandes.num_com%type;import comandes.import_total%type; BEGINFOR

num_comanda IN SELECT num_com FROM comandes

WHERE dni=dni_client

LOOPSELECT SUM(ic.quantitat*i.preu_unitat) INTO import

FROM items_comanda ic, items iWHERE i.num_item=ic.num_item

AND ic.num_com=num_comanda;

UPDATE comandes

SET import_total=importWHERE num_com=num_comanda;

END
LOOP;
END;$$LANGUAGE

plpgsql;

Modifica l’import de la comanda a la taula comandes per cadascuna deles comandes d’un determinat client.