blogspot.com-GA4

星期一, 3月 07, 2011

oracle For each row in a cursor

在oracle function中,
select 出的資料可以放到 cursor中在進行處理,
下面是一個簡單的範例,可以參考一下


SQL> CREATE TABLE emp(
2 emp_ID NUMBER (6),
3 START_DATE DATE,
4 END_DATE DATE,
5 JOB_ID VARCHAR2 (10),
6 DEPARTMENT_ID NUMBER (4)
7 );

Table created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE promotion_review_2
2 IS
3 CURSOR empCursor IS SELECT emp_id, start_date, end_date, job_id FROM emp;
4 BEGIN
5 FOR jh_rec IN empCursor
6 LOOP
7 DBMS_OUTPUT.put_line(jh_rec.emp_id||' had job '||jh_rec.job_id||' for '|| (jh_rec.end_date - jh_rec.start_date)||' days.');
8 END LOOP;
9 END;
10 /

Procedure created.

SQL> drop table emp;

Table dropped.

沒有留言: