Oracle Database 11g Administrator I

Cap. 9 Administrando Objetos de Esquemas

Universidad de El Salvadorcalderonperza@gmail.comhttp://BasesDeDatosUES.blogspot.com

Usuario y esquema• USUARIO => ESQUEMA

• El usuario SYS es el dueño del diccionario de datos.

Objetos de esquema

tablas• Vistas

Secuencias• Sinónimos

Procedimientos• Funciones

Paquetes

COMPARTEN EL MISMO NAMESPACE

NameSpace: define un mismo grupo de objetos

SCHEMA.OBJETO

Otros objetos• INDEX• CONSTRAINTS• CLUSTERS• TRIGGERS• DATABASE LINK

• Ellos cuando se crean se contruyen en namespace propio.

• Si bien es posible que un indice para una tabla se llame igual que la tabla, tal situacion no es una practica adecuada.

TIPOS DE DATOS• Un tipo de dato es un atributo de una parte de los datos que

indica algo sobre la clase de datos sobre los que se va a procesar.

• Esto incluye imponer restricciones en los datos, como qué valores pueden tomar y qué operaciones se pueden realizar.

Tipos de dato alfanuméricos• VARCHAR2

• NVARCHAR2• Igual varchar2 pero permite caracteres unicode

• CHAR

Tipos de datos numéricos• NUMBER

• FLOAT es la versión ANSI de number

• INTEGER, equivale a number con cero decimales.

Tipos de datos para fechas• DATE

• siglo, año, mes, día, hora, minutos y segundos.• TIMESTAMP

• Igual a date pero una precisión de 9 decimales en los segundos• TIMESTAMP WITH TIMEZONE

• Interval Year to Month• Interval Day to Second

Tipos de datos para objetos grandes

• CLOB• Tamaño prácticamente ilimitado de caracteres

• NCLOB• BLOB

• Similar a Clob pero con datos binarios• BFILE

• Puntero a un archivo almacenado en el SO• LONG

• Obsoleto, sustituido por CLOB

CREANDO TABLAS

• Create table ex_emp as select * from employees where 1=2;

• Alter table ex_emp drop column email;• Alter table ex_emp drop column phone_number;• Alter table ex_emp add(fire_date date);• Drop table ex_emp;• Create table ex_emp (employee_id number(6),

first_name varchar2(200));

CONSTRAINTS• Sirven para aplicar reglas de negocio a los datos, garantizando

la integridad del modelo relacional.

• TIPOS:• UNIQUE• NOT NULL• PRIMARY KEY• FOREING KEY• CHECK

UNIQUE CONSTRAINTS• Determina que un conjunto de columnas no puedan tener

valores repetidos.• Sin embargo un Unique permite que se pueda almacenar

VALORES NULOS.• Se asocia con llaves candidatas

• Ejemplo: el campo DNI o DUI, dentro de una tabla Alumno donde la llave primaria es el carnet, el DUI es una llave Unique debido a que no debe ser repetido pero estudiantes menores de 18 anos no tienen aun DUI.

NOT NULL• Obliga a que todos los valores de una columna sean

introducidos.• Dicho de otra manera, no se permite que en dicha columna se

deje una tupla con ese valor como NULL

PRIMARY KEY

• Sirve para identificar una tupla de forma unica.

• La implementacion de una llave primaria es la combinacion de un UNIQUE y un NOT NULL constraint.

• Se recomienda que no existan ninguna tabla sin llave primaria.

FOREIGN KEY

• Es definida en la tabla hija, de una relacion padre e hijo

• Determina una serie de columnas, cuyos valores se deben corresponder con los de la tabla padre.

• Las columnas pueden llamarse distinto, pero deben poseer el mismo tipo de datos.

Llaves Foráneas

• Si bien se permite colocar valores nulos dentro de los campos de llave foráneas, NO se recomienda pues ello puede ocasionar tuplas huérfanas.

• Se aplican restricciones de integridad referencias, relacionando eventos de Eliminación y Actualización en el lado del padre• ON DELETE CASCADE

CHECK CONSTRAINTS

• Aplican reglas sencillas a un campo en una tabla.

• Ejemplo: un campo sexo en la tabla alumno, el campo es CHAR(1) y posee un check que restringe sus valores a M o F

DEPTDeptno: number(2,0)Dname: varchar2(20)

EMPEmpno: number(4,0)Ename: varchar2(20)Mgr: number(4,0)Dob: dateHiredate: dateDeptono: number(2,0)Email: varchar2(30)

1. create table dept( 2. deptno number(2,0) constraint dept_deptno_pk primary key 3. constraint dept_deptno_ck check (deptno between 10 and 90), 4. dname varchar2(20) constraint dept_dname_nn not null);

