Oracle PLSQL语法

--声明变量
declare  
 nn varchar2(20):='张三';
begin
--将查询出的数据放入到变量中
  select ename into nn from emp where empno=7788;
-- 打印输出语句
  dbms_output.put_line('你好：'||nn);
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;


 declare
   a int := 10;
   b int := 1;
 begin
   <<abc>>
   b := b * a;
   a := a - 1;
 if a>=1 then
   goto abc;
   end if;
   a:=10;
 dbms_output.put_line(a || '的阶乘是:' || b);
 end;
 --乘法口诀表
 declare
   a int;
   b int;
   c int;
 begin
   for a in 1 .. 9 loop
     for b in 1 .. a loop
       c := a * b;
       dbms_output.put(b||'*'||a||'='||c||' ');
     end loop;
     dbms_output.new_line;
   end loop;
 end;
--procedure 存储过程
create  procedure p1(a in int, b in int,c out int)as
 j int;
 begin
   c:=0;
   for j in a..b loop
    c:=c+j;
   end loop;
   end;
--调用存储过程
declare
 c int;
begin
  p1(1,100,c);
  dbms_output.put_line(c);
  end;
--编写计算税后工资
create or replace 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;
  --触发器
  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(1,'a','CLERK',null,sysdate,100,null,10);
 --创建离职表
 create table lz
 as select * from emp where 1=2;
 --行级别
 create 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=1;
 select * from lz;
 --update trigger
 create 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;