PL/SQL基本操作

1、常规过程化形式

declare
  o_booking_flag char(10);
begin
 
  -- Call the procedure
  destine_ticket('000000037',
                 20,
                 'E',
                 2,
                 o_booking_flag);
                dbms_output.put_line(o_booking_flag);
end;

2、存储过程

create or replace procedure destine_ticket(i_flightId     in char, --不需要些类型值
                                           i_LuggageLimit in number ,
                                           i_class_code in char ,
                                           i_seats in number,
                                           o_booking_flag out char 
                                           ) 
is
  v_temp integer;
  v_temp1 integer;
begin

    begin
       select 1 into v_temp from flight  t1 where t1.flightid=i_flightId and to_number(t1.estdeparturedatetime-sysdate)*24 >= 3;
    exception --异常捕获
    when no_data_found then
         v_temp:=0; --复制要写:=
    end;
　　return; --退出存储过程

end destine_ticket;

异常处理

--一异常处理的代码
--sqlcode 异常编号
--sqlerrm 信号字符串

/*

在plsql 块中格式

Declare
  变量
Begin
    代码块
    
    EXCEPTION
        when 异常的名称  then
            如生上面的异常时做的具体工作。
End;

*/

set serveroutput on;
create or replace procedure pr12
as
--定义一个int变liang
v_age integer;
v_name varchar(30);
begin 
v_age:=89;
--通过select给v_name设置值
--修改成过程
select name into v_name from stud where id=1;
DBMS_OUTPUT.PUT_LINE('没有出错');
exception
 when value_error then 
 SYS.DBMS_OUTPUT.PUT_LINE('数值错误');
 when no_data_found then 
 SYS.DBMS_OUTPUT.PUT_LINE('没有数据');
 when others then
 SYS.DBMS_OUTPUT.PUT_LINE(sqlcode||'你出错了'||sqlerrm);
 end;

exec pr12();
-----------------------------------------
--自定义异常自己抛出异常/
/*
定义一个自己的异常
      myException Exception;
抛出异常
    RAISE myException;
    
    处理自己的异常：
        Exception 
            When myException then
                ....
*/
set serveroutput on;
declare
myEx exception;
begin
DBMS_OUTPUT.PUT_LINE('这里没错');
raise myEx;
DBMS_OUTPUT.PUT_LINE('不会输出，前面抛出异常');
--处理异常
exception
when myEx then
DBMS_OUTPUT.PUT_LINE('自己的异常'||sqlcode||'  '||sqlerrm);
when others then 
DBMS_OUTPUT.PUT_LINE('不知知道什么错误'||sqlcode||sqlerrm);
END;
---出错直接抛出

declare
begin
DBMS_OUTPUT.PUT_LINE('no errors');
--直接抛出
RAISE_APPLICATION_ERROR(-20000, 'A');
DBMS_OUTPUT.PUT_LINE('go okk....');
exception
   when others then
DBMS_OUTPUT.PUT_LINE(sqlcode||'  '||sqlerrm);
end;

 

 3、过程调用

declare
  o_booking_flag char(10);
begin
 
  -- Call the procedure
  destine_ticket(i_flightid =>'000000037',
                 i_luggagelimit =>20,
                 i_class_code =>'E',
                 i_seats =>2,
                 o_booking_flag =>o_booking_flag);
end;

 

4、触发器

注意，一般删除、更新等触发器，不能调用触发器表本身

create or replace trigger flight_staff_check
  before insert or update or delete on flight  
  for each row
declare
  -- local variables here
  cap_id char(100);
  fir_id char(100);
    flag integer;
begin
  if inserting then
     --select :new.captainstaffid into cap_id,:new.firstofficerstaffid into fir_id from dual;
     cap_id:=:new.captainstaffid;
     fir_id:=:new.firstofficerstaffid;
  end if;
  
  if updating then
     --select :new.captainstaffid into cap_id,:new.firstofficerstaffid into fir_id from dual;
     cap_id:=:new.captainstaffid;
     fir_id:=:new.firstofficerstaffid;
  end if;
  if deleting then
     --select :old.captainstaffid into cap_id,:old.firstofficerstaffid into fir_id from dual;
     cap_id:=:old.captainstaffid;
     fir_id:=:old.firstofficerstaffid;
  end if;

end