cursor is oracle 日期_Oracle cursor

--游标分2种类型： --静态游标：结果集已经存在(静态定义)的游标,分为隐式和显示游标

隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息

显示游标：用户显示声明的游标，即指定结果集，当查询返回结果超过一定行时，就需要一个显示游标

--REF 游标：动态关联结果集的临时对象

-强类型：带return类型

-弱类型: 不带return类型

--隐式游标 --在PL/SQL中编写的每条SQL 语句实际上都是隐匿游标。通过在DML操作后使用SQL%ROWCOUNT属性，可以 --知道语句所改变的行数(INSERT ,UPDATE,DELETE)返回理新行数，SELECT 返回查询行数.

--显示游标

--语法：CURSOR 游标名称 ([参数列表，]) [RETURN 返回值类型] IS 子查询(SELECT _statement)

--第一步：声明游标： CURSOR 游标名 IS SELECT 。。使用CURSOR定义 --第二步:打开游标     使用OPEN

OPEN 游标名 --第三步：提取游标     使用FETCH  游标 INTO 变量 --第四步:关闭游标 CLOSE 游标名

--显式游标属性:   %FOUND     找到是否找到数据，有数据TRUE，没有则FALSE   %ISOPEN

判断游标是否打开，打开则返回TRUE，没有打开则返回FALSE   %NOTFOUND  返回FETCH

...INTO...是否有数据如果没有返回TRUN，有则为FALSE   %ROWCOUNT  返回执行FETCH

语句所返回的行数，初始为0,每执行一行则%ROWCOUNT增加1 --隐式游标属性: SQL%FOUND

找到是否找到数据，有数据TRUE，没有则FALSE   SQL%ISOPEN

判断游标是否打开，打开则返回TRUE，没有打开则返回FALSE   SQL%NOTFOUND  返回FETCH

...INTO...是否有数据如果没有返回TRUN，有则为FALSE   SQL%ROWCOUNT  返回执行FETCH

语句所返回的行数，初始为0,每执行一行则%ROWCOUNT增加1

隐式游标:

--验证SQL%ROWCOUNT

DECLAREv_count NUMBER;

BEGIN

SELECT COUNT(*) INTO v_count FROM dept; --只返回一行结果

dbms_output.put_line('SQL%ROWCOUNT= '|| SQL%ROWCOUNT);

END;

结果:SQL%ROWCOUNT= 1

--验证SLQ%ROWCOUNT并返回行数

DECLARE

BEGIN

INSERT INTO dept(deptno,dname,loc)VALUES(90,'qqqq','北京');

dbms_output.put_line('SQL%ROWCOUNT= '|| SQL%ROWCOUNT);

END;

结果:SQL%ROWCOUNT= 1

--单行隐式游标

DECLAREv_empRow emp%ROWTYPE;

BEGIN

SELECT * INTO v_empRow FROM emp WHERE empno=7369;

IF SQL%FOUND THEN --发现数据

dbms_output.put_line('员工姓名: '|| v_empRow.ename||'职位: '||v_empRow.job);

END IF;

END;

结果：员工姓名: SMITH职位: CLERK

--多行隐式游标

DECLARE

BEGIN

UPDATE EMP SET SAL = SAL * 1.2;

IF SQL%FOUND THEN

--发现数据

DBMS_OUTPUT.PUT_LINE('更新行数' || SQL%ROWCOUNT);

ELSEDBMS_OUTPUT.PUT_LINE('更新行数' || SQL%ROWCOUNT);

END IF;

END;

结果:更新行数14

--显示游标 --定义游标例1：

DECLARE

CURSOR emp_cur IS

SELECT empno,ename FROM emp; --定义游标

v_id emp.empno%TYPE; --定义变量ID

v_name emp.ename%TYPE;

BEGIN

OPEN emp_cur ; --打开游标

FETCH emp_cur INTO v_id,v_name; ---提示取游标

LOOP

EXIT WHEN emp_cur%notFOUND; --判断是否还有数据

