Package

create or replace package MiPrimerPaquete is --creamos el encabezado
  FUNCTION CUENTAEMPLEADOS(pDepto in employees.department_id%type) return number;
end MiPrimerPaquete;


create or replace package body MiPrimerPaquete is
function CUENTAEMPLEADOS(pDepto IN employees.department_id%type)  
RETURN NUMBER AS
totalEmpleados integer;
begin
    select count(*) into totalEmpleados
    from Employees
    where department_id = pDepto;
    RETURN totalEmpleados;
  end CUENTAEMPLEADOS;
end MiPrimerPaquete;

select MiPrimerPaquete.CUENTAEMPLEADOS(90) FROM DUAL;

Triggers

create table autor(
id_autor int,
constraint pk_id_autor primary key(id_autor));

create table libro(
id_libro int,
id_autor int,
constraint pk_id_libro primary key(id_libro),
constraint fk_id_autor foreign key(id_autor) references autor);


ALTER TABLE libro
disable CONSTRAINT fk_id_autor;

insert into autor values(12);
insert into libro values(5,12);

delete from autor
where id_autor = 11;

select * from libro;
select * from autor;

TRIGGER

create or replace trigger miPrimerTrigger
after delete on autor
references old as libroViejo
for each row
begin
     delete from libro
     where id_autor = :libroviejo.id_autor;
    
end;

show err trigger miPrimerTrigger;

delete from autor
where id_autor = 11;

Procedimientos Almacenados

-- Procedimiento Almacenado OUT

create or replace procedure primerProcedure (ptexto out varchar2) is
begin
ptexto := 'Este valor fue modificado en mi procAlmacenado!!';
end primerProcedure;

set serveroutput on
declare
vTexto varchar2(60) := 'Hola po CTM';
begin
primerProcedure(vTexto);
dbms_output.put_line('Mi Mensaje: '||vTexto);
end;

-- Procedimiento Almacenado IN OUT

create or replace procedure segundoProcedure (ptexto in out varchar2) is
begin
dbms_output.put_line('Mensaje de Inicio: '||pTexto);
ptexto := 'Este valor fue modificado en mi procAlmacenado!!';
end segundoProcedure;

set serveroutput on
declare
vTexto varchar2(60) := 'Hola po CTM';
begin
segundoProcedure(vTexto);
dbms_output.put_line('Mi Mensaje: '||vTexto);
end;

-- Procedimiento Almacenado CONCATENAR

create or replace procedure Concatenar (ptexto1 varchar2, ptexto2 varchar2) is
begin
dbms_output.put_line(pTexto1||' '||pTexto2);
end Concatenar;

show err procedure Concatenar;

set serveroutput on
declare
vTexto varchar2(60) := 'Hola po CTM';
begin
Concatenar('Buenas', 'Cachitas');
Concatenar(ptexto2=>'Buenas', ptexto1=>'Cachitas');
end;

-- Funciones

create or replace function funcParImpar(pNumero integer) is
msg varchar2(5) := 'Impar'
begin
if mod(pNumero,2) = 0 then
msg := 'Par';
end if;
return msg;
end;

create or replace function funcConcatenar(ptexto1 varchar(2), ptexto2 varchar(2)) is
begin
return ptexto1||' '||ptexto2;
end;

select funcConcatenar('7')

Cursores 50% Control

Cursor con CICLO LOOP

set serveroutput on
DECLARE
  CURSOR ceEmpleado(pJOB VARCHAR2)
  IS
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
  FROM EMPLOYEES
  where JOB_ID = pJOB;
 
    vID VARCHAR2(3);
    vNombre VARCHAR2(50);
    vApellido VARCHAR2(50);
    vSueldo INTEGER;
     
  BEGIN
    OPEN ceEmpleado('&pJOB');
    LOOP
        FETCH ceEmpleado INTO vID, vNombre, vApellido, vSueldo;
        EXIT WHEN ceEmpleado%NOTFOUND;
        dbms_output.put_line('ID: '||vID||' / '||'Nombre: '||vNombre||' / '||'Apellido: '||vApellido||' / '||'Sueldo: $'||vSueldo);
    END LOOP;
    CLOSE ceEmpleado;
