dokuwiki

create table emp as 
select employee_id, first_name, last_name, salary, 0 bonus from employees

  CREATE GLOBAL TEMPORARY TABLE "HR"."EMP_TEMP" 
   (	"EMPLOYEE_ID" NUMBER(6,0), 
	"FIRST_NAME" VARCHAR2(20), 
	"LAST_NAME" VARCHAR2(25), 
	"SALARY" NUMBER(8,2), 
	"BONUS" NUMBER
   ) ON COMMIT DELETE ROWS 
 

  CREATE GLOBAL TEMPORARY TABLE "HR"."EMP_TEMP2" 
   (	"EMPLOYEE_ID" NUMBER(6,0), 
	"FIRST_NAME" VARCHAR2(20), 
	"LAST_NAME" VARCHAR2(25), 
	"SALARY" NUMBER(8,2), 
	"BONUS" NUMBER
   ) ON COMMIT preserve ROWS 
 

PROCEDURE CURSOR_PARAM
   ( p_min_salary in number)
   IS
   
   cursor c_min_salary(c_p_min_salary number) is
   select first_name, last_name, salary from emp where 
   salary > c_p_min_salary;

   cursor c_min_salary2(c_p_min_salary number) is
   select * from emp where 
   salary > c_p_min_salary;

   
BEGIN
   delete from emp_temp2;
   for rec in c_min_salary(p_min_salary) loop
     dbms_output.put_line(rec.last_name);
     insert into emp_temp2 (      
       FIRST_NAME,
       LAST_NAME,
       SALARY     
         ) values (
       rec.FIRST_NAME,
       rec.LAST_NAME,
       rec.SALARY
       );
   end loop;

   delete from emp_temp;
   for rec in c_min_salary2(p_min_salary) loop
   insert into emp_temp values rec;
   end loop;

END; -- Procedure