Deseas aprender PL/SQL en Oracle 11g
Generalidades de PL/SQL
http://www.unix.com.ua/orelly/oracle/prog2/index.htm
Construccion de paquetes
http://www.unix.com.ua/orelly/oracle/bipack/index.htm
Archive for October, 2008
Declaraciones anclado - %TYPE y %ROWTYPE
Cuando se declara una variable, hay que darle un tipo de datos y de un tamaño.
Vamos a suponer por un momento que tengo una tabla que se parece a esto:
SQL> desc employees Name Null? Type ------------------ -------- ---- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Podría crear un pl/sql- código de la unidad que acceder a algunas de estas columnas. Por ejemplo:
DECLARE v_employee_id NUMBER(6); v_hire_date DATE; v_last_name VARCHAR2(25); BEGIN SELECT employee_id, last_name, hire_date INTO v_employee_id, v_last_name, v_hire_date FROM employees WHERE rownum = 1;END;
En este fragmento de código, declaró el número 6 dígitos de longitud, una fecha y un varchar2 de 25 caracteres de largo. Aunque estos tamaños coinciden con mi cuadro, hay una forma mejor de hacer esto:
DECLARE v_employee_id employees.employee_id%TYPE; v_hire_date employees.hire_date%TYPE; v_last_name employees.last_name%TYPE; BEGIN SELECT employee_id, last_name, hire_date INTO v_employee_id, v_last_name, v_hire_date FROM employees WHERE rownum = 1; END;
Mismos resultados, pero ahora mi software es sólo un poco más fácil de mantener. Si yo uso constantemente %TYPE todo mi código, puede cambiar el tamaño de columna con un poco menos preocupación. Técnicamente, puede ser capaz de cambiar los tipos de datos y estar bien, pero he encontrado que, en la práctica, cuando el usuario cambia los tipos de datos, se necesitará revisar una gran cantidad de su código.
%TYPE es usado cuando usted se refiere son las columnas individuales. Hay muchos casos en donde será usado toda una fila. En lugar de crear manualmente un tipo de registro, puede declarar una variable de %ROWTYPE. Por ejemplo, a continuación voy a seleccionar todas las columnas de mi tabla:
DECLARE v_employees employees%ROWTYPE; BEGIN SELECT * INTO v_employees FROM employees WHERE rownum = 1; END;
Esta recoge la totalidad de las columnas y los ponga a disposición en su programa. Puede detallar cada una de las columnas en su lista de selección como este:
DECLARE v_employees employees%ROWTYPE; BEGIN SELECT employee_id, first_name, last_name,email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id INTO v_employees FROM employees WHERE rownum = 1; END;
Si lo hace detallar cada una de las columnas, es necesario mantenerlos en las columnas en el mismo orden que se declaran en su mesa. Si añade una columna de la tabla, tendrá que modificar su código.
“SELECT *” es más fácil de leer. Asimismo, en los casos en que es necesario recoger las nuevas columnas (por lo general, los procedimientos de mantenimiento tipo), “SELECT *” recoge nuevas columnas con un solo recompilar, en contraposición a la necesidad de añadir manualmente las columnas.
Este es un caso en el que usted necesita para hacer una llamada en cuanto a si un “SELECT *” es una mala cosa o no. Yo no tengo un problema con el “SELECT *” en estos casos. En realidad, me atrevería a decir incluso recomendar que en el caso de que usted realmente necesita todas las columnas (o incluso la mayoría de las columnas). Me doy cuenta de que es una herejía para muchos. Oh, bien.
Una última nota es que %TYPE y %ROWTYPE también como parámetros de trabajo y es una gran manera de enviar datos en torno a que es arrastrada desde su base de datos. Por ejemplo:
CREATE OR REPLACE PROCEDURE
get_employee( p_employee_rec OUT employees%ROWTYPE )
AS
BEGIN
SELECT * INTO p_employee_rec
FROM employees WHERE rownum = 1;
END;
Este fragmento de código devolverá un registro de los datos del empleado a la llamada de rutina.
%TYPE y %ROWTYPE se llaman anclado declaraciones. Básicamente, usted es el tipo de datos de su anclaje declaraciones a la columna de definiciones en su base de datos. Además de asegurar que sus datos tipos y tamaños siempre coinciden, se hace más fácil ya que el código no tiene que buscar los tamaños, así como cada vez que escribir un pedazo de código.
Utilice anclado declaraciones. Utilizar de manera consecuente y la gente que sigue a lo largo de atrás para mantener su código gracias.
Cursores en PL/SQL
Los cursores se utilizan en PL/SQL para manejar las sentencias SELECT. Un cursor esta formado por un conjunto de registros devueltos por una instrucción SQL del tipo SELECT. Desde un punto de visto interno a la base de datos Oracle, los cursores son segmentos de memoria utilizados para realizar operaciones con los registros devueltos tras ejecutar una sentencia SELECT.
Se pueden distinguir dos tipos de cursores:
* Cursores implícitos: Se utilizan cuando la sentencia SELECT devuelve un solo registro y su formato es como sigue:
DECLARE
lsalario empleados.salario%TYPE;
ldni empleados.dni%TYPE;
BEGIN
SELECT salario, dni
INTO lsalario, ldni
FROM empleados
WHERE nombre = ‘Juan’
AND apellidos = ‘Rodrigo Comas’;
/* Resto de sentencias del bloque */
END;
Nota: Mucha gente considera que las sentencias UPDATE, dentro de un bloque PLSQL, son también cursores implícitos, no obstante, yo prefiero no incluirlas dentro de este concepto.
* Cursores explícitos: Se utilizan cuando la sentencia SELECT puede devolver varios registros. También se pueden utilizar en consultas que devuelvan un solo registro por razones de eficiencia con respecto a los cursores implícitos, eficiencia que mejorará especialmente si el cursor explícito se tiene que ejecutar varias veces dentro del bloque de código PL/SQL.
Un cursor explícito tiene que ser definido previamente como cualquier otra variable PLSQL y debe serle asignado un nombre. Veamos un ejemplo que muestra el DNI y el salario de los trabajadores incluidos en la tabla empleados:
DECLARE
CURSOR cemp IS
SELECT salario, dni
FROM empleados;
cepm_rec cemp%ROWTYPE;
BEGIN
FOR cemp_rec IN cemp
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
END LOOP;
END;
Los cursores explícitos admiten el uso de parámetros. Los parámetros deben declararse junto con el cursor. Por ejemplo:
DECLARE
CURSOR cemp(pnombre IN VARCHAR2) IS
SELECT salario, dni
FROM empleados
WHERE nombre = pnombre;
cepm_rec cemp%ROWTYPE;
vnombre VARCHAR2(20);
BEGIN
vnombre := 'Juan';
DBMS_OUTPUT.PUT_LINE
('Sueldo de los empleados con nombre ' || vnombre);
FOR cemp_rec IN cemp(vnombre)
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
END LOOP;
END;
Otra forma de manejar los cursores explicitos, es mediante el uso de las sentencias OPEN, FETCH y CLOSE. La sentencia OPEN identifica el cursor que se tiene que utilizar. La sentencia FETCH pone, registro a registro, los valores devueltos por el cursor en las variables correspondientes, variables que pueden estar constituidas por una lista de variables o un registro PLSQL (este es el caso de los ejemplos que incluyo en este artículo). Por último, la sentencia CLOSE cierra el cursor y libera la memoria reservada. Veamos como quedaría nuestro ejemplo utilizando este tipo de sentencias en lugar de utilizar la sentencia FOR:
DECLARE
CURSOR cemp(pnombre IN VARCHAR2) IS
SELECT salario, dni
FROM empleados
WHERE nombre = pnombre;
cepm_rec cemp%ROWTYPE;
vnombre VARCHAR2(20);
BEGIN
vnombre := 'Juan';
DBMS_OUTPUT.PUT_LINE
('Sueldo de los empleados con nombre ' || vnombre);
OPEN cemp(vnombre);
LOOP
FETCH cemp INTO cemp_rec;
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
EXIT WHEN cemp%NOTFOUND; -- Último registro.
END LOOP;
DBMS_OUTPUT.PUT_LINE
('Número de empleados procesados ' || cemp%ROWCOUNT);
CLOSE cemp;
END;
Existe una tercera opción para manejar cursores que a mí, particularmente, no me gusta utilizar pero que no quiero omitir:
DECLARE
TYPE ecursor IS REF CURSOR RETURN empleados%ROWTYPE;
cemp ecursor;
cepm_rec empleados%ROWTYPE;
BEGIN
OPEN cemp FOR SELECT * FROM empleados;
FOR cemp_rec IN cemp
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
END LOOP;
END;
Finalmente sólo mencionar que existen cuatro tipos de atributos que nos permiten controlar la ejecución de un cursor:
- %ISOPEN: Devuelve “true” si el cursor está abierto.
- %FOUND: Devuelve “true” si el registro fue satisfactoriamente procesado.
- %NOTFOUND: Devuelve “true” si el registro no pudo ser procesado. Normalmente esto ocurre cuando ya se han procesado todos los registros devueltos por el cursor.
- %ROWCOUNT: Devuelve el número de registros que han sido procesados hasta ese momento.
Escribiendo mejor SQL usando Expresiones regulares
Las expresiones regulares son una caracteristica de la base de datos Oracle version 10g, esto es una poderosa herramienta para la manipulacion de datos.
Esta caracteristica mejora la habilidad de busqueda y manipulacion de caracteres de datos. Ya que a mi ver te da mucho mas flexibilidad que los existentes comodines que brinda LIKE.
Algunos ejemplos basicos:
El operador REGEXP_LIKE
En esta consulta SQL se mostraran solo las filas de codigo postal que contengan todo lo contrario a un digito numero:
-- In the USA zip code is like a Codigo postal in Mexico SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP ----- ab123 123xy 007ab abcxy
La funcion REGEXP_INSTR
Esta funcion retorna la posicion del patron, entonces esto se vuelve mucho mas conveniente que la tradicional funcion INSTR.
El siguiente ejemplo retorna la posicion donde inicia el codigo postal de 5 digitos
SELECT
REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph,
CA 91234',
'[[:digit:]]{5}$')
AS rx_instr
FROM dual
RX_INSTR
----------
45
La funcion REGEXP_SUBSTR
Esta funcion extrae parte de la cadena.
SELECT
-- Aplicandole esta expresion regular , [^,]*,
REGEXP_SUBSTR('first field, second field , third field', ', [^,]*,')
-- usamos la tabla dual.. recuerda que no existe
FROM dual
REGEXP_SUBSTR('FIR
------------------
, second field ,
La funcion REGEXP_REPLACE
Primero demos un vistazo a la funcion SQL tradicional REPLACE, la cual sustitulle una cadena por otra. En este caso ejemplo asumimos que los datos extrañamente tienen espacios en el texto
y te gustaria remplazar estos con un solo espacio. Con la funcion REPLACE, tu tendrias que listar exactamente cuantos espacios quieres remplazar, ademas deberas tener en cuenta, que no podria ser el mismo numero de espacios extra en cada texto.
SELECT REPLACE('Joe Smith',' ', ' ')
AS replace
FROM dual
REPLACE
---------
Joe Smith
Pero si usas la funcion REGEXP_REPLACE puedes generar una solucion mas especifica para tu problematica.
SELECT REGEXP_REPLACE('Joe Smith',
'( ){2,}', ' ')
AS RX_REPLACE
FROM dual
RX_REPLACE
----------
Joe Smith
Importando funciones bash desde un archivo
Un colega el dia de ayer me comento (via email)…
Hey Edwin estoy preparando unos scripts en bash.. el detalle es que estos scripts ya estan bastante largos y me gustaria separar las funciones de los scripts que tengo en archivos independientes…
Con el fin de reutilizar codigo en otros scripts.. se puede..?
Como coños importo las funciones!
Este fue el correo que le conteste:
En la parte frontal (oseace la parte de arriba) de tu script principal (despues del #!/bin/bash)… agrega lo siguiente, teniendo en mente esto ( el punto en bash es lo mismo que una directiva include en lenguaje C):
. /ruta/a/fichero_con_funciones.sh
No pierdas de vista el espacio entre el punto y la ruta al fichero de funciones.
Ejemplo de todo lo anterior:
Primero definimos el fichero de variables(o bien tambien lo puedes llenar con funciones):
j4nusx@(none):~$ more variables.sh X="3" Y="3"
Ahora definimos un script que mande a llamar a dichas variables (aunque tambien podriamos mandar a llamar funciones!)
j4nusx@(none):~$ more ejemplo.sh
#!/bin/bash
. ./variables.sh
suma(){
echo "$X + $Y" | bc
}
resta(){
echo "$X - $Y" | bc
}
multiplicacion(){
echo "$X * $Y" | bc
}
#############################
# Aqui inicia nuestro programita shell
echo "La suma de X y Y es:"
suma
echo "La resta de X y Y es:"
resta
echo "La multiplicacion de X y Y es:"
multiplicacion
Ahora corramos nuestro ejemplo…..
j4nusx@(none):~$ chmod 755 ./ejemplo.sh && ./ejemplo.sh La suma de X y Y es: 6 La resta de X y Y es: 0 La multiplicacion de X y Y es: 9 j4nusx@(none):~$
