《PLSQL系列之二:游标、集合和对象(幻灯说明稿).doc》由会员分享,可在线阅读,更多相关《PLSQL系列之二:游标、集合和对象(幻灯说明稿).doc(19页珍藏版)》请在金锄头文库上搜索。
1、PL/SQL系列培训之二:游标、集合和对象游标CURSOR当ORACLE处理每一个SQL语句的时候,它会开辟一块内存空间来存储SQL执行计划、绑定变量等上下文信息。而游标就是指向这块内存区域的指针。PL/SQL块通过游标来访问或操作这块内存区域。CURSOR的两个重要特性:(1)如果是SELECT SQL,可以通过CURSOR来一条一条地获取到查询到的行(2)可以通过对CURSOR命名来持有并操作它。CURSOR分类:(1)隐式游标:只要有SQL执行,ORACLE都会自动声明一个隐式游标,这个过程对用户来说是不可见的。(2)显式游标:显式地在PL/SQL块中为查询SQL定义游标。Implici
2、t Cursor所有DML语句,包括UPDATE,DELETE,INSERT都有一个隐式游标自动关联。UPDATE/DELETE的CURSOR明确了SQL将要修改的行INSERT的CURSOR存放了将要存入的数据可以通过SQL%来访问最近打开的CURSORBEGIN UPDATE tanjie21 SET name = BB; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);END;这样来理解隐式游标,在一个会话里将维持一个隐式游标,在执行新的SQL语句时,ORACLE自动进行REOPEN操作。两个使用隐式游标的场景:(1)SELECT INTO variable_nam
3、e(2)FOR rec IN (SELECT id, name FROM tanjie21) LOOP . END LOOP;Explicit Cursor注意显式游标只能关联到SELECT SQL显式游标使用步骤:(1)定义一个游标 - 只是定义一个游标关联到某个SELECT SQL(2)打开这个游标 - 检查WHERE条件里的变量 - 基于变量的值,PL/SQL引擎执行查询操作并得到结果集 - 将游标指针指向结果集的第一行记录(3)从游标中取数并操作 - 取数的两种方式:FETCH cursor_name INTO variables;FETCH cursor_name INTO reco
4、rd (呆会集合会讲)- 注意:(1)一次只能取一条记录 (2)每次FETCH之后,游标指针会自动指向下一条记录。(4)关闭游标- 注意最后一定要关闭游标,结合前面的GOTO、EXIT、RETURN、RAISE进行强调- 游标关闭之后,将不能再对其进行FETCH和CLOSE操作。典型格式:DECLARE v_id NVARCHAR2(10); CURSOR cur_test IS SELECT id FROM tanjie21;BEGIN OPEN cur_test; LOOP FETCH cur_test INTO v_id; EXIT WHEN cur_test%NOTFOUND; DBM
5、S_OUTPUT.PUT_LINE(v_id); END LOOP; CLOSE cur_test;EXCEPTION WHEN OTHERS THEN IF cur_test%ISOPEN THEN CLOSE cur_test; END IF;END;CURSORs ATTRIBUTES%NOTFOUND:如果之前的FETCH没有返回一行,返回TRUE,否则返回FALSE%FOUND:如果之前的FETCH返回了一行,返回TRUE,否则返回FALSE%ROWCOUNT:返回当前游标已经FETCH的行数%ISOPEN:如果游标OPEN,返回TRUE,否则FALSECURSOR FOR LOOP
6、使用Cursor For,就不用再显式的进行游标的OPEN,FETCH和CLOSE操作DECLARE CURSOR v_cur IS SELECT id, name FROM tanjie21;BEGIN FOR my_rec IN v_cur LOOP DBMS_OUTPUT.put_line(my_rec.id | . | my_rec.name); END LOOP;END;Cursor With Parameters带参数的游标(这个参数的作用域就在游标内部)。好处:(1)简化程序(2)重用(3)可以带默认值定义:CURSOR v_cur(parameters) IS select_s
7、ql;使用:OPEN v_cur(parameter_value);orFOR rec IN v_cur(parameter_value);举例:DECLARE CURSOR v_cur(p_id tanjie21.id%TYPE) IS SELECT name FROM tanjie21 where id = p_id;BEGIN FOR my_rec IN v_cur(1) LOOP DBMS_OUTPUT.put_line(my_rec.name); END LOOP;END;DECLARE v_name NVARCHAR2(10); CURSOR v_cur(p_id NVARCHAR
8、2) IS SELECT name FROM tanjie21 where id = p_id;BEGIN OPEN v_cur(1); LOOP FETCH v_cur INTO v_name; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.put_line(v_name); END LOOP; CLOSE v_cur;END;CURSOR VARIABLES也称REF CURSOR。游标变量是针对游标的一个引用或指针。游标是静态的,它必定与某一个SQL相关联;而游标变量是动态的,它在运行时与不同的SQL进行动态绑定。可以使用游标变量来在SP等情况下进行查询结果
9、集的传递。两种游标变量:(1)强类型(将游标变量进行限定只能处理某种特定类型的结果集)(2)弱类型(适应任何类型,较灵活)要定义游标变量,要先定义一个REF CURSOR的TYPE,然后再定义一个这种TYPE的变量,即游标变量游标变量使用注意事项:(1)同样要注意最终关闭游标!(2)游标变量如果在程序中重新OPEN,则在REOPEN之前可以不CLOSE游标变量,(游标同样如此),不过如果一旦重新OPEN,之前的查询将失效。游标变量举例:DECLARE TYPE type_ref_cursor IS REF CURSOR; v_id tanjie21.id%TYPE; v_name tanjie
10、21.name%TYPE; v_cursor type_ref_cursor;BEGIN OPEN v_cursor FOR SELECT id, name FROM tanjie21; LOOP FETCH v_cursor INTO v_id, v_name; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.put_line(v_id | . | v_name); END LOOP;END;上面这个例子是弱类型的游标变量,如果是强类型的游标变量,就是在REF CURSOR TYPE定义时加上RETURN 类型限制,如下:TYPE type_ref_curs
11、or IS REF CURSOR RETURN tanjie21%ROWTYPE;隐式游标、显式游标、游标变量隐式游标与显式游标相比,代码简单,也不需要手动关闭显式游标与游标变量相比,效率更高建议尽量使用隐式游标,其次使用显式游标,然后再是游标变量 集合RECORD1、什么是record类似与C语言中的结构体,可以理解为一个一行多列的数据表,也可以理解成一个具有多个属性的一维变量。如:图书馆( 地址、规模、藏书、信息化程度、服务质量、馆长、员工人数 . )2、%RowType 与record通常使用Record,需要首先定义RECORD类型,如:declaretype def_inv_mast
12、er is record ( item_code inv_master.item_code%type, item_name nvarchar2(30);var_inv_master_1 def_inv_master;var_inv_master_2 def_inv_master;.但是如果RECORD实际上就是数据表的整行记录,可以使用%ROWTYPE来简化语句。如:declarevar_inv_master inv_master%Rowtype.beginSelect item_code into var_inv_master.item_code from inv_master.item_c
13、ode.如果取得的是数据表的整个行的记录,可以使用%ROWTYPE3、record的实例化field_name field_type NOT NULL := | DEFAULT expression 如:var_inv_master_1 def_inv_master;var_inv_master_2 def_inv_master:=item001,物料001;var_inv_master_3 def_inv_master;.4、给record赋值 .Select item_code into var_inv_master_1.item_code from inv_master where condition1Select item_code into var_inv_master_2.item_code from inv_master where condition2Select item_code, item_name (bulk) into var_inv_master_3 from inv_master where condition3var_inv_master_4 := var_inv_m