CREATE OR REPLACE PROCEDURE PL_SQL_EXAMPLE(
/*——————————————————————————
‘$Source$
‘$Author$
‘$Revision$
‘$Date$
‘
‘ 返回值 : 处理结果 0:正常、10:警告、99:异常
‘
‘ 参数 : pCODE – (i)ID
‘ pNAME – (i)名字
‘ pDIFF – (i)区分
‘ pReturn – (o)处理结果 (0:正常、10:警告、99:异常)
‘ 备考 :
——————————————————————————-*/
pCODE IN VARCHAR2
,pNAME IN VARCHAR2
,pDIFF IN VARCHAR2
,pReturn OUT NUMBER
)
IS
— 参数设定
wRET VARCHAR2(1);
wMSG VARCHAR2(1024) := NULL; — 信息
wMSGCD VARCHAR2(10) := NULL; — 信息ID
wCount NUMBER; — 检索件数
wInsert NUMBER := 0; — 登录件数
wUpdate NUMBER := 0; –更新件数
wDelete NUMBER := 0; –删除件数
wValid BOOLEAN NOT NULL DEFAULT FALSE;–定义变量没有指定初始值,那么该变量的初始值为NULL
wName Table.Col%TYPE; — 使用%TYPE,wName 变量将同Table表的Col列的类型相同(相当于綁一起了)。
— 例外处理用参数
excERR_SKIP EXCEPTION; –实行错
excERR_SYSTEM EXCEPTION; –异常
— 定义游标
CURSOR curGetRecord(
cNO VARCHAR2 –参数(SQL中用)
,cNAME NUMBER –参数(SQL中用)
)
IS
SELECT
ColName
FROM
DUAL
WHERE
1 = cNO
2 = cNAME ;
–recGetRecord 基于游标定义记录变量,可用recGetRecord.ColName取数据
recGetRecord curGetRecord%ROWTYPE;
/*——————————————————————————
‘ 功能 :排他用
‘
‘ 参数 :
‘
‘ 功能说明 :锁记录
——————————————————————————-*/
CURSOR curLock
IS
SELECT
*
FROM
DUAL
FOR UPDATE NOWAIT;
recLock curLock%ROWTYPE;
/*——————————————————————————
‘ 功能 :取件数
‘
‘ 参数 :fNO,fNAME
‘
‘ 功能说明 :用FUNCTION 返回记录件数
——————————————————————————-*/
FUNCTION SQL_Count(
fNO VARCHAR2 — 参数(SQL中用)
,fNAME NUMBER — 参数(SQL中用)
)RETURN NUMBER — 返回一个NUMBER类型的数
IS
pCNT NUMBER; — 定义
BEGIN
SELECT
COUNT(*) INTO pCNT
FROM
DUAL
WHERE
1 = fNO
2 = fNAME ;
RETURN pCNT;
END;
/*——————————————————————————
‘ 功能 :登表
‘
‘ 参数 :
‘
‘ 功能说明 :登数据
——————————————————————————-*/
PROCEDURE SQL_Insert
IS
BEGIN
INSERT INTO Table(
Col
,Col2
,Col3
)
VALUES(
Val
,Val2
,Val3
);
END;
/*——————————————————————————
‘ 功能 :更新
‘
‘ 参数 :
‘
‘ 功能说明 :更新数据
——————————————————————————-*/
PROCEDURE SQL_Update
IS
BEGIN
UPDATE Table SET
Col = ‘1’
,Col2 = ‘2’
WHERE
1 = 1 ;
END;
/*——————————————————————————
‘ 功能 :删除
‘
‘ 参数 :
‘
‘ 功能说明 :删除数据
——————————————————————————-*/
PROCEDURE SQL_Delete
IS
BEGIN
DELETE Table
WHERE
Col = ‘1’
,Col2 = ‘2’
END;
BEGIN
— 打Log 开始(登到一张专门出LOG的表中,方便管理)注:dbms_output.put_line("Debug");可以直接看到要打出的信息.
wRET := FNC_MessageInsert(MSG_INFO ,’START’ ,pCODE ,pNAME);
— 通过SQL文件,取个一个返回出来的件数
wCount := SQL_Count(pCODE,pNAME);
–打开游标
OPEN curGetRecord(pUKBENO,pUKBGYOBNG);
–循环执行
LOOP
BEGIN
FETCH curGetRecord INTO recGetRecord;
EXCEPTION
WHEN OTHERS THEN
wMSG := ‘程序出错’;
RAISE excERR_CURSOR; — 抛错到EXCEPTION中的excERR_CURSOR处理
END;
EXIT WHEN curGetRecord%NOTFOUND; — 如果SQL没有取出数据就退出
–区分(I:登录)可以在PACKAGE中定义个常量文件,可以提高维护性.
IF pDIFF = PKG_CONST.INSERT THEN
— 如果查不到数据就进行插数据
IF wCount = 0 THEN
— Table登表
SQL_Insert;
wInsert := wInsert + 1; — 记录登录数据的件数
ELSE — 当区分为登录并能取到数据时报个信息.
wMSGCD := ‘MSCD0001’; — 用CODE去找相应的信息(数据不存在)
RAISE excERR_SKIP; — 抛错到EXCEPTION中处理
END IF;
END IF;
END LOOP;
— 没有数据报个信息 注:当与NULL比较时,不要使用=,<>,语法是对的.便条件永远是FALSE;错:Expr = NULL
IF (recGetRecord.ColName IS NULL) THEN
wMSGCD := ‘MSCD0002’; –受払テーブルに対象データが存在しません。
RAISE excERR_SKIP;
END IF;
–区分(U:更新)
IF pDIFF = PKG_CONST.UPDATE THEN
–件数1以上
IF wCount > 0 THEN
—-更新前对要进行更新的记录进行锁定,防止在处理时被别人改动.
OPEN curLock;
–更新表记录
SQL_Upate;
–对锁定的记录解除
CLOSE curLock;
–记录更新数据的件数(LOG出力用)
wUpdate := SQL%ROWCOUNT;
ELSE –用CODE去找相应的信息(数据不存在)
wMSGCD := ‘MSCD0003’; –抛错到EXCEPTION中处理
RAISE excERR_SKIP;
END IF;
END IF;
–区分(D:删除)
IF pDIFF = PKG_CONST.DELETE THEN
–件数1以上
IF wCount > 0 THEN
–删除前对要进行删除的记录进行锁定,防止在处理时被别人改动.
OPEN curLock;
–删除表记录
SQL_Delete;
–对锁定的记录解除
CLOSE curLock;
–记录删除数据的件数(LOG出力用)
wDelete := SQL%ROWCOUNT;
ELSE –用CODE去找相应的信息(数据不存在)
wMSGCD := ‘MSCD0004’; –抛错到EXCEPTION中处理
RAISE excERR_SKIP;
END IF;
END IF;
CLOSE curGetRecord;
/*
* 动态SQL文
wSQL:=’Select dummy from dual ‘;– Select 文
wSQL:=’where 1=”’||wTemp||”’ ‘;
EXECUTE IMMEDIATE wSQL INTO pCNT;
*/
/*
* 动态SQL文
wSQL:=’UPDATE dual SET ‘; — Update ,Delete 文
wSQL:=’dummy = ”’wValue”’ ‘;
wSQL:=’where 1=”’||wTemp||”’ ‘;
EXECUTE IMMEDIATE wSQL ;
*/
/*———————————————————–
‘ 打出处理的件数
————————————————————*/
— 登录件数
wMSG := ‘Message INSERT ‘||TO_CHAR(wInsert);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
— 更新件数
wMSG := ‘Message UPDATE ‘||TO_CHAR(wUpate);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
— 删除件数
wMSG := ‘Message DELETE ‘||TO_CHAR(wDelete);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
pReturn := PKG_HARCOCONST.RV_SUCCESS; — 正常终了
–< 例外处理 >—————————————————————
EXCEPTION
WHEN excERR_SKIP THEN — 不能执行
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
pReturn := ’10’; — 警告终了
WHEN excERR_SYSTEM THEN — 不能执行
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
pReturn := ’10’; — 异常终了
WHEN OTHERS THEN — 其它情况
IF curGetRecord%ISOPEN = TRUE THEN
CLOSE curGetRecord;
END IF;
IF curLock%ISOPEN = TRUE THEN
CLOSE curLock;
END IF;
wMSG := SUBSTR(‘ **其它错语**’ ||
‘ SQLCODE=’ || ‘[ ‘ || SQLCODE || ‘ ]’ ||
‘ SQLERRM=’ || ‘[ ‘ || SQLERRM || ‘ ]’,1,256);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
wRET := FNC_MessageInsert(MSG_INFO ,wMSG ,pCODE ,pNAME);
pReturn := ’99’; — 异常终了
END;
/
/*
一些預定义例外
CASE_NOT_FOUND
|
|
|
ORA-06592
|
|
写CASE语句时,在when中没有必须条件分支也没有ELSE子句时触发
|
CURSOR_ALREADY_OPEN
|
|
ORA-06511
|
|
当重新打开已经打开的游标时触发
|
|
|
INVALID_CURSOR
|
|
|
ORA-01001
|
|
没有打开游标,或关了游标要去取数据时触发
|
|
DUP_VAL_ON_INDEX
|
|
|
ORA-00001
|
|
惟一索引列上输入重复值时触发
|
|
|
INVALID_NUMBER
|
|
|
ORA-01722
|
|
字符转换成数字出错时触发
|
|
|
|
NO_DATA_FOUND
|
|
|
ORA-01403
|
|
当执行SELECT INTO没有返回行时触发
|
|
|
TOO_MANY_ROWS
|
|
|
ORA-01422
|
|
当执行SELECT INTO返回超过一行时触发
|
|
|
VALUE_ERROR
|
|
|
ORA-06502
|
|
在赋值时,如果变量长度不能容纳实际数据时触发
*/
| |