Intro sql y plsql

Introducción a SQL y PL/SQL

Orlando Lara Betancourt

Contenido

1. Introducción a Bases de Datos

2. Creación y Manejo de Objetos de Bases de Datos

3. Sentencias Basicas de SQL

4. Manipulación de Datos con SQL

5. Control de Acceso

6. Fundamentos de PL/SQL

7. Estructuras de Control

8. PL/SQL y su interacción con Oracle

9. Manejo de Errores

10.Creación de SubProgramas

Introducción a Bases de Datos

Overview

Conceptos Basicos

Modelo Entidad Relación (MER)

Modelo Relacional de Datos (MRD)

Bases de Datos Oracle

Arquitectura de Bases de Datos Oracle

Conceptos Basicos

Base de Datos: Conjunto de datos estructurados,

almacenados que se consultan a través de

aplicaciones.

Diseño de Bases de datos

– Conceptual: Especificación de la realidad

– Lógico: Descripcion de la estructura de la BD

– Fisico: Implementación de la Estructura

Entidad: Objeto sobre el cual se puede tener información.

Atributo: Caracteristica de la entidad

– Dominio: Conjunto de valores de un atributo

Relación: Asociación entre dos o mas Entidades

– Cardinalidad: Numero de correspondencias.

Identificador: Atributo que identifica una única ocurrencia de la Entidad.

Factura# No FactFechaValor

1:n 1:1

Cliente# CedulaNombreDireccionTelefonoSexo (M/F)

Tiene Pertenece

Estructura Fisica de la Base de Datos

Entidades Tablas: Singular a Plural

Atributos Campos

Identificador Campo Clave

Normalización: Proceso estandar para evitar:

– Redundancia de Información

– Inconsistencia de Información

– Perdida de Información en Cascada

– Anomalias de Inserción

Facturas# No Fact n(4) NNFecha dateValor n(9,2)Cedula n(10) NN

1:n 1:1

Clientes# Cedula n(10) NNNombre v(50) NNDireccion v(80)Telefono v(20)Sexo (M/F) v(1)

Tiene Pertenece

Almacenamiento de Datos

Manipulación de Datos

Consulta de Datos

RDBMS (Relational Database Management System)

Administración de recursos (Hardware) del Sistema.

Garantizar el maximo aprovechamiento de los recursos.

Esta formado por dos Elementos:

– Datos: Conforman la propia BD y son de dos tipos:

– Diccionario de datos: BD de los Objetos y seguridad

– Datos: Datos del Usuario

– Tratamientos: Aplicaciones para el manejo de los datos (SQL Plus, DBA Studio, OEM, Forms, etc)

Aquitectura de la Base de Datos Oracle

DBWnLGWRSMONPMONCKPT

Mandatory Background Processes

Resumen

Conceptos de Bases de Datos

Modelo Entidad Relación

Modelo Relacional de Datos

Caracteristicas de un RDBMS

Arquitectura de la Base de Datos Oracle

Creación y Manejo de Objetos de Bases de Datos

Overview

Principales Objetos de la Base de Datos

Creación de Tablas

Tipos de Datos en Oracle

Alterando Tablas

Instrucciones Drop y Truncate

Creación de constraints

Creación de Vistas

Consultando datos de una vista

Creación y Uso de Secuencias

Creación y Uso de Indices

Creación de Sinonimos

Principales Objetos de la Base de Datos

Tablas

Vistas

Secuencias

Indices

Sinonimos

Procedimientos

Funciones

Paquetes

Creacion de Tablas

Create table Empleados (Codigo number(10) not null primary key,Nombre varchar2(50) not null,Salario number (9,2) default 0,Depto number(4));

Codigo Nombre Salario Depto

Tipos de Datos

Tipo Carácter– CHAR– LONG– NCHAR– NVARCHAR2– VARCHAR2

Tipo Numerico

– NUMBER (L,P)

Tipo Fecha

– DATE

Alterando Tablas

Alter table Empleados Add (Comision number(9,2));

Codigo Nombre Salario Depto Comision

Instrucciones Drop y Truncate

Drop table Empleados;

Elimina la definición de la tabla.