END;





  -------------------------------------------------------
 
  set serveroutput on ---Cursor con CICLO WHILE
declare
  cursor crEmpleado(pJOB VARCHAR2)
    is select employee_id, first_name, salary
    from employees;
   
    registro crEmpleado%ROWTYPE;
begin
 open crEmpleado('&JOB');
 fetch crEmpleado into registro;
 while crEmpleado%FOUND loop
    dbms_output.put_line('Id:' ||registro.employee_id||' - '||' Nombre '||registro.first_name||'-'||' Salario '||' - '||registro.salary);
    fetch crEmpleado into registro;
  end loop;
  close crEmpleado;
end;
------------------------------------------------------------
  set serveroutput on---------CURSOR CON CICLO FOR
declare
  cursor crEmpleado
    is select employee_id, first_name, salary
    from employees;
begin
 for registro in crEmpleado loop
    dbms_output.put_line('Id:' ||registro.employee_id||' - '||' Nombre '||registro.first_name||'-'||' Salario '||' - '||registro.salary);
  end loop;
 end;
 
 
 
 

Cursores - Procedimiento Almacenado

--CURSOR 1
set serveroutput on
DECLARE
 vPropietario varchar2(40);
 vNombreTabla varchar2(40);
    vNombreColumna varchar2(100);
        /* Primer cursor */
        cursor obtieneTablas(pPropietario all_tables.owner%type ) is
        select distinct t.owner, t.table_name
        from all_tables t
        where t.owner = pPropietario;
        /* Segundo cursor */
        cursor obtieneColumnas is
        select distinct c.column_name
        from all_tab_columns c
        where c.owner = vPropietario
        and c.table_name = vNombreTabla;      
begin
   open obtieneTablas('HR');
dbms_output.put_line('Abriendo Cursor - obtieneTablas');
loop fetch obtieneTablas into vPropietario, vNombreTabla;
   exit when obtieneTablas%NOTFOUND;
   dbms_output.put_line('Tabla : '||vPropietario||'.'||vNombreTabla);
       open obtieneColumnas;
       loop fetch obtieneColumnas into vNombreColumna;
     exit when obtieneColumnas%NOTFOUND;
         dbms_output.put_line('=>'||vNombreTabla||'.'||vNombreColumna);
       end loop;
       close obtieneColumnas;
   end loop;
close obtieneTablas;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Se ha detectado un error - '||SQLCODE||' -ERROR- '||SQLERRM);
end;

--CURSOR CON PROCEDIMIENTO ALMACENADO

CREATE OR REPLACE PROCEDURE CATUSER AS
vPropietario varchar2(40);
vNombreTabla varchar2(40);
vNombreColumna varchar2(100);
        /* Primer cursor */
        cursor obtieneTablas(pPropietario all_tables.owner%type ) is
        select distinct t.owner, t.table_name
        from all_tables t
        where t.owner = pPropietario;
        /* Segundo cursor */
        cursor obtieneColumnas is
        select distinct c.column_name
        from all_tab_columns c
        where c.owner = vPropietario
        and c.table_name = vNombreTabla;      
begin
   open obtieneTablas('HR');
dbms_output.put_line('Abriendo Cursor - obtieneTablas');
loop fetch obtieneTablas into vPropietario, vNombreTabla;
   exit when obtieneTablas%NOTFOUND;
   dbms_output.put_line('Tabla : '||vPropietario||'.'||vNombreTabla);
       open obtieneColumnas;
       loop fetch obtieneColumnas into vNombreColumna;
     exit when obtieneColumnas%NOTFOUND;
         dbms_output.put_line('=>'||vNombreTabla||'.'||vNombreColumna);
       end loop;
       close obtieneColumnas;
   end loop;
