【有奖讨论】PL/SQL编程要点和注意点

llover

5
Tom Keyt说过：
You should do it in a single SQL statement if at all possible.
If you cannot do it in a single SQL Statement, then do it in PL/SQL.
If you cannot do it in PL/SQL, try a Java Stored Procedure.
If you cannot do it in Java, do it in a C external procedure.
If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…
当然，这世界上没有绝对正确的真理，我在工作经常遇到一个简单的SQL Statement可以做却最后使用了PLSQL。一个简单例子，一个有上10亿records的table中某一个column，如果是NULL，update到‘A’，这个update statement在我们公司的系统里必死（golden gate, rollback......）。
cursor本身没有问题，更不是洪水猛兽。每一个SQL都会用到cursor，cursor是无法避免的。我们所要避免的是用cursor一个一个的loop，我一般都用以下这个模式：
   open cursor;
   loop
       fetch c bulk collect into l_c1, l_c2, ....... LIMIT 1000;
       for i in 1 .. l_c1.count
       loop
            process....
       end loop;
       forall i in 1 .. l_c1.count
            insert into ..... values ( L_c1(i), .... );
       end loop;
       exit when c%notfound;
   end loop;
   close cursor
LIMIT的值可以自己调整，一般来说我都是用10000，有时候1000。当然要是不愿意”麻烦“的也不要太担心，Oracle Database automatically optimizes it in Oracle Database 10g and above，我记得11g是相当于limit 200（不确定），但是我还是更愿意自己写。

newkid

llover 发表于 2011-12-28 23:57
5
Tom Keyt说过：
You should do it in a single SQL statement if at all possible.

这个所谓的自动优化，应该说的是FOR 游标，和FORALL写入没有关系。

lastwinner

7.减少代码耦合度和提高代码重用性一些讨论和案例，大家有类似经验都可以分享一下。比如包起到封装的效果，封装的好必然可以减少耦合度，提高代码重用性。

事实上，任何事情都要掌握一个度。耦合少了，连接必然增多——比如函数多层嵌套，这就和表该按范式设计还是反范式设计，如果按反范式，又反到多少比较合适？这些问题无法一概而论，只能就事论事

请注意：这里的“事”，可以是具体的事也可以是抽象的事

llover

newkid 发表于 2011-12-29 00:26
这个所谓的自动优化，应该说的是FOR 游标，和FORALL写入没有关系。

对，这个就不能用forall了，不过比一个从table里fetch强点儿

newkid

lastwinner 发表于 2011-12-29 00:44
7.减少代码耦合度和提高代码重用性一些讨论和案例，大家有类似经验都可以分享一下。比如包起到封装的效果， ...

不好说，跟着感觉走
SQL是很难重用的，一个SQL就是解决一个特定问题。

风铃中の鬼

写PL/SQL的话。。要在最开始极力缩小数据范围。在开始用1到2个SQL从物理表中取出最小范围的数据，之后的代码都只是对临时表的操作

lastwinner

newkid 发表于 2011-12-29 02:00
不好说，跟着感觉走
SQL是很难重用的，一个SQL就是解决一个特定问题。

是，除非多个业务都会使用相同的SQL来获取一些基础数据

lastwinner

风铃中の鬼 发表于 2011-12-29 10:04
写PL/SQL的话。。要在最开始极力缩小数据范围。在开始用1到2个SQL从物理表中取出最小范围的数据，之后的代码 ...

提高效率很重要～

nannan5000

1.    PL/SQL中的命名规范和编程规范：包，过程，函数，触发器，类型，变量等


我们统一是前缀
v_  变量
p_  参数
后面跟英文的短名 比如EMP SMPID

一把是全大写


2.PL/SQL中的异常处理规范：异常分类：编译期、运行期、可预测的、不可预测的，异常如何捕获与如何处理，在什么场合和位置处理异常。相关包比如DBMS_UTILITY.FORMAT_ERROR_STACK、DBMS_UTILITY.FORMAT_CALL_STACK、DBMS_UTILITY.FORMAT_ERROR_BACKTRACE以及10g的DML ERROR LOG，批处理异常特点和应用。比如when others部分到底该如何处理，when others null;完事了？是否该考虑定制一个高度封装的异常处理包来处理异常？


我们比较简单的 exception 里
when others 把 sqlcode 和sqlerrm 写到error表里。

我们还采用在程序块中加变量的方式来标示 程序块的用途和提示。

1. declare
   
2. 
   
3. ...
   
4. v_errmsg  varchar2(2000);
   
5. ...
   
6. 
   
7. begin
   
8. .....
   
9. v_errmsg:='查找员工号';
   
10. .....
    
11. v_errmsg:='查找员工工资';
    
12. .....
    
13. v_errmsg:='更新员工薪资系数';
    
14. .....
    
15. v_errmsg:='处理完毕';
    
16. .....
    
17. 
    
18. exception
    
19.   when others
    
20.       pkg_error.p_logerror(sqlcode,sqlerrm,v_errmsg);
    
21. 
    
22. end
    
23. 
    
24. 
    

复制代码

3.PL/SQL中的静态sql和动态sql规范以及何时使用绑定变量，何时避免使用绑定变量，


一般都是 把拼好的sql 放到一个 varchar变量里
然后 用参数 执行


4.sql与PL/SQL的关系，如何最佳使用PL/SQL特性而避免使用sql。

能用sql的不用plsql
毕竟plsql是对sql的包装。

不过plsql 更多的是一种开发语言。从开发的角度用plsql即可~



5.PL/SQL中的cursor使用规范，如何避免不必要的cursor使用 。

我们都是fetch  bullet into

然后close
然后 loop  数组

个人认为 还是要 规范化

1. 
   
2. open cursor for select xxx  from  xxx;
   
3. 
   
4. fetch into xxx;
   
5. 
   
6. close;
   
7. 
   
8. 
   
9. loop
   
10. 
    
11. xxxx
    
12. xxxx
    
13. xxxx
    
14. 
    
15. end loop
    
16. 
    
17. 
    
18. 
    

复制代码

6.如何使用好PL/SQL的一些优化机制：管道函数、bulk binding、nocopy,returning,cache,各种类型的合理使用.事务和并发控制:如commit的频率、场合、位置，for update (nowait),for update skip locked等。

这些俺们都木有用到，对plsql的使用还比较初级~

7.减少代码耦合度和提高代码重用性一些讨论和案例，大家有类似经验都可以分享一下。比如包起到封装的效果，封装的好必然可以减少耦合度，提高代码重用性。

这块更多的是涉及架构、框架性的东西。与语言无太大关系。
一般是把常用的函数抽象出来。比如f_findempname   f_cal啥的
就是把大部分重复性的东西 写成一个。
然后用函数的形式调用。



8.最后一点，PL/SQL内置包可能是PL/SQL编程中比较容易忽略的部分，oracle package众多，如果能使用恰当，会让我们的代码量减少，并且效率更好，那么能否讨论下你认为的常用包以及一般用途。


说实话，常用就一个 output包。
其它了解较少，不太知道需要用啥。









请lz 指导~

mikeamy

个人看法：
1、注意包与包耦合程度。
2、函数或过程功能简单化，尽量可以重用。
3、面向对象的编程思想实现所需要的功能。