%ROWTYPE

Esta función tienen como objetivo principal tomar el tipo de dato de una fila completa o record.

Ejemplo:

DECLARE

   V_EMP   EMPLOYEES%ROWTYPE;

CURSOR C_EMP IS
SELECT *
  FROM EMPLOYEES
 WHERE EMPLOYEE_ID = 100;

BEGIN
 
  OPEN C_EMP;
  FETCH C_EMP
  INTO V_EMP;
  CLOSE C_EMP;

DBMS_OUTPUT.PUT_LINE('Empleado: '|| V_EMP);

END;
 
Como se puede apreciar, el cursor insertara en esta variable tres tipos de datos distintos sin problema alguno.

Es bueno saber que solo se puede hacer este script siempre y cuando se seleccionen todas las columnas, si solo se quieren menos tendríamos que recurrir a una variable TYPE RECORD.

Read More

%TYPE

%TYPE es una función de PL/SQL que adopta el tipo de dato de una columna.

Su objetivo principal es tomar el tipo de datos de una columna y si en algun momento determinado este tipo de datos es cambiado automáticamente la variable tomara el nuevo tipo de datos asignado.

Podemas tenerla en funciones, procedimientos, paquetes y triggers. Nos facilita considerablemente el mantenimiento.

Ejemplo:

DECLARE

        V_EMP    EMPLOYEES.LAST_NAME%TYPE;

BEGIN

       V_EMP := 'JOSE';

END;

Read More

SQL%FOUND

Atributo booleano que se evalúa como TRUE si la última sentencia SQL afectó al menos una fila.

Puede probar el SQL%ROWCOUNT , SQL%FOUND , y atributos de SQL%NOTFOUND en la sección ejecutable de un bloque para recoger información después se ejecuta el comando apropiados DML. PL / SQL no devuelve un error si una sentencia DML no afecta a las filas de la tabla subyacente. Sin embargo, si una sentencia SELECT no recupera ninguna fila, PL / SQL devuelve una excepción.

Observe que los atributos tienen el prefijo SQL. Estos atributos del cursor se utilizan con los cursores implícitos que son creados automáticamente por PL / SQL y para los que no saben los nombres. Por lo tanto, se utiliza SQL en lugar del nombre del cursor.

El atributo SQL%NOTFOUND es lo contrario de SQL%NOTFOUND . Este atributo puede ser utilizado como la condición de salida en un bucle. Es útil en UPDATE y DELETE cuando se cambian ninguna fila porque excepciones no se devuelven en estos casos.
Usted aprenderá acerca de cursor explícito atributos en la lección titulada "Uso de los cursores explícitos".

Ejemplo:

DECLARE

CURSOR C_EMP IS
SELECT LAST_NAME
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = 100;

V_EMP VARCHAR2(30);

BEGIN

OPEN C_EMP ;
FECTH C_EMP
INTO V_EMP;

IF C_EMP%FOUND THEN

--ALGO

END IF;

CLOSE C_EMP;


END;

Read More

SQL%NOTFOUND

Atributo booleano que se evalúa como TRUE si La última sentencia SQL no afectó incluso una fila.

Puede probar el SQL%ROWCOUNT , SQL%FOUND , y atributos de SQL%NOTFOUND en la sección ejecutable de un bloque para recoger información después se ejecuta el comando apropiados DML. PL / SQL no devuelve un error si una sentencia DML no afecta a las filas de la tabla subyacente. Sin embargo, si una sentencia SELECT no recupera ninguna fila, PL / SQL devuelve una excepción.

Observe que los atributos tienen el prefijo SQL. Estos atributos del cursor se utilizan con los cursores implícitos que son creados automáticamente por PL / SQL y para los que no saben los nombres. Por lo tanto, se utiliza SQL en lugar del nombre del cursor.