close obtieneTablas;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Se ha detectado un error - '||SQLCODE||' -ERROR- '||SQLERRM);
end CATUSER;

-----CURSOR 2



create or replace PROCEDURE CATUSER (pOwner all_tables.owner%type) as --indicamos el tipo de parametro
vPropietario all_tables.owner%type;
vNombreTabla   varchar2(40);
vNombreColumna varchar2(100);
        /* Primer cursor */
        cursor obtieneTablas(pPropietario all_tables.owner%type) is
        select distinct t.owner, t.table_name
        from all_tables t
        where t.owner = pPropietario;
        /* Segundo cursor */
        cursor obtieneColumnas is
        select distinct c.column_name
        from all_tab_columns c
        where c.owner = vPropietario
        and c.table_name = vNombreTabla;      
begin
   open obtieneTablas(pOwner);--definimos el parametros a consultar
dbms_output.put_line('Abriendo Cursor - obtieneTablas');
loop fetch obtieneTablas into vPropietario, vNombreTabla;
   exit when obtieneTablas%NOTFOUND;
   dbms_output.put_line('Tabla : '||vPropietario||'.'||vNombreTabla);
       open obtieneColumnas;
       loop fetch obtieneColumnas into vNombreColumna;
     exit when obtieneColumnas%NOTFOUND;
         dbms_output.put_line('=>'||vNombreTabla||'.'||vNombreColumna);
       end loop;
       close obtieneColumnas;
   end loop;
close obtieneTablas;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Se ha detectado un error - '||SQLCODE||' -ERROR- '||SQLERRM);
end CATUSER;

Cursores

---------------------------------------------------------------------------------------------------------

CURSOR 1.0

set serveroutput on

declare

cursor miPrimerCursor is 

  select EMPLOYEE_ID, FIRST_NAME from employees;

vnombre employees.first_name%Type;

vid employees.employee_id%Type;

begin

open miPrimerCursor;

loop

  fetch miPrimerCursor into vid,vnombre;

  exit when miPrimerCursor%NOTFOUND;

  dbms_output.put_line('ID: '||vid||' - ' ||'Nombre: '||vnombre);

end loop;

close miPrimerCursor;

end;

---------------------------------------------------------------------------------------------------------

CURSOR 2.0

set serveroutput on

declare

cursor miPrimerCursor is 

  select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE from employees order by hire_date desc;

vnombre employees.first_name%Type;

vid employees.employee_id%Type;

vFechaContrato employees.HIRE_DATE%Type;

antiguedad integer;

begin

open miPrimerCursor;

loop

  fetch miPrimerCursor into vid,vnombre,vFechaContrato;

  exit when miPrimerCursor%NOTFOUND;

  antiguedad := round((sysdate - vFechaContrato)/365);

  if(antiguedad = 12) then

  dbms_output.put_line(antiguedad||' * ' || ' ID: '||vid||' - ' ||'Nombre: '||vnombre);

  end if;

end loop;

close miPrimerCursor;

end;

---------------------------------------------------------------------------------------------------------

DOS CURSORES

--select TABLE_NAME from all_tables where owner= 'HR'; --obtener tablas

--select OWNER,TABLE_NAME,column_name from all_tab_columns where TABLE_NAME = 'JOBS' AND owner = 'HR'; -- obtener columnas

set serveroutput on -- para que se vean los dbms :D 

declare

nomTabla all_tables.table_name%Type;

nomColumna all_tab_columns.column_name%Type;

cursor CursorTabla is select TABLE_NAME from all_tables where owner ='HR';

cursor CursorColumna is select column_name from all_tab_columns where owner ='HR' ;

BEGIN

open CursorTabla;

loop

fetch CursorTabla into nomTabla;

  exit when CursorTabla%NOTFOUND;

  open CursorColumna;

  loop

  fetch CursorColumna into nomColumna;

    exit when CursorColumna%NOTFOUND;

    dbms_output.put_line(nomTabla || ' - ' || nomColumna);

  end loop;

  close CursorColumna;

