--声明
declare
a int := 5;
begin
select sal into a from emp where ename = 'SCOTT';
dbms_output.put_line(a || '工资');
end;
--if
declare
num int :=5;
begin
if num>4 then
dbms_output.put_line(num||'大于4');
elsif num>3 then
dbms_output.put_line(num||'大于3');
elsif num >2 then
dbms_output.put_line(num||'大于2');
end if ;
end;
--loop
declare
a int := 5;
b int := 1;
begin
loop
b := b * a;
a := a - 1;
exit when a = 1;
end loop;
dbms_output.put_line('5的阶乘是:'||b);
end;
--for
declare
a int := 5;
b int := 1;
c int;
begin
for c in 1 .. a loop
b := b * a;
a := a - 1;
end loop;
dbms_output.put_line('5的阶乘是:' || b);
end;
--while
declare
a int :=5;
b int :=1;
begin
while a>=1 loop
b:=b*a;
a:=a-1;
end loop;
dbms_output.put_line('5的阶乘是'||b);
end;
--存贮过程,procedure
create or replace procedure p1(
a in integer,
b in integer,
c out integer
)
as j integer;
begin c:=0;
for j in a..b loop
c:=c+j;
end loop;
end;
--调用存储过程
declare
c integer;
begin
p1(1, 1000, c);
dbms_output.put_line('c:' || c);
end;
--函数 创建
create or replace function f1(a in emp.empno%type)
return emp.ename%type
as rname emp.ename%type;
begin
select ename into rname from emp
where empno=a;
return rname;
end;
--触发器
create trigger t1
after insert on emp
declare
c int;
begin
select count(*) into c from emp;
dbms_output.put_line('当前员工有'||c||'条数据');
end;
--强制触发(t1)
select * from emp;
insert into emp values (7766,'looo','情节',7756,sysdate,16666,null,20);
--建表
create table lz as select * from emp where 1=2;
--触发器t2
create or replace trigger t2 after delete on emp
for each row
declare
begin
insert into lz values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;
--强制触发
delete from emp where empno=7736;
select * from lz;
--触发器3
-- update trigger
create or replace trigger t3 after update on emp
for each row
declare
begin
dbms_output.put_line('更新前:'||:old.ename);
dbms_output.put_line('更新后:'||:new.ename);
end;
-- 打断触发器
create or replace trigger t4 after delete on dept for each row
declare
n int;
begin
select count(*) into n from emp where deptno=:old.deptno;
if n>0 then
raise_application_error('-20000',:old.dname||'部门有人不能删除');
end if;
end;
--强制触发
delete dept where deptno=10;
declare
a int := 5;
begin
select sal into a from emp where ename = 'SCOTT';
dbms_output.put_line(a || '工资');
end;
--if
declare
num int :=5;
begin
if num>4 then
dbms_output.put_line(num||'大于4');
elsif num>3 then
dbms_output.put_line(num||'大于3');
elsif num >2 then
dbms_output.put_line(num||'大于2');
end if ;
end;
--loop
declare
a int := 5;
b int := 1;
begin
loop
b := b * a;
a := a - 1;
exit when a = 1;
end loop;
dbms_output.put_line('5的阶乘是:'||b);
end;
--for
declare
a int := 5;
b int := 1;
c int;
begin
for c in 1 .. a loop
b := b * a;
a := a - 1;
end loop;
dbms_output.put_line('5的阶乘是:' || b);
end;
--while
declare
a int :=5;
b int :=1;
begin
while a>=1 loop
b:=b*a;
a:=a-1;
end loop;
dbms_output.put_line('5的阶乘是'||b);
end;
--存贮过程,procedure
create or replace procedure p1(
a in integer,
b in integer,
c out integer
)
as j integer;
begin c:=0;
for j in a..b loop
c:=c+j;
end loop;
end;
--调用存储过程
declare
c integer;
begin
p1(1, 1000, c);
dbms_output.put_line('c:' || c);
end;
--函数 创建
create or replace function f1(a in emp.empno%type)
return emp.ename%type
as rname emp.ename%type;
begin
select ename into rname from emp
where empno=a;
return rname;
end;
--触发器
create trigger t1
after insert on emp
declare
c int;
begin
select count(*) into c from emp;
dbms_output.put_line('当前员工有'||c||'条数据');
end;
--强制触发(t1)
select * from emp;
insert into emp values (7766,'looo','情节',7756,sysdate,16666,null,20);
--建表
create table lz as select * from emp where 1=2;
--触发器t2
create or replace trigger t2 after delete on emp
for each row
declare
begin
insert into lz values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;
--强制触发
delete from emp where empno=7736;
select * from lz;
--触发器3
-- update trigger
create or replace trigger t3 after update on emp
for each row
declare
begin
dbms_output.put_line('更新前:'||:old.ename);
dbms_output.put_line('更新后:'||:new.ename);
end;
-- 打断触发器
create or replace trigger t4 after delete on dept for each row
declare
n int;
begin
select count(*) into n from emp where deptno=:old.deptno;
if n>0 then
raise_application_error('-20000',:old.dname||'部门有人不能删除');
end if;
end;
--强制触发
delete dept where deptno=10;