继续更新
--显示游标处理多行返回数据,隐式游标处理select into 和 dml 语句
--FETCH into
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp WHERE deptno ='s1';
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename||','||v_sal);
END LOOP;
CLOSE emp_cursor;
END;
--FETCH BULK COLLECT INTO (LIMIT)
DECLARE
TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);
name_array name_array_type ;
CURSOR emp_cursor IS SELECT ename FROM emp;
rows INT:=5;
v_count INT:=0;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor BULK COLLECT INTO name_array LIMIT rows;
dbms_output.put_line('雇员名');
FOR i IN 1..(emp_cursor%ROWCOUNT-v_count) LOOP
dbms_output.put(name_array(i)||'');
END LOOP;
dbms_output.new_line;
v_count:=emp_cursor%ROWCOUNT;
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
CLOSE emp_cursor;
END;
--定义记录变量提取数据
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(emp_record.ename||','||emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
--游标定义参数 参数要在WHERE子句中
DECLARE
CURSOR emp_cursor(nos NUMBER(6)) IS
SELECT ename,sal FROM emp WHERE deptno =nos;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor(10);
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename||','||v_sal);
END LOOP;
CLOSE emp_cursor;
END;
--使用游标更新
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp FOR UPDATE;
v_ename emp.ename%TYPE;
v_oldsal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_oldsal;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_oldsal <2000 THEN
UPDATE emp SET sal =sal +1000 WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
CLOSE emp_cursor;
END;
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal,dname FROM emp,dept
WHERE emp.deptno = dept.dno;
FOR UPDATE OF emp.deptno;--OF单个表加共享锁 NOWALT 避免等待锁
v_ename emp.ename%TYPE;
v_oldsal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_oldsal;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_oldsal<2000 THEN
UPDATE emp SET sal = sal+100 WHEN CURRENT OF emp_cursor;
END IF;
END LOOP;
CLOSE emp_cursor;
END;
--游标for循环
BEGIN
FOR emp_record IN
(SELECT ename FROM emp)LOOP
dbms_output.put_line(emp_record.ename);
END loop;
END;
--定义游标变量 return 返回
DECLARE
TYPE emp_record_type IS RECORD(
name VARCHAR2(10),salary NUMBER(6,2));
TYPE emp_cursor_type IS REF CURSOR;
RETURN emp_record_type;
emp_cursor emp_cursor_type;
emp_record emp_record_type;
BEGIN
OPEN emp_cursor FOR SELECT ename,sal FROM emp
WHERE deptno =20;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
CLOSE emp_cursor;
END: