This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:cursor_temporaere_tabllen [2009/10/15 16:14] mh created |
kurs:cursor_temporaere_tabllen [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | create table emp as | ||
+ | select employee_id, first_name, last_name, salary, 0 bonus from employees | ||
+ | </code> | ||
+ | <code> | ||
+ | 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 | ||
+ | |||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | 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 | ||
+ | |||
+ | </code> | ||
+ | |||
+ | |||
+ | <code> | ||
+ | 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 | ||
+ | </code> |