44
1 Programación SQL Lucio Salgado Diciembre 2008

Capitulo7_ProgramacionSQL

Embed Size (px)

Citation preview

Page 1: Capitulo7_ProgramacionSQL

1

ProgramaciónSQL

Lucio SalgadoDiciembre 2008

Page 2: Capitulo7_ProgramacionSQL

2

Gestionadores de Bases de Datos (DBMS)

Es el software que permite administrar bases de datos, a través de ella el usuario puede usar los datos con mucha transparencia. En términos simples es una interfaz datos usuario.Los basados en el modelo relacional se denomina RDBMS

Page 3: Capitulo7_ProgramacionSQL

3

Tipos de RDBMS

De escritorioCaracterísticas

Para pocos usuariosProgramación en una sola capaDébil en:Concurrencia,TransaccionesRecuperaciónEj. Paradox, Dbase, MsAccess

Page 4: Capitulo7_ProgramacionSQL

4

RDBMS de escritorio

Aplicación

BDE/DAO

Base deDatos

Aplicación

BDE/DAO

Page 5: Capitulo7_ProgramacionSQL

5

Tipos de RDBMS

Basado en ServidorCaracterísticas

Para muchos usuariosProgramación en varias capasFuerte enConcurrenciaTransaccionesRecuperaciónEj. Oracle, MsSQL, Interbase, Informix, Sybase,DB/2, Btrieve. MySQL,Ingres

Page 6: Capitulo7_ProgramacionSQL

6

RDBMS basado en servidor

Aplicación

ClienteSQL

Aplicación

ClienteSQLServidor

SQL

Base deDatos

Page 7: Capitulo7_ProgramacionSQL

7

Potencialidades en Servidores SQL

Trabajo es más productivoConcurrencia, implementar transacciones atómicasConversación basada en el lenguaje SQLMantener Código en el servidorProcedimientos:

Conjuntos de instrucciones que se guardan en la base de datos. Evitan que los datos viajen

Triggers:Son también secuencia de instrucciones pero se activan en momentos especiales (UPDATE,INSERT,DELETE)

Page 8: Capitulo7_ProgramacionSQL

8

Criterios para evaluar un servidor SQL

Plataformas Soportadas.Soporte a tipos de datos.Lenguaje de triggers y procedimientos.Implementación de transacciones, recuperación y aislamiento.Segmentación.Replicación.Precio.

Page 9: Capitulo7_ProgramacionSQL

9

