Oracle cursor

1,Procedure中通过记录类型来进行查询.

(1),当游标查询由多个表联合查询得出一个混合列的结果时,使用定义记录类型.

例子p_no_pam

create or replace procedure p_no_pam
AS

//定义一个记录复合类型,来存储游标产生的数据;因为游标查询不是单独的一个表,所以v_emp table_name%ROWTYPE无法使用.

TYPE v_emp_type is record(v_department_id number(6),v_department_name varchar2(40),v_counts number(10)             );v_emp v_emp_type;CURSOR cursor_empis  select t1.department_id,d1.department_name,t1.counts  from  (select e.department_id,count(*) counts from employees e left join departments d on e.department_id = d.department_id group by e.department_id) t1  left join departments d1 on t1.department_id = d1.department_id;begin  open cursor_emp;  loop    fetch cursor_emp into v_emp;    exit when cursor_emp%NOTFOUND;    dbms_output.put_line(v_emp.v_department_id||','||v_emp.v_department_name||','||v_emp.v_counts);  end loop;end;

2,Procedur中通过%ROWTYPE进行查询(实质上是一种简化的记录类型)
(1),当游标仅在一个表查询结果时,可以使用%ROWTYPE进行定义变量;
(2),但游标查询只能使用"*"来进行查询,否则fetchcursor_manager into v_manager会报错.
例子p_rowtype

create or replace procedure p_rowtypeasv_manager employees%ROWTYPE;cursor cursor_manager isselect * from employees e1 where e1.employee_id in(select manager_id from employees e2);//注意为*进行查询begin  open cursor_manager;  loop    fetch cursor_manager into v_manager;    exit when cursor_manager%notfound;    dbms_output.put_line(v_manager.FIRST_name||','||v_manager.department_id);  end loop;end p_rowtype;

3,当查询结果为1行记录时,可以不使用游标.

(1),直接使用select into进行赋值;

(2),可以对单个变量进行赋值.

(3),也可进行一行值进行赋值,与%ROWTYPE结合使用.

 

4,索引表复合数据类型
(1),需定义;

type 索引表类型名称 is table of a,定义基本类型的单列;b.或者直接使用table_name%ROWTYPE定义表的多列
type index_type is table of employees.first_name%TYPE index by binary_integer;
type index_type is table of employees%ROWTYPE index by binary_integer;

(2),类似数组,可以使用下标;

 

例子1-输出为单个列查询,使用select 列名 into进行赋值

create or replace procedure p_index_type_1rowastype index_type is table of employees.first_name%TYPE index by binary_integer;v_index_type index_type;begin  select first_name into v_index_type(1) from employees where first_name = 'Jerron';  dbms_output.put_line(v_index_type(1));end;

例子2-输出为单行查询,直接使用select * into进行赋值.

create or replace procedure p_index_type_1rowastype index_type is table of employees%ROWTYPE index by binary_integer;v_index_type index_type;begin  select * into v_index_type(1) from employees where first_name = 'Jerron';  dbms_output.put_line(v_index_type(1).first_name||','||v_index_type(1).last_name||','||v_index_type(1).department_id);end;

例子3-多行输出,游标一次提取1条记录(其实这里可以使用v_cusor_emp employees%ROWTPE的记录类型实现)

create or replace procedure p_index_type_nrows_2ascursor cursor_emp isselect * from employees order by employee_id;type index_type is table of employees%ROWTYPE index by binary_integer;v_cursor_emp index_type;cnt integer := 0;begin  open cursor_emp;  loop       fetch cursor_emp into v_cursor_emp(1);       exit when cursor_emp%NOTFOUND;       cnt := cnt + 1;       dbms_output.put_line(cnt||','||v_cursor_emp(1).first_name                                ||','||v_cursor_emp(1).last_name                                ||','||v_cursor_emp(1).department_id                                );                                 end loop;  close cursor_emp;end;

例子4-多行输出,游标一次提取3条记录

create or replace procedure p_index_type_nrowsascursor cursor_emp isselect * from employees order by employee_id;type index_type is table of employees%ROWTYPE index by binary_integer;v_cursor_emp index_type;begin  open cursor_emp;  loop       fetch cursor_emp bulk collect into v_cursor_emp limit 3;       for i in 1..v_cursor_emp.count loop           dbms_output.put_line(v_cursor_emp(i).first_name                                ||','||v_cursor_emp(i).last_name                                ||','||v_cursor_emp(i).department_id                                );                  end loop;       exit when cursor_emp%NOTFOUND;  end loop;  close cursor_emp;end;

在sqlplus中查询过程的定义

select name,type,text from user_source where name like '%P_NO_PAM%';