manual de oracle 10g

Implementacion

Instructor de Sistemas & Apps to Office: Mañuico Flores, RolyE-mail: [email protected] Movil: 9-6774-3864

TEMA 1Instalación de Oracle XEPara comenzar la instalación. Debemos contar con el instalador de Oracle XE (que te facilitara el Instructor de Sistema: MAÑUICO FLORES, ROLY. ya que eso incluye el material) . Que es una edición gratuita de ORACLE. La cual usaremos como herramienta de trabajo. Debes bajarlos de la página web de Oracle o pedirle una copia pagada a su instructor de sistemas.

Primer Paso Segundo Paso

Doble clic sobre

Ahora debes pulsar el botón siguiente

Tercer PasoAcepta los términos de licencia. Luego pulsar el botón SIGUIENTE >> Cuarto Paso Es Importante verificar es espacio

De tu disco. Para no tener problemas En la instalación.

Presionar botón SIGUIENTE >>Quinto PasoVerificar que el puerto de comunicación no esté siendo utilizado por servicioSi fuese así podrías optar por otro número de puerto el 8085Clic en SIGUIENTE >>

Sexto Paso

Página 1 de 95

Implementacion


Debes ingresar las contraseñas para poder acceder a nuestro servidor como administradores de la base de datos.

Para nuestro servidor actual su contraseña será.

MEGADETH

Repítalo dos veces

Clic en SIGUIENTE >> Ultimo Paso

ES IMPORTANTE ANOTAR ESTOS DATOS YA QUE CUANDO VOLVAMOS A LEVANTAR EL SERVIDOR PODRIA SER QUE NOS PIDA ALGUNO DE ESTOS DATOS

TEMA 2Ingresando a Oracle Express Edition

Página 2 de 95

Implementacion


Primera forma: 1.- INICIO + Todos los Programas + Base de datos Oracle 10g Express Edition + Ir a Página inicial de Base de datos 2.- Ingresa las credenciales de seguridad

La contraseña la cual ingresaste al momento de instalar la cual es MEGADETH y presionar

CONECTAR

Datafiles IntroducciónLos datafiles son los ficheros físicos en los que se almacenan los objetos que forman parte de un tablespace. Un datafile pertenece solamente a un tablespace y a una instancia de base de datos. Un tablespace puede estar formado por uno o varios datafiles.

Cuando se crea un datafile, se debe indicar su nombre, su ubicación o directorio, el tamaño que va a tener y el tablespace al que va a pertenecer. Además, al crearlos, ocupan ya ese espacio aunque se encuentran totalmente vacíos, es decir, Oracle reserva el espacio para poder ir llenándolo poco a poco con posterioridad. Por supuesto, si no hay sitio suficiente para crear un fichero físico del tamaño indicado, se producirá un error y no se creará dicho fichero.

Cuando se van creando objetos en un tablespace, éstos físicamente se van almacenando en los datafiles asignados a dicho tablespace, es decir, cuando creamos una tabla y vamos insertando datos en ella, estos datos realmente se reparten por los ficheros físicos o datafiles que forman parte del tablespace.

No se puede controlar en qué fichero físico se almacenan los datos de un tablespace. Si un tablespace está formado por 2 datafiles y tenemos una tabla en ese tablespace, a medida que vamos insertando filas éstas se almacenarán en

Página 3 de 95

Implementacion


cualquiera de los dos datafiles indistintamente, es decir, unas pueden estar en un datafile y otras en otro.

El espacio total disponible en un tablespace es lógicamente la suma de los tamaños que ocupan los ficheros físicos o datafiles que lo forman. Como hemos indicado estos datafiles, al crearlos, están totalmente vacíos, simplemente es un espacio reservado y formateado por Oracle para su uso. A medida que se van creando objetos en ellos como tablas, índices, etc y se van insertando registros en estas tablas, los datafiles se van llenando o, lo que es lo mismo, el tablespace se va llenando.

Creación y ManipulaciónLa creación de datafiles está estrechamente relacionada con el tablespace al que va a pertenecer. Tenemos varias formas de crear datafiles. Cada vez que se crea un tablespace nuevo, hay que indicar obligatoriamente cual es el datafile que va a pertenecer a dicho tablespace y, en ese momento, se crea tanto el tablespace como su datafile. También se pueden añadir datafiles nuevos a un tablespace que ya existe.

Creación de un nuevo datafile de 50 megabytes junto con un nuevo tablespace:

Create tablespace nombre_tablespace datafile '/users/oracle/orcl/nombre_datafile.dbf' size 50M; Una vez creado este tablespace, si con el tiempo queremos añadirle espacio, lo podemos hacer creando un nuevo datafile y asignándoselo al tablespace:

Alter tablespace nombre_tablespace add datafile '/users/oracle/orcl/nombre_datafile2.dbf' size 100M; Con estas dos instrucciones hemos creado un tablespace nuevo en nuestra base de datos en el que caben 150 megabytes de información. Este espacio está formado físicamente por dos ficheros llamados nombre_datafile.dbf y nombre_datafile2.dbf que se encuentran en el directorio /users/oracle/orcl de nuestra máquina y que ocupan 50 y 100 Mbytes respectivamente.Para conocer los datafiles que forman parte de nuestra base de datos, podemos consultar la vista dba_data_files en la que se nos indica por cada datafile o fichero de datos, a qué tablespace pertenece y cuanto espacio total tiene reservado.

select * from dba_data_files;Es importante recalcar que el espacio que aparece en esta vista es el espacio total que ocupa el fichero físico y no el espacio utilizado de ese fichero, es decir, que si creamos un datafile de 50Mbytes y acto seguido consultamos esta vista, veremos que ocupa 50Mbytes a pesar de estar totalmente vacío. Este dato indica la cantidad de espacio que ocupa el fichero físico, la cantidad de información que podremos introducir en él.

select tablespace_name, file_name, bytes /1024/1024 from dba_data_files;

Página 4 de 95

Implementacion


TEMA 3

SQL Developer es una muy buena herramienta para trabajar con bases de datos Oracle, pero carecía de una función muy importante: la de diseñar o modelar diagramas Entidad/Relación de nuestras bases de datos. Esto ahora se ha solucionado con una nueva tool llamada “SQL Developer Data Modeling” que se encuentra actualmente en fase de testeo y que podemos descargarnos desde el sitio de Oracle.

Primer debemos buscar al instructor de sistemas del curso que llevamos y exigirle que nos proporcione dicha herramienta (solo si pagaste por la autoría de este material). Caso contrario ya deberás buscarlos en internet y descargarlo. Pero bueno comenzamos.

1.- Abrimos la carpeta SqlDeveloper y hacemos clic sobre el archivo

2.- presionamos el botón Nueva Conexion

Página 5 de 95

Primero Pasos

Desde esta ventana. Deberás a acceder al servidor de base de datos xe de oracle. En nombre de Conexion: LOCALHOST, en nombre del Usuario: SYSTEM y la contraseña que usaremos será la que usamos en la instalación. Presionamos el botón PROBAR + CONECTAR

Elementos de

Herramienta Grafica SQLDeveloper

Implementacion


Acceso al sistema informáticoEn informática un acceso es el resultado positivo de una autentificación, para que el acceso dure un tiempo predeterminado, el servidor guarda en el cliente una cookie, esta permitirá que el usuario pueda entrar a su cuenta en el servidor hasta que esta caduque.Acceso autorizado

Un acceso autorizado es el resultado de una autentificación correcta, un ejemplo es el ingreso de usuario y contraseña:

El servidor comparara los datos ingresados con los de la base de datos generalmente con el lenguaje SQL, si los datos coinciden, el servidor guardara una cookie de sesión en el cliente y permitirá que este pueda ver y

editar información dependiendo de los privilegios que este usuario tenga.

Primero chequea si existe o no el usuario, si existe el usuario compara la contraseña, si la contraseña coincide con la de la base de datos, el usuario logra acceder, si la contraseña no coincide con la de la base de datos, se le informa al usuario que la contraseña es incorrecta, si el usuario directamente no se encuentra en la base de datos, se informa que el usuario es incorrecto.Acceso no autorizado

Un acceso no autorizado es producto de la explotación de una vulnerabilidad en el sistema del servidor o en alguna de sus aplicaciones o la utilización de algún otro método para subir privilegios como fuerza bruta, malware, sniffers o ingeniería social, entre otros.

En este caso el atacante exploto una vulnerabilidad para lograr obtener un acceso no autorizado y poder subir su archivo.Permisos

Los permisos o privilegios determinan los límites del usuario sobre un servidor o red, si este usuario posee permisos bajos solo podrá ver y editar una pequeña parte de información, en cambio si el usuario tiene permisos altos, podrá ver y editar toda la información sin ningún tipo de restricciones, a este usuario con permisos altos se lo denomina administrador.

Firewall

Un firewall es una herramienta útil para impedir acceso a usuarios no autorizados, este bloquea los puertos TCP y UDP para que una autentificación no pueda lograrse aunque el usuario tenga los datos necesarios para autentificarse.Monitoreo

Un administrador las 24 horas en un servidor o red puede controlar todos los accesos en todas las zonas y tiene los permisos suficientes para desconectar y banear a cualquier intruso.Ban

Un ban es una política muy útil para impedir accesos no autorizados, este bloquea el acceso de un usuario parcialmente o totalmente por un periodo de tiempo definido o indefinido este puede aplicarse manualmente (por un administrador) o automáticamente (por un bot o el mismo sistema operativo).Bans manualesSon aquellos dados por administrador cuándo este ve un comportamiento extraño en el usuario autentificado.Bans automáticosSon aquellos dados por un bot o el sistema operativo cuándo detectan (basándose en inteligencia artificial) un comportamiento extraño en el usuario autentificado o hay una cantidad determinada de intentos fallidos en una autentificación.

Página 6 de 95

Herramienta de Oracle LINEA DE COMANDO

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

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

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

http://es.wikipedia.org/wiki/Ingenier%C3%ADa_social

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

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

http://es.wikipedia.org/wiki/Aplicaci%C3%B3n_inform%C3%A1tica

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

http://es.wikipedia.org/wiki/Sesi%C3%B3n

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

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

http://es.wikipedia.org/wiki/Cookie_(inform%C3%A1tica)

http://es.wikipedia.org/wiki/Autentificaci%C3%B3n

http://es.wikipedia.org/wiki/Inform%C3%A1tica

Implementacion


Otra forma de acceder al sistema Oracle es haciendo uso de una herramienta de consola (pantalla negra)la cual nos permitirá realizar todas las tareas que se realizan con cualquiera herramienta visual de Oracle.

1.- Inicio + Todos los Programas + Base de datos Oracle 10g Express Edition + Ejecutar líneas de comando SQL (CLIC AQUI)

2.- en comando Conn nos pide que ingresemos el nombre del usuario actual. Para el ejemplo SYSTEM. Luego te pide introducir la contraseña (la cual no se mostrara cuando lo digites)

presionar ENTER

Por medio de lenguaje PS/SQL ahora podras crear todos los objetos de un esquema. Todo a full código. Vamos … no te duermas !!! y a crear los objetos =)

TEMA 3 Desarrollo de esquema Cyberplaza

Página 7 de 95Diseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema CyberplazaDiseño de Tablas del esquema Cyberplaza

Implementacion


Página 8 de 95

Implementacion


Actividad N°1

Página 9 de 95Tablas de Auditoria del esquema Cyberplaza

Implementacion


Página 10 de 95

Implementacion


TEMA 3 Una de las tareas habituales en la administración de una base de datos Oracle es la de crear un nuevo tablespace para contener nuevos objetos como tablas, índices, etc. Un tablespace en Oracle es una unidad de almacenamiento lógica y utiliza datafiles para la parte física donde se guardarán las tablas, índices, etc.

1.- Creando un tablespaceEn este tema vamos a crear un nuevo tablespace para ubicar, por ejemplo, los objetos de una nueva aplicación llamada PANDAL, antes de crearlo tenemos que realizar un análisis de los requerimientos de espacio y ubicación, por ejemplo, el tablespace se debe llamar PANDAL_01, vamos a necesitar 300MB para nuestro nuevo tablespace y el datafile lo vamos a ubicar en /APP_PANDAL/DAT/, tenemos que verificar que tenemos ese espacio libre. TABLESPACE - A LA PRÁCTICAEn esta demostración le enseñamos como crear una partición lógica dentro de nuestra base de datos actual. Donde creamos los objetos físicos que almacenan los tablespace y respectivamente los objetos creados dentro de este espacio de 300mb.

CREATE TABLESPACE Pandal LOGGING DATAFILE ' D:\PANDAL\DAT\Pandal.dbf ' SIZE 300M;

CREATE USER Jhonny PROFILE DEFAULT IDENTIFIED BY TuPirata DEFAULT TABLESPACE Pandal TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

GRANT CONNECT TO Jhonny;GRANT RESOURCE TO Jhonny;

¿Qué función cumple esta consulta?

select * from dba_data_files;

select tablespace_name, file_name, bytes /1024/1024 from dba_data_files;

Página 11 de 95

Aquí estamos creando una partición lógica de 128mb. Este ts de nombre Pandal. Previamente debes contar con las carpetas de destino

CREACION DE TABLESPACE

Aquí estamos creando un usuario de nombre: Jhonny donde laContraseña es: TuPirata Es importante tener estos datos de acceso

CREACION DE USUARIOS

ASIGNANDO PERMISOS DE ACCESO A LA DATA Y CREACION DE OBJETOS

Después de haber asignado estos dos permisos. Debemos acceder al sistema con el usuario Jhonny y crear los sgtes. objetos

Creando TABLESPACE

Implementacion


