游标对表数据的增,删,改及引用游标
来源:互联网 发布:经济类推荐书目知乎 编辑:程序博客网 时间:2024/04/30 13:00
declare
cursor emp_cursor is select ename,salary from emp for update;
v_ename emp.ename%type;
v_salary emp.salary%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_salary;
exit when emp_cursor%notfound;
if v_salary < 1000 then
update emp set salary = 1000 where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
declare
cursor emp_cursor is select deptno,ename,salary from emp for update;
v_deptno emp.deptno%type;
v_ename emp.ename%type;
v_salary emp.salary%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_deptno,v_ename,v_salary;
exit when emp_cursor%notfound;
if v_deptno = 3 then
delete from emp;
end if;
end loop;
close emp_cursor;
end;
declare
cursor emp_cursor is select ename,salary,dname,emp.deptno from emp,dept
where emp.deptno = dept.deptno for update of emp.deptno;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.deptno = 30 then
update emp set salary = salary + 100 where current of emp_cursor;
end if;
end loop;
close emp_record;
end;
declare
cursor emp_cursor is select ename,salary from emp for update nowait;
v_ename emp.ename%type;
v_salary emp.salary%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_salary;
exit when emp_cursor%notfound;
if v_salary < 1000 then
update emp set salary = salary * 1.1 where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
declare
cursor emp_cursor is select ename,salary from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
begin
for emp_record in (select ename,salary from emp) loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
begin
open emp_cursor for select * from emp where empno = '2';
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_record.ename);
end loop;
close emp_cursor;
end;
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,salary from emp where empno='2';
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_record.name);
end loop;
close emp_cursor;
end;
declare
type refcursor is ref cursor;
cursor dept_cursor(no number) is
select a.dname,cursor(select ename,salary from emp where deptno = a.deptno)
from dept a where a.deptno = no;
empcursor ref cursor;
v_dname dept.dname%type;
v_ename dept.ename%type;
v_sal emp.salary%type;
begin
open dept_cursor(&no);
loop
fetch dept_cursor into v_dname,empcursor;
exit when dept_cursor%notfound;
dbms_output.put_line(v_dname);
loop
fetch empcursor into v_ename,v_sal;
exit when empcursor%notfound;
dbms_output.put_line(v_ename||','||v_sal);
end loop;
end loop
close dept_cursor;
end;
- 游标对表数据的增,删,改及引用游标
- 引用游标
- 游标的使用--从游标提取数据
- 游标使用2-常用属性及引用游标
- ORACLE的引用游标SYS_REFCURSOR
- ORACLE的引用游标SYS_REFCURSOR
- oracle存储过程详解--游标 实现增、删、改、查的功能
- oracle存储过程详解--游标 实现增、删、改、查的(2)
- oracle存储过程详解--游标 实现增、删、改、查的(1)
- 对300万一张表数据,用游标进行循环,不同写法的效率比较
- 对300万一张表数据,用游标进行循环,不同写法的效率比较
- 对300万一张表数据,用游标进行循环,不同写法的效率比较
- 游标对应的表的数据删除时,如何保持游标与表数据的一致性
- 父游标、子游标及共享游标
- 父游标、子游标及共享游标
- 游标的概念及使用
- 游标的原理及使用方法
- 表的游标实现
- 穿过黑暗、走出痛苦、告别孤独
- 比较好的国外WEB代理地址
- localhost无法访问,但127.0.0.1可以访问 解决办法
- WinCE开发How to大集合之在Bootloader, WinCE,应用程序间传递参数的方法
- 直接通过ADO操作Access数据库
- 游标对表数据的增,删,改及引用游标
- WinCE开发How to大集合之详解MSDN上推荐的分步加载流驱动实现启动提速
- Excel表格操作
- Linux的tail命令
- Gate A20与保护模式
- magento -- Magento1.4强大的新功能
- 对于“shellcode”和“exploit”的解释
- 123456789四则运算
- 转:VC学习