Introducción a Oracle Database · PDF fileSu tamaño esta determinado por el parámetro DB_BLOCK_SIZE. Extensiones (Extents) Una extensión es el siguiente nivel de almacenamiento

ORACLE

Introducción a Oracle DatabaseOracle Database es el primer servidor de base de datos, diseñado paratrabajar en un entorno grid computing, el modo mas flexible y económico demanejar información y aplicación.

Grid computing: es una tecnología que permite aprovechar los ciclos deprocesamientos no utilizado de las computadoras conectadas a una red.

Permite la integración y el uso colectivo de computadoras de altorendimiento, de redes y de base de datos de diferentes institucionesconectadas a internet.

Oracle base de datos posee estructura física y estructura lógica separadas, elalmacenamiento físico de data puede ser administrado sin afectar el acceso ala estructura de almacenamiento lógico.

Requerimiento de Oracle Database 10g

Requerimiento de hardwareHardware Requerimiento mínimoMemoria física (RAM) 256 MB mínimo. Se recomienda 512 MB.Memoria virtual El doble de la RAM.Espacio de disco Aproximadamente 2 GB dependiendo del

tipo de instalación y de las opciones establecidas.

Adaptador de video 256 colores.Procesador 550 MHz minimo

Requerimiento de softwareSoftware RequerimientoArquitectura del sistema Procesador: Intel (x64), AMD64Sistema operativo Windows 2000 con SP1 o posterior,

todas las ediciones.Windows Server 2003, todas las ediciones.Windows XP profesional.

Protocolo de red TCP/IP

Pagina 1

ORACLE

Tipos de instalación

Enterprise Edition: proporciona administración de datos para el sistema demisión crítica tales como el procesamiento de transacciones en línea (OLTP)de alto volumen, consultas intensivas en entornos de procesamientosanalíticos en línea (OLAP), y las aplicaciones para internet. Esta edición notiene límite en cuanto a la cantidad de procesadores soportados.

Standard Edition: proporciona facilidad de uso, potencial y buena relaciónprecio/rendimiento para aplicaciones de grupo de trabajo, de niveldepartamental, e intranet e internet. Es adecuado para aplicaciones denegocio que van desde pequeños negocios hasta entornos altamentedistribuidos. Soporta un máximo de 4 procesadores en una sola PC, o hasta 4procesadores en un cluster de servidores.

Personal Edition: proporciona compatibilidad con Oracle Enterprise y OracleStandard pero soporta solo un usuario. Puede utilizarse en un sistemaoperativo de escritorio como Windows 98.

Pagina 2

ORACLE

Instalación de Oracle Database 10g Express

Inicio de la instalación del Oracle 10g Express

Pagina 3

ORACLE

Ventana de inicio de instalación, click en el botón Next.

Acepte el contrato de licencia, click en el botón Next.

Pagina 4

ORACLE

Nos pide la ruta donde instalar el programa, si desea puede personalizar, perolo dejaremos por defecto.

Ahora nos requiere ingresar contraseña que se le será asignada aladministrador, en este caso será System, ya que es igual al usuarioadministrador. Como todos los programas nos pide confirmar contraseña, clicen el botón Next.

Pagina 5

ORACLE

Ahora solo procedemos a instalar el software, click en el botón Install.

Esperamos

Pagina 6

ORACLE

Bueno demoro un poco pero ya esta listo, click en el botón Finish.

Pagina 7

ORACLE

Pantalla de inicio.

Como se dará cuenta su interfaz de trabajo es a través de una pagina web,ingresamos nuestro usuario System, su contraseña (es lo que usted ingreso enel momento de la instalación).

Pagina 8

ORACLE

Pantalla de bienvenida

Pagina 9

ORACLE

Cuentas con privilegios administrativos

Cuando se crea la base de datos, se crean automáticamente las siguientescuentas con rol DBA (Database Administrator):

SYS SYSTEM SYSMAN

La contraseña de cada una de ellas se establece al momento de instalarOracle Database.

La cuenta SYSEl esquema SYS contiene todas las tablas y vistas del diccionario de datos (lametadata). Estas tablas y vistas son criticas para el funcionamiento de Oracledatabase, y no deben ser manipuladas directamente por el administrador.Debe asegurarse que los usuarios no tengan acceso con esta cuenta.

La cuenta SYSTEMEl esquema SYSTEM almacena tablas y vistas adicionales creadas por lasherramientas de Oracle con propósito administrativo. El esquema SYSTEM nodebe contener las tablas de los usuarios.

La cuenta SYSMANSe crea por defecto durante la instalación de Oracle Enterprise Manager. Estacuenta debe ser utilizada para realizar tareas poco frecuentes tales comoconfiguración global del sistema. El usuario SYSMAN es un súper administradorque crea otras cuentas para la realización de las tareas administrativasdiarias.

Nota: debe crear por lo menos una cuenta adicional con el rol DBA y concederlos privilegios apropiados para que pueda ejecutar las tareas administrativasdiarias. No utilice las cuentas SYS, SYSTEM y SYSMAN para estos propósitos.

Pagina 10

ORACLE

Arquitectura del servidor OracleCuando se instala Oracle Database se crea una instancia del servidor Oracle.Es posible crear múltiples instancias del servidor en una misma PC, pero no serecomienda ya que cada una consume muchos recursos.

La instancia de OracleA diferencia de otros productos de base de datos en los que por cada instanciadel servidor podemos crear varias bases de datos, por cada instancia deOracle se crea solo una base de datos. Por ejemplo en MS SQL Server creamosen un servidor las bases de datos ventas, producción y personal, en Oraclecreamos una sola base de datos conteniendo los esquemas ventas produccióny personal.

La base de datos de Oracle esta formada por un conjunto de archivos quecontiene data ingresada por los usuarios y las aplicaciones y la informaciónsobre la estructura de la data (la metadata)

Permite verificar la instancia del servidor:

select *from v$instance;

Estructura de memoria de la instancia

Oracle separa parte de la memoria total del sistema para utilizar en susprocesos. Es parte de la memoria que Oracle separa y esta formada por:

SGA – área global del sistemaPGA – área global del programa

SGA: es un grupo de estructuras de memoria compartida que contiene datos einformación de control utilizadas por la instancia de Oracle. Como estamemoria es compartida por todos los procesos, también se le conoce comoárea global compartida.

El SGA contiene la siguiente estructura:

Pagina 11

ORACLE

Estructura DescripciónDatabase BufferCache

La data solicitada por una aplicación para ser consultadao modificada debe ser leída del disco y almacenadapreviamente en la memoria en el buffer cache.

Shared Pool Almacena información que puede ser compartida por losusuarios:Sentencia SQL y PL/SQLInformación del diccionario de datos

Redo Log Buffer Almacena información sobre la data actualizada, y queutilizada por la instancia para actualizar en el momentooportuno los archivos de registro almacenadosfísicamente en el disco.

Large Pool Área opcional utilizada por las solicitudes entrada/salidamuy grandes tales como operaciones de respaldo yrestauración de datos.

Java Pool Utilizada para almacenar código y data especificada asesión Java en JVM (Java Virtual Machine).

Streams Pool Utilizado por el producto Oracle Streams.

Propiedades del SGA

Pagina 12

ORACLE

Podemos ver el tamaño del SGA:

Pagina 13

ORACLE

PGA: es una región de la memoria que contiene datos e información decontrol para un proceso del servidor. Es un área de la memoria no compartidacreada por la instancia de Oracle cuando un proceso de servidor se inicia.El tamaño de las estructuras de memoria de la instancia afecta el rendimientodel servidor y es controlado por los parámetros de inicialización.

Pagina 14

ORACLE

Verificación de los parámetros de instancia

select name,value from v$parameter;

Pagina 15

ORACLE

Estructura física de la base de datos

Esta formada básicamente por los siguientes archivos: los archivos de datos(datafiles), los archivos redo log, y el archivo de control.

Archivo de datos (datafiles) Los datafiles son archivos .DBF en los que se almacenan todos los datosde la base de datos. La base de datos Oracle puede tener uno o másdatafiles.

Un datafile solo puede estar asociado a una base de datos. Un datafile puede ser configurado para que se expanda

automáticamente cuando se refiere espacio adicional para los datos. Uno o más datafiles forman una unidad lógica de almacenamiento

conocida como Tablespaces.

Archivo de control Cada base de datos Oracle tiene un archivo de control (.CTL), el quealmacena la información necesaria para mantener y controlar la integridadde la base de datos.

El nombre de la base de datos. El nombre de ubicación de los datafiles y los archivos redo log. Fecha y hora de la creación de base de datos.

Pagina 16

ORACLE

Archivos Redo Log Los archivos Redo Log (.LOG) almacenan las modificaciones de los datosejecutadas en la base de datos con la finalidad de recuperarlas en casoque produjera una falla en el sistema. Archivos archive Log Los archivos Archive Log son copias fuera de línea de los archivos RedoLog. Se usan para recuperar la base de datos en caso de falla del medio dealmacenamiento. Archivos de parámetros Un archivo de parámetros contiene parámetros y valores que definen lascaracterísticas de la instancia y de la base de datos; por ejemplo contieneparámetros que dimensionan el SGA.

Verificación de los datafiles

select * from v$datafile;

Pagina 17

ORACLE

Estructura lógica de la base de datos

Las estructuras de almacenamiento lógico de Oracle tales como lostablespaces, bloque de datos, extents y segmentos le permiten al servidor uncontrol muy fino sobre el uso de espacio de disco.

Tablespaces Una base de datos se divide en unidades lógicas de almacenamientodenominadas tablespaces. Un tablespaces permite agrupar estructura lógicasque están relacionas entre si como por ejemplo todos los objetos de unaaplicación para simplificar algunas tareas administrativas. Oracle almacena la data lógicamente en los tablespaces físicamente en losdatafiles asociados al tablespace correspondientemente.

Pagina 18

ORACLE

Las bases de datos, los tablespaces, y los datafiles están íntimamenterelacionados aunque hay importantes diferencia entre ellos.

Una base de datos Oracle consiste de una o mas unidades de almacenamiento lógico llamadas tablespace, las que almacén toda la data de la base de datos.

Cada tablespace consiste de uno o mas archivos denominados datafiles, loscuales son las estructuras físicas que es concordante con el sistemaoperativo en el que Oracle se esta ejecutando. La data de la base de datos es almacena en los datafiles que constituye cadauno de los tablespace de la base de datos. La base de datos de Oracle mássimple debe estar formada por una tablespace y un datafiles.

Verificación de los tablespace

select *from v$tablespace;

Pagina 19

ORACLE

Bloque de datos (Data Blocks) La data de una base de datos Oracle es organizada en unidades lógicasllamadas Data Blocks. Es una unidad más pequeña de almacenamiento dedatos. Su tamaño esta determinado por el parámetro DB_BLOCK_SIZE.

Extensiones (Extents) Una extensión es el siguiente nivel de almacenamiento lógico de losdatos. Un extents esta formado por un conjunto de Data Blocks contiguo. Eltamaño predeterminado de un extents es de 1 MB.

Segmento (Segment) Por encima de los extens, se encuentra el segmento, que es unconjunto de extens localizados para determina estructura lógica. La siguiente figura muestra la relación entre tablespaces, segmentos,extents y data blocks.

Pagina 20

ORACLE

Estructura lógica de la base de datosTablespaces

Una base de datos se divide en unidades lógicas de almacenamientodenominadas tablespaces. Un tablespaces permite agrupar estructura lógicasque están relacionas entre si como por ejemplo todos los objetos de unaaplicación para simplificar algunas tareas administrativas.

Oracle almacena la data lógicamente en los tablespaces físicamente en los datafiles asociados al tablespace correspondientemente.

La instrucción CREATE TABLESPACE

Sintaxis

CREATE TABLESPACE nombre_tablespaceDATAFILE ‘ubicación_y_nombre_archivo_datafile’SIZE tamaño [ K|M ]EXTENT MANAGEMENT

DICTIONARY | LOCAL[ AUTOALLOCATE | SIZE tamaño [ K|M ] ]

nombre_tablespace: es el identificador de la estructura lógica.

ubicación_y_nombre_archivo_datafile: es una cadena queincluye la ruta y el nombre del archivo. La ruta debe especificar unacarpeta existente en el servidor en el que esta instalado Oracle.

Tamaño: especifica el tamaño del archivo.

EXTENT MANAGEMENT: especifica si los extents seránadministrados por el diccionario de datos (DICTIONARY), o localmente(LOCAL).

El tamaño de los extents puede ser manejados por el servidor (opciónAUTOALLOCATE), o podemos definir extents de tamaño uniforme(opción SIZE)

Pagina 21

ORACLE

Creación de un tablespace

CREATE TABLESPACE TS_INTDATAFILE ‘C:\oraclexe\oradata\XE\TS_INT.ORA’SIZE 3MEXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Para verificar la creación ejecute el siguiente script.

SELECT NAME FROM V$TABLESPACE WHERE NAME =’TS_INT’;

Pagina 22

ORACLE