La creación de tablespace es muy importante antes de crear un Nuevo esquema. Además que nos permite agrupar los objetos en un repositorio lógico. La creación de usuario, así como la creación del tablespace SOLO SE REALIZAN COMO USUARIO SYSTEM. Este usuario tiene todos los privilegios de un dba y puede gestionar el servidor según sus necesidades.

2.- Ampliando la capacidad de almacenamiento de un tablespace

¿Qué sucede si el área de sistema requiere más espacio para el almacenamiento de datos?. Para resolver este problema debemos ampliar el tamaño del tablespace creado anteriormente Después de haber creado el tablespace. Ahora debemos desconectarnos e ingresar con los datos de autenticación de del nuevo usuario creado

.

Los Archivos Físicos

Al culminar esta tarea de ampliación. Puedes apreciar los archivos generados después de haber ejecutado los scripts de ampliación. Debes saber que estos son los archivos DATAFILE. Los cuales representan físicamente lo que es un tablespace. El tablespace Pandal. Ahora tiene cuenta con 500mb de capacidad.

Página 12 de 95

Entendiendo un TABLESPACE

Aquí estamos creando 3 archivos dbf que nos permiten ampliar el tamaño de un tablespace. Es importante recordar que debemos ser SYSTEM

Implementacion


En ciertas ocasiones vas a necesitar modificar el tamaño de un datafile. Cuando los creastes demasiado grande o alguna otra razón. Aquí te dejo un ejemplo

3.- modificando el tamaño de un Datafile

¿Cómo modificar un datafile para que crezca automáticamente sin la intervención del dba?

¿Cómo detener el crecimiento automático de un datafile?

4.- Mantenimiento de un Tablespace

Para el mantenimiento de los componentes de la base de datos es recomendable realizar un estudio previo de la empresa, para así verificar el funcionamiento de estos componentes y para determinar si existe sobresaturación de la información, esto con el fin de tener una idea más clara de lo que puede hacerse sobre la base de datos para que esta este optimizada.Es muy importante que antes de realizar cualquier cambio en la base de datos esta se debe respaldar, para así evitar cualquier inconveniente.

Dentro de los procesos de mantenimiento de una base de datos se encuentran:

Mantenimiento de Tablespace Modo Seguro de Transacciones

4.1.- Eliminado un Tablespace

ES IMPORTANTE SABER QUE DESPUES DE ELIMINAR UN TABLESPACE DEBES CREAR TU INFORME DE ELIMINACION. DESCRIBIENDO EL NOMBRE, TAMAÑO, UBICACIÓN, USUARIO Y FECHA. ESTOS DETALLES DEL TABLESPACE YA ELIMINADO. ES IMPORTANTE RECORDAR QUE LOS ARCHIVOS FISICOS DEBEN SER BORRADOS Y EL USUARIO.

Página 13 de 95

Probar tus scripst y luego completar

FACIL Y RAPIDO

Antes de eliminar un tablespace debes asegurarte que no está en actividad. O que alguien está usando la base de datos.

a) Deshabilitar el Tablespaceb) Eliminar el Tablespace

Implementacion


TEMA 4En este capítulo veremos una alternativa de transferir nuestros objetos de un esquema hacia otro servidor de base de datos. para este capítulo necesitamos conocer sobre tablespace y creación de usuarios. Bueno empecemos

1.- creamos los objetos

2.- Desconéctate de SYSTEM y conéctate como HUBER

3.- Vamos a poblar de 3 tablas y registros para el ejemplo. Pide todo es script a tu instructor de sistemas. SOLO si tienes la licencia de usar este material ORIGINAL no copias.

Tenemos la tabla cargo y distrito la cual se encuentra en el tablespace Manchester_United. Que a su vez en su Datafile Manu.dbf. Por ultimo gestionado por el usuario Huber que tiene la contraseña Campeon

Página 14 de 95

Importando y exportando un ESQUEMA

Implementacion


4.- Clic en Inicio + Todos los programas + Accesorios + Símbolo del sistema

Aquí te aparece la unidad y el usuario del computador.

5.- ingresa el comando EXP y presiona la tecla ENTER

6.-aquí te pedirá el usuario. Asígnale el usuario Huber y presiona la tecla ENTER

7.- Ingresa la contraseña Campeon

8.- Introduce el tamaño del buffer 1024 y presiona la tecla ENTER

9.- ingresa Exportar archivo: D:\JHONNY\RESPALDO\RspEmpresa.h y presiona la tecla ENTER

NOTA: LAS CARPETAS DE DESTINO YA DEBEN DE EXISTIR

Página 15 de 95

EXPORTANDO ESQUEMA

Implementacion


10.- seguidamente debemos completar los siguientes pasos

A. Usuario o Tabla: 2B. Exportar Permisos: sC. Exportar Datos de la tabla: sD. Comprimir extensiones: n

11.- al finalizar empieza a exportarse todos los objetos del usuario Huber y se a creado un archivo de exportación en la siguiente ruta:

Importar un archivo de datos Oracle nos indica que este archivo con tiene todos los objetos de un esquema. Incluyendo tablas, registros, etc. Para poder montar estos objetos en un nuevo servidor Oracle (Otra computadora) previamente debemos crear el Tablespace Manchester_United y el usuario Huber. Como lo aprendisteis en capítulos anteriores..

Además debemos contar con el archivo de exportación que fue creado en los pasos anteriores. Para este ejemplo mi archivo RspEmpresa se encuentra en el disco D: ósea su ruta de acceso es D:\RspEmpresa.h ahora si a montar nuestros datos.

1.- Clic en Inicio + Todos los programas + Accesorios + Símbolo del sistema.

2.- ingresa el comando IMP y presiona la tecla ENTER

Página 16 de 95

IMPORTANDO ESQUEMA

Implementacion


3.- Ingresar Usuario Huber Contraseña Campeon

4.- Importar archivo (apuntar la ruta) D:\RspEmpresa.h

5.- Introduzca el buffer de 10000

6.- Mostrar solo el solo el contenido de archivo de importación n

7.- seguir las siguientes indicaciones:

Ignorar erro de creacion ….. sImportar permisos sImportar datos de la tabla simportar todo el archivo …. s

RESULTADO:

Ahora ingresamos a Oracle como el usuario Huber y podremos verificar que contamos con las tablas y registros creados anteriormente

Actividad 2

Página 17 de 95

a las preguntas del importador responde con la letra s en las ultimas 4 preguntas

Implementacion


Diseñar un esquema que tengas las siguientes tablas. No requiere relaciones peroSi registros y los tipos de datos adecuados

TEMA 51.- CHAR(n)Cadena de caracteres de longitud fija, tiene un tamaño n bytes.

Si no se especifica n la ORACLE le da un tamaño de 255 bytes.El tamaño máximo en BD es 2000 bytes y el mínimo 1 byte.El tamaño máximo en PL/SQL es 32767 bytes y el minimo 1 byte.

CHARACTER es sinonimo de CHAR. Ver NCHAR.2.- VARCHAR2(n)Cadena de caracteres de longitud variable, tiene un tamaño máximo de n bytes.Es obligatorio especificar el tamaño.

El tamaño máximo en BD es 4000 bytes y el mínimo 1 byte.El tamaño máximo en PL/SQL es 32767 bytes y el minimo 1 byte.STRING y VARCHAR son sinonimos de VARCHAR2. Ver NVARCHAR2.Usando VARCHAR2 en lugar de CHAR ahorramos espacio de almamcenamiento.En contra tiene que si se escriben muchas veces hay que hacer un mayor esfuerzo de

Mantenimiento del sistema para mantener la eficiencia (compactar).3.- NUMBER(p,s)Número de p digitos de los cuales s son decimales.No es obligatorio especificar el tamaño.El tamaño de p va de 1 a 38 y el s desde -84 a 127.El tamaño en PL/SQL 1E-130 .. 10E125.Sinonimos:

numeros de coma fija: DEC,DECIMAL,NUMERICenteros:INTEGER (sinonimo de NUMBER(38)),INT,SMALLINTcoma flotante:DOUBLE PRECISION FLOAT REAL.Ver tambien: PLS_INTEGER, BINARY_INTEGER

El valor 7,456,123.89 se almacenará como:NUMBER(9) 7456124 NUMBER(9,1) 7456123.9NUMBER(*,1) 7456123.9 NUMBER(9,2) 7456123.89 NUMBER(6) [error] NUMBER(7,-2) 7456100 NUMBER 7456123.89 FLOAT 7456123.89 FLOAT(12) 7456000.0

4.- DATEFecha válida.Desde el 1 de enero del 4712 AC hasta el 31 de diciembre del 9999 DC. (en Oracle10 = 4712 DC)5.- LONGCadena de caracteres de longitud variable. Es una versión más grande de VARCHAR2.El tamaño máximo en BD es 2 Gigabytes.6.- CLOBCadena de caracteres de longitud variable. Es una versión más grande de VARCHAR2.El tamaño máximo en BD es 4 Gigabytes. Ver NCLOB.Es recomendable usar CLOB o BLOB en lugar de LONG.7.- BLOBObjeto binario de longitud variable. Es una versión más grande de RAW.El tamaño máximo en BD es 4 Gigabytes.8.- BFILEPuntero a un fichero en disco.El tamaño máximo en BD es 4 Gigabytes.9.- TIMESTAMP (f)El timestamp es un fecha que contiene un granularidad superior al tipo DATE, eso significa que contiene fracciones de segundo.Con f definimos el número de dígitos que queremos en la fracción de segundo. Así, f puedes valer desde 0 hasta 9, el valor por defecto es 6.

Página 18 de 95

Tipos de Datos - Tablas

Implementacion


TEMA 61.- Restricción UNIQUE

La sentencia unique nos permite obligar el valor de unicidad dentro de una o mas columnas de una tabla. Con el objetivo de evitar datos redundantes. Las restricciones tiene su nombre y además pueden ser desactivados

CREAR TABLE nombre_tabla (column1 tipo de datos not / null, columna2 tipo de datos null / not null, ... constraint_name Restricción UNIQUE (columna1, column2, column_n). )

Por ejemplo:

En este ejemplo, hemos creado una restricción única en la tabla de proveedores llamado

UniqueNomProv.Se compone de un solo campo - el campo NombreProveedor.Si intentamos agregar 2 proveedores con el mismo nombre Oracle nos enviara un error de violación de datos.También puede crear una restricción única con más de un campo como en el ejemplo siguiente:

Ahora si la tabla ya está creada podemos asignar esta restricción Mediante una instrucción ALTER TABLE La sintaxis para crear una restricción de unicidad en una sentencia ALTER TABLE es la siguiente:

Por ejemplo:

También puede crear una restricción única con más de un campo como en el ejemplo siguiente:1.2.- Drop de una restricción de unicidad

Por ejemplo:

Página 19 de 95

Uso y manejo de Restricciones

Implementacion


1.3.- Desactivar una restricción de unicidadLa sintaxis para deshabilitar una restricción única es la siguiente:

1.4.- Permitir una restricción de unicidadLa sintaxis para permitir una única restricción es la siguiente:

2.-Restricción Check.- es una restricción que nos permite validar los datos de entrada de un campo. Por ejemplos un campo Sueldo podríamos obligar a que no admita sueldos se encuentren entre 100 y 500 soles, en otro caso que los nombre y apellidos sean ingresados en mayúsculas, también podríamos nombrar el campo sexo. Vemos los ejemplos

2.1 Verificando Restricciones

Obligar el Sexo de diferente manera en otra tabla clientes

2.2 Eliminar una restricción

Página 20 de 95

Implementacion


3.- Restricción Foreign Key

Una clave externa (FK) es una columna o combinación de columnas que se utiliza para establecer y exigir un vínculo entre los datos de dos tablas. Puede crear una clave externa mediante la definición de una restricción FOREIGN KEY cuando cree o modifique una tabla.

En una referencia de clave externa, se crea un vínculo entre dos tablas cuando las columnas de una de ellas hacen referencia a las columnas de la otra que contienen el valor de clave principal. Esta columna se convierte en una clave externa para la segunda tabla.

Por ejemplo, la tabla Docente del esquema Instituto tiene un vínculo a la tabla Curso porque existe una relación lógica entre Cursos del instituto y Docente que dictan dicho curso. La columna idCurso de la tabla Docente coincide con la columna de clave principal de la tabla Curso. La columna idCurso de la tabla Docente es la clave externa para la tabla Curso.

Tabla Curso

Tabla

DocentePágina 21 de 95

Implementacion


Relación de Datos entre ambas tablas

Trabajo de RestriccionesDeterminar un tipo de datos

adecuado para cada tabla. Además de las restricciones aprendidas en clases. Mínimo 10 restricciones en todo el esquema. Agregarle registros de prueba. Recordar que si quieres ver

Página 22 de 95

Implementacion


TEMA 71.- EL LENGUAJE S.Q.L.¿Qué es S.Q.L.

Structured Query Language Establecido como el lenguaje de base de datos relacional estándar. Existen numerosos productos que soportan SQL, cada uno de ellos con pequeñas diferencias sin apenas importancia (p.ej. Oracle).

El SQL estándar es el publicado por ANSI e ISO. Características de S.Q.L. Lenguaje de definición de datos (DDL)

Create, Alter, Drop. Lenguaje de manipulación de datos (DML)

Insert, Update, Delete. Lenguaje de control de datos (DCL)

Grant, Revoke. Control de transacciones

Commit, Rollback, Savepoint Restricciones de integridad

Referencial, datos.

S.Q.L. La sentencia SELECT

Página 23 de 95

El Lenguaje SQL

Implementacion


S.Q.L. Sentencia Básica