5. create table emp( 6. empno number(4,0) constraint emp_empno_pk primary key, 7. ename varchar2(20) constraint emp_ename_nn not null, 8. mgr number (4,0) constraint emp_mgr_fk references emp

(empno), 9. dob date, 10. hiredate date,

1. deptno number(2,0) constraint emp_deptno_fk references dept(deptno)

2. on delete set null, 3. email varchar2(30) constraint emp_email_uk unique, 4. constraint emp_hiredate_ck check (hiredate >= dob +

365*16), 5. constraint emp_email_ck 6. check ((instr(email,'@') > 0) and (instr(email,'.') > 0)));

Índices

• Los indices tienen dos funciones, para implementar llaves primarias y unicas, y para proveer rendimiento.

• Llaves foraneas se implementan por medio de indices, pero los indices existen en la tabla padre.

• Los indices se crean automaticamente.• Si no se posee un indice, se realizara un FULL TABLE

SCAN.

• Son de vital importancia para la clausula WHERE de SQL

Tipos de Indices• B*Tree.

• Es el tipo usado por defecto• B es por Balance• El nodo principal de 3 puntos a muchos nodos en el segundo

nivel• El puntero a una fila es el ROWID

• Bitmap• Son poco utilizados, pues soportan baja cardinalidad.

OPCIONES DE INDICES• UNIQUE: este no permite valores duplicados en el indice.

• REVERSE: realiza una indexacion al reves ej: «JUAN» «NAUJ»

• COMPOSITE: es construido por dos o mas columnas

• COMPRESSED

Creacion de indices• CREATE [UNIQUE | BITMAP ] INDEX NombreIndice ON

NombreTabla (Columna1, columna2) ;

• Por defecto se crea el indice non-unique, no-compressed, no-reverse del tipo B-Tree.

TABLAS TEMPORALES• Una tabla temporal tiene una estructura que es visible a

todas las sesiones, pero las tuplas son privadas, y vistas solo por la sesion que las inserta.

• CREATE GLOBAL TEMPORARY TABLE TablaTemporal (Columna1 DATATYPE, Columna2 DATATYPE) [ON COMMIT {DELETE | PRESERVE} ROWS];

Vista• Para un usuario, una vista es lo mismo que una tabla.• Las instrucciones DML no funcionan sobre una vista.• VENTAJAS

• Seguridad• Simpleza• Prevencion de errores• Facilidad de datos• Rendimiento

Mejorando la Seguridad• Observe la tabla HR.employees, contiene informacion

delicada, sin embargo el depto de finanzas puede requerir alguna informacion, para ello:

• CREATE VIEW hr.emp_fin AS select hire_date, job_id, salary, commission_pct, department_id from hr.employees;

Simpleza• Se vuelve mas sencillo consultar una vista ya elaborada, que

codificar nuestro SQL• CREATE VIEW dept_sal as select d.department_name,

sum(e.salary) from departments d left outer join employees e on d.department_id=e.department_id group by department_name;

• Select * from dept_sal;

Prevenir Errores• Dado que las vistas no son actualizables, al brindar

acceso a las mismas garantizamos la protección sobre los datos reales.

Facilidad de datos• La información en las tablas esta normalizada, por lo

que dificulta la lectura y entendimiento de la misma.

Creando vistas• CREATE [OR REPLACE] VIEW• [Schema.]VistaNombre• AS• Subconsulta

SECUENCIA• Es una estructura para generar valores enteros únicos.• Son de gran utilidad para crear valores de llaves primarias.

Sintaxis• CREATE SEQUENCE NombreSecuencia• [INCREMENT BY numero]• [START WITH numero]• [MAXVALUE numero | NONMAXVALUE]• [MINVALUE numero | NOMINVALUE]• [CYCLE | NOCYCLE ]• [CACHE number | NOCACHE]• [ORDER | NOORDER];

Indica el incremento para la generación del siguiente numero

Valor inicial de la secuencia, por defecto 1

Máximo valor que puede tomar la secuencia en ascenso antes de generar un error o reiniciar conteo

Controla el comportamiento de MAXVALUE y MINVALUE, por defecto mostrara un error, pero si CYCLE es especificado reiniciara la secuencia

Por rendimiento oracle genera un cache de la secuencia de 20 para los siguientes valores

Solo para uso en CLUSTER, order obliga a todas las instancias del cluster a coordinar el incremento en la secuencia

Utilizando secuencias• Cada sesión selecciona su propio valor con NEXTVAL (columna

ficticia de la secuencia)• Nextval es globalmente unico, para cada sesion se genera uno

diferente.• CURRVAL es constante para una sesion, hasta que se

selecciona NEXTVAL nuevamente.• No se puede invocar CURRVAL hasta despues de haber

invocado NEXTVAL

Ejemplo• Create sequence order_seq start with 10;• Create sequence line_seq start with 10;• Insert into orders(order_number, order_date,

customer_number) values(order_seq.nextval, sysdate,‘1000’);• Insert into order_line(order_number, line_number,

item_number, quantity) values (order_seq.currval, line_seq.nextval,’A111’, 1);

• Commit;