Introducción a los esquemasUn esquema es una colección de objetos de la base de datos. El esquema espropiedad de un usuario de la base de datos, y tiene el mismo nombre que suusuario propietario. Los objetos del esquema, tales como tablas, vistas eíndices, son las estructuras lógicas referidas directamente a la data de la basede datos. Creación de un esquema

CREATE USER INSTITUTO IDENTIFIED BY DBA;

En la instrucción CREATE USER, INSTITUTO es el identificador del usuario, yDBA es su contraseña.

Concesión de privilegios

Para el usuario INSTITUTO pueda iniciar la sesión y crear los objetos de suesquema necesita contar con lo privilegios adecuados.

GRANT CONNECT TO INSTITUTO;

Instrucción para asignar el rol CONNECT al usuario INSTITUTO.Ahora conéctese con el usuario.

Pagina 23

ORACLE

Tipo de datosCada constante y variable tiene un tipo de dato en el cual se especifica el formato de almacenamiento, restricciones y rango de valores validos.

PL/SQL proporciona una variedad predefinida de tipos de datos. Casi todos lostipos de datos manejados por PL/SQL son similares a los soportados por SQL. Acontinuación se muestran os tipos de datos mas comunes.

NUMBER (Numérico): Almacena números enteros o de punto flotante,virtualmente de cualquier longitud, aunque puede ser especificada laprecisión (Número de dígitos) y la escala que es la que determina el númerode decimales.

CHAR (Caracter): Almacena datos de tipo carácter con una longitud máximade 32767 y cuyo valor de longitud por default es 1.

VARCHAR2 (Caracter de longitud variable): Almacena datos de tipo carácterempleando sólo la cantidad necesaria aún cuando la longitud máxima seamayor.

BOOLEAN (lógico): Se emplea para almacenar valores TRUE o FALSE.

DATE (Fecha): Almacena datos de tipo fecha. Las fechas se almacenaninternamente como datos numéricos, por lo que es posible realizaroperaciones aritméticas con ellas. Atributos de tipo. Un atributo de tipo PL/SQL es un modificador que puede serusado para obtener información de un objeto de la base de datos. El atributo%TYPE permite conocer el tipo de una variable, constante o campo de la basede datos. El atributo %ROWTYPE permite obtener los tipos de todos loscampos de una tabla de la base de datos, de una vista o de un cursor.

PL/SQL también permite la creación de tipos personalizados (registros) ycolecciones (tablas de PL/SQL), que veremos en sus apartadoscorrespondientes.

Pagina 24

ORACLE

Existen por supuesto más tipos de datos, la siguiente tabla los muestra:

Tipo de dato / Sintáxis

Oracle 8i Oracle 9i Descripción

dec(p, e) La precisión máxima es de 38 dígitos.

La precisión máxima es de 38 dígitos.

Donde p es la precisión y e la escala.

Por ejemplo: dec(3,1) es un número que tiene2 dígitos antes del decimal y un dígito después deldecimal.

decimal(p, e) La precisión máxima es de 38 dígitos.



Por ejemplo: decimal (3,1) es un número que tiene 2 dígitos antes del decimaly un dígito después del decimal.

doubleprecisionfloatintintegernumeric(p, e) La precisión

máxima es de 38 dígitos.



Por ejemplo: numeric(7,2) es un número que tiene 5 dígitos antes del decimaly 2 dígitos después del decimal.

Pagina 25

ORACLE



number(p, e) La precisión máxima es de 38 dígitos.



Por ejemplo: number(7,2) es un número que tiene 5 dígitos antes del decimaly 2 dígitos después del decimal.

realsmallintchar (tamaño) Hasta 32767

bytes en PLSQL.

Hasta 2000 bytes en Oracle 8i.

Hasta 32767 bytes en PLSQL.


Donde tamaño es el número de caracteres a almacenar. Son cadenas de anchofijo. Se rellena con espacios.

varchar2 (tamaño)

Hasta 32767 bytes en PLSQL


Hasta 32767 bytes en PLSQL


Donde tamaño es el número de caracteres a almacenar. Son cadenas de anchovariable.

long Hasta 2 gigabytes.

Hasta 2 gigabytes.

Son cadenas de ancho variable.

raw Hasta 32767 bytes en PLSQL.


Hasta 32767 bytes en PLSQL.


Son cadenas binarias de ancho variable.

long raw Hasta 2 gigabytes.

Hasta 2 gigabytes.

Son cadenas binarias de ancho variable.

Pagina 26

ORACLE



date Una fecha entre el 1 de Enero de 4712 A.C. y el 31 de Diciembre de 9999 D.C.

Una fecha entre el 1 de Enero de 4712 A.C. y el 31 de Diciembre de 9999 D.C.

timestamp (fractional seconds precision)

No soportado por Oracle 8i.

.fractional seconds precision debe ser un número entre 0 y 9. (El valor por defecto es 6)

Incluye año, mes día, hora, minutos y segundos.

Por ejemplo: timestamp(6)

timestamp (fractional seconds precision) with time zone


fractional seconds precision debe ser un número entre 0 y 9. (El valor por defecto es 6)

Incluye año, mes día, hora, minutos y segundos; con un valor de desplazamiento de zona horaria.

Por ejemplo: timestamp(5) with time zone

timestamp (fractional seconds precision) with local time zone



Incluye año, mes día, hora, minutos y segundos; con una zona horaria expresada como la zona horaria actual.

Por ejemplo: timestamp(4) with local time zone

interval year (year precision) to month


year precision debe ser un número entre 0 y 9. (El valor por defecto es 2)

Período de tiempo almacenado en años y meses.

Por ejemplo: interval year(4) to month

Pagina 27

ORACLE

Tipo de dato /Sintáxis


interval day (day precision) to second (fractional seconds precision)


day precision debe ser un número entre 0 y 9. (El valor por defecto es 2)


Incluye año, mes día, hora, minutos y segundos.

Por ejemplo: interval day(2) to second(6)

rowid El formato del campo rowid es:

BBBBBBB.RRRR.FFFFF donde BBBBBBB es el bloque en el fichero de la basede datos; RRRR esla fila del bloque;FFFFF es el fichero de la basede datos.

El formato del campo rowid es:

BBBBBBB.RRRR.FFFFFdonde BBBBBBB es elbloque en el fichero de la base de datos; RRRR es la fila del bloque; FFFFF es el fichero de la base dedatos.

Datos binarios de ancho fijo. Cada registro de la base de datos tiene unadirección física o rowid.

urowid [tamaño]

Hasta 2000 bytes. Hasta 2000 bytes. Rowid universal. Donde tamaño es opcional.

boolean Válido en PLSQL, este tipo de datosno existe en Oracle 8i.

Válido en PLSQL, este tipo de datos noexiste en Oracle 9i.

nchar (tamaño) Hasta 32767 bytesen PLSQL. Hasta 2000 bytes en Oracle 8i.

Hasta 32767 bytes enPLSQL. Hasta 2000 bytes en Oracle 9i.

Donde tamaño es el número de caracteres aalmacenar. Cadena NLS de ancho fijo.

nvarchar2 (tamaño)

Hasta 32767 bytesen PLSQL. Hasta 4000 bytes en Oracle 8i.

Hasta 32767 bytes enPLSQL. Hasta 4000 bytes en Oracle 9i.

Donde tamaño es el número de caracteres aalmacenar. Cadena NLS de ancho variable.

Pagina 28

ORACLE

Tipo de dato /Sintáxis


bfile Hasta 4 gigabytes.

Hasta 4 gigabytes. Localizadores de archivo apuntan a un objeto binario de sólo lectura fuera de la base de datos.

blob Hasta 4 gigabytes.

Hasta 4 gigabytes. Localizadores LOB apuntan a un gran objeto binario dentro de la base de datos.

clob Hasta 4 gigabytes.

Hasta 4 gigabytes. Localizadores LOB apuntan a un gran objeto de caracteres dentro de la base de datos.

nclob Hasta 4 gigabytes.

Hasta 4 gigabytes. Localizadores LOB apuntan a un gran objeto NLS de caracteres dentro de la base de datos.

Pagina 29

ORACLE

Creación de tablasLa instrucción CREATE TABLE

Sintaxis