end loop;

close CursorTabla;

end;

PL SQL- "Oracle 10g"

TIPOS DE DATOS ORACLE 

CHAR(n):
Cadena de caracteres de longitud fija, tiene un tamaño n bytes.

Si no se especifica n la ORACLE le da un tamaño de 255 bytes.

El tamaño maximo en BD es 2000 bytes y el minimo 1 byte.

El tamaño maximo en PL/SQL es 32767 bytes y el minimo 1 byte.

CHARACTER es sinonimo de CHAR.

VARCHAR2(n):
Cadena de caracteres de longitud variable, tiene un tamaño maximo de n bytes.

Es obligatorio especificar el tamaño.

El tamaño maximo en BD es 4000 bytes y el m?nimo 1 byte.

El tamaño maximo en PL/SQL es 32767 bytes y el minimo 1 byte.

STRING y VARCHAR son sinonimos de VARCHAR2. Ver NVARCHAR2.

Usando VARCHAR2 en lugar de CHAR ahorramos espacio de almamcenamiento.

Un char(10) almacenar?     'PEPE      '
Un varchar2(10) almacenar? 'PEPE'


NUMBER(p,s)
Numero de p digitos de los cuales s son decimales.

No es obligatorio especificar el tama?o.

El tamaño de p va de 1 a 38 y el s desde -84 a 127.

El tamaño en PL/SQL 1E-130 .. 10E125.

Sinonimos:
numeros de coma fija: DEC,DECIMAL,NUMERIC
enteros:INTEGER (sinonimo de NUMBER(38)),INT,SMALLINT
coma flotante:DOUBLE PRECISION FLOAT REAL.
Ver tambien: PLS_INTEGER, BINARY_INTEGER

El valor 7,456,123.89 se almacenar? como:

NUMBER(9)     7456124
NUMBER(9,1)   7456123.9
NUMBER(*,1)   7456123.9  
NUMBER(9,2)   7456123.89
NUMBER(6)    [error]
NUMBER(7,-2)  7456100
NUMBER        7456123.89
FLOAT         7456123.89
FLOAT(12)     7456000.0


DATE
Fecha valida.

Desde el 1 de enero del 4712 AC hasta el 31 de diciembre del 9999 DC. (en Oracle7 = 4712 DC)

LONG
Cadena de caracteres de longitud variable. Es una versi?n m?s grande de VARCHAR2.

El tamaño maximo en BD es 2 Gigabytes.

CLOB
Cadena de caracteres de longitud variable. Es una versi?n m?s grande de VARCHAR2.

El tamaño maximo en BD es 4 Gigabytes. Ver NCLOB.

Es recomendable usar CLOB o BLOB en lugar de LONG.


BLOB
Objeto binario de longitud variable. Es una versi?n m?s grande de RAW.

El tamaño maximo en BD es 4 Gigabytes.

BFILE
Puntero a un fichero en disco.

El tamaño maximoen BD es 4 Gigabytes.

TIMESTAMP (f)
El timestamp es un fecha que contiene un granularidad superior al tipo DATE, eso significa que contiene fracciones de segundo.

Con f definimos el numero de digitos que queremos en la fraccion de segundo. Asi, f puedes valer desde 0 hasta 9, el valor por defecto es 6.

SELECT SYSTIMESTAMP FROM DUAL;

Podemos usar WITH {LOCAL} TIMEZONE para grabar con el desplazamiento de hora local.

INTERVAL YEAR (y) TO MONTH
Periodo de tiempo definido en a?os y meses donde y es el n?mero de digitos del a?o. Puede valer de 0 a 9. (por defecto = 2)

INTERVAL DAY (d) TO SECOND (f)
Es un periodo de tiempo definido en dias, horas, minutos y segundos. d es el maximo numero de digitos en el dia f es el maximo numero de digitos en el campo de segundos. d va de 0 a 9. (por defecto = 2) fva de 0 a 9. (por defecto = 6)

