Oracle PL/SQL 学习笔记01

第一章 PL/SQL介绍

 

1.为什么使用PL/SQL

 

PL/SQL和SQL结合紧密，二者使用相同的数据类型。PL/SQL使用更少的代码并具有更快的执行速度

PL/SQL简化了应用程序的逻辑到数据库层的转移，可以增加代码重用(code reusable)和减少网络通信(network traffic)，同时由于代码采用原声编译(natively compiled)，从而获得了更快的执行速度

PL/SQL可以轻松的实现跨平台(由于Oracle数据库可以跨平台)

 

2.PL/SQL不适用的场景

 

当代码需要在不同的数据库系统运行，如使用DB2，Sybase等数据库产品，PL/SQL显然不是一个很好的选择(Java is better)

当终端用户需要直接调用接口时，尽量避免使用PL/SQL

 

3.PL/SQL的基本结构

 

PL/SQL是块结构的语言

 

一个典型的PL/SQL块应该包含一个声明部分，一个可执行部分和一个异常处理部分

 

声明部分是可选的，可执行部分必须介于Begin语句和End语句之间，以英文半角分号结束，异常处理部分也是可选的，但强烈建议使用异常处理

 

declare
v_rate NUMBER;
v_threshould NUMBER := 100.00;
v_increase_flag BOOLEAN := false;
begin
select rate into v_rate from rate_table
where rate_type = 'Monthly'
and effective_date = trunc(sysdate);

if(v_rate < v_threshould) then
v_increase_flag := true;
end if;

if(v_inscrease_flag) then
update rate_table set rate := rate + 2.5 * rate
where rate_type = 'Monthly'
and effective_date = trunc(sysdate);
commit;
end if;
exception when no_data_found then
dbms_output.put_line('Rate is not available');
when others then
dbms_output.put_line('Server error');
dbms_output.put_line(sqlerrm);
end;
/

declare
     v_rate    NUMBER;
     v_threshould    NUMBER := 100.00;
     v_increase_flag    BOOLEAN := false;
begin
     select rate into v_rate from rate_table 
         where rate_type = 'Monthly' 
              and effective_date = trunc(sysdate);
     
     if(v_rate < v_threshould) then 
        v_increase_flag := true;
     end if;
     
     if(v_inscrease_flag) then
        update rate_table set rate := rate + 2.5 * rate
            where rate_type = 'Monthly' 
                 and effective_date = trunc(sysdate);
         commit;      
     end if;
exception when no_data_found then
    dbms_output.put_line('Rate is not available');
    when others then
         dbms_output.put_line('Server error');
         dbms_output.put_line(sqlerrm);
end;
/

 

上面为一个PL/SQL最基本的程序结构，这个PL/SQL块同时可以嵌入到存储过程中

 

create or replcae procedure p_process_rate(
ip_rate_type in rate_table.rate_type%type,
ip_effective_date in rate_table.effective_date%type,
ip_threshould in number,
op_rate out number,
op_success out varchar2,
op_errmsg out varchar2)
is
v_rate number;
v_increase_flag boolean := false;
begin
select rate into v_rate from rate_table
where rate_type = ip_rate_type
and effective_date = ip_effective_date;

if(v_rate < ip_threshould) then
v_increase_flag := true;
end if;

if(v_increase_flag) then
update rate_table set rate := rate + 2.5 * rate
where rate_type = ip_rate_type
and effective_date = ip_effective_date
returning rate into v_rate;
commit;
end if;

op_rate := v_rate;
op_success := 'Y';

exception when no_data_found then
op_rate := null;
op_success := 'N';
op_errmsg := 'Rate is not availabe ';
when others then
op_rate := null;
op_success := 'N';
op_errmsg := 'Server error' || chr(10) || chr(13) || SQLERRM;
end;
/

create or replcae procedure p_process_rate(
   ip_rate_type         in     rate_table.rate_type%type,
   ip_effective_date  in     rate_table.effective_date%type,
   ip_threshould        in     number,
   op_rate                 out   number,
   op_success           out   varchar2,
   op_errmsg            out   varchar2)
is
   v_rate number;
   v_increase_flag boolean := false;
begin
    select rate into v_rate from rate_table
        where rate_type = ip_rate_type
             and effective_date = ip_effective_date;

    if(v_rate < ip_threshould) then
       v_increase_flag := true;
    end if;

    if(v_increase_flag) then
       update rate_table set rate := rate + 2.5 * rate
           where rate_type = ip_rate_type
                and effective_date = ip_effective_date
       returning rate into v_rate;
       commit;
    end if;

    op_rate := v_rate;
    op_success := 'Y';

exception when no_data_found then
    op_rate := null;
    op_success := 'N';
    op_errmsg := 'Rate is not availabe ';
    when others then
         op_rate := null;
         op_success := 'N';
         op_errmsg := 'Server error' || chr(10) || chr(13) || SQLERRM;
end;
/

 

其中in表示输入参数，out表示输出参数

 

 

declare
v_rate number;
v_success_flag varchar2(1);
v_errmsg varchar2(1000);
begin
p_process_rate('Monthly',trunc(sysdate), 100.00,
v_rate, v_success_flag, v_errmsg);

if(v_success_flag != 'Y') then
dbms_output.put_line('Procedure p_process_rate failed with error(s) as follows');
dbms_output.put_line(v_errmsg);
end if;
end;
/

declare
    v_rate number;
    v_success_flag varchar2(1);
    v_errmsg varchar2(1000);
begin
    p_process_rate('Monthly',trunc(sysdate), 100.00,        
                               v_rate, v_success_flag, v_errmsg);

    if(v_success_flag != 'Y') then
       dbms_output.put_line('Procedure p_process_rate failed with error(s) as follows');
    dbms_output.put_line(v_errmsg);
    end if;
end;
/

 

上述代码调用了存储过程p_process_rate