dbms_output.put_line('员工编号'||v_id||',员工姓名:'||v_name);

FETCH emp_cur INTO v_id,v_name; ---提示取游标

ENDLOOP;

CLOSE emp_cur; --关闭游标

END;

结果:

员工编号7369,员工姓名:SMITH

员工编号7499,员工姓名:ALLEN

员工编号7521,员工姓名:WARD

员工编号7566,员工姓名:JONES

员工编号7654,员工姓名:MARTIN

员工编号7698,员工姓名:BLAKE

员工编号7782,员工姓名:CLARK

员工编号7788,员工姓名:SCOTT

员工编号7839,员工姓名:KING

员工编号7844,员工姓名:TURNER

员工编号7876,员工姓名:ADAMS

员工编号7900,员工姓名:JAMES

员工编号7902,员工姓名:FORD

员工编号7934,员工姓名:MILLER

--定义游标例2：

DECLAREV_NAME VARCHAR2(50); --定义变量姓名

V_DNAME VARCHAR2(50); --定义变量部门名称

CURSOR CUR_E IS --定义游标

SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO =DEPT.DEPTNO;

BEGIN

OPEN CUR_E; --打开游标

LOOP

--使用循环来读取游标

FETCHCUR_E

INTO V_NAME, V_DNAME; --提取游标

EXIT WHEN CUR_E%NOTFOUND; --判断游标是否还有内容

DBMS_OUTPUT.PUT_LINE(CUR_E%ROWCOUNT || '员工姓名:' || V_NAME || '部门名称:' ||V_DNAME); --输出内容

ENDLOOP;

CLOSE CUR_E; --关闭游标

END;

结果:

程序结果:

1员工姓名:SMITH 部门名称:RESEARCH

2员工姓名:ALLEN 部门名称:SALES

3员工姓名:WARD 部门名称:SALES

4员工姓名:JONES 部门名称:RESEARCH

5员工姓名:MARTIN 部门名称:SALES

6员工姓名:BLAKE 部门名称:SALES

7员工姓名:CLARK 部门名称:ACCOUNTING

8员工姓名:SCOTT 部门名称:RESEARCH

9员工姓名:KING 部门名称:ACCOUNTING

10员工姓名:TURNER 部门名称:SALES

11员工姓名:ADAMS 部门名称:RESEARCH

12员工姓名:JAMES 部门名称:SALES

13员工姓名:FORD 部门名称:RESEARCH

14 员工姓名:MILLER 部门名称:ACCOUNTING

另一种指定变量类型:

DECLAREV_EMPNAME EMP.ENAME%TYPE;

V_DNAME DEPT.DNAME%TYPE;

CURSOR EMP_CUR IS

SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO =D.DEPTNO;

BEGIN

OPENEMP_CUR;

LOOP

FETCHEMP_CUR

INTOV_EMPNAME, V_DNAME;

EXIT WHEN EMP_CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('员工姓名 :' || V_EMPNAME || '，部门名称 :' ||V_DNAME);

ENDLOOP;

CLOSEEMP_CUR;

END;

结果同上

--定义游标例3：

DECLARE

CURSOR CUR_EMP IS

SELECT * FROMEMP;

V_EMPROW EMP%ROWTYPE;

BEGIN

IF CUR_EMP%ISOPEN THEN

NULL;

ELSE

OPENCUR_EMP;

END IF;

FETCHCUR_EMP

INTOV_EMPROW;

WHILE CUR_EMP%FOUND LOOP

DBMS_OUTPUT.PUT_LINE('员工姓名: ' || V_EMPROW.ENAME || '，职位: ' ||V_EMPROW.JOB || ',工资' ||V_EMPROW.SAL);

FETCHCUR_EMP

INTOV_EMPROW;

ENDLOOP;

CLOSECUR_EMP;

END;

结果:

员工姓名: SMITH，职位: CLERK ,工资800

员工姓名: ALLEN，职位: SALESMAN ,工资1600