ROWID
Cadena hexadecimal que representa de forma ?nica una fila en una tabla (pero no unica en cualquier tabla). Ver funci?n ROWID.
UROWID
Cadena hexadecimal que representa de forma ?nica una fila ORDENADA en una tabla (pero no unica en cualquier tabla). Ver funci?n ROWID.
RAW(n)
Objeto binario de longitud variable.

Es obligatorio especificar el tama?o.

El tamaño maximo en BD es 2000 bytes y el m?nimo 1 byte.

El tamaño maximo en PL/SQL es 32767 bytes y el minimo 1 byte.

LONG RAW
Objeto binario de longitud variable.

El tamaño maximo en BD es 2 Gigabytes.

El tamaño maximo en PL/SQL es 32767 bytes y el minimo 1 byte.

FUNCIONES DE CONVERSIONES

ORACLE - Funciones Conversión : To_Char

La funcion To_char convierte un numero o una fecha en un campo string o caracter.

La sintaxis es:
to_char( valor,[mascara],[ nls language])

valor : es el valor que usted numerico o date que ustede quiere convertir a caracter.
mascara es opcional : es el formato que usted quiere usar para convertir el valor a caracter.
nls language es opcional : Es el lenguaje usado para realizar la conversión del valor a caracter.


Ejemplos usando campos numericos
to_char(45.31, '99.9') retorna '45.3'
to_char(9,125.33, '9,999.99') retorna '9,125.33'
to_char(77, '0099') retorna '0077'


Las funciones PLSQL de las que hablo son:

FUNCION SQL        ZONA HORARIA      TIPO DE DATO DEVUELTO
CURRENT_DATE       Sesión            DATE
CURRENT_TIMESTAMP  Sesión            TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP     Sesión            TIMESTAMP
SYSDATE            Servidor de BBDD  DATE
SYSTIMESTAMP       Servidor de BBDD  TIMESTAMP WITH TIME ZONE
En el siguiente ejemplo podéis ver los valores devueltos por las funciones SYSDATE y SYSTIMESTAMP.

BEGIN
DBMS_OUTPUT.put_line (SYSDATE);
DBMS_OUTPUT.put_line (SYSTIMESTAMP);
DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00
-000000000 00:00:00.437000000

En el ejemplo podemos observar que al haber utilizado la función PLSQL DBMS_OUTPUT.PUT_LINE para mostrar los valores entregados por las funciones SYSDATE y SYSTIMESTAMP, la base de datos Oracle implícitamente convierte los datos devueltos en una cadena de caracteres, para ello utiliza el formato fecha que por defecto tiene asignado la base de datos o la sesión (esto se especifica al definir el valor del parámetro NLS de base de datos denominado NLS_DATE_FORMAT, otros parámetros relacionados son NLS_TIMESTAMP_FORMAT y NLS_TIME_FORMAT). La instalación por defecto de cualquier base de datos Oracle configura el formato de fecha con el valor DD-MON-YYYY.

También debémos darnos cuenta de que al restar al valor devuelto por la función SYSTIMESTAMP el valor entregado por SYSDATE, el resultado es un intervalo que, aun siendo muy cercano a cero, no es exactamente cero.

Conversión de fechas a carácter

De igual manera que con la función TO_CHAR es posible convertir números a caracteres, otra versión de la misma función nos permite hacer lo mismo con los tipos de dato asociados con fechas y tiempo. De igual manera que con los números, TO_CHAR ofrecen un gran número de posibilidades para formatear fechas y que estas aparezcan tal y como queramos.

Veamos algunos ejemplos:

Si utilizamos TO_CHAR sin ningún tipo de mascara de formato, entonces la cadena de caracteres devuelta por la función será la misma que devuelve la base de datos Oracle cuando realiza una conversión implícita (según vimos que ocurría al emplear la función DBMS_OUTPUT.PUT_LINE).

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE));
  DBMS_OUTPUT.put_line (TO_CHAR (SYSTIMESTAMP));
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00
Si queremos que la función PL/SQL TO_CHAR devuelva el día de la semana y el nombre del mes, entonces utilizaríamos la siguiente máscara:

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE,
             'Day, DD "de" Month "de" YYYY'));
