pl/sql游戏规则， 做Oracle开发的人员可以作为参考
PL/SQL遊戲規則
一. 程式編碼
1. 命名原則
(1).程式命名原則: 小於
|
對象 |
前綴 |
對象 |
前綴 |
|
package |
pkg_ |
trigger |
trig_ |
|
StoreProcedure |
sp_ |
exception |
ep_ |
|
function |
f_ |
|
|
|
type |
typ_ |
|
|
例: pkg_BankTransactions, sp_log
(2).程式中對象命名原則: 小於
|
對象 |
前綴 |
對象 |
前綴 |
|
LoclVariables |
l_ |
RelationalTables |
rt_ |
|
Parameters |
p_ |
NestedTables |
nt_ |
|
Cursors |
cus_ |
|
|
|
VarrayNames |
v_ |
|
|
|
ObjectTypeNames |
o_ |
|
|
|
NestedTableTypes |
n_ |
|
|
|
ObjectTables |
ot_ |
|
|
例: cus_trans
2. 書寫格式
(1).關鍵字、保留字、系統包/過程大寫,其他小寫(為表明含義而大小寫混合的除外).
(2).每個代碼塊縮進3個空格.
(3).每條語句一行(太長的可換行,以整齊為原則).
(4).程式段落之間換行.
(5).Insert語句書寫時須加入Column名稱.
3. 程式注釋
(1).每支程式開始都需對程式進行說明,以“Rem”注釋,包括以下內容:
a. NAME(名稱): 程式代號
b. DESCRIPTION(說明): 對程式的功能作說明
c. RETURNS(返回值): 如程式有返回值,則描述一下
d. NOTES(備註): 另外的注釋、限定條件及其他
e. AUTHOR(作者): 創建人、創建日
f. MODIFIED(修改記錄): 修改人、修改日、修改內容
g. 其他認為有必要加入的說明.
(2).程式中有必要說明的段落及變量、參數等,以“--”注釋;儘量不用“/* …… */”.
例：-- Applies pending transactions in the table TRANSACTIONS to the
-- ACCOUNTS table. Used at regular intervals to update bank
-- accounts without interfering with input of new transactions.
而非：/* Applies pending transactions in the table TRANSACTIONS to the
ACCOUNTS table. Used at regular intervals to update bank
accounts without interfering with input of new transactions. */
二. 整體架構
1. pkg_bas為公用package,內含用戶使用procedure的記錄、程式異常log、權限設定、公用ASSERT等.
2. pkg_xxx為不同應用建立的package.
三. 公用package(pkg_bas)
1. sp_log
(1).對於一些需記錄用戶使用時間的procedure,可通過sp_log()來寫到下面table:
|
<<log_call>> |
|||
|
Name |
Code |
DataType |
|
|
調用時間 |
invoking_time |
DATE |
|
|
資料庫用戶 |
DB_user |
VARCHAR2(20) |
|
|
用戶代號 |
user_id |
VARCHAR2(20) |
|
|
用戶名稱 |
user_name |
VARCHAR2(30) |
|
|
程式名稱 |
procedure_name |
VARCHAR2(30) |
|
|
傳遞參數 |
parameters |
VARCHAR2(500) |
|
(2).程式異常時,可通過sp_log()來寫到下面table:
|
<<log_errors>> |
|||
|
Name |
Code |
DataType |
|
|
出錯時間 |
error_time |
DATE |
|
|
程式名稱 |
procedure_name |
VARCHAR2(30) |
|
|
錯誤訊息 |
error_message |
VARCHAR2(90) |
|
|
用戶代號 |
user_id |
VARCHAR2(20) |
|
|
用戶名稱 |
user_name |
VARCHAR2(30) |
|
2. sp_SetRole
用於特殊權限的設定,如:人事系統中的權限.
3. sp_assert
集中通用的斷言在一個procedure中,如:當用戶想調用無權使用的procedure時,可用它來作統一命名. 不用時可關閉.
四. Procedure樣式
Rem NAME: pkg_BankTransactions
Rem DESCRIPTION: The following example creates the specification and body for a package
Rem that contains several procedures and functions that process banking
Rem transactions.
Rem RETURNS:
Rem NOTES:
Rem AUTHOR:
Rem MODIFIED:
------------------------------------------------------------------------------------------
CREATE PACKAGE pkg_BankTransactions (null) AS
minimum_balance CONSTANT NUMBER := 100.00;
PROCEDURE sp10_ApplyTransactions;
PROCEDURE sp20_EnterTransaction (acct NUMBER,
kind CHAR,
amount NUMBER);
END pkg_BankTransactions;
------------------------------------------------------------------------------------------
CREATE PACKAGE BODY pkg_BankTransactions AS
-- Package to input bank transactions
new_status CHAR(20); -- Global variable to record status
-- of transaction being applied. Used
-- for update in sp10_ApplyTransactions.
PROCEDURE sp1000_DoJournalEntry (acct NUMBER, kind CHAR) IS
-- Records a journal entry for each bank transaction applied by the sp10_ApplyTransactions
-- procedure.
BEGIN
INSERT INTO journal VALUES (acct, kind, sysdate);
IF kind = 'D' THEN
new_status := 'Debit applied';
ELSIF kind = 'C' THEN
new_status := 'Credit applied';
ELSE
new_status := 'New account';
END IF;
END sp1000_DoJournalEntry;
------------------------------------------------------------------------------------------
PROCEDURE sp100_CreditAccount (acct NUMBER, credit NUMBER) IS
-- Credits a bank account the specified amount. If the account does not exist, the procedure
-- creates a new account first.
old_balance NUMBER;
new_balance NUMBER;
BEGIN
SELECT balance INTO old_balance FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; -- Locks account for credit update
new_balance := old_balance + credit;
UPDATE accounts SET balance = new_balance
WHERE acct_id = acct;
sp1000_DoJournalEntry(acct, 'C');
EXCEPTION
WHEN NO_DATA_FOUND THEN -- Create new account if not found
INSERT INTO accounts (acct_id, balance) VALUES(acct, credit);
sp1000_DoJournalEntry(acct, 'N');
WHEN OTHERS THEN -- Return other errors to application
new_status := 'Error: ' || SQLERRM(SQLCODE);
END sp100_CreditAccount;
------------------------------------------------------------------------------------------
PROCEDURE sp200_DebitAccount (acct NUMBER, debit NUMBER) IS
-- Debits an existing account if result is greater than the allowed minimum balance.
old_balance NUMBER;
new_balance NUMBER;
insufficient_funds EXCEPTION;
BEGIN
SELECT balance INTO old_balance FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance;
new_balance := old_balance - debit;
IF new_balance >= minimum_balance THEN
UPDATE accounts SET balance = new_balance
WHERE acct_id = acct;
sp1000_DoJournalEntry(acct, 'D');
ELSE
RAISE insufficient_funds;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Nonexistent account';
WHEN insufficient_funds THEN
new_status := 'Insufficient funds';
WHEN OTHERS THEN -- Returns other errors to application
new_status := 'Error: ' || SQLERRM(SQLCODE);
END sp200_DebitAccount;
------------------------------------------------------------------------------------------
PROCEDURE sp10_ApplyTransactions IS
-- Applies pending transactions in the table TRANSACTIONS to the
-- ACCOUNTS table. Used at regular intervals to update bank
-- accounts without interfering with input of new transactions.
-- Cursor fetches and locks all rows from the TRANSACTIONS
-- table with a status of 'Pending'. Locks released after all
-- pending transactions have been applied.
CURSOR cus_TransCursor IS
SELECT acct_id, kind, amount FROM transactions
WHERE status = 'Pending'
ORDER BY time_tag
FOR UPDATE OF status;
BEGIN
FOR trans IN cus_TransCursor LOOP -- implicit open and fetch
IF trans.kind = 'D' THEN
sp200_DebitAccount(trans.acct_id, trans.amount);
ELSIF trans.kind = 'C' THEN
sp100_CreditAccount(trans.acct_id, trans.amount);
ELSE
new_status := 'Rejected';
END IF;
-- Update TRANSACTIONS table to return result of applying this transaction.
UPDATE transactions SET status = new_status
WHERE CURRENT OF cus_TransCursor;
END
COMMIT; -- Release row locks in TRANSACTIONS table.
END sp10_ApplyTransactions;
------------------------------------------------------------------------------------------
PROCEDURE sp20_EnterTransaction (acct NUMBER, kind CHAR, amount NUMBER) IS
-- Enters a bank transaction into the TRANSACTIONS table. A new
-- transaction is always put into this 'queue' before being
-- applied to the specified account by the sp10_ApplyTransactions
-- procedure. Therefore, many transactions can be simultaneously
-- input without interference.
BEGIN
INSERT INTO transactions VALUES (acct, kind, amount, 'Pending', sysdate);
COMMIT;
END sp20_EnterTransaction;
------------------------------------------------------------------------------------------
END pkg_BankTransactions;
