PL/SQL PARTE 1

Las siguientes lineas exponen TIP'S respecto a las consultas PL/SQL:

1. 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í');

1. 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.