游标、动态sql、异常

游标分，静态游标和动态游标

静态游标：在编译的时候就知道其select语句的游标

动态游标：运行的时候动态决定执行何种查询

游标的使用步骤

 游标的属性

--计算个税 
declare

--声明游标
 cursor cursor_teacher is
 select tname,sal from teacher ;
 v_teacher_record cursor%rowtype;
 v_tax number:=0;
 v_tax_sum number:=0;
 v_sal number;
begin
 open cursor_teacher;
     loop 
     fetch cursor_teacher into v_teacher_record;
     exit when cursor_teacher%notfound;
     --开始计算个税  
     v_tax:=0;
     v_sal:=v_teacher_record.sal;
     if v_sal>=5000 and v_sal<10000 then
         v_tax:=(v_sal-5000)*0.05;
         elsif v_sal>=10000 and v_sal<40000 then
         v_tax:=(v_sal-10000)*0.1+5000*0.05;
         elsif v_sal>=40000 then
         v_tax:=(v_sal-40000)*0.3+30000*0.1+5000*0.05;
     end if;    
     v_sum:=v_tax_sum+v_tax;
     dbms_output.put_line(v_teacher_record.tname||'的个税：'||v_tax);
     end loop;
 dbms_output.put_line('个税总额：'||v_tax_sum);
 close cursor_teacher;
 
 
 v_tax_sum:=0;
 for v_teacher_record in cursor_teacher loop
   v_tax:=0;--清0
     v_sal=v_teacher_record.sal;
     if v_sal>=5000 and v_sal<10000 then
         v_tax:=(v_sal-5000)*0.05;
         elsif v_sal>=10000 and v_sal<40000 then
         v_tax:=(v_sal-10000)*0.1+5000*0.05;
         elsif v_sal>=40000 then
         v_tax:=(v_sal-40000)*0.3+30000*0.1+5000*0.05;
     end if;    
     v_tax_sum:=v_tax_sum+v_tax;
 end loop;
end;

以上的税率也可以设置为常量来做

c_tax1 constant number:=0.1;

动态sql实现由2种实现方法：

方法一是10g才有的

方法2是9i的，现在比较少用

execute immediate

declare 
  v_sql  varchar2(2000);
  v_dept  number(2);
  v_name  teacher.tname%type;
  v_gender teacher.gender%type:='女';
  
begin
  v_sql:='update teacher set sal=sal+100 where 1=1';
  if v_dept is not null then
    v_sql:=v_sql||' and deptno='||v_dept; 
  end if;
  if v_name is not null then
     v_sql:=v_sql||' and tname='''||v_name||''''; 
　　--2个引号代表一个单引号，因为是字符串，所以得用引号引起来
  end if;
  if v_gendar is not null then
     v_sql:=v_sql||' and gender='''||v_gender||''''; 
  end if;
  
  --execute immediate v_sql; 
  
  commit;
dbms_output.put_line(v_tname||v_gender);
  
end;

 动态游标

方式1、

--动态游标
declare 
type type_teacher_cursor is ref cursor;
cursor_teacher type_teacher_cursor;
v_name teacher.tname%type;
begin
 open cursor_teacher for 'select tname from teacher where deptno=:x'
 using 30;
 loop
 fetch cursor_teacher into v_name;
 exit when cursor_teacher%notfound;
 dbms_output.put_line(v_name);
 end loop;
 close cursor_teacher;
end;

注意 OPEN cursor_teacher后面没有分号，关闭有

方式2、

using用逗号隔开，各个参数

方式3、（9i使用，现在不要求掌握）

declare 
  v_sql  varchar2(2000);
  v_gender teacher.gendar%type:='女';
  v_cursor number;--定义一个光标
  v_rows number;
begin
  v_sql:='update teacher set sal=sal+100 where gendar=:p_gender';
  --打开光标
  v_cursor:=dbms_sql.open_cursor;
  --解释语句
  dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
  --绑定参数
  dbms_sql.bind_variable(v_cursor,':p_gender',v_gender);
  --execute是一个函数，有返回值，没有接收就会报错
  --执行语句
  v_rows:=dbms_sql.execute(v_cursor);
  --关闭光标
  dbms_sql.close_cursor(v_cursor);
  commit;
end;

异常处理

 

declare 
  v_sql  varchar2(2000);
  v_dept  number(2);
  v_name  teacher.tname%type;
  v_gender teacher.gender%type:='女';
  v_cursor number;
  v_rows number;
  e_outof_money exception;--声明异常     本题改为2000
begin
  v_sql:='update teacher set sal=sal+100 where gender=:p_gender';
 
   v_cursor:=dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);--分析语句
  dbms_sql.bind_variable(v_cursor,':p_gender',v_gender);
  v_rows:=dbms_sql.execute(v_cursor);--执行
   if v_rows>20 then 
       raise e_outof_money;--抛出异常，跳到下面的exception
  end if;
  dbms_sql.close_cursor(v_cursor);
  commit;
exception 
   when e_outof_money THEN
     dbms_output.put_line('超出加薪预算');  
     rollback;
   WHEN OTHERS thhen
     dbms_output.put_line('内部错误');
end;

1.有3个组成

a、声明部分。声明变量。声明光标，声明异常b、执行部分begin。c、异常部分exception

3、解决了查询的结果集问题

4、以上3种