Las siguientes lineas exponen TIP'S respecto a las consultas PL/SQL:
Si queremos usar dbms_output.put_line('ABCDEF.....'); debemos tener activada set serveroutput on size 10000
SQL> set serveroutput on size 10000
SQL> begin
2 dbms_output.put_line('Hola Mundo');
3 end;
4 /
Hola Mundo
DBMS_OUTPUT.put ('Texto sin avance');
DBMS_OUTPUT.put_line (' de línea. Acá sí');
- Definición de SubTipos:
Podemos usar subtipos para definir variables en base a un tipo base:
declare
subtype x is number(18,4);
Podemos definir subtipos de otros subtipos:
declare
temp VARCHAR2(15);
subtype x is temp%TYPE; --La longitud máxima de palabra será de 15.
Los subtipos pueden ayudar en determinados casos al tratamiento de errores, si se definen adecuadamente dentro de algún rango. Por ejemplo si tenemos una variable y sabemos que su rango será –9 … 9, podemos
hacer la definición de la siguiente forma.
CONVERSION DE TIPO DE DATO:
Podemos convertir tipo de datos de un tipo a otro de manera implicita o de maner explicita.
- TO_DATE, TO_NUMBER, TO_CHAR, etc.
Ejemplos:
La siguiente linea guarda en la variable fechaactual el valor en segundos del sistema.
SELECT TO_CHAR(SYSDATE,'SS') INTO fechaactual from dual;
- TO_CHAR (número | fecha [,’fmt’]) Convierte un número o fecha en una cadena de caracteres VARCHAR2 con el modelo de formato fmt. 9: Representa un número
0: Fuerza a que se muestra el cero
$: Signo de dólar
L: Usa el signo de moneda local
.: Imprime el punto decimal
;: Imprime el indicador de millar
Es importante que hagamos la conversión al tipo de dato de la columna de donde sacas a fin de no relentizar nuestras consultas. Las conversiones explicitas son las mejores.
Definición de Tipos
DECLARE
temp NUMBER(1,0);
SUBTYPE Escala IS temp%TYPE;
eje_x Escala; -- El rango será entre –9 y 9
eje_y Escala;
BEGIN
eje_x := 10; -- Esto nos provocará un VALUE_ERROR
En el siguiente enlace podemos hallas mas TIPS del diario quehacer del DBA Oracle: http://www.dataprix.com/oracle_decode_con_like
Uso de DEFAULT:
Se puede utilizar la palabra clave DEFAULT, en lugar del operador de
asignación, para inicializar variables. Por ejemplo, las siguientes
declaraciones:
USO DE CURSORES IMPLICITOS Y EXPLICITOS
Los cursores implicitos se usan con la instrucción SELECT que devuelven un registro
Deben tenerse en cuenta los siguientes puntos cuando se utilizan cursores implicitos:
- Con cada cursor implicito debe existir la palabra clave INTO.
- Las variables que reciben los datos devueltos por el cursor tienen que contener el mismo tipo de dato que las columnas de la tabla.
- Los cursores implicitos solo pueden devolver una única fila. En caso de que se devuelva más de una fila (o ninguna fila) se producirá una excepcion. No se preocupe si aún no sabe que es una excepcion, le valdrá conocer que es el medio por el que PL/SQL gestiona los errores.
declarevdescripcion VARCHAR2(50);
begin SELECT DESCRIPCION
INTO vdescripcion
from PAISES
WHERE CO_PAIS = 'ESP';
dbms_output.put_line('La lectura del cursor es: ' || vdescripcion);
end;
Los cursores Explicitos Los cursores explicitos se emplean para realizar consultas SELECT que pueden devolver cero filas, o más de una fila.
Para trabajar con un cursor explicito necesitamos realizar las siguientes tareas:
- Declarar el cursor (En el bloque de declaraciones).
- Abrir el cursor con la instrucción OPEN.
- Leer los datos del cursor con la instrucción FETCH.
- Cerrar el cursor y liberar los recursos con la instrucción CLOSE.
Ejemplo:
DECLARE
CURSOR cpaises
IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
co_pais VARCHAR2(3);
descripcion VARCHAR2(50);
continente VARCHAR2(25);
BEGIN
OPEN cpaises;
FETCH cpaises INTO co_pais,descripcion,continente;
CLOSE cpaises;
END;
tipo_sangre CHAR := 'O';
valido BOOLEAN := FALSE;
Pero es mejor usar el atributo %ROWTYPE
DECLARE CURSOR cpaises
IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
registro cpaises%ROWTYPE;
BEGIN
OPEN cpaises;
FETCH cpaises INTO registro;
CLOSE cpaises;
END;
Atributos de cursores
Toman los valores TRUE, FALSE o NULL dependiendo de la situación:
| Antes de abrir | Al abrir | Durante la recuperación | Al finalizar la recuperación | Después de cerrar |
%NOTFOUND | ORA-1001 | NULL | FALSE | TRUE | ORA-1001 |
%FOUND | ORA-1001 | NULL | TRUE | FALSE | ORA-1001 |
%ISOPEN | FALSE | TRUE | TRUE | TRUE | FALSE |
%ROWCOUNT | ORA-1001 | 0 | * | ** | ORA-1001 |
tipo_sangre CHAR DEFAULT 'O';
valido BOOLEAN DEFAULT FALSE; Se utiliza DEFAULT para las variables que tienen un valor típico, mientras que el operador de asignación, se usa en aquellos casos en que las variables no tienen dicho valor, como por ejemplo en contadores y acumuladores. Veamos un ejemplo:
horas_trabajo INTEGER DEFAULT 40;
contador INTEGER:=0;
Uso de NOT NULL:
Además de inponer un valor por defecto a una variable, podemos restringuir a una variable para que sea NOT NULL:id_acc INTEGER(4) NOT NULL := 9999;
Siempre que se asigne como NOT NULL debemos inicializar la variable a algún valorid_acc INTEGER(4) NOT NULL; -- Falta la inicialización… los subtipos NATURALN y POSITIVEN, ya están predefinidos como NOT NULL.
cont_emp NATURAL NOT NULL := 0;
cont_emp NATURALN := 0; -- La sentencia de arriba y esta, son equivalentes…
cont_emp NATURALN; -- Declaración Ilegal, falta la inicialización…
USO DE TYPE
El atributo TYPE se usara para definir un tipo de dato a una variable, usado bastante para declarar una variable a un tipo de dato de una columna de b.d.
Ejm 1:
fechanacimiento globalweb.usuario.fechanacimiento%TYPE;
Con esto definimos a fechanacimiento de tipo de datos a la columna fechanacimiento de la table usuario.
Ejm 2:
SET SERVEROUTPUT ON SIZE 10000;
declare
region regions.region_name%type;
begin
SELECT REGION_NAME INTO region from regions where region_id = 4;
dbms_output.put_line(region);
end;
Seleccionar TOP Filas (Usar la seudocolumna "ROWNUM")
SELECT employee_id FROM employees where rownum < 5;
En la siguiente linea devolvemos las 4 primeras tuplas de la tabla employees.