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语句之间，以英文半角分号结束，异常处理部分也是可选的，但强烈建议使用异常处理

 

Sql代码

1. declare
2. v_rate NUMBER;
3. v_threshould NUMBER := 100.00;
4. v_increase_flag BOOLEAN := false;
5. begin
6. select rate into v_rate from rate_table
7. where rate_type = 'Monthly'
8. and effective_date = trunc(sysdate);
10. if(v_rate < v_threshould) then
11. v_increase_flag := true;
12. end if;
14. if(v_inscrease_flag) then
15. update rate_table set rate := rate + 2.5 * rate
16. where rate_type = 'Monthly'
17. and effective_date = trunc(sysdate);
18. commit;
19. end if;
20. exception when no_data_found then
21. dbms_output.put_line('Rate is not available');
22. when others then
23. dbms_output.put_line('Server error');
24. dbms_output.put_line(sqlerrm);
25. end;
26. /

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块同时可以嵌入到存储过程中

 

Sql代码

1. create or replcae procedure p_process_rate(
2. ip_rate_type in rate_table.rate_type%type,
3. ip_effective_date in rate_table.effective_date%type,
4. ip_threshould in number,
5. op_rate out number,
6. op_success out varchar2,
7. op_errmsg out varchar2)
8. is
9. v_rate number;
10. v_increase_flag boolean := false;
11. begin
12. select rate into v_rate from rate_table
13. where rate_type = ip_rate_type
14. and effective_date = ip_effective_date;
16. if(v_rate < ip_threshould) then
17. v_increase_flag := true;
18. end if;
20. if(v_increase_flag) then
21. update rate_table set rate := rate + 2.5 * rate
22. where rate_type = ip_rate_type
23. and effective_date = ip_effective_date
24. returning rate into v_rate;
25. commit;
26. end if;
28. op_rate := v_rate;
29. op_success := 'Y';
31. exception when no_data_found then
32. op_rate := null;
33. op_success := 'N';
34. op_errmsg := 'Rate is not availabe ';
35. when others then
36. op_rate := null;
37. op_success := 'N';
38. op_errmsg := 'Server error' || chr(10) || chr(13) || SQLERRM;
39. end;
40. /

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表示输出参数

 

 

Sql代码

1. declare
2. v_rate number;
3. v_success_flag varchar2(1);
4. v_errmsg varchar2(1000);
5. begin
6. p_process_rate('Monthly',trunc(sysdate), 100.00,
7. v_rate, v_success_flag, v_errmsg);
9. if(v_success_flag != 'Y') then
10. dbms_output.put_line('Procedure p_process_rate failed with error(s) as follows');
11. dbms_output.put_line(v_errmsg);
12. end if;
13. end;
14. /

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