qqip.探测网站建设,营销型网站关键词多少为好,手机模板网站模板下载网站,网站开发提案游标#xff08;cursor #xff09;是一个私有的SQL工作区域,是一个指向上下文区的句柄或指针#xff0c;位于内存中的 临时表。 游标是SQL的一个内存工作区#xff0c;由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某… 游标cursor 是一个私有的SQL工作区域,是一个指向上下文区的句柄或指针位于内存中的 临时表。 游标是SQL的一个内存工作区由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下需要把数据从存放在磁盘的表中调到计算机内存中进行处理最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高否则频繁的磁盘数据交换会降低效率 游标用来管理从数据源返回的数据的属性结果集。这些属性包括并发管理、在结果集中的位置、返回的行数以及是否能够在结果集中向前和/或向后移动可滚动性。 游标跟踪结果集中的位置并允许对结果集逐行执行多个操作在这个过程中可能返回至原始表也可能不返回至原始表 原理游标是从数据表中提取出来的数据以 临时表 的形式存放到 内存中在游标中有一个 数据指针 在初始状态下指向的是首记录利用 fetch 语句可以移动该指针从而对游标中的数据进行各种操作然后将操作结果写回到数据库中。作用 1、用来查询数据库获取记录集合结果集的指针可以让开发者基于当前的结果集位置检索一行或连续的几行在每 条结果集上作操作以编 程的方式访问数据。 2、用 ‘牺牲内存’ 来提升 SQL 执行效率适用于 大数据处理。 3.在结果集的当前位置修改行中的数据对其他用户所做的数据更改定义不同的敏感性级别。 程序语言是面向记录的一组变量一次只能存放一个变量或者一条记录无法直接接收数据库中的查询结果集引入游标就解决了这个问题 游 标的类型 REF 游标用于处理运行时才能确定的动态 SQL 查询的结果 隐式游标不易被用户和程序员察觉和意识到实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL 语句而显式游标是程序员在程序中显式声明的通常我们说的游标均指显式游标 隐式 游标 在 PL/SQL 中使用 DML 和 select into时会自动创建隐式游标隐式游标自动声明、打开和关闭无法手动查看其名为 SQL通过检查隐式游标的属性可以获得 最近执行的 DML 和 select into 语句的信息 DML操作和单行SELECT语句会使用隐式游标它们是 * 插入操作INSERT。 * 更新操作UPDATE。 * 删除操作DELETE。 * 单行查询操作SELECT ... INTO ...。 当系统使用一个隐式游标时可以通过隐式游标的属性来了解操作的状态和结果进而控制程序的流程。隐式游标可以使用名字SQL来访问但要注意通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后立即使用SQL游标名来访问属性 %FOUND语句影响了一行或多行时为 TRUE前一个 fetch 语句是否有值true有false没有%NOTFOUND 语句没有影响任何行时为TRUE与上述相反常被用于 退出循环true有false没有, null : 空。注意哦只有 为 true 时才退出当 第一次 fetch 为 null 时不会退出 EXIT WHEN SQL%NOTFOUND OR SQL%NOTFOUND IS NULL; %ROWCOUNT语句影响的行数当前成功执行的数据行数非总记录数%ISOPEN游标是否打开始终为FALSESQL DECLAREv_TOYID TOYS.ID%type : TOYID;v_TOYNAME TOYS.NAME%Type : TOYNAME;BEGINUPDATE TOYS SET NAME v_TOYNAMEWHERE toyidv_TOYID;IF SQL%NOTFOUND THENDBMS_OUTPUT.PUT_LINE(编号未找到。);ELSEDBMS_OUTPUT.PUT_LINE(‘表已更新);END IF;END;DECLAREv_RoomData rooms%ROWTYPE;
BEGINSELECT *INTO v_RoomData FROM rooms WHERE room_id -1;IF SQL%NOTFOUND THEN
/*注意这里不执行以上自动引发select into预定义异常*/INSERT INTO temp_table (char_col) VALUES
(Not found!);END IF;
EXCEPTIONWHEN NO_DATA_FOUND THEN
/*注意这是真正执行的*/INSERT INTO temp_table (char_col)VALUES (Not found, exception handler);
END;declare2 v_rows number;3 begin4 update emp5 set comm10006 where deptno30;7 v_rows:SQL%ROWCOUNT;8 dbms_output.put_line(给部门30的
|| v_rows || 个雇员每人加了1000元奖金);9 end; 显式游标 显式游标在 PL/SQL 块的声明部分定义查询该查询可以返回多行 1、一行一行的处理返回的数据。 2、保持当前处理行的一个跟踪像一个指针一样指示当前的处理的记录。 3、允许程序员在PLSQL块中人为的控制游标的开启、关闭、上下移动 声明游标 划分存储区域注意此时并没有执行Select 语句 CURSOR 游标名[(参数1 数据类型[参数2 数据类型...])] IS SELECT语句; 参数是可选部分所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数则必须在打开游标时传递相应的实际参数。 SELECT语句是对表或视图的查询语句甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。 打开游标 执行Select 语句获得结果集存储到游标中此时游标指向结果集头 而不是第一条记录 在可执行部分按以下格式打开游标 OPEN 游标名[(实际参数1[实际参数2...])]; 打开游标时SELECT语句的查询结果就被传送到了游标工作区。 结果集控制 移动游标取一条记录 在可执行部分按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。 FETCH 游标名 INTO 变量名1[变量名2...]; 或 FETCH 游标名 INTO 记录变量; 游标打开后有一个指针指向数据区FETCH语句一次返回指针所指的一行数据要返回多行需重复执行可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。 下面对这两种格式进行说明 第一种格式中的变量名是用来从游标中接收数据的变量需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。 第二种格式一次将一行数据取到记录变量中需要使用%ROWTYPE事先定义记录变量这种形式使用起来比较方便不必分别定义和使用多个变量。 定义记录变量的方法如下 变量名 表名|游标名%ROWTYPE 其中的表必须存在游标名也必须先定义。 关闭游标 显式游标打开后必须显式地关闭。游标一旦关闭游标占用的资源就被释放游标变成无效必须重新打开才能使用。 SQLDECLAREmy_toy_price toys.toyprice%TYPE; 声明游标CURSOR toy_cur ISSELECT toyprice FROM toysWHERE toyprice250;BEGINOPEN toy_cur; LOOPFETCH toy_cur INTO my_toy_price;EXIT WHEN toy_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE (TOYPRICE:玩具单价||my_toy_price);END LOOP;CLOSE toy_cur;END;带参数的 DECLAREdesig VARCHAR2(20);emp_code VARCHAR2(5);empnm VARCHAR2(20);CURSOR emp_cur(desigparam VARCHAR2) ISSELECT empno, ename FROM employeeWHERE designationdesig;BEGINdesig: desig;OPEN emp_cur(desig);LOOPFETCH emp_cur INTO emp_code,empnm;EXIT WHEN emp_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_code|| ||empnm);END LOOP;CLOSE emp_cur;END 允许使用游标删除或更新活动集中的行 声明游标时必须使用 SELECT … FOR UPDATE语句 所有返回集中的数据行都将处于行级ROW-LEVEL)独占式锁定其他对象只能查询这些数据行 不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。 在多表查询中使用OF子句来锁定特定的表,如果忽略了OF子句那么所有表中选择的数据行都将被锁定。 如果这些数据行已经被其他会话锁定那么正常情况下ORACLE将等待直到数据行解锁 SQL SET SERVEROUTPUT ON
SQL DECLAREnew_price NUMBER;CURSOR cur_toy ISSELECT toyprice FROM toys WHERE toyprice100FOR UPDATE OF toyprice;
BEGINOPEN cur_toy;LOOPFETCH cur_toy INTO new_price;EXIT WHEN cur_toy%NOTFOUND;UPDATE toysSET toyprice 1.1*new_priceWHERE CURRENT OF cur_toy;END LOOP;CLOSE cur_toy;COMMIT;
END;declare2 cursor mycur(dept_no integer) is3 select * from dept4 where deptnodept_no for update;5 begin6 for myreco in mycur(50) loop7 delete from dept8 where current of mycur;9 end loop;10 end; 循环游标 declare2 cursor c_dept is3 select deptno, dname from dept order by deptno;4 cursor c_emp(p_dept varchar2) is5 select ename,sal from emp where deptnop_dept order by ename;6 v_salary emp.sal%type;7 begin8 for r_dept in c_dept loop9 dbms_output.put_line(Department: || r_dept.deptno||-||r_dept.dname);10 v_salary:0;11 for r_emp in c_emp(r_dept.deptno) loop12 dbms_output.put_line(Name:|| r_emp.ename|| salary||r_emp.sal);13 v_salary:v_salaryr_emp.sal;14 end loop;15 dbms_output.put_line(total salary for dept:|| v_salary);16 end loop;17* end; 动态SELECT Oracle支持动态SELECT语句和动态游标动态的方法大大扩展了程序设计的能力。 对于查询结果为一行的SELECT语句可以用动态生成查询语句字符串的方法在程序执行阶段临时地生成并执行语法是 execute immediate 查询语句字符串 into 变量1[,变量2...]; SET SERVEROUTPUT ON DECLARE str varchar2(100); v_ename varchar2(10); begin str:select ename from scott.emp where empno7788; execute immediate str into v_ename; dbms_output.put_line(v_ename); END; REF 游标 在变量声明部分定义的游标是静态的不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据但还是有很大的局限性。通过采用动态游标可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型然后声明一个游标变量游标对应的查询语句可以在程序的执行过程中动态地说明。 REF 游标和游标变量用于处理运行时动态执行的 SQL 查询 创建游标变量需要两个步骤 声明 REF 游标类型 声明 REF 游标类型的变量 TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]; 声明强类型的 REF 游标 1、for 后是 SQL语句而不能是 字符串 2、cur… 必须和 return 的 类型完全一致 3、无法使用 绑定变量 TYPE my_curtype IS REF CURSORRETURN stud_det%ROWTYPE;
order_cur my_curtype; 声明弱类型的 REF 游标系统类型 SYS_REFCURSOR TYPE my_ctype IS REF CURSOR;
stud_cur my_ctype;declare2 type cursor_type is ref cursor;3 stu_cursor cursor_type;4 v_stu 学生表%rowtype;5 begin6 open stu_cursor for7 select * from 学生表 where 性别男;8 loop9 fetch stu_cursor into v_stu;10 exit when stu_cursor%notfound;11 dbms_output.put_line(v_stu.学号 || ||v_stu.姓名|| ||v_stu.性别|| ||v_stu.年龄);12 end loop;13 close stu_cursor;14 end; declare2 type emp_type is ref cursor;3 cur emp_type;4 name varchar2(20);5 salary number(7,2);6 begin7 open cur for select ename,sal from emp where job:18 using SALESMAN;9 loop10 fetch cur into name,salary;11 exit when cur%notfound;12 dbms_output.put_line(name||:||salary);13 end loop;14 close cur;15 end; declare2 type empcurtyp is ref cursor;3 type idlist is table of emp.empno%type;4 type namelist is table of emp.ename%type;5 type sallist is table of emp.sal%type;6 emp_cv empcurtyp;7 ids idlist;8 names namelist; sals sallist;9 row_cn number;10 begin11 open emp_cv for select empno,ename,sal from emp;12 fetch emp_cv bulk collect into ids,names,sals;13 close emp_cv;14 for i in ids.first.. ids.last loop15 dbms_output.put_line(ids(i)|| ||names(i)|| ||sals(i));16 end loop;17* end; 游标变量的优点和限制 游标变量的功能强大可以简化数据处理。 游标变量的优点有 可从不同的 SELECT 语句中提取结果集 可以作为过程的参数进行传递 可以引用游标的所有属性 可以进行赋值运算 使用游标变量的限制 不能在程序包中声明游标变量 FOR UPDATE子句不能与游标变量一起使用 不能使用比较运算符 DECLAREv_sql VARCHAR(2000);v_b1 NUMBER(3) : 3;TYPE record_stu IS RECORD(v_id system.stu.s_id%TYPE,v_xm system.stu.s_xm%TYPE);TYPE table_stu IS TABLE OF record_stu;v_stu table_stu;cur_stu SYS_REFCURSOR;
BEGINv_sql : SELECT t.s_id, t.s_xm FROM stu t WHERE t.s_id :b1;OPEN cur_stu FOR v_sqlUSING v_b1; -- 绑定变量 : 大数据处理常用优化手段LOOPFETCH cur_stu BULK COLLECTINTO v_stu LIMIT 1; -- 数据量太少仅当前测试使用哦实际开发 建议 500 左右EXIT WHEN v_stu.count 0;FOR i IN v_stu.first .. v_stu.last LOOPdbms_output.put_line(序号 || v_stu(i).v_id || , || 姓名 || v_stu(i).v_xm);END LOOP;END LOOP;CLOSE cur_stu;EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLCODE || : || SQLERRM);dbms_output.put_line(dbms_utility.format_error_backtrace);
END;%type、%rowtype、record %type单条 记录类型自动匹配%rowtype所有 记录类型自动匹配record部分 记录类型自动匹配该变量类型允许用户在代码中使用“表”以便存储多个行数据。记录表类型是对记录类型的扩展可以处理多个记录或多行数据集合操作 在 Oracle 中集合中若想扩充大小有两种方法 方式1手动集合.extend一次只申请一个空间长度 方式2自动Type Type_name IS TABLE OF data_type index by .. Oracle 下标定义和其他语言有些区别 下标从 1 开始算而不是 0 哦。 varry 一维数组设置长度 type 数组名 is varray(size) of 元素类型 [not null]; size : 数组最大长度必填项。 DECLARE TYPE varry IS VARRAY(4) OF VARCHAR2(30); -- 最大长度是 4 v_varry varry; BEGIN -- 初始化 3 个下标, 也就只能写 3 个长度即使定义的 有 4 个长度 v_varry : varry(a, b, c); -- 构造方法 进行初始化 FOR i IN v_varry.first .. v_varry.last LOOP dbms_output.put_line(v_varry (i)); END LOOP; END; / table 多维数组 type table_name is table of element_type[not null] [index by [binary_integer|pls_integer]]; index by : 创建一个主键索引以便引用记录表变量中的特定行. -- 下列参数下标自增无需 显示初始化extend binary_integer : 由 Oracle来 执行不会出现溢出但是执行速度较慢 因为它是由 Oracle 模拟执行。 pls_integer : 由硬件即直接由 CPU 来运算因而会出现溢出但其执行速度 较前者快许多,数据范围参考 ‘Oracle 官方解释中的红色字体’ 存储单行多列 效果同 varry 一维数组 这个和 VARRAY 类似。但是赋值方式稍微有点不同不能使用同名的 构造函数 进行赋值。 有个细节去掉 index by .. 后是可以使用 构造函数 DECLARE -- 此例中 INDEX BY PLS_INTEGER 加不加都可以具体解释看后面 TYPE varry_stu IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER; v_stu_varry varry_stu; BEGIN -- varry_stu : varry_stu(a, b, c); -- 此时 不能像 varry 使用上述 构造函数 进行初始化了哦 v_stu_varry(1) : a; v_stu_varry(2) : b; v_stu_varry(3) : c; FOR i IN v_stu_varry.first .. v_stu_varry.last LOOP dbms_output.put_line(i || : || v_stu_varry(i)); END LOOP; END; / 存储多行多列TYPE 类型名 IS TABLE OF [%rowtype / record] 若想匹配所有字段与 %rowtype 最为方便 若想匹配部分字段与 record 最为方便 下列实例是数据仓库中表同步 的常见写法 当然实现功能的写法有很多种这里只给出我认为最优的。 DECLAREi_stu_id NUMBER(3) : 3; -- 模拟入参 v_sql VARCHAR(2000);cur_stu SYS_REFCURSOR;TYPE record_stu IS RECORD(v_id stu.id%TYPE,v_name stu.name%TYPE);TYPE table_stu IS TABLE OF record_stu; -- 思考加不加 index by ...?v_stu table_stu;BEGINv_sql : SELECT t.id, t.name FROM stu t WHERE t.id :b1;OPEN cur_stu FOR v_sqlUSING i_stu_id; -- 绑定变量 : 大数据处理常用优化手段LOOPFETCH cur_stu BULK COLLECTINTO v_stu LIMIT 2; -- 数据量太少仅当前测试使用哦实际开发 建议 500 左右EXIT WHEN v_stu.count 0;-- 细节如果此处使用 cur_stu%notfound 不足 limit n 的数据不会在操作了哦FOR i IN v_stu.first .. v_stu.last LOOPdbms_output.put_line(学号 || v_stu(i).v_id || , || 姓名 || v_stu(i).v_name);-- 模拟表同步不存在时 insert、存在时 updateEND LOOP;END LOOP;CLOSE cur_stu;EXCEPTIONWHEN OTHERS THENIF cur_stu%ISOPEN THENCLOSE cur_stu;END IF;dbms_output.put_line(SQLERRM);dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
/何时使用 index byPL/SQL 向 集合类型 中插入数据时必须先扩展 内存空间有两种方式 手动extend()自动index by … DECLARETYPE string_array IS TABLE OF VARCHAR2(30);TYPE string_array_index IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;v_a string_array;v_b string_array_index;
BEGIN-- 不加 INDEX BY... 是 可以 使用构造函数的v_a : string_array(a1, a2, a3);-- 加了 INDEX BY... 是 不能 使用构造函数的-- varry_stu : varry_stu(b1, b2, b3); v_b(v_b.count) : b0; -- 细节: 刚开始数组为空故 v_b.count 0v_b(v_b.count) : b1; -- 之后 count 依次递增切记不可指定 count哦不然就不递增了而是重复修改原记录v_b(v_b.count) : b2;-- TODO: 分别插入新数据 new1, new2-- v_av_a.extend(2);v_a(4) : new1;v_a(5) : new2;FOR i IN v_a.first .. v_a.last LOOPdbms_output.put_line(i || : || v_a(i));END LOOP;dbms_output.new_line();-- v_bv_b(v_b.count) : new1;v_b(v_b.count) : new2;FOR i IN v_b.first .. v_b.last LOOPdbms_output.put_line(i || : || v_b(i));END LOOP;
END;
/1 : a1
2 : a2
3 : a3
4 : new1
5 : new20 : b0
1 : b1
2 : b2
3 : new1
4 : new2空数组时 没加 index by 的数组必须 初始化空数组时array.first array.last null;空数组时array.count 0;DECLARETYPE string_array IS TABLE OF VARCHAR2(30);TYPE string_array_index IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;v_a string_array : string_array(); -- 必须先初始化v_b string_array_index; -- 加了 index by 相当于 默认初始化
BEGINdbms_output.put_line(v_a.first: || v_a.first);dbms_output.put_line(v_a.last: || v_a.last);dbms_output.put_line(v_a.count: || v_a.count);dbms_output.new_line();dbms_output.put_line(v_b.first: || v_b.first);dbms_output.put_line(v_b.last: || v_b.last);dbms_output.put_line(v_b.count: || v_a.count);
END;
/v_a.first:
v_a.last:
v_a.count: 0v_b.first:
v_b.last:
v_b.count: 0 清除数组元素 delete 和 置 null 的区别 置 null清除数组元素但 不删除内存空间delete清除数组元素并且 删除内存空间DECLARETYPE string_array_index IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;v_b string_array_index;
BEGINv_b(v_b.count) : b1; -- 因为 数组为空故而 v_b.count 0v_b(v_b.count) : b2;v_b(v_b.count) : b3;-- TODO置 null 方式清除数组元素dbms_output.put_line(原数组长度 || v_b.count);v_b(0) : NULL;dbms_output.put_line(置 null 后的长度 || v_b.count);FOR i IN v_b.first .. v_b.last LOOPdbms_output.put_line(i || : || v_b(i));END LOOP;dbms_output.new_line();-- TODOdelete 方式清除数组元素v_b.delete(0);dbms_output.put_line(delete 后的长度 || v_b.count);FOR i IN v_b.first .. v_b.last LOOPdbms_output.put_line(i || : || v_b(i));END LOOP;
END;
/原数组长度3
置 null 后的长度3
0 :
1 : b2
2 : b3delete 后的长度2
1 : b2
2 : b3 数组遍历时不连续下标异常 数组遍历时是严格按照下标顺序来的若中间出现 断层找不到下标就会报错 DECLARETYPE string_array_index IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;v_b string_array_index;
BEGINv_b(v_b.count) : b1; -- 因为 数组为空故而 v_b.count 0v_b(v_b.count) : b2;v_b(v_b.count) : b3;v_b.delete(1); -- 删除 b2FOR i IN v_b.first .. v_b.last LOOP-- 测试时请去掉 IF直接 dbms_output 即可。IF v_b.exists(i) THENdbms_output.put_line(i || : || v_b(i));END IF;END LOOP;
END;
/0 : b1
2 : b3 数组属性和函数属性/函数 描述 count 返回集合中元素的个数 delete 删除集合中 所有 的元素及 extend delete(x) 删除元素下标为 x 的元素对 varry 非法 delete(x, y) 删除元素下标从 x 到 y 的元素对 varry 非法 trim 从集合末端开始删除一个元素对 index by 非法 trim(x) 从集合末端开始删除 x 个元素 对 index by 非法 exists(x) 如果集合元素 x 已经 初始化(extend) 则返回 true否则返回 false extend 在集合 末尾 添加一个元素对 index by 非法 extend(x) 在集合 末尾 添加 x个元素 对 index by 非法 extend(x, n) 在集合 末尾 添加元素 x 个下标为n 的 副本对 index by 非法 first 返回集合中第一个元素的下标号对 varry 集合 始终 返回 1除非 未初始化 则为 空 last 返回集合中最后一个元素的下标号对 varry 集合 值始终 等于 count 除非 未初始化 则为 空 limit 返回 varry 集合的最大的元素个数对 index by 无效 next(x) 返回在第 x 个元素之后紧挨着它的元素下标(x1)若 x 是最后一个元素则返回 null prior(x) 返回在第x个元素之前紧挨着它的 元素下标(x-1)如果 x 是第一个元素则返回 nullCursor与 Ref Cursor区别从技术底层看两者是相同的。普通plsql cursor在定义时是“静态”的。而Ref cursors可以动态打开。 Ref cursor根据逻辑动态打开而游标cursor定义好了就无法修改了 ref cursor可以返回给客户端cursor则不行。 cursor可以是全局的global ref cursor则必须定义在过程或函数中。 ref cursor可以在子程序间传递cursor则不行。 cursor中定义的静态sql比ref cursor效率高所以ref cursor通常 ①静态游标是静态定义REF 游标是动态关联 ②使用REF 游标需REF 游标变量。 ③REF 游标能做为参数进行传递而静态游标是不可能的。 总结游标用于处理查询结果集中的数据 游标类型有隐式游标、显式游标和 REF 游标 隐式游标由 PL/SQL 自动定义、打开和关闭 显式游标用于处理返回多行的查询 显式游标可以删除和更新活动集中的行 要处理结果集中所有记录时可使用循环游标 在声明 REF 游标时不需要将 SELECT 语句与 其关联
