《2022年Oracle存储过程开发规范与技巧 .pdf》由会员分享，可在线阅读，更多相关《2022年Oracle存储过程开发规范与技巧 .pdf（35页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、存储过程开发规范与技巧开发规范1.书写规范1） ：程序头书写规范程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。示例如下：- /* 名称及实现功能:版本 : ( 版本号标示：新建 V1.0.0 小的修改变为V1.0.1 大的修改 V1.1.0 重构 V2.0.0) Createby*CreateDate2006 - 06 - 29Update by* update Date2006 - 06 - 30修改原因 :Update by* update Date2006 - 06 - 31修改原因 : 涉及的表或视图：dump_in
2、it 辅助表 (DM) ：记录存储过程中使用的物化视图日志序号mlog$_acrcusmrsecindex 源表 (ODS) ：客户第一索引物化视图日志, 使用同义词ft_gld_customerdata 目标表 (DM) ：客户事实表*/ CREATE OR REPLACEPROCEDURE * - 2） ：代码书写规范1.语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、Sql 保留字大写。2.连接符 or、in 、and、以及、 =等前后加上一个空格。3.where 子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。4.查询的 WHERE 过滤
3、，原则应使过滤记录数最多的条件放在最前面。5.多表连接时，使用表的别名来引用列。6.查找数据库表或视图时，只能取出确实需要的那些字段，不要使用*来代替所有列名。7.功能相似的过程和函数，尽量写到同一个包中，加强管理。示例如下：BEGIN名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 35 页 - - - - - - - - - - 查询员工及对应的部门名称SELECT emp . name , dept.name FROM l_dept dept,l_employee e
4、mp WHERE emp . dept_id = dept. dept_id;END; 3）注释书写规范为了提高可读性，应该使用一定数量的注释。注释大约占总行数的1/5。1：注释风格：注释单独成行、放在语句前面。2：应对不易理解的分支条件表达式加注释；3：对重要的计算应说明其功能；4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明；5：每条 SQL 语句均应有注释说明6：对于程序的整体功能， 应在程序开始部分说明， 可采用单行 /多行注释。 （- 或 /* */ 方式）2.命名规范命名对象规则样例存储过程、包、方法1 业务相关以模块代码开头gld_assist_check_p2 如果
5、区分全量和增量，在最后加标识gld_load_to_etl gld_load_to_etl_full3 全局使用，以global 开头global_procedure_check变量以 v 开头v_updatemode1游标以 c 开头c_tablist内存表以 m 开头m_table1 临时表以 t 开头t_tmpTable 存储过程技术1存储过程样例CREATE OR REPLACEPROCEDURE example( v_input INNUMBER, - 输入参数 v_output OUTNUMBER - 输出参数)IS PRAGMA AUTONOMOUS_TRANSACTION ;C
6、URSOR c1 - 定义一个游标，在begin之前ISSELECT b . tablenamemlogtable, MAX ( remarks)KEEP(DENSE_RANKLASTORDER BY starttime)名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 35 页 - - - - - - - - - remarks FROM proc_log a ,table_proc b WHERE TO_CHAR ( starttime,yyyy-mm-dd)= - 转
7、换时间并做比较 TO_CHAR ( SYSDATE - TO_DSINTERVAL (TO_CHAR ( intervaldays) | 00:00:00),yyyy-mm-dd)AND a . remarks LIKESUCCEEDED:%AND a . procedurename = b . procedurename GROUP BY b . tablename); - 定义结束c1_rec c1%ROWTYPE; - 定义接受游标数据行的ROWTYPE v_mlogtable VARCHAR ( 30 ); v_postperiod CHAR ( 2); v_acctbalbegins
8、eq NUMBER; v_systime DATE; BEGIN v_input :=0; - 变量赋值 v_systime := SYSDATE ; OPEN c1 ;- 打开游标LOOP - 循环FETCH c1 INTO c1_rec; - 从当前游标行赋值c1_rec EXITWHEN c1 %NOTFOUND; - 游标没有数据退出 v_mlogtable := c1_rec. mlogtable; - 从行取出具体数据赋给变量CASE TRIM ( LOWER ( v_mlogtable) -CASE 起始WHENString1 - 当条件一THEN - 做条件一工作BEGIN v
9、_remarks :=REPLACE( v_remarks,AA);END;WHEN String2 - 当条件二THENBEGINEND;ELSE - 其他条件NULL;END CASE; -CASE 结束IF( LOWER ( SUBSTR ( v_mlogtable,1,5)mlog$)THENSELECT log_table INTO v_mlogtable FROM user_snapshot_logs WHERE LOWER (MASTER)= LOWER ( v_mlogtable);END IF;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - -
10、- - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 35 页 - - - - - - - - - EXECUTE IMMEDIATEdelete from | v_mlogtable | where sequence$ 2; - 循环跳出条件END LOOP; - 循环结束CLOSE c1 ; - 关闭游标EXCEPTIONWHEN OTHERSTHENROLLBACK;global_procedure_check. check_end( checkdataerror01, v_systime,1,SQLCODE | | SQLERRM);RAISE;RE
11、TURN;END;END example; 2基本知识1) 基本结构- CREATE OR REPLACEPROCEDURE example（parameters）- 过程声明区IS - v_1 NUMBER; - 过程中变量声明区- BEGIN v_1 :=0; - 过程内容区END example; -2) 基本类型CHAR 固定长度字符类型名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 35 页 - - - - - - - - - VARCHAR2 可变长字符类型V
12、ARCHAR 可变长字符类型( 不建议使用 ) NUMBER 一切数值类型DATE 一切日期类型3) 参数三种： IN 输入参数， OUT 输出参数， IN OUT 输入输出参数。4) 变量的声明在变量声明区声明变量的名称和类型例：v_postperiod CHAR ( 2); 可赋初值v_postperiod CHAR ( 2):= 01 ; （这里叫变量声明区可能并不恰当，因为游标、自定义类型等，一切需要事先声明的都应在这里声明。）5) 变量的赋值使用： =为变量赋值1直接使用基本类型赋值例： v_number := 1; 2. 使用函数赋值例： v_date := sysdate; 3使
13、用 SQL 语句为变量赋值1通过 sql 直接赋值SELECT COUNT (*)INTO v_tmpnumber FROM etl_ods_masterdata_tablist; 2通过构造SQL 赋值： v_tmpsql :=SELECT log_table FROM user_snapshot_logs | v_dblink | WHERE UPPER (MASTER) = UPPER ( | v_singletab | );EXECUTE IMMEDIATE v_tmpsql INTO v_tmpvarchar;名师资料总结 - - -精品资料欢迎下载 - - - - - - - -
14、- - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 35 页 - - - - - - - - - 6) 循环1.无限或简单循环LOOP EXITWHEN(退出循环条件 ); END LOOP; 2.while循环WHILE condition LOOP executable_statements; END LOOP; 3.for循环基于数字的for循环：FOR for_index IN low_value . high_value LOOP executable_statements; END LOOP; 基于游标的for循环：FOR record
15、_index IN my_cursor LOOP executable_statements; END LOOP; 7) 调用其他过程或方法1如果单独定义，直接使用例：v_retval0 :=f_dump_init( v_updatemode, v_systime,mlog$_glddocheader, v_procname, v_docheaderbeginseq, v_docheaderendseq );2如果定义在包下，使用包名+过程名例：global_procedure_check.check_run( v_procname) ；3固定用法和函数标识作用用法或类型固定用法：名师资料总结
16、 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 35 页 - - - - - - - - - SYSDATE当前系统时间DATESQLCODE异常代码VARCHAR2SQLERRM异常描述VARCHAR2NO_DATA_FOUND未找到数据异常与 when 搭配OTHERS其他所有异常与 when 搭配RAISE抛出当前异常RAISE;DENSE_RANK非选取字段排序MIN (B) KEEP ( DENSE_RANK FIRSTORDER BY A) MAX(B) KEEP (DE
17、NSE_RANKLAST ORDER BY A) PRAGMAAUTONOMOUS_TRANSACTION BULKCOLLECTINTOSQL%ROWCOUNT 使用自治事务，可以使该过程被调用时单独提交Begin之前使用PRAGMAAUTONOMOUS_TRANSACTION;将前面执行结果大批放入后面的集合中BULKCOLLECTINTO columntab;前一个 DML 语句执行影响行数作为 NUMBER型使用v_number:= SQL% ROWCOUNT DBMS_OUTPUT.put_line （）输出信息函数TO_CHAR转换 NCHAR、NVARCHAR2、CLOB、NCL
18、OB TO_CHAR（A）转换 DATE 型为指定格式TO_CHAR ( time,yyyy-mm-dd)转换 NUMBER型为指定格式TO_CHAR (564.70, $999.9) TO_DATE转换字符串为指定日期to_date( 1900-01-01, YYYY-MM-DD)INSTR (string,substring(,postion)(,occurrence) 返回目标字符串中子字符串的位置。（起始位置和出现次数为可选）INSTR ( bug- archie,archie) INSTR ( haracter?archie, a, 1, 2)LENGTH获得指定字符串长度LENGT
19、H( CANDIDE)LOWER将指定字符串转换成小写LOWER ( LETTERS)UPPER 将指定字符串转换成大写UPPER ( letters)LPAD( str1,n,str2) 将 str1用 str2左补齐至n 位LPAD ( 55,10 ,0)RPAD( str1,n,str2) 将 str1用 str2右补齐至n 位RPAD ( 55,10 ,0)LTRIM 去掉指定字符串左侧的指定字符或字符集合，默认为空格LTRIM ( Way) LTRIM ( 123123Way, 123)RTRIM 去掉指定字符串右侧的指定字符或字符集合，默认为空格RTRIM ( Way xyXxyx
20、y, xy)POWER （m,n ）计算 m的 n 次方POWER ( 2, 3)Extract (year from date) 取出 date的年名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 35 页 - - - - - - - - - 4ROWTYPE的使用可以使用 %type 和% rowtype属性实现使用其他变量、数据库列或表的数据类型的引用。%type属性提供了所需要的变量的类型及长度。% rowtype属性允许人们定义一个记录变量，它的成员变量拥有表中每
21、一列正确的类型及长度, 使用点符号引用记录中的每个成员变量。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型和大小改变了，如果使用了%TYPE, 那么用户就不必修改代码，否则就必须修改代码。CREATE TABLEEMPLOYEE ( EMP_ID NUMBERNOT NULL, EMP_NAME CHAR ( 20 ), CREATE_DATE DATE）DECLARE v_studentrecord employee%ROWTYPE; n employee. create_date%TYPE;BEGINSELECT*INTO v_studentrecord FROM employe
22、eWHERE emp_id =1; n := v_studentrecord. create_date; DBMS_OUTPUT. put_line ( n);END;5内存表的使用内存表主要作为数组用。1） ：一个字段：PROCEDURE t1 ISTYPE t_c ISTABLEOF testa. a1 %TYPEINDEXBY BINARY_INTEGER; aa t_c;BEGIN aa ( 0):=aaa; DBMS_OUTPUT. put_line ( aa ( 0);END;2） ：定义多个字段：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - -
23、 - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 35 页 - - - - - - - - - PROCEDURE t1 ISTYPE t_r ISRECORD ( t1 VARCHAR ( 10 ), t2 VARCHAR ( 10 );TYPE t_t ISTABLEOF t_r INDEXBY BINARY_INTEGER; aa t_t;BEGIN aa ( 0).t1 :=aaa; aa ( 0).t2 :=bbb; DBMS_OUTPUT. put_line ( aa ( 0). t1 ); DBMS_OUTPUT. put_line ( aa
24、 ( 0). t2 );END;6游标的使用游标是用来处理使用 SELECT 语句从数据库中检索到的多行记录的工具。借助于游标的功能，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。DECLARE n NUMBER ;CURSOR c ISSELECT*FROM employee;BEGINFOR v_c IN c LOOP n := v_c. emp_id; DBMS_OUTPUT. put_line ( n);END LOOP;EXCEPTIONWHEN OTHERSTHEN DBMS_OUTPUT. put_line ( error);END;7跟踪调试根踪调试主要是检查程序运行
25、的情况，可以在需要检查程序是否执行正确作为输出的依名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 35 页 - - - - - - - - - 据：DBMS_OUTPUT.PUT_LINE(G_USERID(-2); 执行时设置： set serveroutput on 8临时表临时表用于保存事务或者会话的中间结果，临时表中保存的数据只有对当时的会话是可见的，任何会话都不能看见其他会话的数据。即使COMMIT 之后也是不可见的。对于临时表并行不是问题，即使锁定也不能阻止其
26、他程序的访问。每个数据库创建临时表一次，（ORACLE 的 DDL语句是一种消耗较大的动作）并不用每个程序创建一次，并且临时表总保持为空。下面这个例子可以说明临时表的运行过程：CREATE GLOBALTEMPORARY TABLE REPDB . L_EMP_DEPT_TEMP (EMP_ID VARCHAR( 5),EMP_NAME VARCHAR( 20 ),DEPT_ID VARCHAR( 5),DEPT_NAME VARCHAR( 20 )1 DECLARE 2 D L_EMP_DEPT_TEMP%ROWTYPE; 3 CURSOR C IS 4 SELECT E.EMP_ID AA
27、 ,E.EMP_NAME BB ,D.DEPT_ID CC ,D.NAME DD 5 FROM L_EMPLOYEE E,L_DEPT D 6 WHERE E.DEP_ID=D.DEPT_ID; 7 BEGIN 8 FOR V_C IN C LOOP 9 INSERT INTO L_EMP_DEPT_TEMP 10 VALUES (V_C.AA,V_C.BB,V_C.CC,V_C.DD); 11 END LOOP; 12* END ; SQL / PL/SQL 过程已成功完成。SQL SELECT COUNT(*) 2 FROM L_EMP_DEPT_TEMP 3 / COUNT(*) - 3
28、 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 35 页 - - - - - - - - - SQL COMMIT 2 / 提交完成。SQL SELECT COUNT(*) 2 FROM L_EMP_DEPT_TEMP 3 / COUNT(*) - 0 9异常处理例外是一个非致命事件，它立即中断程序的正常执行并引起一个非条件转移，跳转到当前程序块的例外处理部分。一些例外，像NO_DATE_FOUND或TO_MANY_ROWS，属于预定义例外用于处理常见的 oracle
29、 错误，可以被认为是正常的处理部分。部分ERROR这样的例外表明一个程序错误或一些意料之外的事件。如下所示：1）：正常处理的部分1 DECLARE 2 N CHAR; 3 BEGIN 4 SELECT EMP_NAME 5 INTO N 6 FROM EMPLOYEE; 7 DBMS_OUTPUT.PUT_LINE(N); 8* END; SQL / DECLARE * 第 1 行出现错误 : ORA-01422: 实际返回的行数超出请求的行数ORA-06512: 在 line 4 1 DECLARE 2 N CHAR; 3 BEGIN 4 SELECT EMP_NAME 5 INTO N 6
30、 FROM EMPLOYEE; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 35 页 - - - - - - - - - 7 DBMS_OUTPUT.PUT_LINE(N); 8 EXCEPTION WHEN TOO_MANY_ROWS THEN 9 DBMS_OUTPUT.PUT_LINE(TOO MANY ROWS RETURN); 10* END; PL/SQL 过程已成功完成。输出结果为：TOO MANY ROWS RETURN2） ：非正常处理的部分，自定
31、义异常SQL insert into l_employee 2 values (4,dd,3,sysdate,2000) 3 / insert into l_employee * 第 1 行出现错误 : ORA-02291: 违反完整约束条件(REPDB.FK_EMP_DEPT) - 未找到父项关键字处理方法：自定义异常1 declare 2 e exception ; 3 pragma exception_init(e,-2291); 4 begin 5 insert into l_employee 6 values (6,dd,3,sysdate,2000) ; 7 exception w
32、hen e then 8 DBMS_OUTPUT.PUT_LINE(违反完整约束条件(REPDB.FK_EMP_DEPT); 9* end ; SQL / PL/SQL 过程已成功完成。输出结果为：违反完整约束条件(REPDB.FK_EMP_DEPT) 10嵌套程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。如果字块需要单独提交，应使用自治事务。11标签用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被
33、标记。 标签的形式是。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 35 页 - - - - - - - - - 要求使用标签。12记录转储开始时间和结束时间1）在建立中间表后，用脚本或手工在数据表中建立一条记录，以后每次都更新。对每个转储只记录一条记录，不保存历史记录。2）在开始转储时， 读取上次转储结束时间， 只转储从上次转储以来的新增或修改的记录。3）在转储的存储过程中记录开始时间和结束时间，是否成功。如果失败， 记录失败原因。4）可以用 SQL 语句查找失败的
34、转储，可以查找转储时间过长的转储。表名：转储记录（ TRANSLOG ）字段：程序包名存储过程名中间表名开始时间结束时间成功标识失败原因13授权grantselecton cs_new. ACPStorkFlAssAnaTab to repdbnew revokeselecton cs_new. BILInvoiceUseEntityData from repdbnew 注意：不能为当前用户授权14建立同义词CREATE SYNONYM ACPStorkFlAssAnaTab FOR jcerp. ACPStorkFlAssAnaTab dropSYNONYM BILInvoiceUseEnt
35、ityData 注：建立同义词后用户可以用select访问，但不能建立视图。15为表字段加注释COMMENT ON COLUMN HAN_2 . BBBB ISB 字段 ;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 35 页 - - - - - - - - - 16触发器createorreplacetrigger qytest1_trigger3 BEFORE INSERTON mlog$_qytest1FOR EACH ROWbegin:new.snaptime
36、$ := SYSDATE ;end; 17自定义类型的赋值自定义类型：CREATEORREPLACETYPE repdbnew. INPARAM ASVARRAY( 50 )OFVARCHAR2( 25 );自定义类型的赋值：declare indataname inparam;begin indataname := inparam( 2); indataname:= inparam( isCalCount, NO_TX);end;18OBJECT TYPES 簡單來說， Oracle ObjectTypes就是 Oracle 以 TYPE 的方式來實現物件( Objects) 的方法，宣告/
37、定義的方法，類似於Package。ObjectType的宣告 / 定義中包含了它的Attributes/Properties 與Methods，也就是MemberFunctions/Procedures。本篇來介紹利用 Oracle ObjectTypes來做中介暫存的實作。建立 Oracle Object:定義這個Object的內容，可以把它想像為所希望的Row Columns的定義。view plaincopy to clipboardprint? CREATE TYPE type_obj AS OBJECT( col1 INTEGER , col2 VARCHAR2( 60 );/CRE
38、ATE TYPE type_obj AS OBJECT( col1 INTEGER , col2 VARCHAR2( 60 );/建立Object Collection:建立一個TableType ，這個Table裝的資料列內容( 欄位 ) 就是之前所建立的Object。view plaincopy to clipboardprint? CREATE OR REPLACE名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 35 页 - - - - - - - - - TYP
39、E type_tab ISTABLEOF type_obj;/CREATE OR REPLACETYPE type_tab ISTABLEOF type_obj;/在 PL /SQL中的應用 :定義好前面兩個Type ，就可以拿它來在程式中實作了。view plaincopy to clipboardprint? DECLARE- 宣告與初始化 obj_type_tab type_tab := type_tab();BEGIN- 多筆給值 obj_type_tab := type_tab ( type_obj (37 ,col2_val1), type_obj (3,col2_val2), t
40、ype_obj ( 254 ,col2_val3);- 逐筆給值 obj_type_tab.EXTEND; obj_type_tab( obj_type_tab.LAST):= type_obj(12 ,col_2_val);/* 要逐筆給或一次給多筆就純粹看使用時機與方便性. 舉例來說, 已經有確切可知的數筆資料時, 就可以用多筆給的方式來定義使用. 但是, 如果資料來源是取自其他媒介, 比方說 Cursor, 可能就需要跑 Loop 去逐筆取值與給值了. */* 再來, 可以把已經存放好資料的 obj_type_tab, 像下面這種應用方式去取回資料. 當然, 舉一反三, 它可以用這樣子的
41、 SELECT 方式轉化成 Reference Cursor 及其他相關應用. */FOR rec IN(SELECT col1, col2 FROM TABLE(CAST(obj_type_tab AS TYPE_TAB)LOOPDBMS_OUTPUT.put_line( rec.col1 = | rec. col1 | ; rec.col2 = | rec. col2);END LOOP;/* 輸出結果: rec.col1 = 37; rec.col2 = col2_val1 rec.col1 = 3; rec.col2 = col2_val2 rec.col1 = 254; rec.co
42、l2 = col2_val3 rec.col1 = 12; rec.col2 = col_2_val */END;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 35 页 - - - - - - - - - 19Tabletype 的使用定义：resultset OUT tabletype 使用： OPEN resultset FORSELECTDISTINCT b . procname, b . paramneeded FROM temp_tablenamesa,ta
43、ble_proc b WHERE UPPER (a.tablename)= b . tablename; 查询： DECLARE TABNAMES TABNAMES_OBJ; RESULTSET GETTABLENAMES. TABLETYPE ; w_id VARCHAR2 ( 30 ); h_id VARCHAR2 ( 30 );BEGIN TABNAMES :=( TABNAMES_OBJ ( tabname_list_typ ( REP_FT_CO_CPCB_V);GETTABLENAMES . GETTABLESOFVIEW ( TABNAMES, RESULTSET );LOOPF
44、ETCH RESULTSET INTO w_id, h_id;EXITWHEN RESULTSET% NOTFOUND;DBMS_OUTPUT.put_line( w_id | , | h_id);END LOOP;COMMIT;END;20创建 DB-Link createdatabaselinkerpcar-db link 名称，与连接实例名相同connectto jferp -连接使用用户identifiedbyqmnerp-用户密码usingERPCAR-连接实例21SQL 优化1) SELECT 子句中避免使用 *当你想在SELECT 子句中列出所有的COLUMN时,使用动态SQL
45、列引用* 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际 上,ORACLE 在解析的过程中, 会将 * 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 35 页 - - - - - - - - - 2) 尽量多使用 COMMIT 只要有可能 ,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少 : COMMIT所释放的资源
46、: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE 为管理上述3 种资源中的内部花费3) 减少对表的查询次数在含有子查询的SQL 语句中 ,要特别注意减少对表的查询. 1)例如 : 低效SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_C0LUMNS WHERE VERSION = 604) 高
47、效SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 2)Update 多个 Column 例子 : 低效 : UPDA TE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 高效
48、: UPDA TE EMP SET (EMP_CA T, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 35 页 - - - - - - - - - 4) 用 EXISTS 替代 IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用E
49、XISTS( 或 NOT EXISTS) 通常将提高查询的效率. 低效 : SELECT * FROM EMP ( 基础表 ) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB)高效 : SELECT * FROM EMP ( 基础表 ) WHERE EMPNO 0 AND EXISTS (SELECT XFROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB)用 IN 来替换 OR 下面的查询可以被更有效率的语句替换: 低效 : SELECT .F
50、ROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效SELECT FROM LOCATION WHERE LOC_IN IN (10,20,30); 5) 用 Where 子句替换 HAVING 子句：避免使用 HAVING 子句， HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE 子句限制记录的数目，那就能减少这方面的开销。例如 : 低效 : SELECT REGION ，AVG(LOG_SIZE) FROM LOCA TION GROUP BY REGION