Curso De Oracle 10g Administracion Nivel Intermedio
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Curso De Oracle 10g Administracion Nivel Intermedio as PDF for free.
More details
- Words: 10,950
- Pages: 40
Reproducción prohibida
Versión 1.0
© Copyright Cédric Simon, 2008
SolucionJava.com
Manual del alumno
Ing. Cedric Simon – Tel: 2268 0974 – Cel: 8888 2387 – Email: c e d r i c @ s o l u c i o n j a v a . c o m – Web: www.solucionjava.com
Curso de administración de Oracle 10g (10.2) Nivel Intermedio
Curso de administración de Oracle 10g (10.2)
Pagina 2 / 40
1 Índice 1 Índice..................................................................................................................................................................................2 1 Introducción al curso.........................................................................................................................................................4 1.1 Objetivo de este curso................................................................................................................................................4 1.2 Manual del alumno.....................................................................................................................................................4 1.3 Requisitos para atender a este curso........................................................................................................................4 1.4 Soporte después del curso..........................................................................................................................................4 2 DML - Sentencias de manipulación de datos...................................................................................................................5 2.1 Objetivo del capítulo..................................................................................................................................................5 2.2 Insert............................................................................................................................................................................5 2.3 Update.........................................................................................................................................................................5 2.4 Delete...........................................................................................................................................................................5 2.5 Commit y rollback......................................................................................................................................................5 2.6 Select............................................................................................................................................................................6 2.7 Where..........................................................................................................................................................................6 2.8 Count...........................................................................................................................................................................7 2.9 Sum, avg, min, max....................................................................................................................................................7 2.10 Distinct.......................................................................................................................................................................7 2.11 Order by....................................................................................................................................................................7 2.12 Uniones .....................................................................................................................................................................7 2.13 Subconsultas.............................................................................................................................................................8 2.14 Agrupaciones............................................................................................................................................................8 2.15 Operadores SQL ......................................................................................................................................................8 2.16 La ausencia de valor: NULL ................................................................................................................................10 2.17 Rendimiento............................................................................................................................................................10 3 Creación de objetos..........................................................................................................................................................11 3.1 Vistas.........................................................................................................................................................................11 3.1.1 Creación..............................................................................................................................................................11 3.1.2 Modificación.......................................................................................................................................................11 3.1.3 Eliminación.........................................................................................................................................................11 3.1.4 Ejercicios.............................................................................................................................................................11 3.2 Secuencias.................................................................................................................................................................11 3.2.1 Creación..............................................................................................................................................................11 3.2.2 Modificación.......................................................................................................................................................12 3.2.3 Eliminación.........................................................................................................................................................12 3.2.4 Ejercicios.............................................................................................................................................................12 3.3 Vistas materializadas...............................................................................................................................................12 3.3.1 Creación..............................................................................................................................................................12 3.3.2 Modificación.......................................................................................................................................................13 3.3.3 Eliminación.........................................................................................................................................................13 3.4 Sinónimos..................................................................................................................................................................13 3.4.1 Creación..............................................................................................................................................................13 © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 3 / 40
3.4.2 Eliminación.........................................................................................................................................................14 3.4.3 Ejercicios.............................................................................................................................................................14 3.5 DB Link.....................................................................................................................................................................14 3.5.1 Creación..............................................................................................................................................................14 3.5.2 Eliminación.........................................................................................................................................................16 4 PL/SQL básico.................................................................................................................................................................17 4.1 Introducción..............................................................................................................................................................17 4.2 Bloque anónimo PL/SQL.........................................................................................................................................17 4.3 Identificadores..........................................................................................................................................................17 4.4 Variables...................................................................................................................................................................17 4.4.1 Tipos de variables................................................................................................................................................17 4.4.2 Variables locales.................................................................................................................................................18 4.4.3 Variables globales...............................................................................................................................................18 4.5 Control de flujo.........................................................................................................................................................18 4.6 Bucles.........................................................................................................................................................................19 4.6.1 LOOP..................................................................................................................................................................19 4.6.2 WHILE................................................................................................................................................................19 4.6.3 FOR.....................................................................................................................................................................19 4.7 Cursores....................................................................................................................................................................20 4.8 Excepciones...............................................................................................................................................................21 4.8.1 Excepciones predefinidas ...................................................................................................................................22 4.8.2 Excepciones definidas por el usuario .................................................................................................................23 4.8.3 RAISE_APPLICATION_ERROR......................................................................................................................25 4.9 Procedimientos, funciones, paquetes, disparadores..............................................................................................25 4.9.1 Funciones............................................................................................................................................................25 4.9.2 Procedimientos....................................................................................................................................................30 4.9.3 Paquetes...............................................................................................................................................................31 4.9.4 Disparadores........................................................................................................................................................33 4.9.5 Ejercicios.............................................................................................................................................................36 5 SQL Loader......................................................................................................................................................................37 6 Respaldo y reposición de la base de datos.......................................................................................................................39 6.1 Respaldos físicos online............................................................................................................................................39 6.1.1 Respaldos en caliente (online).............................................................................................................................39 6.2 Restauración hacía un cierto punto el en pasado..................................................................................................39
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 4 / 40
1 Introducción al curso 1.1 Objetivo de este curso Este curso brindará al alumno el conocimiento necesario para administrar una base de datos Oracle 10g.
1.2 Manual del alumno Este manual del alumno es una ayuda para el alumno, para tenga un recuerdo del curso. Este manual contiene un resumen de las materias que se van a estudiar durante el curso, pero el alumno debería de tomar notas personales para completas este manual.
1.3 Requisitos para atender a este curso Se requiere un conocimiento del lenguaje SQL.
1.4 Soporte después del curso Si tienes preguntas sobre la materia del curso en tus ejercicios prácticos, puedes escribir tus preguntas a [email protected] .
Respaldos en caliente (online) Reposición de base de datos con aplicación de logs Programación de trabajos de mantenimiento
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 5 / 40
2 DML Sentencias de manipulación de datos 2.1 Objetivo del capítulo Al fin de este capítulo el alumno será capaz de hacer encuestas de la base de datos. No vamos a ver todas las opciones, ni las encuestas de otros objetos de la base de datos (vistas, funciones, secuencias,...) porque eso sale del cuadro de este curso de iniciación. Existen muchas opciones (top, exists, cube,...) para cada tipo de acción, pero estas opciones dependen de la base de datos utilizadas y/o de su versión. Solo vamos a ver las sentencias básicas. Existen muchos entornos que simplifican las encuestas sobre los datos.
2.2 Insert La sentencia ‘Insert’ permite de insertar datos en una tabla. INSERT INTO <nombre_de_tabla> (,,<...>) VALUES (,,);
También existe: INSERT INTO <nombre_de_tabla> (,,<...>) <SELECT STATEMENT>;
2.3 Update La sentencia ‘Update’ permite de modificar el valor de uno o varios datos en una tabla. UPDATE <nombre_de_tabla> SET=,=,<...>;
De costumbre se limita el cambio a ciertos registros, mencionados utilizando una cláusula WHERE. UPDATE <nombre_de_tabla> SET=,=,<...> WHERE ;
2.4 Delete La sentencia ‘Delete’ permite de borrar un uno o varios registros en una tabla. DELETE FROM <nombre_de_tabla> ;
De costumbre se limita el borrado a ciertos registros, mencionados utilizando una cláusula WHERE. DELETE FROM <nombre_de_tabla>
WHERE;
2.5 Commit y rollback Si la base de datos permite la gestión de transacciones, se puede utilizar ‘Commit’ para confirmar una ‘Insert’, ‘Update’, o ‘Delete’, o `Rollback` para cancelarlos. Ciertas base de datos pueden ser confiuradas
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 6 / 40
para autocommit, que hace un commit automaticamente despues de cada instrucción, a menos que se ha iniciado una transacción de manera explicita (con 'begin transaction xxx;). Hasta que el ‘Commit’ está ejecutado, las modificaciones no están inscritas de manera permanente en la base de datos, y sólo son visible para la sesión en curso del usuario autor de las acciones. Después del ‘Commit’, los cambios son definitivos y visible para todos. Cuidado que ciertos objetos pueden quedar bloqueados (bloqueando otros usuarios) hasta que el commit sea hecho. El commit/rollback permite confirmar o de hacer un lote de transacción, para que si una falle, todas las anteriores se anulan también. Cuando se necesita una integridad de transacción, se utiliza en commit/rollback. Ejemplo: SELECT emp_no,job_grade FROM employee START TRANSACTION; update employee set job_grade=5 where SELECT emp_no,job_grade FROM employee rollback; SELECT emp_no,job_grade FROM employee START TRANSACTION; update employee set job_grade=5 where SELECT emp_no,job_grade FROM employee commit; SELECT emp_no,job_grade FROM employee
where emp_no=45; emp_no=45; where emp_no=45; where emp_no=45; emp_no=45; where emp_no=45; where emp_no=45;
2.6 Select El ‘Select’ permite de seleccionar datos en la base de datos, y visualizarlos. Se puede utilizar un alias para que el campo se pueda llamar con otro nombre. SELECT,,<...> FROM <nombre_tabla>; SELECT as ,,<...> FROM <nombre_tabla>;
Para seleccionar todos los campos de la tabla, se utiliza el asterisco en vez de los nombres de campo. SELECT * FROM <nombre_tabla>; Ejemplo: SELECT emp_no,job_grade as nivel FROM employee; SELECT * FROM employee;
2.7 Where La cláusula ‘Where’ permite de limitar la encuesta a ciertos datos. Se utiliza evaluando un campo versus una condición. Se pueden utilizar varias condiciones, con el uso de ‘Or’, ‘And’, y/o paréntesis. Para compara números, se utiliza el signo '=', o '<', o'>', o'<=', o '>=', o 'between ... and ...'. Para comparar caracteres se utiliza la palabra 'like'. El wildcard es '%'. Para compara fecha, se utiliza el signo '=', o '<', o'>', o'<=', o '>=', o 'between ... and ...'. Para SELECT * FROM <nombre_tabla> WHERE AND ;
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Ejemplo: SELECT emp_no,job_grade FROM SELECT emp_no,job_grade FROM SELECT * FROM employee where SELECT * FROM employee where
employee where employee where emp_no between last_name like
Pagina 7 / 40
emp_no>45; emp_no=45 or 40 and 45; 'P%';
emp_no=41;
2.8 Count Para contar un numero de registros, se utiliza la palabra ‘Count’. SELECT COUNT() FROM <nombre_tabla>; Ejemplo: SELECT count(*) FROM employee where job_grade=4;
2.9 Sum, avg, min, max Para una suma, min, max,... de un campo, se utilizan la palabras ‘Sum’, ‘Min’, ‘Max’, ‘Avg’. SELECT SUM() FROM <nombre_tabla>; Ejemplo: SELECT avg(salary) FROM employee where job_grade=2;
2.10Distinct Para tener la lista de valores distingas de un campo, se utiliza la palabra ‘Distinct’. SELECT DISTINCT() FROM <nombre_tabla>; Ejemplo: SELECT distinct(job_grade) FROM employee;
2.11Order by Para ordenar los registros regresados, hay que utilizar la palabre ‘Order by’. SELECT * FROM <nombre_tabla> ORDER BY,<....>; Ejemplo: SELECT first_name,last_name FROM employee order by first_name,last_name;
2.12Uniones Uniones permiten de unir los resultados de dos consultas. Para poder unirlas, tienen que tener los mismos campos. SELECT,,<...> FROM <nombre_tabla_1> UNION SELECT ,,<...> FROM <nombre_tabla_2>; Ejemplo: select t.first_name,t.last_name from employee t where job_grade=5 union select t2.fname,t2.lname from usuario t2;
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 8 / 40
2.13Subconsultas Subconsultas son consultas sobre otras consultas. La subconsulta se puede utilizar el la cáusula ‘From’, o el la condición de la clásula ‘Where’. La subconsulta se pone entre paréntesis. En MySQL, las subconsultas deben tener sus propios alias. SELECT t3., t3. FROM (SELECT t., t. <where cluase>) t3 WHERE t3. IN (SELECT t2. FROM <nombre_tabla_2> t2);
FROM <nombre_tabla > t
Ejemplo: SELECT t3.first_name,t3.last_name FROM ( select t.first_name,t.last_name from employee t where job_grade=5 union select t2.fname,t2.lname from usuario t2 ) t3 where t3.last_name like '%o%'; SELECT t3.first_name,t3.last_name FROM employee t3 where t3.job_country IN (select t.country from country t where t.currency='Euro');
2.14Agrupaciones Las agrupaciones permiten agrupar datos y saber cuantos datos hay de cada valor. SELECT,, COUNT(*) FROM <nombre_tabla> GROUP BY ,;
Las agrupaciones se pueden filtrar utilizando la clausula HAVING. Ejemplo: SELECT job_grade, count(*) FROM employee where emp_no>45 group by job_grade; SELECT job_grade, sum(salary) FROM employee where emp_no>45 group by job_grade having sum(salary)<1000000;
2.15Operadores SQL Ya hemos visto anteriormente qué tipos de datos se pueden utilizar en Oracle. Y siempre que haya datos, habrá operaciones entre ellos, así que ahora se describirán qué operaciones y con qué operadores se realizan: Los operadores se pueden dividir en TRES conjuntos: ● Aritméticos: utilizan valores numéricos ● Lógicos (o booleanos o de comparación): utilizan valores booleanos o lógicos. ● Concatenación: para unir cadenas de caracteres. Operadores arítméticos Retornan un valor numérico: Símbo lo
Significado
Ejemplo
+
Operación suma
1 + 2
Operación resta
1 2
*
Operación multiplicación
1 * 2
/
Operador división
1 / 2
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 9 / 40
Operadores lógicos Retornan un valor lógico (verdadero o falso) Símbolo
Significado
Ejemplo
=
Igualdad
1 = 2
!= <> ^=
Desigualdad
1 != 2 1 <> 2 1 ^= 2
>
Mayor que
1 > 2
<
Menor que
1 < 2
>=
Mayor o igual que
1 >= 2
<=
Menor o igual que
1 <= 2
IN (RS)
Igual a algún elemento del result set.
1 IN (1,2) [TRUE]
ANY SOME
a algún elemento del result set (derecha). Debe ser estar 10 >= ANY (1,2,3,10) precedido por =, !=, <, <=, >, >= Hace un OR lógico entre todos [TRUE] los elementos.
ALL
a todos los elementos del result set (derecha), Debe ser estar 10 <= ALL (1,2,3,10) precedido por =, !=, <, <=, >, >= Hace un AND lógico entre todos [TRUE] los elementos.
BEETWEEN x Operando de la izquierda entre x e y. Equivalente a op >= x AND y AND op <= y
10 BETWEEN 1 AND 100
EXISTS
Si la retorna al menos una fila
EXISTS( SELECT 1 FROM DUAL)
LIKE(*)
Es como
'pepe' LIKE 'pe%'
IS NULL
Si es nulo
1 IS NULL
IS NOT NULL Si es No nulo
1 IS NOT NULL
NOT cond.
NOT EXISTS… NOT BETWEEN NOT IN NOT =
Niega la condición posterios
cond AND cond Hace un AND lógico entre dos condiciones
1=1 AND 2 IS NULL
Cond OR cond Hace un OR lógico entre dos condiciones
1=1 OR 2 IS NULL
Existen los siguientes comodines: ● %: Conjunto de N caracteres (de 0 a ∞) ● _: Un solo carácter Concatenación
Oracle puede hacer una conversión automática cuando se utilice este operador con valores numéricos: 10 || 20 = '1020' Este proceso de denomina CASTING y se puede aplicar en todos aquellos casos en que se utiliza valores numéricos en puesto de valores alfanuméricos o incluso viceversa.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 10 / 40
2.16La ausencia de valor: NULL Todo valor (sea del tipo que sea) puede contener el valor NULL que no es más que la ausencia de valor. Así que cualquier columna (NUMBER, VARCHAR2, DATE…) puede contener el valor NULL, con lo que se dice que la columna está a NULL. Una operación retorna NULL si cualquiera de los operandos es NULL. Para comprobar si una valor es NULL se utiliza el operador IS NULL o IS NOT NULL.
2.17Rendimiento Un problema común en las encuesta a base de datos es el rendimiento. Las causas de problema de rendimiento son numerosas. Las más comunes son: ● Instrucción sin o con mala clausula WHERE ● Falta de indice sobre un campo utilizado como filtro ● Mal diseño de la base de datos ● Problema de hardware (falta de memoria, disco ocupado, cpu ocupado por otra aplicación,...) ● Mala configuración del servidor (mal uso de la memoria, disco, cpu,...) ● Mala programación en el cliente. Falta de commit, conexión no cerrada, ... ● Red sobrecargada o muy lenta Cuando se enfrenta a un problema de rendimiento hay que probar primero de identificar la causa y los síntomas. Servidor sobrecargado en CPU, disco, memoria? Un cliente afectado o todos? Cuando aparece el problema? Para ayudar a investigar estos problemas existen herramientas. Algunos vienen con la base de datos, otros están desarrollados aparte. Ver la documentación de su base de datos para mas información.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 11 / 40
3 Creación de objetos 3.1 Vistas La vista es una sentencia de selección de datos preparada. Permite facilitar las consultas futuras, especialmente cuando se juntan varias tablas. Permite también limitar el acceso a datos (seguridad). 3.1.1
Creación
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [schema.]view [(alias,...) inline_constraint(s)] [out_of_line_constraint(s)] [XMLType_view_clause] AS subquery options options: WITH READ ONLY WITH CHECK OPTION [CONSTRAINT constraint]
3.1.2 ALTER ALTER ALTER ALTER ALTER ALTER
Modificación VIEW VIEW VIEW VIEW VIEW VIEW
[schema.]view [schema.]view [schema.]view [schema.]view [schema.]view [schema.]view
COMPILE; ADD out_of_line_constraint; MODIFY CONSTRAINT constraint {RELY | NORELY}; DROP CONSTRAINT constraint; DROP PRIMARY KEY UNIQUE (column [,column,...])
When a constraint is in NOVALIDATE mode, Oracle does not enforce it and does not take it into account for query rewrite. If you specify RELY Oracle will still not enforce the constraint but will take it into account for query rewrite. An alternative to ALTER VIEW COMPILE is the builtin pl/sql package DBMS_UTILITY 3.1.3
Eliminación
DROP VIEW [schema.]view [CASCADE CONSTRAINTS]
3.1.4
Ejercicios
1. Crea una vista sobre 2 tablas ligadas. 2. Usa la vista en una consulta.
3.2 Secuencias Una secuencia es un contador que se incrementa automáticamente y permite generar numero únicos. 3.2.1
Creación
CREATE SEQUENCE [schema.]sequence_name option(s) Options: INCREMENT BY int START WITH int MAXVALUE int | NOMAXVALUE MINVALUE int | NOMINVALUE CYCLE | NOCYCLE
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 12 / 40
CACHE int | NOCACHE ORDER | NOORDER
3.2.2
Modificación
ALTER SEQUENCE [schema.]sequence_name option(s) Options: INCREMENT BY int MAXVALUE int | NOMAXVALUE MINVALUE int | NOMINVALUE CYCLE | NOCYCLE CACHE int | NOCACHE ORDER | NOORDER
3.2.3
Eliminación
DROP SEQUENCE [schema.]sequence_name
3.2.4
Ejercicios
1. Crea una secuencia 2. Altera la secuencia para que el próximo numero generado sea 20
3.3 Vistas materializadas Una vista materializada es un conjunto de datos de una o varias tablas (como una vista) pero del cual el dato se guarda físicamente. Aumenta el rendimiento de las consultas en comparación de la vista normal, pero disminuye el rendimiento de las consultas DML sobre las tablas ligadas a la vista materializada, ya que tiene que mantener la vista materializada ademas de la tabla fuente. 3.3.1
Creación
CREATE MATERIALIZED VIEW [schema.]mview Mview_Options [USING INDEX storage_options] [{REFRESH [refresh_options] | NEVER REFRESH] [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE] AS subbquery; storage_options: PCTFREE int PCTUSED int INITRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace refresh_options: FAST | COMPLETE | FORCE ON [DEMAND | COMMIT] {NEXT | START WITH} date WITH {PRIMARY KEY | ROWID} USING DEFAULT {MASTER|LOCAL} ROLLBACK SEGMENT USING {MASTER|LOCAL} ROLLBACK SEGMENT rb_segment idx_organized_tbl_clause: storage_option(s) {MAPPING TABLE | NOMAPPING} [PCTTHRESHOLD int] [COMPRESS int|NOCOMPRESS] [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ] external_table_clause: ([TYPE access_driver_type]
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 13 / 40
DEFAULT DIRECTORY directory [ACCESS PARAMETERS {USING CLOB subquery | (opaque_format_spec) }] LOCATION (directory:'location_specifier' [,directory2:'location_specifier2'...) ) [REJECT LIMIT {int|UNLIMITED}] nested_storage_clause: NESTED TABLE {nested_item | COLUMN_VALUE} [ [ELEMENT] IS OF TYPE (ONLY type) ]] | STORE AS storage_table [RETURN AS {LOCATOR|VALUE} ]
3.3.2
[ [NOT] SUBSTITUTABLE AT ALL LEVELS ]]
Modificación
ALTER MATERIALIZED VIEW [schema.]mview options iot_options [USING INDEX index_options] [REFRESH [refresh_options]] [COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE] ALTER MATERIALIZED VIEW [schema.]mview options iot_options [USING INDEX index_options] [REBUILD] [COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE] ALTER MATERIALIZED VIEW [schema.]mview options iot_options [USING INDEX index_options] MODIFY SCOPE FOR (ref_column/attribute) IS [schema.]scope_table [COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE] Options: COMPRESS|NOCOMPRESS CACHE | NOCACHE PARALLEL int | NOPARALLEL ALLOCATE EXTENT [( [DATAFILE filename] [, SIZE int {K | M}] [, INSTANCE int] )] Partitioning clause Physical_options LOB Clause
3.3.3
Eliminación
DROP MATERIALIZED VIEW [schema.] materialized_view
Snapshot is synonymous with Materialized View. Para más información sobre las vistas materializadas, ver en http://www.softics.ru/docs/oracle10r2/server.101/b10759/statements_6002.htm
3.4 Sinónimos Un sinónimo es un nombre de objeto que refiere a un objeto con otro nombre, o que se pueda encontrar en otro esquema. Un sinónimo public es disponible para todos los usuarios (según sus privilegios). 3.4.1
Creación
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object [@dblink] 'PUBLIC' will create a public synonym, accessible to all users (with the appropriate privileges.) Unlike Views, Synonyms do not need to be recompiled when the underlying table is redefined. There is a small performance hit when accessing data through a public synonym.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 14 / 40
Oracle will resolve object names in the following order: current user private synonym public synonym An alternative method to access data in another schema is to use: ALTER SESSION set current_schema = Other_Schema
3.4.2
Eliminación
DROP [PUBLIC] SYNONYM [schema.]synonym [FORCE] FORCE will drop the synonym even there are dependent tables or user-defined types.
3.4.3
Ejercicios
1. Crea un sinónimo publico para un objeto. Luego conecta te como otro usuario y consulta el objeto via el sinónimo público.
3.5 DB Link Un DB Link permite llamar a objetos que se encuentran en otra base de datos (onlcuido en un servidor remoto). Hay que tener cuidado son el rendimiento ligado a consultas remotas, ya que puede impactar de manera significativa en servidor remoto. 3.5.1
Creación
CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password [ dblink_authentication ] } | dblink_authentication ] [ USING 'connect_string' ] ; dblink_authentication: AUTHENTICATED BY user IDENTIFIED BY password
Specify SHARED to use a single network connection to create a public database link that can be shared among multiple users. Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you. Dblink: Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database. Restriction on Creating Database Links You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 15 / 40
entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.) The CONNECT TO clause lets you enable a connection to the remote database. Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user. When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott. However, if the stored object is an invokerrights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invokerrights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges. user IDENTIFIED BY password : Specify the username and password used to connect to the remote database using a fixed user database link. If you omit this clause, the database link uses the username and password of each user who is connected to the database. This is called a connected user database link. dblink_authentication Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user. You must specify this clause when using the SHARED clause. USING 'connect string': Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name. 3.5.1.1
Examples
The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different. Defining a Public Database Link: Example The following statement defines a shared public database link named remote that refers to the database specified by the service name remote: CREATE PUBLIC DATABASE LINK remote USING 'remote';
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 16 / 40
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges): UPDATE employees@remote SET salary=salary*1.1 WHERE last_name = 'Baer';
Defining a FixedUser Database Link: Example In the following statement, user hr on the remote database defines a fixeduser database link named local to the hr schema on the local database: CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';
Once this database link is created, hr can query tables in the schema hr on the local database in this manner: SELECT * FROM employees@local; User hr can also use DML statements to modify data on the local database: INSERT INTO employees@local (employee_id, last_name, email, hire_date, job_id) VALUES (999, 'Claus', '[email protected]', SYSDATE, 'SH_CLERK'); UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK'; DELETE FROM employees@local WHERE employee_id = 999;
Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table: SELECT * FROM oe.customers@local;
Defining a CURRENT_USER Database Link: Example The following statement defines a currentuser database link to the remote database, using the entire service name as the link name: CREATE DATABASE LINK remote.us.oracle.com CONNECT TO CURRENT_USER USING 'remote';
The user who issues this statement must be a global user registered with the LDAP directory service. You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database: CREATE SYNONYM emp_table FOR [email protected];
3.5.2
Eliminación
DROP [ PUBLIC ] DATABASE LINK dblink
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 17 / 40
4 PL/SQL básico 4.1 Introducción El PL/SQL es un lenguaje de programación de Oracle, que permite que el código sea ejecutado en el servidor Oracle mismo. Para códigos que necesitan muchas manipulaciones de datos, el PL/SQL es muy eficiente ya que se ejecuta localmente, con acceso directo a los datos. El PL/SQL no es CASESENSITIVE, es decir, no diferencia mayúsculas de minúsculas como otros lenguajes de programación como C o Java. Sin embargo debemos recordar que ORACLE es CASE SENSITIVE en la búsquedas de texto (hasta la versión 10.2R2 que lo permite mediante nuevas valores para variables NLS_SORT y NLS_COMP). Las instructiones PL/SQL terminan con un puntocoma (;), excepto algunas instrucciones con BEGIN, DECLARE, etc.. Los comentarios de una línea se inician con – y los de varias líneas inician con /* y terminan con */ Este capítulo esta en gran parte basado sobre el curso de PL/SQL disponible en http://www.devjoker.com/contenidos/TutorialPLSQL/66/SQLDinamico.aspx
4.2 Bloque anónimo PL/SQL Un bloque PL/SQL es un conjunto de instrucciones PL/SQL relacionadas entre ellas. Un simple bloque PL/SQL inicia con BEGIN y termina con END; Ejemplo: SET SERVEROUTPUT ON; BEGIN dbms_output.put_line('Hola'); END; /
4.3 Identificadores Un identificador es el nombre de un objeto PL/SQL, como por ejemplo una constante, variable, excepción, paquete, función, procedimiento, tabla, cursor,... Un identificador puede tener hasta 30 caracteres, debe iniciar con una letra.
4.4 Variables Las variables se declaran antes de la palabra BEGIN, y se preceden de la palabra DECLARE. La sintaxis es : nombre_de_variable [CONSTANT] tipo [NOT NULL] [DEFAULT valor_por_defecto]; 4.4.1
Tipos de variables
Los tipos de variables que se pueden usar en PL/SQL son básicamente los mismos que se usan para definir campos en una tabla, plus algunos específicos del PL/SQL. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 18 / 40
PLS_INTEGER: tipo numérico del PL/SQL, equivalente a NUMBER, pero más eficiente si se necesita hacer cálculos aritméticos. %TYPE: refiere de manera dinámica a otro objeto. 4.4.2
Variables locales
Por defecto, una variable solo está disponible en el bloque PL/SQL donde se encuentra, y los sub bloques dentro de este bloque. Ejemplo: DECLARE v_cnt_emp number; BEGIN select count(*) into v_cnt_emp from scott.emp where sal>1000; BEGIN dbms_output.put_line ('Empleados con salario>1000 : '||v_cnt_emp); END; END; /
4.4.3
Variables globales
Las variables globales son variables que se pueden llamar desde otro código. Solo están disponibles en paquetes.
4.5 Control de flujo Se usa el IF..ELSIF..ELSE para controlar el flujo en PL/SQL. IF (expresion) THEN Instrucciones ELSIF (expresion) THEN Instrucciones ELSE Instrucciones END IF; Ejemplo: DECLARE v_cnt_emp number; v_tot_emp number; BEGIN select count(*) into v_tot_emp from scott.emp; dbms_output.put_line ('Hay un total de '||v_tot_emp||' empleados.'); select count(*) into v_cnt_emp from scott.emp where sal>1000; dbms_output.put_line ('Hay '||v_cnt_emp||' empleados que ganan mas de 1000 $.'); dbms_output.put_line ('Resultado de la analisis.'); IF (v_tot_emp=v_cnt_emp) then dbms_output.put_line ('Los empleados ganan todos mas de 1000 $'); ELSIF (v_tot_emp
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 19 / 40
END IF; END; /
4.6 Bucles Una bucle permite repetir una acción un sin número de veces. Hay que tener cuidado en no crear bucles infinitas. En PL/SQL tenemos a nuestra disposición los siguientes iteradores o bucles: * LOOP * WHILE * FOR 4.6.1
LOOP
El bucle LOOP, se repite tantas veces como sea necesario hasta que se fuerza su salida con la instrucción EXIT. Su sintaxis es la siguiente LOOP Instrucciones IF (expresion) THEN Instrucciones EXIT; END IF; END LOOP; Ejemplo: DECLARE v_cnt PLS_INTEGER DEFAULT 0; BEGIN LOOP v_cnt:=v_cnt+1; dbms_output.put_line ('Contador = '||v_cnt); IF (v_cnt>=10) then dbms_output.put_line ('Ya merito!'); exit; END IF; END LOOP; END;
4.6.2
WHILE
El bucle WHILE, se repite mientras que se cumpla expresion. WHILE (expresion) LOOP Instrucciones END LOOP; Ejemplo: DECLARE v_cnt PLS_INTEGER DEFAULT 0; BEGIN v_cnt:=10; WHILE (v_cnt>0) LOOP dbms_output.put_line ('Contador = '||v_cnt); v_cnt:=v_cnt-1; END LOOP; END;
4.6.3
FOR
El bucle FOR, se repite tanta veces como le indiquemos en los identificadores inicio y final. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 20 / 40
FOR contador IN [REVERSE] inicio..final LOOP Instrucciones END LOOP;
En el caso de especificar REVERSE el bucle se recorre en sentido inverso. Ejemplo:
DECLARE v_cnt PLS_INTEGER DEFAULT 0; BEGIN FOR v_cnt IN REVERSE 1..10 LOOP dbms_output.put_line ('Contador = '||v_cnt); END LOOP; END;
4.7 Cursores PL/SQL utiliza cursores para gestionar las instrucciones SELECT. Un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente los cursores son fragmentos de memoria que reservados para procesar los resultados de una consulta SELECT. Podemos distinguir dos tipos de cursores: * Cursores implicitos. Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta devuelve un único registro. * Cursores explicitos. Son los cursores que son declarados y controlados por el programador. Se utilizan cuando la consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que devuelven un único registro por razones de eficiencia. Son más rápidos. Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de acuerdo a los mismos convenios que cualquier otra variable. Los cursores implicitos no necesitan declaración. Para procesar instrucciones SELECT que devuelvan más de una fila, son necesarios cursores explicitos combinados con un estructura de bloque. Un cursor admite el uso de parámetros. Los parámetros deben declararse junto con el cursor. El siguiente diagrama representa como se procesa una instrucción SQL a través de un cursor.
Ejemplo de cursor explicito: DECLARE CURSOR c_emp IS /*CURSOR*/ select ename, sal from scott.emp; BEGIN FOR fila IN c_emp LOOP /*no es necesario definir la variable fila, será de tipo %ROW */ dbms_output.put_line(fila.ename||' tiene un salario de '||fila.sal); END LOOP;
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 21 / 40
END;
Ejemplo de cursor implicito:
DECLARE v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM scott.emp WHERE empno=7369; dbms_output.put_line('El empleado numero 7369 tiene un salario de '||v_sal||' $'); end;
4.8 Excepciones En PL/SQL una advertencia o condición de error es llamada una excepción. Los bloques de excepciones permiten atrapar errores de ejecución y darles eventualmente un tratamiento para evitar que se pare el programa de manera anormal. Las excepciones se controlan dentro de su propio bloque. La estructura de bloque de una excepción se muestra a continuación. DECLARE Declaraciones BEGIN Ejecucion EXCEPTION Excepcion END; Cuando ocurre un error, se ejecuta la porción del programa marcada por el bloque EXCEPTION, transfiriéndose el control a ese bloque de sentencias. El siguiente ejemplo muestra un bloque de excepciones que captura las excepciones NO_DATA_FOUND y ZERO_DIVIDE. Cualquier otra excepcion será capturada en el bloque WHEN OTHERS THEN. DECLARE Declaraciones BEGIN Ejecucion EXCEPTION WHEN NO_DATA_FOUND THEN Se ejecuta cuando ocurre una excepcion de tipo NO_DATA_FOUND WHEN ZERO_DIVIDE THEN Se ejecuta cuando ocurre una excepcion de tipo ZERO_DIVIDE WHEN OTHERS THEN Se ejecuta cuando ocurre una excepcion de un tipo no tratado en los bloques anteriores END; Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el bloque anterior. Si existe un bloque de excepción apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo de excepción se ejecutará el bloque de excepción WHEN OTHERS THEN (si existe!). WHEN OTHERS debe ser el último manejador de excepciones. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 22 / 40
Las excepciones pueden ser definidas en forma interna o explícitamente por el usuario. Ejemplos de excepciones definidas en forma interna son la división por cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos: ZERO_DIVIDE y STORAGE_ERROR. Las excepciones definidas por el usuario deben ser alcanzadas explícitamente utilizando la sentencia RAISE. Con las excepciones se pueden manejar los errores cómodamente sin necesidad de mantener múltiples chequeos por cada sentencia escrita. También provee claridad en el código ya que permite mantener las rutinas correspondientes al tratamiento de los errores de forma separada de la lógica del negocio. 4.8.1
Excepciones predefinidas
PL/SQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales. Las excepciones predefinidas no necesitan ser declaradas. Simplemente se utilizan cuando estas son lanzadas por algún error determinado. 4.8.1.1
Excepciones asociadas a los cursores implícitos.
Los cursores implicitos sólo pueden devolver una fila, por lo que pueden producirse determinadas excepciones. Las más comunes que se pueden encontrar son no_data_found y too_many_rows. La siguiente tabla explica brevemente estas excepciones. NO_DATA_FOUND Se produce cuando una sentencia SELECT intenta recuperar datos pero ninguna fila satisface sus condiciones. Es decir, cuando "no hay datos" TOO_MANY_ROWS Dado que cada cursor implicito sólo es capaz de recuperar una fila , esta excepcion detecta la existencia de más de una fila. Ejemplo:
DECLARE v_sal NUMBER; BEGIN BEGIN SELECT sal INTO v_sal FROM scott.emp WHERE empno=1; dbms_output.put_line('El empleado numero 1 tiene un salario de '||v_sal||' $'); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('El empleado numero 1 no existe!'); END; BEGIN SELECT empno INTO v_sal FROM scott.emp WHERE sal>1000; dbms_output.put_line('El empleado que tiene un salario>1000 $ es el empleado numero'||v_sal); EXCEPTION WHEN too_many_rows THEN dbms_output.put_line('HAY MAS DE UN EMPLEADO QUE GANA MAS DE 1000 $!'); END; END;
4.8.1.2
Lista de excepciones predefinidas
ACCESS_INTO_NULL inicializado 6530 COLLECTION_IS_NULL inicializada 6531
© Copyright Cédric Simon, 2008
El programa intentó asignar valores a los atributos de un objeto no El programa intentó asignar valores a una tabla anidada aún no
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 23 / 40
CURSOR_ALREADY_OPEN El programa intentó abrir un cursor que ya se encontraba abierto. Recuerde que un cursor de ciclo FOR automáticamente lo abre y ello no se debe especificar con la sentencia OPEN 6511 DUP_VAL_ON_INDEX El programa intentó almacenar valores duplicados en una columna que se mantiene con restricción de integridad de un índice único (unique index) 1 INVALID_CURSOR El programa intentó efectuar una operación no válida sobre un cursor 1001 INVALID_NUMBER En una sentencia SQL, la conversión de una cadena de caracteres hacia un número falla cuando esa cadena no representa un número válido 1722 LOGIN_DENIED El programa intentó conectarse a Oracle con un nombre de usuario o password inválido 1017 NO_DATA_FOUND Una sentencia SELECT INTO no devolvió valores o el programa referenció un elemento no inicializado en una tabla indexada 100 NOT_LOGGED_ON El programa efectuó una llamada a Oracle sin estar conectado 1012 PROGRAM_ERROR PL/SQL tiene un problema interno 6501 ROWTYPE_MISMATCH Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) tienen tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado 6504 SELF_IS_NULL El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo 30625 STORAGE_ERROR La memoria se terminó o está corrupta 6500 SUBSCRIPT_BEYOND_COUNT El programa está tratando de referenciar un elemento de un arreglo indexado que se encuentra en una posición más grande que el número real de elementos de la colección 6533 SUBSCRIPT_OUTSIDE_LIMIT El programa está referenciando un elemento de un arreglo utilizando un número fuera del rango permitido (por ejemplo, el elemento “1”) 6532 SYS_INVALID_ROWID La conversión de una cadena de caracteres hacia un tipo rowid falló porque la cadena no representa un número 1410 TIMEOUT_ON_RESOURCE Se excedió el tiempo máximo de espera por un recurso en Oracle 51 TOO_MANY_ROWS Una sentencia SELECT INTO devuelve más de una fila 1422 VALUE_ERROR Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, sucede cuando se intenta calzar un valor muy grande dentro de una variable más pequeña 6502 ZERO_DIVIDE El programa intentó efectuar una división por cero 1476
4.8.2
Excepciones definidas por el usuario
PL/SQL permite al usuario definir sus propias excepciones, las que deberán ser declaradas y lanzadas explícitamente utilizando la sentencia RAISE. Las excepciones deben ser declaradas en el segmento DECLARE de un bloque, subprograma o paquete. Se declara una excepción como cualquier otra variable, asignandole el tipo EXCEPTION. Las mismas reglas de alcance aplican tanto sobre variables como sobre las excepciones. DECLARE Declaraciones MyExcepcion EXCEPTION; BEGIN Ejecucion EXCEPTION Excepcion END; © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
4.8.2.1
Pagina 24 / 40
Reglas de Alcance
Una excepcion es válida dentro de su ambito de alcance, es decir el bloque o programa donde ha sido declarada. Las excepciones predefinidas son siempre válidas. Como las variables, una excepción declarada en un bloque es local a ese bloque y global a todos los sub bloques que comprende. 4.8.2.2
La sentencia RAISE
La sentencia RAISE permite lanzar una excepción en forma explícita. Es posible utilizar esta sentencia en cualquier lugar que se encuentre dentro del alcance de la excepción. DECLARE Declaramos una excepcion identificada por VALOR_NEGATIVO VALOR_NEGATIVO EXCEPTION; valor NUMBER; BEGIN Ejecucion valor := 1; IF valor < 0 THEN RAISE VALOR_NEGATIVO; END IF; EXCEPTION Excepcion WHEN VALOR_NEGATIVO THEN dbms_output.put_line('El valor no puede ser negativo'); END; Con la sentencia RAISE podemos lanzar una excepción definida por el usuario o predefinida, siendo el comportamiento habitual lanzar excepciones definidas por el usuario. Recordar la existencia de la excepción OTHERS, que simboliza cualquier condición de excepción que no ha sido declarada. Se utiliza comúnmente para controlar cualquier tipo de error que no ha sido previsto. En ese caso, es común observar la sentencia ROLLBACK en el grupo de sentencias de la excepción o alguna de las funciones SQLCODE – SQLERRM, que se detallan en el próximo punto. 4.8.2.3
Uso de SQLCODE y SQLERRM
Al manejar una excepción es posible usar las funciones predefinidas SQLCode y SQLERRM para aclarar al usuario la situación de error acontecida. SQLcode devuelve el número del error de Oracle y un 0 (cero) en caso de exito al ejecutarse una sentencia SQL. Por otra parte, SQLERRM devuelve el correspondiente mensaje de error. Estas funciones son muy útiles cuando se utilizan en el bloque de excepciones, para aclarar el significado de la excepción OTHERS. Estas funciones no pueden ser utilizadas directamente en una sentencia SQL, pero sí se puede asignar su valor a alguna variable de programa y luego usar esta última en alguna sentencia. DECLARE
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 25 / 40
err_num NUMBER; err_msg VARCHAR2(255); result NUMBER; BEGIN SELECT 1/0 INTO result FROM DUAL; EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SQLERRM; DBMS_OUTPUT.put_line('Error:'||TO_CHAR(err_num)); DBMS_OUTPUT.put_line(err_msg); END;
También es posible entregarle a la función SQLERRM un número negativo que represente un error de Oracle y ésta devolverá el mensaje asociado. DECLARE msg VARCHAR2(255); BEGIN msg := SQLERRM(-1403); DBMS_OUTPUT.put_line(MSG); END;
4.8.3
RAISE_APPLICATION_ERROR
En ocasiones queremos enviar un mensaje de error personalizado al producirse una excepción PL/SQL. Para ello es necesario utilizar la instruccion RAISE_APPLICATION_ERROR; La sintaxis general es la siguiente: RAISE_APPLICATION_ERROR(<error_num>,<mensaje>); Siendo: * error_num es un entero negativo comprendido entre 20001 y 20999 * mensaje la descripcion del error Ejemplo: DECLARE
v_div NUMBER; BEGIN SELECT 1/0 INTO v_div FROM DUAL; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'No se puede dividir por cero'); END;
4.9 Procedimientos, funciones, paquetes, disparadores Son objetos PL/SQL residentes en el servidor PL/SQL. 4.9.1
Funciones
Una función es un código compilados en el servidor, que se ejecutan en local, y que pueden aceptar parámetros de entrada y tiene un solo parámetro de salido. Una función SIEMPRE debe regresar un valor. Una función se puede usar en otro código PL/SQL, o en SQL ya sea en un SELECT, una clausula WHERE, CONNECT BY, START WITH, ORDER BY, GROUP BY, como VALUES en un INSERT, o como SET en un UPDATE.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
4.9.1.1
Pagina 26 / 40
Funciones predefinidas
PL/SQL tiene un gran número de funciones incorporadas, sumamente útiles. A continuación vamos a ver algunas de las más utilizadas. SYSDATE Devuelve la fecha del sistema: SELECT SYSDATE FROM DUAL; NVL Devuelve el valor recibido como parámetro en el caso de que expresión sea NULL,o expresión en caso contrario. NVL(<expresion>,) El siguiente ejemplo devuelve 0 si el precio es nulo, y el precio cuando está informado: SELECT CO_PRODUCTO, NVL(PRECIO, 0) FROM PRECIOS; DECODE Decode proporciona la funcionalidad de una sentencia de control de flujo ifelseifelse. DECODE(<expr>, , [, ..., , ], <default>) Esta función evalúa una expresión "<expr>", si se cumple la primera condición "" devuelve el valor1 "", en caso contrario evalúa la siguiente condición y así hasta que una de las condiciones se cumpla. Si no se cumple ninguna condición se devuelve el valor por defecto. Es muy común escribir la función DECODE identada como si se tratase de un bloque IF. SELECT DECODE (co_pais, /* Expresion a evaluar */ 'ESP', 'ESPAÑA', /* Si co_pais = 'ESP' ==> 'ESPAÑA' */ 'MEX', 'MEXICO', /* Si co_pais = 'MEX' ==> 'MEXICO' */ 'PAIS '||co_pais)/* ELSE ==> concatena */ FROM PAISES; TO_DATE Convierte una expresión al tipo fecha. El parámetro opcional formato indica el formato de entrada de la expresión no el de salida. TO_DATE(<expresion>, []) En este ejemplo convertimos la expresion '01/12/2006' de tipo CHAR a una fecha (tipo DATE). Con el parámetro formato le indicamos que la fecha está escrita como díamesaño para que devuelve el uno de diciembre y no el doce de enero. SELECT TO_DATE('01/12/2006', 'DD/MM/YYYY') FROM DUAL; Este otro ejemplo muestra la conversión con formato de día y hora. SELECT TO_DATE('31/12/2006 23:59:59',
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 27 / 40
'DD/MM/YYYY HH24:MI:SS') FROM DUAL; TO_CHAR Convierte una expresión al tipo CHAR. El parámetro opcional formato indica el formato de salida de la expresión. TO_CHAR(<expresion>, []) SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYYY') FROM DUAL; TO_NUMBER Convierte una expresion alfanumérica en numerica. Opcionalmente podemos especificar el formato de salida. TO_NUMBER(<expresion>, []) SELECT TO_NUMBER ('10') FROM DUAL; TRUNC Trunca una fecha o número. Si el parámetro recibido es una fecha elimina las horas, minutos y segundos de la misma. SELECT TRUNC(SYSDATE)FROM DUAL; Si el parámetro es un número devuelve la parte entera. SELECT TRUNC(9.99)FROM DUAL; LENGTH Devuelve la longitud de un tipo CHAR. SELECT LENGTH('HOLA MUNDO')FROM DUAL; INSTR Busca una cadena de caracteres dentro de otra. Devuelve la posicion de la ocurrencia de la cadena buscada. Su sintaxis es la siguiente: INSTR(, <search_string>, <startpos>, ) SELECT INSTR('AQUI ES DONDE SE BUSCA', 'BUSCA', 1, 1 ) FROM DUAL; REPLACE Reemplaza un texto por otro en un expresion de busqueda. REPLACE(<expresion>, , ) El siguiente ejemplo reemplaza la palabra 'HOLA' por 'VAYA' en la cadena 'HOLA MUNDO'. SELECT REPLACE ('HOLA MUNDO','HOLA', 'VAYA') devuelve VAYA MUNDO © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 28 / 40
FROM DUAL; SUBSTR Obtiene una parte de una expresion, desde una posición de inicio hasta una determinada longitud. SUBSTR(<expresion>, <posicion_ini>, ) SELECT SUBSTR('HOLA MUNDO', 6, 5) Devuelve MUNDO FROM DUAL; UPPER Convierte una expresion alfanumerica a mayúsculas. SELECT UPPER('hola mundo') Devuelve HOLA MUNDO FROM DUAL; LOWER Convierte una expresion alfanumerica a minúsculas. SELECT LOWER('HOLA MUNDO') Devuelve hola mundo FROM DUAL; ROWIDTOCHAR Convierte un ROWID a tipo caracter. SELECT ROWIDTOCHAR(ROWID) FROM DUAL; RPAD Añade N veces una determinada cadena de caracteres a la derecha una expresión. Muy util para generar ficheros de texto de ancho fijo. RPAD(<expresion>, , <pad_string> ) El siguiente ejemplo añade puntos a la expresion 'Hola mundo' hasta alcanzar una longitud de 50 caracteres. SELECT RPAD('Hola Mundo', 50, '.') FROM DUAL; LPAD Añade N veces una determinada cadena de caracteres a la izquierda de una expresión. Muy util para generar ficheros de texto de ancho fijo. LPAD(<expresion>, , <pad_string> ) El siguiente ejemplo añade puntos a la expresion 'Hola mundo' hasta alcanzar una longitud de 50 caracteres. SELECT LPAD('Hola Mundo', 50, '.') FROM DUAL; RTRIM Elimina los espacios en blanco a la derecha de una expresion © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 29 / 40
SELECT RTRIM ('Hola Mundo ') FROM DUAL; LTRIM Elimina los espacios en blanco a la izquierda de una expresion SELECT LTRIM (' Hola Mundo') FROM DUAL; TRIM Elimina los espacios en blanco a la izquierda y derecha de una expresion SELECT TRIM (' Hola Mundo ') FROM DUAL; MOD Devuelve el resto de la división entera entre dos números. MOD(, ) SELECT MOD(20,15) Devuelve el modulo de dividir 20/15 FROM DUAL 4.9.1.2
Funciones definidas por el usuario
La sintaxis para construir funciones es la siguiente: CREATE [OR REPLACE] FUNCTION[(<param1> IN , <param2> IN , ...)] RETURN IS result ; BEGIN return(result); [EXCEPTION] Sentencias control de excepcion END []; El uso de OR REPLACE permite sobreescribir una función existente. Si se omite, y la función existe, se producirá, un error. La sintaxis de los parámetros es la misma que en los procedimientos almacenado, exceptuando que solo pueden ser de entrada. Ejemplo: create table emp as select * from scott.emp; CREATE OR REPLACE FUNCTION fn_Obtener_Salario(p_empno NUMBER) RETURN NUMBER IS result NUMBER; BEGIN SELECT sal INTO result FROM emp WHERE empno = p_empno; return(result); EXCEPTION WHEN NO_DATA_FOUND THEN return 0;
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 30 / 40
END ;
Si el sistema nos indica que el la función se ha creado con errores de compilación podemos ver estos errores de compilacion con la orden SHOW ERRORS en SQL *Plus. Las funciones pueden utilizarse en sentencias SQL de manipulación de datos (SELECT, UPDATE, INSERT y DELETE): SELECT
empno, ename, fn_Obtener_Salario( empno) FROM scott.emp;
4.9.2
Procedimientos
Los procedimientos son códigos compilados en el servidor, que se ejecutan en el servidor, y que pueden aceptar parámetros de entrada y/o de salida. Un procedimiento se puede usar en un select, pero no en un where clause. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código. La sintaxis de un procedimiento almacenado es la siguiente: CREATE [OR REPLACE] PROCEDURE <procedure_name> [(<param1> [IN|OUT|IN OUT], <param2> [IN|OUT|IN OUT] , ...)] IS Declaracion de variables locales BEGIN Sentencias [EXCEPTION] Sentencias control de excepcion END [<procedure_name>]; El uso de OR REPLACE permite sobreescribir un procedimiento existente. Si se omite, y el procedimiento existe, se producirá, un error. La sintaxis es muy parecida a la de un bloque anónimo, salvo porque se reemplaza la sección DECLARE por la secuencia PROCEDURE ... IS en la especificación del procedimiento. Debemos especificar el tipo de datos de cada parámetro. Al especificar el tipo de dato del parámetro no debemos especificar la longitud del tipo. Los parámetros pueden ser de entrada (IN), de salida (OUT) o de entrada salida (IN OUT). El valor por defecto es IN, y se toma ese valor en caso de que no especifiquemos nada. create or replace PROCEDURE Actualiza_Salario(p_empno NUMBER, p_new_salario NUMBER) IS -- Declaracion de variables locales BEGIN -- Sentencias UPDATE emp SET sal = p_new_salario, hiredate = SYSDATE WHERE empno = p_empno;
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 31 / 40
END Actualiza_Salario;
También podemos asignar un valor por defecto a los parámetros, utilizando la clausula DEFAULT o el operador de asiganción (:=) . create or replace PROCEDURE Actualiza_Salario(p_empno NUMBER, p_new_salario NUMBER DEFAULT 500) IS ... END Actualiza_Salario;
Una vez creado y compilado el procedimiento almacenado podemos ejecutarlo. Si el sistema nos indica que el procedimiento se ha creado con errores de compilación podemos ver estos errores de compilación con la orden SHOW ERRORS en SQL *Plus. Existen dos formas de pasar argumentos a un procedimiento almacenado a la hora de ejecutarlo (en realidad es válido para cualquier subprograma). Estas son: * Notación posicional: Se pasan los valores de los parámetros en el mismo orden en que el procedure los define. BEGIN Actualiza_Salario(7369,2500); COMMIT; END;
* Notación nominal:Se pasan los valores en cualquier orden nombrando explicitamente el parámetro. BEGIN Actualiza_Salario(p_empno => 7369,p_new_salario COMMIT; END;
4.9.3
=> 2500);
Paquetes
Un paquete es un conjunto de funciones y/o procedimiento. Permite facilitar la administración de los códigos (agrupaciones), y la seguridad (a nivel de paquete en vez de por función/procedimiento). En el paquete se pueden definir variable de alcance de todo el paquete (global variables). Lo primero que debemos tener en cuenta es que los paquetes están formados por dos partes: la especificación y el cuerpo. La especificación del un paquete y su cuerpo se crean por separado. La especificación es la interfaz con las aplicaciones. En ella es posible declarar los tipos, variables, constantes, excepciones, cursores y subprogramas disponibles para su uso posterior desde fuera del paquete. En la especificación del paquete sólo se declaran los objetos (procedures, funciones, variables ...), no se implementa el código. Los objetos declarados en la especificación del paquete son accesibles desde fuera del paquete por otro script de PL/SQL o programa. Para crear la especificación de un paquete la sintaxis general es la siguiente: CREATE [OR REPLACE] PACKAGE IS Declaraciones de tipos y registros públicas {[TYPE IS ;]} Declaraciones de variables y constantes publicas También podemos declarar cursores {[ CONSTANT := ;]} © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 32 / 40
{[ ;]} Declaraciones de procedimientos y funciones públicas {[FUNCTION (<Parameter> ,...) RETURN ;]} {[PROCEDURE (<Parameter> , ...);]} END ; El cuerpo es la implementación del paquete. El cuerpo del paquete debe implementar lo que se declaró inicialmente en la especificación. Es el donde debemos escribir el código de los subprogramas. En el cuerpo de un package podemos declarar nuevos subprogramas y tipos, pero estos serán privados para el propio package. La sintaxis general para crear el cuerpo de un paquete es muy parecida a la de la especificación, tan solo se añade la palabra clave BODY, y se implementa el código de los subprogramas. CREATE [OR REPLACE] PACKAGE BODY IS Declaraciones de tipos y registros privados {[TYPE IS ;]} Declaraciones de variables y constantes privadas También podemos declarar cursores {[ CONSTANT := ;]} {[ ;]} Implementacion de procedimientos y funciones FUNCTION (<Parameter> ,...) RETURN IS Variables locales de la funcion BEGIN Implementeacion de la funcion return(); [EXCEPTION] Control de excepciones END; PROCEDURE (<Parameter> , ...) IS Variables locales de la funcion BEGIN Implementacion de procedimiento [EXCEPTION] Control de excepciones END; END ; Es posible modificar el cuerpo de un paquete sin necesidad de alterar por ello la especificación del mismo.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 33 / 40
Los paquetes pueden llegar a ser programas muy complejos y suelen almacenar gran parte de la lógica de negocio. Ejemplo:
4.9.4
Disparadores
Los disparadores (triggers). Un disparador es un codigo que dispara cada vez que se ha modificado el dato de una tabla. Puede disparar a nivel de la consulta, o a nivel de cada línea afectada por la consulta. también puede disparar antes o después de la consulta, y solo por ciertos tipos de consulta (insert/update/delete), y eventualmente solo cuando cierto(s) campo(s) estan afectados(s). 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: CREATE [OR REPLACE] TRIGGER <nombre_trigger> {BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN] [OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]} ON <nombre_tabla> [FOR EACH ROW [WHEN ()]] DECLARE variables locales BEGIN Sentencias [EXCEPTION] Sentencias control de excepcion END <nombre_trigger>; 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. INSERT, DELETE, UPDATE Define qué tipo de orden DML provoca la activación del disparador. BEFORE , AFTER Define si el disparador se activa antes o después de que se ejecute la orden.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 34 / 40
FOR EACH ROW Los disparadores con nivel de fila se activan una vez por cada fila afectada por la orden que provocó el disparo. Los disparadores con nivel de orden se activan sólo una vez, antes o después de la orden. Los disparadores con nivel de fila se identifican por la cláusula FOR EACH ROW en la definición del disparador. La cláusula WHEN sólo es válida para los disparadores con nivel de fila. 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 EMP_AUDIT cada vez que modificamos el salario de un registro en la tabla emp: Create table emp_audit (empno number not null, fecha date not null, msg varchar2(500) not null); create or replace TRIGGER TR_EMP_AU AFTER UPDATE ON EMP FOR EACH ROW WHEN (OLD.sal<>NEW.sal) DECLARE -- local variables BEGIN INSERT INTO emp_audit (empno,fecha,msg) VALUES (:NEW.empno,SYSDATE,'Salario modificado de '||:old.sal||' a '||:new.sal); END ;
El trigger se ejecutará cuando sobre la tabla EMP se ejecute una sentencia UPDATE que modifica el salario. Ejemplo: BEGIN
Actualiza_Salario(p_empno => 7369,p_new_salario COMMIT; END;
4.9.4.1
=> 2500);
Orden de ejecución de los triggers
Una 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. 4.9.4.2
Restricciones de los triggers
El 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:
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 35 / 40
* 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. * 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 4.9.4.3
Utilización de :OLD y :NEW
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. La siguiente tabla muestra los valores de OLD y NEW. ACCION SQL OLD NEW INSERT No definido; todos los campos toman valor NULL. Valores que serán insertados cuando se complete la orden. UPDATE Valores originales de la fila, antes de la actualización. Nuevos valores que serán escritos cuando se complete la orden. DELETE Valores, antes del borrado de la fila. No definidos; todos los campos toman el valor NULL. 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 DELETING Dentro 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: INSERTING TRUE si la orden de disparo es INSERT; FALSE en otro caso. UPDATING TRUE si la orden de disparo es UPDATE; FALSE en otro caso. DELETING TRUE si la orden de disparo es DELETE; FALSE en otro caso. Sintaxis completa: CREATE [OR REPLACE] TRIGGER [schema.]trigger BEFORE event [WHEN (condition)] {pl_sql_block | call_procedure_statement} CREATE [OR REPLACE] TRIGGER [schema.]trigger AFTER event
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 36 / 40
[WHEN (condition)] {pl_sql_block | call_procedure_statement} CREATE [OR REPLACE] TRIGGER [schema.]trigger INSTEAD OF event [WHEN (condition)] {pl_sql_block | call_procedure_statement} event can be one or more of the following (separate multiple events with OR) DELETE event_ref referencing_clause INSERT event_ref referencing_clause UPDATE event_ref referencing_clause UPDATE OF column, column... event_ref db/ddl_event ON [schema.object] db/ddl_event ON DATABASE event_ref: ON [schema.]table ON [schema.]view ON [NESTED TABLE nested_table_column OF] [schema.]view referencing_clause: FOR EACH ROW REFERENCING OLD [AS] old [FOR EACH ROW] REFERENCING NEW [AS] new [FOR EACH ROW] REFERENCING PARENT [AS] parent [FOR EACH ROW] db/ddl_event: ALTER ANALYSE ASSOCIATE STATISTICS AUDIT COMMENT CREATE DDL DISASSOCIATE STATISTICS DROP GRANT LOGON LOGOFF NOAUDIT RENAME REVOKE TRUNCATE SERVERERROR STARTUP SHUTDOWN SUSPEND Multiple db/ddl_events can be separated with OR Multiple OLD, NEW and PARENT correlation names can be defined in one REFERENCING clause. Database constraints are a factor of 8x faster than triggers.
4.9.5
Ejercicios
1. Crear una función que agrega 10 al valor pasado en parametro y regresa el resultado 2. Crear un procedimiento que inserta un nuevo empleado 3. Crear un paquete con procedimiento para modificar un empleado, y una función para obtener la fecha de nacimiento del empleado 4. Usar los procedimientos/funciones en select 5. Crear un trigger que genera la clave primaria del empleado basado en una secuencia
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 37 / 40
5 SQL Loader SQL Loader es una herramienta de Oracle que permite cargar de manera muy eficiente (rápida) datos desde un archivo externeo (texto o binario) hacia una tabla de Oracle. Se lanza desde la línea de comando, lo que permite usarlo en script. Se necesita, además del archivo a cargar, un cliente Oracle instalado (de preferencia la misma versión de cliente que el servidor), y un archivo de parámetros (fichero de control). Al SQLLoader ( sqlldr) se le pasan como parámetros (los más importantes) el fichero que contiene los datos que se van a cargar y la ruta del fichero de control que contiene las acciones a realizar. El formato de los datos, donde se cargaran y cualquier otro tipo de control. SYNTAXIS
sqlldr userid=USUARIO/CONTRASEÑA@BASE control=/RUTA/DEL/FICHERO/DE/CONTROL data=/RUTA/DEL/FICHERO/QUE/CONTIENE/LOS/DATOS log=/LO/MISMO bad=/A/DONDE/VAN/LOS/DATOS/MALOS discard=/A/DONDE/VAN/LOS/DATOS/DESCARTADOS
EJEMPLO: sqlldr userid=alumno/123@curso control=/tmp/test.ctl data=/tmp/test.txt
Como puedes ver no es muy complicado. Simplemente te conectas con un usuario de la base de datos y le dices cual es el fichero de texto que contiene la información y cual es el que contiene las especificaciones sobre lo que se va a hacer con esa información Para más información, ver en: http://downloaduk.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm Parámetros Comunes userid=USUARIO/CONTRASEÑA@BASE Usuario, contraseña e instancia a la que te conectas. control=Ruta del archivo de control que contiene las directivas de lo que se va a hacer. data=Ruta del archivo que contiene los datos. log=Ruta del archivo donde quieres que se genere el log. discard=Ruta del archivo donde quieres que te envíe los registros descartados y no cargados. bad=Ruta del fichero donde van a parar los registros malos. Archivo de Control El archivo de control ( Control file en bárbaro ) es donde se especifica cómo se van a cargar los datos. Normalmente suele responder al siguiente esquema: LOAD DATA BADFILE '/a/donde/van/los/datos/malos' DISCARDFILE '/a/donde/van/los/datos/descartados' INFILE '/la/ruta/del/archivo/desde/el/que/se/cargan/los/datos' APPEND INTO TABLE TU_TABLA_DE_DESTINO FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( CAMPO0 POSITION(1:15), CAMPO1 CHAR "ltrim(rtrim(:CAMPO1))", CAMPO2 CHAR "ltrim(rtrim(:CAMPO2))", © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 38 / 40
CAMPO3NUMERICO INTEGER EXTERNAL, CAMPO4FECHA DATE "DDMonthYY" ) Como ves, si los campos tienen una longitud fija puedes especificarle las posiciones, desde el carácter 1 a la 15 y sucesivamente. O si no, puedes dejar que Oracle interprete los campos y los inserte. En este caso está introduciondo datos APPEND al final de la tabla. ( Podría ser REPLACE para que reemplazara datos preexistentes). Como puedes imaginar por defecto cada linea es una fila. Y en este ejemplo cada campo está delimitado por el carácter |. TRAILING NULLCOLS sirve para que interprete que los campos sin contenido sean interpretados como NULOS. Sql Loader y problemas con fechas Si estas cargando datos con fechas es posible que te cause problemas al intentar insertar columnas con fechas y campos nulos. Una manera de evitarlo es borrar los caracteres en blanco del campo de fecha. Ejemplo: campo_de_fecha "to_date (ltrim(rtrim(:campo_de_fecha)), 'yyyy-mm-dd hh24:mi')"
Ejemplo completo: 1) Crear la tabla: create table cim (CX char(2) not null, LANG char(2) not null, CODE varchar2(10) not null, descripcion varchar2(1000) not null);
2) Copiar el archivo de datos “cim10.txt” en /tmp 3) Crear el archivo de control “test.ctl” en /tmp: LOAD DATA INTO TABLE cim TRUNCATE FIELDS TERMINATED BY X'09' TRAILING NULLCOLS ( CX, LANG, CODE, DESCRIPCION )
4) Cargar los datos:
sqlldr userid=alumno/123@core2d control=/tmp/test.ctl data=/tmp/cim-10.txt log=/home/oracle/test.log
5) Validar que los datos han sido cargados
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 39 / 40
6 Respaldo y reposición de la base de datos 6.1 Respaldos físicos online Este tipo de respaldo es la mejor elección para un plan de recuperación después de un desastre. Los respaldos 'online' permiten hacer respaldos mientras el sistema está en uso. Se pueden hacer respaldos diferenciales, o acumulativos. Solo están disponible si la base de datos corre en modo ARCHIVELOG. Este tipo de respaldo/restauración será visto en un curso avanzado y no hace parte de este curso básico. Los respaldos en caliente deben siempre tener como base un respaldo en frío. Sin respaldo correcto y completo en frío, no se podrá hacer una reposición usando los respaldos hechos en caliente. 6.1.1
Respaldos en caliente (online)
Para hacer un respaldo en caliente, cada tablespace debe ser cambiado a modo de backup antes de iniciar el respaldo. Ejemplo: ALTER TABLESPACE xyz BEGIN BACKUP; cp xyfFile1 /backupDir/ ALTER TABLESPACE xyz END BACKUP;
Desde 10g es también posible poner todos los tablespace en modo de backup con un solo comando: ALTER DATABASE BEGIN BACKUP;
No olviden de guardar también una copia de todos los árchive logs' y de los control files. ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf'; cp /camino_hacia_los_logs/*.arc /camino_de_backup/
Si su base de datos termino anormalmente (crash) mientras uno o varios tablespaces estaban en modo de backup, hay que mencionar a Oracle de termniar el modo de backup cuando la base de datos es montada (no abierta). ALTER DATABASE END BACKUP;
6.2 Restauración hacía un cierto punto el en pasado Las restauraciones más fácil son las restauraciones a partir de un respaldo lógico, o de un respaldo 'offline'. En caso de reposición desde un respaldo en caliente, será necesario aplicar los archive logs, para recuperar todas las transacciones ejecutadas (y confirmadas con un commit). Las tablas de FLASHBACK permiten recuperar fácilmente tablas botadas o registros borrados sin necesidad de hace una restauración completa de la base de datos. Desde 10g es muy fácil crear respaldos y restaurar datos usando en Enterprise Manager. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 40 / 40
Ver detalles en : http://examples.oreilly.com/unixbr/oracle.html © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Versión 1.0
© Copyright Cédric Simon, 2008
SolucionJava.com
Manual del alumno
Ing. Cedric Simon – Tel: 2268 0974 – Cel: 8888 2387 – Email: c e d r i c @ s o l u c i o n j a v a . c o m – Web: www.solucionjava.com
Curso de administración de Oracle 10g (10.2) Nivel Intermedio
Curso de administración de Oracle 10g (10.2)
Pagina 2 / 40
1 Índice 1 Índice..................................................................................................................................................................................2 1 Introducción al curso.........................................................................................................................................................4 1.1 Objetivo de este curso................................................................................................................................................4 1.2 Manual del alumno.....................................................................................................................................................4 1.3 Requisitos para atender a este curso........................................................................................................................4 1.4 Soporte después del curso..........................................................................................................................................4 2 DML - Sentencias de manipulación de datos...................................................................................................................5 2.1 Objetivo del capítulo..................................................................................................................................................5 2.2 Insert............................................................................................................................................................................5 2.3 Update.........................................................................................................................................................................5 2.4 Delete...........................................................................................................................................................................5 2.5 Commit y rollback......................................................................................................................................................5 2.6 Select............................................................................................................................................................................6 2.7 Where..........................................................................................................................................................................6 2.8 Count...........................................................................................................................................................................7 2.9 Sum, avg, min, max....................................................................................................................................................7 2.10 Distinct.......................................................................................................................................................................7 2.11 Order by....................................................................................................................................................................7 2.12 Uniones .....................................................................................................................................................................7 2.13 Subconsultas.............................................................................................................................................................8 2.14 Agrupaciones............................................................................................................................................................8 2.15 Operadores SQL ......................................................................................................................................................8 2.16 La ausencia de valor: NULL ................................................................................................................................10 2.17 Rendimiento............................................................................................................................................................10 3 Creación de objetos..........................................................................................................................................................11 3.1 Vistas.........................................................................................................................................................................11 3.1.1 Creación..............................................................................................................................................................11 3.1.2 Modificación.......................................................................................................................................................11 3.1.3 Eliminación.........................................................................................................................................................11 3.1.4 Ejercicios.............................................................................................................................................................11 3.2 Secuencias.................................................................................................................................................................11 3.2.1 Creación..............................................................................................................................................................11 3.2.2 Modificación.......................................................................................................................................................12 3.2.3 Eliminación.........................................................................................................................................................12 3.2.4 Ejercicios.............................................................................................................................................................12 3.3 Vistas materializadas...............................................................................................................................................12 3.3.1 Creación..............................................................................................................................................................12 3.3.2 Modificación.......................................................................................................................................................13 3.3.3 Eliminación.........................................................................................................................................................13 3.4 Sinónimos..................................................................................................................................................................13 3.4.1 Creación..............................................................................................................................................................13 © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 3 / 40
3.4.2 Eliminación.........................................................................................................................................................14 3.4.3 Ejercicios.............................................................................................................................................................14 3.5 DB Link.....................................................................................................................................................................14 3.5.1 Creación..............................................................................................................................................................14 3.5.2 Eliminación.........................................................................................................................................................16 4 PL/SQL básico.................................................................................................................................................................17 4.1 Introducción..............................................................................................................................................................17 4.2 Bloque anónimo PL/SQL.........................................................................................................................................17 4.3 Identificadores..........................................................................................................................................................17 4.4 Variables...................................................................................................................................................................17 4.4.1 Tipos de variables................................................................................................................................................17 4.4.2 Variables locales.................................................................................................................................................18 4.4.3 Variables globales...............................................................................................................................................18 4.5 Control de flujo.........................................................................................................................................................18 4.6 Bucles.........................................................................................................................................................................19 4.6.1 LOOP..................................................................................................................................................................19 4.6.2 WHILE................................................................................................................................................................19 4.6.3 FOR.....................................................................................................................................................................19 4.7 Cursores....................................................................................................................................................................20 4.8 Excepciones...............................................................................................................................................................21 4.8.1 Excepciones predefinidas ...................................................................................................................................22 4.8.2 Excepciones definidas por el usuario .................................................................................................................23 4.8.3 RAISE_APPLICATION_ERROR......................................................................................................................25 4.9 Procedimientos, funciones, paquetes, disparadores..............................................................................................25 4.9.1 Funciones............................................................................................................................................................25 4.9.2 Procedimientos....................................................................................................................................................30 4.9.3 Paquetes...............................................................................................................................................................31 4.9.4 Disparadores........................................................................................................................................................33 4.9.5 Ejercicios.............................................................................................................................................................36 5 SQL Loader......................................................................................................................................................................37 6 Respaldo y reposición de la base de datos.......................................................................................................................39 6.1 Respaldos físicos online............................................................................................................................................39 6.1.1 Respaldos en caliente (online).............................................................................................................................39 6.2 Restauración hacía un cierto punto el en pasado..................................................................................................39
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 4 / 40
1 Introducción al curso 1.1 Objetivo de este curso Este curso brindará al alumno el conocimiento necesario para administrar una base de datos Oracle 10g.
1.2 Manual del alumno Este manual del alumno es una ayuda para el alumno, para tenga un recuerdo del curso. Este manual contiene un resumen de las materias que se van a estudiar durante el curso, pero el alumno debería de tomar notas personales para completas este manual.
1.3 Requisitos para atender a este curso Se requiere un conocimiento del lenguaje SQL.
1.4 Soporte después del curso Si tienes preguntas sobre la materia del curso en tus ejercicios prácticos, puedes escribir tus preguntas a [email protected] .
Respaldos en caliente (online) Reposición de base de datos con aplicación de logs Programación de trabajos de mantenimiento
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 5 / 40
2 DML Sentencias de manipulación de datos 2.1 Objetivo del capítulo Al fin de este capítulo el alumno será capaz de hacer encuestas de la base de datos. No vamos a ver todas las opciones, ni las encuestas de otros objetos de la base de datos (vistas, funciones, secuencias,...) porque eso sale del cuadro de este curso de iniciación. Existen muchas opciones (top, exists, cube,...) para cada tipo de acción, pero estas opciones dependen de la base de datos utilizadas y/o de su versión. Solo vamos a ver las sentencias básicas. Existen muchos entornos que simplifican las encuestas sobre los datos.
2.2 Insert La sentencia ‘Insert’ permite de insertar datos en una tabla. INSERT INTO <nombre_de_tabla> (
También existe: INSERT INTO <nombre_de_tabla> (
2.3 Update La sentencia ‘Update’ permite de modificar el valor de uno o varios datos en una tabla. UPDATE <nombre_de_tabla> SET
De costumbre se limita el cambio a ciertos registros, mencionados utilizando una cláusula WHERE. UPDATE <nombre_de_tabla> SET
2.4 Delete La sentencia ‘Delete’ permite de borrar un uno o varios registros en una tabla. DELETE FROM <nombre_de_tabla> ;
De costumbre se limita el borrado a ciertos registros, mencionados utilizando una cláusula WHERE. DELETE FROM <nombre_de_tabla>
WHERE
2.5 Commit y rollback Si la base de datos permite la gestión de transacciones, se puede utilizar ‘Commit’ para confirmar una ‘Insert’, ‘Update’, o ‘Delete’, o `Rollback` para cancelarlos. Ciertas base de datos pueden ser confiuradas
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 6 / 40
para autocommit, que hace un commit automaticamente despues de cada instrucción, a menos que se ha iniciado una transacción de manera explicita (con 'begin transaction xxx;). Hasta que el ‘Commit’ está ejecutado, las modificaciones no están inscritas de manera permanente en la base de datos, y sólo son visible para la sesión en curso del usuario autor de las acciones. Después del ‘Commit’, los cambios son definitivos y visible para todos. Cuidado que ciertos objetos pueden quedar bloqueados (bloqueando otros usuarios) hasta que el commit sea hecho. El commit/rollback permite confirmar o de hacer un lote de transacción, para que si una falle, todas las anteriores se anulan también. Cuando se necesita una integridad de transacción, se utiliza en commit/rollback. Ejemplo: SELECT emp_no,job_grade FROM employee START TRANSACTION; update employee set job_grade=5 where SELECT emp_no,job_grade FROM employee rollback; SELECT emp_no,job_grade FROM employee START TRANSACTION; update employee set job_grade=5 where SELECT emp_no,job_grade FROM employee commit; SELECT emp_no,job_grade FROM employee
where emp_no=45; emp_no=45; where emp_no=45; where emp_no=45; emp_no=45; where emp_no=45; where emp_no=45;
2.6 Select El ‘Select’ permite de seleccionar datos en la base de datos, y visualizarlos. Se puede utilizar un alias para que el campo se pueda llamar con otro nombre. SELECT
Para seleccionar todos los campos de la tabla, se utiliza el asterisco en vez de los nombres de campo. SELECT * FROM <nombre_tabla>; Ejemplo: SELECT emp_no,job_grade as nivel FROM employee; SELECT * FROM employee;
2.7 Where La cláusula ‘Where’ permite de limitar la encuesta a ciertos datos. Se utiliza evaluando un campo versus una condición. Se pueden utilizar varias condiciones, con el uso de ‘Or’, ‘And’, y/o paréntesis. Para compara números, se utiliza el signo '=', o '<', o'>', o'<=', o '>=', o 'between ... and ...'. Para comparar caracteres se utiliza la palabra 'like'. El wildcard es '%'. Para compara fecha, se utiliza el signo '=', o '<', o'>', o'<=', o '>=', o 'between ... and ...'. Para SELECT * FROM <nombre_tabla> WHERE
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Ejemplo: SELECT emp_no,job_grade FROM SELECT emp_no,job_grade FROM SELECT * FROM employee where SELECT * FROM employee where
employee where employee where emp_no between last_name like
Pagina 7 / 40
emp_no>45; emp_no=45 or 40 and 45; 'P%';
emp_no=41;
2.8 Count Para contar un numero de registros, se utiliza la palabra ‘Count’. SELECT COUNT(
2.9 Sum, avg, min, max Para una suma, min, max,... de un campo, se utilizan la palabras ‘Sum’, ‘Min’, ‘Max’, ‘Avg’. SELECT SUM(
2.10Distinct Para tener la lista de valores distingas de un campo, se utiliza la palabra ‘Distinct’. SELECT DISTINCT(
2.11Order by Para ordenar los registros regresados, hay que utilizar la palabre ‘Order by’. SELECT * FROM <nombre_tabla> ORDER BY
2.12Uniones Uniones permiten de unir los resultados de dos consultas. Para poder unirlas, tienen que tener los mismos campos. SELECT
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 8 / 40
2.13Subconsultas Subconsultas son consultas sobre otras consultas. La subconsulta se puede utilizar el la cáusula ‘From’, o el la condición de la clásula ‘Where’. La subconsulta se pone entre paréntesis. En MySQL, las subconsultas deben tener sus propios alias. SELECT t3.
FROM <nombre_tabla > t
Ejemplo: SELECT t3.first_name,t3.last_name FROM ( select t.first_name,t.last_name from employee t where job_grade=5 union select t2.fname,t2.lname from usuario t2 ) t3 where t3.last_name like '%o%'; SELECT t3.first_name,t3.last_name FROM employee t3 where t3.job_country IN (select t.country from country t where t.currency='Euro');
2.14Agrupaciones Las agrupaciones permiten agrupar datos y saber cuantos datos hay de cada valor. SELECT
Las agrupaciones se pueden filtrar utilizando la clausula HAVING. Ejemplo: SELECT job_grade, count(*) FROM employee where emp_no>45 group by job_grade; SELECT job_grade, sum(salary) FROM employee where emp_no>45 group by job_grade having sum(salary)<1000000;
2.15Operadores SQL Ya hemos visto anteriormente qué tipos de datos se pueden utilizar en Oracle. Y siempre que haya datos, habrá operaciones entre ellos, así que ahora se describirán qué operaciones y con qué operadores se realizan: Los operadores se pueden dividir en TRES conjuntos: ● Aritméticos: utilizan valores numéricos ● Lógicos (o booleanos o de comparación): utilizan valores booleanos o lógicos. ● Concatenación: para unir cadenas de caracteres. Operadores arítméticos Retornan un valor numérico: Símbo lo
Significado
Ejemplo
+
Operación suma
1 + 2
Operación resta
1 2
*
Operación multiplicación
1 * 2
/
Operador división
1 / 2
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 9 / 40
Operadores lógicos Retornan un valor lógico (verdadero o falso) Símbolo
Significado
Ejemplo
=
Igualdad
1 = 2
!= <> ^=
Desigualdad
1 != 2 1 <> 2 1 ^= 2
>
Mayor que
1 > 2
<
Menor que
1 < 2
>=
Mayor o igual que
1 >= 2
<=
Menor o igual que
1 <= 2
IN (RS)
Igual a algún elemento del result set.
1 IN (1,2) [TRUE]
ANY SOME
a algún elemento del result set (derecha). Debe ser estar 10 >= ANY (1,2,3,10) precedido por =, !=, <, <=, >, >= Hace un OR lógico entre todos [TRUE] los elementos.
ALL
a todos los elementos del result set (derecha), Debe ser estar 10 <= ALL (1,2,3,10) precedido por =, !=, <, <=, >, >= Hace un AND lógico entre todos [TRUE] los elementos.
BEETWEEN x Operando de la izquierda entre x e y. Equivalente a op >= x AND y AND op <= y
10 BETWEEN 1 AND 100
EXISTS
Si la retorna al menos una fila
EXISTS( SELECT 1 FROM DUAL)
LIKE(*)
Es como
'pepe' LIKE 'pe%'
IS NULL
Si es nulo
1 IS NULL
IS NOT NULL Si es No nulo
1 IS NOT NULL
NOT cond.
NOT EXISTS… NOT BETWEEN NOT IN NOT =
Niega la condición posterios
cond AND cond Hace un AND lógico entre dos condiciones
1=1 AND 2 IS NULL
Cond OR cond Hace un OR lógico entre dos condiciones
1=1 OR 2 IS NULL
Existen los siguientes comodines: ● %: Conjunto de N caracteres (de 0 a ∞) ● _: Un solo carácter Concatenación
Oracle puede hacer una conversión automática cuando se utilice este operador con valores numéricos: 10 || 20 = '1020' Este proceso de denomina CASTING y se puede aplicar en todos aquellos casos en que se utiliza valores numéricos en puesto de valores alfanuméricos o incluso viceversa.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 10 / 40
2.16La ausencia de valor: NULL Todo valor (sea del tipo que sea) puede contener el valor NULL que no es más que la ausencia de valor. Así que cualquier columna (NUMBER, VARCHAR2, DATE…) puede contener el valor NULL, con lo que se dice que la columna está a NULL. Una operación retorna NULL si cualquiera de los operandos es NULL. Para comprobar si una valor es NULL se utiliza el operador IS NULL o IS NOT NULL.
2.17Rendimiento Un problema común en las encuesta a base de datos es el rendimiento. Las causas de problema de rendimiento son numerosas. Las más comunes son: ● Instrucción sin o con mala clausula WHERE ● Falta de indice sobre un campo utilizado como filtro ● Mal diseño de la base de datos ● Problema de hardware (falta de memoria, disco ocupado, cpu ocupado por otra aplicación,...) ● Mala configuración del servidor (mal uso de la memoria, disco, cpu,...) ● Mala programación en el cliente. Falta de commit, conexión no cerrada, ... ● Red sobrecargada o muy lenta Cuando se enfrenta a un problema de rendimiento hay que probar primero de identificar la causa y los síntomas. Servidor sobrecargado en CPU, disco, memoria? Un cliente afectado o todos? Cuando aparece el problema? Para ayudar a investigar estos problemas existen herramientas. Algunos vienen con la base de datos, otros están desarrollados aparte. Ver la documentación de su base de datos para mas información.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 11 / 40
3 Creación de objetos 3.1 Vistas La vista es una sentencia de selección de datos preparada. Permite facilitar las consultas futuras, especialmente cuando se juntan varias tablas. Permite también limitar el acceso a datos (seguridad). 3.1.1
Creación
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [schema.]view [(alias,...) inline_constraint(s)] [out_of_line_constraint(s)] [XMLType_view_clause] AS subquery options options: WITH READ ONLY WITH CHECK OPTION [CONSTRAINT constraint]
3.1.2 ALTER ALTER ALTER ALTER ALTER ALTER
Modificación VIEW VIEW VIEW VIEW VIEW VIEW
[schema.]view [schema.]view [schema.]view [schema.]view [schema.]view [schema.]view
COMPILE; ADD out_of_line_constraint; MODIFY CONSTRAINT constraint {RELY | NORELY}; DROP CONSTRAINT constraint; DROP PRIMARY KEY UNIQUE (column [,column,...])
When a constraint is in NOVALIDATE mode, Oracle does not enforce it and does not take it into account for query rewrite. If you specify RELY Oracle will still not enforce the constraint but will take it into account for query rewrite. An alternative to ALTER VIEW COMPILE is the builtin pl/sql package DBMS_UTILITY 3.1.3
Eliminación
DROP VIEW [schema.]view [CASCADE CONSTRAINTS]
3.1.4
Ejercicios
1. Crea una vista sobre 2 tablas ligadas. 2. Usa la vista en una consulta.
3.2 Secuencias Una secuencia es un contador que se incrementa automáticamente y permite generar numero únicos. 3.2.1
Creación
CREATE SEQUENCE [schema.]sequence_name option(s) Options: INCREMENT BY int START WITH int MAXVALUE int | NOMAXVALUE MINVALUE int | NOMINVALUE CYCLE | NOCYCLE
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 12 / 40
CACHE int | NOCACHE ORDER | NOORDER
3.2.2
Modificación
ALTER SEQUENCE [schema.]sequence_name option(s) Options: INCREMENT BY int MAXVALUE int | NOMAXVALUE MINVALUE int | NOMINVALUE CYCLE | NOCYCLE CACHE int | NOCACHE ORDER | NOORDER
3.2.3
Eliminación
DROP SEQUENCE [schema.]sequence_name
3.2.4
Ejercicios
1. Crea una secuencia 2. Altera la secuencia para que el próximo numero generado sea 20
3.3 Vistas materializadas Una vista materializada es un conjunto de datos de una o varias tablas (como una vista) pero del cual el dato se guarda físicamente. Aumenta el rendimiento de las consultas en comparación de la vista normal, pero disminuye el rendimiento de las consultas DML sobre las tablas ligadas a la vista materializada, ya que tiene que mantener la vista materializada ademas de la tabla fuente. 3.3.1
Creación
CREATE MATERIALIZED VIEW [schema.]mview Mview_Options [USING INDEX storage_options] [{REFRESH [refresh_options] | NEVER REFRESH] [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE] AS subbquery; storage_options: PCTFREE int PCTUSED int INITRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace refresh_options: FAST | COMPLETE | FORCE ON [DEMAND | COMMIT] {NEXT | START WITH} date WITH {PRIMARY KEY | ROWID} USING DEFAULT {MASTER|LOCAL} ROLLBACK SEGMENT USING {MASTER|LOCAL} ROLLBACK SEGMENT rb_segment idx_organized_tbl_clause: storage_option(s) {MAPPING TABLE | NOMAPPING} [PCTTHRESHOLD int] [COMPRESS int|NOCOMPRESS] [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ] external_table_clause: ([TYPE access_driver_type]
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 13 / 40
DEFAULT DIRECTORY directory [ACCESS PARAMETERS {USING CLOB subquery | (opaque_format_spec) }] LOCATION (directory:'location_specifier' [,directory2:'location_specifier2'...) ) [REJECT LIMIT {int|UNLIMITED}] nested_storage_clause: NESTED TABLE {nested_item | COLUMN_VALUE} [ [ELEMENT] IS OF TYPE (ONLY type) ]] | STORE AS storage_table [RETURN AS {LOCATOR|VALUE} ]
3.3.2
[ [NOT] SUBSTITUTABLE AT ALL LEVELS ]]
Modificación
ALTER MATERIALIZED VIEW [schema.]mview options iot_options [USING INDEX index_options] [REFRESH [refresh_options]] [COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE] ALTER MATERIALIZED VIEW [schema.]mview options iot_options [USING INDEX index_options] [REBUILD] [COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE] ALTER MATERIALIZED VIEW [schema.]mview options iot_options [USING INDEX index_options] MODIFY SCOPE FOR (ref_column/attribute) IS [schema.]scope_table [COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE] Options: COMPRESS|NOCOMPRESS CACHE | NOCACHE PARALLEL int | NOPARALLEL ALLOCATE EXTENT [( [DATAFILE filename] [, SIZE int {K | M}] [, INSTANCE int] )] Partitioning clause Physical_options LOB Clause
3.3.3
Eliminación
DROP MATERIALIZED VIEW [schema.] materialized_view
Snapshot is synonymous with Materialized View. Para más información sobre las vistas materializadas, ver en http://www.softics.ru/docs/oracle10r2/server.101/b10759/statements_6002.htm
3.4 Sinónimos Un sinónimo es un nombre de objeto que refiere a un objeto con otro nombre, o que se pueda encontrar en otro esquema. Un sinónimo public es disponible para todos los usuarios (según sus privilegios). 3.4.1
Creación
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object [@dblink] 'PUBLIC' will create a public synonym, accessible to all users (with the appropriate privileges.) Unlike Views, Synonyms do not need to be recompiled when the underlying table is redefined. There is a small performance hit when accessing data through a public synonym.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 14 / 40
Oracle will resolve object names in the following order: current user private synonym public synonym An alternative method to access data in another schema is to use: ALTER SESSION set current_schema = Other_Schema
3.4.2
Eliminación
DROP [PUBLIC] SYNONYM [schema.]synonym [FORCE] FORCE will drop the synonym even there are dependent tables or user-defined types.
3.4.3
Ejercicios
1. Crea un sinónimo publico para un objeto. Luego conecta te como otro usuario y consulta el objeto via el sinónimo público.
3.5 DB Link Un DB Link permite llamar a objetos que se encuentran en otra base de datos (onlcuido en un servidor remoto). Hay que tener cuidado son el rendimiento ligado a consultas remotas, ya que puede impactar de manera significativa en servidor remoto. 3.5.1
Creación
CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password [ dblink_authentication ] } | dblink_authentication ] [ USING 'connect_string' ] ; dblink_authentication: AUTHENTICATED BY user IDENTIFIED BY password
Specify SHARED to use a single network connection to create a public database link that can be shared among multiple users. Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you. Dblink: Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database. Restriction on Creating Database Links You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 15 / 40
entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.) The CONNECT TO clause lets you enable a connection to the remote database. Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user. When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott. However, if the stored object is an invokerrights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invokerrights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges. user IDENTIFIED BY password : Specify the username and password used to connect to the remote database using a fixed user database link. If you omit this clause, the database link uses the username and password of each user who is connected to the database. This is called a connected user database link. dblink_authentication Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user. You must specify this clause when using the SHARED clause. USING 'connect string': Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name. 3.5.1.1
Examples
The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different. Defining a Public Database Link: Example The following statement defines a shared public database link named remote that refers to the database specified by the service name remote: CREATE PUBLIC DATABASE LINK remote USING 'remote';
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 16 / 40
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges): UPDATE employees@remote SET salary=salary*1.1 WHERE last_name = 'Baer';
Defining a FixedUser Database Link: Example In the following statement, user hr on the remote database defines a fixeduser database link named local to the hr schema on the local database: CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';
Once this database link is created, hr can query tables in the schema hr on the local database in this manner: SELECT * FROM employees@local; User hr can also use DML statements to modify data on the local database: INSERT INTO employees@local (employee_id, last_name, email, hire_date, job_id) VALUES (999, 'Claus', '[email protected]', SYSDATE, 'SH_CLERK'); UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK'; DELETE FROM employees@local WHERE employee_id = 999;
Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table: SELECT * FROM oe.customers@local;
Defining a CURRENT_USER Database Link: Example The following statement defines a currentuser database link to the remote database, using the entire service name as the link name: CREATE DATABASE LINK remote.us.oracle.com CONNECT TO CURRENT_USER USING 'remote';
The user who issues this statement must be a global user registered with the LDAP directory service. You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database: CREATE SYNONYM emp_table FOR [email protected];
3.5.2
Eliminación
DROP [ PUBLIC ] DATABASE LINK dblink
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 17 / 40
4 PL/SQL básico 4.1 Introducción El PL/SQL es un lenguaje de programación de Oracle, que permite que el código sea ejecutado en el servidor Oracle mismo. Para códigos que necesitan muchas manipulaciones de datos, el PL/SQL es muy eficiente ya que se ejecuta localmente, con acceso directo a los datos. El PL/SQL no es CASESENSITIVE, es decir, no diferencia mayúsculas de minúsculas como otros lenguajes de programación como C o Java. Sin embargo debemos recordar que ORACLE es CASE SENSITIVE en la búsquedas de texto (hasta la versión 10.2R2 que lo permite mediante nuevas valores para variables NLS_SORT y NLS_COMP). Las instructiones PL/SQL terminan con un puntocoma (;), excepto algunas instrucciones con BEGIN, DECLARE, etc.. Los comentarios de una línea se inician con – y los de varias líneas inician con /* y terminan con */ Este capítulo esta en gran parte basado sobre el curso de PL/SQL disponible en http://www.devjoker.com/contenidos/TutorialPLSQL/66/SQLDinamico.aspx
4.2 Bloque anónimo PL/SQL Un bloque PL/SQL es un conjunto de instrucciones PL/SQL relacionadas entre ellas. Un simple bloque PL/SQL inicia con BEGIN y termina con END; Ejemplo: SET SERVEROUTPUT ON; BEGIN dbms_output.put_line('Hola'); END; /
4.3 Identificadores Un identificador es el nombre de un objeto PL/SQL, como por ejemplo una constante, variable, excepción, paquete, función, procedimiento, tabla, cursor,... Un identificador puede tener hasta 30 caracteres, debe iniciar con una letra.
4.4 Variables Las variables se declaran antes de la palabra BEGIN, y se preceden de la palabra DECLARE. La sintaxis es : nombre_de_variable [CONSTANT] tipo [NOT NULL] [DEFAULT valor_por_defecto]; 4.4.1
Tipos de variables
Los tipos de variables que se pueden usar en PL/SQL son básicamente los mismos que se usan para definir campos en una tabla, plus algunos específicos del PL/SQL. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 18 / 40
PLS_INTEGER: tipo numérico del PL/SQL, equivalente a NUMBER, pero más eficiente si se necesita hacer cálculos aritméticos. %TYPE: refiere de manera dinámica a otro objeto. 4.4.2
Variables locales
Por defecto, una variable solo está disponible en el bloque PL/SQL donde se encuentra, y los sub bloques dentro de este bloque. Ejemplo: DECLARE v_cnt_emp number; BEGIN select count(*) into v_cnt_emp from scott.emp where sal>1000; BEGIN dbms_output.put_line ('Empleados con salario>1000 : '||v_cnt_emp); END; END; /
4.4.3
Variables globales
Las variables globales son variables que se pueden llamar desde otro código. Solo están disponibles en paquetes.
4.5 Control de flujo Se usa el IF..ELSIF..ELSE para controlar el flujo en PL/SQL. IF (expresion) THEN Instrucciones ELSIF (expresion) THEN Instrucciones ELSE Instrucciones END IF; Ejemplo: DECLARE v_cnt_emp number; v_tot_emp number; BEGIN select count(*) into v_tot_emp from scott.emp; dbms_output.put_line ('Hay un total de '||v_tot_emp||' empleados.'); select count(*) into v_cnt_emp from scott.emp where sal>1000; dbms_output.put_line ('Hay '||v_cnt_emp||' empleados que ganan mas de 1000 $.'); dbms_output.put_line ('Resultado de la analisis.'); IF (v_tot_emp=v_cnt_emp) then dbms_output.put_line ('Los empleados ganan todos mas de 1000 $'); ELSIF (v_tot_emp
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 19 / 40
END IF; END; /
4.6 Bucles Una bucle permite repetir una acción un sin número de veces. Hay que tener cuidado en no crear bucles infinitas. En PL/SQL tenemos a nuestra disposición los siguientes iteradores o bucles: * LOOP * WHILE * FOR 4.6.1
LOOP
El bucle LOOP, se repite tantas veces como sea necesario hasta que se fuerza su salida con la instrucción EXIT. Su sintaxis es la siguiente LOOP Instrucciones IF (expresion) THEN Instrucciones EXIT; END IF; END LOOP; Ejemplo: DECLARE v_cnt PLS_INTEGER DEFAULT 0; BEGIN LOOP v_cnt:=v_cnt+1; dbms_output.put_line ('Contador = '||v_cnt); IF (v_cnt>=10) then dbms_output.put_line ('Ya merito!'); exit; END IF; END LOOP; END;
4.6.2
WHILE
El bucle WHILE, se repite mientras que se cumpla expresion. WHILE (expresion) LOOP Instrucciones END LOOP; Ejemplo: DECLARE v_cnt PLS_INTEGER DEFAULT 0; BEGIN v_cnt:=10; WHILE (v_cnt>0) LOOP dbms_output.put_line ('Contador = '||v_cnt); v_cnt:=v_cnt-1; END LOOP; END;
4.6.3
FOR
El bucle FOR, se repite tanta veces como le indiquemos en los identificadores inicio y final. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 20 / 40
FOR contador IN [REVERSE] inicio..final LOOP Instrucciones END LOOP;
En el caso de especificar REVERSE el bucle se recorre en sentido inverso. Ejemplo:
DECLARE v_cnt PLS_INTEGER DEFAULT 0; BEGIN FOR v_cnt IN REVERSE 1..10 LOOP dbms_output.put_line ('Contador = '||v_cnt); END LOOP; END;
4.7 Cursores PL/SQL utiliza cursores para gestionar las instrucciones SELECT. Un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente los cursores son fragmentos de memoria que reservados para procesar los resultados de una consulta SELECT. Podemos distinguir dos tipos de cursores: * Cursores implicitos. Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta devuelve un único registro. * Cursores explicitos. Son los cursores que son declarados y controlados por el programador. Se utilizan cuando la consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que devuelven un único registro por razones de eficiencia. Son más rápidos. Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de acuerdo a los mismos convenios que cualquier otra variable. Los cursores implicitos no necesitan declaración. Para procesar instrucciones SELECT que devuelvan más de una fila, son necesarios cursores explicitos combinados con un estructura de bloque. Un cursor admite el uso de parámetros. Los parámetros deben declararse junto con el cursor. El siguiente diagrama representa como se procesa una instrucción SQL a través de un cursor.
Ejemplo de cursor explicito: DECLARE CURSOR c_emp IS /*CURSOR*/ select ename, sal from scott.emp; BEGIN FOR fila IN c_emp LOOP /*no es necesario definir la variable fila, será de tipo %ROW */ dbms_output.put_line(fila.ename||' tiene un salario de '||fila.sal); END LOOP;
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 21 / 40
END;
Ejemplo de cursor implicito:
DECLARE v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM scott.emp WHERE empno=7369; dbms_output.put_line('El empleado numero 7369 tiene un salario de '||v_sal||' $'); end;
4.8 Excepciones En PL/SQL una advertencia o condición de error es llamada una excepción. Los bloques de excepciones permiten atrapar errores de ejecución y darles eventualmente un tratamiento para evitar que se pare el programa de manera anormal. Las excepciones se controlan dentro de su propio bloque. La estructura de bloque de una excepción se muestra a continuación. DECLARE Declaraciones BEGIN Ejecucion EXCEPTION Excepcion END; Cuando ocurre un error, se ejecuta la porción del programa marcada por el bloque EXCEPTION, transfiriéndose el control a ese bloque de sentencias. El siguiente ejemplo muestra un bloque de excepciones que captura las excepciones NO_DATA_FOUND y ZERO_DIVIDE. Cualquier otra excepcion será capturada en el bloque WHEN OTHERS THEN. DECLARE Declaraciones BEGIN Ejecucion EXCEPTION WHEN NO_DATA_FOUND THEN Se ejecuta cuando ocurre una excepcion de tipo NO_DATA_FOUND WHEN ZERO_DIVIDE THEN Se ejecuta cuando ocurre una excepcion de tipo ZERO_DIVIDE WHEN OTHERS THEN Se ejecuta cuando ocurre una excepcion de un tipo no tratado en los bloques anteriores END; Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el bloque anterior. Si existe un bloque de excepción apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo de excepción se ejecutará el bloque de excepción WHEN OTHERS THEN (si existe!). WHEN OTHERS debe ser el último manejador de excepciones. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 22 / 40
Las excepciones pueden ser definidas en forma interna o explícitamente por el usuario. Ejemplos de excepciones definidas en forma interna son la división por cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos: ZERO_DIVIDE y STORAGE_ERROR. Las excepciones definidas por el usuario deben ser alcanzadas explícitamente utilizando la sentencia RAISE. Con las excepciones se pueden manejar los errores cómodamente sin necesidad de mantener múltiples chequeos por cada sentencia escrita. También provee claridad en el código ya que permite mantener las rutinas correspondientes al tratamiento de los errores de forma separada de la lógica del negocio. 4.8.1
Excepciones predefinidas
PL/SQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales. Las excepciones predefinidas no necesitan ser declaradas. Simplemente se utilizan cuando estas son lanzadas por algún error determinado. 4.8.1.1
Excepciones asociadas a los cursores implícitos.
Los cursores implicitos sólo pueden devolver una fila, por lo que pueden producirse determinadas excepciones. Las más comunes que se pueden encontrar son no_data_found y too_many_rows. La siguiente tabla explica brevemente estas excepciones. NO_DATA_FOUND Se produce cuando una sentencia SELECT intenta recuperar datos pero ninguna fila satisface sus condiciones. Es decir, cuando "no hay datos" TOO_MANY_ROWS Dado que cada cursor implicito sólo es capaz de recuperar una fila , esta excepcion detecta la existencia de más de una fila. Ejemplo:
DECLARE v_sal NUMBER; BEGIN BEGIN SELECT sal INTO v_sal FROM scott.emp WHERE empno=1; dbms_output.put_line('El empleado numero 1 tiene un salario de '||v_sal||' $'); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('El empleado numero 1 no existe!'); END; BEGIN SELECT empno INTO v_sal FROM scott.emp WHERE sal>1000; dbms_output.put_line('El empleado que tiene un salario>1000 $ es el empleado numero'||v_sal); EXCEPTION WHEN too_many_rows THEN dbms_output.put_line('HAY MAS DE UN EMPLEADO QUE GANA MAS DE 1000 $!'); END; END;
4.8.1.2
Lista de excepciones predefinidas
ACCESS_INTO_NULL inicializado 6530 COLLECTION_IS_NULL inicializada 6531
© Copyright Cédric Simon, 2008
El programa intentó asignar valores a los atributos de un objeto no El programa intentó asignar valores a una tabla anidada aún no
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 23 / 40
CURSOR_ALREADY_OPEN El programa intentó abrir un cursor que ya se encontraba abierto. Recuerde que un cursor de ciclo FOR automáticamente lo abre y ello no se debe especificar con la sentencia OPEN 6511 DUP_VAL_ON_INDEX El programa intentó almacenar valores duplicados en una columna que se mantiene con restricción de integridad de un índice único (unique index) 1 INVALID_CURSOR El programa intentó efectuar una operación no válida sobre un cursor 1001 INVALID_NUMBER En una sentencia SQL, la conversión de una cadena de caracteres hacia un número falla cuando esa cadena no representa un número válido 1722 LOGIN_DENIED El programa intentó conectarse a Oracle con un nombre de usuario o password inválido 1017 NO_DATA_FOUND Una sentencia SELECT INTO no devolvió valores o el programa referenció un elemento no inicializado en una tabla indexada 100 NOT_LOGGED_ON El programa efectuó una llamada a Oracle sin estar conectado 1012 PROGRAM_ERROR PL/SQL tiene un problema interno 6501 ROWTYPE_MISMATCH Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) tienen tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado 6504 SELF_IS_NULL El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo 30625 STORAGE_ERROR La memoria se terminó o está corrupta 6500 SUBSCRIPT_BEYOND_COUNT El programa está tratando de referenciar un elemento de un arreglo indexado que se encuentra en una posición más grande que el número real de elementos de la colección 6533 SUBSCRIPT_OUTSIDE_LIMIT El programa está referenciando un elemento de un arreglo utilizando un número fuera del rango permitido (por ejemplo, el elemento “1”) 6532 SYS_INVALID_ROWID La conversión de una cadena de caracteres hacia un tipo rowid falló porque la cadena no representa un número 1410 TIMEOUT_ON_RESOURCE Se excedió el tiempo máximo de espera por un recurso en Oracle 51 TOO_MANY_ROWS Una sentencia SELECT INTO devuelve más de una fila 1422 VALUE_ERROR Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, sucede cuando se intenta calzar un valor muy grande dentro de una variable más pequeña 6502 ZERO_DIVIDE El programa intentó efectuar una división por cero 1476
4.8.2
Excepciones definidas por el usuario
PL/SQL permite al usuario definir sus propias excepciones, las que deberán ser declaradas y lanzadas explícitamente utilizando la sentencia RAISE. Las excepciones deben ser declaradas en el segmento DECLARE de un bloque, subprograma o paquete. Se declara una excepción como cualquier otra variable, asignandole el tipo EXCEPTION. Las mismas reglas de alcance aplican tanto sobre variables como sobre las excepciones. DECLARE Declaraciones MyExcepcion EXCEPTION; BEGIN Ejecucion EXCEPTION Excepcion END; © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
4.8.2.1
Pagina 24 / 40
Reglas de Alcance
Una excepcion es válida dentro de su ambito de alcance, es decir el bloque o programa donde ha sido declarada. Las excepciones predefinidas son siempre válidas. Como las variables, una excepción declarada en un bloque es local a ese bloque y global a todos los sub bloques que comprende. 4.8.2.2
La sentencia RAISE
La sentencia RAISE permite lanzar una excepción en forma explícita. Es posible utilizar esta sentencia en cualquier lugar que se encuentre dentro del alcance de la excepción. DECLARE Declaramos una excepcion identificada por VALOR_NEGATIVO VALOR_NEGATIVO EXCEPTION; valor NUMBER; BEGIN Ejecucion valor := 1; IF valor < 0 THEN RAISE VALOR_NEGATIVO; END IF; EXCEPTION Excepcion WHEN VALOR_NEGATIVO THEN dbms_output.put_line('El valor no puede ser negativo'); END; Con la sentencia RAISE podemos lanzar una excepción definida por el usuario o predefinida, siendo el comportamiento habitual lanzar excepciones definidas por el usuario. Recordar la existencia de la excepción OTHERS, que simboliza cualquier condición de excepción que no ha sido declarada. Se utiliza comúnmente para controlar cualquier tipo de error que no ha sido previsto. En ese caso, es común observar la sentencia ROLLBACK en el grupo de sentencias de la excepción o alguna de las funciones SQLCODE – SQLERRM, que se detallan en el próximo punto. 4.8.2.3
Uso de SQLCODE y SQLERRM
Al manejar una excepción es posible usar las funciones predefinidas SQLCode y SQLERRM para aclarar al usuario la situación de error acontecida. SQLcode devuelve el número del error de Oracle y un 0 (cero) en caso de exito al ejecutarse una sentencia SQL. Por otra parte, SQLERRM devuelve el correspondiente mensaje de error. Estas funciones son muy útiles cuando se utilizan en el bloque de excepciones, para aclarar el significado de la excepción OTHERS. Estas funciones no pueden ser utilizadas directamente en una sentencia SQL, pero sí se puede asignar su valor a alguna variable de programa y luego usar esta última en alguna sentencia. DECLARE
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 25 / 40
err_num NUMBER; err_msg VARCHAR2(255); result NUMBER; BEGIN SELECT 1/0 INTO result FROM DUAL; EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SQLERRM; DBMS_OUTPUT.put_line('Error:'||TO_CHAR(err_num)); DBMS_OUTPUT.put_line(err_msg); END;
También es posible entregarle a la función SQLERRM un número negativo que represente un error de Oracle y ésta devolverá el mensaje asociado. DECLARE msg VARCHAR2(255); BEGIN msg := SQLERRM(-1403); DBMS_OUTPUT.put_line(MSG); END;
4.8.3
RAISE_APPLICATION_ERROR
En ocasiones queremos enviar un mensaje de error personalizado al producirse una excepción PL/SQL. Para ello es necesario utilizar la instruccion RAISE_APPLICATION_ERROR; La sintaxis general es la siguiente: RAISE_APPLICATION_ERROR(<error_num>,<mensaje>); Siendo: * error_num es un entero negativo comprendido entre 20001 y 20999 * mensaje la descripcion del error Ejemplo: DECLARE
v_div NUMBER; BEGIN SELECT 1/0 INTO v_div FROM DUAL; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'No se puede dividir por cero'); END;
4.9 Procedimientos, funciones, paquetes, disparadores Son objetos PL/SQL residentes en el servidor PL/SQL. 4.9.1
Funciones
Una función es un código compilados en el servidor, que se ejecutan en local, y que pueden aceptar parámetros de entrada y tiene un solo parámetro de salido. Una función SIEMPRE debe regresar un valor. Una función se puede usar en otro código PL/SQL, o en SQL ya sea en un SELECT, una clausula WHERE, CONNECT BY, START WITH, ORDER BY, GROUP BY, como VALUES en un INSERT, o como SET en un UPDATE.
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
4.9.1.1
Pagina 26 / 40
Funciones predefinidas
PL/SQL tiene un gran número de funciones incorporadas, sumamente útiles. A continuación vamos a ver algunas de las más utilizadas. SYSDATE Devuelve la fecha del sistema: SELECT SYSDATE FROM DUAL; NVL Devuelve el valor recibido como parámetro en el caso de que expresión sea NULL,o expresión en caso contrario. NVL(<expresion>,
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 27 / 40
'DD/MM/YYYY HH24:MI:SS') FROM DUAL; TO_CHAR Convierte una expresión al tipo CHAR. El parámetro opcional formato indica el formato de salida de la expresión. TO_CHAR(<expresion>, [
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 28 / 40
FROM DUAL; SUBSTR Obtiene una parte de una expresion, desde una posición de inicio hasta una determinada longitud. SUBSTR(<expresion>, <posicion_ini>,
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 29 / 40
SELECT RTRIM ('Hola Mundo ') FROM DUAL; LTRIM Elimina los espacios en blanco a la izquierda de una expresion SELECT LTRIM (' Hola Mundo') FROM DUAL; TRIM Elimina los espacios en blanco a la izquierda y derecha de una expresion SELECT TRIM (' Hola Mundo ') FROM DUAL; MOD Devuelve el resto de la división entera entre dos números. MOD(
Funciones definidas por el usuario
La sintaxis para construir funciones es la siguiente: CREATE [OR REPLACE] FUNCTION
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 30 / 40
END ;
Si el sistema nos indica que el la función se ha creado con errores de compilación podemos ver estos errores de compilacion con la orden SHOW ERRORS en SQL *Plus. Las funciones pueden utilizarse en sentencias SQL de manipulación de datos (SELECT, UPDATE, INSERT y DELETE): SELECT
empno, ename, fn_Obtener_Salario( empno) FROM scott.emp;
4.9.2
Procedimientos
Los procedimientos son códigos compilados en el servidor, que se ejecutan en el servidor, y que pueden aceptar parámetros de entrada y/o de salida. Un procedimiento se puede usar en un select, pero no en un where clause. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código. La sintaxis de un procedimiento almacenado es la siguiente: CREATE [OR REPLACE] PROCEDURE <procedure_name> [(<param1> [IN|OUT|IN OUT]
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 31 / 40
END Actualiza_Salario;
También podemos asignar un valor por defecto a los parámetros, utilizando la clausula DEFAULT o el operador de asiganción (:=) . create or replace PROCEDURE Actualiza_Salario(p_empno NUMBER, p_new_salario NUMBER DEFAULT 500) IS ... END Actualiza_Salario;
Una vez creado y compilado el procedimiento almacenado podemos ejecutarlo. Si el sistema nos indica que el procedimiento se ha creado con errores de compilación podemos ver estos errores de compilación con la orden SHOW ERRORS en SQL *Plus. Existen dos formas de pasar argumentos a un procedimiento almacenado a la hora de ejecutarlo (en realidad es válido para cualquier subprograma). Estas son: * Notación posicional: Se pasan los valores de los parámetros en el mismo orden en que el procedure los define. BEGIN Actualiza_Salario(7369,2500); COMMIT; END;
* Notación nominal:Se pasan los valores en cualquier orden nombrando explicitamente el parámetro. BEGIN Actualiza_Salario(p_empno => 7369,p_new_salario COMMIT; END;
4.9.3
=> 2500);
Paquetes
Un paquete es un conjunto de funciones y/o procedimiento. Permite facilitar la administración de los códigos (agrupaciones), y la seguridad (a nivel de paquete en vez de por función/procedimiento). En el paquete se pueden definir variable de alcance de todo el paquete (global variables). Lo primero que debemos tener en cuenta es que los paquetes están formados por dos partes: la especificación y el cuerpo. La especificación del un paquete y su cuerpo se crean por separado. La especificación es la interfaz con las aplicaciones. En ella es posible declarar los tipos, variables, constantes, excepciones, cursores y subprogramas disponibles para su uso posterior desde fuera del paquete. En la especificación del paquete sólo se declaran los objetos (procedures, funciones, variables ...), no se implementa el código. Los objetos declarados en la especificación del paquete son accesibles desde fuera del paquete por otro script de PL/SQL o programa. Para crear la especificación de un paquete la sintaxis general es la siguiente: CREATE [OR REPLACE] PACKAGE
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 32 / 40
{[
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 33 / 40
Los paquetes pueden llegar a ser programas muy complejos y suelen almacenar gran parte de la lógica de negocio. Ejemplo:
4.9.4
Disparadores
Los disparadores (triggers). Un disparador es un codigo que dispara cada vez que se ha modificado el dato de una tabla. Puede disparar a nivel de la consulta, o a nivel de cada línea afectada por la consulta. también puede disparar antes o después de la consulta, y solo por ciertos tipos de consulta (insert/update/delete), y eventualmente solo cuando cierto(s) campo(s) estan afectados(s). 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: CREATE [OR REPLACE] TRIGGER <nombre_trigger> {BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN] [OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]} ON <nombre_tabla> [FOR EACH ROW [WHEN (
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 34 / 40
FOR EACH ROW Los disparadores con nivel de fila se activan una vez por cada fila afectada por la orden que provocó el disparo. Los disparadores con nivel de orden se activan sólo una vez, antes o después de la orden. Los disparadores con nivel de fila se identifican por la cláusula FOR EACH ROW en la definición del disparador. La cláusula WHEN sólo es válida para los disparadores con nivel de fila. 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 EMP_AUDIT cada vez que modificamos el salario de un registro en la tabla emp: Create table emp_audit (empno number not null, fecha date not null, msg varchar2(500) not null); create or replace TRIGGER TR_EMP_AU AFTER UPDATE ON EMP FOR EACH ROW WHEN (OLD.sal<>NEW.sal) DECLARE -- local variables BEGIN INSERT INTO emp_audit (empno,fecha,msg) VALUES (:NEW.empno,SYSDATE,'Salario modificado de '||:old.sal||' a '||:new.sal); END ;
El trigger se ejecutará cuando sobre la tabla EMP se ejecute una sentencia UPDATE que modifica el salario. Ejemplo: BEGIN
Actualiza_Salario(p_empno => 7369,p_new_salario COMMIT; END;
4.9.4.1
=> 2500);
Orden de ejecución de los triggers
Una 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. 4.9.4.2
Restricciones de los triggers
El 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:
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 35 / 40
* 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. * 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 4.9.4.3
Utilización de :OLD y :NEW
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. La siguiente tabla muestra los valores de OLD y NEW. ACCION SQL OLD NEW INSERT No definido; todos los campos toman valor NULL. Valores que serán insertados cuando se complete la orden. UPDATE Valores originales de la fila, antes de la actualización. Nuevos valores que serán escritos cuando se complete la orden. DELETE Valores, antes del borrado de la fila. No definidos; todos los campos toman el valor NULL. 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 DELETING Dentro 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: INSERTING TRUE si la orden de disparo es INSERT; FALSE en otro caso. UPDATING TRUE si la orden de disparo es UPDATE; FALSE en otro caso. DELETING TRUE si la orden de disparo es DELETE; FALSE en otro caso. Sintaxis completa: CREATE [OR REPLACE] TRIGGER [schema.]trigger BEFORE event [WHEN (condition)] {pl_sql_block | call_procedure_statement} CREATE [OR REPLACE] TRIGGER [schema.]trigger AFTER event
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 36 / 40
[WHEN (condition)] {pl_sql_block | call_procedure_statement} CREATE [OR REPLACE] TRIGGER [schema.]trigger INSTEAD OF event [WHEN (condition)] {pl_sql_block | call_procedure_statement} event can be one or more of the following (separate multiple events with OR) DELETE event_ref referencing_clause INSERT event_ref referencing_clause UPDATE event_ref referencing_clause UPDATE OF column, column... event_ref db/ddl_event ON [schema.object] db/ddl_event ON DATABASE event_ref: ON [schema.]table ON [schema.]view ON [NESTED TABLE nested_table_column OF] [schema.]view referencing_clause: FOR EACH ROW REFERENCING OLD [AS] old [FOR EACH ROW] REFERENCING NEW [AS] new [FOR EACH ROW] REFERENCING PARENT [AS] parent [FOR EACH ROW] db/ddl_event: ALTER ANALYSE ASSOCIATE STATISTICS AUDIT COMMENT CREATE DDL DISASSOCIATE STATISTICS DROP GRANT LOGON LOGOFF NOAUDIT RENAME REVOKE TRUNCATE SERVERERROR STARTUP SHUTDOWN SUSPEND Multiple db/ddl_events can be separated with OR Multiple OLD, NEW and PARENT correlation names can be defined in one REFERENCING clause. Database constraints are a factor of 8x faster than triggers.
4.9.5
Ejercicios
1. Crear una función que agrega 10 al valor pasado en parametro y regresa el resultado 2. Crear un procedimiento que inserta un nuevo empleado 3. Crear un paquete con procedimiento para modificar un empleado, y una función para obtener la fecha de nacimiento del empleado 4. Usar los procedimientos/funciones en select 5. Crear un trigger que genera la clave primaria del empleado basado en una secuencia
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 37 / 40
5 SQL Loader SQL Loader es una herramienta de Oracle que permite cargar de manera muy eficiente (rápida) datos desde un archivo externeo (texto o binario) hacia una tabla de Oracle. Se lanza desde la línea de comando, lo que permite usarlo en script. Se necesita, además del archivo a cargar, un cliente Oracle instalado (de preferencia la misma versión de cliente que el servidor), y un archivo de parámetros (fichero de control). Al SQLLoader ( sqlldr) se le pasan como parámetros (los más importantes) el fichero que contiene los datos que se van a cargar y la ruta del fichero de control que contiene las acciones a realizar. El formato de los datos, donde se cargaran y cualquier otro tipo de control. SYNTAXIS
sqlldr userid=USUARIO/CONTRASEÑA@BASE control=/RUTA/DEL/FICHERO/DE/CONTROL data=/RUTA/DEL/FICHERO/QUE/CONTIENE/LOS/DATOS log=/LO/MISMO bad=/A/DONDE/VAN/LOS/DATOS/MALOS discard=/A/DONDE/VAN/LOS/DATOS/DESCARTADOS
EJEMPLO: sqlldr userid=alumno/123@curso control=/tmp/test.ctl data=/tmp/test.txt
Como puedes ver no es muy complicado. Simplemente te conectas con un usuario de la base de datos y le dices cual es el fichero de texto que contiene la información y cual es el que contiene las especificaciones sobre lo que se va a hacer con esa información Para más información, ver en: http://downloaduk.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm Parámetros Comunes userid=USUARIO/CONTRASEÑA@BASE Usuario, contraseña e instancia a la que te conectas. control=Ruta del archivo de control que contiene las directivas de lo que se va a hacer. data=Ruta del archivo que contiene los datos. log=Ruta del archivo donde quieres que se genere el log. discard=Ruta del archivo donde quieres que te envíe los registros descartados y no cargados. bad=Ruta del fichero donde van a parar los registros malos. Archivo de Control El archivo de control ( Control file en bárbaro ) es donde se especifica cómo se van a cargar los datos. Normalmente suele responder al siguiente esquema: LOAD DATA BADFILE '/a/donde/van/los/datos/malos' DISCARDFILE '/a/donde/van/los/datos/descartados' INFILE '/la/ruta/del/archivo/desde/el/que/se/cargan/los/datos' APPEND INTO TABLE TU_TABLA_DE_DESTINO FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( CAMPO0 POSITION(1:15), CAMPO1 CHAR "ltrim(rtrim(:CAMPO1))", CAMPO2 CHAR "ltrim(rtrim(:CAMPO2))", © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 38 / 40
CAMPO3NUMERICO INTEGER EXTERNAL, CAMPO4FECHA DATE "DDMonthYY" ) Como ves, si los campos tienen una longitud fija puedes especificarle las posiciones, desde el carácter 1 a la 15 y sucesivamente. O si no, puedes dejar que Oracle interprete los campos y los inserte. En este caso está introduciondo datos APPEND al final de la tabla. ( Podría ser REPLACE para que reemplazara datos preexistentes). Como puedes imaginar por defecto cada linea es una fila. Y en este ejemplo cada campo está delimitado por el carácter |. TRAILING NULLCOLS sirve para que interprete que los campos sin contenido sean interpretados como NULOS. Sql Loader y problemas con fechas Si estas cargando datos con fechas es posible que te cause problemas al intentar insertar columnas con fechas y campos nulos. Una manera de evitarlo es borrar los caracteres en blanco del campo de fecha. Ejemplo: campo_de_fecha "to_date (ltrim(rtrim(:campo_de_fecha)), 'yyyy-mm-dd hh24:mi')"
Ejemplo completo: 1) Crear la tabla: create table cim (CX char(2) not null, LANG char(2) not null, CODE varchar2(10) not null, descripcion varchar2(1000) not null);
2) Copiar el archivo de datos “cim10.txt” en /tmp 3) Crear el archivo de control “test.ctl” en /tmp: LOAD DATA INTO TABLE cim TRUNCATE FIELDS TERMINATED BY X'09' TRAILING NULLCOLS ( CX, LANG, CODE, DESCRIPCION )
4) Cargar los datos:
sqlldr userid=alumno/123@core2d control=/tmp/test.ctl data=/tmp/cim-10.txt log=/home/oracle/test.log
5) Validar que los datos han sido cargados
© Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 39 / 40
6 Respaldo y reposición de la base de datos 6.1 Respaldos físicos online Este tipo de respaldo es la mejor elección para un plan de recuperación después de un desastre. Los respaldos 'online' permiten hacer respaldos mientras el sistema está en uso. Se pueden hacer respaldos diferenciales, o acumulativos. Solo están disponible si la base de datos corre en modo ARCHIVELOG. Este tipo de respaldo/restauración será visto en un curso avanzado y no hace parte de este curso básico. Los respaldos en caliente deben siempre tener como base un respaldo en frío. Sin respaldo correcto y completo en frío, no se podrá hacer una reposición usando los respaldos hechos en caliente. 6.1.1
Respaldos en caliente (online)
Para hacer un respaldo en caliente, cada tablespace debe ser cambiado a modo de backup antes de iniciar el respaldo. Ejemplo: ALTER TABLESPACE xyz BEGIN BACKUP; cp xyfFile1 /backupDir/ ALTER TABLESPACE xyz END BACKUP;
Desde 10g es también posible poner todos los tablespace en modo de backup con un solo comando: ALTER DATABASE BEGIN BACKUP;
No olviden de guardar también una copia de todos los árchive logs' y de los control files. ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf'; cp /camino_hacia_los_logs/*.arc /camino_de_backup/
Si su base de datos termino anormalmente (crash) mientras uno o varios tablespaces estaban en modo de backup, hay que mencionar a Oracle de termniar el modo de backup cuando la base de datos es montada (no abierta). ALTER DATABASE END BACKUP;
6.2 Restauración hacía un cierto punto el en pasado Las restauraciones más fácil son las restauraciones a partir de un respaldo lógico, o de un respaldo 'offline'. En caso de reposición desde un respaldo en caliente, será necesario aplicar los archive logs, para recuperar todas las transacciones ejecutadas (y confirmadas con un commit). Las tablas de FLASHBACK permiten recuperar fácilmente tablas botadas o registros borrados sin necesidad de hace una restauración completa de la base de datos. Desde 10g es muy fácil crear respaldos y restaurar datos usando en Enterprise Manager. © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Curso de administración de Oracle 10g (10.2)
Pagina 40 / 40
Ver detalles en : http://examples.oreilly.com/unixbr/oracle.html © Copyright Cédric Simon, 2008
Versión 1.1
Reproducción prohibida
Related Documents
Mistakes (nivel Intermedio)
June 2020 0
Nivel Intermedio 09-10
June 2020 4
Nivel Intermedio Oficiales 2009-10
June 2020 3
Oracle Jdeveloper 10g Documentation
November 2019 36
Oracle 10g Exceptions
July 2020 11More Documents from ""
Curso De Mysql
May 2020 6
Curso De Ajax Con Java
May 2020 7
Curso De Css
May 2020 5
Curso De Java Avanzado
May 2020 9