oracle 过程,包,触发器一. 语法介绍:1.1 函数,存储过程,触发器:create [or Replace] procedure procedure_name[ (argument[{ IN | out | in out}] type,…argument[{ IN | out | in out}] type)] {is|as}procedure_Bodycreate [or Replace] function procedure_name[ (argument[{ IN | out | in out}] type,…argument[{ IN | out | in out}] type)] return return_type{is|as}function_BodyCreate [or Replace ] trigger trigger_Name{before | after |instead of} Triggering_eventReferencing_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_declarationEnd [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 NUMBERIStmpVar NUMBER;BEGINtmpVar := 0;RETURN tmpVar;EXCEPTIONWHEN NO_DATA_FOUND THENNull;WHEN OTHERS THENNull;END F_%YourObjectName%;2.2包/******************************************************************************概要说明:包 名 : PK_%YourObjectName%中文名称:用 途 :数 据 库:修订记录:日期 修改人 对象 原因%DATE% %USERNAME% %YourObjectName% 创建包******************************************************************************/CREATE OR REPLACE PACKAGE PK_%YourObjectname% ISTYPE 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 studentsBEFORE INSERT OR UPDATE OR DELETE ON studentsFOR EACH ROWDECLAREtmpVar NUMBER;BEGINtmpVar := 0;Select MySeq.NextVal into tmpVar from dual;:OLD.SequenceColumn := tmpVar;:NEW.CreatedDate := Sysdate;:NEW.CreatedUser := User;EXCEPTIONWHEN OTHERS THENNull;END T_%YourObjectName%;三. 命名约定:3.1 函数,存储过程:P(F)_ 调用分类_功能分类_详细名称其中 P表示是存储过程,F是函数,详细名称是与存储过程意义相关联的汉语拼音首字母,按调用者分类:公用类—— GY内部用—— NB按功能分类:查询类: CX数据维护类: WH业务处理类: YW例: P_NB_YW_GFQS3.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
(select
rownum
rdm
,
menu_id
,
menu_Name
,
menu_Desc
from
t_sys_menu
where
rownum<6)
aa
where
aa
.
rdm
>=
2
;
5.12 在原表的基础上建新的表
CREATE TABLE NEWTABLENAME AS
SELECT * FROM OLDTABLENAME WHERE 1=2;