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;
640
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