Truncate table Empleados;

Elimina los datos de la tabla

Creación de constraints

ALTER TABLE EMPLEADOS ADD (

CONSTRAINT PK_EMPLEADOS PRIMARY KEY (CODIGO));

ALTER TABLE EMPLEADOS ADD (

CONSTRAINT FK_EMPL_DPTO FOREIGN KEY (DEPTO) REFERENCES DEPTOS (CODIGO));

Creación de Vistas

CREATE OR REPLACE VIEW EMP_DEPTO AS

Select Empleados.Codigo CodEmp,

Empleados.Nombre NomEmp,

Empleados.Salario SalEmp,

Deptos.Nombre Depto

From Empleados,Deptos

Where Empleado.Depto = Deptos.Codigo;

Consultando Datos de una Vista

Select CodEmp,NomEmp,

SalEmp,Depto

From EMP_DEPTO;

CodEmp NomEmp SalEmp Depto

10 Andres Escobar R 10000 Ventas

20 Carlos Velasquez 5000 Planeación

30 Juan Manuel Rivas 20000 Ventas

40 Jose Juaquin Velez 50000 Recursos Humanos

50 Mario Hernandez 1000 Ventas

Creación y Uso de las Secuencias

CREATE SEQUENCE SEQEMPLEADOS

START WITH 1

INCREMENT BY 1

MAXVALUE 1E27

MINVALUE 1;

Creación y Uso de Indices

CREATE UNIQUE INDEX IDX_CODEMP ON EMPLEADOS (CODIGO);

CREATE INDEX IDX_NOMEMP ON

EMPLEADOS (NOMBRE);

Creacion de Sinonimos

CREATE SYNONYM PEGASO.OBSEMULT FOR GAS.OBSEMULT@DESA;

CREATE PUBLIC DATABASE LINK DESA

CONNECT TO DESA

IDENTIFIED BY ud953_f1

USING 'DESA';

CREATE PUBLIC SYNONYM PEGASO.OBSEMULT FOR GAS.OBSEMULT@DESA;

Resumen

Objetos de Bases de Datos

Tablas

Constraints

Vistas

Secuencias

Indices

Sinonimos

Sentencias Basicas de SQL

Overview

Instrucción Select

Limitando la cantidad de registros (Where)

Desplegar datos de multiples tablas (Uso de Joins)

Instrucción Order by

Instrucción Select

Select *

From Empleados;

Empleados#CodigoNombreSalario

Codigo Nombre Salario

10 Andres Escobar R 10000

20 Carlos Velasquez 5000

30 Juan Manuel Rivas 20000

40 Jose Juaquin Velez 50000

50 Mario Hernandez 1000

Limitando la Cantidad de Registros

Select *

From Empleados

Where Salario > 10000;

La clausula Where determina la condición de la consulta

Codigo Nombre Salario

30 Juan Manuel Rivas 20000

40 Jose Juaquin Velez 50000

Desplegar Datos de Multiples Tablas

Codigo Nombre

10 Recursos Humanos

20 Ventas

30 Planeación

Empleados#CodigoNombreSalarioDepto

Deptos#CodigoNombre

1:n 1:1

Tiene Pertenece

Desplegar Datos de Multiples Tablas

Select Empleados.Codigo,

Empleados.Nombre,

Empleados.salario,

Deptos.Nombre Depto

From Empleados,Deptos

Where Empleados.Depto = Deptos.Codigo;

Instrucción Order By

Select Empleados.Codigo,

Empleados.Nombre,

Empleados.Salario,

From Empleados

Order by Empleados.Salario;

Resumen

Instrucciones básicas de SQL para consultar datos.

– Select

– Where

– Alias

– Joins

– Order by

Manipulación de Datos con SQL

Overview

Insertando Datos (Insert)

Actualizando Datos (Update)

Eliminando Información (Delete)

Control de Transacciones (Commit - Rollback)

Insertando Datos (insert)

Insert into Empleados

Values (60,’Jota Mario Valencia’,

3000,10);

60 Jota Mario Valencia 3000 10

Empleados

Insertando Datos (insert)

Insert into Empleados (Codigo,Nombre,Salario,Depto)

