J := J + 1;
end loop;
end;
begin
for k in 1..10 loop
dbms_output.putline(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
92.PL_SQL语句 异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
no_data_found 没找到数据
93.PL_SQL语句 游标（指针） cursor
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c; --开始执行select语句
fetch c into v_emp; --fetch提取游标数据
dbms_output.put_line(v_emp.eename);
close c;
end;
循环
loop
fetch c into v_emp;
exit when (c%notfound);
........;
end loop;
for循环
declare
cusor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
94.PL_SQL语句 带参数的游标
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno = v_deprno and job = v_job;
--v_temp c%rowtype;
begin
for v_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop
end;
95.PL_SQL语句 可更新的游标
declare
cursor c
is
select * from emp2 for update;
--v_temp c%type;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c; --current当前的
elsif(v_temp.sal = 5000) then
delect from emp2 where current of c;
end if;
end loop;
commit;
end;
96.存储过程procedure
创建存储过程
create or replace procedure p
is
cursor c
is
select * from emp2 for update;
--v_temp c%type;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c; --current当前的
elsif(v_temp.sal = 5000) then
delect from emp2 where current of c;
end if;
end loop;
commit;
end;
执行此存储过程
exec p;
97.带参数的存储过程 默认in
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_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;
调用
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.outline(v_ret);
dbms_output.putline(v_temp);
end; 答案 4，6
98.函数 调用方式和系统函数调用方式一样
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else if;
end;
99.触发器 必须在表上，在什么时间，等 for each row 每处理一行触发一次。删除触发器 drop trigger trig;
create or replace trigger trig
after insert or delete or update on emp2 for each row
begin
if inserting then
insert into emp2_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
end if;
end;
100. update 执行会有前后两个状态 NEW, OLD
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno =: NEW.deptno where deptno =: OLD.deptno;
end;