Oracle存储过程开发规范与技巧8122.docx

《Oracle存储过程开发规范与技巧8122.docx》由会员分享，可在线阅读，更多相关《Oracle存储过程开发规范与技巧8122.docx（35页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存储过程开发规范与技巧开发规范1.书写规范1）：程序头书写规范程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。示例如下：-/*名称及实现功能: 版本: ( 版本号标示：新建 V1.0.0 小的修改变为V1.0.1 大的修改V1.1.0 重构V2.0.0)Create by * Create Date 2006-06-29Update by * update Date 2006-06-30修改原因:Update by * update Date 2006-06-31修改原因:涉及的表或视图：dump_init 辅助表(DM)：记

2、录存储过程中使用的物化视图日志序号mlog$_acrcusmrsecindex 源表(ODS)：客户第一索引物化视图日志,使用同义词ft_gld_customerdata 目标表(DM)：客户事实表*/CREATE OR REPLACE PROCEDURE *-2）：代码书写规范1. 语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、Sql保留字大写。 2. 连接符or、in、and、以及、=等前后加上一个空格。3. where子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。4. 查询的WHERE过滤，原则应使过滤记录数最多的条件放在最前面。 5.

3、 多表连接时，使用表的别名来引用列。6. 查找数据库表或视图时，只能取出确实需要的那些字段，不要使用*来代替所有列名。7. 功能相似的过程和函数，尽量写到同一个包中，加强管理。 示例如下：BEGIN -查询员工及对应的部门名称 SELECT emp.name, dept.name FROM l_dept dept, l_employee emp WHERE emp.dept_id = dept.dept_id;END;3）注释书写规范为了提高可读性，应该使用一定数量的注释。注释大约占总行数的1/5。1：注释风格：注释单独成行、放在语句前面。2：应对不易理解的分支条件表达式加注释；3：对重要的计

4、算应说明其功能；4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明；5：每条SQL语句均应有注释说明6：对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。（- 或 /* */ 方式）2.命名规范命名对象规则样例存储过程、包、方法1 业务相关以模块代码开头gld_assist_check_p2 如果区分全量和增量，在最后加标识gld_load_to_etlgld_load_to_etl_full3 全局使用，以global开头global_procedure_check变量以 v 开头v_updatemode1游标以 c 开头c_tablist内存表以 m 开头m_tab

5、le1临时表以 t 开头t_tmpTable存储过程技术1存储过程样例CREATE OR REPLACE PROCEDURE example ( v_input IN NUMBER,-输入参数 v_output OUT NUMBER -输出参数)ISPRAGMA AUTONOMOUS_TRANSACTION; CURSOR c1-定义一个游标，在begin之前 IS SELECT b.tablename mlogtable, MAX (remarks)KEEP (DENSE_RANK LAST ORDER BY starttime) remarks FROM proc_log a, table

6、_proc b WHERE TO_CHAR (starttime, yyyy-mm-dd) = -转换时间并做比较 TO_CHAR ( SYSDATE - TO_DSINTERVAL ( TO_CHAR (intervaldays) | 00:00:00), yyyy-mm-dd ) AND a.remarks LIKE SUCCEEDED:% AND a.procedurename = b.procedurename GROUP BY b.tablename); -定义结束c1_rec c1%ROWTYPE; -定义接受游标数据行的ROWTYPE v_mlogtable VARCHAR (3

7、0); v_postperiod CHAR (2); v_acctbalbeginseq NUMBER; v_systime DATE;BEGIN v_input := 0;-变量赋值 v_systime := SYSDATE; OPEN c1;-打开游标 LOOP-循环 FETCH c1 INTO c1_rec;-从当前游标行赋值c1_rec EXIT WHEN c1%NOTFOUND;-游标没有数据退出 v_mlogtable := c1_rec.mlogtable;-从行取出具体数据赋给变量 CASE TRIM (LOWER (v_mlogtable)-CASE起始 WHEN Strin

8、g1-当条件一 THEN-做条件一工作 BEGIN v_remarks := REPLACE (v_remarks, AA); END; WHEN String2-当条件二 THEN BEGIN END; ELSE-其他条件 NULL; END CASE;-CASE结束 IF (LOWER (SUBSTR (v_mlogtable, 1, 5) mlog$) THEN SELECT log_table INTO v_mlogtable FROM user_snapshot_logs WHERE LOWER (MASTER) = LOWER (v_mlogtable); END IF; EXEC

9、UTE IMMEDIATE delete from | v_mlogtable | where sequence$ 2;-循环跳出条件 END LOOP;-循环结束 CLOSE c1;-关闭游标 EXCEPTION WHEN OTHERS THEN ROLLBACK; global_procedure_check.check_end (checkdataerror01, v_systime, 1, SQLCODE | | SQLERRM ); RAISE; RETURN; END;END example;2基本知识1) 基本结构-CREATE OR REPLACE PROCEDURE exam

10、ple（parameters）-过程声明区IS-v_1 NUMBER;-过程中变量声明区-BEGIN v_1 := 0;-过程内容区END example;-2) 基本类型CHAR固定长度字符类型VARCHAR2可变长字符类型VARCHAR可变长字符类型(不建议使用)NUMBER一切数值类型DATE一切日期类型3) 参数三种：IN 输入参数，OUT输出参数，IN OUT 输入输出参数。4) 变量的声明在变量声明区声明变量的名称和类型例：v_postperiod CHAR (2);可赋初值v_postperiod CHAR (2):=01;（这里叫变量声明区可能并不恰当，因为游标、自定义类型等，