员工姓名: WARD，职位: SALESMAN ,工资1250

员工姓名: JONES，职位: MANAGER ,工资2975

员工姓名: MARTIN，职位: SALESMAN ,工资1250

员工姓名: BLAKE，职位: MANAGER ,工资2850

员工姓名: CLARK，职位: MANAGER ,工资2450

员工姓名: SCOTT，职位: ANALYST ,工资3000

员工姓名: KING，职位: PRESIDENT ,工资5000

员工姓名: TURNER，职位: SALESMAN ,工资1500

员工姓名: ADAMS，职位: CLERK ,工资1100

员工姓名: JAMES，职位: CLERK ,工资950

员工姓名: FORD，职位: ANALYST ,工资3000

员工姓名: MILLER，职位: CLERK ,工资1300

--使用FOR循环

DECLARE

CURSOR cur_emp IS SELECT * FROMemp;

BEGIN

FOR emp_row INcur_emp LOOP

DBMS_OUTPUT.PUT_LINE('员工姓名: ' || emp_row.ENAME || '，职位: ' ||emp_row.JOB || ',工资' ||emp_row.SAL);

ENDLOOP;

END;

结果同上

--使用FOR循环操作游标不仅代码简单，而且可以将游标的状态交给系统去完成，尽量使用FOR循环为主

--定义游标例4：使用游标UPDATE数据 --公司上市，决定给员工涨工资，入职年限超过1年加100，1000元封顶 --第一种 直接将计算的结果进行判断

DECLAREV_ID EMP.EMPNO%TYPE;

V_HIREDATE EMP.HIREDATE%TYPE;

CURSOR EMP_CUR IS

SELECT EMPNO, HIREDATE FROMEMP;

BEGIN

OPENEMP_CUR;

LOOP

FETCHEMP_CUR

INTOV_ID, V_HIREDATE;

EXIT WHEN EMP_CUR%NOTFOUND;

