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_emp
is
  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_rowtype
as
v_manager employees%ROWTYPE;
cursor cursor_manager is
select * 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_1row
as
type 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_1row
as
type 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_2
as
cursor cursor_emp is
select * 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_nrows
as
cursor cursor_emp is
select * 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%';