oracle三大范式及plsql例题与应用

-- 声明变量
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;