guía laboratorio

GUÍA LABORATORIO BASE DE DATOS IIISIS 306

ING. ENRIQUE YAÑEZ VENEGAS

SUCRE – 2010

ÍNDICE

SISTEMA DE GESTIÓN DE DATOS POSTGRESQL

Características Principales

INTRODUCCIÓN A POSTRGRESQL

Ingresando a PostgreSQL, Manejo de Base de Datos, Esquemas, Tablas, DDL, DML

PRÁCTICA Nº 1: SISTEMA DE GESTIÓN DE DATOS POSTGRESQL

Atributos y restricciones, Dominios, Secuencias.

PRÁCTICA Nº 2: PROCEDIMIENTOS ALMACENADOS EN POSTGRESQL

P1: Lenguaje PL/PgSQL,

SISTEMA DE GESTIÓN DE DATOS POSTGRESQL

BASE DE DATOS III GUÍA DE LABORATORIO

POSTGRESQL

Desarrollador

PostgreSQL Global Development Group

www.postgresql.org

Información general

Última versión estable 9.0 (20 de septiembre de 2010)

Género RDBMS

Sistema operativo Multiplataforma

Licencia BSD

En español

PostgreSQL es un sistema de gestión de base de datos relacional orientada a objetos y libre, publicado bajo la licencia BSD.

Como muchos otros proyectos de código abierto, el desarrollo de PostgreSQL no es manejado por una sola empresa sino que es dirigido por una comunidad de desarrolladores y organizaciones comerciales las cuales trabajan en su desarrollo. Dicha comunidad es denominada el PGDG (PostgreSQL Global Development Group).

http://es.wikipedia.org/wiki/Idioma_espa%C3%B1ol

http://es.wikipedia.org/wiki/Licencia_BSD

http://es.wikipedia.org/wiki/Licencia_de_software

http://es.wikipedia.org/wiki/Multiplataforma

http://es.wikipedia.org/wiki/Sistema_operativo

http://es.wikipedia.org/wiki/Sistema_administrador_de_bases_de_datos_relacionales

http://es.wikipedia.org/wiki/Anexo:G%C3%A9neros_de_software

http://es.wikipedia.org/wiki/Fases_del_desarrollo_de_software

http://www.postgresql.org/

http://es.wikipedia.org/w/index.php?title=PGDG&action=edit&redlink=1

http://es.wikipedia.org/wiki/Archivo:Pg_logo.png

http://es.wikipedia.org/wiki/Archivo:Yes_check.svg


CARACTERÍSTICASAlgunas de sus principales características son, entre otras:

ALTA CONCURRENCIA

Mediante un sistema denominado MVCC (Acceso concurrente multiversión, por sus siglas en inglés) PostgreSQL permite que mientras un proceso escribe en una tabla, otros accedan a la misma tabla sin necesidad de bloqueos. Cada usuario obtiene una visión consistente de lo último a lo que se le hizo commit. Esta estrategia es superior al uso de bloqueos por tabla o por filas común en otras bases, eliminando la necesidad del uso de bloqueos explícitos.

AMPLIA VARIEDAD DE TIPOS NATIVOS

PostgreSQL provee nativamente soporte para:

Números de precisión arbitraria. Texto de largo ilimitado. Figuras geométricas (con una variedad de funciones asociadas) Direcciones IP (IPv4 e IPv6). Bloques de direcciones estilo CIDR. Direcciones MAC. Arrays.

Adicionalmente los usuarios pueden crear sus propios tipos de datos, los que pueden ser por completo indexables gracias a la infraestructura GiST de PostgreSQL. Algunos ejemplos son los tipos de datos GIS creados por el proyecto PostGIS.

OTRAS CARACTERÍSTICAS

Claves ajenas también denominadas Llaves ajenas o Claves Foráneas (foreign keys).

Disparadores (triggers): Un disparador o trigger se define en una acción especifica basada en algo ocurrente dentro de la base de datos. En PostgreSQL esto significa la ejecución de un procedimiento almacenado basado en una determinada acción sobre una tabla específica.

Entonces combinando estas seis características, PostgreSQL le permitirá crear una amplia funcionalidad a través de su sistema de activación de disparadores (triggers).

Vistas . Integridad transaccional.

http://es.wikipedia.org/wiki/Vista_(base_de_datos)

http://es.wikipedia.org/wiki/Disparador_(base_de_datos)

http://es.wikipedia.org/wiki/Clave_ajena


Herencia de tablas. Tipos de datos y operaciones geométricas. Soporte para transacciones distribuidas. Permite a PostgreSQL integrase en un

sistema distribuido formado por varios recursos (p.ej, una base de datos PostgreSQL, otra Oracle, una cola de mensajes IBM MQ JMS y un ERP SAP) .

FUNCIONES

Bloques de código que se ejecutan en el servidor. Pueden ser escritos en varios lenguajes, con la potencia que cada uno de ellos da, desde las operaciones básicas de programación, tales como bifurcaciones y bucles, hasta las complejidades de la programación orientada a objetos o la programación funcional.

Los disparadores (triggers en inglés) son funciones enlazadas a operaciones sobre los datos.

Algunos de los lenguajes que se pueden usar son los siguientes:

Un lenguaje propio llamado PL/PgSQL (similar al PL/SQL de oracle). C . C++ . Java PL/Java web. PL/Perl . plPHP . PL/Python . PL/Ruby . PL/sh . PL/Tcl . PL/Scheme . Lenguaje para aplicaciones estadísticas R por medio de PL/R.

PostgreSQL soporta funciones que retornan "filas", donde la salida puede tratarse como un conjunto de valores que pueden ser tratados igual a una fila retornada por una consulta (query en inglés).

Las funciones pueden ser definidas para ejecutarse con los derechos del usuario ejecutor o con los derechos de un usuario previamente definido. El concepto de funciones, en otros DBMS, son muchas veces referidas como "procedimientos almacenados" (stored procedures en inglés).1

1 http://es.wikipedia.org/wiki/PostgreSQL

http://es.wikipedia.org/wiki/PostgreSQL

http://www.joeconway.com/plr/

http://es.wikipedia.org/wiki/Lenguaje_R

http://plscheme.projects.postgresql.org/

http://www.postgresql.org/docs/current/interactive/pltcl.html

