Augusto Humire Martinez web blog

algo de web-in

Tutorial de Oracle PL/SQL

Hace unos dias me toco lidiar con DBs Oracle, asi que requeri hacer alguunas cosas por a qui unas cosas por alla, y en resumen felizmente no era administracion si no algunas consultas simples pero que simplificaban la vida a mas de uno, en resumen si sabes SQL (No sql server)  me refiero al Structure Query Language sabes el resto de DBs, claro que cada DB tiene su propio potencial cuando hablamos de procedimientos almacenados o funciones etc, cosas que ya tengan que ver con programar, lo que algunos no les gusta ni les gustara, en fin, no todos nacen para ser eso no? quizas nacieron para hacer otra cosa, pero es tema aparte, como les decia tuve que lidiar con algo de PL/SQL (a mi parecer muy personal el lenguaje transaccional mas impecable), que en realidad es muy parecido al PLpgSQL el lenguaje de Postgres (algunas niñas le dicen Postres, me canso de repetir que se dice P O S T G R E S Q L y no “postres”) .

Bueno como recomendacion les dire que cuando creen sus bases de datos y sus tablas y todo cuanta descripcion pueda ser leida sea de forma clara y transparente es decir si tiene una tabla que contendra las “solicitutes de credito” pues como nombre deberian poner SolicitudCredito (idioma castellano), y no nombres abreviados o acronimos como “SolCre”  (idioma Namekusei), que resulta a la larga una forma ineficiente de describir los objetos en nuestra BD y nos puede llevar a dolores de cabeza cuando hay que hacer reingenieria, control, administracion o implementación de la base de datos y sus elementos.

Procedimientos Almacenados en Oracle

Haber entonces, y como se crea una procedimiento almacenado en oracle?, bien en realidad es sencillo

create or replace procedure miProcedimiento
 as
 fecha date;
 begin
 select sysdate into fecha from dual;
 dbms_output.put_line(fecha);
 end;

En este codigo anterior hemos nombrado a nuestro procedimiento como miProcedimiento, luego hemos declarado una variable de tipo date llamada fecha, para comenzar con las lineas de codigo pl/sql deberá de comenzar con la palabra clave begin y tendra que terminar con end; atencion al punto y coma ” ; ” , las instrucciones deben cerrarse con un punto y coma; y lo que hemos hecho en el select es extraer la fecha del sistema utilizando la funcion sysdate y su resultado lo insertamos utilizando into a la variable fecha creadad anteriormente, seguido de ello lo creamos presionando F8, ahora para poder utlizarlo creamos una seccion de codigo y llamaremos a nuestro procedimiento

begin
 miProcedimiento;
 end;

el resultado se muestra en el output como se ve en la siguiente figura.

Sequence o secuencias en Oracle

Entonces con lo primero que me topé fue con la duda, como se hace un IDENTITY al estilo de SQL Server?, chesu, no habia Identity, y ahora? bueno resulta que para hacer algo parecido al Identity en Oracle se tiene que trabaja con un SEQUENCE crear un trigger y aplicarlos desde alli a la tabla que requerimos, medio rolludo no? pero que se le hace, viva Oracle.

Y los mas sapientes diran… pero porque no usas un identificador como “0001”, “0002”, “0003”, “0004”, …”000N”, en realidad no me gusta usar ello por que me parece complicado llamar a los registros por una pk tan larga, ademas que concidero que para indexar, mejor es un numero(integer) que varias letras(varchar).

bueno al grano, la idea es crear una tabla que tenga un campo identificador del tipo integer que sea autonumerico o Identity para los amantes del SQL Server y que de paso sea una llave primaria ¿facil es hablar no? ¿mas facil es hacer?, no manches sherk, entonces como se crea una secuancia o sequence?

CREATE SEQUENCE SQ_DADLOGE; /*esta es la forma facil*/
CREATE SEQUENCE SQ_DADLOGE /*esta es la forma yuca*/
 INCREMENT BY 1
 START WITH 1
 NOMAXVALUE
 NOCYCLE
 CACHE 10;

ambas funcionan, luego creamos la tabla que en mi caso se llama DADLOGE a la que queramos aplicarle la secuencia, y le definimos un campo con cualquier nombre, en mi caso N_IDENTI y tiene que ser del tipo NUMBER

CREATE TABLE DADLOGE
 (
 N_IDENTI NUMBER PRIMARY KEY
 , C_NOMTAB VARCHAR2(50) /*NOMBRETABLA*/
 , C_NOMREG VARCHAR2(50) /*NOMBREREGLA*/
  , C_IDREGI VARCHAR2(50) /*IDREGISTRO*/
  , C_NOMCAM VARCHAR2(50) /*NOMBRECAMPO*/
  , C_VALCAM VARCHAR2(50) /*VALORCAMPO*/
  , C_TABLAR VARCHAR2(50) /*TABLAREFERENCIA*/
  , C_IDREGR VARCHAR2(50) /*IDREGISTROREFERENCIA*/
  , C_NOMCAR VARCHAR2(50) /*NOMBRECAMPOREFERENCIA*/
  , C_VALCAR VARCHAR2(50) /*VALORCAMPOREFERENCIA*/
  , C_OBSERV VARCHAR2(255) /*OBSERVACION*/
  , D_FECTES DATE DEFAULT CURRENT_TIMESTAMP /*FECHATEST*/
  );

una vez creada la secuencia, y la tabla a la que le aplicaremos la secuencia tenemos que relacionarla, la forma practica es creando un trigger en mi caso mi trigger se llama tr_ToSeqInDADLOGE, entonces como se crea un trigger?

CREATE OR REPLACE TRIGGER tr_ToSeqInDADLOGE /*SECUENCIA PARA DADLOGE*/
 BEFORE INSERT ON DADLOGE FOR EACH ROW
 WHEN(NEW.N_IDENTI IS NULL)
 BEGIN
 SELECT SQ_DADLOGE.NEXTVAL INTO :NEW.N_IDENTI FROM DUAL;
 END;

Con eso tenemos resuelto nuestro problema del identity, y ahora para usarlo solo hariamos un insert como siempre.

INSERT INTO DADLOGE
 (
 C_NOMTAB
 ,C_NOMREG
 ,C_IDREGI
 ,C_NOMCAM
 ,C_VALCAM
 ,C_TABLAR
 ,C_IDREGR
 ,C_NOMCAR
 ,C_VALCAR
 )
 
VALUES
 (
 'TABLA'
 ,'1'
 ,'3'
 ,'CAMPO1'
 ,'VALOR'
 ,'1'
 ,'3'
 ,'CAMPO1'
 ,'VALOR'
 );

Commit;

Como es de esperarse por ningun lado pongo el campo N_IDENTI que representa el identificador con el INDETITY O Autonumerico.

ya que hemos creado procedimientos, quizas quieras saber como se crea una funcion que me retorne un valor, pues bien ahi les va, entonces como creo una funcion en oracle?

Functions o funciones en Oracle

create or replace function miFuncion
 return date as fecha date;
 begin
 select sysdate into fecha from dual;
 return fecha;
 end;

ahora si queremos usar la funcion o invocarla podemos hacerlo desde un procedimiento almacenado

create or replace procedure miProcedimiento2
 as
 fecha date;
 begin
 fecha := miFuncion();
 dbms_output.put_line(fecha);
 end;

y luego probamos con

begin
  miProcedimiento2;
 end;
 

o desde un select sencillo de la siguiente manera

select miFuncion from dual;

en realidad dentro de begin se puede hacer todo lo que nuestra mente pueda imaginarse con lo que a consultas se refiere por ejemplo podria querer devolver el count de una determinada consulta y seria asi:

create or replace function miFuncion
 return number as total_registros number;
 begin
 select count(*) into total_registros from Pagos;
 return total_registros;
 end;

y para llamarlo usamos

select miFuncion from dual;

muy similar con la diferencia del tipo de dato que retorno.

Cursores Implicitos y Explicitos

Algunas veces seguramente hemos querido recorrer un select por alguna razon, para sumar quizas para restar o comprobar entre otros, entonces irremediablemente habremos querido utilizar cursores, un cursos es como un enumerador pero que solo viaja de ida, es decir recorrer el resultado de un select solo de ida sin la posibilidad de ir a ningun registro anterior.