CREATE TABLE nombre_tabla(Nombre_columna1 tipo_dato1 (NULL | NOT NULL),Nombre_columna1 tipo_dato2 (NULL | NOT NULL),Nombre_columna1 tipo_dato3 (NULL | NOT NULL)

Especificación de NULL o NOT NULL

NULL: columna que puede ser nulo de datos.

NOT NULL: columna obligado a ingresar datos.

Creación de tablas

En el esquema INSTITUTO crear la tabla especialidad asociada al tablespaceTS_INT.

CREATE TABLE especialidad(id_especialidad NUMBER(3) NOT NULL,nombre_especialidad VARCHAR2(40))TABLESPACE TS_INT;

Usted recibe un mensaje de error, el cual indica que usted no cuenta con losprivilegios necesarios para crear las tablas.

Concesión al esquema INSTITUTO del privilegio para crear las tablas

Conéctese con el usuario SYSTEM.

Ejecute el siguiente script.

GRANT RESOURCE TO INSTITUTO;

El rol RESOURCE concede privilegios para crear ciertos objetos de unesquema, entre ellos las tablas.

Pagina 30

ORACLE

Conéctese con el usuario INSTITUTO

Vuelva a ejecutar el script.

CREATE TABLE especialidad(id_especialidad NUMBER(3) NOT NULL,nombre_especialidad VARCHAR2(40))TABLESPACE TS_INT;

Verificación la creación de la tabla.

SELECT *FROM CAT;

Verificar la definición de la tabla.

DESCRIBE especialidad;

Laboratorio 01:

1. Crear las siguientes tablas.

2. Verifique la creación de las tablas.

3. Verificar la definición de las tablas.

Pagina 31

ORACLE

Modificando la definición de la tabla.

En una tabla podemos añadir nuevas columnas, eliminar columnas, cambiar las propiedades de las columnas, añadir o eliminar restricciones.

La instrucción ALTER TABLE

Sintaxis

ALTER TABLE nombre_tablaADD nombre_columna propiedades_columna

| DROP COLUMN nombre_columna| ALTER COLUMN nombre_columna

nuevas_propiedades_columna| ADD CONSTRAINT nombre_restriccion

PRIMARY KEY | UNIQUE | FOREIGN KEY | DEFAULT | CHECK | DROP CONSTRAINT nombre_restriccion

ADD nombre_columna: permite añadir nueva columna a la tabla.

DROP COLUMN: se usa para eliminar una columna.

ALTER COLUMN: permite modificar la definición de una columna.

ADD CONSTRAINT: permite añadir una restricción PRIMARY KEY, UNIQUE FOREIGN KEY, DEFAULT o CHECK a la definición de una tabla.

DROP CONSTRAINT: se usa para eliminar una restricción.

Para ver el efecto de la instrucción ALTER TABLE, en el esquema INSTITUTO crearemos una tabla.

CREATE TABLE tabla_prueba(columna1 NUMBER(3) NOT NULL,columna2 VARCHAR2(10))TABLESPACE TS_INT;

Añadir una columna de tipo NUMBER(8,2) con la propiedad NULL.

ALTER TABLE tabla_pruebaADD columna3 NUMBER(8,2) NULL;

Pagina 32

ORACLE

Adición de una columna NOT NULL a una tabla.

Insertamos una fila en la tabla tabla_prueba

INSERT INTO tabla_prueba VALUES(101,’CARLOS’,30.45);

Ahora tratemos de añadir una columna de tipo VARCHA2 con la propiedad NOTNULL.

ALTER TABLE tabla_pruebaADD columna4 VARCHAR2(20) NOT NULL;

Usted recibe un mensaje de error, el cual indica que solo puede agregarcolumnas con la propiedad NOT NULL cuando la tabla esta vacía.

Añadiendo la columna con la propiedad NULL

ALTER TABLE tabla_pruebaADD columna4 VARCHAR2(20) NULL;

Ahora, establecemos cual es el valor para la nueva columna de la fila que ya esta registrada en la tabla.

UPDATE tabla_pruebaSET columna4=’LIMA’WHERE columna1=’101’;

Ahora cambiamos la propiedad NULL de dicha columna a NOT NULL.

ALTER TABLE tabla_pruebaMODIFY (columna4 VARCHAR2(20) NOT NULL);

Pagina 33

ORACLE

RestriccionesSon un método declarativo de definición de la integridad de datos ya que ellasse definen al momento de crear la tabla con la sentencia (CREATE TABLE) o almomento de modificar de tabla con la sentencia (ALTER TABLE).

Tipos de restricciones

Las restricciones son un método estándar ANSI para forzar la integridad de losdatos. A continuación los tipos de restricciones.

tipos de restricciones descripciónPRIMARY KEY(clave primaria)

garantiza que cada fila o registro en una tabla sea único, no permite valores nulos.

UNIQUE(valor no duplicado)

garantiza que cada valor en una columna es único. permite valores únicos.

FOREIGN KEY(clave foránea)

define la columna o combinación de columnas de una tabla secundaria cuyos valores dependende la clave primaria de una tabla primaria.

DEFAULT(valor predeterminado)

establece el valor predeterminado para una columna.

CHECK(regla de validación)

establece la regla que debe cumplir un valor para que se un valor aceptable en una columna.

Creación de la clave primaria (PK)

Sintaxis

ALTER TABLE nombre_tablaADD CONSTRAINT pk_nombre_tablaPRIMARY KEY (columnaP, columnaX)

pk_nombre_tabla: es el nombre de la restricción clave primaria.

columnaP, columnaX: es la columna o combinación de columnas que se define como clave primaria.

Creación de clave primaria de la tabla curso.

ALTER TABLE cursoADD CONSTRAINT PK_cursoPRIMARY KEY(id_curso);

Pagina 34

ORACLE

Creación de clave foránea (FK).

Sintaxis

ALTER TABLE nombre_tablaADD CONSTRAINT fk_nombre_tabla_tabla_referenciadaFOREIGN KEY (columnaP, columnaX) REFERENCES tabla_referenciada

fk_nombre_tabla_tabla_referenciada: es el nombre de larestricción clave foranea.

columnaP, columnaX: es la columna o combinación de columnasque se define como clave foránea.

tabla_referenciada: es el nombre de la tabla primaria con la que serelaciona la tabla secundaria que tiene la clave foránea.

Creación de la clave foránea en la tabla notas.

ALTER TABLE notasADD CONSTRAINT FK_notas_cursoFOREIGN KEY (id_curso) REFERENCES curso;

Creación de la restricción valor no duplicado (UNIQUE).

Sintaxis

ALTER TABLE nombre_tablaADD CONSTRAINT u_nombre_tabla_nombre_columnaUNIQUE (columnaP, columnaX)

u_nombre_tabla_nombre_columna: es el nombre de la restricción valor no duplicado o UNIQUE.

columnaP, columnaX: es la columna o combinación de columnas ala que se aplica la restricción.

Pagina 35

ORACLE

Creación de restricción UNIQUE para la columna nombre_curso en la tabla curso.

ALTER TABLE cursoADD CONSTRAINT U_curso_nombre_cursoUNIQUE (nombre_curso);

Creación de valor predeterminado (DEFAULT)

Sintaxis

ALTER TABLE nombre_tablaMODIFY (columnaX DEFAULT valor_predeterminado)

valor_predeterminado: es el valor que se almacena en la columnas cuando inserta una fila no se especificada el valor para esa columna.

columnaX: es la columna que se aplica la restricción.

Creación de una restricción DEFUALT para la columna credito_curso en la tabla curso.

Antes vamos a insertar un registro en la tabla curso

INSERT INTO curso VALUES (501,’JAVA’,30);

Ahora procedemos a crear la restricción.

ALTER TABLE cursoMODIFY (credito_curso DEFAULT 30);

Ahora inserte un registro

INSERT INTO curso (id_curso,nombre_curso)VALUES (501,’JAVA’);

Pagina 36

ORACLE

Creación de regla de validación (CHECK)

Sintaxis

ALTER TABLE nombre_tablaADD CONSTRAINT ck_nombre_tabla_nombre_columnaCHECK (condición)

ck_nombre_tabla_nombre_columna: es el nombre de la restricción regla de validación o check.

condición: es la expresión que determina como debe ser el valor a ingresar en la columna afectada por la restricción.

Creación de la restricción CHECK para la columna credito_curso de la tabla curso.

ALTER TABLE cursoADD CONSTRAINT CK_curso_credito_cursoCHECK (credito_curso > 0);

Eliminación de tablas (la instrucción DROP TABLE)

Elimina la definición de una tabla.

Si la tabla referencial por una clave foránea, no se podrá eliminar.

Eliminación de tablas

DROP TABLE tabla_prueba;

Pagina 37

ORACLE

Laboratorio 02:

1. Establecer clave principal y relacione las siguientes tablas.

2. Inserte 2 registros en la tabla especialidad, registros a la tabla curso, 3 registros en la tabla alumno y 3 registros a la tabla notas.

3. Agregar la columna (telefono_alumno varchar2 (10)) a la tabla alumno.

4. Establecer restricción a la columna (credito_curso) de la tabla curso, elcredito del curso no puede ser mayor a 120.

5. Mostrar la fecha del sistema.

6. Verificar los tablespace creados y escribirlos.

7. Verificar todos los objetos del catalogo de esquema INSTITUTO.

Pagina 38

ORACLE

Mantenimiento de datosUna vez de haber creado las tablas de la base de datos se debe cargar la datalo que normalmente se lleva a cabo desde una aplicación cliente que contieneformularios para que el usuario puede cargar los datos con facilidad ycomodidad.

Inserción de filas (instrucción INSERT)

Sintaxis

INSERT INTO nombre_tabla (lista_columnas) VALUES (lista_de_ valores)

lista_columnas: es la relación de columnas en las que se almacenaran losvalores especificados.

lista_de_valores: es la relación de valores a almacenar en la fila ainsertar.

Los elementos de ambas listas van separados.

Inserción de una fila con lista de valores completa

INSERT INTO curso VALUES (502,’VISUAL STUDIO .NET’,20);

Inserción de una fila con lista de valores incompleta

INSERT INTO curso (id_curso,nombre_curso) VALUES (503,POWER BUILDER’);

Objeto sequence

Pagina 39

ORACLE

Es un objeto de base de datos que genera números secuenciales y se utilizapara asignar valores a campos numéricos enteros en los que se requiere que lageneración de su contenido sea automática y secuencial.

Sintaxis

CREATE SECUENCE nombre_secuencia [ STAR WITH valor_inicial ] [ INCREMENT BY incremento ] [ MAXVALUE valor_maximo | NOMAXVALUE ] [ MINVALUE valor_minimo | NOMINVALUE ] [ CYCLE | NOCYCLE ]

Valor_inicial: valor que empieza la secuencia. Incremento: establece la diferencia entre un valor de la secuencia y

el siguiente si no específica se asume 1. Valor_maximo: mayor valor de la secuencia que puede alcanzar.

Valor_minimo: menor valor en la secuencia.

Cycle: permite que la secuencia continué a partir de MINVALUEcuando se alcanza MAXVALUE.

Nocycle: modo predeterminado.

Creando una nueva tabla, y creando un objeto sequence.

CREATE TABLE tabla_secuencia(codigo NUMBER(4) PRIMARY KEY NOT NULL, nombre VARCHAR2(20) NOT NULL) TABLESPACE TS_INT;

CREATE SEQUENCE sq_codigoMAXVALUE 9999;

Utilizamos el objeto secuencia sq_codigo para insertar datos.

INSERT INTO tabla_secuencia VALUES(sq_codigo.NEXTVAL,’CARLOS EDUARDO’);

Pagina 40

ORACLE

Actualización de datos (instrucción UPDATE)

Sintaxis

UPDATE nombre_tablaSET columnaX = expresionX, columnaP = expresionP, …WHERE condicion_fila_actualizar

columnaX, columnaP: son las columnas cuyo contenido se actualizará.

expresionX, expresionP: establecen los nuevos valores a almacenar en las columnas.

condicion_fila_actualizar: es una expresión lógica que determina que las filas en las que la actualización se debe llevar acabo.

Uso del UPDATE

Antes de del uso del UPDATE, ejecute la siguiente instrucción.

SELECT *FROM curso;

Ejecute la siguiente instrucción

UPDATE cursoSET credito_curso=25WHERE id_curso=502;

Pagina 41

ORACLE

Eliminación de filas (instrucción DELETE)

Sintaxis

DELETE FROM nombre_tablaWHERE condicion_fila_eliminar

condicion_fila_eliminar: es una expresión lógica que determina las filasen las que la eliminación se debe llevar acabo.

Uso del DELETE

Antes de del uso del DELETE, ejecute la siguiente instrucción.

SELECT *FROM curso;

Elimine el curso POWER BUILDER

DELETE FROM cursoWHERE id_curso=503;

Si desea eliminar todos los registros.

DELETE FROM curso;

Eliminación de todas las filas de una tabla (instrucción TRUNCATE TABLE)

La instrucción TRUNCATE TABLE se ejecuta más rápido debido a que noregistra la eliminación de cada fila en el log de transacciones como lo haceDELETE.

TRUNCATE TABLE nombre_tabla

Eliminar todas las filas de la tabla curso

TRUNCATE TABLE curso;

Pagina 42

ORACLE

Fundamentos de Oracle - SQLEs la implementación de Oracle del estándar ANSI SQL que define elementosdel lenguaje SQL que puede ejecutarse desde cualquier aplicación frontal.

Elementos de Oracle - SQL

DML: Data Manipulation Languague (consultar y modificar datos)DDL: Data Definition Languague (crear objetos de la base de datos)DCL: Data Control Languague (quien modifica los datos)

Elementos adicionales al lenguaje: variables, operadores, funciones, sentencia de control de flujo y comentarios.

Declaración DMLTrabaja con los datos de la base de datos

instrucción descripciónSELECT selecciona filas y columnas de una o más tablas de la base

de datos.INSERT añade una nueva fila a una tabla.UPDATE modifica los datos existentes en una tabla.DELETE elimina filas de una tabla.

Declaración DDLPermite crear base de datos, tablas, y tipos de datos definidos por el usuario.

instrucción DescripciónCREATE nombre_objeto

crea un objeto de la base de datos.

ALTER nombre_objeto

modifica la definición del objeto de la base de datos.

DROP nombre_objeto

elimina un objeto de la base de datos.

Declaración DCLPermite cambiar los permisos o roles asociado con un usuario de la base dedatos.

instrucción descripciónGRANT crea una entrada en la seguridad del sistema que le

permite a un usuario trabajar con los datos o ejecutar ciertas sentencias.

DENY crea una entrada de seguridad del sistema negando un permiso de una cuenta de seguridad.

REVOKE quita un permiso previamente concebido o negado

Pagina 43

ORACLE

Operadores

La siguiente tabla ilustra los operadores de Oracle.

tipo de operador operadoresOperador de asignación := (dos puntos + igual)Operadores aritméticos + (suma)

- (resta) * (multiplicación)/ (división) ** (exponente)

Operadores relacionales o de comparación

= (igual a) <> (distinto de) < (menor que) > (mayor que) >= (mayor o igual a) <= (menor o igual a)

Operadores lógicos AND (y lógico)NOT (negación)OR (o lógico)

Operador de concatenación ||

Pagina 44

ORACLE

Laboratorio 03:

1. Crear un tablespace de nombre TS_INT.

2. Crear un esquema de nombre INSTITUTO.

3. Crear las siguientes tablas dentro del esquema (INSTITUTO):Establecer clave principal a cada tabla y relacionarlas.

4. En la tabla notas la nota no debe sobre pasar más de 20.

5. Insertando los siguientes registros:

Tabla: ESPECIALIDAD

Tabla: CURSO

Tabla: ALUMNO

Pagina 45

ORACLE

Tabla: NOTAS

6. Cambie el crédito del curso a 150 para los cursos LIBRO CAJA y POWERBUILDER.

7. Mostrar los alumnos que tenga la especialidad de COMPUTACION EINFORMACTICA.

8. Mostar las notas de los alumnos que lleven el curso POWER BUILDER.

9. Aumentar 2 puntos en el EXAMEN PARCIAL, al alumno JOSE ANTONIOROMAN LEIVA.

10.Mostar el promedio global de los alumnos que lleven el curso deVISUAL STUDIO.NET.

Pagina 46

ORACLE

11.Mostrar el promedio global mas bajo.

12.Mostrar los datos del alumno MARIA ISABEL LOZADA ROJAS de tal formaque se vea en un solo cuadro.

13.Cambie el curso de VISUAL STUDIO.NET a JAVA CREATOR.

14.Mostrar los cursos quetenga el crédito del curso mayor a 100.

15.Restar con 5 puntos en el EXAMEN FINAL a los alumnos que lleven elcurso de LIBRO CAJA.

16.Insertarun nuevo alumno que tenga la especialidad de COMPUTACION EINFORMATICA.

Pagina 47

ORACLE

17.Mostrar los alumnos que tenga en el promedio global más 15.

18.Insertar 5 nuevos alumnos, 3 de ellos para la especialidad deCONTABILIDAD Y FINANZAS y los otros 2 para la otra especialidad.

19.Ingresar notas a los alumnos que no tengan, los alumnos deCOMPUTACION E INFORMATICA lleva el curso de JAVA CREATOR.

20.Mostrar el promedio global más bajo que lleven el curso de JAVACREATOR.

Pagina 48

ORACLE

21.Aumentar con 3puntos más alas notas delEXAMENPARCIAL que

estén bajo o igual a 13.

22.Mostrar los cursos que tenga en CREDITO DE CURSO menor a 100

23.Cambie el curso de LIBRO CAJA a LIBRO MAYOR.

24.Mostar todos los alumnos que tenga la especialidad de CONTABILIDAD YFINANZAS.

25.Mostrar los alumnos que lleven el curso de JAVA CREATOR y que supromedio global sea mayor igual a 17.

Pagina 49

ORACLE

26.Cambie el curso de COSTOS Y PRESUPUESTOS a ECONOMIA Y FINANZAS.

27.Restar con 1 punto en el EXAMEN FINAL a los alumnos que lleven elcurso de LIBRO MAYOR.

28.Mostrar los alumnos que tenga nota de promedio global 15 o 17.

29.Mostrar las notas del alumno CARMEN ROSARIO SOLIS ALVAREZ.

30.Restar con 12 puntos en el EXAMEN FNAL a los alumnos que lleven elcurso de POWER BUILDER.

Pagina 50

ORACLE

Preguntas de investigación

31.Mostrar la fecha del sistema incrementado 5 días.

32.Mostar la hora del sistema.

33.Mostrar la condición APROBADO o DESAPROBADO dependiendo el promedio global.

34.Mostrar su edad actual

35.Genere el proceso de operación para el promedio global.

Pagina 51

ORACLE

Consultas simplesAntes de empezar, resuelva el laboratorio 04

Laboratorio 04:

1. Crear un tablespace de nombre TS_COM.

2. Crear un esquema de nombre COMERCIAL.

3. Crear las siguientes tablas dentro del esquema (COMERCIAL):Establecer clave principal a cada tabla y relacionarlas.

Pagina 52

ORACLE

E-Mail: [email protected] Pagina 53

ORACLE

La instrucción SELECT

Hay tres componentes básicos en la instrucción SELECT: SELECT FROM yWHERE.

Sintaxis

SELECT * | lista_columnasFROM nombre_tablaWHERE condicion_filas

lista_columnas: es la lista de las columnas a mostrar en el resultado de laconsulta. Si se especifica * se mostraran todas las columnas de la tabla.

condicion_filas: es una expresión lógica que indica que las filas a mostrarson aquellas para las que el valor de la expresión es verdadero.

Sintaxis con cláusulas más utilizadas

SELECT ALL |DISTINCT lista_columnasINTO nueva_tabla FROM tabla_origenWHERE condicion_filasGROUP BY ALL expresión_agrupar_por ,HAVING condicion_gruposORDER BY nombre_columa ASC | DESC.

Lectura de todos los datos.

SELECT *FROM PERSONAL;

Pagina 54

ORACLE

Lectura de columnas seleccionadas.

SELECT COD_CLI,APE_CLI,NOM_CLI,TEL_CLI FROM CLIENTE;

Definiendo alias para los nombres de las columnas.

SELECT COD_PER AS ID,APE_PER AS APELLIDOS,NOM_PER AS NOMBRES FROM PERSONAL;

Definiendo columnas computadas.

SELECT COD_PRO,NOM_PRO,COD_LIN,PRE_PRO,(PRE_PRO - (PRE_PRO * 0.05)) AS "PRECIO CON DSCTO"FROM PRODUCTO;

Pagina 55

ORACLE Machanay Sulca, Carlos E.

Concatenando cadena.

SELECT COD_CLI,NOM_CLI || ' ' || APE_CLI AS CLIENTE,DIR_CLI FROM CLIENTE;


ORACLE Machanay Sulca, Carlos E.

Definición de filtros

En la cláusula WHERE, condicion_filas es una expresión lógica que establecela condición que deben cumplir las filas a mostrar el resultado de la consulta.Para construir la expresión lógica utilice operadores relacionales o decomparación y de operadores lógicos SQL como LIKE, BETWEEN e IN.

Uso del operador igual (=)

SELECT COD_PER,APE_PER,NOM_PER,COD_DIS FROM PERSONALWHERE COD_DIS='D03';

Uso del operador diferente (<> !=)

SELECT COD_CAR,NOM_CAR FROM CARGOWHERE COD_CAR <> 'C003';

SELECT COD_CAR,NOM_CAR FROM CARGOWHERE COD_CAR != 'C003';

Uso del operador menor que (<)

SELECT COD_PRO,NOM_PRO,COD_LIN,COD_MAR,PRE_PRO,STO_ACT FROM PRODUCTOWHERE STO_ACT < 15;


ORACLE

Uso del operador mayor que (>)

SELECT COD_PRO,NOM_PRO,COD_LIN,COD_MAR,PRE_PRO,STO_ACT FROM PRODUCTOWHERE PRE_PRO > 3100;

Uso del operador menor o igual que (< =)

SELECT COD_CLI,NOM_CLI,APE_CLI,DIR_CLI FROM CLIENTEWHERE APE_CLI <='LOAYZA MARENGO';

Uso del operador mayor o igual que (> =)

SELECT COD_CLI,NOM_CLI,APE_CLI,DIR_CLI FROM CLIENTEWHERE APE_CLI >='SALAZAR RUIZ';

Pagina 58

ORACLE

Manipulación de datos tipo Fecha y Hora.

Fecha del sistema

SELECT SYSDATE FROM DUAL;

Uso de la función TO_CHAR con datos tipo fecha

TO_CHAR(expresión_fecha,’patron_formato_fecha’)

Convierte la expresión_fecha a una cadena con el formato de fechaespecificado en patron_formato_fecha.

expresión_fecha: representa la fecha que se desea convertir a cadena conformato fecha.

patron_formato_fecha: especifica el formato de la cadena que representa una fecha.

Mostrar solo fecha

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') AS FECHA FROM DUAL;

Mostrar solo hora

SELECT TO_CHAR(SYSDATE,'HH:MI:SS') AS HORA FROM DUAL;

Pagina 59

ORACLE

Búsqueda basada en cadena de caracteres

Operador LIKE

Sintaxis

SELECT * | FROM lista_columnasFROM nombre_tablaWHERE columna LIKE expresión_cadena_a_buscar

Comodín del operador LIKE.

Comodín Descripción% indica que en la posición del comodín puede ir cualquier

cadena de caracteres, incluso una cadena nula.- indica que en la posición del comodín puede ir cualquier

cadena de caracteres, no nulo.

Uso del comodín (%)

Permite generar la busqueda por el incio del campo.

SELECT COD_CLI,NOM_CLI,APE_CLI,DIR_CLI FROM CLIENTEWHERE APE_CLI LIKE 'PAL%';

Permite generar la búsqueda por el final del campo.

SELECT COD_CLI,NOM_CLI,APE_CLI,DIR_CLI FROM CLIENTEWHERE APE_CLI LIKE '%IVA';

Pagina 60

ORACLE

Permite generar la búsqueda por cualquier lado del campo.

SELECT COD_CLI,NOM_CLI,APE_CLI,DIR_CLI FROM CLIENTEWHERE NOM_CLI LIKE '%MARIA%';

Uso del comodín ( _ )

SELECT COD_PRO,NOM_PRO FROM PRODUCTOWHERE NOM_PRO LIKE '%43_';

Búsqueda basada en rango

El operador BETWEEN permite ejecutar consultas que ejecutan búsquedasbasadas en rango de valores.

Sintaxis

SELECT * | lista_columnasFROM nombre_tablaWHERE columna BETWEEN valor_inicial AND valor_final

between indica que el valor en columna debe encontrarse en el rango definido por valor_inicial y valor _final.

columna: es la columna en la que se busca según el rango especificado por valor_inicial y valor_final.

valor_inicial, valor_final: establecen los límites del rango de valores en el que se basa la búsqueda.

Pagina 61

ORACLE

Búsqueda basada en rango de valores numéricos.

SELECT COD_PRO,NOM_PRO,PRE_PRO,STO_ACT FROM PRODUCTOWHERE STO_ACT BETWEEN 10 AND 14;

Búsqueda basada en rango de valores cadena.

SELECT COD_PRO,NOM_PRO,PRE_PRO,STO_ACT FROM PRODUCTOWHERE NOM_PRO BETWEEN 'CO' AND 'EX'ORDER BY NOM_PRO;

Búsqueda basada en rango de valores fecha.

SELECT TOTAL,NUM_COMP FROM COMPROBANTEWHERE FECHA_EMISION BETWEEN '02/02/2009' AND

'03/03/2009';

Pagina 62

ORACLE

Búsqueda basada en conjunto de valores.

El operador IN permite ejecutar consultas que ejecutan búsquedas basadas enconjunto de valores.

Sintaxis

SELECT * | lista_columnasFROM nombre_tablaWHERE columna [NOT] IN (conjunto_de_valores)

Busqueda en conjunto de valores

SELECT COD_PRO,NOM_PRO,PRE_PRO,STO_ACT FROM PRODUCTOWHERE COD_PRO IN('P0000010','P0000080','P0000041');

Manipulación de valores NULL

Un valor NULL indica que el valor es desconocido, no aplicable, o quesimplemente se registrara posteriormente. Un valor NULL es distinto a unacadena vacía o al valor cero (0), y también es distinto a cualquier otro valorNULL.

La comparación u operación entre dos valores NULL, o entre un valor NULL ycualquier otro valor retorna un valor desconocido (otro valor NULL), ya quecada valor NULL es desconocido.

Antes de ejecutar un valor NULL, ejecute la siguiente sentencia.

UPDATE PRODUCTOSET STO_ACT=''WHERE COD_PRO IN('P0000001','P0000005');

Pagina 63

ORACLE

Ahora muestre los valores NULL.

SELECT COD_PRO,NOM_PRO,PRE_PRO FROM PRODUCTOWHERE STO_ACT IS NULL;

Calculo con valores de columnas que contienen valores NULL.

SELECT COD_PRO,NOM_PRO,PRE_PRO, STO_MIN,STO_ACT,STO_MIN + STO_ACT AS STOCK FROM PRODUCTOWHERE COD_PRO IN('P0000001','P0000005');

Calculo con valores de columnas que contienen valores NULL – uso de la función NVL

Sintaxis

NVL (expresión_a_verificar, valor_de_remplazo)

expresión_a_verificar: es una expresión cuyo valor puede ser NULL.

valor_de_remplazo: es el valor con que se remplaza expresión_a_verificar en el caso que esta fuera NULL.

Ejecutando la consulta empleando la función NVL.

SELECT COD_PRO,NOM_PRO,PRE_PRO, STO_MIN,NVL(STO_ACT,0),STO_MIN + NVL(STO_ACT,0) AS STOCK FROM PRODUCTOWHERE COD_PRO IN('P0000001','P0000005');

Pagina 64

ORACLE

Pagina 65

ORACLE

Laboratorio 05:

Utilizando el esquema COMERCIAL.

1. Mostrar todos productos que sean de tipo DIGITAL.

2. Mostrar todos los clientes que al final de su nombre termine con RIA.

3. Mostrar a todo el personal que al inicio de su apellido empiece con C.

Pagina 66

ORACLE

4. Mostrar todos los productos que tenga en stock de 10 a 15 unidades.

5. Mostrar todos los productos que empiece con la letra T, y que tenga en stock de 1 a 15 unidades.

6. Mostrar todos los productos de la línea ESCRITORIO, que tenga en stock menos de 50 unidades y el precio este entre 300 y 1000.

7. Mostrar todos los productos que empiece con la letra TV y que sean de la marca MIRAY.

Pagina 67

ORACLE

8. Mostrar todas las líneas que terminen con la letra O.

9. Mostrar todos los productos que utilicen GAS.

10.Mostrar todos los clientes que tenga los siguientes códigos: C0000001, C0000018, C0000020.

Preguntas de investigación.

11.Mostrar todos los clientes que al inicio de sus datos sea mayúscula, y que tenga la letra Z.

12.Elevar a la potencia 5 ^ 2.

Pagina 68

ORACLE

Funciones ORACLEFunciones numérica

Las funciones numéricas ejecutan un cálculo en base a valores de entradarecibidos como parámetros retornando como valor numérico.

MOD (expresionNumerica1, expresionNumerica2)Retorna el residuo de la división.

Ejemplo

SELECT MOD(11,2) FROM DUAL;

POWER (expresionNumerica, n)Retorna el resultado de elevar expresionNumerica a la potencia n.

Ejemplo

SELECT POWER(4,3) FROM DUAL;

ROUND (expresionNumerica, presicion)Retorna el valor de expresionNumerica redondeando a la presicion especificada.

Ejemplo

SELECT ROUND(230.5879,0)FROM DUAL; 231SELECT ROUND(230.5879,1)FROM DUAL; 230.6SELECT ROUND(230.5879,2)FROM DUAL; 230.59SELECT ROUND(230.5879,3)FROM DUAL; 230.588SELECT ROUND(230.5879,4)FROM DUAL; 230.5879

Pagina 69

ORACLE

SQRT (expresionNumerica)Retorna la raiz cuadrada de expresionNumerica.

Ejemplo

SELECT SQRT(9)FROM DUAL;

TRUNC (expresionNumerica, [n])Trunc espresionNumerica a n lugares decimales.

Ejemplo

SELECT TRUNC(3457.5252,2) FROM DUAL;

Funciones Cadena

Ejecutan operaciones sobre una cadena ingresada como parámetro, ydevuelven una cadena o valor numérico o información sobre el juego decaracteres.

CONCAT (expresionCadena1, expresionCadena2)Retorna expresionCadena1 concatenando con expresionCadena2.

Ejemplo

SELECT CONCAT('ALIANZA LIMA ', 'CAMPEON 2009' ) AS EQUIPO FROM DUAL;

INITCAP (expresionCadena)Retorna expresionCadena con la inicial de cada palabra mayúscula.

Ejemplo

SELECT INITCAP('carlos eduardo machanay sulca') AS CLIENTE FROM DUAL;

Pagina 70

ORACLE

LENGTH (expresionCadena)Retorna longitud de expresionCadena.

Ejemplo

SELECT LENGTH('ALIANZA LIMA')AS EQUIPO FROM DUAL;

LOWER (expresionCadena)Retorna expresionCadena, pero todo en minúscula.

Ejemplo

SELECT LOWER(‘JAVA’) AS SOFTWARE FROM DUAL;

LTRIM (expresionCadena1 [,expresionCadena2])Elimina los espacios en blanco por izquierda.

Ejemplo

SELECT LTRIM(' ORACLE Y JAVA')AS SOFTWARE FROM DUAL;

REPLACE (expresionCadena1, expresionCadena2, expresionCadena3)Remplaza en expresionCadena1 todas las ocurrencias de expresionCadena2con expresionCadena3.

Ejemplo

SELECT REPLACE('EL MEJOR EQUIPO DEL PERU UNIVERSITARIO ' ,'UNIVERSITARIO','ALIANZA LIMA') AS EQUIPO FROM DUAL;

Pagina 71

ORACLE

RTRIM (expresionCadena1 [,expresionCadena2])Elimina los espacios en blanco por derecha.

Ejemplo

SELECT RTRIM('PL/SQL DEVELOPER ') AS MANUAL FROM DUAL;

SELECT RTRIM('PL/SQL DEVELOPER','DEVELOPER') AS MANUAL FROM DUAL;

SUBSTR (expresionCadena, inicio, n)A partir de expresionCadena retorna una subcadena de n caracteres delongitud generada a partir de la posición inicio.

Ejemplo

SELECT SUBSTR('ALIANZA LIMA CAMPEON 2009',1,12) AS EQUIPO FROM DUAL;

UPPER (expresionCadena)Retorna expresionCadena, pero todo en mayúscula.

Ejemplo

SELECT UPPER('oracle developer') AS MANUAL FROM DUAL;

Pagina 72

ORACLE

Funciones Fecha y Hora

Las funciones de fecha y hora operan sobre valores de fecha (DATE).

ADD_MONTHS (expresionFecha,n)Incrementa expresionFecha en n meses y genera una nueva fecha.

Ejemplo

SELECT ADD_MONTHS(SYSDATE,4)AS FECHA FROM DUAL;

EXTRACT (parteFecha, from expresionFecha)Entrega a partir de expresionFecha, la parteFecha especificada. parteFecha puede ser una de las siguientes:

YEARMONTHDAYHOURMINUTESECOND

Ejemplo

SELECT EXTRACT(YEAR FROM SYSDATE) AS AÑO FROM DUAL;

LAST_DAY (expresionFecha)Entrega la fecha del último día del mes especificado:

Ejemplo

SELECT LAST_DAY(SYSDATE) AS FECHA FROM DUAL;

Pagina 73

ORACLE

NEXT_DAY (expresionFecha, diaSemana)Entrega la fecha posterior más cercana a expresionFecha y que cae en el diaSemana especificado.

Ejemplo

SELECT NEXT_DAY('25/08/2009','VIERNES') AS FECHA FROM DUAL;

ROUND (expresionFecha, Unidad)Entrega la nueva fecha que es el resultado de redondear expresionFecha a la unidad especificada.

Ejemplo

SELECT ROUND(TO_DATE('25/02/2009'),'YEAR') AS FECHA FROM DUAL;

TO_CHAR (expresionFecha, formato [,idioma]) )Convierte expresionFecha a una cadena con el formato e idioma especificados.

