Temario para evaluación miércoles 10 de abril

• PL básico con SELECT INTO,  declaración de variables en bloque anónimo

 DECLARE -- declare and assign values
bonus_rate CONSTANT NUMBER(2,3) := 0.05;
bonus NUMBER(8,2);
emp_id NUMBER(6) := 120; -- assign a test value for employee ID
BEGIN
-- retreive a salary from the employees table, then calculate the bonus and
-- assign the value to the bonus variable
SELECT salary * bonus_rate
INTO bonus
FROM employees
WHERE employee_id = emp_id;
-- display the employee_id, bonus amount, and bonus rate
DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id)|| ' Bonus: ' || TO_CHAR(bonus) ||
                                                   ' Bonus Rate: ' || TO_CHAR(bonus_rate));
END;

• Joins de 2 o mas tablas en SQL

select  *
from employees E, departments D
where e.department_id=d.department_id
and d.department_id=50;

• Consultas con GROUP BY

select  department_id, count(*) "tot_empleados", sum(salary)  "sum_sala"
from employees

group by department_id
order by department_id; 

• Consultas de GROUP BY con JOIN

 select  department_name, count(*) "tot_empleados", sum(salary)  "sum_sala"
from employees E, departments D
where e.department_id=d.department_id
group by d.department_name
order by d.department_name; 

• PL con va. tipo registro de una sola tabla

DECLARE -- declare variables
-- DEclara una va. tipo registro para vaciar los datos del renglon que traerá en el SELECT INTO de la tabla declare record variable that represents a row fetched from the employees table
emp_rec employees%ROWTYPE; -- declare variable con  %ROWTYPE , que contiene la definicion de todos los campos de la tabla
BEGIN
SELECT * 
INTO emp_rec
FROM EMPLOYEES
WHERE employee_id = 120;  -- REgistro que se trae, del empleado 120
DBMS_OUTPUT.PUT_LINE('Nombre Empleado:' || emp_rec.first_name || ' '
|| emp_rec.last_name); -- DEspliegue a pantalla
DBMS_OUTPUT.PUT_LINE('Salario: '|| emp_rec.salary);
DBMS_OUTPUT.PUT_LINE('Puesto de trabajo: '|| emp_rec.job_id);
END;

• Procedimiento con cursores, con cualquier código SQL visto en el parcial

create or replace procedure  maestro_detalle as
departments departments%rowtype;


begin