IF (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100 < 1000 THEN

UPDATEEMP

SET SAL = SAL +(TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100

WHERE EMPNO =V_ID;

DBMS_OUTPUT.PUT_LINE('工资增加成功');

COMMIT;

ELSE

UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO =V_ID;

DBMS_OUTPUT.PUT_LINE('工资增加成功');

COMMIT;

END IF;

ENDLOOP;

CLOSEEMP_CUR;

EXCEPTION

WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('数据异常');

ROLLBACK;

END;

--第二种通过一个变量判断

DECLAREV_ID EMP.EMPNO%TYPE; --定义员工编号ID

V_HIREDATE EMP.HIREDATE%TYPE; --定义员工入职日期变量

V_SAL EMP.SAL%TYPE; --定义计算每个员工要涨工资的总数变量

CURSOR CUR_EMP IS

SELECT EMPNO, HIREDATE --定义游标查询员工ID和入职日期

FROMEMP;

BEGIN

IF CUR_EMP%ISOPEN THEN

--判断游标是否打开

NULL; --打开了就什么也不做

ELSE

OPEN CUR_EMP; --没有打开就打开游标

END IF;

LOOP

FETCHCUR_EMP

INTOV_ID, V_HIREDATE;

EXIT WHEN CUR_EMP%NOTFOUND;

V_SAL := (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100;

IF V_SAL < 1000 THEN

--判断是否小于1000

UPDATE EMP SET SAL = SAL + V_SAL WHERE EMPNO =V_ID;

COMMIT;

ELSE

--大于1000

UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO =V_ID;

COMMIT;

END IF;

ENDLOOP;

CLOSE CUR_EMP; --关闭游标

EXCEPTION

WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('数据异常');

ROLLBACK; --出现异常 就回滚

END;

--定义游标例5 --在动态SELECT中使用游标

DECLAREV_LOWSAL EMP.SAL%TYPE := &LOWSAL;

V_HISAL EMP.SAL%TYPE := &HISSAL;

CURSOR CUR_EMP IS

SELECT * FROM EMP WHERE SAL BETWEEN V_LOWSAL ANDV_HISAL;

BEGIN

FOR EMP_ROW INCUR_EMP LOOP

DBMS_OUTPUT.PUT_LINE('员工姓名: ' || EMP_ROW.ENAME || '，职位: ' ||EMP_ROW.JOB || ',工资' ||EMP_ROW.SAL);

ENDLOOP;

END;

--REF动态游标 TYPE 类型名 IS REF CURSOR [RETURN]数据类型 游标名 类型名       OPEN 游标名 FOR 查询语句 --强类型:带RETURN

DECLARETYPE REF_EMP IS REF CURSOR RETURN EMP%ROWTYPE; --定义一个REF动态游标，并返回类型

CUR_EMP REF_EMP; --定义一个变量类型是上面的REF动态游标也称游标变量

V_EMP EMP%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型

BEGIN

OPEN CUR_EMP FOR

SELECT * FROM EMP; --打开游标，并关联查询语句

LOOP

FETCHCUR_EMP

INTO V_EMP; --提取游标数据

EXIT WHEN CUR_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT||'员工编号:' || V_EMP.EMPNO || '员工姓名:' ||V_EMP.ENAME);

ENDLOOP;

CLOSECUR_EMP;

END;

--弱类型:不带RETURN

DECLARETYPE REF_EMP IS REF CURSOR; --定义一个REF动态游标，并返回类型

CUR_EMP REF_EMP; --定义一个变量类型是上面的REF动态游标也称游标变量

V_EMP EMP%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型

V_DEPT DEPT%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型

BEGIN

--员工表

OPEN CUR_EMP FOR

SELECT * FROM EMP; --打开游标，并关联查询语句

LOOP

FETCHCUR_EMP

INTO V_EMP; --提取游标数据

EXIT WHEN CUR_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '员工编号:' || V_EMP.EMPNO ||

'员工姓名:' ||V_EMP.ENAME);

ENDLOOP;

CLOSECUR_EMP;

------------下面是部门表

OPEN CUR_EMP FOR

SELECT * FROM DEPT; --打开游标，并关联查询语句

LOOP

FETCHCUR_EMP

INTO V_DEPT; --提取游标数据

EXIT WHEN CUR_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '部门编号:' || V_DEPT.DEPTNO ||

'部门名称:' ||V_DEPT.DNAME);

ENDLOOP;

CLOSECUR_EMP;

END;

在Oracle9i之后为了方便用户使用弱类型游标变量，可以使用

SYS_REFCURSOR 来替代 TYPE REF_EMP IS REF CURSOR上面的声明可以换为:

CUR_EMP SYS_REFCURSOR; --定义一个变量类型是上面的REF动态游标也称游标变量

V_EMP EMP%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型

V_DEPT DEPT%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型

--根据用户输入，来输出内容

DECLARE

--TYPE REFC_T IS REF CURSOR;

REFC SYS_REFCURSOR;

V_ID NUMBER;

V_NAME VARCHAR2(50);

V_INPUT VARCHAR(1) := UPPER(SUBSTR('&input', 1, 1));

BEGIN

IF V_INPUT = 'E' THEN

OPEN REFC FOR

SELECT EMPNO, ENAME FROMEMP;

DBMS_OUTPUT.PUT_LINE('=====员工表信息======');

ELSIF V_INPUT = 'D' THEN

OPEN REFC FOR

SELECT DEPTNO, DNAME FROMDEPT;

DBMS_OUTPUT.PUT_LINE('=====部门表信息======');

ELSEDBMS_OUTPUT.PUT_LINE('=====员工表信息(E)或者部门表信息(D)=======');

RETURN;

END IF;

FETCHREFC

INTOV_ID, V_NAME;

WHILE REFC%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(REFC%ROWCOUNT || '# ' || V_ID || ' ' ||V_NAME);

FETCHREFC

INTOV_ID, V_NAME;

ENDLOOP;

CLOSEREFC;

END;