-- 声明变量
declare
na varchar2(20):='张三';
begin
-- 将查询出的数据放入到变量里面
select ename into na from emp where empno=7788;
-- 打印输出语句
dbms_output.put_line('你好:'||na);
end;
-- if语句
declare
score number := 55;
begin
if score >= 90 then
dbms_output.put_line('很优秀');
elsif score >= 70 then
dbms_output.put_line('良好');
elsif score >= 60 then
dbms_output.put_line('及格');
else
dbms_output.put_line('不及格');
end if;
end;
declare
sal number;
begin
select sal into sal from emp where ename='SCOTT';
if sal >= 3000 then
dbms_output.put_line('神豪');
elsif sal >= 2000 then
dbms_output.put_line('土豪');
elsif sal >= 1000 then
dbms_output.put_line('还可以');
else
dbms_output.put_line('帝豪');
end if;
end;
-- case
declare
s varchar2(10) := 'A';
r varchar2(20);
begin
r := case s
when 'A' then
'优秀'
when 'B' then
'良好'
when 'C' then
'及格'
when 'D' then
'不及格'
else
'找不到'
end;
dbms_output.put_line(r);
end;
-- loop
declare
a int := 10;
t int := 1;
begin
loop
t := t * a;
a := a - 1;
exit when a = 1;
end loop;
a:=10;
dbms_output.put_line(a || '的阶乘是:' || t);
end;
-- for
declare
a int := 10;
t int := 1;
j int;
begin
for j in 1 .. a loop
t := t * j;
end loop;
dbms_output.put_line(a || '的阶乘是:' || t);
end;
-- while
declare
a int := 10;
t int := 1;
begin
while a>=1 loop
t:=t*a;
a:=a-1;
end loop;
a:=10;
dbms_output.put_line(a || '的阶乘是:' || t);
end;
-- goto
declare
a int := 10;
t int := 1;
begin
<<abc>>
t:=t*a;
a:=a-1;
if a>=1 then
goto abc;
end if;
dbms_output.put_line(a || '的阶乘是:' || t);
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 procedure p2(a in out int)
as
begin
if a <= 3500 then
dbms_output.put_line('不用交税');
elsif a <= 5000 then
a := a - (a - 3500) * 3 / 100;
elsif a <= 8000 then
a := a - (a - 5000) * 10 / 100 - 105;
elsif a <= 12500 then
a := a - (a - 8000) * 20 / 100 - 555;
end if;
end;
declare
a int := 8888;
begin
p2(a);
dbms_output.put_line(a);
end;
-- function
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;
declare
rn emp.ename%type;
begin
rn:=f1(7788);
dbms_output.put_line(rn);
end;
-- trigger
create trigger t1
after insert on emp
declare
c int;
begin
select count(*) into c from emp;
dbms_output.put_line('当前员工表中有' || c || '条数据');
end;
select * from emp
insert into emp values(999,'a','b',7902,sysdate,8,null,10);
create table lz
as select * from emp where 1=2;
-- 行级别
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=999;
select * from lz;
-- 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;
update emp set ename='QQ' where ename='a';
-- 打断触发器
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;
oracle数据库if 循环 存储过程 函数 触发器
最新推荐文章于 2023-12-05 18:45:57 发布