oracle存储过程，SQL Procedural Language，oracle procedure

oracle存储过程，SQL Procedural Language，oracle procedure


转自：http://www.oracle.com/technetwork/database/database-technologies/rdb/sql-proc-lang-feat-334044.html


SQL Procedural Language


Brought to you by the Oracle Rdb Server Technology Group


Ian Smith
Consulting Engineer
Oracle Corporation
New England Development Center


This is the first in a series of articles on the SQL Procedural Language. In this issue, I'll summarize the language and discuss the motivation for using such a language.


The Oracle Rdb procedural language is based on the SQL92 Persistent Stored Modules component (known as ISO/IEC 9075-4:1996). Although this part of the SQL92 language was added in 1996, Rdb already had a version of the language in production by 1994. In Oracle Rdb7.1, there are additional language features drawn from the draft SQL3 database language.


The language includes many familiar constructs, which I'll list briefly here. Please refer to the Oracle Rdb7 SQL Reference Manual, the Oracle Rdb7.1 New Features Manual, and the Oracle Rdb7.1 Release Notes for more details.


Compound statements
The familiar BEGIN ... END construct
Declaration statements
DECLARE variables
DECLARE LOCAL TEMPORARY TABLE for temporary in-memory tables.
Rdb7.1 adds global variables which can be reference by all routines in the module and hold persistent data across call statements.
Conditional statements
IF ... THEN ... ELSE
CASE ... WHEN ... END CASE
Rdb7.1 adds enhancements to the CASE statement allowing lists of value expressions, and a new searched form of the CASE statement.
Looping statements
LOOP ... END LOOP
WHILE ... DO ... END WHILE
REPEAT ... UNTIL ... END REPEAT (Rdb7.1)
The counted loop FOR ... IN ... END FOR (Rdb7.1)
Row processing
The cursor loop FOR ... SELECT ... DO ... END FOR
UPDATE ... WHERE CURRENT OF … statement
DELETE ... WHERE CURRENT OF ... statement
Loop termination control statements
LEAVE
ITERATE (Rdb7.1)
Table operations
Singleton SELECT statement
INSERT statement
INSERT ... SELECT statement
UPDATE statement
Transaction statements
SET TRANSACTION
COMMIT
ROLLBACK
LOCK TABLE (Rdb7.1)
Informational statement
GET DIAGNOSTICS
Nested stored procedures
CALL statement
References to SQL functions
Miscellaneous statements
RETURN: Return a SQL function result.
TRACE: Trace procedure parameters, variables.


Why is a procedural language needed?


There are two reasons to include a procedural language in SQL. One is to allow the development of more complex definitions within the database. The other is performance.


Consider the following example: a simple banking transaction to an Automated Teller Machine (ATM). The transaction is started; the customers account balance is updated with the deposited or withdrawn amount; some bookkeeping is performed; and finally, the new balance is returned to be printed on the receipt.




set transaction read write;


-- adjust account balance
update account
set balance = balance + :amt
where account_id = :id;


-- record change in the tellers balance
update teller
set balance = balance + :amt
where teller_id = :tid;


-- record change for the branch
update branch
set balance = balance + :amt
where branch_id = :bid;


-- record the details of the transaction
insert into history


values (:id, :tid, :bid, :amt, current_timestamp);


-- return the new balance to the teller
select balance
into :amt
from account
where account_id = :id;


commit;


When the client application executes these statements on the database server, there is an associated message overhead. This message cost can be described by two parts: an input message that passes in data and an output message that returns data and the status of the call. In this case, the cost of this transaction can be measured as 14 messages.


Rdb introduced the compound statement in Rdb V5.1. We could have coded this transaction in that version as a compound statement, performing the steps in three parts: start transaction, perform action, and commit transaction. This reduces the transaction cost to 6 messages.




procedure ATM_TRANSACTION
sqlcode,
:id integer,
:tid integer,


:bid integer,
:amt integer;


begin
-- adjust account balance
update account
set balance = balance + :amt
where account_id = :id;


-- record change in the tellers balance
update teller
set balance = balance + :amt
where teller_id = :tid;


-- record change for the branch
update branch


set balance = balance + :amt
where branch_id = :bid;


-- record the details of the transaction
insert into history
values (:id, :tid, :bid, :amt, current_timestamp);


-- return the new balance to the teller
select balance
into :amt
from account
where account_id = :id;


end;


In Rdb V6.0, SQL added the ability to place the SET TRANSACTION and COMMIT statements in the compound statement and achieve the best possible overhead cost of two messages.


Most applications in use today still use the single statement approach to SQL programming. By collecting multiple statements into compound statements, several improvements in communications with the database server can be achieved.


Further, these compound statements can be named and stored in the database. The functionality and message cost remains the same. However, now these procedure can have security applied, and can be allowed to to run at an elevated privilege for selected users.


In a future Rdb Web Journal, we will discuss the Stored Procedure execution model and examine the best way to develop new stored procedures. This will include a discussion of the new Oracle Rdb features to enable application evolution: optional arguments, default values, and synonyms.


Technical Tips: Diagnosing Syntax Errors in Stored Procedures


While interactive SQL is the preferred tool for placing stored procedures and functions in the database, it has limited diagnostic capabilities. This is because interactive SQL just performs a single pass over the input and only sees one statement at a time. Typically, it aborts processing on the first error.


On the other hand, the SQL module language is a language compiler and uses several passes over the source file. It therefore has the ability to diagnose problems with inline messages, and reports errors for multiple statements. Therefore, when I am developing stored procedures, I prefer to create a simple module language header, which looks like this:




module TEST
language GENERAL
declare alias filename 'mydatabase'
procedure X (sqlcode);


I simply append my CREATE MODULE statement to this template and use the SQL module language compiler to report any errors. Once it compiles, cleanly delete the top few lines and execute in interactive SQL.

I think you'll find this technique is the best way to avoid the edit and retry cycle for correcting SQL syntax errors.