Oracle存储过程学习_游标CURSOR使用

游标CURSOR的使用学习游标的类型：1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标，名字固定叫sql。

2，显式游标：显式游标用于处理返回多行的查询。

3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果一、隐式游标在PL/SQL中使用DML语句时自动创建隐式游标 q隐式游标自动声明、打开和关闭，其名为 SQL q通过检查隐式游标的属性可以获得最近执行的DML 语句的信息q隐式游标的属性有：q%FOUND – SQL 语句影响了一行或多行时为 TRUEq%NOTFOUND – SQL 语句没有影响任何行时为TRUEq%ROWCOUNT – SQL 语句影响的行数q%ISOPEN - 游标是否打开，始终为FALSEbeginupdate student s set s.sage = s.sage + 10;if sql %FOUND thendbms_output.put_line('这次更新了' || sql% rowcount);elsedbms_output.put_line('一行也没有更新');end if;end;在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS declaresname1 student.sname%TYPE;beginselect sname into sname1 from student;if sql%found thendbms_output.put_line(sql%rowcount);elsedbms_output.put_line('没有找到数据');end if;exceptionwhen too_many_rows thendbms_output.put_line('查找的行记录多于1行');when no_data_found thendbms_output.put_line('未找到匹配的行');end;显式游标：sqlserver与oracle的不同之处在于：最后sqlserver会deallocate 丢弃游标，而oracle只有前面四步：声明游标、打开游标、使用游标读取记录、关闭游标。

显式游标的使用：----------------------------无参数游标-------------------------------declaresname varchar2(20); --声明变量cursor student_cursor isselect sname from student; --声明游标beginopen student_cursor; --打开游标fetch student_cursorinto sname; --让游标指针往下移动while student_cursor%found--判断游标指针是否指向某行记录loop--遍历dbms_output.put_line('学生姓名' || sname);fetch student_cursorinto sname;end loop;close student_cursor;end;-----------------------------有参数游标-------------------------------declaresname student.sname%type;sno student.sno%type;cursor student_cursor(input_sno number) isselect s.sname, s.sno from student s where s.sno > input_sno; --声明带参数的游标beginsno := &请输入学号; --要求从客户端输入参数值，"&"相当于占位符；open student_cursor(sno); --打开游标，并且传递参数fetch student_cursorinto sname, sno; --移动游标while student_cursor% found loopdbms_output.put_line('学号为：' || sno || '姓名为：' || sname);fetch student_cursorinto sname, sno;end loop;close student_cursor;end;-----------------------------循环游标------------------------------- -- Created on 18-1月-15 by 永文declarestu1 student%rowtype; --这里也不需要定义变量来接收fetch到的值cursor student_cursor isselect * from student;beginopen student_cursor; --这里不需要开启游标for stu1 in student_cursor loopdbms_output.put_line('学生学号：' || stu1.sno || '学生姓名：' ||stu1.sname);fetch student_cursorinto stu1; --也不需要fetch了end loop;close student_cursor; --这里也不需要关闭游标end;--------------------------使用游标更新行------------------------------- declarestu1 student%rowtype;cursor student_cursor isselect * from student s where s.sno in (2, 3) for update; --创建更新游标beginopen student_cursor;fetch student_cursorinto stu1; --移动游标while student_cursor%found--遍历游标，判断是否指向某个值loopupdate student set sage = sage + 10where current of student_cursor; --通过游标中的信息更新数据fetch student_cursorinto stu1; --移动游标end loop;close student_cursor;end;declarestu1 student%rowtype;cursor student_cursor isselect * from student s where s.sno in (2, 3) for update; --创建更新游标beginopen student_cursor;-- fetch student_cursor into stu1;--移动游标-- while student_cursor%found--遍历游标，判断是否指向某个值loopfetch student_cursorinto stu1; --移动游标exit when student_cursor %notfound;update student set sage = sage + 10where current of student_cursor; --通过游标中的信息更新数据end loop;close student_cursor;end;--------------使用fetch ... bulk collect into-------------------------- declarecursor my_cursor isselect ename from emp where deptno = 10; --声明游标type ename_table_type is table of varchar2(10); --定义一种表类型，表中的属性列为varchar2类型ename_table ename_table_type; --通过上面定义的类型来定义变量beginopen my_cursor; --打开游标fetch my_cursor bulk collectinto ename_table; --移动游标for i in1 .. ename_table.count loopdbms_output.put_line(ename_table(i));end loop;close my_cursor;end;e---------------------显示游标题目--------------------------------------SQL >select * from student;XH XM---------- ----------1 A2 B3 C4 DSQL >select * from address;XH ZZ---------- ----------2郑州1开封3洛阳4新乡完成的任务 :给表student添加一列zz, 是varchar2(10) 类型；再从address中，将zz字段的数值取出来，对应的插入到 student新增的zz列中。

即：得到的结果：student表中，是： XH XM ZZ-- ---------- ------1 A 开封2 B 郑州3 C 洛阳4 D 新乡declare stu1 student %rowtype;add1 address %rowtype;cursor student_cursor isselect * from student for update; --声明更新游标cursor address_cursor isselect * from address; --声明游标beginopen student_cursor; --打开游标fetch student_cursorinto stu1; --移动游标while student_cursor% found--判断游标是否指向某条记录loopopen address_cursor; --打开另外一个游标fetch address_cursorinto add1; --移动游标while address_cursor %found--判断游标是否指向某条记录loopif add1.xh = stu1.xh then--判断两个游标所指向的记录中xh的值是否相等update student sset s.zz = add1.zzwhere current of student_cursor; --假如相等就更新游标所指向的记录值end if;fetch address_cursorinto add1; --移动游标end loop;close address_cursor; --关闭游标fetch student_cursorinto stu1; --移动游标end loop;close student_cursor; --关闭游标end;REF游标也叫动态游标：qREF 游标和游标变量用于处理运行时动态执行的 SQL 查询 q创建游标变量需要两个步骤： q声明 REF 游标类型 q声明 REF 游标类型的变量 q用于声明REF 游标类型的语法为：TYPE <ref_cursor_name> IS REF CURSOR[RETURN <return_type>]; ?1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 -----------------------------------ref 游标---------------------------------declaretype ref_cursor is ref cursor; --声明一个ref 游标类型tab_cursor ref_cursor ;--声明一个ref 游标sname student.xm %type ;sno student.xh %type ;tab_name varchar2 (20 );begintab_name := '&tab_name'; --接收客户输入的表明if tab_name = 'student' thenopen tab_cursor for select xh ,xm from student ; --打开ref 游标 fetch tab_cursor into sno ,sname ;--移动游标while tab_cursor %foundloopdbms_output.put_line ('学号：' ||sno ||'姓名：' ||sname ); fetch tab_cursor into sno ,sname ;end loop;close tab_cursor ;elsedbms_output.put_line ('没有找到你想要找的表数据信息' ); end if ;end;-----------------------------------ref 游标题目---------------------------------SQL > select * from student ;XH KC---------- ----------1 语文1 数学1 英语1 历史2 语文2 数学2 英语3 语文3 英语9 rows selectedSQL >43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 完成的任务 :生成student2表 (xh number, kc varchar2 (50 ))；对应于每一个学生，求出他的总的选课记录，把每个学生的选课记录插入到student2表中。