Oracle |
您所在的位置：网站首页 › 语法错误的种类 › Oracle |
Oracle
|
一、游标的分类
Oracle数据库提供了俩种游标类型,分别为静态游标和动态游标,而静态游标又分为隐士游标和显式游标,动态游标分为弱类型和强类型俩种。 二、静态游标1、显式游标 1).声明游标语法： cursor 游标名 [(游标输入参数1[,游标输入参数2]…)] [return 返回类型] is 查询语句 游标名：指定义的游标名称,一般采用cursor_名称这种命名格式。 游标输入参数：为游标指定输入参数,注意指定参数类型时,不能约束长度,比如NUMBER(10)是错误的。 返回类型：表示游标提取的一行数据的类型 查询语句：游标所使用的查询语句 游标参数和返回类型用[]圈起来表示是可以省略的条件,声明游标时可以不加这俩个参数 2).打开游标语法： open 游标名 [所有定义的游标输入参数] 3).提取游标语法： fetch 游标名 into 接收变量 4).关闭游标语法： close 游标名 使用示例： --检索EMP表中的所有JOB为MANAGER的雇员信息 DECLARE /*声明游标、(游标输入参数变量为VAR_JOB)可选项*/ CURSOR CUR_EMP(VAR_JOB IN VARCHAR2:='SALESMAN') IS /*游标所使用的查询语句*/ SELECT EMPNO, ENAME, SAL FROM EMP WHERE JOB = VAR_JOB; /*声明一个RECORD类型的记录变量*/ TYPE RECORD_EMP IS RECORD( VAR_EMPNO EMP.EMPNO%TYPE, VAR_ENAME EMP.ENAME%TYPE, VAR_SAL EMP.SAL%TYPE); EMP_ROW RECORD_EMP; BEGIN /*打开游标,指定输入参数值为MANAGER*/ OPEN CUR_EMP('MANAGER'); /*将游标指向结果集第一行数据并存入RECORD记录变量*/ FETCH CUR_EMP INTO EMP_ROW; /*如果游标有数据就循环*/ WHILE CUR_EMP%FOUND LOOP DBMS_OUTPUT.PUT_LINE('雇员编号：' || EMP_ROW.VAR_EMPNO || ' 雇员姓名：' || EMP_ROW.VAR_ENAME || ' 雇员薪水：' || EMP_ROW.VAR_SAL); /*将游标指向结果集下一条数据*/ FETCH CUR_EMP INTO EMP_ROW; END LOOP; /*关闭游标*/ CLOSE CUR_EMP; END; --另一种方式,使用%ROWTYPE类型 DECLARE CURSOR CUR_EMP IS /*使用FETCH+%ROWTYPE查询这里的查询字段必须和表中字段顺序及数量一致*/ SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP; /*定义一个%ROWTYPE接收变量*/ VAR_EMP_TYPE EMP%ROWTYPE; BEGIN OPEN CUR_EMP; FETCH CUR_EMP INTO VAR_EMP_TYPE; WHILE CUR_EMP%FOUND LOOP DBMS_OUTPUT.PUT_LINE('雇员编号：' || VAR_EMP_TYPE.EMPNO || ' 雇员姓名：' || VAR_EMP_TYPE.ENAME || ' 雇员薪水：' || VAR_EMP_TYPE.SAL); FETCH CUR_EMP INTO VAR_EMP_TYPE; END LOOP; CLOSE CUR_EMP; END;显示游标具有以下属性： 2、隐式游标 PL/SQL为所有SQL数据操纵语句(包括返回一行的SELECT)隐士声明游标,称为隐式游标的原因是用户不能直接命名和控制此类游标。当用户在PL/SQL中使用数据操纵语言(DML)时,Oracle预先定义一个名为SQL的隐士游标,通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息。 隐式游标属性表如下： 使用示例： --将EMP表中SALESMAN的工资加100,使用隐士游标sql的%rowcount属性输出增加工资的员工数量 DECLARE BEGIN UPDATE EMP SET SAL = SAL + 100 WHERE JOB = 'SALESMAN'; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('没有符合条件的雇员'); ELSE DBMS_OUTPUT.PUT_LINE('上调了：' || SQL%ROWCOUNT || '个雇员的工资'); END IF; END; 三、动态游标静态游标在使用时,使用的查询语句已经确定,如果需要在运行的时候动态的绝对执行哪种查询,可以使用REF游标(动态游标)和游标变量。 1).声明游标的语法 声明REF游标需要2个步骤：声明REF的游标类型和声明使用此类型的游标变量。 声明游标类型的语法： type 游标类型名称 is ref cursor [return 游标返回值类型] 游标类型名称：定义的一个游标变量的类型,一般采用ref_type_类型名 这种格式。 游标返回值类型：可选项,定义游标变量的返回值类型,注意必须为记录变量。 REF游标分为强类型和弱类型俩种,在定义游标变量类型时,如果指定了游标变量的返回值类型,那么就是强类型,如果没指定,就是弱类型。并且一旦指定了返回值类型,在打开游标时,绑定的查询结果的返回集一定是return中定义的类型. 声明游标变量的语法： 游标变量名称 游标类型名称 2).打开游标的语法： open 游标变量名 for 查询语句 3).提取游标,同显式游标。 4).关闭游标同显式游标。 使用示例： --弱类型 --将薪水低于3000的雇员薪水增加500,增加后最高不超过3000 DECLARE TYPE REF_CURSOR_TYPE IS REF CURSOR; --声明一个弱类型的动态游标类型 REF_CURSOR REF_CURSOR_TYPE; --定义一个游标为声明的弱类型游标 V_SAL EMP.SAL%TYPE;--声明一个变量用来接收雇员薪水 V_EMPNO EMP.EMPNO%TYPE;--声明一个变量用来接收雇员编号 BEGIN OPEN REF_CURSOR FOR SELECT SAL, EMPNO FROM EMP;--打开游标并指定使用的SQL语句 LOOP FETCH REF_CURSOR INTO V_SAL, V_EMPNO;--将游标指向一行数据并给变量赋值 EXIT WHEN REF_CURSOR%NOTFOUND;--当游标无数据时退出 IF V_SAL < 3000 THEN--薪水低于3000进入IF,这个IF最后会执行更新SQL IF V_SAL >= 2500 THEN--薪水低于3000又大于2500进入这个IF V_SAL := 3000; ELSE V_SAL := V_SAL + 500; END IF;--结束一个IF,一个IF对应一个END IF； UPDATE EMP SET SAL = V_SAL WHERE EMPNO = V_EMPNO;--更新雇员薪水 END IF;--结束最外层IF END LOOP;--结束LOOP循环 CLOSE REF_CURSOR;--关闭游标 END; --强类型 --查询所有雇员姓名 DECLARE TYPE EMP_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;--声明一个强类型(指定返回类型)的动态游标 REF_CURSOR EMP_REF_CURSOR;--定义一个声明的强类型游标 V_EMP_RECORD EMP%ROWTYPE;--定义一个接收变量 BEGIN OPEN REF_CURSOR FOR SELECT * FROM EMP; LOOP FETCH REF_CURSOR INTO V_EMP_RECORD; EXIT WHEN REF_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(V_EMP_RECORD.ENAME); END LOOP; CLOSE REF_CURSOR; END; 四、通过for语句循环游标使用for语句循环游标可以简化显式游标的处理代码。for语句循环游标隐式的打开游标,自动从活动集获取行、然后在处理完所有行时关闭游标。循环游标自动创建%ROWTYPE类型的变量并将此变量用做记录索引。 语法： for 记录变量 in 游标变量 loop //操作记录变量 end loop; 记录变量是PL/SQL声明的记录变量,此变量的属性声明为%ROWTYPE类型,作用域在for循环之内,for循环之外不能访问此变量。 for语句循环游标特性： 1、在从游标中提取了所有记录之后自动终止。 2、提取和处理游标中的每一条记录。 3、如果在提取之后%NOTFOUND属性返回TRUE,则终止循环。如果未返回行,则不进入循环。 使用示例： --查询所有雇员姓名 DECLARE CURSOR EMP_CURSOR IS SELECT * FROM EMP; BEGIN /*使用for语句循环游标无需手动打开/关闭游标*/ FOR V_EMP_RECORD IN EMP_CURSOR LOOP DBMS_OUTPUT.PUT_LINE(V_EMP_RECORD.ENAME); END LOOP; END; 五、NO_DATA_FOUND和%NOTFOUND的区别1、SELECT…INTO语句返回0条和多条记录时触发NO_DATA_FOUND。 2、当UPDATE或DELETE语句的WHERE子句未找到时触发%NOTFOUND。 3、在提取循环中要用%NOTFOUND或%FOUND来确定循环的退出条件,不要使用NO_DATA_FOUND。 |
【本文地址】