select * from dept;
--显式游标
declare
loc dept.loc%type;
dname dept.dname%type;
cursor dept_cursor is
select d.dname, d.loc from dept d;
begin
open dept_cursor; --开启游标
loop
fetch dept_cursor
into dname, loc;
exit when dept_cursor%Notfound;
dbms_output.put_line('第' || dept_cursor%rowcount || '条数据,部门名称:' ||
dname || ',部门地点:' || loc);
end loop;
close dept_cursor;
end;
--
select * from emp;
declare
cursor emp_cursor is
select ename, sal from emp where deptno = 20;
emp_record emp_cursor%rowtype;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor
into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line('第' || emp_cursor%rowcount || '条数据,员工姓名:' ||
emp_record.ename || '工资:' || emp_record.sal);
end loop;
close emp_cursor;
end;
--for显示游标
declare
--下面声明的这个类型可以不用声明,直接使用;
/*type dept_c is record(
loc dept.loc%type;
dname dept.dname%type;
);*/
cursor dept_cursor is
select d.dname, d.loc from dept d;
begin
for dept_c in dept_cursor loop
dbms_output.put_line('第' || dept_cursor%rowcount || '条数据,部门名称:' ||
dept_c.dname || ',部门地点:' || dept_c.loc);
end loop;
end;
--带参数的游标
declare
v_no number := &v_no; --动态赋值
cursor emp_cursor(no number) is
select ename, sal from emp where deptno = no;
emp_record emp_cursor%rowtype;
-- v_emp emp%rowtype;
-- v_sal emp.sal%type;
begin
if not emp_cursor%isopen then
--可以省略这句,直接打开
open emp_cursor(v_no);
end if;
loop
fetch emp_cursor
into emp_record;
exit when emp_cursor %notfound;
dbms_output.put_line('第' || emp_cursor%rowcount || '条数据,员工姓名:' ||
emp_record.ename || '工资:' || emp_record.sal);
end loop;
close emp_cursor;
end;
--显示游标更新数据
--单表
select * from emp;
declare
cursor emp_cursor is
select ename, sal, deptno from emp for update; --如果删除表数据,也是用update
emp_record emp_cursor%rowtype;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor
into emp_record;
if emp_record.deptno = 30 and emp_record.sal > 1500 then
--delete from emp where current of emp_cursor;
update emp set sal = sal + 500 where current of emp_cursor; --注意这一点current of emp_cursor
end if;
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
--多表
--
select * from emp;
select * from dept;
declare
cursor emp_cursor is
select ename, sal, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno
for update of emp.sal, dept.dname;
emp_record emp_cursor%rowtype;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor
into emp_record;
exit when emp_cursor%notfound;
if emp_record.deptno = 30 then
update emp set sal = sal + 1000 where current of emp_cursor;
dbms_output.put_line(emp_record.sal);
update dept set dname = 'name' where current of emp_cursor;
dbms_output.put_line(emp_record.dname);
end if;
end loop;
close emp_cursor;
end;
--REF游标
--查询emp表的部门编号为20的员工信息,之后查询dept表中的部门编号为10的部门信息,打印输出;
select * from emp;
select * from dept;
DECLARE
TYPE C_TYPECURSOR is REF CURSOR;
C_CURSOR C_TYPECURSOR;
v_emp emp%rowtype;
v_dept dept%rowtype;
BEGIN
open C_CURSOR for
select * from emp where deptno = 20;
loop
fetch C_CURSOR
into v_emp;
exit when C_CURSOR%notfound;
dbms_output.put_line('第'||c_cursor%rowcount||'个员工信息:'||v_emp.ename||','||v_emp.job);
end loop;
--游标可以不用关闭,继续使用,相当于变量再次赋值
open C_CURSOR for
select * from dept where deptno = 10;
loop
fetch C_CURSOR
into v_dept;
exit when C_CURSOR%notfound;
dbms_output.put_line('部门编号:'||v_dept.deptno||'部门名称:'||v_dept.dname||'部门地址:'||v_dept.loc);
end loop;
close c_cursor;
END;
--动态SQL语句
--使用本地动态SQL实现:
--修改雇员编号为7788的福利补助(comm)列为1000,查询工种为'CLERK'的所有雇员信息
select * from emp;
select * from dept;
DECLARE
sql_str varchar2(200);
sql_str1 varchar2(200);
BEGIN
sql_str:='update emp set comm=1100 where empno=:empno';
execute immediate sql_str using 7788;
COMMIT;
sql_str1:='update emp set comm=1300 where empno=:empno';
execute immediate sql_str1 using 7782;
commit;
END;
----事务处理
select * from emp;
select * from dept;
insert into dept values(50,'mm','dd');
insert into dept values(60,'MM','cc');
savepoint a;
insert into dept values(70,'AA','cc');
rollback to savepoint a;--回滚到存储点a