游标

游标相当于临时存储一个查询返回的多行数据，通过遍历游标可以逐行访问处理改结果的数据。
声明-打开-读取-关闭 四个步骤

语法

游标的属性

DECLARE 
    --声明游标
    CURSOR c_emp is select id,company from t_company;
    -- 声明接收游标数据的变量
    v_company t_company.company%type;
    v_id t_company.id%type;

BEGIN
    -- 打开游标
    OPEN c_emp;
    -- 遍历游标
    LOOP
        -- 获取游标数据
        FETCH c_emp into v_id, v_company;
        EXIT WHEN c_emp%NOTFOUND;
        dbms_output.put_line(v_company||'-'||v_id);
    END LOOP;

    -- 关闭游标
    CLOSE c_emp;
END;

带参数游标

就是在声明游标的时候做一个参数，然后打开游标时，传入实参。

DECLARE 
    --声明游标
    CURSOR c_emp(v_company_j t_company.company_j%type) is select id,company from t_company where company_j=v_company_j;
    -- 声明接收游标数据的变量
    v_company t_company.company%type;
    v_id t_company.id%type;

BEGIN
    -- 打开游标
    OPEN c_emp('abc');
    -- 遍历游标
    LOOP
        -- 获取游标数据
        FETCH c_emp into v_id, v_company;
        EXIT WHEN c_emp%NOTFOUND;
        dbms_output.put_line(v_company||'-'||v_id);
    END LOOP;

    -- 关闭游标
    CLOSE c_emp;
END;

for 循环写法

DECLARE 
-- 声明一个行级变量
v_emp_record emp%rowtype
--声明游标
cursor c_emp is select empno,ename from emp;
BEGIN
-- for循环让行级变量v_emp_record 在游标中循环
for v_emp_record in c_emp loop
  dbms.output.put_line(v_emp_record.empno||' '||v_emp_record.ename);
end loop;
END;

隐形游标

非查询语句，如更新删除，由Oracle系统自动创建，自动完成 ，存放的数据与用户自定义的显示游标无关的，最新一条sql语句所包含的数据。游标名为sql。
调用方式：sql%游标属性名。

案例1

DECLARE
BEGIN
  update emp set ename='new' where  empno='7788';
  if sql%notfound then
    dbms.output.put_line('没有找到数据')；
elsif sql%found then
     dbms.output.put_line('sql%rowcount')；
  end if;
END;

案例2

删除部门表中名称重复的记录，保留最后一次添加的部门，ID最大。

DECLARE
  -- CURSOR cur_delnames is select  * from dept group by dname;
BEGIN
-- 隐形游标 
delete from dept where deptno not in
(
  select max(deptno) from dept group by dname
);
if sql%found then 
  dbms.output.put_line('删除'|sql%count|'条数据');
commit;
end if;
END;

动态sql

plsql内部正常运行DML(insert,delete,update),DQL(select, into )。
如果去运行DDL(create,truncate,drop)需要动态sql来执行。
动态sql不但可以在plsql中运行DDL，也可以运行DML,DQL
动态sql语法结构
execute immediate 'sql语句' [into 变量][using 绑定参数]

案例1

--动态sql DDL语句
declare
v_sql varchar2(255):='create table a (id number primary key ,name varchar2(30) not null)';
begin
-- 执行创建表
execute immediate v_sql;
--执行删除表中所有数据操作
--execute immediate 'truncate table a';  

end;

案例2

动态sql执行DML语句增删改

BEGIN
execute immediate 'insert into a(id,name) values(1,''张三'')';
-- 张三是两个单引号，其中一个单引号是转义符号。
-- 转义符当字段过多时不好写，可以采用占位参数的方式
execute immediate 'insert into a(id,name) values(:1,:2)' using 2,'李四';
END;

案例3

执行DQL语句

DECLARE
  v_ename emp.ename%type;
  v_deptno emp.deptno%type;
  v_sale emp.sale%type;
BEGIN
execute immediate 'select ename,deptno,sale into v_ename,v_deptno,v_sale  from emp where sal> :sal and deptno= :deptno' using 2000,10 ;
dbms_output.put_line(v_ename||v_sale||v_emp.deptno)
-- 其中：sal 和：deptno都是占位参数，后面接using 
END;

select into 变量不能返回多行，所以当选择多行数据时会报错。

动态游标

显性游标加上动态sql就是动态游标。

DECLARE
-- 声明动态游标类型
type refcur is ref cursor;
-- 定义动态游标变量
v_cur refcur;
v_emp emp%rowtype;

BEGIN
open v_cur for 'select * from emp where sal >:sal'   using   3000;  
loop
fetch v_cur into v_emp;
exit when v_cur%notfound;
dbms_output.put_line(v_emp.empno||','||v_emp.ename);
end loop;
close v_cur;
END;

动态游标无法传入参数。

自定义记录型

--自定义记录型
declare
 type mytype is record(x number(4), y varchar2(10));
 mt mytype;
 cursor mysur is
 select 1, '张三' from dual
 union
 select 2, '李四' from dual
 union
 select 3, '王五' from dual;
begin
 open mysur;
 loop
  fetch mysur into mt;
 
  exit when mysur%notfound;
  dbms_output.put_line(mt.x || ',' || mt.y);
 end loop;
 close mysur;    
end;

image.png

异常处理

用于处理程序中产生错误信息导致整个程序终止的代码，用EXCEPTION来处理，使程序能够继续执行。
declare begin 程序块； exception 错误信息处理； end;

DECLARE
v_x int;
BEGIN
dbms.output.put_line('begin执行开始')；
--这里报错无效数字
SELECT empno from emp into v_x where empno=1000;
dbms.output.put_line('begin执行结束')；
-- 异常处理部分
EXCEPTION
  when no_data_found then 
    dbms.output.put_line('没有这个ID')；
--假如还有其他错误，也可以把上面的注释掉，直接使用others 代表所有错误。
WHEN OTHERS THEN
  dbms.output.put_line('其他错误')；
END;

上一篇：控制流语句
下一篇：存储过程