11、一切需要事先声明的都应在这里声明。）5) 变量的赋值使用：=为变量赋值1直接使用基本类型赋值例：v_number := 1;2.使用函数赋值例：v_date := sysdate;3使用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_singlet

12、ab | ); EXECUTE IMMEDIATE v_tmpsql INTO v_tmpvarchar;6) 循环1. 无限或简单循环LOOPEXIT WHEN (退出循环条件);END LOOP;2. while循环WHILE conditionLOOP executable_statements;END LOOP;3. for循环基于数字的for循环：FOR for_index IN low_value . high_valueLOOP executable_statements;END LOOP; 基于游标的for循环：FOR record_index IN my_cursorLOOP

13、 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固定用法和函数标识作用用法或类型固定用法：SYSDATE当前系统时间DATESQLCODE异常代码VA

14、RCHAR2SQLERRM异常描述VARCHAR2NO_DATA_FOUND未找到数据异常与 when 搭配OTHERS其他所有异常与 when 搭配RAISE抛出当前异常RAISE;DENSE_RANK非选取字段排序MIN(B) KEEP (DENSE_RANK FIRST ORDER BY A)MAX(B) KEEP (DENSE_RANK LAST ORDER BY A)PRAGMA AUTONOMOUS_TRANSACTIONBULK COLLECT INTOSQL%ROWCOUNT使用自治事务，可以使该过程被调用时单独提交Begin之前使用 PRAGMA AUTONOMOUS_TRA

15、NSACTION;将前面执行结果大批放入后面的集合中BULK COLLECT INTO columntab;前一个DML语句执行影响行数作为NUMBER型使用v_number:= SQL%ROWCOUNTDBMS_OUTPUT.put_line（）输出信息函数TO_CHAR转换NCHAR、NVARCHAR2、CLOB、NCLOBTO_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)IN

16、STR(string,substring(,postion)(, occurrence)返回目标字符串中子字符串的位置。（起始位置和出现次数为可选）INSTR (bug- archie, archie)INSTR (haracter?archie, a, 1, 2)LENGTH获得指定字符串长度LENGTH(CANDIDE)LOWER将指定字符串转换成小写LOWER (LETTERS)UPPER将指定字符串转换成大写UPPER (letters)LPAD(str1,n,str2)将str1用str2左补齐至n位LPAD (55, 10, 0)RPAD(str1,n,str2)将str1用str

17、2右补齐至n位RPAD (55, 10, 0)LTRIM去掉指定字符串左侧的指定字符或字符集合，默认为空格LTRIM ( Way)LTRIM (123123Way,123)RTRIM去掉指定字符串右侧的指定字符或字符集合，默认为空格RTRIM (Way xyXxyxy,xy)POWER（m,n）计算m的n次方POWER(2,3)Extract (year from date)取出date的年4ROWTYPE的使用可以使用%type 和% rowtype属性实现使用其他变量、数据库列或表的数据类型的引用。%type属性提供了所需要的变量的类型及长度。% rowtype属性允许人们定义一个记录变量

18、，它的成员变量拥有表中每一列正确的类型及长度,使用点符号引用记录中的每个成员变量。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型和大小改变了，如果使用了%TYPE,那么用户就不必修改代码，否则就必须修改代码。 CREATE TABLE EMPLOYEE ( EMP_ID NUMBER NOT NULL, EMP_NAME CHAR (20), CREATE_DATE DATE）DECLARE v_studentrecord employee%ROWTYPE; n employee.create_date%TYPE;BEGIN SELECT * INTO v_studentrecord

19、 FROM employee WHERE emp_id = 1; n := v_studentrecord.create_date; DBMS_OUTPUT.put_line (n);END;5内存表的使用内存表主要作为数组用。1）：一个字段：PROCEDURE t1IS TYPE t_c IS TABLE OF testa.a1%TYPE INDEX BY BINARY_INTEGER; aa t_c;BEGIN aa (0) := aaa; DBMS_OUTPUT.put_line (aa (0);END;2）：定义多个字段：PROCEDURE t1IS TYPE t_r IS RECOR

20、D ( t1 VARCHAR (10), t2 VARCHAR (10) ); TYPE t_t IS TABLE OF t_r INDEX BY 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 (0).t2);END;6游标的使用游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助于游标的功能，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。DECLARE n NUM

21、BER; CURSOR c IS SELECT * FROM employee;BEGIN FOR v_c IN c LOOP n := v_c.emp_id; DBMS_OUTPUT.put_line (n); END LOOP;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (error);END;7跟踪调试根踪调试主要是检查程序运行的情况，可以在需要检查程序是否执行正确作为输出的依据：DBMS_OUTPUT.PUT_LINE(G_USERID(-2);执行时设置：set serveroutput on8临时表临时表用于保存事务或者会话的中间

22、结果，临时表中保存的数据只有对当时的会话是可见的，任何会话都不能看见其他会话的数据。即使COMMIT之后也是不可见的。对于临时表并行不是问题，即使锁定也不能阻止其他程序的访问。每个数据库创建临时表一次，（ORACLE 的DDL语句是一种消耗较大的动作）并不用每个程序创建一次，并且临时表总保持为空。下面这个例子可以说明临时表的运行过程：CREATE GLOBAL TEMPORARY TABLE REPDB.L_EMP_DEPT_TEMP(EMP_IDVARCHAR(5),EMP_NAMEVARCHAR(20),DEPT_IDVARCHAR(5),DEPT_NAMEVARCHAR(20) 1 DE

23、CLARE 2 D L_EMP_DEPT_TEMP%ROWTYPE; 3 CURSOR C IS 4 SELECT E.EMP_ID AA ,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

24、过程已成功完成。SQL SELECT COUNT(*) 2 FROM L_EMP_DEPT_TEMP 3 / COUNT(*)- 3SQL COMMIT 2 /提交完成。SQL SELECT COUNT(*) 2 FROM L_EMP_DEPT_TEMP 3 / COUNT(*)- 09异常处理例外是一个非致命事件，它立即中断程序的正常执行并引起一个非条件转移，跳转到当前程序块的例外处理部分。一些例外，像NO_DATE_FOUND或TO_MANY_ROWS，属于预定义例外用于处理常见的oracle错误，可以被认为是正常的处理部分。部分ERROR这样的例外表明一个程序错误或一些意料之外的事件。如

25、下所示：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 FROM EMPLOYEE; 7 DBMS_OUTPUT.PUT_LINE(N); 8 EXCEPTION

26、WHEN TOO_MANY_ROWS THEN 9 DBMS_OUTPUT.PUT_LINE(TOO MANY ROWS RETURN); 10* END;PL/SQL 过程已成功完成。 输出结果为：TOO MANY ROWS RETURN2）：非正常处理的部分，自定义异常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 declar

27、e 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 when e then 8 DBMS_OUTPUT.PUT_LINE(违反完整约束条件 (REPDB.FK_EMP_DEPT); 9* end ;SQL /PL/SQL 过程已成功完成。 输出结果为： 违反完整约束条件 (REPDB.FK_EMP_DEPT) 10嵌套程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义

28、在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。如果字块需要单独提交，应使用自治事务。11标签用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被标记。标签的形式是。要求使用标签。12记录转储开始时间和结束时间1）在建立中间表后，用脚本或手工在数据表中建立一条记录，以后每次都更新。对每个转储只记录一条记录，不保存历史记录。2）在开始转储时，读取上次转储结束时间，只转储从上次转储以来的新增或修改的记录。3）在转储的存储过程中记录开始时间和结束时间，是否成功。如果失败，记录失败原

29、因。4）可以用SQL语句查找失败的转储，可以查找转储时间过长的转储。表名：转储记录（TRANSLOG）字段：程序包名存储过程名中间表名开始时间结束时间成功标识失败原因13授权grant select on cs_new.ACPStorkFlAssAnaTab to repdbnewrevoke select on cs_new.BILInvoiceUseEntityData from repdbnew注意：不能为当前用户授权14建立同义词CREATE SYNONYM ACPStorkFlAssAnaTab FOR jcerp.ACPStorkFlAssAnaTabdrop SYNONYM BI

30、LInvoiceUseEntityData注：建立同义词后用户可以用select访问，但不能建立视图。15为表字段加注释COMMENT ON COLUMN HAN_2.BBBB IS B字段;16触发器create or replace trigger qytest1_trigger3 BEFORE INSERT ON mlog$_qytest1FOR EACH ROWbegin:new.snaptime$ := SYSDATE;end;17自定义类型的赋值自定义类型：CREATE OR REPLACE TYPE repdbnew.INPARAM AS VARRAY(50) OF VARCHA

31、R2(25); 自定义类型的赋值： declare indataname inparam; begin indataname := inparam(2); indataname:= inparam(isCalCount,NO_TX); end; 18OBJECT TYPES簡單來說，Oracle Object Types 就是 Oracle 以 TYPE 的方式來實現物件(Objects)的方法，宣告/定義的方法，類似於 Package。Object Type 的宣告/定義中包含了它的 Attributes/Properties 與 Methods，也就是 Member Functions/P

32、rocedures。本篇來介紹利用 Oracle Object Types 來做中介暫存的實作。建立 Oracle Object:定義這個 Object 的內容，可以把它想像為所希望的 Row Columns 的定義。view plaincopy to clipboardprint?CREATE TYPE type_obj AS OBJECT ( col1 INTEGER, col2 VARCHAR2(60) ); / CREATE TYPE type_obj AS OBJECT ( col1 INTEGER, col2 VARCHAR2(60);/建立 Object Collection:建

33、立一個 Table Type，這個 Table 裝的資料列內容(欄位)就是之前所建立的 Object。view plaincopy to clipboardprint?CREATE OR REPLACE TYPE type_tab IS TABLE OF type_obj; / CREATE OR REPLACE TYPE type_tab IS TABLE OF type_obj;/在 PL/SQL 中的應用:定義好前面兩個 Type，就可以拿它來在程式中實作了。view plaincopy to clipboardprint?DECLARE - 宣告與初始化 obj_type_tab ty

34、pe_tab := type_tab(); BEGIN - 多筆給值 obj_type_tab := type_tab ( type_obj ( 37, col2_val1), type_obj ( 3, col2_val2), type_obj (254, col2_val3); - 逐筆給值 obj_type_tab.EXTEND; obj_type_tab(obj_type_tab.LAST) := type_obj( 12, col_2_val); /* 要逐筆給或一次給多筆就純粹看使用時機與方便性. 舉例來說, 已經有確切可知的數筆資料時, 就可以用多筆給的方式來定義使用. 但是,

35、如果資料來源是取自其他媒介, 比方說 Cursor, 可能就需要跑 Loop 去逐筆取值與給值了. */ /* 再來, 可以把已經存放好資料的 obj_type_tab, 像下面這種應用方式去取回資料. 當然, 舉一反三, 它可以用這樣子的 SELECT 方式轉化成 Reference Cursor 及其他相關應用. */ FOR rec IN (SELECT col1, col2 FROM TABLE(CAST(obj_type_tab AS TYPE_TAB) LOOP DBMS_OUTPUT.put_line( rec.col1 = | rec.col1 | ; rec.col2 = |

36、 rec.col2); END LOOP; /* 輸出結果: rec.col1 = 37; rec.col2 = col2_val1 rec.col1 = 3; rec.col2 = col2_val2 rec.col1 = 254; rec.col2 = col2_val3 rec.col1 = 12; rec.col2 = col_2_val */ END; 19Tabletype的使用定义：resultset OUT tabletype使用：OPEN resultset FOR SELECT DISTINCT b.procname, b.paramneeded FROM temp_tab

37、lenames a, table_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); LOOP FETCH RESULTSET INTO w_id, h_id; EXIT WHEN RESULTSET%NOTFOUND; DBMS_OUTPUT.put_line (w_id | , | h_id); END LOOP; COMMIT;E