范例：验证ROWCOUNT
|
DECLARE v_count NUMBER ; BEGIN SELECT COUNT(*) INTO v_count FROM dept ; -- 只返回一行结果 DBMS_OUTPUT.put_line('SQL%ROWCOUNT = '|| SQL%ROWCOUNT) ; END ; / |
范例：验证ROWCOUNT，增加新数据返回行数
|
DECLARE BEGIN INSERT INTO dept(deptno,dname,loc) VALUES (90,'MLDN','北京') ; DBMS_OUTPUT.put_line('SQL%ROWCOUNT = '|| SQL%ROWCOUNT) ; END ; / |
范例：单行隐式游标
|
DECLARE v_empRow emp%ROWTYPE ; -- 保存emp每行记录 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 ; / |
范例：更新多行记录
|
BEGIN UPDATE emp SET sal=sal*1.2 ; IF SQL%FOUND THEN -- 发现数据 DBMS_OUTPUT.put_line('更新记录行数：' ||SQL%ROWCOUNT) ; ELSE DBMS_OUTPUT.put_line('没有记录被修改！') ; END IF ; END ; / |
范例：定义显式游标
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp ; v_empRow emp%ROWTYPE ; BEGIN IF cur_emp%ISOPEN THEN -- 游标已经打开 NULL ; ELSE -- 游标未打开 OPEN cur_emp ; -- 打开游标 END IF ; FETCH cur_emp INTO v_empRow ; -- 取出游标当前行数据 WHILE cur_emp%FOUND LOOP -- 判断是否有数据 DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || v_empRow.ename || '，职位：' || v_empRow.job || '，工资：' || v_empRow.sal) ; FETCH cur_emp INTO v_empRow ; -- 把游标指向下一行 END LOOP ; CLOSE cur_emp ; -- 关闭游标 END ; / |
范例：修改游标定义
|
CURSOR cur_emp RETURN emp%ROWTYPE IS SELECT * FROM emp ; |
范例：没有打开游标直接进行操作
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp ; v_empRow emp%ROWTYPE ; BEGIN LOOP -- 没有打开游标 FETCH cur_emp INTO v_empRow ; -- 取出游标当前行数据 EXIT WHEN cur_emp%NOTFOUND ; -- 如果没有找到数据则退出循环 DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || v_empRow.ename) ; END LOOP ; CLOSE cur_emp ; -- 关闭游标 EXCEPTION WHEN INVALID_CURSOR THEN DBMS_OUTPUT.put_line('程序出错。SQL CODE = ' || SQLCODE || '，SQLERRM = ' || SQLERRM) ; END ; / |
范例：使用LOOP循环输出游标
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp ; v_empRow emp%ROWTYPE ; BEGIN IF cur_emp%ISOPEN THEN -- 游标打开 NULL ; ELSE -- 游标未打开 OPEN cur_emp ; -- 打开游标 END IF ; LOOP FETCH cur_emp INTO v_empRow ; -- 取出游标当前行数据 EXIT WHEN cur_emp%NOTFOUND ; -- 如果没有找到数据则退出循环 DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || v_empRow.ename || '，职位：' || v_empRow.job || '，工资：' || v_empRow.sal) ; END LOOP ; CLOSE cur_emp ; -- 关闭游标 END ; / |
范例：使用FOR循环操作游标
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp ; BEGIN FOR emp_row IN cur_emp LOOP DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || emp_row.ename || '，职位：' || emp_row.job || '，工资：' || emp_row.sal) ; END LOOP ; END ; / |
范例：查询数据
|
BEGIN FOR v_dept IN (SELECT deptno,dname,loc FROM dept) LOOP DBMS_OUTPUT.put_line('部门编号：' || v_dept.deptno || '，名称：' || v_dept.dname || '，位置：' || v_dept.loc) ; END LOOP ; END ; / |
范例：将游标数据保存在索引表中
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp ; -- 定义游标取得emp表数据 TYPE emp_index IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER ;-- 定义索引表数据类型为emp行结构 v_emp emp_index ; -- 定义索引表变量 BEGIN FOR emp_row IN cur_emp LOOP -- 利用循环取得每一行记录 v_emp(emp_row.empno) := emp_row ; -- 将雇员编号作为索引表下标 END LOOP ; DBMS_OUTPUT.put_line('雇员编号：' || v_emp(7369).empno || '，姓名：' || v_emp(7369).ename || '，职位：' || v_emp(7369).job) ; END ; / |
范例：动态SELECT中使用游标
|
DECLARE v_lowsal emp.sal%TYPE := &inputlowsal ; v_highsal emp.sal%TYPE := &inputhighsal ; CURSOR cur_emp IS SELECT * FROM emp WHERE sal BETWEEN v_lowsal AND v_highsal ; BEGIN FOR emp_row IN cur_emp LOOP DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || emp_row.ename || '，职位：' || emp_row.job || '，工资：' || emp_row.sal) ; END LOOP ; END ; / |
范例：定义参数游标
|
DECLARE CURSOR cur_emp (p_dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=p_dno; BEGIN FOR emp_row IN cur_emp(&inputDeptno) LOOP DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || emp_row.ename || '，职位：' || emp_row.job || '，工资：' || emp_row.sal) ; END LOOP ; END ; / |
范例：使用嵌套表接收游标数据
|
DECLARE TYPE dept_nested IS TABLE OF dept%ROWTYPE ; -- 定义dept的嵌套表类型 v_dept dept_nested ; CURSOR cur_dept IS SELECT * FROM dept ; -- 定义游标 BEGIN IF cur_dept%ISOPEN THEN -- 游标已经打开 NULL ; ELSE -- 游标未打开 OPEN cur_dept ; -- 打开游标 END IF ; FETCH cur_dept BULK COLLECT INTO v_dept ; -- 保存整个游标 CLOSE cur_dept ; -- 关闭游标 FOR x IN v_dept.FIRST .. v_dept.LAST LOOP DBMS_OUTPUT.put_line('部门编号：' || v_dept(x).deptno || '，部门名称：' || v_dept(x).dname || '，部门位置：' || v_dept(x).loc) ; END LOOP ; END ; / |
范例：取得部分数据保存在数组之中
|
DECLARE TYPE dept_varray IS VARRAY(2) OF dept%ROWTYPE ; v_dept dept_varray ; v_rows NUMBER := 2 ; -- 每次提取的行数 v_count NUMBER := 1 ; -- 每次少显示1条记录 CURSOR cur_dept IS SELECT * FROM dept ; -- 定义游标 BEGIN IF cur_dept%ISOPEN THEN -- 游标已经打开 NULL ; ELSE -- 游标未打开 OPEN cur_dept ; -- 打开游标 END IF ; FETCH cur_dept BULK COLLECT INTO v_dept LIMIT v_rows ;-- 保存指定行数 CLOSE cur_dept ; -- 关闭游标 FOR x IN v_dept.FIRST .. (v_dept.LAST - v_count) LOOP DBMS_OUTPUT.put_line('部门编号：' || v_dept(x).deptno || '，部门名称：' || v_dept(x).dname || '，部门位置：' || v_dept(x).loc) ; END LOOP ; END ; / |
范例：一次上涨所有人的工资，工资上涨原则如下：
· 10部门上涨15%；
· 20部门上涨22%；
· 30部门上涨39%。
但是每一个雇员的工资上限为5000，即上涨5000之后就不能再涨了。
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp ; -- emp表游标数据 BEGIN FOR emp_row IN cur_emp LOOP -- 循环游标的每一行数据 IF emp_row.deptno = 10 THEN IF emp_row.sal*1.15 < 5000 THEN UPDATE emp SET sal=sal*1.15 WHERE empno=emp_row.empno ; ELSE UPDATE emp SET sal=5000 WHERE empno=emp_row.empno ; END IF ; ELSIF emp_row.deptno = 20 THEN IF emp_row.sal*1.22 < 5000 THEN UPDATE emp SET sal=sal*1.22 WHERE empno=emp_row.empno ; ELSE UPDATE emp SET sal=5000 WHERE empno=emp_row.empno ; END IF ; ELSIF emp_row.deptno = 30 THEN IF emp_row.sal*1.39 < 5000 THEN UPDATE emp SET sal=sal*1.39 WHERE empno=emp_row.empno ; ELSE UPDATE emp SET sal=5000 WHERE empno=emp_row.empno ; END IF ; ELSE NULL ; END IF ; END LOOP ; EXCEPTION WHEN others THEN DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; ROLLBACK ; END ; / |
范例：为游标数据增加行级锁
|
CURSOR cur_emp IS SELECT * FROM emp WHERE deptno=10 FOR UPDATE OF sal,comm. ; |
范例：创建不等待游标
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp WHERE deptno=10 FOR UPDATE NOWAIT; BEGIN FOR emp_row IN cur_emp LOOP UPDATE emp SET sal=9999 WHERE empno=emp_row.empno ; END LOOP ; END ; / |
范例：使用WHERE CURRENT OF子句
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp WHERE deptno=10 FOR UPDATE OF sal,comm ; BEGIN FOR emp_row IN cur_emp LOOP UPDATE emp SET sal=9999 WHERE CURRENT OF cur_emp ; END LOOP ; END ; / |
范例：使用游标删除数据
|
DECLARE CURSOR cur_emp IS SELECT * FROM emp WHERE deptno=10 FOR UPDATE OF sal, comm ; BEGIN FOR emp_row IN cur_emp LOOP DELETE FROM emp WHERE CURRENT OF cur_emp ; END LOOP ; END ; / |
1、 创建一个新的游标使用“FOR UPDATE”，采用多表查询
|
DECLARE CURSOR cur_emp IS SELECT e.ename,e.job,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=10 AND d.deptno=e.deptno FOR UPDATE ; BEGIN FOR emp_row IN cur_emp LOOP UPDATE emp SET sal=9999 WHERE CURRENT OF cur_emp ; END LOOP ; END ; / |
创建一个新的游标使用“FOR UPDATE OF 列..”，采用多表查询
|
DECLARE CURSOR cur_emp IS SELECT e.ename,e.job,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=10 AND d.deptno=e.deptno FOR UPDATE OF sal ; BEGIN FOR emp_row IN cur_emp LOOP UPDATE emp SET sal=9999 WHERE CURRENT OF cur_emp ; END LOOP ; END ; / |
范例：定义一个游标类型，此游标类型为dept类型
|
DECLARE TYPE dept_ref IS REF CURSOR RETURN dept%ROWTYPE ; -- 定义游标类型 cur_dept dept_ref ; -- 定义游标变量 v_deptRow dept%ROWTYPE ; -- 定义行类型 BEGIN OPEN cur_dept FOR SELECT * FROM dept ; -- 打开游标 LOOP FETCH cur_dept INTO v_deptRow ; -- 取得游标数据 EXIT WHEN cur_dept%NOTFOUND ; -- 如果没有数据则退出 DBMS_OUTPUT.put_line('部门名称：' || v_deptRow.dname || '，部门位置：' || v_deptRow.loc) ; END LOOP ; CLOSE cur_dept ; -- 关闭游标 END ; / |
范例：设置错误的数据结构
|
DECLARE TYPE dept_ref IS REF CURSOR ; -- 定义游标类型 cur_dept dept_ref ; -- 定义游标变量 v_deptRow dept%ROWTYPE ; -- 定义行类型 BEGIN OPEN cur_dept FOR SELECT * FROM emp ;-- 打开游标，类型错误 LOOP FETCH cur_dept INTO v_deptRow ; -- 取得游标数据 EXIT WHEN cur_dept%NOTFOUND ; -- 如果没有数据则退出 DBMS_OUTPUT.put_line('部门名称：' || v_deptRow.dname || '，部门位置：' || v_deptRow.loc) ; END LOOP ; CLOSE cur_dept ; -- 关闭游标 EXCEPTION WHEN ROWTYPE_MISMATCH THEN DBMS_OUTPUT.put_line('游标数据类型不匹配异常。SQL CODE = ' || SQLCODE || '，SQLERRM = ' || SQLERRM) ; END ; / |
范例：定义弱类型游标变量
|
DECLARE TYPE cursor_ref IS REF CURSOR ; -- 定义游标类型 cur_var cursor_ref ; -- 定义游标变量 v_deptRow dept%ROWTYPE ; -- 定义行类型 v_empRow emp%ROWTYPE ; -- 定义行类型 BEGIN OPEN cur_var FOR SELECT * FROM dept ; -- 打开游标 LOOP FETCH cur_var INTO v_deptRow ; -- 取得游标数据 EXIT WHEN cur_var%NOTFOUND ; -- 如果没有数据则退出 DBMS_OUTPUT.put_line('1、部门名称：' || v_deptRow.dname || '，部门位置：' || v_deptRow.loc) ; END LOOP ; CLOSE cur_var ; OPEN cur_var FOR SELECT * FROM emp WHERE deptno=10 ; -- 打开游标 LOOP FETCH cur_var INTO v_empRow ; -- 取得游标数据 EXIT WHEN cur_var%NOTFOUND ; -- 如果没有数据则退出 DBMS_OUTPUT.put_line('2、雇员姓名：' || v_empRow.ename || '，雇员职位：' || v_empRow.job) ; END LOOP ; CLOSE cur_dept ; -- 关闭游标 END ; / |
