open c; loop
fetch c into v_emp;
--下面两行顺序改变后，将会把最后一条记录打印两遍 dbms_output.put_line(v_emp.ename); exit when(c%notfound);
end loop; end; --while循环 declare cursor c is select * from emp; v_emp emp%rowtype; begin open c;
fetch c into v_emp; while(c%found) loop
dbms_output.put_line(v_emp.ename); fetch c into v_emp; end loop; close c; end; --for循环 declare cursor c is select * from emp; begin open c;
for v_emp in c loop
dbms_output.put_line(v_emp.ename); end loop; close c; end;
--带参数的游标 declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is select ename,sal from emp where deptno=v_deptno and job=v_job; --v_emp emp%rowtype; begin
for v_emp in c(30,'JAY') loop
dbms_output.put_line(v_emp.ename); end loop; end;
--可更新的游标 declare cursor c is
select * from emp for update; --v_temp c%rowtype; begin
for v_temp in c loop if(v_temp.sal < 2000) then
update emp set sal=sal*2 where current of c; elsif(v_temp.sal=5000) then delete from emp where current of c; end if; end loop; commit; end;
第49～50课：procedure create or replace procedure p is cursor c is
select * from emp for update; begin
for v_temp in c loop if(v_temp.deptno=10) then
update emp set sal=sal+10 where current of c; elsif(v_temp.deptno=20) then
update emp set sal=sal+20 where current of c; else
update emp set sal=sal+50 where current of c; end if; end loop; commit; end;
--带参数的存储过程
create or replace procedure p
(v_a in number,v_b number,v_ret out number,v_temp in out number) is begin
if(v_a v_temp:=v_temp+1; end; declare v_a number:=10; v_b number:=20; v_ret number; v_temp number:=99; begin p(v_a,v_b,v_ret,v_temp); dbms_output.put_line(v_ret); dbms_output.put_line(v_temp); end; /* 存储过程在创建过程中如果出现错误，仍然会创建，并且不会提示错误在哪儿，可以使用 show error来查看错误出现在哪儿。 */ create or replace function tax_sal (v_sal number) return number is begin if(v_sal<2000) then return 0.10; elsif(v_sal<3000) then return 0.20; else return 0.30; end if; end; --触发器 create table emp_log ( uname varchar2(20); action varchar2(10); atime date ); create or replace trigger trig after/before insert or update or delete on emp2 for each row begin if inserting then insert into emp_log values(USER,'insert',sysdate); elsif updating then insert into emp_log values(USER,'update',sysdate); else insert into emp_log values(USER,'delete',sysdate); end if; end; update dept set deptno=99 where deptno=10; --违反约束条件 create or replace trigger trig after update on dept for each row begin update emp set deptno=:NEW.deptno where deptno=:OLD.deptno; end;