Values (60,’Jota Mario Valencia’,

3000,10);

60 Jota Mario Valencia 3000 10

Empleados

Actualizando Datos (update)

Update Empleados

Set Depto = 10;

10 Andres Escobar R 10000 10

20 Carlos Velasquez 5000 10

30 Juan Manuel Rivas 20000 10

40 Jose Juaquin Velez 50000 10

50 Mario Hernandez 1000 10

Eliminado Datos (delete)

Delete Empleados

Where Codigo = 10;

20 Carlos Velasquez 5000 10

30 Juan Manuel Rivas 20000 10

40 Jose Juaquin Velez 50000 10

50 Mario Hernandez 1000 10

Control de Transacciones

Commit

– Commit;

Rollback

– Rollback;

Save Point

– Save Point <Nombre>

Resumen

Instrucciones SQL para manipular datos.

– Insert

– Update

– Delete

– Commit

– Rollback

Control de Acceso

Overview

Conceptos:

– Usuarios

– Privilegios

– Roles

Otorgar (Grant) y Revocar (Revoke) Privilegios a los objetos de la Base de Datos

Conceptos

Usuarios: SYS y SYSTEM

CREATE USER ORLANDOL

IDENTIFIED BY VALUES 'ECE5A72B8C80CBE3’;

Privilegios- Tablas- Vistas- Secuencias- Procedimientos- Funciones- Paquetes

DML (Data Manipulation)DDL (Data Definition)

Labor de DBA

Conceptos

Roles: Facilita la administración

– Roles de Aplicación: Conjunto de privilegios necesarios para ejecutar la Aplicación.

– Roles de Usuario: Se crea un role para un grupo de usuarios con privilegios comunes.

CREATE ROLE ROLGAS;

Otorgar (Grant) y Revocar (Revoke) Accesos a los objetos de la Base de Datos

GRANT DELETE, INSERT, SELECT, UPDATE

ON ACTA TO ROLGAS;

GRANT EXECUTE

ON PROLEGALIZAR TO JUANCM;

REVOKE DELETE, INSERT, SELECT, UPDATE

ON ACTA FROM ROLGAS;

Ejemplo de Uso de Seguridad

Resumen

Usuarios

Privilegios

Manejo de Seguridad de Datos en Oracle

Fundamentos de PL/SQL

Overview

Declaración de Variables

– DEFAULT

– NOT NULL

– %TYPE

– %ROWTYPE

Convenciones de Nombres de variables

Asignación de Valores a las variables

Expresiones y Comparaciones

Funciones del Lenguaje

Declaración de Variables

sbCadena varchar2(50);

nuValor number (9,2);

dtFecha date;

blExiste boolean;

nuCodigo Empleados.Codigo%type;

rtEmp Empleados%rowtype;

nuValor number(9,2) DEFAULT –1;

sbCadena varchar2(50) NOT NULL := ‘CADENA’;

Convenciones de Nombres de Variables

sb Cadena

nu Numerico

dt Fecha

bl Booleano

rt Registro

cu Cursores

cn Constante

Asignación de Valores a las variables

nuValor Empleados.Codigo%type;

nuValor := 25

select Codigo

into nuValor

from Empleados

where Codigo = 25;

Precedencia de Operación

Operaadores Logicos

Operadores de Comparación

IS NULL

– IF Variable IS NULL THEN ...

BETWEEN

– 45 BETWEEN 38 AND 44

IN y NOT IN

– WHERE CODIGO IN (2,3,4)

– WHERE NOT IN (6,7)

CONCATENAR

– ‘suite’||’case’ ‘suitecase’

SELECT DECODE (codigo,NULL,0,10,’A’,20,’B’) VALOR

FROM EMPLEADOS;

DECODE

Expresiones y ComparacionesCASE

select sysdate fecha, to_number(TO_CHAR(sysdate, 'SS')) Minuto, 'Segundos ' || CASE

WHEN (to_number(TO_CHAR(sysdate, 'SS')) IS NULL) THEN 'Nulos'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 0) THEN 'Cero'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 1) THEN 'Uno'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 2) THEN 'Dos'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 3) THEN 'Tres'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 4) THEN 'Cuatro'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 5) THEN 'Cinco'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 6) THEN 'Seis'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 7) THEN 'Siete'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 8) THEN 'Ocho'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 9) THEN 'Nueve'WHEN (to_number(TO_CHAR(sysdate, 'SS')) = 10) THEN 'Diez'WHEN (to_number(TO_CHAR(sysdate, 'SS')) > 10 AND

to_number(TO_CHAR(sysdate, 'SS')) < 31) THEN 'entre 11 y 30'WHEN (to_number(TO_CHAR(sysdate, 'SS')) in

(31,32,33,34,35,36,37,38,39)) THEN 'in (31,32,33,34,35,36,37,38,39)'

ELSE 'entre 40 y 59' END as Opcionfrom dual

Funciones del Lenguaje (Tabla DUAL)

Resumen

Fundamentos básicos de PL/SQL

Declaracion de Variables

Convenciones

Asignación de Valores a Variables

Funciones del Lenguaje

Estructuras de Control

Overview

Condicionales

– IF-THEN

– IF-THEN-ELSE

– IF-THEN-ELSIF

Iteraciones

– LOOP

– WHILE

– FOR

Condicionales

IF – THEN

IF condicion THEN

secuencia de instrucciones

END IF;

Ejemplo:

IF codigo = 5 THEN

nuValor := 50000;

END IF;

Condicionales

IF – THEN – ELSE

IF condicion THEN

secuencia de instrucciones 1;

END IF;

Ejemplo

IF codigo = 5 THEN

nuValor := 50000;

nuValor := 10;

END IF;

Condicionales

IF – THEN – ELSIF

IF condicion1 THEN

ELSIF condicion2 THEN

END IF;

Iteraciones

secuencia de instrucciones;

END LOOP;

EXIT – EXIT WHEN

LOOP LOOP

IF condicion THEN ...

... EXIT WHEN a<5;

EXIT; END LOOP;

END IF;

END LOOP;

Iteraciones

WHILE – LOOP

WHILE condicion LOOP

secuencia de instrucciones

END LOOP;

Ejemplo

WHILE total <=25000 LOOP

total := total + 20;

END LOOP;

Iteraciones

FOR – LOOP

FOR contador IN [REVERSE] desde..hasta LOOP

secuencia de instrucciones;

END LOOP;

Ejemplo:

FOR i IN 1..10 LOOP

total := total + (i*10);

END LOOP;

Resumen

Estructuras de Control

Condicionales

– IF – THEN

– IF – THEN – ELSE

– IF – THEN – ELSIF

Iteraciones

– LOOP

– WHILE

– FOR

PL/SQL y su Interacción con Oracle

Overview

Caracteristicas de PL/SQL

Manejo de Cursores– Implicit– Explicit

Paso de Parametros a los cursores

Variables tipo cursor

Atributos de los cursores– %FOUND– %NOTFOUND– %ISOPEN– %ROWCOUNT

Caracteristicas de PL/SQL

Soporte de SQL

Soporta Programación Orientada a Objetos

Mejor Perfomance

Portable

Integrado con SQL

Manejo de Seguridad

Manejo de Cursores

Explicit Cursor

– Comandos: OPEN, FETCH, CLOSE.

Declaración

DECLARE

CURSOR <Nombre> [(parametro1,parametro2...)] IS

instrucción SELECT...

Manejo de Cursores

Declare

cursor cuEmpleados is

select codigo,nombre

from empleados

where salario > 1000;

nuCodigo Empleados.Codigo%type;

sbNombre Empleados.Nombre%type;

open cuEmpleados;

fetch cuEmpleados into nuCodigo,sbNombre;

close cuEmpleados;

Manejo de Cursores

Implicit Cursor

– No utilizan las instrucciones OPEN, FETCH y CLOSE.

– Utilizan Atributos de los Cursores para determinar el estado del cursor.

Ejemplo:

SELECT codigo

INTO nuValor

FROM Empleados;

Manejo de Cursores

FOR CURSOR

declarecursor cuEmpleados isselect codigo,nombrefrom Empleadoswhere Salario > 1000;

beginFor rtEmp in cuEmpleados loop

...rtEmp.Codigo ...rtEmp.Nombre ...

end loop;end;

Paso de Parametros a Cursores

DECLARE

CURSOR cuEmpleados (nuSalario number) is

SELECT codigo,nombre

FROM Empleados

WHERE Salario > nuSalario;

Variables tipo cursor

DECLARE

CURSOR cuEmpleados is

SELECT *

FROM Empleados;

rtEmp cuEmpleados%rowtype;

OPEN cuEmpleados

FETCH cuEmpleados into rtEmp;

CLOSE cuEmpleados;

Atributos de los Cursores

%FOUND: Es verdadero si se encontraron registros

del cursor.

%NOTFOUND: Es verdadero si NO se encontraron

registros del cursor.

%ISOPEN: Es verdadero si el cursor está abierto.

%ROWCOUNT: Devuleve la cantidad de registros que

retorna el cursor.

Resumen

Uso de SQL y PL/SQL

Uso de los Cursores

– Implicit

– Explicit

FOR CURSOR

Parametros de los cursores

Atributos de los cursores

Manejo de Errores

Overview

Instrucción Exception

User-Defined Exception

Mensajes de Error

En Oracle las Advertencias o Mensajes de Error son llamados Excepciones (Exception).

Permite capturar los errores del

sistema y mantener un control

sobre las aplicaciones.

Pueden ser predefinidas o

creadas por el programador

User Define Exception

Declaración

DECLARE null_salary EXCEPTION;

Inicializa la variable de Error

PRAGMA EXCEPTION_INIT (null_salary,-60);

DECLARE

null_salary EXCEPTION;

PRAGMA EXCEPTION_INIT (null_salary,-60);

... RAISE null_salary;

EXCEPTION

WHEN null_salary THEN

-- Manejo del Error

Mensajes de Error

Utilizacion de Tablas de Mensaje

Funcion que retorne Mensaje Ej: fsbMensaje();

Mensajes Oracle SQLERRM

Mensajes ORA-XX

Resumen

Manejo de errores Exception

Usos de las Excepciones

Mensajes de Error

Creación de SubProgramas y Paquetes

Overview

SubProgramas

– Funciones

– Procedimientos

Paquetes

SubProgramas

CREATE OR REPLACE

FUNCTION nombre (parametro1,parametro2)

return <tipo de dato> IS|AS

declaraciones locales;

-- Instrucciones

EXCEPTION

-- Manejo de Errores

Funciones

SubProgramas

CREATE OR REPLACE

FUNCTION balance (cuenta number) RETURN number IS

-- Instrucciones

return nuValor;

EXCEPTION

Funciones (Uso de RETURN)

SubProgramas

CREATE OR REPLACE

PROCEDURE inserta_tabla IS

-- Instrucciones

EXCEPTION

Procedimientos: Ejecuta Acción especifica

Paquetes

Especificación

Paquetes

Package Body

Resumen

Objetos de Bases de Datos

– Funciones

– Procedimientos

– Paquetes

Intro sql y plsql

Technology

Transcript of Intro sql y plsql

Unidad vi esp parte 2 procesimientos en plsql y transact sql

Tema9-PLSQL Oracle (7)

1415_Apuntes plsql

19482524 Curso de Oracle PLSQL

PLSQL Presentation

2asi Sgbd u08 Plsql Practica14 Lobs

PLSQL y paquetes

Informe Final Seminario Plsql Cuentas Por Cobrar Revisado

Plsql Oracle

Lenguaje PLSQL (ppt)

040628 plsql basico

Clase 2 - Plsql

Optimización de Inyecciones SQLOWASP LATAM TOUR 2012 4 Agenda •SQL Injection (intro) •Blind SQL Injection •“Booleanización” •Algoritmo de búsqueda binaria. •Técnica

Lección 8- PLSQL Fundamentals

Plsql Material

PLSQL Program Units Castellano Completo Sin Anotaciones D21979

Apuntes Oracle y PlSQL

4 Conceptos bßsicos de programaci¾n con PLSQL

Ejercicios Prog PLSQL

Curso SQL Plsql