http://plsh.projects.postgresql.org/

http://raa.ruby-lang.org/project/pl-ruby

http://www.postgresql.org/docs/current/interactive/plpython.html

http://plphp.commandprompt.com/

http://es.wikipedia.org/wiki/Perl

http://gborg.postgresql.org/project/pljava/projdisplay.php

http://es.wikipedia.org/wiki/Lenguaje_de_programaci%C3%B3n_Java

http://es.wikipedia.org/wiki/C%2B%2B

http://es.wikipedia.org/wiki/Lenguaje_de_programaci%C3%B3n_C

http://es.wikipedia.org/wiki/PL/SQL

http://www.postgresql.org/docs/8.4/interactive/plpgsql.html


INTRODUCCIÓN A POSTGRESQL

INGRESANDO A POSTGRESQL, MANEJO DE BASE DE DATOS, ESQUEMAS, TABLAS, DDL, DML


1.- INGRESANDO A POSTGRESQL

1. Iniciar el servicio2. Ingresar a pgAdmin III

Una vez abierta la pantalla hacer doble click en PostgreSQL : Database Server … etc.:

Se habilitan las opciones siguientes:

ING. ENRIQUE YAÑEZ VENEGAS Página 9


BARA DE HERRAMIENTAS

EXPLORADOR DE OJBJETOS Y VENTANA DE PROPIEDADES


Actualizar el objeto seleccionado

Añadir Conexión a

un

Crear un objeto igual al objeto seleccionado

Eliminar el objeto seleccionado

Visualizar o editar las

propiedades del objeto

seleccionado

Opciones de mantenimiento de la

base de datos

Abrir la consola de consultas

Visualizar la ayuda

Permite explorar las diferentes bases de datos, tablas, esquemas triggers y objetos que creemos dentro de postgres.

Permite explorar las propiedades del objeto seleccionado; como podemos ver en la pantalla tenemos seleccionada la base de datos test y podemos observar su nombre, propietario y codificado por ejemplo.


PANEL SQL


Permite observar el código en SQL del objeto seleccionado, como en el caso anterior observamos la base de datos test, donde podemos observar los comandos de su creación. Este código lo genera automáticamente postgres por lo que no es posible editarlo. Para eso debemos utilizar la consola de Consultas.


CONSOLA DE CONSULTAS

Se abre al hacer click en el botón de la barra de herramientas. NOTA: Este botón solo se habilitara si tenemos seleccionado un objeto por ejemplo una base de datos.

BARRA DE HERRAMIENTAS DE LA CONSOLA DE CONSULTAS

Tiene las funciones comunes abrir guardar cortar pegar etc.

Nos muestra la base de datos a la que estamos conectados; ej. Test.


En esta sección podemos escribir las consultas, en lenguaje SQL con algunas pequeñas diferencias que iremos estudiando con el avance de los capítulos

En esta sección observamos los resultado de ejecutar las consultas, en el ejemplo en pantalla añadir una tabla a la base de datos Test

Botón para ejecutar las consultas


2.- MANEJO DE BASES DE DATOS

Para el manejo de bases de datos en PostgreSQL se tienen dos opciones: utilizar la interfaz grafica de pgAdmin III, o utilizar la línea de comandos.

Dentro de la interfaz gráfica utilizaremos el panel de consultas SQL para desarrollar los diferentes comandos de manipulación y creación de las bases de datos.

CREAR UNA BASE DE DATOS.- PostgreSQL permite la creación de cualquier número de base de datos en un servidor, siendo el usuario que la crea automáticamente el administrador de la base de datos. NOTA.- El nombre de la BD debe comenzar siempre con una letra y estar limitada a 32 caracteres.

Para crear la base de datos se utiliza la sentencia:

CREATE DATABASE [nombre_de_la_bd];

Ejemplos:

CREATE DATABASE Nueva; CREATE DATABASE Empresa1;CREATE DATABASE BANCO;

Otros comandos para el manejo de las bases de datos:

DROP DATABASE Nueva;

NOTA: El comando CREATE DATABASE nos permite el uso de más opciones de configuración como ser:

CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ]



3.- ESQUEMAS

Los esquemas contienen una colección de tablas, vistas, funciones y otros tipos de objetos, al interior de una base de datos. Nos permiten tener un mejor control al momento de establecer que contenidos son privados y cuales públicos.

Para crear un esquema se utiliza la sentencia:

CREATE SCHEMA [nombre_del_esquema];

Ejemplos:

CREATE SCHEMA Privado;CREATE SCHEMA Restringido;

También se pueden crear a través de la interfaz gráfica. Para adicionar una tabla a un esquema se utiliza:

SET SEARCH_PATH TO ’[nombre_esquema]’, ’ $usuario’ ;

Ejemplo:

SET SEARCH_PATH TO privado, $user ;

Podemos ver el esquema en el que estamos trabajando ejecutando el comando:

SHOW SEARCH_PATH;

NOTA: Por defecto el esquema en todas las bases de datos es public.

Cuando trabajamos con muchos esquemas se puede acceder de forma rápida a ellos utilizando la notación: nombre_esquema.nombre_tabla ej:

SELECT * FROM privado.nueva;



4.- TABLAS

Para crear la tabla se utiliza la sentencia:

CREATE TABLE [nombre_tabla] (atributo1 tipo, atributo2 tipo,…, atributoN tipo);

Ejemplos:

CREATE TABLE cliente(id_cliente INT PRIMARY KEY, nombre CHAR(10), ap_pat CHAR(10), ap_mat CHAR(10));CREATE TABLE cuenta (id_cuenta INT, PRIMARY KEY, id_cliente INT, saldo FLOAT, fecha DATE, FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente) );

Otros comandos: ALTER, DROP, TEMPORARY. Ejemplos:

ALTER TABLE cliente ADD COLUMN direccion CHAR(20);DROP TABLE cuenta;

TABLA TEMPORAL

Permite crear una tabla temporal dentro la base de datos ej:

CREATE TEMPORARY TABLE cli_temp AS SELECT nombre, ap_pat FROM cliente;

NOTA: En la interfaz gráfica podemos ver el contenido de una tabla con click derecho seleccionando la opción Ver Todas las Filas como se muestra en la figura.