OracleAcepta varias plataformas, como Windows, Unix, Linux, etc.Permite funciones avanzadas.Segmentación y Replicación.Todos los tipos de datos.Extensión procedimental de le lenguaje PL/SQL que es completo y potente.Paquetes y tipos de datos abstractos.Definen Clases((ya un DBMS objeto relacional)Lenguaje de triggers completo.Alto costo, por licencia y sobre todo por mantenimiento (support). Producto muy estable

Page 10: Capitulo7_ProgramacionSQL

10

InterbaseAcepta muchas plataformasEl mantenimiento Simple. Aunque su entorno de administración deja mucho que desear.Cada base de datos se localiza en un archivo .gdbLas Copias de seguridad se pueden realizar en calienteAcepta todo tipo de datos y orientado a cumplir los estándares.El uso de Restricción de Integridad Referencial es completoTiene un Lenguaje de procedimientos completo.Acceso concurrente garantizado.

Page 11: Capitulo7_ProgramacionSQL

11

MS SQL ServerEntorno MS Windows

Win NT, WINxx, Windows XP, Windows 2003.Instalación y manejo sencillos.Mantenimiento avanzado algo complicadoNo copias de seguridad en vivoIntegridad referencial no muy sofisticado.Muy particular su lenguaje de procedimientos: Transact-SQLIntegración con otros productosLas últimas versiones MS SQL Server 7.0, MS SQL Server 2000, MS SQL Server 2005, son mucho más sofisticadas y estables.

Page 12: Capitulo7_ProgramacionSQL

12

Procedimientos Almacenados

Un procedimiento se puede entender como un algoritmo cuya definición reside en la base de datos y es ejecutado por el servidor del sistema, cuando algún usuario lo solicite.

Page 13: Capitulo7_ProgramacionSQL

13

Definición y ejecución de un Procedimiento-1

ORACLEPara crear:

Create or replace procedure <<nombre>>(<<parámetros>>)as<<CuerpodeProcedimiento>>Llamar procedimientos:execute <<nombre>> [(<<parámetros>>)]

Page 14: Capitulo7_ProgramacionSQL

14

Ejemplo-1CREATE OR REPLACE PROCEDURE clienteInsProc(

v_nit IN CHAR, v_nombre IN CHAR, v_direccion IN CHAR, v_telefono IN CHAR)

AS BEGIN

INSERT INTO cliente(nit, nombre, direccion, telefono) VALUES(v_nit, v_nombre, v_direccion, v_telefono);

END; /

Page 15: Capitulo7_ProgramacionSQL

15

Definición y ejecución de un Procedimiento-2

INTERBASEPara crear:create procedure <<nombre>>[(<<parametrosdeentrada>>)][returns (<<parametros de salida>>)]as <<Cuerpodeprocedimiento>>Llamar procedimientos:execute procedure <<nombre>> <<parametrosentradas>> returning_values<<parametrosdeSalida>>

Page 16: Capitulo7_ProgramacionSQL

16

Ejemplo-2SET TERM !! ;CREATE PROCEDURE clienteInsProc( v_ruc CHAR(10),

v_nombre CHAR(25),v_direccion CHAR(10),v_telefono CHAR(10))

ASBEGIN

INSERT INTO cliente(ruc,nombre, direccion, telefono)VALUES(:v_ruc,:v_nombre,:v_direccion,:v_telefono);

END;!!SET TERM ; !!

Page 17: Capitulo7_ProgramacionSQL

17

Definición y ejecución de un Procedimiento-3

MS SQL SERVERPara crearCreate procedure <<nombre>>(<<parametrosdeEntradaySalida>>)as<<cuerpodeprocedimiento>>Llamar procedimientos:exec nombre [parametros]

Page 18: Capitulo7_ProgramacionSQL

18

Ejemplo 3CREATE PROCEDURE clienteInsProc( @ruc char(10),

@nombre char(25) =NULL,@direccion char(10) =NULL,@telefono char(10) =NULL)

ASBEGININSERT INTO cliente(ruc,nombre,direccion, telefono)

VALUES(@ruc,@nombre,@direccion,@telefono)ENDgo

Page 19: Capitulo7_ProgramacionSQL

19

Ejecutando el procedimiento

MS SQL ServerEXEC clienteInsProc ’452341’, ’JOSE BUENDIA’, ’SUCRE #1435’, ’5243612’

Page 20: Capitulo7_ProgramacionSQL

20

Ejemplo 4

Actualiza la tabla historial (registro de materias aprobadas) con los datos de la tabla registro para algún estudiante en particular conocido su ci: Estudiante(ci,nom,dir,tel,ing,fechanac)Registro(ci,sigla,sem,nota)Materia(sigla,nom,creditos)Historial(ci,sigla,sem,nota)

Page 21: Capitulo7_ProgramacionSQL

21

Ejemplo 4 - código MS SQL Server

CREATE PROCEDURE act_hist (@cix CHAR(15))ASBEGIN

DELETE FROM historialWHERE ci=@cixINSERT INTO historial (ci,sigla,sem,nota)SELECT ci,sigla,sem,notaFROM registroWHERE ci=@cix AND nota >=51

END

Page 22: Capitulo7_ProgramacionSQL

22

Aspectos Complementariosde

Programación

Page 23: Capitulo7_ProgramacionSQL

23

Uso de variables o identificadores -1

ORACLELa declaración se hace así:declare <<nombrevar>> tipo

Ej.declare x numberdeclare nom char(20)

Page 24: Capitulo7_ProgramacionSQL

24

Uso de variables o identificadores -2

INTERBASEPara Declarar:declare variable <<nombrevar>> tipo

Ej.declare variable x char(10);declare variable y integer;

Page 25: Capitulo7_ProgramacionSQL

25

Uso de variables o identificadores -3

MS SQL SERVERAl igual que en los anteriores, excepto que el nombre siempre debe comenzar con el símbolo @:

declare <<nombrevar>> tipo

Ej.declare @x char(10);declare @y integer;

Page 26: Capitulo7_ProgramacionSQL

26

Asignaciones-1

ORACLEPara la Asignación se usa := <<Variable>>:=<<Expresión>>Ej.

x:=x+1y:= y*2 +x

Page 27: Capitulo7_ProgramacionSQL

27

Asignaciones-2

INTERBASELa asignación es algo muy simpleVariable=ExpresionEjemplo.y=y+1;x=’2340’;

Page 28: Capitulo7_ProgramacionSQL

28

Asignaciones-3

MS SQL SERVERUna asignación se ve algo distintoselect <<Variable>>=<<Expresión>>Ej.select @y=@y+1;select @x=‘2340’;

Page 29: Capitulo7_ProgramacionSQL

29

Estructuras de Control-1

ORACLECondicionalesif (<<condicion>>) then

<<instruccion1>>[else

<< instruccion2>>]Cicloswhile (<<Condicion>>) loop<<instrucción>>

end loop

Page 30: Capitulo7_ProgramacionSQL

30

Estructuras de Control-2

INTERBASECondicionalesif (<<condicion>>) then <<instruccion1>>[else <<instruccion2>>]Cicloswhile (<<condicion>>) do<<instrucción>>

Page 31: Capitulo7_ProgramacionSQL

31

Estructuras de Control-3Condicionalesif (<<condicion>>) << instruccion1>>

[else<< instruccion2>>]

select case when <<condicion1>> then <<valor1Mostrar>>…when <<condicionN>> then <<valorNMostrar>>

endCicloswhile (<<Condicion>>) do<<instrucción>>

Page 32: Capitulo7_ProgramacionSQL

32

Un poco de códigoMS SQL Server

DECLARE @x INTSET @x=2SELECT @x=@x+4SELECT @xIF @x>5

SELECT 'mayor a 5'SELECT CASE

WHEN @x>5 THEN 'cinco'WHEN @x<10 THEN 'diez'

ENDWHILE (@x<20)BEGIN

SET @x=@x+1SELECT @x

END

Page 33: Capitulo7_ProgramacionSQL

33

Cursores

Un cursor es una variable de “tipo tabla”, concretamente puede entenderse como un conjunto de registros. pasos: 1. Declarar2. Abrir3. Usar (normalmente en una estructura repetitiva

hasta fin del cursor) 4. Cerrar el cursor.

Page 34: Capitulo7_ProgramacionSQL

34

Oracle

- cursor <<nombre>> is<<sentenciaSelect>>

- open <<nombre>>- fetch <<nombre>> into

<<variables>>- close <<nombre>>

Page 35: Capitulo7_ProgramacionSQL

35

Interbase

Iteracion en consultas (equivalente a cursores).- for <<instruccionSelect>> into<<variables>>do <<Instrucción>>

Page 36: Capitulo7_ProgramacionSQL

36

MS SQL Server

- declare <<nombre>> cursor for<<sentenciaSelect>>- open <<nombre>>- fetch next from <<nombre>> into<<variables>>- close <<nombre>>

Page 37: Capitulo7_ProgramacionSQL

37

Un ejemplo de cursoresMS SQL Server-1

Actualiza los datos de la tabla paralelos con la información que hay en la tabla registro, del siguiente modelo relacional.Estudiante(ci,nom,dir,tel,ing,fechanac)Registro(ci,sigla,sem,nota)Materia(sigla,nom,creditos)Historial(ci,sigla,sem,nota)Paralelo(sigla,sem,inscritos,reprobados,aprobados)

Page 38: Capitulo7_ProgramacionSQL

38

Un ejemplo de cursoresMS SQL Server-1

/*CURSORES*/CREATE PROCEDURE l_par(@semx CHAR(7))ASDECLARE @sigla CHAR(7),@inscritos INT,@aprobados

INT,@reprobados INT/* Declaracion del cursor*/DECLARE lpara CURSOR FORSELECT sigla FROM paraleloWHERE sem=@semx/* Abrir el cursor */OPEN lpara/* Usar el cursor */

Page 39: Capitulo7_ProgramacionSQL

39

Un ejemplo de cursoresMS SQL Server-2

FETCH NEXT FROM lpara INTO @siglaWHILE (@@FETCH_STATUS=0)BEGIN

SELECT @inscritos=COUNT(*)FROM registroWHERE sigla=@sigla AND sem=@semxSELECT @aprobados=COUNT(*)FROM registroWHERE sigla=@sigla AND sem=@semx AND nota

>=51

Page 40: Capitulo7_ProgramacionSQL

40

Un ejemplo de cursoresMS SQL Server-3

SELECT @reprobados=COUNT(*)FROM registroWHERE sigla=@sigla AND sem=@semx AND nota < 51UPDATE paralelo SET inscritos=@inscritos,reprobados=@reprobados,aprobados=@aprobados WHERE CURRENT OF lpara/* Usar el cursor */FETCH NEXT FROM lpara INTO @sigla

END/*cerrar el cursor*/CLOSE lpara/*Eliminar la variable cursor*/ DEALLOCATE lpara

Page 41: Capitulo7_ProgramacionSQL

41

Disparadores (triggers)

Un trigger es código que se almacena en la base de datos, su comportamiento es lo que le diferencia de un procedimiento, Se activa con algún evento con un elemento de la base de datos Ej. una tabla, al adicionar, al modificar un valor o eliminar un dato.

Page 42: Capitulo7_ProgramacionSQL

42

Ejemplo

Mantiene sincronizada el total de la tabla factura con los datos de detalle.

Factura(num,fecha,nit,total)Detalle(num,codpro,cantidad,monto)

Al inserta o adicionar una fila detalle se aumentará el valor de monto en factura

Page 43: Capitulo7_ProgramacionSQL

43

CódigoORACLE

CREATE OR REPLACE TRIGGER facturasumaAFTER INSERT ON detalleFOR EACH ROWBEGIN

UPDATE facturaSET total=total+:new.montoWHERE numero=:new.numero ;

END;

Page 44: Capitulo7_ProgramacionSQL

44

¿Preguntas?