Ejemplos:

S.Q.L.Etiquetas de Columnas por defectoJustificación de etiquetas y datos por defecto:

Izquierda fechas y caracteres Derecha datos numéricos Etiquetas y datos, por defecto, se muestran en mayúsculas. Posibilidad del uso de alias de columnas.

S.Q.L.Alias de columnaUn alias de columna renombra un encabezamiento de columna. Útil, especialmente, en cálculos. Sigue inmediatamente al nombre de la columna mediante la palabra clave AS entre ellos. Se requiere encerrar un alias entre comillas dobles si contiene espacios en blanco, caracteres especiales o es case sensitive.

S.Q.L.

Página 24 de 95

Implementacion


Demostración:

Expresiones aritméticas - Operadores: Suma (+) Resta (-) Multiplicación (*) División (/)

Cualquier sentencia SQL Precedencia de operadores: * / + - Operadores misma prioridad se evalúan de izq. a derecha. Paréntesis sobre escriben reglas de precedencia.

S.Q.L. Valor NULL NULL es un valor inaccesible, sin valor desconocido o inaplicable. NULL no representa ni un cero ni un blanco. Las expresiones aritméticas que contengan NULL se evalúan a NULL (<> cero).

S.Q.L.Operador de concatenación Representado por dos barras verticales: || Vincula columnas o cadenas de caracteres. Crea una columna resultado que es una expresión de tipo carácter.

Ejemplo:

S.Q.L. Filas duplicadasLas consultas, por defecto, muestran todas las filas, incluyendo las duplicadas. Las duplicadas se eliminan usando DISTINCT en la cláusula SELECT:

S.Q.L. Uso de la cláusula WHEREEstablece un criterio de selección aplicable a la tabla de la que se quiere recuperar datos. Se especifica a continuación de FROM. La condición de WHERE permite comparar columnas con otras columnas, valores, literales, expresiones aritméticas o funciones. S.Q.L. Sintaxis de WHERE

Página 25 de 95

Implementacion


Ejemplo:

S.Q.L. Operadores de comparación Aplicables en las condiciones de la cláusula WHERE:

= Igual que >Mayor que >= Mayor que o igual a < Menor que <= Menor que o igual a

Distinto BETWEEN m AND n Entre m y n (inclusive) IN(lista) Se encuentra en la lista LIKE Se ajusta a un patrón IS NULL Es valor nulo

S.Q.L. Ejemplos de comparación

SELECT ename, saL FROM empWHERE sal BETWEEN 1000 AND 1500;

SELECT empno, ename, sal, mgr FROM EMPWHERE mgr IN (7902, 7566, 7788);

SELECT ename FROM empWHERE ename LIKE ‘_A%’;

Sintaxis de LIKE: “%” representa cero o varios caracteres “_” representa un solo carácter

SELECT ename, mgr FROM emp WHERE mgr IS NULL;

S.Q.L. Operadores lógicos AND devuelve TRUE si ambas condiciones son TRUE. OR devuelve TRUE si alguna de las condiciones es TRUE. NOT devuelve TRUE si la siguiente condición es FALSE.

S.Q.L. Ejemplos con operadores lógicos

SELECT empno, ename, job, sal FROM empWHERE sal >= 1100AND job = ‘CLERK’;

SELECT empno, ename, job, sal FROM empWHERE sal >= 2000OR job = ‘MANAGER’;

Página 26 de 95

Implementacion


SELECT ename, job FROM empWHERE job NOT IN (‘CLERK’, ‘MANAGER’, ‘ANALYST’);

S.Q.L. Reglas de precedenciaSiempre es aconsejable, para facilitar la lectura de la sentencia SQL, utilizar paréntesis que fuercen la prioridad de los operadores lógicos. Sin paréntesis, el orden de evaluación es:

Todos los operadores de comparación NOT AND OR

S.Q.L.Recuperaciones ordenadasLas filas recuperadas en la sentencia SELECT, si no se especifica nada, no tienen ningún orden determinado. Se pueden ordenar con la cláusula ORDER BY siempre al final de una sentencia SELECT.

ASC Orden ascendente. Por defecto. DESC Orden descendente.

TEMA 8Para desarrollar esta clase necesitas habilitar el esquema HR que es una base de datos de ejemplo que viene el Oracle XE. Pero ahora te presentamos su diagrama y explicaremos su composición:

1.- Esquema HR

2.- Consultas: Estas consultan merecen un total análisis para las futuras consultas que deberás de asumir en otras herramientas de información. Las consultas SQL son el 80% de todo lo que es manejo de información. Así que tómale una importancia real. Bueno mucho bla bla bla a comenzar.

Página 27 de 95

Consultas SQL

Implementacion


Consultas HR2.1: seleccionar los nombres, fecha de nacimiento, salario, número telefónico y email de los empleados con salario mayor o igual a 17000.

Select first_name, last_name, hire_date,phone_number

Resultado

2.2: mostrar el código del empleado, nombre del empleado y el nombre del departamento al q corresponde

select employees.employee_id, employees.first_name, department_name from employees, departments where employees.department_id = departments.department_id;

Resultado

2.3: Muestra el nombre, apellido de los empleados con respecto al salario mayor al de Jennifer

select first_name , last_namefrom employeeswhere salary >

(select salary Página 28 de 95

Implementacion


from employees where employee_id = 200 );

Resultado

4: Muestra los empleados que cobran menos de cada departamento. incluso menos que los del departamento 20?

select department_id, min(salary)from employeesgroup by department_idhaving min(salary) >

