For example, a programmer may know Java, and may also know SQL, but may not
have any experience with Oracle??™s language PL/SQL. The highest performance approach might be to use stored
procedures, but in order to shorten development time, and reduce training and support requirements, a group
might decide to simply put SQL statements into Java code instead of writing stored procedures.
The SQL CREATE command is used to create stored procedures. To give the flavor of a stored procedure,
here is an example of a stored procedure from an Oracle database. This procedure was written by the authors
to support an example database from David Kroenke??™s book Database Processing, 9th Ed., 2004. We will not
explain this syntax here, as an entire book could be written on the topic of PL/SQL. We present this code simply
to illustrate our discussion with a realistic example.
Create or Replace Procedure Record_sale
(
v_CustomerName IN Customer.Name%TYPE,
v_Artist IN Artist.Name%TYPE,
v_Title IN Work.Title%TYPE,
v_Copy IN Work.Copy%TYPE,
v_Price IN NUMBER,
v_Return OUT varChar2 --Return message to caller
)
AS
recCount int;
v_TransactionFound Boolean;
v_CustomerID Art_Customer.CustomerID%TYPE;
CHAP. 8] DATABASE 157
v_WorkID Work.
Pages:
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434