El atributo SQL%NOTFOUND es lo contrario de SQL%NOTFOUND . Este atributo puede ser utilizado como la condición de salida en un bucle. Es útil en UPDATE y DELETE cuando se cambian ninguna fila porque excepciones no se devuelven en estos casos.
Usted aprenderá acerca de cursor explícito atributos en la lección titulada "Uso de los cursores explícitos".

Ejemplo:

DECLARE

CURSOR C_EMP IS
SELECT LAST_NAME
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = 100;

V_EMP VARCHAR2(30);

BEGIN

OPEN C_EMP ;
FECTH C_EMP
INTO V_EMP;

IF C_EMP%NOTFOUND THEN

--ALGO

END IF;

CLOSE C_EMP;


END;

Read More

SQL%ROWCOUNT

Un valor entero que representa el número de filas afectadas por la última sentencia SQL.

Puede probar el SQL%ROWCOUNT , SQL%FOUND , y atributos de SQL%NOTFOUND en la sección ejecutable de un bloque para recoger información después se ejecuta el comando apropiados DML. PL / SQL no devuelve un error si una sentencia DML no afecta a las filas de la tabla subyacente. Sin embargo, si una sentencia SELECT no recupera ninguna fila, PL / SQL devuelve una excepción.

Observe que los atributos tienen el prefijo SQL. Estos atributos del cursor se utilizan con los cursores implícitos que son creados automáticamente por PL / SQL y para los que no saben los nombres. Por lo tanto, se utiliza SQL en lugar del nombre del cursor.

El atributo SQL%NOTFOUND es lo contrario de SQL%NOTFOUND . Este atributo puede ser utilizado como la condición de salida en un bucle. Es útil en UPDATE y DELETE cuando se cambian ninguna fila porque excepciones no se devuelven en estos casos.
Usted aprenderá acerca de cursor explícito atributos en la lección titulada "Uso de los cursores explícitos".

DECLARE
  v_rows_deleted VARCHAR2(30)
  v_empno employees.employee_id%TYPE := 176;
BEGIN

  DELETE FROM  employees
  WHERE employee_id = v_empno;
  v_rows_deleted := (SQL%ROWCOUNT ||
                       ' row deleted.');
  DBMS_OUTPUT.PUT_LINE (v_rows_deleted);

END;

El ejemplo elimina una fila con employee_id 176 de la tabla de empleados. Usando el atributo% ROWCOUNT SQL, puede imprimir el número de registros borrados.

Read More

CURSORES

Un cursor es un puntero a la zona de memoria privada asignada por el servidor Oracle. Se utiliza para manejar el conjunto de resultados de una sentencia SELECT.

Hay dos tipos de cursores: implícitos y explícitos.

1. Implícito: Creado y gestionado internamente por el servidor Oracle para procesar sentencias SQL
2. Explícita: Declarado explícitamente por el programador

Usted ya ha aprendido que puede incluir sentencias SQL que devuelven una sola fila en un bloque PL/SQL. Los datos recuperados por la sentencia SQL deberían celebrarse en las variables mediante la cláusula INTO.

De dónde viene el proceso de declaraciones de SQL Server Oracle?

El servidor de Oracle asigna un área de memoria privada llamada el área de contexto para el procesamiento de sentencias SQL. La sentencia SQL se analiza y procesa en esta área. La información necesaria para el procesamiento y la información recuperada después de su transformación se almacenan en esta área. Usted no tiene control sobre esta zona, ya que se maneja internamente por el servidor Oracle.

Un cursor es un puntero a la zona de contexto. Sin embargo, este cursor es un cursor implícito y es gestionado automáticamente por el servidor Oracle. Cuando el bloque ejecutable emite una sentencia SQL, PL / SQL crea un cursor implícito.

Tipos de cursores

Hay dos tipos de cursores:

Implícita: se crea y gestionado por el servidor de Oracle Un cursor implícito. Usted no tiene acceso a ella. El servidor de Oracle crea un cursor como cuando tiene que ejecutar una sentencia SQL.

Explícita: Como programador, es posible que desee recuperar varias filas de una tabla de base de datos, tiene un puntero a cada fila que se recupera, y el trabajo en las filas de una en una. En tales casos, se puede declarar cursores explícitamente en función de sus necesidades de negocio. Un cursor declarado por los programadores se llama cursor explícito. Usted declara un cursor tal en la parte declarativa de un bloque PL / SQL.

1. En la parte declarativa de un bloque PL/SQL, declare el cursor por nombrarlo y la definición de la estructura de la consulta que se asocia con él.
2. Abra el cursor.  La sentencia OPEN ejecuta la consulta y se une cualquier variable que se hace referencia. Filas identificados por la consulta se llaman el conjunto activo y ahora están disponibles para ir a buscar.
3. Obtener los datos desde el cursor.  En el diagrama de flujo mostrado en la diapositiva, después de cada zona de alcance, se prueba el cursor para cualquier fila existente. Si no hay más filas para procesar, debe cerrar el cursor.
4. Cierre el cursor.  La sentencia CLOSE libera el conjunto activo de filas. Ahora es posible reabrir el cursor para establecer un conjunto activo fresco.

Un programa PL/SQL abre un cursor, procesa filas devueltas por una consulta, y luego cierra el cursor. El cursor marca la posición actual en el conjunto activo.

1. La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el conjunto activo, y posiciona el cursor en la primera fila.

2. La sentencia FETCH recupera la fila actual y avanza el cursor a la siguiente Remar hasta que no haya más filas o se cumple una condición especificada.

3. La sentencia CLOSE libera el cursor.

La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el conjunto activo, y posiciona el puntero del cursor en la primera fila. La instrucción OPEN está incluido en la sección ejecutable del bloque PL / SQL.

OPEN es una sentencia ejecutable que realiza las siguientes operaciones:

1. asigna dinámicamente la memoria para un área de contexto

2. analiza la instrucción SELECT

3. Se liga las variables de entrada (establece los valores de las variables de entrada mediante la obtención de sus direcciones de memoria)

4. Identifica el conjunto activo (el conjunto de filas que satisfacen los criterios de búsqueda). Las filas en el conjunto activo no se recuperan en variables cuando se ejecute la sentencia OPEN. Más bien, la sentencia FETCH recupera las filas desde el cursor a las variables.

5. Coloca el puntero a la primera fila en el conjunto activo

Nota: Si una consulta no devuelve ninguna fila cuando se abre el cursor, PL / SQL no plantea una excepción. Usted puede encontrar el número de filas devueltas con un cursor explícitos mediante el uso de la <cursor_name>% ROWCOUNT atributo.

La sentencia FETCH recupera las filas de la cursor uno a la vez. Después de cada zona de alcance, el cursor avanza a la siguiente fila en el conjunto activo. Usted puede utilizar el atributo% NOTFOUND para determinar si todo el conjunto activo se ha recuperado.

Considere el ejemplo mostrado en la diapositiva. Dos variables, empno y lname, se declaran para contener los valores han obtenido desde el cursor. Examine la sentencia FETCH.

Haber recogido con éxito los valores desde el cursor a las variables. Sin embargo, hay seis empleados del departamento 30, pero sólo una fila era descabellada. Para recuperar todas las filas, debe utilizar bucles. En la siguiente diapositiva, se ve cómo se utiliza un bucle para buscar a todas las filas.

La sentencia FETCH realiza las siguientes operaciones:

1. Lee los datos de la fila actual en la salida de las variables PL / SQL

2. Avanza el puntero a la siguiente fila en el conjunto activo

