SELECT empno,ename,job,sal from emp
/*******
FOR 游标
*******/
DECLARE
--定义游标
CURSOR C_MAN IS
SELECT * FROM EMP WHERE JOB = 'MANAGER';
--定义游标的行(在FOR语句中,这个可以不用定义)
-- C_ROW C_MAN%ROWTYPE;
BEGIN
--循环游标查询 行.字段 (访问)
FOR C_ROW IN C_MAN LOOP
DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO);
--exit when C_MAN % NOTFOUND = FALSE;
END LOOP;
END;
/*******
FOR 游标 更新当前行
where ... UPDATE OF 字段
update ...where ..FOR CURRENT 游标名
*******/
DECLARE
CURSOR c_table IS SELECT * FROM emp WHERE UPPER(Job)='MANAGER' FOR UPDATE OF sal;
BEGIN
FOR c_row IN c_table LOOP
IF c_row.empno=7566 THEN
UPDATE emp SET sal=sal-0.1 WHERE CURRENT OF c_table;
END IF;
END LOOP;
COMMIT;
END;
/*******
FOR 游标 简洁
*******/
BEGIN
FOR c_table IN(SELECT * FROM emp) LOOP
IF c_table.empno=7566 THEN
dbms_output.put_line(c_table.sal);
END IF;
END LOOP;
END;
/*******
FETCH 游标 读取行
*******/
DECLARE
CURSOR c_table IS
SELECT * FROM emp WHERE UPPER(job)='MANAGER';
c_row c_table%ROWTYPE;
BEGIN
--打开游标
OPEN c_table;
--循环体
LOOP
--读取一行
FETCH c_table INTO c_row;
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
--退出条件
EXIT WHEN c_table%NOTFOUND;
END LOOP;
--关闭游标
CLOSE c_table;
END;
/*******
FETCH 游标 读取列
*******/
DECLARE
v_job emp.job%TYPE;
v_sal emp.sal%TYPE;
CURSOR c_table IS SELECT job,sal FROM emp WHERE empno IN(7369,7499) ;
BEGIN
OPEN c_table;
LOOP
FETCH c_table INTO v_job,v_sal;
EXIT WHEN c_table%NOTFOUND;
dbms_output.put_line(v_job||to_char(v_sal));
END LOOP;
CLOSE c_table;
--COMMIT;
END;
游标的几种写法
最新推荐文章于 2024-09-24 11:22:08 发布