END;
/

Martes   , 21 de Febrero    de 2012
En este sentido debemos saber que el idioma utilizado para mostrar los datos de la fecha viene determinado por el valor del parámetro de la base de datos Oracle NLS_DATE_LANGUAGE, un valor que también puede pasarse como tercer argumento de la función TO_CHAR como podemos ver en el siguiente ejemplo:

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE,
             'Day, DDth Month YYYY',
             'NLS_DATE_LANGUAGE=English'));
END;
/

Tuesday  , 21ST February  2012
En los anteriores ejemplos observaréis que la función TO_CHAR devuelve algunos caracteres extra en blanco, esto ocurre porque por defecto la base de datos Oracle añade dichos caracteres hasta completar la máxima longitud posible del día de la semana o del mes. Esta claro que en la mayoría de los casos querremos evitar que estos caracteres extra aparezcan, para ello disponemos del elemento formateador FM, que si lo añadimos al principio de nuestra máscara eliminará los mencionados espacios.

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE,
             'FMDay, DD "de" Month "de" YYYY'));
END;
/

Martes, 21 de Febrero de 2012
También es posible utilizar determinadas formatos que nos permitirán mostrar informaciones variadas relacionadas con una fecha.

Cuatrimestre:
TO_CHAR (SYSDATE, 'Q') -> 1

Día del año:
TO_CHAR (SYSDATE, 'DDD') -> 052

Diferentes formatos de fecha y hora:
TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') -> 21-02-2012 14:06:30
TO_CHAR (SYSDATE, 'DD-MON-YY HH:MI:SS AM') -> 21-FEB-12 02:06:30 PM

Otra de las funciones PL/SQL que permite manejar fechas es la función EXTRACT, una función que permite extraer los valores de diferentes elementos de un dato de tipo fecha. A continuación os dejo diferentes empleos de la misma.

Año:
EXTRACT (YEAR FROM SYSDATE) -> 2012

Día del mes:
EXTRACT (DAY FROM SYSDATE) -> 21

Por otro lado, para convertir una cadena de caracteres a un dato de tipo fecha se utilizan las funciones TO_DATE y TO_TIMESTAMP. Estas funciones también admiten la utilización de una máscara para indicar el formato de la fecha y para configurar el idioma de la misma.

A continuación os dejo un ejemplo con varios usos de la función TO_DATE.

DECLARE
  l_date DATE;
BEGIN
  l_date := TO_DATE ('21-FEB-2012');
  l_date := TO_DATE ('022112', 'MMDDYY');
  l_date := TO_DATE ('21-Feb-12', 'DD-Mon-YY HH:MI:SS');
  l_date := TO_DATE ('Feb/21/12', 'Mon/DD/YY HH:MI:SS');
  l_date := TO_DATE ('Febrero.21.2012', 'Month.DD.YYYY HH:MI:SS');
END ;
Obviamente si la máscara que pasamos a las funciones TO_DATE o TO_TIMESTAMP no se corresponde con el formato de la fecha que queremos convertir, la base de datos Oracle generará un error.

Por ejemplo, la siguiente consulta SQL devolverá el error "ORA-01843: mes no válido":

SELECT TO_DATE ('21-02-2012', 'DD-MON-YYYY')
FROM dual;
Desde el punto de vista de la programación PL/SQL, conviene tener mucha precaución a la hora de utilizar las funciones TO_DATE o TO_TIMESTAMP, sobre todo si se utilizan sin especificar una máscara concreta (es decir, utilizarán la máscara que emplea la base de datos Oracle por defecto), ya que cambios posteriores del parámetro de base de datos NLS_DATE_FORMAT pueden provocar errores en nuestro programa.