编程技巧3.docx
《编程技巧3.docx》由会员分享，可在线阅读，更多相关《编程技巧3.docx（31页珍藏版）》请在冰豆网上搜索。
编程技巧3
一、关于提高Oracle应用程序的编程效率3
1、SQL语句书写格式，使用共享SQL语句资源3
2、使用索引4
3、表连接4
4、嵌套循环6
5、大表扫描6
6UPDATE时锁表。
7
7、批次插入大量数据7
二、SQL的使用8
1、show和set命令是两条用于维护SQL*Plus系统变量的命令8
2、sqlplus程序的调试8
3、如何用SQL生成SQL批处理文件8
4、外部联接"+"的用法8
5、在编写PL/SQL代码中使用SELECT语句时如何避免例外发生？
9
6、查找、删除表中重复的记录9
7、在Oracle中快速进行数据行存在性检查9
8、SQL语句中多表连接时，from后面的表顺序10
9、用utl_file包中相关的文件操作函数进行文件的操作，在sqlplus中实现文件的上下载功能10
10、having子句的用法10
11、游标和临时表10
三、ORACLE索引的使用11
1、在建立索引时的注意事项：
11
2、在使用索引时的注意事项：
11
四、DEVELOP2000/FORM的使用12
1、匿名登陆系统12
2、在客户端省略输入数据库串，只输入用户名、密码12
3、用FORM时使用参数列表进行参数传递12
4、使用显示项做到按钮的效果13
5、数组的使用13
6、FORM中，触发子exit_form的两种功能13
7、批次生成FMX文件14
8、屏蔽英文提示信息的两种方法14
9、截获系统信息，转换为汉字提示信息14
10、用FORMS实现ORACLE数据库与其它数据源数据的交换15
11、当使用主从块时，如何使从块自动提交？
15
12、如何减少FORM所占磁盘空间？
16
13、ORACLE*Forms中ListItem动态加载数据的方法16
14、取随机数between0and116
15、在form中执行ddl语句17
16、在form中使用同步17
18、用类似于其他语言中的struct结构来存储相关的数据信息，可提高程序的可读性和效率。
17
五、DEVELOP2000/REPORT的使用18
1、OracleReport3.0汉字输入问题的解决18
2、使用中间表来控制报表的输出格式18
3、报表输出到文本文件18
4、OracleReportWizard技巧18
5、如何使不同布局的报表显示在同一页上？
18
附摘文章20
文章一为什么有时ORACLE数据库不用索引来查找数据？
20
文章二Oracle基于Client/Server的性能调整22
一、关于提高Oracle应用程序的编程效率
Oracle应用程序效率的提高可以从以下几个方面着手进行：
业务逻辑功能调整；
优化数据结构设计；
优化数据处理步骤；
优化SQL语句；
物理结构调整；
内存分配优化；
I/O调整；
解决内存争用；
优化操作系统。
这里主要就SQL语句的优化总结一些经验以供参考：
1、SQL语句书写格式，使用共享SQL语句资源
在OracleServer中，执行的SQL语句以游标（cursor）的方式存在于共享SQL区（sharedSQLaera）中，并按照下图所示流程处理：
当执行一条SQL语句时，Oracle先在共享SQL区中查找是否有相同的SQL语句存在，如果不存在，Oracle将新建游标，并且对语句进行分析，然后绑定变量并按分析后的执行计划执行SQL语句；但如果有相同的SQL语句存在，Oracle可共享使用已有游标，仅需重新绑定变量（或者不需要），即可重复执行。
因此，使用相同的SQL语句，可以节省系统内存空间，提高代码运行效率。
Oracle按照以下原则判断SQL语句是否相同：
字母大小写相同；
空格数相同；
注释相同；
所使用的数据库对象（如：
表等）相同；
变量类型相同。
为实现游标共享，可使用存储过程、触发器、程序库等方式编写集中的代码。
同时，按照固定的格式书写SQL语句也将提高游标共享的机率，从而提高代码效率。
下例可作为SQL语句的书写格式以供参考：
selectb.polno,
b.brno,
b.start_date
fromevaluationa,
decrease_sumb
whereb.start_dateisnotnull
andb.finish_dateisnull
and（b.proc_typein（selectproc_type
fromevaluation_proc_type
whereevaluation_src='2'）
orb.proc_typein（selectproc_type
fromevaluation_proc_type
whereevaluation_src='3'）
anda.ctrlno=b.ctrlno
anda.proc_type=b.proc_type
anda.client_agree='Y'）;
2、使用索引
在对大量数据的表进行操作时，使用索引可以提高程序执行效率。
建立索引时应注意：
将经常用来查询的字段放在复合索引的前面；
建立全表索引时，将限制性最强的字段放在最前面；
建立主键、唯一键约束时，系统自动建立索引；
建立外键约束时，系统不会自动建立索引，此时如果被引用的表经常修改，应在外键字段上建立索引，以防止锁表；
例如：
pol_info.applicant引用client_info.insno作外键，如果pol_info.applicant上无索引，则修改client_info时会锁pol_info，如果在pol_info.applicant上加索引，即可防止上述锁出现，从而提高程序运行效率。
只加必要的索引，否则反而会影响效率；
例如：
对存在大量重复值的字段加索引会使程序执行效率大幅降低。
尽量在where字句中直接使用有索引的字段；
例如：
wherecol1=‘XXX’及wherecol1>v_num均可利用索引，而wherecol1||‘’=‘XXX’或wherecol1-0>v_num则不可利用索引。
可强制SQL语句使用索引；例如：
select/*+index（classes）*/*
fromclasses
wheretype=‘IDL’
andloc_idbetween3003and30005;
3、表连接
使用非相等连接将降低执行效率；
多个表连接时，对于在select子句中不出现的表，可用子查询代替表连接；
在from子句中，将小表放在后面，可提高执行效率；
例如：
查询语句A执行效率将高于语句B：
（A）selectb.polno,
b.brno,
b.start_date
fromevaluationa,
decrease_sumb
whereb.start_dateisnotnull
andb.finish_dateisnull
and（b.proc_typein（selectproc_type
fromevaluation_proc_type
whereevaluation_src='2'）
orb.proc_typein（selectproc_type
fromevaluation_proc_type
whereevaluation_src='3'）
anda.ctrlno=b.ctrlno
anda.proc_type=b.proc_type
anda.client_agree='Y'）;
（B）selectb.polno,
b.brno,
b.start_date
fromdecrease_sumb,
evaluationa
whereb.start_dateisnotnull
andb.finish_dateisnull
and（b.proc_typein（selectproc_type
fromevaluation_proc_type
whereevaluation_src='2'）
orb.proc_typein（selectproc_type
fromevaluation_proc_type
whereevaluation_src='3'）
anda.ctrlno=b.ctrlno
anda.proc_type=b.proc_type
anda.client_agree='Y'）;
在where子句中将过滤记录数最多的条件放在最后；
子查询数量较大时，使用表连接代替低效的SQL语句（IN，EXISTS，NOTIN，NOTEXISTS等）。
例如：
有表一：
createtabletab1（col1varchar2（20）primarykey,col2number）;
和表二：
createtabletab2（col1varchar2（20）primarykey,col2number）;
A现读取sum（tab1.col2），且tab1.col1存在于tab2.col1中：
使用IN：
selectsum（col2）fromtab1wherecol1in（selectcol1fromtab2）;
使用EXISTS:
selectsum（col2）fromtab1a
whereexists（select*fromtab2wherecol1=a.col1）;
使用连接：
selectsum（a.col2）fromtab1a,tab2bwherea.col1=b.col2;
B现读取sum（tab1.col2），且tab1.col1不存在于tab2.col1中：
使用NOTIN：
selectsum（col2）fromtab1wherecol1notin（selectcol1fromtab2）;
使用NOTEXISTS:
selectsum（col2）fromtab1a
wherenotexists（select*fromtab2wherecol1=a.col1）;
使用外连接：
selectsum（a.col2）fromtab1a,tab2b
wherea.col1=b.col2（+）andb.col1isnull;
4、嵌套循环
在PL/SQL过程中，如果有两个表查询结果的嵌套循环，应尽量将小表放在外层循环中。
5、大表扫描
如果需要对两张很大的表进行连接扫描，并且其中一表的连接字段为主键或唯一键，可用过程实现两表均一次扫描完成。
（代码如下，用tab1.col1=tab2.col1连接，tab1.col1为主键）
declare
cursorcur_tab1is
select*
fromtab1
orderbycol1asc;
cursorcur_tab2is
select*
fromtab2
orderbycol1asc;
v_row1tab1%rowtype;
v_row2tab2%rowtype;
begin
opencur_tab1;
fetchcur_tab1intov_row1;
opencur_tab2;
fetchcur_tab2intov_row2;
loop
exitwhennot（cur_tab1%foundorcur_tab2%found）;
if（v_row1.col1=v_row2.col1）then
--符合连接条件，进行处理
......
......
--处理完毕
fetchcur_tab2intov_row2;
elsif（v_row1.col1>v_row2.col1）then
fetchcur_tab2intov_row2;
else
fetchcur_tab1intov_row1;
endif;
endloop;
closecur_tab1;
closecur_tab2;
end;
6UPDATE时锁表。
在PL/SQL中使用UPDATE语句时，请使用cursor防止表锁。
（代码如下）
declare
cursorcur_tab1（cv_col1tab1.col1%type）is
select'X'
fromtab1
wherecol1=cv_col1
forupdatecol2;
v_col1tab1.col1%type;
v_col2tab1.col2%type;
v_charchar
（1）;
begin
......
opencur_tab1（v_col1）;
loop
fetchcur_tab1intov_char;
exitwhencur_tab1%notfound;
updatetab1
setcol2=v_col2
wherecurrentofcur_tab1;
endloop;
closecur_tab1;
......
end;
7、批次插入大量数据
批次插入大量数据时，可以使用数据类型PL/SQLTABLE，将数据放在PL/SQLTABLE中，再一次提交插入目的表中。
二、SQL的使用
1、show和set命令是两条用于维护SQL*Plus系统变量的命令
showall--查看所有68个系统变量值
showuser--显示当前连接用户
showerror --显示错误
setheadingoff--禁止输出列标题，默认值为ON
setfeedbackoff--禁止显示最后的计数信息，默认值为"对6个或更多记录，回送ON"
settimingon--默认为OFF，可用来估计SQL语句的执行时间（单位是1/1000*秒）
setsqlprompt”SQL>”--设置默认提示符，默认值就是"SQL>"
setlinesize1000--设置屏幕显示行宽，默认100
setautocommitON--设置是否自动提交，默认为OFF
setpauseon--默认为OFF，等待按下ENTER键，再显示下一页
setarraysize1--默认为15
setlong1000--默认为80
2、sqlplus程序的调试
在sqlplus程序的调试中，可以用dbms_output.put_line（'aa'）来输出调试信息，功能类似于form的message，不过在使用此功能的时候，要用setserveroutputon将调试信息显示功能打开。
3、如何用SQL生成SQL批处理文件
查询当前用户下所有表的记录数
select'select'''||tname||''',count（*）from'||tname||';'fromtabwheretabtype='TABLE';
把所有符合条件的表的select权限授予为public
select'grantselecton'||table_name||'topublic;'fromuser_tableswhere《条件》;
删除用户下各种对象
select'drop'||tabtype||''||tnamefromtab;
删除符合条件用户
select'dropuser'||username||'cascade;'fromall_userswhereuser_id>25;
4、外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接。
若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回，若二者均不带'+'，则二者中无法匹配的均被返回。
利用外部联接"+"，可以替代效率十分低下的notin运算，大大提高运行速度。
例如，下面这条命令执行起来很慢：
selectempnofromempwhereempnonotin
（selectempnofromemp1wherejob='SALE'）;
倘若利用外部联接,改写命令如下:
selecta.empnofromempa,emp1b
wherea.empno=b.empno（+）
andb.empnoisnull
andb.job='SALE';
可以发现,运行速度明显提高。
5、在编写PL/SQL代码中使用SELECT语句时如何避免例外发生？
在使用SELECT语句为某变量赋值时，往往会出现"NO_DATA_FOUND"和"TOO_MANY_ROWS"等异常情况，使用SELECT语句分两种情况：
第一种情况，判断某表中是否有符合某一条件的记录，这时使用聚组函数MAX就可以避免以上两个例外的发生。
其格式：
SELECTMAX（列1），MAX（列2）......INTO变量1，变量2......FROM基表WHERE条件，当没有符合条件的数据时，该语句返回空，即变量1，变量2......的值均为空，而不会出现"NO_DATA_FOUND"例外，另外使用聚组函数MAX的同时也就避免了"TOO_MANY_ROWS"例外。
第二种情况，SELECT语句中含聚组函数SUM，这时如果使用了GROUPBY和HAVING子句，当没有符合条件的记录时将发生"NO_DATA_FOUND"例外，此时避免例外的办法是：
去掉GROUPBY和HAVING子句，将HAVING子句的条件加到WHERE子句中。
这样在使用SELECT语句时就不必再额外增加一段处理例外的代码，从而简化的代码的编写。
6、查找、删除表中重复的记录
每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。
查找重复记录
selectrowid,bm,mcfroma
wherea.rowid!
=（selectmax（rowid）fromabwherea.bm=b.bmanda.mc=b.mc）;
删除重复记录
deletefromaa
wherea.rowid!
=（selectmax（rowid）fromabwherea.bm=b.bmanda.mc=b.mc）;
7、在Oracle中快速进行数据行存在性检查
当在应用程序的中需要检查一个外键是否有相关的主键时，往往使用SelectCount（*）类型的SQL语句。
这是一个很显而易的方法，但却不是最快的方法。
Count（*）函数调用可能会引起对整个表的进行扫描，这是一件很费时的操作。
一个更好的方法是使用Oracle提供的称为ROWNUM的新特性，使用这个特性可以使数据库只检索一个启示就可以判断主键是否能与外键相配，这比Count（*）方法快得多，例如:
SELECTCount（*）INTO:
ll_Count----应用Count（*）
FROMORDER
WHEREPROD_ID=:
ls_CheckProd
USINGSQLCA;
IFll_Count>0THEN//Cannotdeleteproduct
SELECTORDER_IDINTO:
ll_OrderID----应用ROWNUM
FROMORDER
WHEREPROD_ID=:
ls_CheckProd
ANDROWNUM<2
USINGSQLCA;
IFSQLCA.SQLNRows<>0THEN//cannotdeleteproduct
8、SQL语句中多表连接时，from后面的表顺序
小表做为驱动表，驱动表是from语句的最后一个表
9、用utl_file包中相关的文件操作函数进行文件的操作，在sqlplus中实现文件的上下载功能
10、having子句的用法
having子句对groupby子句所确定的行组进行控制，having子句条件中只允许涉及常量、聚组函数或groupby子句中的列。
11、游标和临时表
游标提供了对特定集合中逐行扫描的手段，一般使用游标逐行遍历数据，根据取出的数据不同条件进行不同的操作。
对多表和大表定义的游标循环很容易使程序进入一个漫长的等特甚至死机。
有些场合，有时必须使用游标，此时也可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，可是性能得到明显提高。
三、ORACLE索引的使用
1、在建立索引时的注意事项：
A数据重复性大的列上不要建立索引，如h_pol_info.ben_sts；
B需要经常性修改的列上不要建立索引；
C使用函数或运算（MIN，MAX除外）的列上不要建立索引；
D若一个表的外键的引用表上有频繁的插入、删除和修改操作，则要考虑建立索引，因为建立索引可以解除表锁。
如业务表中的polno,clientno字段要考虑建立索引；
E建立索引时要考虑索引使查询性能提高与使用索引导致插入、删除和更新性能的降低做比较，还要考虑储存索引对数据库空间的需求增加的因素；
2、在使用索引时的注意事项：
Awhere子句中如下情形没有利用索引：
（注意：
<=,<,>=,>,=使用了索引）
对字段做函数和运算；对字段使用了'in,or,like'做条件；对字段使用了不等号'!
='。
B写where条件时，有索引字段的判断在前，其它字段的判断在后。
C写where条件时用到多个索引字段，应按索引顺序判断。
Dselect,update,delete语句中的子查询应当有规律地查找少于20%的表行。
如果一个语句查找的行数超过总行数的20%，它将不能通过使用索引获得性能上的提高。
E索引可能产生碎片，因为记录从表中删除时，相应也从表的索引中删除。
表释放的空间可以再用，而索引释放的空间却不能再用。
频繁进行删除操作的被索引的表，应当阶段性地重建索引，以避免在索引中造成空间碎片，影响性能。
在许可的条件下，也可以阶段性地truncate表，truncate命令删除表中所有记录，也删除索引碎片。
四
升级会员 