Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T7»

De wikiserver
Dreceres ràpides: navegació, cerca
(→‎T7- Tipus de dades compostes)
(→‎T7- Tipus de dades compostes)
Línia 1: Línia 1:
−
==T7- Tipus de dades compostes ==
+
==Solución 7-1: Uso de Cursores Explícitos==
  
−
Crea una nova taula per emmagatzemar salaris i sous.
+
En esta lección, realizará dos ejercicios:
 +
:*Primero, utilizará un cursor explícito para procesar un número de filas de una tabla y rellenar otra tabla con los resultados mediante un bucle FOR de cursor.
 +
:*Después, escribirá un bloque PL/SQL que procese la información con dos cursores, incluido uno que utilice un parámetro.
  
 +
1) Cree un bloque PL/SQL que realice las siguientes acciones:
 +
 +
:a) En la sección de declaraciones, declare e inicialice una variable llamada v_deptno de tipo NUMBER. Asigne un valor de identificador de departamento válido (consulte los valores en la tabla del paso d).
 +
<pre>
 +
    DECLARE
 +
    v_deptno NUMBER := 10;
 +
</pre>
 +
:b) Declare un cursor llamado c_emp_cursor, que recupere last_name, salary y manager_id de los empleados que trabajan en el departamento especificado en v_deptno.
 
<pre>
 
<pre>
−
SQL> CREATE TABLE top_dogs
+
    CURSOR c_emp_cursor IS
−
  2  (name    VARCHAR2(25),
+
      SELECT
−
  3  salary NUMBER(11,2));
+
      last_name, salary,manager_id
 +
      FROM employees
 +
      WHERE department_id = v_deptno;
 
</pre>
 
</pre>
−
 
+
:c) En la sección ejecutable, utilice el bucle FOR de cursor para realizar operaciones en los datos recuperados. Si el salario del empleado es menor que 5.000 y si el identificador de superior es 101 o 124, aparece el mensaje “<<last_name>> Due for a raise”. De lo contrario, aparece el mensaje “<<last_name>> Not Due for a raise”.
−
*'''Exercici 1'''
 
−
 
 
−
Escriu un bloc PL/SQL que inicialitzi les 3 primeres posicions d'una taula de registres (nom, salari) i a continuació actualitzi la taula top_dogs amb aquesta informació.
 
−
 
 
−
Creem el tipus registre:
 
−
 
 
−
  TYPE emp_record_type IS RECORD
 
−
    (name    VARCHAR2(25),
 
−
      salary  NUMBER(11,2));
 
−
 
 
−
Creem el tipus taula de registres:
 
−
 
−
  TYPE emp_table_type IS TABLE OF emp_record_type;
 
−
 
 
−
*Alternativa: TYPE emp_table_type IS TABLE OF top_dogs%TYPE (amb aquesta opció no caldria haver declarat el tipus registre)
 
−
 
 
−
Creem la variable taula de registres:
 
−
 
 
−
  emp_table emp_table_type;
 
−
 
 
−
Inicialitzem la taula:
 
−
 
 
−
  emp_table(1).name := 'Pere';
 
−
  emp_table(1).salary := 3000;
 
−
  emp_table(2).name := 'Joan';
 
−
  emp_table(2).salary := 4000;
 
−
  emp_table(3).name := 'Raquel';
 
−
  emp_table(3).salary := 3500;
 
−
 
 
−
Inserim els valors a la taula TOP_DOGS:
 
−
 
 
−
  FOR i IN 1..3 LOOP
 
−
    INSERT top_dogs
 
−
    VALUES (emp_table(1).name, emp_talbe(1).salary);
 
−
  END LOOP;
 
−
 
 
−
Programa sencer:
 
 
<pre>
 
<pre>
−
DECLARE
+
    BEGIN
−
 
+
      FOR emp_record IN c_emp_cursor
−
  TYPE emp_record_type IS RECORD
+
      LOOP
−
    (name    VARCHAR2(25),
+
        IF emp_record.salary < 5000 AND (emp_record.manager_id=101OR emp_record.manager_id=124) THEN
−
      salary NUMBER(11,2));
+
          DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise');
−
  TYPE emp_table_type IS TABLE OF emp_record_type
+
        ELSE
−
      INDEX BY BINARY_INTEGER;
+
          DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Not Due for a raise');
−
 
+
        END IF;
−
  emp_table emp_table_type;
+
      END LOOP;
−
BEGIN
+
    END;
−
 
 
−
  emp_table(1).name := 'Pere';
 
−
  emp_table(1).salary := 3000;
 
−
  emp_table(2).name := 'Joan';
 
−
  emp_table(2).salary := 4000;
 
−
  emp_table(3).name := 'Raquel';
 
−
  emp_table(3).salary := 3500;
 
−
 
 
−
  FOR i IN 1..3 LOOP
 
−
    INSERT top_dogs
 
−
    VALUES (emp_table(i).name, emp_table(i).salary);
 
−
  END LOOP;
 
