guía laboratorio
TRANSCRIPT
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).
BASE DE DATOS III GUÍA DE LABORATORIO
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.
BASE DE DATOS III GUÍA DE LABORATORIO
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
BASE DE DATOS III GUÍA DE LABORATORIO
INTRODUCCIÓN A POSTGRESQL
INGRESANDO A POSTGRESQL, MANEJO DE BASE DE DATOS, ESQUEMAS, TABLAS, DDL, DML
BASE DE DATOS III GUÍA DE LABORATORIO
BASE DE DATOS III GUÍA DE LABORATORIO
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
BASE DE DATOS III GUÍA DE LABORATORIO
BARA DE HERRAMIENTAS
EXPLORADOR DE OJBJETOS Y VENTANA DE PROPIEDADES
ING. ENRIQUE YAÑEZ VENEGAS Página 10
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.
BASE DE DATOS III GUÍA DE LABORATORIO
PANEL SQL
ING. ENRIQUE YAÑEZ VENEGAS Página 11
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.
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 12
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
BASE DE DATOS III GUÍA DE LABORATORIO
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 ] ]
ING. ENRIQUE YAÑEZ VENEGAS Página 13
BASE DE DATOS III GUÍA DE LABORATORIO
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;
ING. ENRIQUE YAÑEZ VENEGAS Página 14
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 15
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 16
BASE DE DATOS III GUÍA DE LABORATORIO
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;
ING. ENRIQUE YAÑEZ VENEGAS Página 17
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 18
BASE DE DATOS III GUÍA DE LABORATORIO
ADMINISTRACIÓN DE POSTGRESQL (2ª PARTE)
ING. ENRIQUE YAÑEZ VENEGAS Página 19
PRA
CTIC
A I
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 20
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 21
BASE DE DATOS III GUÍA DE LABORATORIO
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 );
ING. ENRIQUE YAÑEZ VENEGAS Página 22
BASE DE DATOS III GUÍA DE LABORATORIO
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));
ING. ENRIQUE YAÑEZ VENEGAS Página 23
BASE DE DATOS III GUÍA DE LABORATORIO
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;
ING. ENRIQUE YAÑEZ VENEGAS Página 24
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 25
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 26
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 27
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 28
BASE DE DATOS III GUÍA DE LABORATORIO
FUNCIONES Y CURSORES
ING. ENRIQUE YAÑEZ VENEGAS Página 29
PRA
CTIC
A II
BASE DE DATOS III GUÍA DE LABORATORIO
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’;
ING. ENRIQUE YAÑEZ VENEGAS Página 30
BASE DE DATOS III GUÍA DE LABORATORIO
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;
’ LANGUAGE ’plpgsql’;
CREATE FUNCTION concat_texto (text, text) RETURNS text AS ’ -- FUNCION PARA BEGIN -- CONCATENAR TEXTO
RETURN $1 || $2;END;
’ LANGUAGE ’plpgsql’;
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;
END;’ LANGUAGE ’plpgsql’;
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’.
ING. ENRIQUE YAÑEZ VENEGAS Página 31
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 32
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 33
BASE DE DATOS III GUÍA DE LABORATORIO
PL/PGSQL (1ª PARTE)
ING. ENRIQUE YAÑEZ VENEGAS Página 34
PRA
CTIC
A II
I
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 35
BASE DE DATOS III GUÍA DE LABORATORIO
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;
END;’ LANGUAGE ’plpgsql’;
2.1. COMENTARIOS, CONSTANTES, VARIABLES Y ASIGNACIÓN DE VALORES
2.1.1 COMENTARIOS
ING. ENRIQUE YAÑEZ VENEGAS Página 36
Bloque 1
Sub-bloque
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 37
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 38
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 39
BASE DE DATOS III GUÍA DE LABORATORIO
EJERCICIOSUtilizando la base de datos BANCO realizar los siguientes ejercicios:
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
ING. ENRIQUE YAÑEZ VENEGAS Página 40
BASE DE DATOS III GUÍA DE LABORATORIO
PL/PGSQL (2ª PARTE)
ING. ENRIQUE YAÑEZ VENEGAS Página 41
PRA
CTIC
A IV
BASE DE DATOS III GUÍA DE LABORATORIO
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;
ING. ENRIQUE YAÑEZ VENEGAS Página 42
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 43
BASE DE DATOS III GUÍA DE LABORATORIO
VISTAS Y REGLAS
ING. ENRIQUE YAÑEZ VENEGAS Página 44
PRA
CTIC
A V
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 45
BASE DE DATOS III GUÍA DE LABORATORIO
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>
ING. ENRIQUE YAÑEZ VENEGAS Página 46
BASE DE DATOS III GUÍA DE LABORATORIO
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);
ING. ENRIQUE YAÑEZ VENEGAS Página 47
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 48
BASE DE DATOS III GUÍA DE LABORATORIO
FUNCIONES TRIGGER Y TRIGGERS
ING. ENRIQUE YAÑEZ VENEGAS Página 49
PRA
CTIC
A V
I
BASE DE DATOS III GUÍA DE LABORATORIO
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 50
BASE DE DATOS III GUÍA DE LABORATORIO
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()
ING. ENRIQUE YAÑEZ VENEGAS Página 51
BASE DE DATOS III GUÍA DE LABORATORIO
EJERCICIOSUtilizando la base de datos BANCO realizar los siguientes ejercicios:
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.
ING. ENRIQUE YAÑEZ VENEGAS Página 52
BASE DE DATOS III GUÍA DE LABORATORIO
ANEXO 1 – TIPOS DE DATOS DE POSTGRES
ING. ENRIQUE YAÑEZ VENEGAS Página 53
BASE DE DATOS III GUÍA DE LABORATORIO
Tipo Descripción
ING. ENRIQUE YAÑEZ VENEGAS Página 54
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 55
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 56
BASE DE DATOS III GUÍA DE LABORATORIO
ANEXO 2 - MANEJO DE DATOS TEMPORALES EN POSTGRES
ING. ENRIQUE YAÑEZ VENEGAS Página 57
BASE DE DATOS III GUÍA DE LABORATORIO
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
ING. ENRIQUE YAÑEZ VENEGAS Página 58
BASE DE DATOS III GUÍA DE LABORATORIO
Fuente: http://www.postgresql.org/docs/7.3/static/functions-datetime.html
ING. ENRIQUE YAÑEZ VENEGAS Página 59