- 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;
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