En Oracle podemo usar los cursores implicitos y los cursores explicitos, un cursor implicito quiere decir no que se declara ningun variable del tipo cursor si no que se inserta la sentencia que queremos directamente en el LOOP o iterador como se ve en el siguiente ejemplo:

/*FOR con un cursor implicito*/

DECLARE
 CODIGO NUMBER;
 NOMBRECOMPLETO VARCHAR2(255);
 AÑOS NUMBER;
 BEGIN
 FOR RESULTADO IN (SELECT * FROM CLIENTES)
 LOOP
 AÑOS := TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) -  TO_NUMBER(TO_CHAR(RESULTADO.NACIMIENTO,'YYYY'));
 DBMS_OUTPUT.PUT_LINE(RESULTADO.ID||' - '||RESULTADO.APELLIDOPATERNO||' '||RESULTADO.APELLIDOPATERNO||' : '|| AÑOS);
  END LOOP;
 END;

/*FOR con un cursor explicito*/

DECLARE
 CODIGO NUMBER;
 NOMBRECOMPLETO VARCHAR2(255);
 AÑOS NUMBER;
 CURSOR MyCursor IS SELECT * FROM CLIENTES;
 BEGIN
 FOR RESULTADO IN MyCursor
 LOOP
 AÑOS := TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) -  TO_NUMBER(TO_CHAR(RESULTADO.NACIMIENTO,'YYYY'));
 DBMS_OUTPUT.PUT_LINE(RESULTADO.ID||' - '||RESULTADO.APELLIDOPATERNO||' '||RESULTADO.APELLIDOPATERNO||' : '|| AÑOS);
 END LOOP;
 END;

Sentencia IF THEN ELSE

Como sabemos la sentencia IF nos permite hacer bifurcacion para ejecutar una determinada sentencia si se cumple una determinada condicion

DECLARE
  sales  NUMBER(8,2) := 12100;
  quota  NUMBER(8,2) := 10000;
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 120;
BEGIN
  IF sales > (quota + 200) THEN
     bonus := (sales - quota)/4;
  ELSE
     bonus := 50;
  END IF;
  UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
END;
/

Sentecia IF THEN ELSEIF

Alugunas veces requerimos preguntar por mas de una condicion y en cada una ellas ejecutar la sentencia correspondiente,  eso nos lleva a la estructura IF THEN ELSEIF

DECLARE
  sales  NUMBER(8,2) := 20000;
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 120;
BEGIN
   IF sales > 50000 THEN
      bonus := 1500;
   ELSIF sales > 35000 THEN
      bonus := 500;
   ELSE
      bonus := 100;
   END IF;
   UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
END;
/

Sentencia CASE

Esta sentencia nos permite evaluar una variable para variados valores de la unica variable, y si no se cumple ninguna de las comparaciones entonces nos ofrece una salida atravez de else como se ve en el siguiente ejemplo

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;

Sentencia EXIT

Esta sentecia como expresa su propio nombre significa “salir”, pero de donde sale? pues sale de estructuras LOOP, por ejemplo si quisieramos romper una sentencia FOR o terminarlo utilizariamos la sentencia EXIT

DECLARE
  credit_rating NUMBER := 0;
BEGIN
  LOOP
    credit_rating := credit_rating + 1;
    IF credit_rating > 3 THEN
       EXIT;  -- exit loop immediately
    END IF;
 END LOOP;
 -- control resumes here
 DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));
 IF credit_rating > 3 THEN
       RETURN;  -- use RETURN not EXIT when outside a LOOP
 END IF;
 DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));
END;
/

Tambien podemos expresar la sentencia EXIT de diferentes formas

IF count > 100 THEN EXIT; ENDIF;
EXIT WHEN count > 100;

Ambas entradas cumplen el mismo objetivo, solo que estan expresadas de manera diferente, cada quien utilizará la forma que mas se acomode, como repito ambas sentencias cumplen el mismo objetivo.

Consultas Dinamicas

