ORACLE，游标，存储过程，游标的使用（10）

 

游标

概念

在PL/SQL块执行SELECT ,INSERT,UPDATE,DELETE语句时，ORACLE会在内存中为其分配上下文区(Context Area)。游标是指向该区的指针，或是命名一个工作区(Work Area)，或是一种结构化数据类型。 它为应用程序提供了一种对具有多行数据的查询结果集中每行数据进行单独处理的方法，是设计交互式应用程序的常用编程接口。

 

      

Oracle 服务器 •执行 PL/SQL •程序 内存单元 保存到游标中 一次处理一行 •检索行 提取行

 

 

游标的类型

1）    隐式游标

2）    显示游标

3）    引用游标（REF）

 

使用游标的步骤

1）    声明游标

2）    打开游标

3）    提取数据

4）    关闭游标

范例：循环所有的员工信息，如果工资小于1000的，输出[需要加工资]，如果在1000和3000之间的，输出[还需要努力]， 如果大于3000的，输出[基本解决温饱问题]

DECLARE   CURSOR cursor_emp IS SELECT * FROM EMP;   v_emprow emp%rowtype; BEGIN   OPEN cursor_emp; -- 打开游标，游标指针指向第一行数据   LOOP        FETCH cursor_emp INTO v_emprow; --取出一行数据放到变量中,取完之后会将指针移动到下一行        IF v_emprow.SAL < 1000 THEN           DBMS_OUTPUT.PUT_LINE('工资太少了');        END IF;        EXIT WHEN cursor_emp%NOTFOUND;   END LOOP;   CLOSE cursor_emp; END;

游标%NOTFOUND : 当游标指针没有下一行的时候

游标%FOUND： 当游标可以移动到下一行，则返回TRUE

游标%ROWCOUNT : 当前游标指针处理到了第几行。

游标%ISOPEN: 判断当前游标是否已经打开， 只有打开的情况下才能操作这个游标数据。

 

 

参数游标

DECLARE   CURSOR cursor_emp(v_sal NUMBER) IS SELECT * FROM EMP WHERE SAL > v_sal; --参数中的数据类型不能指定精度   v_emprow emp%rowtype; BEGIN   OPEN cursor_emp(3000); -- 打开游标， 需要传递参数值   LOOP        IF cursor_emp%ISOPEN THEN          FETCH cursor_emp INTO v_emprow;          EXIT WHEN cursor_emp%NOTFOUND;          IF v_emprow.SAL < 1000 THEN             --DBMS_OUTPUT.PUT_LINE(cursor_emp%rowcount || ':工资太少了');             UPDATE EMP SET SAL = SAL + SAL * 10 WHERE empno = v_emprow.empno;             DBMS_OUTPUT.PUT_LINE(v_emprow.SAL + v_emprow.SAL * 10);          ELSIF v_emprow.SAL > 1000 AND v_emprow.SAL < 3000 THEN                UPDATE EMP SET SAL = SAL + SAL * 11 WHERE empno = v_emprow.empno;                DBMS_OUTPUT.PUT_LINE(v_emprow.SAL + v_emprow.SAL * 11);          ELSE                DBMS_OUTPUT.PUT_LINE(v_emprow.SAL + v_emprow.SAL * 15);                UPDATE EMP SET SAL = SAL + SAL * 15 WHERE empno = v_emprow.empno;          END IF;                END IF;   END LOOP;   CLOSE cursor_emp; END;

 

 

隐式游标

Begin   update emp set sal = 1200 where empno = 1234;   if SQL%NOTFOUND then    DBMS_OUTPUT.PUT_LINE('没有找到数据');   end if; End;     BEGIN   UPDATE emp SET ename = 'Rob Mathew' ;   DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END;

 

 

FOR循环游标数据

DECLARE   CURSOR cursor_emp IS SELECT * FROM EMP;   BEGIN         FOR rowone IN cursor_emp LOOP             DBMS_OUTPUT.PUT_LINE(rowone.ename);         END LOOP;   END;

 

 

 