Ejemplo

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY')AS FECHA FROM DUAL;

Pagina 74

ORACLE

Elementos para formato

Elemento DescripciónDL formato de fecha largo.

DS formato de fecha corto.

DAY nombre del dia de la semana.

DY nombre abreviado del dia de la semana.

D dia de la semana(1 - 7)

DD dia del mes (1 - 31)

DDD dia del año (1 - 365)

MONTH nombre del mes.

MON nombre abreviado del mes.

MM mes del año (01 - 12)

YYYY año con 4 dígitos.

YY año con 2 digitos.

HH hora del dia (1 - 12)

HH24 hora del dia (0 - 23)

MI minutos (0 - 59)

SS segundos (0 – 59)

TS formato de hora.

TO_DATE (‘cadenaFormatoFecha’ [,formato] [,formato]) Entrega un tipo Fecha-Hora a partir de ‘cadenaFormatoFecha’ usando elformato e idioma especificados.

Ejemplo

SELECT TO_DATE('25/08/2009','DD/MM/YYYY')AS FECHA FROM DUAL;

Pagina 75

ORACLE

Funciones de conversión de tipos

CAST (expresión as tipoDato) Convierte el valor de la expresión al tipo especificado en tipoDato.

Ejemplo

SELECT CAST(SYSDATE AS CHAR(10))AS FECHA FROM DUAL;

