plsql游标for循环_oracle游标的使用详解

Oracle之plsql及游标 　　–1、赋值 –:= 赋值 declare var_name varchar2(10) :=’&请输入名字’;–&是一个提示输入的特殊符号，会打开一个输入框 var_age number(3) :=’&请输入年龄’; begin dbms_output.put_line(var_name||’—‘||var_age);–输入 ||是连接符号和java中的+一样 end; –into 赋值 declare var_name varchar2(10); var_age number(3); begin select stuname,age into var_name,var_age from t_student where id = 2; dbms_output.put_line(var_name||’—‘||var_age); end; –2、 特殊类型： –%type 绑定某个表中的特定字段的类型 declare v_name emp.ename%type; v_job emp.job%type; begin select ename,job into v_name,v_job from emp where emp.empno=3; dbms_output.put_line(v_name||’—‘||v_job); end; –%rowtype:行类型和表中的一行对应 declare v_emp emp%rowtype; — v_emp的类型是一个行类型 和emp的一条记录对应 begin select * into v_emp from emp where empno=1; dbms_output.put_line(v_emp.ename||’ ‘||v_emp.sal||’ ‘||v_emp.job); end; –3、if条件判断 if语句 语法格式: if 条件 then [ elsif 条件 then ] [ elsif 条件 then] [else ] end if; –实例 declare v_age number(3) :=&请输入年龄; begin if v_age = 18 then dbms_output.put_line(’18’); elsif v_age > 18 then dbms_output.put_line(‘大于18’); else dbms_output.put_line(‘小于18′); end if; end; –4、case case when 条件 then when 条件 then else end case; –实例 declare v_age number(3) :=&请输入年龄; begin case when v_age = 18 then dbms_output.put_line(’18’);–条件可以是一个定值也可以是>或者< when v_age = 19 then dbms_output.put_line(’19’); when v_age = 20 then dbms_output.put_line(’20’); when v_age = 21 then dbms_output.put_line(’21’); when v_age = 22 then dbms_output.put_line(’22’); when v_age > 23 then dbms_output.put_line(‘大于23’); else dbms_output.put_line(‘不知道’); end case; end; –5、循环 –5、1无限循环 loop — 循环体 exit when 退出条件; end loop; –实例 declare v_num number(3) := 1; begin loop dbms_output.put_line(v_num); v_num := v_num+1; exit when v_num > 10; end loop; end; –5、2 while带条件的循环 while 循环条件 loop –循环体 end loop; –实例 declare v_i number(5) := 1; begin while v_i<10 loop dbms_output.put_line(v_i); v_i := v_i+1; end loop; end; –5/3 for循环: –1.不用专门去声明循环变量 –2.每次只能自增一， –3.要想实现循环降序需要在 in 后加 reverse –实例 declare begin for v_i in reverse 1..9 loop dbms_output.put_line(v_i); end loop; end; –6、goto关键字：跳转到指定的位置 –实例： declare v_num number(5) := &请输入; begin if v_num = 18 then dbms_output.put_line(18); goto a1; elsif v_num = 20 then dbms_output.put_line(20); goto a2; else dbms_output.put_line(‘———-‘); goto a3; end if; <<a1>> dbms_output.put_line(‘a1======’); <<a2>> dbms_output.put_line(‘a2======’); <<a3>> dbms_output.put_line(‘a3======’); end; –7、动态SQL语句：解决的是字符串格式的sql语句执行的问题 EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list]; declare v_stuname t_student.stuname%type; v_sql varchar2(100) := ‘select stuname from t_student where id = :id’; v_id number(3) := &请输入查询的id; begin execute immediate v_sql into v_stuname using v_id; dbms_output.put_line(v_stuname); end; –8、异常处理 –8、1 系统异常 no_data_found;没有找到数据的异常 too_many_rows: 多行数据 others 其他异常 declare v_stuname t_student.stuname%type; v_id t_student.id%type; begin — 业务逻辑代码 v_id:=100; select stuname into v_stuname from t_student where id = v_id; dbms_output.put_line(v_stuname); — 异常处理代码 exception when no_data_found then dbms_output.put_line(‘找不到数据’); when too_many_rows then dbms_output.put_line(‘数据过多’); when others then dbms_output.put_line(‘其他异常’); end; –8、2 自定义异常 declare v_i number(3) :=&请输入; myexception exception; begin if v_i = 2 then raise myexception; end if; dbms_output.put_line(‘==================’); exception when myexception then dbms_output.put_line(‘自定义异常触发了’); when others then dbms_output.put_line(‘不知道的异常’); end; 游标： 一.隐式游标：系统自动维护的，在我们做DML操作的时候系统会自动的维护这样一个游标 名称叫:sql 隐式游标提供的常用的属性： sql%found: boolean 找到数据 true sql%notfound: boolean 没有找到数据 true sql%rowcount: 数值型 影响的行数 sql%isopen: 是否打开 dml中都是 false 插入：select * from student; begin update t_student set stuname=’xxxx’ where id = 10; if sql%found then dbms_output.put_line(‘影响了’|| sql%rowcount ||’行记录’); end if; if sql%isopen then dbms_output.put_line(‘——–1——–‘); end if; if sql%notfound then dbms_output.put_line(‘———–2———–‘); end if; end; 二.显示游标:处理多行数据，隐式游标配合显示游标使用 2.1无参游标 查询出学生表中的所有的记录： 使用的步骤： 1.声明游标 2.打开游标 3.循环提取数据 4.关闭游标 declare v_student t_student%rowtype; –1.声明游标 cursor mycursor is select * from t_student; v_count number(3):=0; begin –2.打开游标 open mycursor; –3.循环提取数据 loop –提取数据 — 每循环一次从游标中取一条记录保存到v_student变量中 fetch mycursor into v_student; –指定退出条件 exit when mycursor%notfound; v_count := v_count+1; dbms_output.put_line(v_student.id||v_student.stuname||v_student.sex); end loop; dbms_output.put_line(‘有’||v_count||’记录’); –4.关闭游标 close mycursor; end; declare v_student t_student%rowtype; cursor mycursor is select * from student for update; — 1. for update begin open mycursor; loop fetch mycursor into v_student; exit when mycursor%notfound; dbms_output.put_line(v_student.id||v_student.name||v_student.sex||v_student.birth); if v_student.birth is null then — 2.在更新语句后加 current of 游标名称 update t_student set stuname=’xxxx’ where current of mycursor; end if; end loop; commit; close mycursor; end; 2.2 有参游标 根据姓名查询学生表中的所有的学生信息 declare v_student t_student%rowtype; v_name t_student.stuname%type:=’&请输入姓名’; cursor mycursor(c_name varchar2) is select * from t_student where stuname like ‘%’||c_name||’%’; begin open mycursor(v_name); loop fetch mycursor into v_student; if mycursor%found then dbms_output.put_line(v_student.id||’–‘||v_student.stuname||’—‘||v_student.age); else exit; end if; end loop; close mycursor; end; declare v_student t_student%rowtype; v_name t_student.stuname%type := ‘&请输入要查询的姓名’; cursor mycursor — 带有参数 is select * from t_student where stuname like ‘%’||v_name||’%’; begin open mycursor; –打开的时候需要指定参数 loop fetch mycursor into v_student; if mycursor%found then — 有数据 dbms_output.put_line(v_student.id||v_student.stuname||v_student.sex); else — 退出 exit; end if; end loop; close mycursor; end; 2.3 游标循环时使用for循环提取 declare v_name t_student.stuname%type := ‘&请输入’; cursor mycursor is select * from t_student where stuname like ‘%’||v_name||’%’; begin for v_student in mycursor loop update t_student set age=23; dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex); end loop; commit; end; 3.REF游标【动态游标】：是解决游标动态执行sql 显示游标在声明的时候就必须制定sql语句 动态游标：在打开的时候确定要执行的sql语句比显示游标更加的灵活 缺点：不能使用for循环和更新行 3.1 自定义ref游标 通过REF游标查询出学生表中的所有的学生记录 declare type myreftype is ref cursor;– 1.定义一个ref 类型 myrefcursor myreftype;– 2.声明一个myreftype类型的变量 v_student t_student%rowtype; v_sql varchar2(100); begin v_sql:=’select * from t_student’; — for 后及可以带” 也可以直接是sql语句 –open myrefcursor for select * from student; — 打开游标的同时指定要执行的sql语句 open myrefcursor for v_sql; loop fetch myrefcursor into v_student; exit when myrefcursor%notfound; dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex); end loop; close myrefcursor; end; 3.2 sys_refcursor:系统提供的一个 ref cursor 类型 declare myrefcursor sys_refcursor; — 声明一个变量类型是 refcursor 类型 v_student t_student%rowtype; v_sql varchar2(100); begin v_sql :=’select * from t_student’; open myrefcursor for v_sql; loop fetch myrefcursor into v_student; exit when myrefcursor%notfound; dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex); end loop; close myrefcursor; end;