Laboratorio#3- Prueba

1 Ejercicio:

SET ServerOutput ON;
SET VERIFY OFF;
DECLARE
mail varchar2(40):= &email;
puesto number;
nombre varchar2(40);
BEGIN
puesto:=INSTR(mail, '@');
nombre:=SUBSTR(mail,0, puesto -1);
DBMS_OUTPUT.PUT_LINE(nombre);
END;


2 Ejercicio:

1. Creación de la tabla:
create table TOP_DOGS(
NAME VARCHAR2(20),
SALARY NUMBER(8,2),
COMMISION NUMBER(8,2),
REGION VARCHAR2(25)
);

2. Inserción de datos:
DECLARE
CURSOR cursorIns IS
SELECT e.first_name,e.salary,e.commission_pct, r.region_name
from employees e , regions r,departments d, locations l, countries c
WHERE r.region_id=c.region_id and c.country_id=l.country_id and l.location_id=d.location_id and e.department_id=d.department_id
and e.commission_pct is not null;
v_sal EMPLOYEES.salary%TYPE;
v_nom EMPLOYEES.first_name%TYPE;
v_comm EMPLOYEES.commission_pct%TYPE;
v_region REGIONS.region_name%TYPE;
v_num1 number:=&num_registros;
v_num2 number:=1;
BEGIN
delete from top_dogs;
OPEN cursorIns;
LOOP
FETCH cursorIns INTO v_nom,v_sal,v_comm, v_region;
v_num2:=v_num2+1;
INSERT INTO TOP_DOGS(name,salary,commision,region) values(v_nom,v_sal,v_comm, v_region);
EXIT WHEN v_num2>v_num1;
END LOOP;
commit;
CLOSE cursorIns;
END;