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;
/
　　
　　上面为一个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;
/
　　
　　其中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;
/
　　
　　上述代码调用了存储过程p_process_rate