(select min(salary) from employees

Resultado

5: Muestra el nombre apellido y puesto de trabajo del empleado con mayor sueldo

select first_name , last_name, job_titlefrom employees, jobswhere employees.job_id = jobs.job_idand salary > all

(select avg(salary) from employees group by department_id );

Resultado

Página 29 de 95

Implementacion


6: consultar los países por region

select regions.region_id,region_name,country_name from regions,countries where regions.region_id=countries.region_id;

Resultado

7: consultar los departamentos que se encuentran fuera de eeuu, Y SU respectiva ciudad

select locations.location_id, city, department_name from locations, departments where (locations.location_id=departments.location_id) and (country_id='us');

select r.region_id, r.region_name,c.country_name from regions r, countries c where (r.region_id=c.region_id) and (r.region_name='asia');

8: muestra los paises de Asia

select r.region_id, r.region_name,c.country_name from regions r, countries c where (r.region_id=c.region_id) and (r.region_name='asia');

Página 30 de 95

Implementacion


Resultado

9: Mostrar los países de la tabla countries que empiezan con “a”

select c.country_name,l.city from countries c, locations l where (c.country_id=l.country_id(+))and(c.country_name like 'a%');

Resultado

10: muestra los paises de America

select r.region_id, r.region_name,c.country_name from regions r, countries c where (r.region_id=c.region_id) and (r.region_name='americas');

Resultado

11: muestra la lista de los empleados y sus respectivos jefes

select e.last_name employee,m.last_name manager from employees e inner join employees m on m.employee_id=e.manager_id;

Resultado

Página 31 de 95

Implementacion


12: mostrar el código, nombres, apellidos y sueldo de la tabla empleado

Select employee_id as codigo, first_name as nombre, last_name as apellidos, salary as sueldo from employees

Resultado

13: mostrar la el campo nombre, apellido, sueldo y cargo del empleado

Select first_name as nombre,last_name as apellidos,salary as sueldo,job_title as cargo from employees inner join jobs on employees.job_id=jobs.job_id

Resultado

14: Cantidad de empleados por departamentos con alias

select department_id as departamento,count(*)as empleadosfrom employees group by department_id

Resultado

Página 32 de 95

Implementacion


15: mostrar los campos código, nombre, apellidos, sueldo, área sueldo menor a 5000 y que el nombre empiece con “l”

Select employee_id as codigo,first_name as nombre,last_name as apellidos,salary as sueldo,department_name as area from employees inner join departments on employees.department_id=departments.department_id where last_name like 'l%' and salary<5000

Resultado

16: cantidad de empleados que han ingresado por año con alias

select extract(year from hire_date) as año,count(*)as empleados from employeesgroup by extract(year from hire_date)

Resultado

Página 33 de 95

Implementacion


17: Muestra los salarios que cobran más de cada departamento. Incluso más que los del departamento 100

select department_id,max(salary)from employeesgroup by department_idhaving max(salary)< (select max(salary)from employeeswhere department_id=100);

Resultado

18: mostrar la cantidad de los president y de los administration vice president

Select first_name as nombre,last_name as apellidos,salary as sueldo,job_title as cargo from employees inner join jobs on employees.job_id=jobs.job_id where job_title='president' or job_title='administration vice president'

Resultado

19: mostrar la cantidad de stock manager

Select first_name as nombre,last_name as apellidos,salary as sueldo,job_title as cargo from employees inner join jobs on employees.job_id=jobs.job_id where job_title='stock manager'

Resultado

Página 34 de 95

Implementacion


20: Mostrar la cantidad de empleados que su nombre comience con” p”

Resultado

Select employee_id as codigo, first_name as nombre, last_name as apellidos,salary as sueldo from employees where last_name like 'p%'

TEMA 91.- S.Q.L. Funciones SQL

Existen dos tipos de funciones: o Funciones a nivel de fila o Funciones a nivel de múltiples filas

Funciones a nivel de fila. Tipos: o Carácter o Número o Fecha o Conversión

Funciones a nivel de grupo. Tipos: o Manipulan grupos de filas y devuelven un resultado por cada uno de ellos.

2.- S.Q.L. Funciones de Caracteres Pueden ser de dos tipos:

o Funciones de conversión o Funciones de manipulación de caracteres

De manipulación de caracteres: o CONCAT o SUBSTR o LENGTH o INSTR o LPAD Y RPAD

De conversión: o LOWER , UPPER e INITCAP

3.- S.Q.L. Funciones de conversión caracteres LOWER: Convierte a minúsculas.

Página 35 de 95

Funciones SQL

Implementacion


UPPER: Convierte a mayúsculas. INITCAP: Convierte la primera letra de cada palabra en mayúsculas, y el resto en

minúscula. Atención: Usar una función de conversión dentro de la cláusula WHERE puede ser

altamente ineficiente porque si la columna afectada forma parte de un índice éste lo desactiva, provocando un bajo rendimiento.

4.- S.Q.L. Funciones manipulación caracteres CONCAT: Concatena dos valores. SUBSTR: Extrae una subcadena. LENGTH: Devuelve la longitud de la cadena. INSTR: Devuelve la posición de un carácter o subcadena. LPAD: Justifica a la derecha la cadena. RPAD: Justifica a la izquierda la cadena.

5.- S.Q.L. Funciones Numéricas ROUND (columna | expresión, n)

o Redondea a n posiciones decimales. Si se omite n, no se redondea con decimales. Si n es negativo, los números a la izquierda del punto decimal se redondean a decenas, centenas, ...

TRUNC (columna | expresión, n) o Trunca en la enésima posición decimal. Si se omite n, sin lugares decimales. Si

n es negativo, los números a la izquierda del punto decimal se truncan a cero. MOD (m, n)

o Devuelve el resto de la división de m por n.

Ejemplos sobre funciones

1.- S.Q.L. Ejemplos de funciones numéricas

2.- S.Q.L.

DUAL es una tabla virtual de la bd., que puede ser Usada para inspeccionar SYSDATE.

Página 36 de 95

Implementacion


3.- S.Q.L. Operadores aritméticos de fechas

Sumar o restar un número a/o de una fecha da por resultado una fecha. Restar dos fechas para encontrar la cantidad de días entre esas fechas. Sumar horas a una fecha dividiendo la cantidad de horas por 24.

RESOLVER: ¿Qué retorna de cada consulta?

1.- S.Q.L. Funciones de Grupo (I) AVG ([DISTINCT | ALL] n)

o Valor promedio de n. COUNT ({* | [DISTINCT | ALL |] expr})

o Cantidad de filas con expr no nulo. Con * se cuentan todas las filas incluyendo duplicadas y valores nulos.

MAX ([DISTINCT | ALL] expr) o Valor máximo de expr.

MIN ([DISTINCT | ALL] expr) o Valor mínimo de expr., ignorando los valores nulos.

2.- S.Q.L. Funciones de Grupo (II) STDDEV ([DISTINCT | ALL] n)

o Desviación estándar de n, ignorando los valores nulos. SUM ([DISTINCT | ALL] n)

o Suma los valores de n, ignorando los valores nulos. VARIANCE ([DISTINCT | ALL] n)

o Varianza de n, ignorando los valores nulos Estas funciones no se pueden usar en la cláusula WHERE.

3.- S.Q.L. Funciones de grupo y Nulos Las funciones de grupo IGNORAN los valores nulos de las columnas. ¿Qué resultado obtendríamos se calculamos la media de la comisión de los

empleados?

4.- S.Q.L. NVL y funciones de grupo Esta media no es correcta porque se han ignorado las filas cuya comisión es nula. Solución: Uso de la función NVL para forzar a las funciones de grupo que admitan los

valores nulos. Página 37 de 95

Implementacion


5.- S.Q.L. Sintaxis de GROUP BY Crea grupo de datos, por lo tanto se pueden usar funciones de grupo para devolver

información resumida para cada grupo. 6.- S.Q.L. Uso de GROUP BY (I)

Si se incluye una función de grupo en una cláusula SELECT, no se puede seleccionar resultados individuales a menos que la columna aparezca en la cláusula GROUP BY.

No se pueden usar alias en GROUP BY. Por defecto, tras un GROUP BY, las filas se ordenan de forma ascendente Ejemplo:

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

7.- S.Q.L.Uso de GROUP BY (II) La columna referenciada por GROUP BY no es necesario seleccionarla. Todas las columnas mencionadas en la SELECT que no son funciones de grupo,

tienen que estar en la cláusula GROUP BY. Se pueden formar agrupaciones sobre múltiples columnas:

SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job;

8.- S.Q.L.Consultas no válidas Cualquier columna o expresión en la SELECT que no sea una función agregada, tiene

que ser especificada en la cláusula GROUP BY

SELECT deptno, COUNT(ename) FROM emp;

No puede usar una cláusula WHERE para restringir grupos. Utilice la cláusula HAVING para restringir grupos.

SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000 GROUP BY deptno;

9.- S.Q.L.Cláusula HAVING Use la cláusula HAVING para restringir grupos:

o Los registros son agrupados o Se aplica la función de grupo o Los grupos que se corresponden con la cláusula HAVING se visualizan

(condición TRUE). HAVING puede preceder a GROUP BY, pero se recomienda que se ponga en primer

lugar GROUP BY porque es más lógico. (1º se calculan grupos y posteriormente se calcula HAVING sobre esos gpos.).

Actividad 1

Para la sgte clase un. Debe de entregar 3 ejemplos de cada función que Ud. Aprendió. Los ejemplos deben estar impresos en su folder y además deben enviar al correo [email protected] con el asunto: QUERYS DE FUNCIONES.Para una evaluación. Para el desarrollo de este trabajo debe usar el esquema HR y además la tabla DUAL si fuera necesario.

Página 38 de 95

mailto:[email protected]

Implementacion


TEMA 10Para ingresar a este tema pídale al instructor de sistema Jhonny Pandal. Ósea yo jejeje el esquema TIGER que se usara en estos ejemplos y seguro otros también

Página 39 de 95

Inner Join + y + Tablas

Implementacion


S.Q.L.Concepto de JOIN

Un JOIN se utiliza para consultar datos de más de una tabla La condición de JOIN se escribe en la cláusula WHERE. Si existen columnas con el mismo nombre en las tablas seleccionadas, se deberán

nombrar los campos Ejemplo:

S.Q.L.Tipos de JOIN

Existen dos tipos principales de JOIN: o EQUIJOIN Join sobre dos o más tablas, por igualdad de campos. o NON-EQUIJOIN Por desigualdad, sin correspondencia directa entre campos de

tablas. La relación se puede establecer mediante criterios de rango (<, >, BETWEEN, ...)

Y dos más adicionales: o OUTER JOIN Para ver, también, las filas que no complen la condición de

Join. El operador de un Outer Join es el signo más (+), en el “lado” del join que es deficiente en información.

o SELF JOIN Combinación de una tabla consigo misma.

S.Q.L. Ejemplo de Equijoin

Ya que la columna DEPTNO es igual en ambas tablas, ésta debe ir prefijada por el nombre de la tabla

para evitar la ambigüedad.

S.Q.L. Ejemplo de Non-Equijoins

En este ejemplo se han usado alias de tablas (e para la tabla emp y s para la tabla salgrade).

S.Q.L.Ejemplo de Outer Join

Página 40 de 95

Implementacion


En este ejemplo se muestran los números y nombres de todos los departamentos, incluidos aquellos que no tienen empleado.

Si se le añade: AND emp.deptno is null, sólo se mostrarían las no coincidencias.

S.Q.L.Ejemplo de Self Join

En este ejemplo la cláusula WHERE contiene la combinación "dónde un jefe de un trabajador coincide con el número de empleado para el jefe".

TEMA 11S.Q.L. ¿Cuándo subconsultas?

¿Quién tiene un salario superior al de Jones

S.Q.L. Sintaxis de una subconsulta

La subconsulta se ejecuta una vez y antes de la consulta principal.

Página 41 de 95

Subconsultas

Implementacion


El resultado de ella es usado por la consulta principal externa.

S.Q.L. Guía Uso de Subconsultas

Encierre las subconsultas entre paréntesis. No añada una cláusula ORDER BY a una subconsulta. Utilice operadores a nivel de fila para subconsultas que devuelvan solo una fila

MONOREGISTRO. Utilice operadores que actúan sobre varios registros para subconsultas que devuelven

más de una fila MULTIREGISTRO.

S.Q.L. Subconsultas Mono-registro

Devuelven un único registro. Se utilizan operadores de comparación (=, >, >=, <, <= y <>). Ejemplo:

S.Q.L. Subconsultas Multi-registro

Devuelven más de un registro Se utilizan comparadores multiregistro:

o IN TRUE si se encuentra en la lista. o ANY (y sinónimo SOME) TRUE si la condición se cumple con algún

registro de la lista devuelta por la subconsulta. o ALL TRUE si la condición se cumple con todos los registros de la lista

devuelta por la subconsulta. El operador NOT puede ser utilizado con los operadores IN, ANY y ALL.

S.Q.L. Ejemplo subc. Multi-registro

Página 42 de 95

Implementacion


S.Q.L. Subcons. en cláusula FROM

Puede utilizar una subconsulta en una cláusula FROM de una sentencia SELECT:

Este ejemplo muestra los nombres, salarios, núm. Departamentos y media de salarios, de todos los empleados que cobran más que la media de salarios de su departamento.

TEMA 10S.Q.L. Manipulación de Datos (DML)

Sentencias DML son: o INSERT Añade registros a una tabla. o UPDATE Modifica registros existentes de una tabla. o DELETE Elimina registros existentes de una tabla.

S.Q.L. Control de Datos (DCL)Página 43 de 95

DML

Implementacion


Estas sentencias se completan con los comandos de control de transacción (DCL), las cuales aseguran la consistencia de los datos.

o COMMIT Finaliza la transacción actual haciendo que todos los cambios pendientes pasen a ser permanentes.

o ROLLBACK Finaliza la transacción en curso descartando todos los cambios pendientes.

o SAVEPOINT Establece una "marca" dentro de la transacción en curso, usada por COMMIT o ROLLBACK.

S.Q.L. La Sentencia INSERT

Mediante esta sentencia sólo se inserta un registro cada vez. El nombre de las columnas es opcional. Si se omiten se deben colocar los valores en el

orden que las columnas tienen en la tabla. Caracteres y fechas entre comillas simples.

S.Q.L. Inserción de Valores Nulos

Método Implícito: Omitir la columna en la lista:

Método Explícito: Especificar NULL o el string vacío (‘’), para cadenas y fechas, en la lista de VALUES:

S.Q.L. Inserción Valores Especiales

SYSDATE registra la fecha y hora actual:

USERID inserta el nombre del usuario actual

S.Q.L. Inserción reg. de otra tabla

Se escribe el comando INSERT con una subconsulta. No usar la cláusula VALUES. Deben coincidir el número de columnas de INSERT con el de la subconsulta

S.Q.L. La Sentencia UPDATEPágina 44 de 95

Implementacion


Los registros a modificar se especifican por medio de la cláusula WHERE. Si se omite WHERE se modificarían todos los registros de la tabla.

S.Q.L. Modificación con subconsultas

P.ej.: Modificar el oficio y departamento del empleado 7698, con los valores correspondientes actualmente al empleado 7499:

S.Q.L. La Sentencia DELETE

Los registros a eliminar se especifican en la cláusula WHERE. Si se omite WHERE se borrarán todos los registros de la tabla.

S.Q.L. Eliminación con subconsulta

Utilice subconsultas en sentencias DELETE, para eliminar registros de una tabla, basados en valores de otra tabla:

TEMA 11S.Q.L.Definición de Datos (DDL)

Sentencias DDL son: o CREATE TABLE Crea una tabla. Para ello el usuario debe de tener el

privilegio CREATE TABLE. o ALTER TABLE Permite modificar la estructura definida para una tabla. o DROP TABLE Elimina una tabla (datos y estructura) y sus índices. No se

puede hacer Rollback de esta sentencia. o RENAME Cambia el nombre de una tabla, vista, secuencia o sinónimo.

Página 45 de 95

DDL

Implementacion


S.Q.L. Sentencia CREATE TABLE Necesario tener privilegio CREATE TABLE. Ha de especificar:

o Nombre de tabla o Para las columnas: nombre, tipo de dato y tamaño.

S.Q.L. Reglas para los nombres Deben de comenzar con una letra. Pueden tener una longitud de 1 – 30 caracteres de largo. Deben contener solamente A-Z, a-z, 0-9, _, $ y #. No deben duplicar el nombre de otro objeto que sea propiedad del mismo usuario o

schema. No debe ser una palabra reservada del servidor Oracle8.

S.Q.L. Tipos de Datos

VARCHAR2(tamaño) Dato carácter de longitud variable. Máx. 4000.CHAR(tamaño) Dato carácter de longitud fija. Máx. 255.NUMBER(p,s) Dato numérico de longitud variable.p entre 1..38; s entre 84..127

DATE Valores de fecha y hora. Entre el 1 Enero 4712 A.C. Y el 31 Diciembre del 4712 D.C.

LONG Dato carácter de long.variable hasta 2 Gb.CLOB Dato carácter “single-byte” de hasta 4 Gb.RAW(tamaño) y LONG RAW

Datos Binarios según tamaño especificado y Datos Binarios de long.variable hasta 2 Gb.

BLOB Datos Binarios hasta 4 Gb.BFILE Datos binarios almacenados en fich. Externo. Hasta 4 Gb.

S.Q.L. Creación de tabla por subconsulta

Se puede crear una tabla e insertar filas combinando el comando CREATE TABLE con la opción AS subconsulta.

Es necesario hacer coincidir la cantidad de columnas especificadas con las de la subconsulta.

Si no se indican nombres de columnas, éstas serán los mismos que los de la subconsulta.

S.Q.L. Creación de tabla por subconsultA

Ejemplo:

Página 46 de 95

Implementacion


S.Q.L.Sentencia ALTER TABLE

Permite añadir nuevas columnas o modificar una ya existente.

S.Q.L. Añadir una Columna

La nueva columna aparecerá en el último lugar de la tabla. No se puede especificar el orden.

Puede añadir o modificar columnas, pero no eliminarlas de una tabla. Si la tabla ya contiene registros al añadir una nueva columna, ésta se inicializará con

valores nulos para todos los registros. Puede definir una columna NOT NULL sólo si la tabla está vacía.

S.Q.L. Modificar una Columna

Puede cambiar el tipo de datos de una columna, su tamaño y valor por defecto Si cambia el valor por defecto, afectará sólo a sucesivas inserciones en la tabla.

S.Q.L.Sentencia DROP TABLE

Se borra estructura, datos e índices de la tabla. Borrado Físico. No se puede hacer Rollback de la sentencia. Sólo el propietario de la tabla u otro usuario con el permiso DROP ANY TABLE puede

eliminar una tabla.

S.Q.L. Sentencia RENAME

Permite cambiar el nombre de una tabla, vista, secuencia o sinónimo. Debe ser el propietario del objeto.

TEMA 12S.Q.L. Concepto de Vista

Una vista es una tabla lógica basada en una tabla u otra vista. No contiene datos en sí misma, pero es como una ventana a través de la cual se

pueden ver o cambiar los datos de las tablas. Podemos representar con ellas subconjuntos lógicos o combinaciones de datos. Las tablas sobre las cuales se basa una vista se llaman tablas base. Se almacenan en el Diccionario de Datos, USER_VIEWS.

S.Q.L. ¿Por qué usar Vistas?

Para restringir el acceso a la B.D. Para realizar consultas complejas de manera fácil.

Página 47 de 95

VISTASL

Implementacion


Para obtener una independencia de los datos Para presentar diferentes vistas de los mismos datos.

S.Q.L. Creación de una Vista

FORCE: Crea la vista sin importar que la tabla base exista o no. WITH CHECK OPTION: Especifica que solamente las filas accesibles a la vista pueden

ser insertadas o actualizadas. CONSTRAINT: Nombre asignado a la restricción CHECK OPTION. WITH READ ONLY: Asegura que ninguna operación DML pueda realizarse sobre esta

vista.

S.Q.L.Ejemplo creación de Vista

S.Q.L. Eliminación de una Vista

Al borrar una vista no perderá los datos, porque la vista está basada en tablas subyacentes de la B.D.

Únicamente el creador o un usuario con el privilegio DROP ANY VIEW puede eliminar una vista.

S.Q.L. Limitaciones DML en Vistas

Se pueden realizar operaciones DML sobre vistas simples. No se puede eliminar una fila si la vista contiene Funciones de grupo, una cláusula

GROUP BY o el comando DISTINCT. No es posible modificar datos en la vista si contiene cualquiera de las condiciones

anteriores, columnas definidas por expresiones o la pseudocolumna ROWNUM No se puede agregar datos si la vista contiene cualquiera de las condiciones anteriores

o cualquier columna NOT NULL no incluida por la vista (tabla base).

S.Q.L. Creación de Sinónimos

Simplifican el acceso a los objetos al crear otro nombre para un objeto (sinónimo). Hacen referencia a una tabla propia o de otro usuario. Permite acortar la longitud de los nombre de los objetos a la vez que elimina la

necesidad de cualificar el objeto con un esquema. El DBA puede crear un sinónimo público accesible a todos los usuarios.

Página 48 de 95

Implementacion


S.Q.L. Eliminación de Sinónimos

Sólo el DBA puede eliminar un sinónimo público.

Actividad 1

Crear 15 vistas haciando uso del esquema TIGER Crear 2 tablas por Consulta Agregarle 3 Columnas según a su criterio

Estoy trabajos se presentan en el folder y archivos .sql para validar la nota. Enviar al correo [email protected] con el asunto: DDL

TEMA 13Creación y manejo de Cursores

En general PL/SQL utiliza cursos para todos los comandos de acceso a la base de datos. En este caso el lenguaje permite los cursores implícitos y explícitos. Los cursos implícitos son aquellos establecidos para los comandos que no cuentan con un cursor explícito declarado. Usted deberá utilizar cursores explícitos para aquellas consultas que retornen más de una fila.

Declaración de CursoresSe debe definir el curso en el área de definición de variables, pero se utilizan las palabras reservadas CURSOR nombre_cursor IS, como se muestra a continuación:

CURSOR c_line_item IS

(comando sql)

Página 49 de 95

CURSORES

Implementacion


El comando SQL puede ser cualquier consulta válida. Una vez creada la definición del cursor, usted puede ser capaz de controlar el flujo del mismo por medio del OPEN, FETCH y el CLOSE.

Control del CursorPara manipular los datos del cursor, usted debe usar primero el comando OPEN seguido del nombre del cursor. Posterior a esto debe utilizar el comando FETCH para extraer las filas del cursor, según el criterio seguido por el select. Por último, una vez que ha sido procesado por completo, debe utilizarse el comando CLOSE seguido del nombre del cursor, para esta forma terminar cualquier actividad asociada con el cursor abierto. Veamos un ejemplo:

OPEN c_line_item;...FETCH c_line_item INTO li_info;...(retrieved row processing)...CLOSE c_line_item;

En este caso el código abre el cursor c_line_item y procesa las filas extraídas. Luego de procesar toda la información cierra el cursor.

Atributos del cursor explícitoExisten cuatro atributos asociados con los cursores de PL/SQL:

%NOTFOUND%FOUND%ROWCOUNT%ISOPEN

El %NOTFOUND devuelve FALSE cuando es extraída una fila, TRUE si el último FETCH no extrajo fila alguna y NULL si el SELECT del cursor no ha retornado datos.

El %FOUND es lógicamente opuesto al %FOUND en cuanto al TRUE y FALSE, pero sigue retornando NULL si el cursor no ha retornado datos.

El %ROWCOUNT se utiliza para determinar cuántas filas han sido extraídas en un punto. Este valor se incrementa con cada fila extraída y tiene valor cero cuando apenas ha sido abierto el cursor.

Por último, el %ISOPEN, tiene valores TRUE o FALSE, dependiendo de si el cursor asociado ha sido abierto o no.

Parámetros de CursoresSe pueden especificar parámetros para los cursores de la misma forma que se pasan a procedimientos y/o funciones. Veamos el siguiente ejemplo:

CURSOR c_line_item (p_order_num IN NUMBER) IS SELECT merch_gross, recipient_num FROM line_item WHERE order_num = p_order_num;

Página 50 de 95

Implementacion


Véase que los parámetros son siempre de tipo IN (solo de entrada), pero el tipo de datos es cualquier tipo de datos válido. La idea es referenciar el parámetro dentro de la consulta, y este se pasa a la hora de abrir el cursor.

Es posible también definir los parámetros con default, de forma tal que se puede variar la cantidad de parámetros a utilizar en el cursor.

DECLARECURSOR c_line_item ( order_num NUMBER DEFAULT 100, line_num NUMBER DEFAULT 1 ) IS .... . .BEGIN...OPEN c_line_item ( 19 ); -- valores 19, 1...OPEN c_line_item ( 20, 4 ); -- valores 20, 4...OPEN c_line_item; -- valores 100, 1...END;

CASO PRÁCTICODe esta forma se pueden pasar todos, uno o ningún parámetro a la hora de abrir el cursor.Para un cursor. Para este ejemplo vamos a diseñar un tabla y pasarle un cursos. A trabajar

1.- Diseño la siguiente tabla y le lleno los registros

2.- Abrir Oracle por LINEA DE COMANDO SQL (pantalla negra)

3.- Ingresar EDIT Lista

Página 51 de 95

Aparecerá una ventana de bloc de notas. Debes guardar el archivo con el nombre Lista.sql en el disco local D:

Implementacion


4.- una vez guardado este archivo SQL ahora agregaremos el siguiente código

Página 52 de 95

Implementacion


5.- una vez ingresado el código a este archivo SQL debes cerrar dicha ventana y ejecutar este archivo SQL desde Línea de Comandos SQL haciendo uso del comando START y seguido de nombre y ruta del archivo SQL

Veamos ahora un ejemplo de utilización de %ROWCOUNT:

1.- creamos un archivo SQL de nombre ListaContar.sql, en su interior le agregamos el siguiente cursor y luego lo ejecutamos.

01 Ejemplo

¿Qué te muestra dicho cursor y porque?

02 Ejemplos

USANDO CURSORES EL PROCEDIMIENTOS ALMACENADOS

En este ejemplo ya hemos creado un procedimiento almacenado que nos ayuda a tener un cursor más dinámico. Con una manera de consultar mucho mejor sus registros. El procedimiento almacenado se llama usp_alumnosXEsp y tiene un parámetro de entrada de nombre espec la cual nos facilitara el manejo de las consultas.

Página 53 de 95

Implementacion


Ejecución:

ALGUNOS OTROS EJEMPLOS SOBRE CURSORES (auspiciadores externos)

Página 54 de 95

Implementacion


TEMA 14PROCEDIMIENTOS y FUNCIONES

Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Funciones y procedimientos PL/SQL no son muy diferentes. Un procedimiento o función está constituido por un conjunto de sentencias SQL y PL/SQL lógicamente agrupados para realizar una tarea específica. Los procedimientos y funciones almacenados constituyen un bloque de código PLSQL que ha sido compilado y almacenado en las tablas del sistema de la base de datos Oracle.

Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.

Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.

Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:1.- Verificar si el usuario tiene permiso de ejecución.2.- Verificar la validez del procedimiento o función.3.- Y finalmente ejecutarlo.

Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:1.- Facilidad para

Página 55 de 95

PROCEDIMIENTOS Y FUNCIONES

Implementacion


gestionar la seguridad.2.- Mejor rendimiento al estar compilados y almacenados en la base de datos.3.- Mejor gestión de la memoria.4.- Mayor productividad e integridad.

La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).

Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.

IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.

OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.

IN OUT: Son parámetros de entrada y salida a la vez.

Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).

Ejemplos 01

Previos objetos

Página 56 de 95

Implementacion


Ejemplos 01:Este procedimiento almacenado muestra la descripción del producto cuyo precio es el más elevado. Sea del producto cuyo estado es activo o inactivo (si o no)

Creación:

Ejecución:

Página 57 de 95

Implementacion


Ejemplo 02La solicitud del área de sistemas es que ud. Genere un procedimiento almacenado que permita incrementar en un 10% el precio de los productos que su estado de producción sea SI

Importante sabes que cada vez que ejecutas este USP Ud. Estará realizando ese proceso

Hablan las imágenes

Ejemplo 03El área de sistemas le pide un procedimiento almacenado que le permita consultas por el estado (si o no). Además por el precio que sea mayor a una determinada cantidad. A resolverlo

Página 58 de 95

Implementacion


Para este tipo de ups se necesita obligadamente un cursor. Para recorrer las filas y realizar la entrega de los resultados. Así que es importante conocer el manejo de cursores. Analice estos datos y practíquelo 3 veces y veras que no es tan difícil como parece. Ahora ejecutemos nuestro usp.

Ejecución

Resultado

Ejemplo 04

Página 59 de 95

Implementacion


Nos piden diseñar un ups que nos permita registrar a los nuevos productos que llegan a almacén. Para esto se necesita conocer los tipos de datos de todos los campos de la tabla productos

luego lo ejecutamos entregándole los valores a los parámetros de forma ordenada

Ejemplo 04

Nos piden diseñar un ups que nos permita registrar a los nuevos productos que llegan a almacén PERO QUE TENGAN VALORES POR DEFECTO. Para esto se necesita conocer los tipos de datos de todos los campos de la tabla productos

Seguramente al ingresar unos nuevos registros tendrás problemas de valores y claves duplicadas. Como tú ya eres grandecito. Búscale una solución a esto ok.

TEMA 15Página 60 de 95

PROCEDIMIENTOS DE GESTION

Implementacion


No permite aplicar a un sistema de información rendimiento y seguridad. En este material le entregamos todo para ser trabajado desde SQLDEVELOPER

Página 61 de 95

Creando tabla y registros

Creando Usp_Buscar_Usuario

Ejecutando Usp_Buscar_Usuario

Creando Usp_Guardar_Usuario

Implementacion


Página 62 de 95

Creando Usp_Editar_Usuario

Ejecutando Usp_Editar_Usuario

Creando Usp_Eliminar_Usuario

Implementacion


Seguramente al crear este último procedimiento almacenado. Tendrás problemas de creación y lógicamente ejecución. Me pregunto ¿Qué solución le darías? No piense mucho asi que vamos que de esto depende tu

TEMA 16Página 63 de 95

Ejecutando Usp_Eliminar_Usuario

Creando y Ejecutando Usp_Listar_Creadores

Implementacion


GENERALIDADES DE PL/SQL

1.- Generalidades de PL/SQL (I)Comentarios en PL/SQL: Anteponga a los comentarios de una sola línea dos guiones (--) a modo de prefijo. Coloque comentarios de varias líneas entre /* y */.

Operadores de PL/SQL:**, NOT Exponenciación, negación lógica+, - Identidad, negación*, / Multiplicación, división+, -, || Suma, resta, concatenación=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN ComparaciónAND ConjunciónOR Inclusión

2.- Generalidades de PL/SQL (II)Bloques Anidados y Ámbito de la Variable:

3.- Generalidades de PL/SQL (III)Para hacer referencia a una variable de sustitución en PL/SQL debe anteponer a su nombre dos puntos (:) a modo de prefijo:

4.- Generalidades de PL/SQL (IV) Directrices de Programación para facilitar el mantenimiento del código: Documente el código con comentarios. Desarrolle una convención de mayúsculas/minúsculas para el código. Desarrolle convenciones de nomenclatura para identificadores y otros objetos. Sangre el código para facilitar la lectura. Evite la ambigüedad entre variables locales, parámetros formales y nombres de

columnas de las tablas de la B.D.

5.-Generalidades de PL/SQL (V)Cuando trabaje con valores nulos puede evitar algunos de los errores más habituales si recuerda las siguientes reglas:

Las comparaciones en las que se incluyen valores nulos siempre resultan NULL.

Página 64 de 95

Implementacion


Si se aplica el operador lógico NOT a un valor nulo resulta NULL.En las sentencias de control condicionales, si la condición resulta NULL, no se ejecutarán las sentencias asociadas.

Sentencias SQL en PL/SQL SELECT recupera exactamente UNA fila. INSERT añade una fila. UPDATE modifica una o más filas existentes. DELETE suprime una o más filas existentes. COMMIT hace permanentes todas las modificaciones pendientes. ROLLBACK elimina todas las modificaciones pendientes. SAVEPOINT marca un punto intermedio en el procesamiento de las transacciones.

Sentencia SELECTSintaxis:

Recuerde, sólo se debe de recuperar una fila. Más de una fila provocará errores.

Ejemplo de SELECTRecupere la suma de los salarios de todos los empleados de un departamento específico:

Inserción de DatosAñada nueva información sobre los empleados en la tabla emp:

Actualización de DatosAumente el salario de todos los empleados de la tabla emp que son Analistas:

Página 65 de 95

Implementacion


Supresión de DatosSuprima filas pertenecientes al departamento 10 de la tabla emp:

Control de Transacciones

COMMIT finaliza la transacción actual realizando todos los cambios pendientes en la B.D.

ROLLBACK finaliza la Transacción actual desechando todos los cambios pendientes.

Control de Transacciones

Página 66 de 95

Implementacion


Control Flujo de EjecuciónPuede modificar el flujo lógico de sentencias utilizando sentencias IF condicionales y

Estructuras de control de bucles. Sentencias IF condicionales: IF-THEN IF-THEN-ELSE IF-THEN-ELSIF

Control de bucles: Bucle básico LOOP Bucle FOR Bucle WHILE

Sentencia IFSintaxis:

Flujo de IF-THEN-ELSE

Página 67 de 95

Implementacion


Flujo de IF-THEN-ELSIF

Condiciones Booleanas

Bucle Básico: LOOPSintaxis:

Página 68 de 95

Implementacion


Donde condición es una expresión o variable booleana (TRUE, FALSE o NULL).

Bucle FORSintaxis:

No declare el índice; se declara implícitamente como un BINARY_INTEGER. Fuera del bucle el índice no está definido. Los límites desde. Hasta deben de ser literales numéricos. Pueden ser expresiones que se convierten en valores numéricos.

Bucle WHILESintaxis:

La condición se evalúa al inicio de cada iteración

Etiquetas y Loops Anidados Puede anidar bucles a varios niveles. Utilice etiquetas para distinguir entre los bloques y los bucles. Salga al bucle externo con la sentencia EXIT que hace referencia a la etiqueta. Los nombres de etiquetas deben ir antes de la palabra LOOP y entre

los delimitadores << >>.

Etiquetas y Loops AnidadosEjemplo:

Ejemplos y demostraciones

Página 69 de 95

Implementacion


Ejemplo #1: Supone que debes calcular un bono por las ventas que ha realizado cada empleado dependiendo de ciertas condiciones, veamos cómo se hace:

En este ejemplo se muestra la forma más simple la sentencia IF, donde se pregunta si las ventas fueron mayores que 10000, si la condición es verdadera se asigna un bono de 500, fíjate que en los PL-SQL Packages en Oracle siempre debes incluir la cláusula THEN y cerrar el bloque con END IF; (no es ENDIF;).

Ejemplo #2: Agreguemos ahora la cláusula ELSE al ejemplo anterior, veamos cómo se hace:

