利用FOR循环统计并输出各个部门的平均工资
DECLARE
CURSOR c_1 IS SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;
v_dept c_1%rowtype;
begin
FOR v_dept IN c_1
loop
dbms_output.put_line(v_dept.deptno||' '||v_dept.avgsal);
end loop;
end;
简化1:
DECLARE
CURSOR c_1 IS SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;
begin
FOR v_dept IN c_1
loop
dbms_output.put_line(v_dept.deptno||' '||v_dept.avgsal);
end loop;
end;
简化2:
DECLARE
begin
FOR v_dept IN (SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno)
loop
dbms_output.put_line(v_dept.deptno||' '||v_dept.avgsal);
end loop;
end;
带UPDATE的游标
DECLARE
v_empno scott.emp.empno%type;
v_sal scott.emp.sal%type;
CURSOR c_cursor IS SELECT empno,sal FROM scott.emp;
BEGIN
OPEN c_cursor;
LOOP
FETCH c_cursor INTO v_empno,v_sal;
EXIT WHEN c_cursor%NOTFOUND;
IF v_sal<=1200 THEN
UPDATE scott.emp SET sal = sal + 50 WHERE empno = v_empno;
DBMS_OUTPUT.put_line('编码为'||v_empno||'工资已更新');
END IF;
dbms_output.put_line('记录数:'||c_cursor%rowcount);
END LOOP;
CLOSE c_cursor;
END;
DECLARE
v_empno scott.emp.empno%type;
v_sal scott.emp.sal%type;
CURSOR c_cursor IS SELECT empno,sal FROM scott.emp WHERE sal<1200 FOR UPDATE;
BEGIN
OPEN c_cursor;
LOOP
FETCH c_cursor INTO v_empno,v_sal;
EXIT WHEN c_cursor%NOTFOUND;
UPDATE scott.emp SET sal = sal + 50 WHERE current of c_cursor;
dbms_output.put_line('编码为:'||v_empno||'工资已更新');
dbms_output.put_line('记录数:'||c_cursor%rowcount);
END LOOP;
CLOSE c_cursor;
END;
带FOR UPDATE的游标
DECLARE
cursor c_emp IS SELECT * FROM scott.emp FOR UPDATE;
v_z1 NUMBER;
v_emp c_emp%rowtype;
BEGIN
FOR v_emp IN c_emp LOOP
CASE v_emp.deptno
WHEN 10 THEN v_z1:=100;
WHEN 20 THEN v_z1:=150;
WHEN 30 THEN v_z1:=200;
ELSE v_z1:=200;
END CASE;
UPDATE scott.emp SET sal = sal + v_z1 WHERE CURRENT OF c_emp;
END LOOP;
END;
用于更新的游标
DECLARE
cursor c_1 IS SELECT empno,sal FROM scott.emp FOR UPDATE OF sal nowait;
v_sal scott.emp.sal%type;
BEGIN
FOR cursor_1 IN C_1
LOOP
IF cursor_1.sal<=1000 THEN
v_sal:=1500;
ELSE
v_sal:=cursor_1.sal*1.5;
IF v_sal > 10000 THEN
v_sal:=10000;
END IF;
END IF;
UPDATE scott.emp SET sal = v_sal WHERE CURRENT OF c_1;
END LOOP;
END;
游标变量示例
DECLARE
TYPE t_dept IS REF cursor RETURN scott.emp%rowtype;
c_1 t_dept;
v_row scott.emp%rowtype;
BEGIN
OPEN c_1 FOR SELECT * FROM scott.emp WHERE deptno = 10;
FETCH c_1 INTO v_row;
dbms_output.put_line(v_row.empno||' '||v_row.job);
CLOSE c_1;
OPEN c_1 FOR SELECT * FROM scott.emp WHERE sal>=2000;
FETCH c_1 INTO v_row;
dbms_output.put_line(v_row.empno||' '||v_row.job);
CLOSE c_1;
END;
Oracle游标的简单应用
最新推荐文章于 2024-01-15 08:00:00 发布