oracle过程,包,触发器

  
 
 
 
oracle过程,包,触发器
 一.    语法介绍：
1.1    函数，存储过程，触发器：
 create [or Replace] procedure procedure_name 
 [ (argument[{ IN | out | in out}] type,
…
argument[{ IN | out | in out}] type)] {is|as}
       procedure_Body
 
   
create [or Replace] function procedure_name 
 [ (argument[{ IN | out | in out}] type,
…
argument[{ IN | out | in out}] type)] return return_type{is|as}
       function_Body
    
 Create [or Replace ] trigger trigger_Name 
 {before | after |instead of} Triggering_event
Referencing_Clause
[When Trigger_condition] [for each row]
trigger_Body;
 
1）    or Replace 一定要加，表示存在就替换同名的过程，触发器或函数。
2）    参数尽量采用这样的方式p_parame2 in out students.currFld%type（取对应数据表的字段类型），尽量不要这样p_Parame2 varchar2（直接定义）
3）    is|as 统一使用is。
4）    参数的缺省值用:=，如：ChrMenuID varchar2:=''
5）    尽量不要使用内函数。
 
1.2    包：
      Create [or replace] package package_name {is|as}
 Type_definition |
 Procedure_specification |
 Function_specification |
 Variable_declaration |
 Exception_declaration |
 Cursor_declaration |
 Pragma_declaration
End [package_name]
 
   1) or Replace 一定要加，表示存在就替换同名的包。
   2) 变量定义尽量用％type和％rowtype等。
   3) is|as 包定义用as，包内部的函数和过程用is。
   4) 参数的缺省值用:=，如：ChrMenuID varchar2:=''
 5) 一定先定义变量，游标，异常类型等，过程和函数放在最后定义
 6) 游标变量一般使用弱类型游标。
 
 
 
二.    模版及在Toad中的使用：
在%Toad%/ temps目录下有四个函数，存储过程，触发器，包的模版文件，改动如下，之后可以通过procdure editor->new procedure按钮按模版方式创建新的函数，存储过程，触发器，包。
2.1函数和过程：
  /******************************************************************************
概要说明：    
         中文名称：
         用    途: 
         数据库：
语法信息：
         输入参数：见下                    
         输出参数: 无
         返回值 ：NUMBER
         调用举例:    
                  NUMBER := F_%YourObjectName%();
 
外部联系：
         上级调用：
         下级调用：无
         输入表：
         输出表：无
功能修订：
         简要说明：
         修订记录：
                      日期        修改人       原因                      
            %DATE% %USERNAME%       创建      
******************************************************************************/
CREATE OR REPLACE FUNCTION F_%YOUROBJECTNAME% 
             (ChrUserID varchar2:='',    --用户号                                                
                        NumCode number)    
RETURN NUMBER 
IS
    tmpVar NUMBER;
BEGIN
    tmpVar := 0;
    RETURN tmpVar;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
       Null;
     WHEN OTHERS THEN
       Null;
END F_%YourObjectName%;
2.2包
 
/******************************************************************************
概要说明：
    包    名: PK_%YourObjectName%
         中文名称：
         用    途: 
    数据库：
修订记录：
    日期        修改人    对象          原因                  
    %DATE% %USERNAME%    %YourObjectName%    创建包          
******************************************************************************/
CREATE OR REPLACE PACKAGE PK_%YourObjectname% IS
    TYPE CurReturn IS REF CURSOR;
    /******************************************************************************
    概要说明：    
             中文名称：
             用    途: 
    语法信息：
             输入参数：见下                      
             输出参数: 无
             返回值 ：NUMBER
             调用举例:    
                       NUMBER := MyFuncName();
    修订记录：
                  日期        修改人       原因                     
        %DATE% %USERNAME%       创建         
    ******************************************************************************/
    FUNCTION MyFuncName ( inVal Number ) Return Number;
    /******************************************************************************
    概要说明：    
             中文名称：
            用    途: 
    语法信息：
            输入参数：见下                      
             输出参数: 无
             返回值 ：
             调用举例:    
                       MyProcName();
    修订记录：
                  日期        修改人       原因                     
        %DATE% %USERNAME%       创建         
    ******************************************************************************/
    PROCEDURE MyProcName ( inVal Number, JobId VARCHAR2 );
END PK_%YourObjectName%;
/
 
CREATE OR REPLACE PACKAGE BODY PK_%YourObjectName% AS
     。。。。。。
 
2.3 触发器
/******************************************************************************
概要说明：
     中文名称：
     用    途:
     数据库：
语法信息：
     更新表名：
     类    型：前;插入,删除,修改;替代;系统;                
     级    别: 行级;语句级
     调用举例:    
              
功能修订：
     修订记录：
                  日期        修改人       原因                      
            %DATE% %USERNAME%       创建 
******************************************************************************/
CREATE OR REPLACE TRIGGER T_%YourObjectname% 
 --INSTEAD OF INSERT OR UPDATE OR DELETE ON students
 BEFORE INSERT OR UPDATE OR DELETE ON students
    FOR EACH ROW
DECLARE
    tmpVar NUMBER;
BEGIN
    tmpVar := 0;
 
    Select MySeq.NextVal into tmpVar from dual;
    :OLD.SequenceColumn := tmpVar;
    :NEW.CreatedDate := Sysdate;
    :NEW.CreatedUser := User;
 
EXCEPTION
    WHEN OTHERS THEN
        Null;
END T_%YourObjectName%;
 
三.    命名约定：
 3.1 函数，存储过程：
 P(F)_调用分类_功能分类_详细名称