Otras funciones

NVL (expresión1, expresión2) Si expresión1 es NULL, la funcion retorna expresión2.

NVL2 (expresión1, expresión2, expresión3) Si expresión1 no es NULL, la funcion retorna expresión2, Si expresión1 es NULL, la funcion retorna expresión3 .

USER Entrega nombre del usuario en la sesión actual.

Pagina 76

ORACLE

Laboratorio 06:

1. Resuelva la siguiente operación.

2. Agregar un trimestre a la fecha del sistema.

3. Redondear el siguiente número a 2 decimales 563.256

4. Mostrar el mes actual en texto.

5. Calcular su edad actual.

Pagina 77

ORACLE

Funciones de agregación y agrupamiento de datosFunciones de agregación

Son funciones que permite efectuar una operación aritmética que resume losvalores de una columna de toda la tabla o que resume los valores de unacolumna agrupados según determinado criterio.

La función produce un solo valor que es el resumen de la tabla, o de cada unade los grupos.

Función DescripciónAVG() Retorna el promedio de los valores de una columna o

expresión.COUNT() Retorna la cuenta del número de filas de una consulta.

MAX() Retorna el valor máximo de una columna o expresión.

MIN() Retorna el valor mínimo de una columna o expresión.

SUM() Retorna la suma de los valores de una columna o expresión.

Puede usar las funciones de agregación con la declaración SELECT o en combinación con la cláusula GROUP BY.


Uso de la función AVG()

Sintaxis

AVG ( [DISTINCT ] Expresión_Numerica)

DISTINCT indica que debe eliminarse los valores duplicados deExpresión_Numerica antes de evaluar la función.

Ejemplo: Obtener el precio promedio de los productos.

SELECT AVG(PRE_PRO) AS PRECIO FROM PRODUCTO;

Pagina 78

ORACLE

Ejemplo: Obtener el precio promedio de los productos de la línea AUDIO.

SELECT AVG(PRE_PRO) AS PRECIO FROM PRODUCTOWHERE COD_LIN='L008';

Ejemplo: Obtener el stock promedio de los productos cuyo precio sea mayora 600.

SELECT AVG(STO_ACT)AS STOCK FROM PRODUCTOWHERE PRE_PRO > 600;

Uso de la función COUNT()

Sintaxis

COUNT ( [DISTINCT ] Expresión)COUNT ( * )

DISTINCT indica que debe eliminarse los valores duplicados de la Expresiónantes de evaluar la función.

COUNT (Expresion) Ignora los valores NULL de expresión; COUNT (*) se utilizapara contar filas, por lo que no ignora los valores NULL.

Ejemplo: Cuenta los productos registrados.

SELECT COUNT(*) AS PRODUCTO FROM PRODUCTO;

Uso de las funciones MAX () Y MIN ()

Pagina 79

ORACLE

Sintaxis

MAX (Expresión)MIN (Expresión)

Expresión: indica los valores para los que se debe hallar el máximo ymínimo valor.

Ejemplo: Stock mas alto y stock mas bajo.

SELECT MAX(STO_ACT) AS "STOCK MAXIMO", MIN(STO_ACT) AS "STOCK MINIMO" FROM PRODUCTO;

Ejemplo: Nombre del primer producto y del ultimo producto.

SELECT MIN(NOM_PRO)AS "PRODUCTO MIN" ,MAX(NOM_PRO) AS "PRODUCTO MAX" FROM PRODUCTO;

Uso de la función SUM ()

Pagina 80

ORACLE

Sintaxis

SUM ( [DISTINCT ] Expresión)

DISTINCT indica que debe eliminarse los valores duplicados de la Expresiónantes de evaluar la función.

Ejemplo: Stock total de los productos.

SELECT SUM(STO_ACT)AS "TOTAL STOCK" FROM PRODUCTO;

Ejemplo: Stock total de los productos que son de la línea AUDIO.

SELECT SUM(STO_ACT)AS "TOTAL STOCK" FROM PRODUCTOWHERE COD_LIN='L008';

Pagina 81

ORACLE

Cláusula GROUP BY

Se utiliza para agrupar las filas en base a determinado criterio, y luegoejecutar una operación que resume un atributo para cada uno de los gruposasí formados.

Sintaxis

SELECT lista_columnas, funcion_agregacion(columna), funcion_agregacion(columna),

FROM n_tabla [ WHERE condicion_filas ]GROUP BY lista_columnas[ HAVING condicion_grupos ]

Las columnas presentes en lista_columnas de la clausula GROUP BY debennecesariamente estar presentes en la lista_columnas de SELECT.

Cualquier columna presente en SELECT, y que no se encuentre en lalista_columnas de GROUP BY debe estar afectada por una funciónde agregación.

condicion_grupos en la clausula HAVING permite establecer unaexpresión logica que indica que los grupos a mostrar en el resultado sonaquellos para los que el valor de la expresión es verdadero.

Una consulta GROUP BY solo entrega una fila por cada grupogenerado. Esta fila muestra el resultado de la funcion_agregacionaplicada sobre el grupo. No muestra el contenido del grupo.

Cuando se utiliza GROUP BY sobre una columna que contiene valoresNULL, estos se procesan como grupo.

Pagina 82

ORACLE

Ejemplo: Cantidad de productos registrados por cada línea.

SELECT COD_LIN AS LINEA,COUNT(COD_PRO)AS PRODUCTO FROM PRODUCTOGROUP BY COD_LIN;

Cláusula GROUP BY con el operador CUBE

Resume los valores de grupos. El operador CUBE se usa normalmente paraproducir promedios acumulados o sumas acumuladas.

Ejemplo: GROUP BY agrupa las filas de la tabla PRODUCTO en base a lacombinación de los valores de las columnas, COD_LIN y para cada una de lascombinaciones obtenidas calcula el promedio de columna PRE_PRO.

SELECT COD_LIN AS LINEA,AVG(PRE_PRO)AS "PRECIO PROMEDIO" FROM PRODUCTOGROUP BY CUBE(COD_LIN)ORDER BY COD_LIN;

Pagina 83

ORACLE

Uso de GROUP BY con el operador GROUPING SETS

Permite que una sola consulta GROUP BY se presente grupos formados pordistintas combinaciones de atributos.

Uso del operador GROUPING SETS

Este ejemplo muestra la suma acumulada del STO_ACT para trescombinaciones de atributos.

(COD_LIN, COD_PRV, PRE_PRO) (COD_LIN, PRE_PRO) (COD_PRV, PRE_PRO)

SELECT COD_LIN,COD_PRV,PRE_PRO,SUM(STO_ACT) AS "STOCK ACTUAL" FROM PRODUCTOGROUP BY GROUPING SETS( (COD_LIN,COD_PRV,PRE_PRO), (COD_LIN,PRE_PRO), (COD_PRV,PRE_PRO)) ORDER BY COD_LIN,COD_PRV,PRE_PRO;

Pagina 84

ORACLE

Uso de la condición HAVING ()

Cuando ejecutamos una consulta GROUP BY es posible establecer un filtro degrupos utilizando la cláusula HAVING.

Ejemplo: GROUP BY agrupa las filas de la tabla PRODUCTO en base a lacombinación de los valores de las columnas, COD_LIN y COD_MAR, y paracada una de las combinaciones obtenidas calcula el promedio de columnaPRE_PRO. Luego, filtra los grupos para mostrar solo aquellos definidos.

SELECT COD_LIN AS LINEA,COD_MAR AS MARCA,AVG(PRE_PRO)AS"PRECIO PROMEDIO" FROM PRODUCTOGROUP BY CUBE(COD_LIN,COD_MAR)HAVING COD_LIN='L005';ORDER BY COD_LIN,COD_MAR;

Pagina 85

ORACLE

Laboratorio 07:


1. Mostrar el precio promedio de todos productos que sean de tipoDIGITAL.

2. Incrementar el código del cliente más 1.

3. Mostrar el total del stock de los productos que pertenecen a la líneaESCRITORIO.

4. Mostrar el total del stock de los productos por línea que sea menor a150 unidades.

5. Mostrar el precio total de las líneas de productos que estén entre 1000a 6000 soles.

