Differences

This shows you the differences between two versions of the page.

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

dokuwiki

User Tools

Site Tools

Differences

Page Tools