En este ejemplo se muestra la segunda forma de usar la sentencia IF incluyendo la cláusula ELSE para generar un caso contrario en tus PL-SQL Packages en Oracle, es decir, si la primera condición no es verdadera se asigna un bono de 200 y luego el control pasa a la instrucción UPDATE. También puedes anidar sentencias IF de la siguiente forma:

En este ejemplo se agrega otra sentencia IF para obtener un bono de 100 si las ventas son menores que 5000, de esta forma se pueden anidar todas las sentencias IF que sean necesarias.Ejemplo #3: Veamos ahora como usar la cláusula ELSIF (no es ELSEIF) modificando el mismo ejemplo anterior:

Página 70 de 95

Implementacion


La cláusula ELSIF permite anidar condiciones mutuamente excluyentes, si la primera condición es falsa se evalúa la segunda condición y así sucesivamente. En este ejemplo dePL-SQL Packages en Oracle si las ventas son menores que 5000 se cumple la segunda condición y se asigna un bono de 100 y el control pasa a la instrucción UPDATE.Te recomiendo que siempre se use el ELSIF en lugar de IF anidados, de esta manera el código de tus PL-SQL Packages en Oracle queda más fácil de leer y de entender para modificaciones posteriores.

CASE

La instrucción CASE puede evaluar múltiples expresiones y devolver para cada una de ellas un valor/bloque de instrucciones. El resultado de cada WHEN puede ser un valor o una sentencia, en el primer caso el resultado de una sentencia CASE se puede guardar en una variable.

Su sintaxis:

Página 71 de 95

Implementacion


Ejemplos de Case

probando

Página 72 de 95

Implementacion


Oracle & Visual Basic I

EL DESARROLLO DE ESTA PEQUEÑA APLICACIÓN BASADA EN UNA FACTURA NOS PERMITE APRENDER LAS CONEXIONES Y LOS OBJETOS DE ADO NET. PERO BASADOS EN ORACLE.

EL DISEÑO DEL FORMULARIO DEBE SER A TU CRITERIO

Página 73 de 95

CAPA DE DATOS

CAPA DE PRESENTACION

CODIGO DEL FORM

Implementacion


Imports System.Data.OracleClientPublic Class frmfactura Dim cn As New OracleConnection("datasource=xe;userid=MERCADO; password=MERCADO;integrated security=no;") Dim orden As String Sub buscar_cliente()

Orden = "select*from cliente where codcli='" & TXTCODCLI.Text & "'" Dim cmd As New OracleCommand(orden, cn) Dim dr As OracleDataReader cn.Open() dr = cmd.ExecuteReader

If dr.Read Then TXTNOMCLI.Text = dr(1) TXTDNICLI.Text = dr(2) Else MsgBox("no existe ...") End If

cn.Close()

End Sub

Sub BUSCAR_PRODUCTO()

orden = "select*from producto where codprod='" & TXTCODPROD.Text & "'" Dim cmd As New OracleCommand(orden, cn) Dim dr As OracleDataReader cn.Open() dr = cmd.ExecuteReader

If dr.Read Then TXTNOMPROD.Text = dr(1) TXTPRECIO.Text = dr(2) TXTCANT.Focus() Else MsgBox("no existe ...") End If

cn.Close()

End Sub Sub BUSCAR_EMPLEADO()

orden = "select*from EMPLEADO where codEMP='" & txtcodemp.Text & "'" Dim cmd As New OracleCommand(orden, cn) Dim dr As OracleDataReader cn.Open() dr = cmd.ExecuteReader

If dr.Read Then txtnomemp.Text = dr(1) Else MsgBox("no existe ...") End If

cn.Close()

End Sub Sub TOTALES() Dim SUMA, IGV, NETO As Decimal For K As Integer = 0 To LSTCOD.Items.Count - 1 SUMA = SUMA + Val(LSTSUB.Items(K))

Página 74 de 95

Implementacion


Next IGV = SUMA * 0.18 NETO = SUMA + IGV

TXTTOTAL.Text = SUMA TXTIGV.Text = IGV TXTNETO.Text = NETO End Sub

Programando sobre los botones

Private Sub TXTCODCLI_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TXTCODCLI.KeyPress If Asc(e.KeyChar) = 13 Then Call buscar_cliente() End IfEnd Sub Private Sub TXTCODPROD_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TXTCODPROD.KeyPress If Asc(e.KeyChar) = 13 Then Call BUSCAR_PRODUCTO() End If End Sub

Private Sub btnbuscaremp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbuscaremp.Click Call BUSCAR_EMPLEADO() End Sub

Private Sub BTNAGREGAR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNAGREGAR.Click

Dim subt As Decimal

LSTCOD.Items.Add(Me.TXTCODPROD.Text) LSTDES.Items.Add(Me.TXTNOMPROD.Text) LSTPRE.Items.Add(Me.TXTPRECIO.Text) LSTCAN.Items.Add(Me.TXTCANT.Text)

subt = Val(TXTPRECIO.Text) * Val(TXTCANT.Text) LSTSUB.Items.Add(subt)

TXTCODPROD.Clear() TXTNOMPROD.Clear() TXTPRECIO.Clear() TXTCANT.Clear()

TXTCODPROD.Clear()

Call TOTALES() End Sub

Private Sub ToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem1.Click If LSTCOD.SelectedIndex <> -1 Then

Página 75 de 95

Implementacion


Dim Idx As Integer Idx = LSTCOD.SelectedIndex

LSTCAN.Items.RemoveAt(Idx) LSTCOD.Items.RemoveAt(Idx) LSTDES.Items.RemoveAt(Idx) LSTPRE.Items.RemoveAt(Idx) LSTSUB.Items.RemoveAt(Idx) Call TOTALES() Else MsgBox("SELECCIONE CODIGO DE PRODUCTO A ELIMINAR .... ") End IfEnd Sub

Sub GRABAR_FACTURA() Orden = "INSERT INTO FACTURA VALUES('" & txtcodfact.Text & "', '" & TXTFECHA.Text & "','" & TXTCODCLI.Text & "', '" & txtcodemp.Text & "'," & TXTTOTAL.Text & ", " & TXTIGV.Text & "," & TXTNETO.Text & ")" Dim CMD As New OracleCommand(orden, cn) cn.Open() CMD.ExecuteNonQuery() cn.Close() MsgBox("PROCESANDO AL 50% .......")

End Sub

Sub GRABAR_DETALLES() For K As Integer = 0 To LSTSUB.Items.Count - 1 Dim COD, NOM As String Dim PRE, CAN, SUBT As Decimal COD = LSTCOD.Items(K) NOM = LSTDES.Items(K) PRE = Val(LSTPRE.Items(K)) CAN = Val(LSTCAN.Items(K)) SUBT = (Val(LSTSUB.Items(K)))

orden = "INSERT INTO DETALLE VALUES('" & txtcodfact.Text & "', '" & COD & "','" & NOM & "'," & PRE & "," & CAN & "," & SUBT & ")" Dim CMD As New OracleCommand(orden, cn) cn.Open() CMD.ExecuteNonQuery() cn.Close() Next MsgBox("PROCESANDO AL 99% .......") MsgBox(" ----- FACTURA GRABADA AL 100% -----") End Sub

Desencadenadores (TRIGGERS)

Página 76 de 95

Implementacion


Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta como consecuencia de una determinada instrucción SQL (una operación DML: INSERT, UPDATE o DELETE) sobre dicha tabla.

La sintaxis para crear un trigger es la siguiente:

El uso de OR REPLACE permite sobreescribir un trigger existente. Si se omite, y el trigger existe, se producirá, un error.

Los triggers pueden definirse para las operaciones INSERT, UPDATE o DELETE, y pueden ejecutarse antes o después de la operación. El modificador BEFORE AFTER indica que el trigger se ejecutará antes o despues de ejecutarse la sentencia SQL definida por DELETE INSERT UPDATE. Si incluimos el modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los campos incluidos en la lista.

El alcance de los disparadores puede ser la fila o de orden. El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se realizan operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.

La siguiente tabla resume los contenidos anteriores.

La cláusula WHEN sólo es válida para los disparadores con nivel de fila.

Página 77 de 95

Implementacion


Dentro del ambito de un trigger disponemos de las variables OLD y NEW . Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando o borrando.

El siguiente ejemplo muestra un trigger que inserta un registro en la tabla PRECIOS_PRODUCTOS cada vez que insertamos un nuevo registro en la tabla PRODUTOS:

El trigger se ejecutará cuando sobre la tabla PRODUCTOS se ejecute una sentencia INSERT.

Orden de ejecución de los triggersUna misma tabla puede tener varios triggers. En tal caso es necesario conocer el orden en el Que se van a ejecutar.Los disparadores se activan al ejecutarse la sentencia SQL.

Si existe, se ejecuta el disparador de tipo BEFORE (disparador previo) con nivel de orden. Para cada fila a la que afecte la orden:

o Se ejecuta si existe, el disparador de tipo BEFORE con nivel de fila.o Se ejecuta la propia orden.o Se ejecuta si existe, el disparador de tipo AFTER (disparador posterior) con nivel

de fila. Se ejecuta, si existe, el disparador de tipo AFTER con nivel de orden.

Restricciones de los triggersEl cuerpo de un trigger es un bloque PL/SQL. Cualquier orden que sea legal en un bloque PL/SQL, es legal en el cuerpo de un disparador, con las siguientes restricciones:

Un disparador no puede emitir ninguna orden de control de transacciones: COMMIT, ROLLBACK o SAVEPOINT. El disparador se activa como parte de la ejecución de la orden que provocó el disparo, y forma parte de la misma transacción que dicha orden. Cuando la orden que provoca el disparo es confirmada o cancelada, se confirma o cancela también el trabajo realizado por el disparador.

Página 78 de 95

Implementacion


Por razones idénticas, ningún procedimiento o función llamado por el disparador puede emitir órdenes de control de transacciones.

El cuerpo del disparador no puede contener ninguna declaración de variables LONG o LONG RAW

Utilización de :OLD y :NEWDentro del ambito de un trigger disponemos de las variables OLD y NEW . Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando o borrando. La siguiente tabla muestra los valores de OLD y NEW.

Los registros OLD y NEW son sólo válidos dentro de los disparadores con nivel de fila. Podemos usar OLD y NEW como cualquier otra variable PL/SQL.

Utilización de predicados de los triggers: INSERTING, UPDATING y DELETINGDentro de un disparador en el que se disparan distintos tipos de órdenes DML (INSERT, UPDATE y DELETE), hay tres funciones booleanas que pueden emplearse para determinar de qué operación se trata. Estos predicados son INSERTING, UPDATING y DELETING.

Su comportamiento es el siguiente:

Este disparador cumple la siguiente función. Su tarea es de insertar en una tabla historias los nuevos registros de la tabla oficial, los registros eliminados o los registros antes de ser actualizados.

Página 79 de 95

Ejemplo 01

Implementacion


Create Or Replace Trigger Tr_Historial_Hardware

After Insert Or Update Or Delete On Hardware For Each Row Declare Fecha_Ahora Date; Equipo_Ahora Varchar2(50); Usuario_Ahora Varchar2(50); Begin Fecha_Ahora := Sysdate; Equipo_Ahora := Sys_Context('Userenv', 'Host'); Select User Into Usuario_Ahora From Dual;If Inserting Then

Insert Into Historial_Hardware (Cod_Hard,Cod_Fabr,Nombre,Modelo,Serie,Frecuencia,Capacidad,Interfaz,Estado,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:New.Cod_Hard,:New.Cod_Fabr,:New.Nombre,:New.Modelo,:New.Serie,:New.Frecuencia,:New.Capacidad,:New.Interfaz,:New.Estado,:New.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'I');

End If;If Updating Then

Insert Into Historial_Hardware (Cod_Hard,Cod_Fabr,Nombre,Modelo,Serie,Frecuencia,Capacidad,Interfaz,Estado,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:Old.Cod_Hard,:New.Cod_Fabr,:New.Nombre,:New.Modelo,:New.Serie,:New.Frecuencia,:New.Capacidad,:New.Interfaz,:New.Estado,:New.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'A');

End If;If Deleting Then

Insert Into Historial_Hardware (Cod_Hard,Cod_Fabr,Nombre,Modelo,Serie,Frecuencia,Capacidad,Interfaz,Estado,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:Old.Cod_Hard,:Old.Cod_Fabr,:Old.Nombre,:Old.Modelo,:Old.Serie,:Old.Frecuencia,:Old.Capacidad,:Old.Interfaz,:Old.Estado,:Old.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'E');

End If;End;

Create Or Replace Trigger Tr_Historial_Software After Insert Or Update Or Delete On Software For Each Row Declare Fecha_Ahora Date; Equipo_Ahora Varchar2(50); Usuario_Ahora Varchar2(50); Begin Fecha_Ahora := Sysdate; Equipo_Ahora := Sys_Context('Userenv', 'Host'); Select User Into Usuario_Ahora From Dual;

If Inserting Then Insert Into Historial_Software (Cod_Soft,Cod_Fabr,Nombre,Version_S,Serie,Obervacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)

Página 80 de 95

Ejemplo 02

Implementacion


Values(:New.Cod_Soft,:New.Cod_Fabr,:New.Nombre,:New.Version_S,:New.Serie,:New.Obervacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'I');


Insert Into Historial_Software (Cod_Soft,Cod_Fabr,Nombre,Version_S,Serie,Obervacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:Old.Cod_Soft,:New.Cod_Fabr,:New.Nombre,:New.Version_S,:New.Serie,:New.Obervacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'A');