Mientras avanzaba con los scripts que tenia que crear, recorde que uno de los requerimientos era encontrar todos los campos de los registros cuyo tipo de dato sea number y que sean negativos, es decir una de las concistenciaciones de datos era encontrar los numeros de toda la base de datos que sean negativos, uuuuuufffff y ahora como haces eso?, pues bueno tuve que recurrir a las consultas dinamicas, y que es una
consulta dinamica?, bueno una consulta dinamica es generar y ejecutar una sentencia sql dinamicamente, en el buen castellano por ejemplo mmmmm… como explicarlo…. mmmmm . ok.  Si tuviera select * from tabla y quisiera que tabla sea pueda ser cualquier nombre de tabla de la base de datos entonces tendria que cambiar tabla dinamicamente algo asi como esto “select * from variable_tabla“, un ejemplo sencillo primero de como usar esto y luego uno un poco mas complejo.

CREATE OR REPLACE PROCEDURE EXEC_SQLQUERY (TABLENAME VARCHAR2, ID NUMBER)
as
SQLQUERY VARCHAR2(255);
begin
SQLQUERY := ‘DELETE FROM ‘||TABLENAME||’ WHERE ID = ‘||ID;
EXECUTE IMMEDIATE SQLQUERY;
end;

Como se puede apreciar este procedimiento recibe 2 argumentos, el primer argumento recibre el nombre de la tabla y el segundo argumento el id o identificador del registro, entoces antes de usar nuestro procedimiento tenemos

ahora usaremos el procedimiento de la siguiente manera

BEGIN
EXEC_SQLQUERY(‘CLIENTES’, 3);
END;

Entonces lo que genero este procedimiento fue en realidad

DELETE FROM CLIENTES WHERE ID = 3

y nuestra tabla quedaria asi

y con el EXECUTE IMMEDIATE lo hemos ejecutado, asi entonces con sql dinamico se puede generar sentencias sql de cualquier tipo para cualquier fin, solo es necesario general la sentencia sql correctamente.

bien ese fue el ejemplo sencillo, ahora uno de regular peso, la idea como les decia parrafos anteriores es encontrar todos los campos numericos negativos, por que por motivos de migración queda la probabilidad de que algo se nos haya escapado, entonces para poder encontrar nuestros indeseados negativos usamos una funcion a la que le paso una sentencia sql y a la vez la uso desde un procedimiento de la siguiente manera

CREATE OR REPLACE FUNCTION FN_CHECKNEGATIVES(SQLQUERY VARCHAR2) RETURN NUMBER
AS
   ROWSCOUNTED NUMBER;
BEGIN
  EXECUTE IMMEDIATE SQLQUERY INTO ROWSCOUNTED;
  RETURN ROWSCOUNTED;
END;

una vez creata la funcion creamos el siguiente procedimiento

CREATE OR REPLACE PROCEDURE PS_CHECKNEGATIVOS
AS
       --<REGLA-NEGATIVOS>
       SQLQUERY    VARCHAR(255);
       ENCONTRADOS INTEGER;
       --<REGLA-NEGATIVOS>
BEGIN
FOR TABLA IN (SELECT DISTINCT TABLE_NAME
                  FROM USER_TAB_COLS
                 ORDER BY TABLE_NAME) LOOP
    FOR CAMPO IN (SELECT COLUMN_NAME, DATA_TYPE
                    FROM USER_TAB_COLS
                   WHERE TABLE_NAME = TABLA.TABLE_NAME) LOOP
      IF (CAMPO.DATA_TYPE = 'NUMBER') THEN
        SQLQUERY := 'SELECT COUNT(' || CAMPO.COLUMN_NAME || ') FROM ' ||
                    TABLA.TABLE_NAME || ' WHERE ' || CAMPO.COLUMN_NAME ||
                    ' < 0';
        SELECT FN_CHECKNEGATIVES(SQLQUERY) INTO ENCONTRADOS FROM DUAL;
        IF ENCONTRADOS > 0 THEN
          DBMS_OUTPUT.put_line(TABLA.TABLE_NAME || '.' || CAMPO.COLUMN_NAME || ' tiene ' ||
             ENCONTRADOS || ' valor(es) inferior(es) a cero');
        END IF;
      END IF;
    END LOOP;
  END LOOP;
END;

