plsql中循环的应用
plsql中循环的应用
begin
-- 两个单引号代替一个双引号
execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';
end;
drop table T;
PLSQL中的判断分支和循环
eg:取出7369这个人的薪水，如果<1200,则输出‘LOW’如果<2000则输出“middle”否则‘high’
7839
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
if(v_sal<1200) then
dbms_output.put_line('low');
elsif(v_sal<2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
循环：（do while()）
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when(i>=11);
end loop; --相当于do while()循环
end;
while()循环
declare
j binary_integer := 1;
begin
while j< 11
loop
dbms_output.put_line(j);
j := j+1;
end loop;
end;
for（）循环
begin
for k in 1..10
loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
错误处理：Exception
出现太多行：
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;
找不到数据的异常：
declare
v_temp number(3);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
dba经常记录错误的作法：
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
因为错误自动递增，创建一个sequence，保存数据
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg,sysdate);
commit;
end;
select * from errorlog;
具体的出错时间
select to_char(errdate,'YYYY-MM-DD HH24:MI:SS' ) from errorlog;
declare
cursor c
is
select * from emp2 for update;
--v_temp c%rowtype;
begin
for v_temp in c
loop
if(v_temp.sal< 2000) then
update emp2 set sal=sal*2 where current of c ;
elsif(v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