Insert Into Historial_Software (Cod_Soft,Cod_Fabr,Nombre,Version_S,Serie,Obervacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:Old.Cod_Soft,:Old.Cod_Fabr,:Old.Nombre,:Old.Version_S,:Old.Serie,:Old.Obervacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'E');

End If;End;

Create Or Replace Trigger Tr_Historial_Licencia After Insert Or Update Or Delete On Licencias For Each Row Declare Fecha_Ahora Date; Equipo_Ahora Varchar2(50); Usuario_Ahora Varchar2(50); Begin Fecha_Ahora := Sysdate; Equipo_Ahora := Sys_Context('Userenv', 'Host'); Select User Into Usuario_Ahora From Dual;

If Inserting ThenInsert Into Historial_Licencias (Cod_Lice,Cod_Soft,Cantidad,Tipo,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:New.Cod_Lice,:New.Cod_Soft,:New.Cantidad,:New.Tipo,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'I');


Insert Into Historial_Licencias (Cod_Lice,Cod_Soft,Cantidad,Tipo,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:Old.Cod_Lice,:New.Cod_Soft,:New.Cantidad,:New.Tipo,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'A');

End If;

If Deleting Then Insert Into Historial_Licencias (Cod_Lice,Cod_Soft,Cantidad,Tipo,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:Old.Cod_Lice,:Old.Cod_Soft,:Old.Cantidad,:Old.Tipo,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'E');

Página 81 de 95

Ejemplo 03

Implementacion


End If;End;

Create Or Replace Trigger Tr_Historial_Computador After Insert Or Update Or Delete On Computador For Each Row Declare Fecha_Ahora Date; Equipo_Ahora Varchar2(50); Usuario_Ahora Varchar2(50); Begin Fecha_Ahora := Sysdate; Equipo_Ahora := Sys_Context('Userenv', 'Host'); Select User Into Usuario_Ahora From Dual;

If Inserting Then Insert Into Historial_Computador (Cod_Comp,Cod_Tipo,Ip,Nombre,Usuario,Clave,Estado,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:New.Cod_Comp,:New.Cod_Tipo,:New.Ip,:New.Nombre,:New.Usuario,:New.Clave,:New.Estado,:New.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'I');


Insert Into Historial_Computador (Cod_Comp,Cod_Tipo,Ip,Nombre,Usuario,Clave,Estado,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:Old.Cod_Comp,:New.Cod_Tipo,:New.Ip,:New.Nombre,:New.Usuario,:New.Clave,:New.Estado,:New.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'A');


Insert Into Historial_Computador (Cod_Comp,Cod_Tipo,Ip,Nombre,Usuario,Clave,Estado,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif) Values(:Old.Cod_Comp,:Old.Cod_Tipo,:Old.Ip,:Old.Nombre,:Old.Usuario,:Old.Clave,:Old.Estado,:Old.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'E');

End If;End;

Create Or Replace Trigger Tr_Historial_Cotizacion After Insert Or Update Or Delete On Cotizacion For Each Row Declare Fecha_Ahora Date; Equipo_Ahora Varchar2(50); Usuario_Ahora Varchar2(50); Begin Fecha_Ahora := Sysdate; Equipo_Ahora := Sys_Context('Userenv', 'Host'); Select User Into Usuario_Ahora From Dual;

If Inserting ThenInsert Into Historial_Cotizacion (Cod_Coti,Cod_Prov,Fecha,Tipo,Detalle,Precio,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:New.Cod_Coti,:New.Cod_Prov,:New.Fecha,:New.Tipo,:New.Detalle,:New.Precio,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'I');

Página 82 de 95

Ejemplo 04

Ejemplo 05

Implementacion



Insert Into Historial_Cotizacion (Cod_Coti,Cod_Prov,Fecha,Tipo,Detalle,Precio,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:Old.Cod_Coti,:New.Cod_Prov,:New.Fecha,:New.Tipo,:New.Detalle,:New.Precio,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'A');


Insert Into Historial_Cotizacion (Cod_Coti,Cod_Prov,Fecha,Tipo,Detalle,Precio,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:Old.Cod_Coti,:Old.Cod_Prov,:Old.Fecha,:Old.Tipo,:Old.Detalle,:Old.Precio,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'E');

End If;End;

Create Or Replace Trigger Tr_Historial_Movimientos After Insert Or Update Or Delete On Movimientos For Each Row Declare Fecha_Ahora Date; Equipo_Ahora Varchar2(50); Usuario_Ahora Varchar2(50); Begin Fecha_Ahora := Sysdate; Equipo_Ahora := Sys_Context('Userenv', 'Host'); Select User Into Usuario_Ahora From Dual;

If Inserting ThenInsert Into Historial_Movimientos (Cod_Movi,Fecha,Cod_Comp,Cod_Empl,Motivo,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:New.Cod_Movi,:New.Fecha,:New.Cod_Comp,:New.Cod_Empl,:New.Motivo,:New.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'I');


Insert Into Historial_Movimientos (Cod_Movi,Fecha,Cod_Comp,Cod_Empl,Motivo,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:Old.Cod_Movi,:New.Fecha,:New.Cod_Comp,:New.Cod_Empl,:New.Motivo,:New.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'A');


Insert Into Historial_Movimientos (Cod_Movi,Fecha,Cod_Comp,Cod_Empl,Motivo,Observacion,Fecha_Modif,Equipo_Modif,Usuario_Modif,Tipo_Modif)Values(:Old.Cod_Movi,:Old.Fecha,:Old.Cod_Comp,:Old.Cod_Empl,:Old.Motivo,:Old.Observacion,Fecha_Ahora,Equipo_Ahora,Usuario_Ahora,'E');

End If;END;

1. Creación de usuarios

Una de las más básicas tareas de un administrador de base de datos es identificar los usuarios. Cada usuario que conecta en la base de datos debe de tener una cuenta. En las

Página 83 de 95

Ejemplo 06

Implementacion


cuentas compartidas son difíciles de aplicar una auditoria.

Para crear un usuario utilizamos la sentencia CREATE USER. Cuando creas una cuenta como mínimo tienes que asignar un único nombre (username) y una contraseña para poder autenticarse.

Para cambiar alguno de los atributos que se le ha añadido al usuario creado se utiliza la sentencia ALTER USER.

2. Autenticación Oracle

Cuando uno se conecta con una instancia de una base de datos la cuenta de usuario debe de estar autenticada. ORACLE provee tres métodos de autenticación para nuestra cuenta de usuario.

AUTENTICACIÓN MEDIANTE PASSWORD:

Cuando un usuario conecta con una base de datos verifica que este usuario y la contraseña introducida almacenada en la base de datos, sea correcta. Las contraseñas se guardan encriptadas en la base de datos (en el data dictionary).

SQL > CREATE USER david IDENTIFIED BY tititus;

En este caso tititus es la contraseña de david que será guardada encriptada en la base de datos.

AUTENTICACIÓN EXTERNA:

Cuando un usuario conecta con la base de datos se verifica que el nombre de usuario es el mismo que el nombre de usuario del sistema operativo para permitir la validación.

No se almacenan las cuentas en la base de datos de ninguna forma. Estas cuentas están siempre referidas con OPS$ .A partir de la versión 10g puedes configurar OS_AUTHENT_PREFIX en el spfile

SQL > CREATE USER ops$david IDENTIFIED BY tititus;

Mediante IDENTIFIED EXTERNALLY decimos a la base de datos que nuestra cuenta es externa y tiene que ser validada con el sistema operativo.

AUTENTICACIÓN GLOBAL:

Cuando un usuario se conecta con la base de datos se verifica globalmente cuando la información pasa por una opción avanzada de seguridad ( ADVANCED SECURITY OPTION ) para la autenticación tal como Kerberos, RADIUS ....

Para las cuentas globales no se almacena tampoco nada en la base de datos.

Página 84 de 95

Implementacion


SQL > CREATE USER david IDENTIFIED GLOBALLY AS ‘CN=alumnos,OU=campus .......’

Mediante IDENTIFIED GLOBALLY decimos a la base de datos que nuestra cuenta se autentica globalmente, mediante otra opción de seguridad avanzada.

3. Asignaciones a los usuarios

ASIGNACIÓN DE UN USUARIO A UN TABLESPACE ( DEFAULT TABLESPACE )

Mediante esta sentencia asignamos un usuario a un tablespace, este será su tablespace por defecto cuando creamos un usuario.

SQL > CREATE USER david IDENTIFIED BY tititus DEFAULT TABLESPACE users;

Mediante esta sentencia, en caso de tener creado ya el usuario le asignamos un tablespace.

SQL > ALTER USER david DEFAULT TABLESPACE users;

La base de datos toma un tablespace por defecto, en caso de querer cambiar este tablespace utilizamos la siguiente sentencia

SQL > ALTER DATABASE DEFAULT TABLESPACE users;

ASIGNACIÓN DE UN USUARIO A UN TABLESPACE TEMPORAL

Un tablespace temporal se utiliza para almacenar “segmentos” temporales que son creados durante operaciones como ORDER BY,SELECT DISTINCT, MERGE JOIN o CREATE INDEX. A veces a los usuarios se les asocia un tablespace temporal para realizar este tipo de operaciones, cuando estas operaciones finalizan este segmento temporal que se ha creado exclusivamente para la operación desaparece.

SQL > CREATE USER david IDENTIFIED BY tititus DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

Mediante TEMPORARY TABLESPACE asignamos como tablespace temporal temp al usuario david. En caso de que el usuario esté creado si queremos asignarle un tablespace temporal utilizamos ALTER USER

SQL > ALTER USER david TEMPORARY TABLESPACE Temp;

ASIGNACIÓN DE UN PERFIL A UN USUARIO

Al igual que podemos asignar un tablespace a un usuario, también podemos asignarle un perfil (profile). El principal perfil ( profile ) por defecto se denomina default.

Página 85 de 95

Implementacion


Si el usuario no está lo podemos crear de la siguiente forma:

SQL > CREATE USER david IDENTIFIED BY tititusDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempPROFILE resource_profile;

En caso de que el usuario ya esté creado al igual que en los anteriores casos utilizamos la sentencia ALTER USER.

SQL > ALTER USER david PROFILE resource_profile;

BORRADO DE UN USUARIO

Para borrar un usuario utilizamos la sentencia DROP USER, podemos opcionalmente incluir la opción CASCADE, se utiliza para borrar recursivamente los objetos del usuario que se pretende borrar.

SQL > DROP USER david CASCADE

OTORGANDO PRIVILEGIOS (GRANTING)

A un usuario podemos otorgarle una serie de privilegios. Un privilegio permite a un usuario acceder a ciertos objetos o realizar ciertas acciones:.- Privilegios sobre Objetos ( Object privileges ) a permisos sobre vistas, tablas, secuencias, procedimientos, paquetes.- Privilegios del Sistema ( System privileges ) a permisos sobre “niveles de la base de datos” como pueden ser conexión a la base de datos, creación de usuarios, limitar cuentas.- Privilegios sobre Roles ( Role privileges ) a muchos permisos son otorgados mediante roles agrupando un conjunto de privilegios.

Para otorgar privilegios utilizamos la sentencia GRANT, para quitar un privilegio o permiso a un usuario utilizamos la sentencia REVOKE

EJEMPLOS:

Privilegio sobre una tabla:

SQL > GRANT ALL ON tabla_alumnos TO david

Siendo tabla_alumnos una tabla de nuestra base de datos y david un usuario de esta, hemos asignado mediante GRANT ALL,todos los permisos al usuario david sobre esta tabla.

GRANT ALL = permisos SELECT, INSERT, UPDATE, DELETE

Página 86 de 95

Implementacion


Si queremos asignar sólo uno de estos permisos utilizamos la misma sentencia pero con el permiso que queramos otorgar.

SQL > GRANT SELECT ON tabla_alumnos TO davidSQL > GRANT SELECT,INSERT ON tabla_alumnos TO david

Privilegio sobre una vista:

Para el caso de las vistas podemos a un usuario otorgar permisos SELECT, INSERT, UPDATE, DELETE, DEBUG, REFERENCES.Siendo vista_alumnos una vista de nuestra base de datos y david un usuario de esta:

Otorgamos al usuario david todos los permisos sobre la vista vista_alumnos.

SQL > GRANT ALL ON vista_alumnos TO david

Otorgamos al usuario david algunos permisos sobre la vista_alumnos

SQL > GRANT SELECT ON vista_alumnos TO david

SQL > GRANT SELECT,INSERT ON vista_alumnos TO david

Privilegio sobre una secuencia:

Con las secuencias pasa lo mismo que con los anteriores objetos vistos, para otorgar permisos se utiliza GRANT. Los permisos que podemos otorgar a una secuencia es SELECT o ALTER.

Privilegio sobre un paquete,función o procedimiento:

Los permisos que podemos otorgar a las funciones, paquetes o procedimientos almacenados en nuestra base de datos son los siguientes: EXECUTE, DEBUG.

QUITANDO PRIVILEGIOS

Si queremos quitar un privilegio a uno de estos objetos haremos lo mismo que con GRANT pero utilizando la sentencia REVOKE.

SQL > REVOKE ALL ON tabla_usuarios FROM david

Página 87 de 95

Implementacion


Principios Básicos de Seguridad en Bases de Datos

1. La seguridad de las bases de datos

La gran mayoría de los datos sensibles del mundo están almacenados en sistemas gestores de bases de datos comerciales tales como Oracle, Microsoft SQL Server entre otros, y atacar una bases de datos es uno de los objetivos favoritos para los criminales.