−
 
 
−
END;
 
−
 
 
 
</pre>
 
</pre>
−
 
−
*'''Exercici 2'''
 
−
 
−
Escriu un bloc PL/SQL per recuperar el nom i el sou d'un empleat concret de la taula EMP a partir de la introducció del seu número d'empleat.
 
−
 
−
a. Declara dues taules: rev_salary_table_type per emmagatzemar el percentatge de augment de salari. i emp_table_type per guardar l'empleat tractat.
 
−
 
−
b. Inicialitza les posicions de la taula rev_salary_table, corresponents als números de departament de la taula dept, amb els diferents percentatges:
 
−
 
−
      10 -->  2; 20 --> 3; 30 --> 4; 40 --> 3 ...
 
−
 
−
c. Guarda el nom i salari en la taula emp_table en la posició corresponent al departament de l'empleat.
 
−
 
−
d. Finalment guarda en la taula top_dogs el nom de l'empleat i el seu salari revisat en funció del departament al que pertany.
 
−
 
−
 
−
::SET VERIFY OFF
 
−
 
−
::ACCEPT p_empno PROMPT 'Número de empleado:'
 
−
 
−
::DECLARE
 
−
 
 
−
:::TYPE emp_record IS RECORD
 
−
:::(
 
−
:::ename emp.ename%TYPE ,
 
−
:::sal emp.sal%TYPE
 
−
:::);
 
−
 
−
:::TYPE emp_table_type IS TABLE OF emp_record
 
−
::::INDEX BY BINARY_INTEGER;
 
−
 
 
−
:::TYPE rev_salary_table_type IS TABLE OF NUMBER(2)
 
−
::::INDEX BY BINARY_INTEGER;
 
−
 
−
:::emp_table emp_table_type;
 
−
 
−
:::rev_salary_table rev_salary_talbe_type;
 
−
   
 
−
:::v_empno emp.empno%TYPE := &p_empno;
 
−
 
−
:::v_deptno BINARY_INTEGER;
 
−
 
 
−
:::v_emp emp_record;
 
−
 
−
:::v_salary_rev emp.sal%TYPE;
 
−
 
−
::BEGIN
 
−
 
−
:::rev_salary_table(10) := 2;
 
−
 
−
:::rev_salary_table(20) := 3;
 
−
 
−
:::rev_salary_table(30) := 4;
 
−
 
−
:::rev_salary_table(40) := 3;
 
−
 
−
:::SELECT ename, sal, deptno
 
−
 
 
−
:::INTO v_emp.ename, v_emp.sal, v_deptno
 
−
 
−
:::FROM emp
 
−
 
 
−
:::WHERE empno = v_empno;
 
−
 
−
:::emp_table(v_deptno) := v_emp;
 
−
 
−
:::v_salary_rev := emp_table(v_deptno).sal * (1 + rev_salary_table(v_deptno)/100)
 
−
 
−
:::INSERT INTO top_dogs (name,salary)
 
−
 
−
:::VALUES (emp_table(v_deptno).ename, v_salary_rev);
 
−
 
 
−
:::COMMIT;
 
−
 
−
::END;
 
−
 
−
::/
 
−
 
−
::SET VERIFY ON
 

Revisió del 16:56, 25 abr 2016

Solución 7-1: Uso de Cursores Explícitos

En esta lección, realizará dos ejercicios:

  • Primero, utilizará un cursor explícito para procesar un número de filas de una tabla y rellenar otra tabla con los resultados mediante un bucle FOR de cursor.
  • Después, escribirá un bloque PL/SQL que procese la información con dos cursores, incluido uno que utilice un parámetro.

1) Cree un bloque PL/SQL que realice las siguientes acciones:

a) En la sección de declaraciones, declare e inicialice una variable llamada v_deptno de tipo NUMBER. Asigne un valor de identificador de departamento válido (consulte los valores en la tabla del paso d).
    DECLARE
    v_deptno NUMBER := 10;
b) Declare un cursor llamado c_emp_cursor, que recupere last_name, salary y manager_id de los empleados que trabajan en el departamento especificado en v_deptno.
    CURSOR c_emp_cursor IS
      SELECT
      last_name, salary,manager_id
      FROM employees
      WHERE department_id = v_deptno;
c) En la sección ejecutable, utilice el bucle FOR de cursor para realizar operaciones en los datos recuperados. Si el salario del empleado es menor que 5.000 y si el identificador de superior es 101 o 124, aparece el mensaje “<<last_name>> Due for a raise”. De lo contrario, aparece el mensaje “<<last_name>> Not Due for a raise”.
    BEGIN
      FOR emp_record IN c_emp_cursor
      LOOP
        IF emp_record.salary < 5000 AND (emp_record.manager_id=101OR emp_record.manager_id=124) THEN
          DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise');
        ELSE
           DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Not Due for a raise');
        END IF;
      END LOOP;
    END;
Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF3:_Llenguatges_SQL_/_Solucions_PL-SQL_T7&oldid=7044»