PL/SQL里面执行DDL语句 begin execute immediate 'create table T(nnn varchar2(20) default ''aaa'')'; end; PL/SQL的分支语句: 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;
pL/Sql循环 declare i binary_integer:=1;
begin loop dbms_output.put_line(i);
i:=i+1; exit when(i>=11);
end loop;
end;
PL/SQL 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(4);
begin select empno into v_temp from emp where deptno=2222; exception when no_data_found then dbms_output.put_line('没数据');
when others then dbms_output.put_line('error');
end; / 错误处理 create table errorlog ( id number primary key, errcode number, errmsg varchar2(1024), errdate date ); 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; exception when others then rollback; v_errcode:=SQLCODE; v_errmsg:=SQLERRN; insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
commit;
end;
游标 declare cursor c is select * from emp;
v_emp c%rowtype;
begin open c; fetch c into v_emp; --(取游标的第一个值插入v_emp,在不断的循环) dbms_output.put_line(v_emp.ename); close c; end; 例子 declare cursor c is select * from emp;
v_emp c%rowtype;
begin open c;
loop fetch c into v_emp;
exit when(c%notfound); dbms_output.put_line(v_emp.ename);
end loop;
close c;
end; declare cursor c is select * from emp;
v_emp c%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; declare cursor c is select * from emp; begin for v_emp in c loop dbms_output.put_line(v_emp.ename);
end loop;
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;
begin for v_temp in c(30,'chick') loop dbms_output.put_line(v_temp.ename);
end loop;
end; 可更新的游标
exec p执行存储过程 begin
p:
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_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.put_line(v_ret);
dbms_output.put_line(v_temp);
end; show error返回错误信息 删除存储过程 存储过程中的函数 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.5;
else return 0.20;
end if;
end; 触发器 create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
end; /