Esto puede explicar por qué los ataques externos, tales como inyección de SQL, subieron 345% en 2009, “Esta tendencia es prueba adicional de que los agresores tienen éxito en hospedar páginas Web maliciosas, y de que las vulnerabilidades y explotación en relación a los navegadores Web están conformando un beneficio importante para ellos”[*]

Para empeorar las cosas, según un estudio publicado en febrero de 2009 The Independent Oracle Users Group (IOUG), casi la mitad de todos los usuarios de Oracle tienen al menos dos parches sin aplicar en sus manejadores de bases de datos [1].

Mientras que la atención generalmente se ha centrado en asegurar los perímetros de las redes por medio de, firewalls, IDS / IPS y antivirus, cada vez más las organizaciones se están enfocando en la seguridad de las bases de datos con datos críticos, protegiéndolos de intrusiones y cambios no autorizados.

En las siguientes secciones daremos las siete recomendaciones para proteger una base de datos en instalaciones tradicionales.

2. Principios básicos de seguridad de bases de datos

En esta sección daremos siete recomendaciones sobre seguridad en bases de datos, instaladas en servidores propios de la organización.

2.1 Identifique su sensibilidad

No se puede asegurar lo que no se conoce.

Confeccione un buen catálogo de tablas o datos sensibles [2] de sus instancias de base de datos. Además, automatice el proceso de identificación, ya que estos datos y su correspondiente ubicación pueden estar en constante cambio debido a nuevas aplicaciones o cambios producto de fusiones y adquisiciones.

Página 88 de 95

Implementacion


Desarrolle o adquiera herramientas de identificación, asegurando éstas contra el malware [3], colocado en su base de datos el resultado de los ataques de inyección SQL [4]; pues aparte de exponer información confidencial debido a vulnerabilidades, como la inyección SQL, también facilita a los atacantes incorporar otros ataques en el interior de la base de datos.

2.2 Evaluación de la vulnerabilidad y la configuraciónEvalúe su configuración de bases de datos, para asegurarse que no tiene huecos de seguridad.

Esto incluye la verificación de la forma en que se instaló la base de datos y su sistema operativo (por ejemplo, la comprobación privilegios de grupos de archivo -lectura, escritura y ejecución- de base de datos y bitácoras de transacciones).

Asimismo con archivos con parámetros de configuración y programas ejecutables.

Además, es necesario verificar que no se está ejecutando la base de datos con versiones que incluyen vulnerabilidades conocidas; así como impedir consultas SQL desde las aplicaciones o capa de usuarios. Para ello se pueden considerar (como administrador):

Limitar el acceso a los procedimientos a ciertos usuarios. Delimitar el acceso a los datos para ciertos usuarios, procedimientos

y/o datos. Declinar la coincidencia de horarios entre usuarios que coincidan.

2.3 Endurecimiento

Como resultado de una evaluación de la vulnerabilidad a menudo se dan una serie de recomendaciones específicas. Este es el primer paso en el endurecimiento de la base de datos. Otros elementos de endurecimiento implican la eliminación de todas las funciones y opciones que se no utilicen. Aplique una política estricta sobre que se puede y que no se puede hacer, pero asegúrese de desactivar lo que no necesita.

2.4 Audite

Una vez que haya creado una configuración y controles de endurecimiento, realice auto evaluaciones y seguimiento a las recomendaciones de auditoría para asegurar que no se desvíe de su objetivo (la seguridad).

Página 89 de 95

Implementacion


Automatice el control de la configuración de tal forma que se registre cualquier cambio en la misma. Implemente alertas sobre cambios en la configuración. Cada vez que un cambio se realice, este podría afectar a la seguridad de la base de datos.

2.5 Monitoreo

Monitoreo en tiempo real de la actividad de base de datos es clave para limitar su exposición, aplique o adquiera agentes inteligentes [5] de monitoreo, detección de intrusiones y uso indebido.

Por ejemplo, alertas sobre patrones inusuales de acceso, que podrían indicar la presencia de un ataque de inyección SQL, cambios no autorizados a los datos, cambios en privilegios de las cuentas, y los cambios de configuración que se ejecutan a mediante de comandos de SQL.

Recuerde que el monitoreo usuarios privilegiados, es requisito para la gobernabilidad de datos y cumplimiento de regulaciones como SOX y regulaciones de privacidad. También, ayuda a detectar intrusiones, ya que muchos de los ataques más comunes se hacen con privilegios de usuario de alto nivel.

El monitoreo dinámico es también un elemento esencial de la evaluación de vulnerabilidad, le permite ir más allá de evaluaciones estáticas o forenses. Un ejemplo clásico lo vemos cuando múltiples usuarios comparten credenciales con privilegios o un número excesivo de inicios de sesión de base de datos.

2.6 Pistas de Auditoría

Aplique pistas de auditoría y genere trazabilidad de las actividades que afectan la integridad de los datos, o la visualización los datos sensibles.

Recuerde que es un requisito de auditoría, y también es importante para las investigaciones forenses.

La mayoría de las organizaciones en la actualidad emplean alguna forma de manual de auditoría de transacciones o aplicaciones nativas de los sistemas gestores de bases de datos. Sin embargo, estas aplicaciones son a menudo desactivadas, debido a:

su complejidad altos costos operativos problemas de rendimiento la falta de segregación de funciones y

Página 90 de 95

http://sox.sourceforge.net/

Implementacion


la necesidad mayor capacidad de almacenamiento.

Afortunadamente, se han desarrollado soluciones con un mínimo de impacto en el rendimiento y poco costo operativo, basado en tecnologías de agente inteligentes .

2.7 Autenticación, control de acceso, y Gestión de derechos

No todos los datos y no todos los usuarios son creados iguales. Usted debe autenticar a los usuarios, garantizar la rendición de cuentas por usuario, y administrar los privilegios para de limitar el acceso a los datos.

Implemente y revise periódicamente los informes sobre de derechos de usuarios, como parte de un proceso de formal de auditoría.

Utilice el cifrado [6] para hacer ilegibles los datos confidenciales, complique el trabajo a los atacantes, esto incluye el cifrado de los datos en tránsito, de modo que un atacante no puede escuchar en la capa de red y tener acceso a los datos cuando se envía al cliente de base de datos.

Administrador de base de datosEl Administrador de bases de datos (DBA1 ) es el profesional de tecnologías de la información y la comunicación, responsable de los aspectos técnicos, tecnológicos, científicos, inteligencia de negocios y legales de bases de datos. Los administradores de bases de datos, implementan, dan soporte y gestionan, bases de datos corporativas. Los administradores de bases de datos, crean y configuran bases de datos relacionales. Los administradores de bases de datos, son responsables de la integridad de los datos y la disponibilidad. Los administradores de bases de datos, diseñan, despliegan y monitorizan servidores de bases de datos. Los administradores de bases de datos, diseñan la distribución de los datos y las soluciones de almacenamiento. Los DBAs, garantizan la seguridad de las bases de datos, incluyendo backups y recuperación de desastres. Los administradores de bases de datos, planean e implementan el aprovisionamiento de los datos y aplicaciones. Los administradores de bases de datos, diseñan planes de contigencia. Los administradores de bases de datos, diseñan y crean las bases de datos corporativas de soluciones avanzadas. Los DBAs, analizan y reportan datos corporativos que ayuden a la toma de decisiones en la inteligencia de negocios. Los DBAs, producen diagramas de entidades relacionales y diagramas de

Página 91 de 95

http://catarina.udlap.mx/u_dl_a/tales/documentos/lis/camargo_m_d/capitulo3.pdf

http://catarina.udlap.mx/u_dl_a/tales/documentos/lis/camargo_m_d/capitulo3.pdf

Implementacion


flujos de datos, normalización esquemática, localización lógica y física de bases de datos y parámetros de tablas. Los administradores de bases de datos tienen competencias y capacidades en uno o más sistemas de gestión de bases de datos, algunos ejemplos: Microsoft SQL Server, IBM DB2, Oracle MySQL, Oracle database y SQL Anywhere. En ingeniería estadística es una de las cualificaciones subyacentes, que trata la información para almacenarla, hacerla altamente explotable y altamente disponible. Además, vela por la eficacia técnológica del almacenamiento en el desempeño de investigaciones, buscando inferencias sólidas y compactas, para canalizar resultados manteniendo un equilibrio entre las ciencias involucradas y la propiamente enunciada, ingeniería estadística de las ciencias de la computación. El control de tecnologías de bases de datos y las matemáticas permite al DBA rendir informes, realizar reportes sobre cualquier proceso industrial y participar de forma activa en procesos avanzados de desarrollo, consolidando las capacidades propias de un profesional de tecnologías de la información y un ingeniero especialista. Los factores de éxito en la carrera del DBA se versan sobre las cualificaciones en los avances de las tecnologías de gestión del almacenamiento, los avances en sistemas gestores de bases de datos y requerimientos de cualificación para cada proyecto como garantía de calidad necesaria en el rol a asignar, incluyendo, técnicas avanzadas de gestión de infraestructuras tecnológicas, la gestión de protocolos y servicios de redes, la optimización de código de programación, garantizar el procesamiento eficaz de información, la gestión de interfaces integrales para el tratamiento de datos, la gestión de cambios, la gestión por objetivos y las gestión por resultados. Se definen algunos aspectos que incluye la profesión del DBA:

Profesional de software de fabricante - Profesionales acreditados en administración de bases de datos y tecnologías específicas, desde, tecnólogos, ingenieros, post-graduados, másteres y doctorados(en proyectos de investigación como en biotecnologías y tratamiento de datos de Genómica, por ejemplo).

Metodología de desarrollo software - Ofreciendo y compartiendo diseños concretos sobre el trabajo total, estandarizando sus actividades, definiendo arquitecturas compartidas en un único uso desde las fases desarrollo y las implementaciones necesarias para ejercer el control de los datos garantizando e inclusive el cumplimiento

Página 92 de 95

Implementacion


de los plazos de entrega, intercambiando requerimientos de calidad en el software y cumpliendo con todos los acuerdos contractuales alineados al objetivo empresarial, por ejemplo SOA.

Optimización de software - Realización de tareas de mejora y solución de problemas en los niveles de servicios implicados.

Ingeniería del software y Herramientas CASE - Diseño, Planeación, implementación y gestión de arquitecturas e infraestructuras software.

Ingeniería de requerimientos - Estudios de funcionalidad y compatibilidad en la analítica del negocio.

Tecnologías de almacenamiento - Coordinación de Racks, plataformas hardware & software de sistemas operativos, cabinas de almacenamiento, sistemas de particionamiento, Centro de procesamiento de datos y comunicaciones.

Desastres y recuperación - Implementación de copias de seguridad y centros de respaldo.

Integridad de datos - Integrar proyectos compatibles de formato controlando la consistencia de los datos desde los requerimientos del desarrollo hasta la integración de los sistemas con las lineas del negocio.

Seguridad tecnológica - Brindar las soluciones en los estudios de gestión de riesgos y estudios avanzados (Guerra informática, guerra digital o ciberguerra).

Disponibilidad - Asegurar la continuidad de los servicios de las bases de datos "full time, 24x7, non stop database, open 369".

Análisis de sistemas - Analizar ciclos de procesamiento y el retorno funcional de todas las capas de negocio.

Testing - Realizar pruebas de software y/o de hardware. Gestión de proyectos.

Auditorías tecnológicas del DBA

El DBA implementa protocolos y soluciones de seguridad en infraestructuras tecnológicas, implementando los planes de seguridad de aplicación orientadas a producto, implementando requerimientos deauditoría e implementando soluciones estrictas de seguridad (Gobierno y leyes); Además, se encarga de diseñar, actualizar y ejecutar planes de seguridad integrales desde la inteligencia productiva de negocios y los requerimientos debidamente expuestos.

Página 93 de 95

Implementacion


Ingeniería de soporte del DBA

Los ingenieros de soporte DBA están encargados de ofrecer soluciones de disponibilidad en los planes de continuidad de negocios y en todos los procesos de aprovisionamiento de datos en entornos de producción y entornos de desarrollo, coordinando procesos de entrega con gerentes de servicio y líderes de equipo de Tecnologías de la Información, documentando procesos de mejora y cumpliendo con los objetivos establecidos por la dirección de TI definidas en herramientas ITIL.

Testing del DBA

Los ingenieros de testing realizan pruebas de rendimiento, pruebas de impacto, pruebas funcionales, pruebas de código, pruebas de carga de datos, pruebas de implementación y pruebas de integración en proyectos Investigación, desarrollo e innovación. Para la ejecución de pruebas es necesario aplicar las metodologías basadas en Ingeniería del software, sus aplicaciones funcionales y los requerimientos de calidad, añadiendo las lineas base de la ingeniería requerimientos en la inteligencia de negocios y las tecnologías de la información.

Roles del DBA

Analistas de datos. Analistas de sistemas. Analistas de programación. Administradores de bases de datos. Consultores de sistemas. Consultores de tecnologías de la información. Consultores de aplicaciones. Consultores de programación. Programadores de bases de datos. Programadores de procesos de negocio. Integradores de datos. Ingenieros de datos. Ingenieros estadísticos. Jefes de centros de datos. Coordinadores de área. Jefes de aplicaciones. Ingenieros de bases de datos espaciales.

Página 94 de 95

Implementacion


Minería de datos espaciales. Jefe de proyectos espaciales. Ingenieros de software. Instructores de software. Profesores de bases de datos. Gestores de green IT. Ingenieros de soporte TI. Jefes de soporte TI. Administrador de proyectos de minería de datos. Consultor de proyectos de Minería de datos. Directores de área. Contractors. Gerentes de producto. Gerentes de consultoría. Gerentes de soporte TI.

Página 95 de 95

manual de oracle 10g

Documents