/*---------游标-------*/
/*
游标是一个机制,通过这个机制可以给一个SQL语句命名,并操作该
SQL返回的数据或者使用该SQL返回的数据操作其他的数据
*/
/*
显式游标:显式声明游标,且由代码管理游标生命周期
隐式游标:非显式声明游标,由系统管理游标生命周期
及有无open,close。
*/
--loop..end loop;
declare
cursor c_departments(p_department_id number) is
select dp.department_id, dp.department_name
from departments dp
where dp.department_id = p_department_id;
c_dept_rec c_departments%rowtype;
v_dept_id number := 2001;
begin
open c_departments(v_dept_id);
loop
fetch c_departments
into c_dept_rec;
exit when c_departments%notfound;
dbms_output.put_line('部门ID:' || c_dept_rec.department_id || ',部门名称:' ||
c_dept_rec.department_name);
end loop;
close c_departments;
end;
--while..loop..end loop;
declare
cursor c_departments(p_department_id number) is
select dp.department_id, dp.department_name
from departments dp
where dp.department_id = p_department_id;
c_dept_rec c_departments%rowtype;
v_department_id number := 2001;
begin
open c_departments(v_department_id);
fetch c_departments --不用带参数
into c_dept_rec;
while c_departments%found loop
dbms_output.put_line('部门ID:' || c_dept_rec.department_id || ',部门名称:' ||
c_dept_rec.department_name);
fetch c_departments
into c_dept_rec;
end loop;
close c_departments;
end;
--for..in..loop..end loop;
declare
cursor c_departments(p_dept_id number) is
select dp.department_id, dp.department_name
from departments dp
where dp.department_id = p_dept_id;
--不用声明 c_dept_rec c_departments%rowtype;
v_dept_id number := 2001;
begin
--不用open,close
for c_dept_rec in c_departments(v_dept_id)/*此处要带参数*/ loop
dbms_output.put_line('部门ID:' || c_dept_rec.department_id || ',部门名称:' ||
c_dept_rec.department_name);
end loop;
end;
--隐式游标
/*
从数据库中获取数据到变量时,如果相关SQL只返回低于1条记录,
使用显式游标过于麻烦,Oracle提供SELECT INTO快速实现改功能。
所有DML语句执行时创建一个隐式游标,所以SELECT INTO也是游标,
不过其创建和打开、关闭全部由ORACLE自动完成
*/
--select into
declare
v_emp_num employees.employee_number%type;
v_emp_name employees.employee_name%type;
begin
select em.employee_number,em.employee_name
into v_emp_num,v_emp_name
from employees em
where em.employee_name='John smith';
dbms_output.put_line('员工号码:'||v_emp_num||',员工姓名:'||v_emp_name);
exception
when no_data_found then
dbms_output.put_line('无此员工,请检查');
end;