Pagina 86

ORACLE

6. Mostrar los productos que se repitan y que cantidad

7. Mostrar el precio máximo a la raíz cuadrada, mostrando solo dosdecimales.

8. Mostrar el total de stock por cada línea de producto, si el stock esmayor a 200 unidades muestra un mensaje en el siguiente campoSTOCK SUFICIENTE de lo contrario STOCK INSUFICIENTE.

9. Mostrar la cantidad de los productos más 1.

10.Mostrar el precio mínimo, el stock máximo y la cantidad de productos.

11.Mostrar los productos, donde realice cálculo del precio por cantidad (5unidades) me da el importe, donde muestre los productos cocina y queel importe sea mayor a 3000 soles y que sea de la marca INDURAMA.

Pagina 87

ORACLE

Consultas multitablasConsultas correlacionadas

Un Join, combinación o consulta correlacionada es la consulta que seleccionacolumnas de dos tablas o conjunto de filas, y las entrega en un único conjuntode resultados. Las filas de las tablas o conjunto de filas se combinanrelacionando valores comunes, típicamente de clave primaria y clave foránea.

Sintaxis

SELECT lista_columnasFROM tabla1tipo_join JOIN tabla2 ON condicion_del_join

lista_columnas es la lista de columnas a mostrar en el resultado de laconsulta. Se recomienda que cada columna se calificada con alias de latabla a la cual pertenece.

tipo_join indica si el join es interior (INNER), exterior (OUTER) oirrestricto (CROSS).

condicion_del_join es una expresión que indica en base a quecolumnas de cada una de las tablas se establece la relación entre ellas.

Una combinación (join) puede ser cualquiera de los siguientes tipos.

INNER JOIN OUTER JOIN

LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN

CROSS JOIN

INNER JOIN.

Un INNER JOIN es la consulta correlacionada que combina todas las filas queestán relacionadas de las dos tablas o conjunto de filas.

Pagina 88

ORACLE

Uso del INNER JOIN.

Mostrar los nombres de los distritos de la tabla cliente que vivan en LIMA.

SELECT CLIENTE.COD_CLI AS CODIGO,CLIENTE.RAZON_SOCIAL,CLIENTE.RUC_CLI AS RUC,DISTRITO.NOM_DIS AS DISTRITO FROM CLIENTEINNER JOIN DISTRITO ON CLIENTE.COD_DIS=DISTRITO.COD_DISWHERE DISTRITO.NOM_DIS='LIMA';

Usando el alias como referencia a las tablas.

SELECT C.COD_CLI AS CODIGO,C.RAZON_SOCIAL,C.RUC_CLI AS RUC,D.NOM_DIS AS DISTRITO FROM CLIENTE CINNER JOIN DISTRITO D ON C.COD_DIS=D.COD_DISWHERE D.NOM_DIS='LIMA';

Pagina 89

ORACLE

Uso del INNER JOIN con 3 tablas.

Mostrar los datos de la tabla comprobante, que se ha el numero CP00000004, que tipo de comprobante es, así como el importe.

SELECT COMPROBANTE.NUM_COMP AS NUMERO,TIPOCOMPROBANTE.NOM_TIC AS COMPROBANTE,COMPROBANTE.FECHA_EMISION AS FECHA,SUM(DETALLE.PRECIO * DETALLE.CANT) AS IMPORTEFROM COMPROBANTE INNER JOIN DETALLE ON COMPROBANTE.NUM_COMP=DETALLE.NUM_COMPINNER JOIN TIPOCOMPROBANTE ON COMPROBANTE.COD_TIC=TIPOCOMPROBANTE.COD_TICGROUP BY COMPROBANTE.NUM_COMP,TIPOCOMPROBANTE.NOM_TIC,COMPROBANTE.FECHA_EMISIONHAVING COMPROBANTE.NUM_COMP='CP00000004';

Uso del OUTER JOIN

Un OUTER JOIN es la consulta correlacionada que entrega todas las filasque están relacionadas, y además.

Las filas no relacionadas de la tabla izquierda (LEFT OUTER JOIN)Las filas no relacionadas de la tabla derecha (RIGHT OUTER JOIN)Las filas no relacionadas de ambas tablas (FULL OUTER JOIN)

Se considera como la tabla izquierda, a aquella que se menciona primero enla cláusula FROM.

Pagina 90

ORACLE

Mostrar los productos que han sido vendidos.

SELECT PRODUCTO.COD_PRO AS CODIGO,PRODUCTO.NOM_PRO AS PRODUCTO,DETALLE.CANT FROM PRODUCTOLEFT OUTER JOIN DETALLE ON PRODUCTO.COD_PRO=DETALLE.COD_PROWHERE NOT DETALLE.CANT IS NULL;

Uso de CROSS JOIN

Es una consulta correlacionada que combina cada una de la filas de una de lastablas con cada una de las filas de la otra tabla.

No es necesario que exista una columna en común para ejecutar un CROSSJOIN. Esta consulta también se le conoce como el producto cartesiano dedos tablas.

Pagina 91

ORACLE

SELECT MARCA.COD_MAR,MARCA.NOM_MAR,PRODUCTO.COD_PRO,PRODUCTO.NOM_PRO FROM MARCACROSS JOIN PRODUCTO;

Operador UNION

Sintaxis

Sentencia_SELECT_1 UNION [ALL]

Sentencia_SELECT_2UNION [ALL]

Sentencia_SELECT_3

El operador UNION une los resultados de dos o más instrucciones SELECT enun solo conjunto de resultados.

Use el operador UNION cuando los datos que desea recuperar residen endiferentes localizaciones y no puede acceder a ellos con una sola consulta.Cuando use el operador UNION considere los siguiente.

SQL Server requiere que las consultas a las tablas referenciadas tengael mismo número de columnas, los mismos tipos de datos, y que lascolumnas se encuentren en el mismo orden en la lista de cada uno delos SELECT.

Pagina 92

ORACLE

SQL Server elimina las filas duplicadas en el resultado. Sin embargo, siusa la opción ALL, todas las filas (incluso las duplicadas) son incluidasen el resultado.

Debe especificar los nombres de las columnas en la primera instrucciónSELECT. Por consiguiente, si quiere definir los nuevos títulos de lascolumnas para el resultado, debe crear los seudónimos de las columnasen la primera instrucción SELECT.

Si quiere que el resultado completo sea devuelto en un ordenespecifico, debe especificar el orden e incluir la cláusula ORDER BYdentro de la sentencia UNION.

Uso del operador UNION

SELECT COD_PRO,PRE_PRO FROM PRODUCTOUNIONSELECT COD_PRO,PRECIO FROM DETALLE;

Pagina 93

ORACLE

Para explicar como trabaja el operador UNION ALL y además los operadores INTERSECT y MINUS, vamos a crear dos pequeñas tablas con algunos datos.

Crear la tabla OPERARIOS

CREATE TABLE OPERARIOS(IDOPERARIO VARCHAR2(4),NOMBRE VARCHAR2(15));

Insertando 5 registros

INSERT INTO OPERARIOS VALUES('0967','ANTONIO');INSERT INTO OPERARIOS VALUES('0245','NANCY');INSERT INTO OPERARIOS VALUES('0376','CARLOS');INSERT INTO OPERARIOS VALUES('0879','ROSA');INSERT INTO OPERARIOS VALUES('0147','MARCOS');

Crear la tabla SUPERVISORES

CREATE TABLE SUPERVISORES(IDSUPERVISOR VARCHAR2(4),NOMBRE VARCHAR2(15));

Insertando 2 registros

INSERT INTO SUPERVISORES VALUES('0376','CARLOS');INSERT INTO SUPERVISORES VALUES('0713','GUMERCINDO');

Ejecución de UNION

SELECT IDSUPERVISOR AS EMPLEADO,NOMBRE FROM SUPERVISORESUNIONSELECT IDOPERARIO,NOMBRE FROM OPERARIOS;

Pagina 94

ORACLE

Ejecución de UNION ALL

SELECT IDSUPERVISOR AS EMPLEADO,NOMBRE FROM SUPERVISORESUNION ALLSELECT IDOPERARIO,NOMBRE FROM OPERARIOS;

Uso del operador INTERSECT

El operador INTERSECT, al igual que el operador UNION, permite procesarfilas de dos consultas. INTERSECT entrega las filas que son comunes a lasdos consultas.

SELECT IDSUPERVISOR AS EMPLEADO,NOMBRE FROM SUPERVISORESINTERSECTSELECT IDOPERARIO,NOMBRE FROM OPERARIOS;

Pagina 95

ORACLE

Uso del operador MINUS

El operador MINUS construye una relación de filas formado por las filas que aparecen en el primer conjunto, pero que no aparecen en el segundo conjunto.

SELECT IDOPERARIO AS EMPLEADO,NOMBRE FROM OPERARIOSMINUSSELECT IDSUPERVISOR,NOMBRE

FROM SUPERVISORES;

Consulta AUTOJOIN

Es una consulta correlacionada en la que una tabla se combina consigo mismapara generar un nuevo conjunto de resultado.

Para generar un ejemplo crearemos una tabla que tenga autorelacion. Estatabla contiene clave foránea que apunta a la clave primaria en la mismatabla.

Crear la tabla TRABAJADOR, establecer clave primaria.

CREATE TABLE TRABAJADOR (IDTRABAJADOR NUMBER PRIMARY KEY, APELLIDOS VARCHAR2(30)NOT NULL, JEFE NUMBER NULL); Estableciendo clave foránea.

ALTER TABLE TRABAJADOR ADD CONSTRAINT FK_TRABAJADOR_TRABAJADOR FOREIGN KEY(JEFE) REFERENCES TRABAJADOR;

Pagina 96

ORACLE

Insertando 7 registros.

INSERT INTO TRABAJADOR VALUES(102,'ARDILES SOTO',NULL);INSERT INTO TRABAJADOR VALUES(101,'CAMACHO SARAVIA',102);INSERT INTO TRABAJADOR VALUES(105,'VILCHEZ SANTOS',102);INSERT INTO TRABAJADOR VALUES(103,'SANCHEZ ALIAGA',101);INSERT INTO TRABAJADOR VALUES(104,'CASTRO AVILA',101);INSERT INTO TRABAJADOR VALUES(107,'URRUNAGA TAPIA',101);INSERT INTO TRABAJADOR VALUES(106,'JUAREZ PINTO',105);

Ejemplo: crear una consulta que muestre una lista de trabajadores. La listadebe mostrar apellidos del jefe de cada trabajador.

SELECT T1.IDTRABAJADOR,T1.APELLIDOS,T2.APELLIDOS AS JEFE FROM TRABAJADOR T1 INNER JOIN TRABAJADOR T2 ON T1.JEFE= T2.IDTRABAJADOR;

Modifique la consulta para quetambién se muestre al

trabajador 102.

SELECT T1.IDTRABAJADOR,T1.APELLIDOS,T2.APELLIDOS AS JEFEFROM TRABAJADOR T1 LEFT OUTER JOIN TRABAJADOR T2ON t1.jefe= t2.idtrabajador;

Pagina 97

ORACLE

Subconsultas

Una subconsulta es una declaración SELECT anidada dentro de una sentenciaSELECT, INSERT, UPDATE o DELETE o dentro de otra subconsulta.

Las consultas son de los tipos siguientes:

Subconsulta que entrega un solo valor (1 fila, 1 columna)Subconsulta que entrega un conjunto de valores (varias filas, 1 columna)

Una subconsulta se especifica entre paréntesis.

Test de pertenencia

SELECT COD_PRO,NOM_PRO FROM PRODUCTOWHERE COD_PRO NOT IN(SELECT COD_PRO FROM DETALLE)ORDER BY COD_PRO;

Pagina 98

ORACLE

Test de existencia – uso de EXISTS

Productos que han sido vendidos.

SELECT PRODUCTO.COD_PRO, PRODUCTO.NOM_PROFROM PRODUCTOWHERE EXISTS(SELECT *FROM DETALLE WHERE PRODUCTO.COD_PRO=DETALLE.COD_PRO);

Pagina 99

ORACLE

Inserción de filas con datos leídos por SELECT.

Sintaxis

INSERT [INTO] tabla_destinoSELECT lista_columnas

FROM tabla_origen[WHERE condicion_filas_tabla_origen]

Inserción de filas.

Antes de empezar, crearemos una tabla.

CREATE TABLE TABLA_PRUEBA (DISTRITO VARCHAR2(60)); Ahora insertaremos los datos de la tabla distrito a la tabla tabla_prueba.

INSERT INTO TABLA_PRUEBA SELECT NOM_DIS FROM DISTRITO;

Ahora revisemos la tabla TABLA_PRUEBA.

SELECT *FROM TABLA_PRUEBA;

Pagina 100

ORACLE

Laboratorio 08:


1. Mostrar los productos que sean de la línea MUSICA.

2. Mostar el cargo del personal, su nombre del personal que emitióel comprobante numero CP00000001.

3. Mostrar los productos que su proveedor se a del país de MEXICO.

4. Mostrar el proveedor de los productos vendidos.

Pagina 101

ORACLE

5. Mostrar todos los clientes que todavía no han generado alguna compra.

6. Mostrar el total de los importes vendidos de la línea MUSICA.

7. Mostrar los productos procedentes del país de MEXICO y que no sean dela marca SAMSUNG.

8. Mostrar el total redondeado de las compras por cada cliente.

9. Mostrar el total del precio por cada línea, que sea mayor a los 4000.

Pagina 102

ORACLE

10.Mostrar que tipo de comprobante, número del día, nombre del mes que han sido emitidos.

11.Mostrar el código del producto más 1.

12.Mostrar los productos vendidos que sean de la línea VIDEO.

13.Mostrar los comprobantes emitidos de los clientes que sean del distrito de LIMA y que su razón social se a S.A.

Pagina 103

ORACLE

14.Mostrar los productos vendidos, el stock actual, el precio por unidad, y el precio de todo el stock actual.

15.Mostrar el personal donde su teléfono no empiece con el número 3 y que sea del distrito de ATE.

16.Mostrar los productos que en el penúltimo carácter sea 3 y que sean de la marca MIRAY.

17.Mostar los productos donde usted pueda obtener la ganancia liquida de los productos DVD, de la línea AUDIO, y la ganancia se mayor a 45.

Pagina 104

ORACLE

18.Mostrar el total de ventas por meses.

19.Mostrar el total de ventas por distrito, y que cantidad por distrito.

20.Mostrar la descripción de los tipos de comprobantes.

21.Mostrar los proveedores que pertenecen a la línea AUDIO.

22.Modificar la ciudad, estado del proveedor CORPORACION LIGHT GOOD.

23.Modificar los apellidos, nombres del cliente CLINICA SAN LUCAS S.A.

Pagina 105

ORACLE

24.Mostrar los clientes que todavía no han generado alguna compra, y que en el distrito tenga la letra O.

25.Mostrar los productos que sean de la línea LINEA BLANCA, y que el stockeste entre 10 y 20.

26.Incrementar el stock máximo del producto con 30 unidades a la línea MAQUINAS DE COSER.

Pagina 106

ORACLE

Pagina 107

ORACLE

Las vistasUna vista es un objeto que almacena una consulta predefinida y queproporciona un modo alternativo de visualización de datos sin tener queredefinir la consulta.

Ejemplo:

Un subconjunto de filas o columnas de una tabla base. Una unión de dos tablas o mas tablas base. Un join de dos o más tablas. Un resumen estadístico de una tabla base. Un subconjunto de otra vista, o alguna combinación de vistas y tablas base.

Antes de poder crear una vista debemos conceder los permisos necesariospara crear una vista.

GRANT CREATE VIEW, DROP ANY VIEW TO COMERCIAL;

Ejemplo de vista CREATE VIEW V_LISTA_PRODUCTO AS SELECT L.NOM_LIN AS LINEA,P.NOM_PRO AS PRODUCTO,P.PRE_PRO AS PRECIO FROM PRODUCTO P LEFT OUTER JOIN LINEA L ON P.COD_LIN=L.COD_LIN;

Ahora ejecuta la vista SELECT *FROM V_LISTA_PRODUCTO WHERE LINEA='MUSICA';

Pagina 108

ORACLE

Observe que para ejecutar la vista se utiliza la instrucción SELECT. Una vistase manipula como si fuera una tabla; es decir, que se puede aplicar lasdeclaraciones SELECT, INSERT, UPDATE y DELETE. Sin embargo, no debe ver lavista como una tabla, sino pensar en ella como un programa que ejecuta unasola instrucción.

Ventajas de una vista El usuario accede a la data importante o apropiada para el. Limita elacceso a datos sensibles. Oculta la complejidad del modelo de datos. Un Join de múltiples tablasconvierte en un simple SELECT para el usuario. Desde el punto de vista del usuario, una vista es una “tabla” pues puedeejecutar en ella todas las operaciones de datos: SELECT, INSERT, UPDATE yDELETE.Debido a que una vista es un objeto de la base de datos, puede asignarlepermisos de usuario. Esto es mucho más eficiente que colocar los mismospermisos sobre columnas individuales en una tabla.

Sintaxis: CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW nombre_vista [ ( lista_columnas ) ] AS sentencia_select [ WITH CHECK OPTION ] [ WITH READ ONLY ];

OR REPLACE indica que si la vista ya existe, debe ser remplazada por la nuevadefinición de la tabla.

FORCE | NOFORCE indica que se debe verificar la existencia de las tablasbase antes de crear la vista. NOFORCE indica que la vista solo se debe crear silas tablas bases existen (opción predeterminada). FORCE crea la vista aun silas tablas base no existen.

WITH CHECK OPTION indica que si la definición de la vista contiene lacláusula WHERE, y la vista se utiliza para operaciones de mantenimiento dedatos, estas operaciones deben respetar la condición definida en el WHERE.

WITH READ ONLY indica que la vista solo permite consultas solo sepuede ejecutar SELECT sobre la vista, y no puede ejecutarse INSERT, UPDATEni DELETE.

Pagina 109

ORACLE

Eliminación de una vista Sintaxis: DROP VIEW nombre _ vista; Eliminación de una vista. DROP VIEW V_LISTA_PRODUCTO; Uso de WITH CHECK OPTION Ejemplo de vista

CREATE VIEW V_LISTA_PRODUCTO_MUSICA AS SELECT L.NOM_LIN AS LINEA,P.NOM_PRO AS PRODUCTO,P.PRE_PRO ASPRECIO FROM PRODUCTO P LEFT OUTER JOIN LINEA L ON P.COD_LIN=L.COD_LIN WHERE L.NOM_LIN='MUSICA' WITH CHECK OPTION;

Ahora ejecuta la vista SELECT *FROM V_LISTA_PRODUCTO_MUSICA;

Pagina 110

ORACLE

Laboratorio 09:


1. Mostrar los productos.

2. Crear 7 vistas, utilice el esquema comercial (utilice JOINS, función(agregación, criterios de búsqueda), etc.).

Nota: las vistas son independientes, no copie.

Pagina 111

ORACLE

Fundamentos de PL/SQLSQL es un lenguaje de consulta para los sistemas de bases de datosrelaciónales, pero que no posee la potencia de los lenguajes de programación.No permite el uso de variables, estructuras de control de flujo, bucles... ydemás elementos característicos de la programación. No es de extrañar, SQLes un lenguaje de consulta, no un lenguaje de programación.

Sin embargo, SQL es la herramienta ideal para trabajar con bases dedatos. Cuando se desea realizar una aplicación completa para el manejo deuna base de datos relacional, resulta necesario utilizar alguna herramientaque soporte la capacidad de consulta del SQL y la versatilidad de los lenguajesde programación tradicionales. PL/SQL es el lenguaje de programación queproporciona Oracle para extender el SQL estándar con otro tipo deinstrucciones y elementos propios de los lenguajes de programación.

Con PL/SQL vamos a poder programar las unidades de programa de la base dedatos ORACLE, están son:

Bloque anónimo Procedimientos almacenados Funciones Triggers Paquetes

Pagina 112

ORACLE

Bloque anónimo Es la unidad básica de la programación en PL/SQL. Sintaxis: DECLARE BEGIN EXECEPTION END; BEGIN es un elemento obligatorio y define el inicio de la sección al ejecutabledel bloque que incluye las sentencias PL/SQL que el bloque debe ejecutarse. DECLARE es una sección opcional y define objetos tales como variablesconstantes, cursores, y excepciones definidas por el usuario a los que se haráreferencia dentro del bloque. EXCEPTION define los manejadores de errores que soporta el bloque. END es un elemento obligatorio y define el final del bloque.

Ejemplo del bloque anónimo: Mostrar la fecha del sistema DECLARE V_FECHA DATE; BEGIN SELECT SYSDATE INTO V_FECHA FROM DUAL; dbms_output.put_line('FECHA : ' || V_FECHA); END;

Pagina 113

ORACLE

Pagina 114

ORACLE

Laboratorio 10:

1. Mostrar el igv como una constante.2. Mostrar PI como una constante.3. Crear un bloque anónimo donde muestre su edad actual.4. Crear 5 bloques anónimos.

Nota: los bloques anónimos son independientes, no copie.

Pagina 115

ORACLE

Declaración de variables Son elementos de lenguajes que se define en la memoria del sistema a los quese pueden asignar valores para luego usarlos posteriormente.

Sintaxis nombre_variable tipo_dato [CONSTANT] [NOT NULL] [:=Valor]; nombre_variable: es el identificador de la variable. tipo_dato: es cualquier tipo de dato. CONSTANT: indica la definición de una constante. El valor no puede cambiar. NOT NULL: indica que la variable debe ser inicializada obligatoriamente.Valor: si una variable no es inicializada entonces toma el valor nulo como valor inicial.

Ejemplo:

Declare nombre VARCHAR2 (60);

Uso de %TYPE Este operador permite declarar una variable del mismo tipo que la otravariable constante o columna.

Sintaxis nombre_variable identificador%type; nombre_variable: es el identificador de la variable. Identificador: es el nombre de la variable, constante o columna que define eltipo de dato o variable a declarar

Ejemplo:

Declare dFecha comprobante.fechaEmision%TYPE;

Pagina 116

ORACLE

Asignación de valores a variables

Hay dos formas de asignar valor a una variable:Utilizando el operador de asignaciónUtilizando la instrucción Select.

Sintaxis

nombre_variable :=valor;

nombre_variable: es el identificador de la variable.Valor: es una expresión cuyo resultado se asigna a la variable.

Ejemplo:

v_Nombre := ’JUAN PABLO’;n_Stock := 50;

Usando la instrucción Select

Sintaxis

SELECT columnaX columnaPINTO variableX, variablePFROM tabla WHERE condición;

columnaX, columnaP: son valores que lee la consulta.variableX, variableP: son valores que almacenan los valores leídos.

Ejemplo:

SELECT SUM(precio * cantidad) INTO dImporteFROM Comprobante_DetalleWHERE numero_comprobante=25010;

Pagina 117

ORACLE

ProcedimientoUn bloque se puede almacenar como un objeto de la base de datos de modode modo que se puede ejecutar repetidamente invocándolo con la instrucciónEXECUTE. El bloque se puede almacenar como procedimiento o función.

Sintaxis

CREATE [ OR REPLACE ] PROCEDURE nombre_procedimiento(parametro1 [ modo_parametro1 ] tipo_dato1, parametro2 [ modo_parametro2 ] tipo_dato2,…...)IS | AS

BEGIN

EXECEPTIONS

END;

parametro1, parametro2 define los parámetros del procedimiento.

modo_parametro1, modo_parametro2 indica si el parámetro es de entrada(IN), de la salida (OUT), o de entrada o salida (IN OUT), el modopredeterminado es IN.

tipo_dato1, tipo_dato2 define el tipo de datos del parámetro.

La sección de declaraciones de un procedimiento no tiene la palabra reservada DECLARE.

Pagina 118

ORACLE

Ejemplo:

Mostrar el monto total del comprobante CP00000002

CREATE OR REPLACE PROCEDURE USP_MONTO_COMPROBANTE (C_COMPROBANTE CHAR) AS N_MONTO NUMBER(9,2); BEGIN SELECT SUM(PRECIO * CANT) INTO N_MONTO FROM DETALLE WHERE NUM_COMP=C_COMPROBANTE; dbms_output.put_line('MONTO: ' || N_MONTO); END;

El procedimiento recibirá como parámetro un número de comprobante en la variable C_COMPROBANTE. Luego calcula el monto de dicho comprobante, y el resultado lo almacena en la variable N_MONTO. Finalmente muestra el resultado en la variable N_MONTO.

Ejecute el procedimiento. (F9)

EXECUTE USP_MONTO_COMPROBANTE('CP00000002');

Pagina 119

ORACLE

FunciónUna función es similar a un procedimiento con la diferencia que la funciónretorna siempre un valor.

Sintaxis:

CREATE [ OR REPLACE ] FUNCTION nombre_funcion(parametro1 [ modo_parametro1 ] tipo_dato1, parametro2 [ modo_parametro2 ] tipo_dato2,…...)

RETURN tipo_dato_valor_retornoIS | AS

BEGIN

EXECEPTIONS

END;

parametro1, parametro2 define los parámetros del procedimiento.

modo_parametro1, modo_parametro2 indica si el parámetro es de entrada (IN), de la salida (OUT), o de entrada o salida (IN OUT), el modo predeterminado es IN.

tipo_dato1, tipo_dato2 define el tipo de datos del parámetro.

Pagina 120

ORACLE

Ejemplo:

Mostrar el monto total del comprobante CP00000002

CREATE OR REPLACE FUNCTION FN_MONTO_COMPROBANTE(C_COMPROBANTE CHAR) RETURN NUMBER AS N_MONTO NUMBER(9,2); BEGIN SELECT SUM(PRECIO * CANT)INTO N_MONTO FROM DETALLE WHERE NUM_COMP=C_COMPROBANTE; RETURN N_MONTO; END;

Ejecute la función. (F9)

SELECT FN_MONTO_COMPROBANTE('CP00000002') AS MONTO FROM DUAL;

Pagina 121

ORACLE

Laboratorio 11:


1. Crear una vista donde muestre los continentes de los proveedores, solomostrar los proveedores que sean de los continentes ASIA y EUROPA.

2. Crear un bloque anónimo donde pueda insertar una nueva línea, dondeel código de la línea se tiene que incrementar.

3. Crear una función donde muestre el total del mes de marzo.

4. Crear una función donde se pueda obtener el número al cubo de 5 o nnúmero.

5. Crear un Store Procedure donde pueda insertar un nuevo cargo, dondeel código del cargo se tiene que incrementar.

6. Crear un Store Procedure donde pueda actualizar los datos de tablacliente.

7. Crear un Store Procedure donde pueda eliminar la nueva líneaingresada.

8. Crear un Store Procedure donde pueda cargar toda la data de la tablapersonal y testearla en Visual Studio.Net (Visual Basic)

9. Genere un mantenimiento a la tabla cliente en Visual Studio.Net (VisualBasic).

10.Generar un vista y testearlo en Visual Studio.Net (Visual Basic)

Pagina 122

ORACLE

Estructuras de control de flujo

Se tiene 2 estructuras de control básicas: los condicionales, que permiten ejecutar un conjunto de instrucciones u otro en función del valor de una expresión lógica; y los bucles, que permiten la ejecución repetitiva de un conjunto de instrucciones.

El condicional IF

Caso 01: ejecución condicional de un grupo de instrucciones

Sintaxis:

IF (expresión_logica) THEN

END IF;

El conjunto de instrucciones entre IF y END IF se ejecuta solo si laexpresión_logica es verdadera (TRUE).

Caso 02: ejecución condicional de un grupo de instrucciones o de otro grupode instrucciones alternativo

Sintaxis:

IF (expresión_logica) THEN

ELSE

END IF;

El conjunto de instrucciones entre IF y ELSE se ejecuta solo si laexpresión_logica es verdadera (TRUE). El conjunto de instrucciones entreELSE y END IF se ejecuta cuando la expresión_logica se evalúa a FALSE oNULL.

Pagina 123

ORACLE

Caso 03: ejecución condicional de un grupo de instrucciones entre muchosgrupos de instrucciones

Sintaxis:

IF (expresión_logica1) THEN

ELSEIF (expresion_logica2) THEN

ELSEIF (expresion_logica3) THEN

ELSE

END IF;

En este caso, si la expression_logica1 se evalúa a FALSE o NULL, la clausulaELSEIF prueba la expresion_logica2; si esta resulta TRUE, se ejecuta elconjunto de instrucciones entre este ELSEIF y el siguiente; en caso contrario,se evalúa la expresión_logica3.

Pagina 124

ORACLE

Ejemplo:

Crear una función que evalué el stock actual del producto, comparando con elstock mínimo.

CREATE OR REPLACE FUNCTION FX_EVALUCION_STOCK(C_COD_PRO PRODUCTO.COD_PRO%TYPE) RETURN CHARAS N_STOCK_ACTUAL PRODUCTO.STO_ACT%TYPE; N_STOCK_MINIMO PRODUCTO.STO_MIN%TYPE; V_MENSAJE VARCHAR2(50);BEGIN SELECT STO_ACT,STO_MIN INTO N_STOCK_ACTUAL,N_STOCK_MINIMO FROM PRODUCTO WHERE COD_PRO=C_COD_PRO; IF(N_STOCK_ACTUAL < N_STOCK_MINIMO) THEN V_MENSAJE :='HAY QUE REPONER MERCADERIA'; END IF; IF (N_STOCK_ACTUAL > N_STOCK_MINIMO)THEN V_MENSAJE :='TENEMOS MERCADERIA'; END IF; RETURN V_MENSAJE; END;

Ahora procedemos a ejecutar.

SELECT FX_EVALUCION_STOCK ('P0000004') AS MENSAJE FROM DUAL;

Pagina 125

ORACLE

El condicional CASE

Permite la ejecución de un conjunto de instrucciones en base al valor de unavariable o campo (el selector) o en base al valor de una expresión lógica.

Caso 01: ejecución condicional según el valor de una variable o campo

Sintaxis:

CASE selectorWHEN valor1 THEN

WHEN valor2 THEN

ELSE

END CASE;

Caso 02: ejecución condicional según el valor de un conjunto de expresioneslógicas

Sintaxis:

CASE selectorWHEN expression_logica1 THEN

WHEN expression_logica2 THEN

ELSE

END CASE;

Pagina 126

ORACLE

Ejemplo:

Consulta muestra una lista de los proveedores con una columna computadacuyo valor depende del valor encontrado en columna PAIS de la tablaproveedor.

SELECT COD_PRV,RAZON_SOCIAL, CASE PAIS WHEN 'ESPAÑA' THEN 'CONTINENTE EUROPEO' WHEN 'CHINA' THEN 'CONTINENTE ASIATICO' WHEN 'MEXICO' THEN 'CONTINENTE AMERICANO' ELSE 'FALTA ESTABLECER' END AS CONDICION FROM PROVEEDOR;

Pagina 127

ORACLE

Ejemplo:

Consulta califica el nivel de las unidades vendidas de los productos de la líneaL002.

SELECT P.COD_PRO,P.NOM_PRO, CASE WHEN SUM(D.CANT) < 20 THEN 'NIVEL MUY BAJO, < 20 UNIDADES' WHEN SUM(D.CANT) >=20 AND SUM(D.CANT) < 35 THEN 'NIVELBAJO, < 20 UNIDADES' END AS CONDICION FROM PRODUCTO P LEFT OUTER JOIN DETALLE D ON P.COD_PRO=D.COD_PRO WHERE P.COD_LIN='L002' GROUP BY P.COD_PRO,P.NOM_PRO;

Pagina 128

ORACLE

Ejemplo:

Función que establece si el precio del producto esta por encima o por debajodel precio promedio de todos los productos.

CREATE OR REPLACE FUNCTION FX_PRECIO_ALTO_BAJO(C_COD_PRO PRODUCTO.COD_PRO%TYPE) RETURN CHARAS N_PRE_PRO PRODUCTO.PRE_PRO%TYPE; N_PRE_PRO_PROM PRODUCTO.PRE_PRO%TYPE; V_MENSAJE VARCHAR2(60); BEGIN SELECT AVG(PRE_PRO)INTO N_PRE_PRO_PROM FROM PRODUCTO; SELECT PRE_PRO INTO N_PRE_PRO FROM PRODUCTO WHERE COD_PRO=C_COD_PRO;

CASE WHEN (N_PRE_PRO > N_PRE_PRO_PROM) THEN V_MENSAJE := 'PRECIO POR ENCIMA DEL PROMEDIO'; WHEN (N_PRE_PRO = N_PRE_PRO_PROM) THEN V_MENSAJE := 'PRECIO PROMEDIO'; WHEN (N_PRE_PRO < N_PRE_PRO_PROM) THEN V_MENSAJE := 'PRECIO POR DEBAJO DEL PROMEDIO'; END CASE; V_MENSAJE := 'PRODUCTO: ' || C_COD_PRO || ' ' ||TO_CHAR(N_PRE_PRO) || ' - ' || V_MENSAJE; RETURN V_MENSAJE;END;


SELECT FX_PRECIO_ALTO_BAJO ('P0000004') AS MENSAJE FROM DUAL;

Pagina 129

ORACLE

Ejecución de una función o procedimiento utilizando el IDE deOracle SQL Developer

En el cuadro de dialogo Conexiones expanda el nodo Functions. Click derecho del mouse sobre la función – Ejecutar.

Pagina 130

ORACLE

Observe que la función tiene un parámetro de entrada (IN) de nombre C_COD_PRO, ubique el bloque del código la instrucción que inicializa el parámetro.

El parámetro esta inicializado con valor NULL. Remplace este por el código del producto.

En el cuadro de dialogo click en el botón Aceptar para ejecutar la función.

El bucle LOOP

Pagina 131

ORACLE

Permite ejecutar un conjunto de instrucciones en forma repetida. El númerode repeticiones se controla mediante un condicional definido en el bloque delbucle.

Sintaxis:

LOOPIF (expresion_logica) THEN EXITEND IF;

EXIT WHEN (expresion_logica)

END LOOP;

Ejemplo:

Calcula el factorial de N número.

CREATE OR REPLACE FUNCTION FX_FACTORIAL(N_NUMERO NUMBER) RETURN NUMBERAS N_FACTOR NUMBER :=1; N_CONTROL NUMBER :=N_NUMERO;BEGIN LOOP N_FACTOR := N_FACTOR * N_CONTROL; N_CONTROL := N_CONTROL - 1; EXIT WHEN (N_CONTROL=0); END LOOP; RETURN N_FACTOR;END;


SELECT FX_FACTORIAL (5) AS MENSAJE FROM DUAL;

El bucle WHILE

Pagina 132

ORACLE

Permite ejecutar un conjunto de instrucciones en forma repetida. Laejecución del bucle se controla evaluando una expresión lógica antes delinicio de cada una de las repeticiones. Como la evaluación es previa a laejecución del conjunto de instrucciones se puede presentar una situación enla que el bucle no ejecuta nunca el conjunto de instrucciones si la expresiónlógica se evalúa inicialmente a FALSE o NULL.

Sintaxis:

WHILE (expresion_logica) LOOP

END LOOP;

Ejemplo:

Usaremos un bucle WHILE para insertar filas en una tabla que tiene una llave primaria numérica cuyo valor es generado automáticamente por el sistema. Para ello haremos uso del objeto secuencia.

Creación de la secuencia sqClave CREATE SEQUENCE SQCLAVE;

Creación de la tabla temporal Prueba_Claves

CREATE GLOBAL TEMPORARY TABLE PRUEBA_CLAVES (CODIGO NUMBER PRIMARY KEY,NOMBRE VARCHAR2 (30))ON COMMIT PRESERVE ROWS;

Creación del procedimiento que inserta filas en la tabla

CREATE OR REPLACE PROCEDURE PRCARGARTABLA(NFILAS NUMBER)ASNCONTROL NUMBER := 0;BEGINWHILE(NCONTROL < NFILAS) LOOPINSERT INTO PRUEBA_CLAVESVALUES(SQCLAVE.NEXTVAL, 'NO DISPONIBLE');NCONTROL := NCONTROL + 1;END LOOP;COMMIT;DBMS_OUTPUT.PUT_LINE('PROCESO EJECUTADO');END;Probar el procedimiento

Pagina 133

ORACLE

EXECUTE PRCARGARTABLA(5);

SELECT *FROM PRUEBA_CLAVES;

El bucle FOR

Permite ejecutar un conjunto de instrucciones en forma repetitivaestableciendo de antemano el número de repeticiones a ejecutar.

Sintaxis:

FOR contador IN [ REVERSE ] limite_inferior … limite_superior LOOP

END LOOP;

contador es la variable que controla la ejecución de las repeticiones, su valorse incrementa después de cada repetición.

limite_inferior, limite_superior definen el rango de valores que puede asumir el contador durante la ejecución del bucle.

Ejemplo:

Pagina 134

ORACLE

Crearemos un procedimiento que inserte filas en la tabla temporal creada en el ejercicio anterior, pero haciendo uso del bucle FOR.

CREATE OR REPLACE PROCEDURE PRINSERTAR_FILAS(N_FILAS NUMBER)ASBEGIN FOR NCONTADOR IN 1 .. N_FILAS LOOP INSERT INTO PRUEBA_CLAVES VALUES(SQCLAVE.NEXTVAL,'DATO FALTANTE'); END LOOP; DBMS_OUTPUT.PUT_LINE('PROCESO TERMINADO');END;

EXECUTE PRINSERTAR_FILAS(6);

SELECT *FROM PRUEBA_CLAVES;

Ejemplo:

Bucle FOR – Uso de REVERSE

De modo predeterminado, el valor de la variable de control se incrementadespués de la ejecución de cada repetición hasta llegar al límite superior delrango. La clausula REVERSE hace que la variable del control se inicie en ellimite superior y su valor disminuya en uno luego de cada repetición hastallegar al limite inferior.

El siguiente ejemplo hace el uso de REVERSE mediante un procedimiento que crea una tabla de multiplicar.

Pagina 135

ORACLE

CREATE OR REPLACE PROCEDURE PRTABLAMULTIPLICAR (NFACTOR NUMBER)ASV_TEXTO VARCHAR2(20);BEGIN FOR NCONTADOR IN REVERSE 1..12 LOOP V_TEXTO :=NFACTOR || ' X ' || NCONTADOR || ' = ' || (NFACTOR * NCONTADOR); DBMS_OUTPUT.PUT_LINE(V_TEXTO); END LOOP; END;

EXECUTE PRTABLAMULTIPLICAR(10);

Pagina 136

Documents

Introducción a Oracle Database · PDF fileSu tamaño esta determinado por el parámetro DB_BLOCK_SIZE. Extensiones (Extents) Una extensión es el siguiente nivel de almacenamiento