其中 P表示是存储过程，F是函数，详细名称是与存储过程意义相关联的汉语拼音首字母，
按调用者分类： 
公用类—— GY
内部用—— NB
按功能分类：
              查询类：       CX
              数据维护类：   WH
              业务处理类：   YW
例： P_NB_YW_GFQS
 
 3.2 触发器
TR_表名(系统操作名)_详细名称
表名是触发器用的表名或系统触发器调用动作（用户登陆，服务器重启等），详细名称是与触发器意义相关联的汉语拼音首字母
 3.3 包
PK_分类名_详细名称
同上
 
四.    变量名：
 参数命名约定：作用域+变量类型+名称，变量类型和名称用小写。
 1) 作用域： g   全局类型的变量（包中定义的变量）
               p   存储过程，函数，触发器使用的参数变量
              无   内部变量
 2) 变量类型：
    
变量类型
简写
举例
Char，varchar2
Chr
ChrPPNO
Number
Num
NumCount
Date
Dt
DtDate
Long
Lng
LngEdit
记录类型（record）
Rec
RecStudent
用户自定义类型Type
Typ
TypCode
异常类型变量Exception
Exp
ExpNoFound
Index-By表
Tbi
TbiName
嵌套表
Tab
TabName
可变数组
arr
ArrName
游标变量Cursor
Cur
CurStudent
游标类型Ref Cursor
Rfc
RfcEdit
 
 
 
 
五.    注意事项：
 
5.1对SQL语句特别是INERT、DELETE、UPDATE语句成功与否的判断，用隐形游标Sql％notfound和Sql%rowcount来判断。
5.2 关于临时表：ORACLE的临时表与SQL SERVER中的临时表不一样。它需要实际建立一张表，但是只有表结构。操作上与正常表无异，其数据在每次程序模块运行结束时自动清除。在Oracle8i中，可以创建以下两种临时表： 
1）会话特有的临时表 
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> ) 
ON COMMIT PRESERVE ROWS；
 
2) 事务特有的临时表 
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> ) 
ON COMMIT DELETE ROWS； 
--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行） 
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
 
   例如：CREATE GLOBAL TEMPORARY TABLE flight_schedule ( 
startdate DATE, enddate DATE, cost NUMBER) 
ON COMMIT PRESERVE ROWS;
   我们规定临时表必须以tmp_开头。
5.3   采用规范的缩进文本格式书写
一般设置： TAB = 4， 并将TAB自动转换为空格；
若有多个Begin…End语句嵌套时采用如下方式
BEGIN /*1*/
…
BEGIN /*1.1*/
…
BEGIN /*1.1.1*/
…
END /*1.1.1*/
 
BEGIN /*1.1.2*/
…
END /*1.1.2*/
 
END /*1.1*/
END /*1*/
其中1表示第一级嵌套，1.1表示第二级嵌套，1.1.1表示第三级嵌套，1.1.2表示第三级的第二个嵌套… ，一般不要超过三级嵌套。
 
5.4事务处理放在语句块中，如下例
 begin
<some code>
commit;
 exception
WHEN OTHERS THEN
 <some code> 
 rollback;
 end;
5.5关于注释：
单行要用--
大段注释用 /* 注释内容 */ 的形式。
 
5.6在PL/Sql中只允许使用DML和事务控制语句；不能出现DDL语句，如果要用可通过本地动态Sql实现：
   EXECUTE IMMEDIATE
'CREATE TABLE execute_table (col1 VARCHAR(10))';
5.7可通过DBMS_OUTPUT.PUT_LINE显示PL/Sql中的变量，相当于Sql server中的print，注意要设置输出缓冲区大一些（set serveroutput on size 2000）
5.8 函数一般是一个返回值（如果返回值多于一个，尽量用过程）。一般 0 成功 <0 为失败 >0 表示其他返回值。
5.9 在PL/Sql中尽量少用goto语句，因为它很可能造成逻辑混乱，可读性也差。Goto语句的典型使用方法如下：
1)      declare 
     v_counter binary_integer:=1;
begin
 loop 
insert into …
v_counter:=v_counter+1;
if v_counter>50 then
 goto End_Lable
end if;
 end loop;
 
<<End_Label>>
 insert into…
   end;
2)      begin
<<End_Loop>>
for I in 1..50 loop
 for k in 2..10 loop
    if I>40 then
      exit End_Loop;   --退到外层循环
    end if;
end loop;
end loop End_Loop；
end;
 
5.10 关于变量声明：oracle中变量声明但未初始化，其值为null。且不能这样声明变量：
   declare
Str1,Str2 varchar2(30);
   只能这样定义
   declare
str1 varchar2(30);
Str2 varchar2(30):=’edit’;
   在付初值之前，Str1为null。
5.11 RowID与RowNum：
 RowID是表自动产生隐含列的，是表当前行的行标识符，如下例：
update test set o = 
(select tmp.rn from 
(select rid,rownum rn from (select rowid rid from test order by v desc)) tmp 
where test.rowid = tmp.rid);
 RowNum在查询中返回当前的行号，是查询时临时产生的，限制要处理的行的总数。第一行的RowNum＝1，第二行的RowNum＝2，依此类推，下例为显示t_sys_menu表的2-5行
select menu_id,menu_Name,menu_Desc 
 from(selectrownum rdm,menu_id,menu_Name,menu_Desc from t_sys_menu whererownum<6) aa
    where aa.rdm>=2;
5.12 在原表的基础上建新的表
 CREATE TABLE NEWTABLENAME AS 
SELECT * FROM OLDTABLENAME WHERE 1=2;