允许使用游标删除或更新活动集中的行 声明游标时必须使用 SELECT … FOR UPDATE语句 OF 列名            CURSOR <cursor_name> IS          SELECT statement FOR UPDATE;          UPDATE <table_name>        SET <set_clause>        WHERE CURRENT OF <cursor_name>

 

注意：更新完数据之后不要忘记提交数据（COMMIT）

 

引用游标

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询 创建游标变量需要两个步骤： 声明 REF 游标类型 声明 REF 游标类型的变量 用于声明 REF 游标类型的语法为： TYPE <ref_cursor_name> IS REF CURSOR        [RETURN <return_type>];          return用于指定游标提取结果集的返回类型。有return表示是强类型ref游标，没有return表示是弱类型的游标。弱类型游标可以提取任何类型的结果集。

 

 

弱类型游标范例

DECLARE   TYPE cursor_emp_type IS REF CURSOR; --弱类型的游标，游标指向的数据是不确定的，可以指向任何的结果集   v_emprow emp%rowtype;   cursor_emp cursor_emp_type; BEGIN      OPEN cursor_emp FOR SELECT * FROM EMP;      LOOP           FETCH cursor_emp INTO v_emprow;           EXIT WHEN cursor_emp%NOTFOUND;           DBMS_OUTPUT.PUT_LINE(v_emprow.ename);      END LOOP;      CLOSE cursor_emp; END;

 

 

强类型游标范例

DECLARE   TYPE cursor_emp_type IS REF CURSOR RETURN emp%rowtype;--强类型的游标，要求结果集一定是访问EMP表   v_emprow emp%rowtype;   cursor_emp cursor_emp_type; BEGIN      OPEN cursor_emp FOR SELECT * FROM EMP;      LOOP           FETCH cursor_emp INTO v_emprow;           EXIT WHEN cursor_emp%NOTFOUND;           DBMS_OUTPUT.PUT_LINE(v_emprow.ename);      END LOOP;      CLOSE cursor_emp; END;

存储过程

 

 

 

 

 

 

ORACLE 服务器

WEB 服务器（TOMCAT）

发送SQL

存储过程 PROC_A PROC_B

浏 览 器

PROC_A

数据对象

 

 

 

 

 

 

 

 

 

 

 

 

 

 

存储过程的优点：

1）   存储过程是被编译之后放到服务器端的，提高访问效率。

2）   减少网络流量

3）   安全性（是否具有调用该存储过程的权限）

4）   模块化编程

 

语法

[create [or replace]] procedure procedure_name[(parameter[, parameter]…)] [authid {definer | current_schema}]{is | as} [pragma autonomous_transaction;] [local_declarations] begin executable statements [exception exception handlers] end [procedure_name];

 

 

范例：使用存储过程，显示员工工资。如果大于或等于2500的员工显示员工编号和工资，如果没有则打印【没找到数据】。

CREATE OR REPLACE PROCEDURE PROC_FIND_EMP AS  -- 声明区  CURSOR cursor_emp IS SELECT * FROM EMP; BEGIN  FOR v_emprow IN cursor_emp LOOP       IF(v_emprow.SAL >= 2500) THEN              DBMS_OUTPUT.put_line(v_emprow.empno || ':' || v_emprow.sal);       ELSE              DBMS_OUTPUT.put_line('工资太低');               END IF;  END LOOP; END;

 

 

参数的类型

IN : 只能查看，不能改变这个值

OUT： 是作为输出的值，在调用的地方是可以访问的。

IN OUT ： 既可以作为输入，也可以作为输出。

 

CREATE OR REPLACE PROCEDURE PROC_FIND_EMPINFO(v_empno IN NUMBER, yearsal OUT NUMBER) AS  BEGIN   SELECT SAL * 12 + NVL(COMM, 0) INTO yearsal FROM EMP WHERE EMPNO = v_empno; END;    --调用  DECLARE   v_yearsal number(7,2);  BEGIN   PROC_FIND_EMPINFO(7499, v_yearsal);  DBMS_OUTPUT.PUT_LINE('年薪是:' || v_yearsal);  END;