Una vez creado el procedimiento asegurese que es un usuario que tiene acceso al esquema de las tablas creadas por Ud., procure no usar un acceso del tipo SYSDBA o similar, por que la SYSDBA tiene acceso a toooooooooodas las tablas de Oracle por lo tanto demoraria mucho la consulta, aun que tenga inclusive una sola tabla en su DB puesto que SYSDBA tiene acceso a todas las tablas del sistema no recomiendo que se ejecute este procedimiento con privilegios de SYSDBA, entonces queda la advertencia, si Ud. insiste es cosa suya.

y para ejecutar la consulta ejecutamos lo siguiente

BEGIN
      PS_CHECKNEGATIVOS;
END;

Como podra ver lo que se hace es mostrar el resultado en el OUTPUT, sin embargo podria reemplazar la linea del output por una insercion a su propia tabla de validaciones y asi registrar las columnas con valores negativos.

nota: los caracteres “||” sirven para concatenar expresiones de cadena

otra nota: con la consulta

SELECT DISTINCT TABLE_NAME
                  FROM USER_TAB_COLS
                 ORDER BY TABLE_NAME

lo que hacemos es obtener el nombre de las tablas de oracle a las que tenemos acceso

otra nota mas: con la consulta

SELECT COLUMN_NAME, DATA_TYPE
                    FROM USER_TAB_COLS
                   WHERE TABLE_NAME = TABLA.TABLE_NAME

lo que estamos obtener el nombre y el tipo de dato de la columna segun cada tabla de oracle a los que tenemos acceso

las tildes en este texto fueron retiradas intensionalmente

septiembre 13, 2008 - Posted by | Base de Datos |

8 comentarios »

  1. Hola, gracias por la información, quisiera saber si dentro de una Función se puede tener un cursor, si es así…tenés un ejemplo para ver.

    Gracias.

    Comentario por Hugo Mira | octubre 1, 2008

  2. saludos, en realidad dentro de una function puedes hacer casi lo mismo que en cualquier estructura de PLSQL, ahi te envio el ejemplo
    create or replace function GetTotalClientes return number
    as
    numero number;
    begin
    numero := 0;
    for fila in (select * from clientes)
    loop
    numero := numero + 1;
    end loop;
    return numero;
    end;

    y para que lo puedas probar

    select GetTotalClientes from dual

    Comentario por augustohumire | octubre 1, 2008

  3. Hola soy nuevo en esto y mi pregunta una vez creado el procedimiento para ir dejando el autonumerico de uno en uno, este ultimo al borrar los datos el autonumerico igual sigue sumando uno en uno este se puede volver a dejar a “1” GRACIAS

    Comentario por alex | agosto 11, 2010

  4. Hola, como se haria para crear un procedimiento para poder ejecutar lo siguiente: select * from marca_autos, me gustaria hacerlo pero en un procedimiento.

    Comentario por Hernan | diciembre 12, 2012

  5. como muestro en select que retorna mas de un valor con un procedimiento almacenado.

    Comentario por Ronald | marzo 29, 2013

  6. aa… pero no con el dbms_output.

    Comentario por Ronald | marzo 29, 2013

  7. create or replace
    PROCEDURE validar ( us out number, pasw out varchar2)
    IS

    –Declaracion de las variables locales
    BEGIN
    –SENTENCIAS
    select xd.idusuario, xd.psw
    into us, pasw
    from usuariolc xd
    where idusuario = us and psw = pasw;
    dbms_output.put_line(us, pasw);

    EXCEPTION
    when others then
    ROLLBACK WORK;
    DBMS_OUTPUT.PUT_LINE(‘ERROR EN LA TRANSACCIÓN’);
    END validar;

    cuando hago el execute validar(1278, ‘hola2’); dice que los valores que mando no se pueden utilizar como destino de asignacion

    Comentario por Mariana_ChiQ | enero 5, 2015

  8. Hope to wake up from this (everyday a new)nightmare of a government!
    Cartier love bracelet girl yellow gold http://cartierlovebraceletladyyellowgold.blogspot.com/

    Comentario por pkfrcphg@hotmail.com | enero 15, 2015


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Salir /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Salir /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Salir /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Salir /  Cambiar )

Conectando a %s

A %d blogueros les gusta esto: