Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T2»
De wikiserver
Línia 137: | Línia 137: | ||
[[Imatge:M2UF3_SOL_CFUNC_6.png |400px|center| Solucions Creació de funcions]] | [[Imatge:M2UF3_SOL_CFUNC_6.png |400px|center| Solucions Creació de funcions]] | ||
+ | |||
+ | :c) Llame a ADD_EMPLOYEE para el nombre 'Jane Harris' del departamento 15, dejando otros parámetros con los valores por defecto. ¿Cuál es el resultado? | ||
+ | |||
+ | :'''Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_c.sql.''' | ||
+ | :'''Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento. El código y el resultado se muestran de la siguiente forma: | ||
+ | <pre> | ||
+ | EXECUTE add_employee('Jane', 'Harris', 'JAHARRIS', p_deptid=> 15) | ||
+ | </pre> | ||
+ | |||
+ | [[Imatge:M2UF3_SOL_CFUNC_7.png |400px|center| Solucions Creació de funcions]] | ||
+ | |||
+ | :d) Agregue otro empleado llamado Joe Harris en el departamento 80, dejando los parámetros restantes con sus valores por defecto. ¿Cuál es el resultado? | ||
+ | |||
+ | :'''Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_d.sql.''' | ||
+ | :'''Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento. El código y el resultado se muestran de la siguiente forma:''' | ||
+ | <pre> | ||
+ | EXECUTE add_employee('Joe', 'Harris', 'JAHARRIS',p_deptid=> 80) | ||
+ | </pre> | ||
+ | |||
+ | [[Imatge:M2UF3_SOL_CFUNC_8.png |400px|center| Solucions Creació de funcions]] |
Revisió del 21:03, 23 abr 2016
En esta práctica, creará, compilará y utilizará las funciones almacenadas y un procedimiento.
1) Cree y llame a la función GET_JOB para devolver un cargo.
- a) Cree y compile la función denominada GET_JOB para devolver un cargo.
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_01_a.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar la función. El código y el resultado se muestran de la siguiente forma:
CREATE OR REPLACE FUNCTION get_job (p_jobid IN jobs.job_id%type) RETURN jobs.job_title%type IS v_title jobs.job_title%type; BEGIN SELECT job_title INTO v_title FROM jobs WHERE job_id = p_jobid; RETURN v_title; END get_job; /
- b) Cree una variable de host VARCHAR2 denominada b_title, que permita una longitud de 35 caracteres. Llame a la función con identificador de trabajo SA_REP para que devuelva el valor de la variable del host y, a continuación, imprima la variable de host para ver el resultado.
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_01_b.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar la función. El código y el resultado se muestran de la siguiente forma:
VARIABLE b_title VARCHAR2(35) EXECUTE :b_title := get_job ('SA_REP'); PRINT b_title
2) Cree una función denominada GET_ANNUAL_COMP para devolver el salario anual de un empleado calculado a partir del salario mensual y la comisión transferidos como parámetros.
- a) Cree la función GET_ANNUAL_COMP, que acepta valores de parámetros del salario mensual y la comisión. Uno o ambos valores transferidos pueden ser NULL, pero la función deberá devolver un salario anual no NULL. Utilice la siguiente fórmula básica para calcular el salario anual:
(salary*12) + (commission_pct*salary*12)
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_02_a.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar la función. El código y el resultado se muestran de la siguiente forma:
CREATE OR REPLACE FUNCTION get_annual_comp( p_sal IN employees.salary%TYPE, p_comm IN employees.commission_pct%TYPE) RETURN NUMBER IS BEGIN RETURN (NVL(p_sal,0) * 12 + (NVL(p_comm,0) * nvl(p_sal,0)* 12)); END get_annual_comp; /
b) Utilice la función en una sentencia SELECT en la tabla EMPLOYEES para los empleados del departamento 30.
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_02_b.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar la función. El código y el resultado se muestran de la siguiente forma:
SELECT employee_id, last_name, get_annual_comp(salary,commission_pct) "Annual Compensation" FROM employees WHERE department_id=30 /
3) Cree un procedimiento, ADD_EMPLOYEE, para insertar un nuevo empleado en la tabla EMPLOYEES. El procedimiento llamará a una función VALID_DEPTID para comprobar si el identificador (ID) de departamento especificado para el nuevo empleado existe en la tabla DEPARTMENTS.
- a) Cree una función denominada VALID_DEPTID para validar el identificador de departamento especificado y devolver un valor BOOLEAN TRUE si existe el departamento.
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_a.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear la función. El código y el resultado se muestran de la siguiente forma:
CREATE OR REPLACE FUNCTION valid_deptid( p_deptid IN departments.department_id%TYPE) RETURN BOOLEAN IS v_dummy PLS_INTEGER; BEGIN SELECT 1 INTO v_dummy FROM departments WHERE department_id = p_deptid; RETURN TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE; END valid_deptid; /
- b) Cree el procedimiento ADD_EMPLOYEE para agregar un empleado a la tabla EMPLOYEES. La fila se debe agregar a la tabla EMPLOYEES si la función VALID_DEPTID devuelve TRUE; de lo contrario, alertará al usuario con un mensaje adecuado. Proporcione los siguientes parámetros:
- - first_name
- - last_name
- - job: utilice 'SA_REP' como valor por defecto.
- - mgr: utilice 145 como valor por defecto.
- - sal: utilice 1000 como valor por defecto.
- - comm: utilice 0 como valor por defecto.
- - deptid: utilice 30 como valor por defecto.
- - Utilice la secuencia EMPLOYEES_SEQ para definir la columna employee_id.
- - Defina la columna hire_date en TRUNC(SYSDATE).
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_b.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar el procedimiento. El código y el resultado se muestran de la siguiente forma:
CREATE OR REPLACE PROCEDURE add_employee( p_first_name employees.first_name%TYPE, p_last_name employees.last_name%TYPE, p_email employees.email%TYPE, p_job employees.job_id%TYPE DEFAULT 'SA_REP', p_mgr employees.manager_id%TYPE DEFAULT 145, p_sal employees.salary%TYPE DEFAULT 1000, p_comm employees.commission_pct%TYPE DEFAULT 0, p_deptid employees.department_id%TYPE DEFAULT 30) IS BEGIN IF valid_deptid(p_deptid) THEN INSERT INTO employees(employee_id, first_name,last_name, email, job_id, manager_id, hire_date, salary, commission_pct, department_id) VALUES (employees_seq.NEXTVAL, p_first_name,p_last_name, p_email, p_job, p_mgr, TRUNC(SYSDATE), p_sal, p_comm, p_deptid); ELSE RAISE_APPLICATION_ERROR (-20204, 'Invalid department ID. Try again.'); END IF; END add_employee; /
- c) Llame a ADD_EMPLOYEE para el nombre 'Jane Harris' del departamento 15, dejando otros parámetros con los valores por defecto. ¿Cuál es el resultado?
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_c.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento. El código y el resultado se muestran de la siguiente forma:
EXECUTE add_employee('Jane', 'Harris', 'JAHARRIS', p_deptid=> 15)
- d) Agregue otro empleado llamado Joe Harris en el departamento 80, dejando los parámetros restantes con sus valores por defecto. ¿Cuál es el resultado?
- Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_d.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento. El código y el resultado se muestran de la siguiente forma:
EXECUTE add_employee('Joe', 'Harris', 'JAHARRIS',p_deptid=> 80)