Puede incluir el mismo número de variables en la cláusula INTO de la sentencia FETCH ya que hay columnas en la sentencia SELECT; asegúrese de que los tipos de datos son compatibles. Relacionar cada variable para corresponder a las columnas posicionalmente. Alternativamente, también puede definir un récord para el cursor y hacer referencia al registro en el FETCH cláusula INTO. Por último, la prueba para ver si el cursor contiene filas. Si una zona de alcance adquiere ningún valor, no hay filas de izquierda a procesar en el conjunto activo y se registra ningún error.

Read More

DML EN PL-SQL

Usted manipular datos en la base de datos mediante el uso de comandos DML. Puede emitir comandos DML tales como INSERT, UPDATE, DELETE y MERGE sin restricciones en PL / SQL. Los bloqueos de fila (y bloqueos de tabla) se liberan mediante la inclusión de las declaraciones de confirmar o deshacer en el código PL / SQL.

• La sentencia INSERT añade nuevas filas a la tabla.
• La sentencia UPDATE modifica filas existentes en la tabla.
• La instrucción DELETE elimina filas de la tabla.
• La instrucción MERGE selecciona filas de una tabla a actualizar o insertar en otra mesa. La decisión de actualizar o insertar en la tabla de destino se basa en una condición en la cláusula ON.

Nota: MERGE es una afirmación determinista. Es decir, no se puede actualizar la misma fila de la tabla de destino varias veces en la misma sentencia MERGE. Debe tener INSERT y objetos UPDATE privilegios en la tabla de destino y SELECT privilegio en la tabla fuente.

Ejemplos:

DECLARE

V_CODIGO NUMBER := 1;
V_NOMBRE VARCHAR2(30) := 'JOSE';

BEGIN

INSERT INTO TABLA(C_CODIGO,C_NOMBRE)
VALUES (V_CODIGO,V_NOMBRE);

V_NOMBRE := 'JUAN';

UPDATE TABLA NOMBRE
SET C_NOMBRE = V_NOMBRE

WHERE C_CODIGO = V_CODIGO;

DELETE TABLA
WHERE C_CODIGO = V_CODIGO;

COMMIT;

END;

La instrucción MERGE inserciones o actualizaciones filas de una tabla utilizando los datos de otra tabla. Cada fila se inserta o actualiza en la tabla de destino en función de una condición de unión igualitaria.
El ejemplo que se muestra coincide con la columna empno en la tabla copy_emp a la columna de employee_id en la tabla empleados. Si se encuentra una coincidencia, la fila se actualiza para que coincida con la línea de la tabla empleados. Si no se encuentra la fila, se inserta en la tabla copy_emp.
El ejemplo completo de usar MERGE en un bloque PL / SQL se muestra en la página siguiente.

BEGIN

MERGE INTO copy_emp c

     USING employees e

     ON (e.employee_id = c.empno)

   WHEN MATCHED THEN

     UPDATE SET

       c.first_name     = e.first_name,

       c.last_name      = e.last_name,

       c.email          = e.email,

       c.phone_number   = e.phone_number,

       c.hire_date      = e.hire_date,

       c.job_id         = e.job_id,

       c.salary         = e.salary,

       c.commission_pct = e.commission_pct,

       c.manager_id     = e.manager_id,

       c.department_id  = e.department_id

   WHEN NOT MATCHED THEN

     INSERT VALUES(e.employee_id,  e.first_name, e.last_name,

          e.email, e.phone_number, e.hire_date, e.job_id,

          e.salary, e.commission_pct, e.manager_id, 

          e.department_id);

END;

/

Read More

SERVEROTUPUT

El SERVEROTUPUT es el paquete que nos permite utilizar funciones propias de ORACLE, como puede ser la salida de mensajes como mostrare a continuacion.

set SERVEROUTPUT ON

DECLARE

 V_NOMBRE VARCHAR2(30) := 'JOSE JIMENEZ';

BEGIN

--DBMS_OUTPUT = PAQUETE
--PUT_LINE = FUNCION

DBMS_OUTPUT.PUT_LINE(V_NOMBRE );

END;

Resultado: JOSE JIMENEZ

Read More