5.-DDL (DATA DEFINITION LANGUAGE) Podemos distinguir los siguientes tipos de datos en Postgres:

Tabla 4.1: Tipos de datos del estándar SQL3 en PostgreSQLTipos de datos del estándar SQL3 en PostgreSQL

Tipo en Postgres

Correspondiente en SQL3 Descripción

bool Boolean valor lógico o booleano (true/false)char(n) character(n) cadena de caracteres de tamaño fijodate Date fecha (sin hora)float4/8 float(86#86) número de punto flotante con precisión 86#86float8 real, double precision número de punto flotante de doble precisiónint2 Smallint entero de dos bytes con signoint4 int, integer entero de cuatro bytes con signoint4 decimal(87#87) número exacto con 88#88int4 numeric(87#87) número exacto con 89#89money decimal(9,2) cantidad monetariatime Time hora en horas, minutos, segundos y centésimastimespan Interval intervalo de tiempotimestamp timestamp with time zone fecha y hora con zonificaciónvarchar(n) character varying(n) cadena de caracteres de tamaño variable

Fuente: Tipos de datos relevantes en PostgreSQL

Tabla 4.2: Tipos de datos extendidos por PostgreSQLTipos de datos extendidos en PostgreSQL

Tipo Descripciónbox caja rectangular en el planocidr dirección de red o de host en IP versión 4circle círculo en el planoinet dirección de red o de host en IP versión 4int8 entero de ocho bytes con signoline línea infinita en el planolseg segmento de línea en el planopath trayectoria geométrica, abierta o cerrada, en el planopoint punto geométrico en el planopolygon trayectoria geométrica cerrada en el planoserial identificador numérico único

Fuente: Tipos de datos relevantes en PostgreSQL 2

2 El listado completo se encuentra en el Anexo Tipos de Datos en Postgres o en la dirección indicada abajo. http://www.ibiblio.org/pub/linux/docs/LuCaS/Tutoriales/NOTAS-CURSO-BBDD/notas-curso-BD/node134.htm


http://www.ibiblio.org/pub/linux/docs/LuCaS/Tutoriales/NOTAS-CURSO-BBDD/notas-curso-BD/node134.htm

http://www.ibiblio.org/pub/linux/docs/LuCaS/Tutoriales/NOTAS-CURSO-BBDD/notas-curso-BD/node134.htm


6.- DML (DATA MANIPULATION LANGUAGE)

Podemos realizar las operaciones que conocemos:

INSERT

La sentencia INSERT permite agregar datos a una tabla. La forma de INSERT es:

INSERT INTO [nombre_tabla] VALUES (valor1, valor2,…, valorN);

Ejemplos:

INSERT INTO cliente VALUES (0001,’Juan’, ‘Perez’, ’Pinto’, ’Loa 100’);INSERT INTO cuenta VALUES (0001,0010,4589.12);

NOTA: Para ingresar valores reales se utiliza el punto ( . ) no así la coma ( , ).

SELECT

La sentencia SELECT permite obtener filas desde una tabla. La forma más sencilla es:

SELECT atributo1, atributo2,…, atributoN

FROM [nombre_tabla]

WHERE atributo=valor;

Ejemplos:

SELECT nombre,ap_pat,ap_mat FROM cliente WHERE nombre=’Juan’;SELECT COUNT(*) FROM cliente;

UPDATE

UPDATE es la cláusula que permite hacer modificaciones a registros ya existentes en la base de datos. Su forma más sencilla es

UPDATE [nombre_tabla]

SET atributo1 = valor1, atributo2 = valor2,…, atributoN = valorN

WHERE atributo=valor;

Ejemplo:

UPDATE cliente SET ap_pat=’Pinto’, ap_mat=’Perez’ WHERE id_cliente=0001; UPDATE cuenta SET saldo=0 WHERE id_cliente=0001;



DELETE

DELETE es la cláusula que permite eliminar registros de una tabla. Su uso es muy sencillo:

DELETE FROM [nombre_tabla]

WHERE atributo=valor {CASCADE | RESTRICT};

Ejemplos:

DELETE FROM cliente WHERE id_cliente=0001 CASCADE;DELETE FROM cuenta WHERE id_cuenta=0010;

NOTA: CASCADE permite eliminar registros aún cuando estos están relacionados con otras tablas.



ADMINISTRACIÓN DE POSTGRESQL (2ª PARTE)


PRA

CTIC

A I


7.- ATRIBUTOS Y RESTRICCIONES

Postgres nos permite utilizar ciertos comandos para un mejor manejo de los datos. Entre ellos tenemos:

DEFAULT

DEFAULT Asigna un valor por defecto a un campo, por ejemplo:

CREATE TABLE cliente (id_cliente INT, nit CHAR(11),nombre VARCHAR(40), telf INT DEFAULT 0 );

CREATE TABLE estudiante(id_estudiante INT,nombre CHAR(15),ap_pat CHAR(15), ap_mat CHAR(15),fecha_ins date DEFAULT CURRENT_DATE); 3

CONSTRAINT

CONSTRAINT Es un tipo de restricción que permite definir un conjunto de valores validos sobre una tabla, existen dos tipos:

NOT NULL Obliga a que un campo contenga únicamente valores no nulos. Ejemplo:

CREATE TABLE cliente (id_cliente INT , nit INT, nombre VARCHAR(40) CONSTRAINT no_nulo NOT NULL, telf INT);

3 El manejo de los datos de tiempo esta incluido en el Anexo Manejo de Tiempo



CREATE TABLE cuenta (id_cuenta INT, id_cliente INT,saldo FLOAT,fecha_apertura date CONSTRAINT no_vacio NOT NULL);

UNIQUE Obliga a un grupo de uno o más campos de una tabla a contener valores únicos. Ejemplo:

CREATE TABLE cliente (id_cliente INT, documento_identidad CHAR(11) CONSTRAINT unico UNIQUE, nombre VARCHAR(40), telf INT);

CREATE TABLE producto(id_producto INT, nombre CHAR(11) CONSTRAINT unico UNIQUE, descripcion VARCHAR(40), precio FLOAT);

NOTA: No es necesario declarar CONSTRAINT para utilizar NOT NULL y UNIQUE por ejemplo se podría declarar:CREATE TABLE product (id_producto INT, nombre CHAR(11) UNIQUE, descripcion VARCHAR(40), precio FLOAT);Y tendría el mismo efecto que el caso anterior.



CHECK

CHECK Es una restricción sobre los valores permitidos en un campo: Ejemplo:

CREATE TABLE libros (ISBN CHAR(11), titulo VARCHAR(40), autor VARCHAR(40), stock INT CHECK(stock>0) );

CREATE TABLE distribuidores ( did decimal(3), nonmbre vaCHAR(40) CONSTRAINT con1 CHECK (did > 100 AND nombre<> ''));

PRIMARY KEY Y FOREIGN KEY

PRIMARY KEY Obliga a que un campo contenga únicamente valores únicos y no nulos. Solo debe existir una clave primaria por tabla. Ejemplo:

CREATE TABLE cliente (id_cliente INT PRIMARY KEY, documento_identidad CHAR(11) ,nombre VARCHAR(40), telf INT);

NOTA: PRIMARY KEY contiene las restricciones UNIQUE y NOT NULL por lo tanto no es necesario agregarlas a las llaves primarias.

FOREIGN KEY Clave ajena que proviene de otra tabla. Ejemplo:

CREATE TABLE cuenta (id_cuenta INT PRIMARY KEY, id_cliente INT REFERENCES cliente(id_cliente) ,saldo VARCHAR(40), fecha_apertura DATE );



También se puede utilizar de esta forma:

CREATE TABLE cuenta (id_cuenta INT PRIMARY KEY, id_cliente INT ,saldo VARCHAR(40), fecha_apertura DATE,FOREIGN KEY id_cliente REFERENCES cliente(id_cliente));



8.- DOMINIOS

Un Dominio puede ser considerado como un tipo de dato y un Constraint, requiere de dos datos: el tipo base que utilizará el dominio, y la restricción de limitación para aceptar valores.

Crear dominios Se utiliza el comando CREATE DOMAIN, y comprende también una serie de otras instrucciones como ser: CHECK, NOT NULL, DEFAULT, etc.

CREATE DOMAIN [nombre] AS [tipo] (CONSTRAINT [nombre]) CHECK [condicion]

Ejemplo:

CREATE DOMAIN edad AS INT CONSTRAINT “validar_edad”CHECK((VALUE >0) AND (VALUE<99));

Eliminar un dominio Se utiliza la sentencia DROP DOMAIN.

DROP DOMAIN edad CASCADE;



9.- SECUENCIAS

Es un objeto de base de datos creado con el propósito de asignar números únicos de entrada a una tabla, generalmente utilizados para generar claves primarias.

Crear una secuencia Ejemplo:

CREATE SEQUENCE correlativo INCREMENT 1 START 1000;

Funciones de secuencia

Nextval [nombre_seq] Para obtener el siguiente valor de la secuencia. Currval [nombre_seq] Determina el ultimo valor devuelto por la secuencia. Setval [nombre_seq, nuevo_val] Cambia el valor actual de la secuencia indicada.

Alterar una secuencia Ejemplo:

ALTER SEQUENCE correlativo SET START 2000;

Eliminar una secuencia Ejemplo:

DROP SEQUENCE correlativo;

Ejemplo de uso de secuencia:

CREATE SEQUENCE cod_cuenta INCREMENT 1 START 2000;

CREATE TABLE cuenta (id_cuenta INT PRIMARY KEY DEFAULT netval(‘cod_cuenta,), id_cliente INT REFERENCES cliente(id_cliente) ,saldo VARCHAR(40), fecha_apertura DATE );

NOTA: Cada vez que se utiliza Nextval la secuencia sube el incremento incluso cuando se ejecuta SELECT nextval(‘correlativo’) por lo tanto se debe reasignar el valor correcto con setval.



EJERCICIOS1.- Crear el esquema privado donde debe estar incluida la tabla cliente y gerente2.- Crear el dominio telefono que restrinja los datos entre 6400000 y 64999993.- Crear las siguientes tablas según las especificaciones indicadas.

GERENTEATRIBUTO TIPO CONDICION VALOR POR DEFECTO DESCRIPCION

id_gerente int PRIMARY KEY SECUENCIA(cod_ger) llave primarianombre varchar(25) NOT NULL datos personalesap_pat varchar(25) datos personalesap_mat varchar(25) datos personalesdir_gerente

varchar(25) datos personales

tel_gerente telefono * datos personaleslogin varchar(15) UNIQUE nombre de usuario

NOT NULLpass varchar(15) UNIQUE password

NOT NULL

CLIENTEATRIBUTO TIPO CONDICION VALOR POR DEFECTO DESCRIPCIONid_cliente int PRIMARY KEY SECUENCIA(cod_cli) llave primarianombre varchar(25) NOT NULL datos personalesap_pat varchar(25) datos personalesap_mat varchar(25) datos personalesdir_cliente varchar(25) NOT NULL datos personalestel_cliente telefono * datos personalesCI int UNIQUE datos personales

NOT NULLNIT int UNIQUE password

NOT NULL

SUCURSALATRIBUTO TIPO CONDICION VALOR POR DEFECTO DESCRIPCION

id_sucursal int PRIMARY KEY

SECUENCIA(cod_suc) llave primaria

ciudad_sucursal

varchar(15) NOT NULL datos sucursal

dir_sucursal varchar(25) NOT NULL datos sucursaltel_sucursal telefono* > 6400000 y < 650000 datos sucursalcapital_disp float dinero disponible



id_gerente int FOREIGN KEY llave gerente

CUENTAATRIBUTO TIPO CONDICION VALOR POR DEFECTO DESCRIPCION

id_cuenta int PRIMARY KEY SECUENCIA(cod_cue) llave primariaid_sucursal int FOREIGN KEY llave sucursalid_cliente int FOREIGN KEY llave clientesaldo float NO

NEGATIVA saldo de la cuenta

interes_mensual float MAYOR A 0,5 interés por la cuenta en porcentajeMENOR A 4

fecha_apertura datetime CURRENT_TIME fecha del préstamofecha_ult_mod datetime cambio en la cuenta

PRESTAMOATRIBUTO TIPO CONDICION VALOR POR

DEFECTODESCRIPCION

id_prestamo int PRIMARY KEY

SECUENCIA(cod_pre)

llave primaria

id_sucursal int FOREIGN KEY llave sucursalid_cliente int FOREIGN KEY llave clientemonto_pres float NO

NEGATIVA monto prestado

interes float MAYOR A 0,5 interés por el préstamo en porcentaje

MENOR A 4

fecha_pres datetime CURRENT_TIME fecha del préstamoperiodo_int datetime periodo cuando se

incrementa el interés al préstamo

CLIENTE_CUENTAATRIBUTO TIPO CONDICION VALOR POR DEFECTO DESCRIPCION

id_cliente int PRIMARY KEY FOREIGN KEY

id_cuenta int PRIMARY KEY FOREIGN KEY

CLIENTE_PRESTAMOATRIBUTO TIP

OCONDICION VALOR POR DEFECTO DESCRIPCION

id_cliente int PRIMARY KEY FOREIGN KEY

id_prestamo int PRIMARY KEY



FOREIGN KEY

4.- Insertar 1 gerente, 6 sucursales, 25 clientes 15 cuentas y 10 prestamos

5.- Verificar el resultado de insertar una cuenta con saldo negativo, un interés de 5%

6.- Crear la tabla temporal cliente-cuenta y mostrar los datos personales del cliente y su saldo.



FUNCIONES Y CURSORES


PRA

CTIC

A II


ALIAS

Para una mejor legibilidad del código, es posible definir un alias para un parámetro posicional de una función. Estos alias son necesarios cuando un tipo compuesto se pasa como argumento a una función. La notación punto $1.salary como en funciones SQL no se permiten en PL/pgSQL. Ej.:

nombre ALIAS FOR $n;

VARIABLES PASADAS A LAS FUNCIONES

Las variables que se pasan a las funciones son denominadas con los identificadores $1, $2, etc. (el máximo es 16).

Algunos ejemplos:

CREATE FUNCTION iva_venta(REAL) RETURNS REAL AS ’DECLARE

subtotal ALIAS FOR $1;BEGIN

return subtotal * 0.16;END;

’ LANGUAGE ’plpgsql’;

CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS ’DECLARE

v_string ALIAS FOR $1;index ALIAS FOR $2;

BEGIN-- ALGUNOS CÁCULOS IRÍAN AQUÍ.

END;’ LANGUAGE ’plpgsql’;

FUNCIONES

Una función devuelve un valor como resultado de su ejecución:

Para crear una función se tiene la siguiente sintaxis:

CREATE OR REPLACE FUNCTION [nombre_funcion] (tipo1 nombre1,…, tipoN nombreN) RETURNS tipo_resultado AS ‘

BEGIN sentencia;

END;‘LANGUAGE ‘plpgsql’;



Para eliminar una función se utiliza el comando DROP FUNCTION.

CREATE FUNCTION suma_uno (int4) RETURNS int4 AS ’ -- FUNCION PARA SUMAR 1 + 1BEGIN

RETURN $1 + 1;END;


CREATE FUNCTION concat_texto (text, text) RETURNS text AS ’ -- FUNCION PARA BEGIN -- CONCATENAR TEXTO

RETURN $1 || $2;END;


CREATE FUNCTION excedente_sueldo (EMP, int4) RETURNS bool AS ’--FUNCION QUE DECLARE -- COMPRUEBA SI

emp_reg ALIAS FOR $1; -- EL SALARIO DE UN sal_lim ALIAS FOR $2; -- EMPLEADO ES

BEGIN -- SUPERIOR A UN IF emp_reg.salary ISNULL THEN -- LIMITE

RETURN ’’f’’;END IF;RETURN emp_reg.salario > sal_lim;


NOTA: En la última función se envía un registro EMP que corresponde a la selección SELECT * FROM EMPLEADO WHERE id=’id_buscado’.



CURSORES

Son tablas temporales que permiten ejecutar grandes consultas. Son soportados dentro de las funciones, como un SQL embebido. Los cursores pueden o no hacer copias de sus resultados y sus resultados son de solo lectura.

Utilizan los siguientes comandos:

BEGIN.- Para indicar el comienzo de la operación.

DECLARE.- Define un cursor para acceso a una tabla.

FETCH.- Permite devolver las filas usando un cursor. El numero de filas devueltas es especificado por un número (#), este puede ser reemplazado por ALL que hará que se devuelvan todas las filas del cursor. También se pueden utilizar los comandos BACKWARD y FORWARD para indicar la dirección.

CLOSE.- Libera los recursos del cursor abierto.

COMMIT.- Realiza la transacción actual.

END.- Es un sinónimo en PostgreSQL de COMMIT. Realiza la transacción actual.

ROLLBACK.- Deshace la transacción actual y provoca que todas las modificaciones originadas por la misma sean descartadas.

EJEMPLO:

BEING WORK;DECLARE capital_1 CURSOR FORSELECT * FROM sucursal WHERE capital<20000; -- CREA EL CURSORFETCH FORWARD 2 IN capital_1; -- RECORRE ADELANTE DOS POSICIONESFETCH BACKWARD 1 IN capital_1; -- RECORRE ATRAS UNA POSICIONCLOSE capital_1; -- CIERRA EL CURSORCOMMIT WORK; -- TERMINA LA TRANSACCION



EJERCICIOSUtilizando la base de datos BANCO realizar los siguientes ejercicios:

1.- Crear una función que devuelva el interés ganado de una cuenta. (Saldo*interes_mesual)

2.- Crear una función que devuelva el mayor préstamo realizado a un cliente.

3.- Crear una función para devolver la cantidad total de prestamos de una sucursal

4.- Crear un cursor que contenga los préstamos realizados en el último mes y mostrar los 2 últimos.

5.- Crear un cursor con los clientes de Sucre y mostrar los 3 primeros.



PL/PGSQL (1ª PARTE)


PRA

CTIC

A II

I


1. INTRODUCCIÓN

Es común que los desarrolladores de aplicaciones subutilicen las prestaciones de las bases de

datos relacionales modernas, en ocasiones implemente por desconocer las ventajas que le

ofrecen o por desconocer su manejo. Dentro de PostgreSQL, la base de datos de código abierto

más poderosa, se pueden desarrollar funciones en varios lenguajes. El lenguaje PL/pgSQL es uno

de los más utilizados dentro de PostgreSQL, debido a que guarda cierta similitud con PL/SQL de

Oracle y a su facilidad de uso.

En este tutorial se mostrará la sintaxis, el control de flujo y otras características del lenguaje,

además de presentarán algunos ejemplos reales.(3)

VENTAJAS DE USAR PL/PGSQL

SQL es el lenguaje estándar para realizar consultas a un servidor de base de datos. Cada

sentencia SQL se ejecuta de manera individual por el servidor, lo cual implica que las

aplicaciones cliente deben enviar cada consulta al servidor, esperar a que la procese, recibir los

resultados, procesar los datos y después enviar la siguiente sentencia.

Al usar PL/pgSQL es posible realizar cálculos, manejo de cadenas y consultas dentro del servidor

de la base de datos, combinando el poder de un lenguaje procedimental y la facilidad de uso de

SQL, minimizando el tiempo de conexión entre el cliente y el servidor.4

2. ESTRUCTURA DE PL/PGSQL4 Programación de funciones en PL/pgSQL para PostgreSQL Roberto Andrade Fonseca. ABL Consultores, S.A. de C.V.



Estructura de PL / PsSQL

Es un lenguaje estructurado a base de bloques. La sintaxis de cada bloque esta definida de la siguiente manera:

[ <<label>> ][ DECLARE declaraciones ]BEGIN sentenciasEND;

Pueden existir varios bloques o sub-bloques en la sección de sentencias de un bloque. Los sub-

bloques pueden ser usados para ocultar las variables a los bloques más externos.

Normalmente una de las sentencias es el valor de retorno, usando la palabra clave RETURN.

Las variables declaradas en la sección que antecede a un bloque se inicializan a su valor por

omisión cada vez que se entra al bloque, no solamente al ser llamada la función. Por ejemplo:

CREATE FUNCTION funcion() RETURNS INTEGER AS ’DECLARE

cantidad INTEGER := 30;BEGIN

RAISE NOTICE ’’Cantidad contiene aquí %’’, cantidad;-- Cantidad contiene aquí 30cantidad := 50;-- Creamos un sub-bloque

DECLAREcantidad INTEGER := 80;BEGINRAISE NOTICE ’’Cantidad contiene aquí %’’, cantidad;-- Cantidad contiene aquí 80END;

RAISE NOTICE ’’Cantidad contiene aquí %’’, cantidad;-- Cantidad contiene aquí 50RETURN cantidad;


2.1. COMENTARIOS, CONSTANTES, VARIABLES Y ASIGNACIÓN DE VALORES

2.1.1 COMENTARIOS


Bloque 1

Sub-bloque


Existen dos tipo de comentarios en PL/pgSQL. Un doble guión –– da inicio a un comentario, el cual se extiende hasta el final de la línea. Un /* inicia un bloque que se extiende hasta la primera ocurrencia de */.

–– ESTE ES UN COMENTARIO DE UNA LINEA

/* ESTE ES UN COMENTARIO

DE MAS DE UNA LINEA */

2.1.2 VARIABLES Y CONSTANTES

Todas las variables, filas y registros usados en un bloque o en sus sub-bloques deben declararse en la sección de declaraciones del bloque. La excepción es la variable de un ciclo FOR que itera sobre un rango de valores enteros.

Las variables en PL/pgSQL pueden ser de cualquier tipo de datos de SQL, como INTEGER, VARCHAR y CHAR. El valor por omisión de todas las variables es el valor NULL de SQL.

A continuación se muestran algunos ejemplos de declaración de variables:

Id_usuario INTEGER;

cantidad INTEGER;

url VARCHAR;

2.1.3 ASIGNACIÓN DE VALORES

Para la asignación de valores a las variables se utiliza := como se muestra a continuación:

id_usuario CONSTANT INTEGER := 10; –– ASIGNACION DE UNA CONSTANTE

cantidad INTEGER := 32;

url VARCHAR := ’’http://misitio.com’’;

2.2. ESTRUCTURAS DE CONTROL DE FLUJO



2.2.1. CONDICIONES

IF condición THENsentencias

[ELSEsentencias]

END IF;

condición debe devolver un valor que al menos pueda ser adaptado en un tipo booleano.

2.2.2. BUCLES

Hay varios tipos de bucles.

[<<etiqueta>>]LOOP

sentenciasEND LOOP;

Se trata de un bucle no condicional que ha de ser terminado de forma explicita, mediante una sentencia EXIT. La etiqueta opcional puede ser usada por las sentencias EXIT de otros bucles anidados, para especificar el nivel del bucle que ha de terminarse.

[<<etiqueta>>]WHILE condición LOOP

sentenciasEND LOOP;

Se trata de un lazo condicional que se ejecuta mientras la evaluación de condición sea cierta.

[<<etiqueta>>]FOR nombre_variable IN [ REVERSE ]condición LOOP

sentenciasEND LOOP;

Se trata de un bucle que se itera sobre un rango de valores enteros. La variable nombre_variable se crea automáticamente con el tipo entero, y existe solo dentro del bucle. Las dos expresiones dan el límite inferior y superior del rango y son evaluados sólo cuando se entra en el bucle. El paso de la iteración es siempre 1.

[<<etiquetas>>]FOR registro | fila IN clausula_select LOOP



sentenciasEND LOOP;

El registro o fila se asigna a todas las filas resultantes de la clausula de selección, y la sentencia se ejecuta para cada una de ellas. Si el bucle se termina con una sentencia EXIT, la última fila asignada es aún accesible después del bucle.

EXIT [ etiqueta ] [ WHEN condición ];

Si no se incluye etiqueta, se termina el lazo más interno, y se ejecuta la sentencia que sigue a END LOOP. Si se incluye etiqueta ha de ser la etiqueta del bucle actual u de otro de mayor nivel. EL bucle indicado se termina, y el control se pasa a la sentencia de después del END del bucle o bloque correspondiente.




1.- Crear una función en PL/pgSQL que devuelva cuantos clientes tienen una cuenta superior a 5000

2.- Crear una función que en PL/pgSQL que devuelva cuantos clientes tienen una cuenta creada por lo menos hace un año



PL/PGSQL (2ª PARTE)


PRA

CTIC

A IV


SENTENCIAS

Cualquier cosa no comprendida por el analizador PL/pgSQL tal como se especifica adelante será enviada al gestor de la base de datos, para su ejecución. La consulta resultante no devolverá ningún dato.

ASIGNACIÓN DE UNA SELECCIÓN COMPLETA

Una asignación de una selección completa en un registro o fila puede hacerse del siguiente modo:

SELECT expresion INTO destino FROM ...;

destino puede ser un registro, una variable de fila o una lista separada por comas de variables y campo de de registros o filas.Ej:

SELECT COUNT(empleados) INTO cant_emp FROM EMPLEADOS;

SELECT EMPLEADO.all INTO empleados_jovenes FROM EMPLEADOS WHERE (edad<35);

Si una fila o una lista de variables se usan como objetivo, los valores seleccionados han de coincidir exactamente con la estructura de los objetivos o se producirá un error de ejecución. La palabra clave FROM puede preceder a cualquier calificador válido, agrupación, ordenación, etc. que pueda pasarse a una sentencia SELECT.

Existe una variable especial llamada FOUND de tipo booleano, que puede usarse inmediatamente después de SELECT INTO para comprobar si una asignación ha tenido éxito.

SELECT * INTO registro FROM EMP WHERE empnombre = nombre;IF NOT FOUND THEN

RAISE EXCEPTION ’’empleado % no encontrado’’, nombre;END IF;

Si la selección devuelve múltiples filas, solo la primera se mueve a los campos objetivo; todas las demás se descartan.

ATRIBUTOS

Usando los atributos %TYPE y %ROWTYPE, es posible declarar variables con el mismo tipo de dato o estructura de otro item de la base de datos (por ejemplo, un campo de una tabla).

%TYPE Proporciona el tipo de dato de una variable o una columna. Se puede utilizar para declarar variables que almacenen valores de bases de datos. Por ejemplo, supongamos que se tiene una columna llamada id_usuario en la tabla usuarios. Para declarar una variable con el mismo tipo de dato que el usado en nuestra tabla de usuarios, lo que se haría es:

id_usuario usuarios.id_usuario\%TYPE;



TABLA USUARIO TIPO PL/PGSQL TIPOid_usario INT id_usuario

usuarios.id_usuario\%TYPE

INT

Al usar %TYPE no importan los cambios que se realicen en la definición de los datos de una tabla siempre obtendremos el tipo del valor al que hacemos referencia.

nombre T_tabla%ROWTYPE Declara una renglón con la estructura de la tabla especificada.

T_tabla puede ser una tabla o una vista que exista en la base de datos. Los campos del renglón se accedan con la notación punto. Los parámetros de una función pueden ser de tipo compuesto (renglones completos de una tabla). Es este caso, el identificador correspondiente $n será del tipo rowtype, pero debe usarse un seudónimo o alias usando el comando ALIAS .

Solamente los atributos del usuario de la tabla pueden ser accesibles en el renglón, ni los OID ni otros atributos del sistema (debido a que el renglón puede ser de una vista). Los campos de un rowtype heredan los tamaños de los campos o la precisión de los tipos de dato para char(), etc.



VISTAS Y REGLAS


PRA

CTIC

A V


CONCEPTOS Y DEFINICION DE VISTA.

Los usuarios que acceden a una base de datos relacional, lo hacen típicamente a través de vistas, de modo que diferentes usuarios tienen diferentes vistas.

Una vista, en sí, es una “tabla virtual” derivada, con nombre. El término virtual significa que la tabla no existe como tal, pero para el usuario si parece existir. Por el contrario una tabla es real, en el sentido que existe y está almacenada en algún dispositivo físico de almacenamiento.Las vistas no se sustentan en datos almacenados físicamente, solo se almacena su definición en el catálogo de sistema, y esta construida en base a otras tablas.Las vistas tienen la misma estructura que una tabla: filas y columnas. Los datos se recuperan mediante una consulta SELECT y se presentarán igual que los de una tabla.

DEFINICIÓN DE VISTA EN POSTGRESQL.

Una vista es una relación virtual cuya extensión se deriva de las relaciones básicas de la base de datos, es decir, por medio de sentencias SELECT.

La sintaxis de definición de una vista en SQL es:

CREATE [OR REPLACE] VIEW <nombre_de_vista> ASsentencia_SELECT campos1 [, campo2, ... , campoN ]FROM tabla1 [, tabla2, ... , tablaN ][ WHERE condiciones_de_consulta ][ ORDER BY lista_de_campos ][ GROUP BY lista_de_campos ]

Ejemplo:

CREATE VIEW cliente_apellido ASSELECT * FROM clientes WHERE ap_paterno LIKE ‘A%’

Las vistas en PostgreSQL no son actualizables, es decir, si bien es cierto, son tratadas como tablas, no es posible hacer INSERT, DELETE ni UPDATE sobre las vistas, esta desventaja es una característica particular en PostgreSQL dado que esta cualidad si esta disponible en otros motores de bases de datos como ORACLE, Informix y SQL Server, sin embargo cabe notar que PostgreSQL cubre esta falencia en las vistas con la creación de reglas (CREATE RULE) que permite llenar el vacío dejado por la vista.



ELIMINAR VISTAS.

Si en algún caso, se necesita modificar una vista, deberá eliminarla primero y luego volverla a crear, de forma similar si también necesitas eliminar una vista de la base de datos, la sentencia es la siguiente:

DROP VIEW <nombre_de_la_vista>



REGLAS

REGLAS SOBRE INSERT, UPDATE Y DELETE

Las reglas en PostgreSQL permiten realizar las siguientes acciones:

Pueden no tener acción. Pueden tener múltiples acciones. La palabra clave INSTEAD es opcional. Las pseudo-relaciones NEW y OLD se vuelven utilizables. Puede haber cualificaciones a las reglas.

Su sintaxis es la siguiente:

CREATE RULE nombre_regla AS ON eventTO objeto [WHERE condición]DO [INSTEAD] [accion | (acciones) | NOTHING];

Ejemplos:

CREATE RULE suc_ins_protec AS ON INSERT TO vista_sucursalDO INSTEAD NOTHING;

CREATE RULE suc_ins AS ON INSERT TO sucursal_vistaDO INSTEADINSERT INTO sucursal VALUES (NEW.sucursal.nombre, NEW.sucursal. ciudad,NEW.sucursal.direccion, NEW.sucursal.telefono, NEW.sucursal.capital);



EJERCICIOS1.- Crear una vista para los Clientes por ciudad2.- Crear una vista con todos los datos de las sucursales3.- Crear una vista con los clientes y sus cuentas4.- Crear una vista con los clientes y sus préstamos5.- Crear reglas para que no se puedan insertar datos en las vistas



FUNCIONES TRIGGER Y TRIGGERS


PRA

CTIC

A V

I


FUNCIONES TRIGGER

Tienen las siguientes características:

No tienen argumentos. Tienen acceso a los constructores especiales NEW y OLD. El tipo de valor de retorno de una función trigger, debería ser de tipo trigger.

Ejemplo:

CREATE OR REPLACE FUNCTION actual()RETURNS trigger AS $$

BEGINNEW.fecha_prestamo=now();RETURN NEW;

END;$$LANGUAGE plpgsql;

ALTERAR Y ELIMINAR UN TRIGGER

ALTER TRIGGER permite modificar un trigger, DROP TRIGGER borrara todas las referencias que existan a la definición de un disparador.



TRIGGERS

Un trigger define una acción basada en una ocurrencia específica en una base de datos. En PostgreSQL , esto significa la ejecución de un procedimiento almacenado, basado en acciones repetidas en una tabla específica.

Los triggers son definidos por seis características:

El nombre del trigger. El Tiempo en el cual se debe inicializar el trigger. El evento en el cual se disparara el trigger. La tabla en la cual se disparará el trigger. La frecuencia de ejecución. La función que debería ser llamada.

CREACIÓN DE TRIGGERS

CREATE TRIGGER, crea un nuevo disparador en la base de datos actual.

Sintaxis:CREATE TRIGGER nombre_trigger{BEFORE|AFTER} {EVENT[OR…]}ON table[FOR [EACH]{ROW|STATEMENT}]EXECUTE PROCEDURE nombre_función{argumentos}

Donde:

BEFORE El trigger se ejecuta antes de que la operación sea realizada. AFTER El trigger se ejecuta después de que la operación haya sido realizada. EVENT Especifica el tipo de consulta que va a ejecutar el trigger, puede ser DELETE,

INSERT o UPDATE. ON TABLE Indica la tabla donde se ejecutara el trigger. FOR EACH Define la frecuencia en con la cual es llamada la ejecución de la función. EXECUTE PROCEDURE Define el procedimiento que va a ser ejecutado por el trigger.

Ejemplo:

CREATE TRIGGER emp_sueldo BEFORE INSERT OR UPDATE ON empleadoFOR EACH ROW EXECUTE PROCEDURE emp_sueldo();

NOTA: La función utilizada en el trigger deberá ser declarada antes del Trigger, en el ejmplo la función emp_sueldo()




1.- Crear un trigger que controle que el capital no sea menor a 1000.

2.- Crear un trigger que controle que el saldo de un cliente sea menor a 100.

3.- Crear un trigger que controle que ningún cliente saque un préstamo mayor al capital de la sucursal.

4.- Crear un Trigger para calcular automáticamente el interés de un préstamo al concluir el periodo de cálculo de interés.



ANEXO 1 – TIPOS DE DATOS DE POSTGRES



Tipo Descripción



SET conjunto de tuplasabstime fecha y hora absoluta de rango limitado (Unix system time)aclitem lista de control de accesobool booleano 'true'/'false'box rectángulo geométrico '(izquierda abajo, derecha arriba)'bpchar caracteres rellenos con espacios, longitud especificada al momento de creaciónbytea arreglo de bytes de longitud variablechar un sólo caráctercid command identifier type, identificador de secuencia en transaccionescidr dirección de redcircle círculo geométrico '(centro, radio)'date fecha ANSI SQL 'aaaa-mm-dd'datetime fecha y hora 'aaaa-mm-dd hh:mm:ss'filename nombre de archivo usado en tablas del sistemafloat4 número real de precisión simple de 4 bytesfloat8 número real de precisión doble de 8 bytesinet dirección de redint2 número entero de dos bytes, de -32k a 32kint28 8 números enteros de 2 bytes, usado internamenteint4 número entero de 4 bytes, -2B to 2Bint8 número entero de 8 bytes, 90#9018 dígitosline línea geométrica '(pt1, pt2)'lseg segmento de línea geométrica '(pt1, pt2)'macaddr dirección MACmoney unidad monetaria '$d,ddd.cc'name tipo de 31 caracteres para guardar identificadores del sistemanumeric número de precisión múltipleoid tipo de identificación de objetosoid8 arreglo de 8 oids, utilizado en tablas del sistemapath trayectoria geométrica '(pt1, ...)'point punto geométrico '(x, y)'polygon polígono geométrico '(pt1, ...)'regproc procedimiento registradoreltime intervalo de tiempo de rango limitado y relativo (Unix delta time)smgr manejador de almacenamiento (storage manager)text cadena de caracteres nativa de longitud variabletid tipo de identificador de tupla, localización física de tupla



Tipo Descripcióntime hora ANSI SQL 'hh:mm:ss'timespan intervalo de tiempo '@ <number> <units>'timestamp

fecha y hora en formato ISO de rango limitado

tinterval intervalo de tiempo '(abstime, abstime)'unknown tipo desconocidovarchar cadena de caracteres sin espacios al final, longitud especificada al momento de creación



ANEXO 2 - MANEJO DE DATOS TEMPORALES EN POSTGRES



OBTENER LA FECHA Y HORA ACTUAL

SELECT CURRENT_TIMESTAMP;SELECT now();SELECT TIMESTAMP 'now';SELECT timeofday();Resultado: Sat Feb 17 19:07:32.000126 2010 EST

SELECT CURRENT_TIME;Resultado: 14:39:53.662522-05

SELECT CURRENT_DATE;2010-10-23

SELECCIONAR UN CAMPO ESPECÍFICO DE UNA FECHA

OBTENER EL DÍASELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');Resultado: 16

OBTENER EL MESSELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');Resultado: 02

OBTENER EL AÑOSELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');Resultado: 2001



Fuente: http://www.postgresql.org/docs/7.3/static/functions-datetime.html


http://www.postgresql.org/docs/7.3/static/functions-datetime.html

guía laboratorio

Documents