Following are examples that show the use of SQL Procedures.
Sample Procedure 1
In this example, SQL names that do not consist of delimited identifiers are converted to uppercase by SQL. Also, notice that the at sign (@), used in conjunction with the TERM=@ parameter in DBSQLPR, enables DBSQLPR to skip over semicolons embedded in statements and instead recognize the @ as the end of the statement. For more information about TERM=, see DBSQLPR Options. For z/OS and z/VSE JCL samples, see Example JCL.
Note: Use the following as a guide to prepare your JCL. The JCL statements are for example only. Lowercase letters in a statement indicate a value you must supply. Code all statements to your site and installation standards.
//jobname See the note above.
// REGION=1024K
//JOBLIB your DD statements go here (see Listing Libraries for CA Datacom Products)
//SQLEXEC EXEC PGM=DBSQLPR,
// PARM='authid=sysadm,prtWidth=1500,inputWidth=80,term=@'
//*CAOESTOP DD DUMMY
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//STDERR DD SYSOUT=*
//STDOUT DD SYSOUT=*
//OPTIONS DD *
/*
//SYSIN DD *
-- Supply customer credit limits for procedure creditLimitCheck below.
create table credit
(custId numeric(10),
creditMax decimal(15,2),
creditUsed decimal(15,2)) @
insert into credit values (1, 2000., 1000.)@
commit@
--
-- Does the customer have enough credit to purchase an ordered part?
create procedure creditLimitCheck
(inout result varchar(25), in custId char(5),
in purchaseAmount decimal(15,2), in turnDebugDumpsOn int)
language sql
limitCheck: begin atomic
declare creditAvailable decimal(15,2) default 0;
-- Compute available credit.
select creditMax-creditUsed into creditAvailable
from credit
where credit.custId =
cast(creditLimitCheck.custId as numeric(5));
if (purchaseAmount > creditAvailable) then
set result = 'CREDIT LIMIT EXCEEDED';
else
set result = 'CREDIT APPROVED';
end if;
if (turnDebugDumpsOn = 1) then
datacom dump purchaseAmount, creditAvailable, result,
'CREDITLIMITCHECK CUSTID=' || custId
to pxxsql;
end if;
end limitCheck@
--
-- Use SIMULATE to supply a variable for the INOUT "result" parameter
-- SIMULATE was invented to provide procedure-like functionality to DBSQLPR.
simulate datacom procedure
supplyVarsForCall: begin atomic
declare errorMessage char(80) default 'N/A';
declare result varchar(25) default 'N/A';
-- The default value for SQL variables is NULL
declare sqlstateLocal char(5);
--
-- Note that proc. "creditLimitCheck" could have contained the error handlers
-- directly and returned an error message and sqlstate through the parameters.
declare continue handler for not found
set errorMessage = 'CUSTOMER HAS NO CREDIT RECORD';
declare continue handler for sqlexception, sqlwarning
get diagnostics sqlstateLocal = returned_sqlstate,
errorMessage = message_text;
datacom dump 'VALUES ON ENTRY:', result, errorMessage to pxxsql;
-- Note that SQL variable "result" is being used like a host variable here.
call creditLimitCheck(result, '00001', 999.99, 1);
datacom dump 'VALUES ON EXIT:', result,sqlstateLocal,errorMessage to pxxsql;
end supplyVarsForCall@
--
-- The above procedure, coded using a SELECT rather than a compound statement:
--commit@
-- Does the customer have enough credit to purchase an ordered part?
create procedure creditLimitCheckV2
(inout result varchar(80), in custId char(5),
in purchaseAmount decimal(15,2))
language sql
select case
when creditMax-creditUsed >= purchaseAmount then
'CREDIT APPROVED'
else
'CREDIT LIMIT EXCEEDED'
end
into result
from credit
where credit.custId =
cast(creditLimitCheckV2.custId as numeric(5))@
--
-- Use SIMULATE to supply a host variable for the INOUT "result" parameter
simulate datacom procedure
supplyVarsForCall: begin atomic
declare errorMessage, result char(80) default 'N/A';
-- The default value for SQL vars is NULL
declare sqlstateLocal char(5);
declare continue handler for not found
set errorMessage = 'CUSTOMER HAS NO CREDIT RECORD';
declare continue handler for sqlexception, sqlwarning
get diagnostics sqlstateLocal = returned_sqlstate,
errorMessage = message_text;
datacom dump 'VALUES ON ENTRY:', result, errorMessage to pxxsql;
call creditLimitCheckV2(result, '00001', 999.99);
datacom dump 'VALUES ON EXIT:', result,sqlstateLocal,errorMessage to pxxsql;
end supplyVarsForCall@
/*
Sample Procedure 2
In this example, SQL names that do not consist of delimited identifiers are converted to uppercase by SQL. Also, notice that the at sign (@), used in conjunction with the TERM=@ parameter in DBSQLPR, enables DBSQLPR to skip over semicolons embedded in statements and instead recognize the @ as the end of the statement. For more information about TERM=, see DBSQLPR Options. For z/OS and z/VSE JCL samples, see Example JCL.
Note: Use the following as a guide to prepare your JCL. The JCL statements are for example only. Lowercase letters in a statement indicate a value you must supply. Code all statements to your site and installation standards.
//jobname See the note above.
// REGION=1024K
//JOBLIB your DD statements go here
-- Regarding TERM=@ in the following, DBSQLPR normally uses a semicolon to detect
-- the end of an SQL statement, but SQL statements, as sub-statements within
-- compound-statements, are also required to be terminated with semicolons.
-- This presents a challenge for DBSQLPR, because DBSQLPR must detect the end of a
-- compound-statement (without the problem-prone addition of an extraneous SQL parser
-- outside SQL-proper) despite the appearance of additional semicolons. We circumvent
-- this inherent ambiguity by using the TERM=@ specification to provide a different
-- terminating character for the compound-statement. The specification applies to
-- the entire input file (SYSIN).
//SQLEXEC EXEC PGM=DBSQLPR,PARM='AUTHID=SYSADM,TERM=@,prtwidth=500'
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//STDOUT DD SYSOUT=*
//OPTIONS DD *
inputwidth=80
/*
//SYSIN DD *
-- Set up tables and data for use by the procedure
drop procedure orderReview CASCADE@
drop table errTable@
drop table newOrder@
drop table warehouseToDo@
commit@
create table errTable
(orderId int,
errCountCol int,
sqlStateCol char(5),
errMsgCol varchar(128))@
create table newOrder
(orderId int,
custId numeric(10),
creditReqAmt decimal(15,2),
status char(25))@
create table credit
(custId numeric(10),
creditMax decimal(15,2),
creditUsed decimal(15,2))@
create table warehouseToDo
(orderId integer,
instructions char(500),
whenInstructed timestamp)@
commit@
delete from neworder@
delete from credit@
insert into newOrder values(1, 1, 501.01, 'NOT REVIEWED')@
insert into newOrder values(2, 2, 902.02, 'NOT REVIEWED')@
insert into credit values(1,750.01, 100.01)@
insert into credit values(2, 750.02, 100.02)@
commit@
--
CREATE PROCEDURE ORDERREVIEW
(out ordersAccepted int, out ordersRejected int)
language sql
-- The rest of this procedure consists of a single compound statement
-- labelled "orderReviewMain".
orderReviewMain:
begin atomic
-- Non-delimited variables will be uppercased so ABC matches abc.
declare statusLocal char(25);
declare creditReqAmt, creditAvail decimal(15,2);
declare custId numeric(10);
declare errCount, orderId, I_want_to_leave_the_loop_NOW,
I_want_to_re_loop_NOW int;
declare errMsg varchar(128);
declare sqlStateLocal char(5);
--
declare orderCrs cursor for
select orderId, custId, creditReqAmt
from newOrder
where status = 'NOT REVIEWED';
-- Conditions and Condition Handler(s)
--
-- This condition is signaled when an order is approved.
declare orderApproved condition;
--
-- Handlers may consist of a compound or non-compound statement.
-- Non-compound handler examples:
--
declare continue handler for orderApproved
insert into warehouseToDo values (orderReviewMain.orderId,
'ORDER APPROVED. BEGIN MAKE-READY',
current timestamp);
declare undo handler for sqlstate '44444', sqlstate value '55555'
set I_want_to_leave_the_loop_NOW = 1;
declare exit handler for sqlstate '66666'
set sqlStateLocal = '66666';
declare continue handler for not found
set sqlStateLocal = '02000';
--
-- Compound handler example:
-- Note the use of "errCount" even though it's declared outside the
-- scope of the errHandler1 compound statement. If errHandler1 had an
-- errCount variable, we'd have to specify "orderReviewMain.errCount"
-- to reference the variable from the outer compound statement. Also,
-- a locally declared copy of "errCount" would have to have its' value
-- reset on each entry to the handler, hence the "outer" reference.
--
-- Note that there ARE duplicate "errMsg" variables in other scopes
-- but we'll resolve to the one inside the error handler's context.
--
-- SQL exceptions '44444', '55555', and '66666' would trigger the
-- handlers for those specific SQLSTATEs. But all other SQLSTATEs
-- qualifying as SQLEXCEPTIONs will trigger this handler.
--
declare continue handler for sqlexception, sqlstate '12345',
sqlwarning
errHandler1: begin atomic
set errCount = errCount + 1;
get STACKED diagnostics
sqlStateLocal = returned_sqlstate,
errMsg = message_text;
insert into errTable values
(orderId,errCount,sqlStateLocal,errMsg);
end errHandler1;
-- Main Logic Start --
set errCount = 0;
set ordersAccepted = 0;
set ordersRejected = 0;
set I_want_to_leave_the_loop_NOW = 0;
set I_want_to_re_loop_NOW = 0;
delete from errTable;
set sqlStateLocal = '00000';
-- Loop thru new orders and approve or reject each.
open orderCrs;
start_while:
while (sqlStateLocal = '00000') do
--
-- Note references to SQL variables
fetch orderCrs into orderId, custId, creditReqAmt;
-- Note usage of SQL variable orderWatchMain.custId in query
select creditMax-creditUsed into creditAvail
from credit
where credit.custId = orderReviewMain.custId;
if (creditReqAmt > creditAvail) then
set statusLocal = 'CREDIT LIMIT EXCEEDED';
set ordersRejected = ordersRejected + 1;
else
set statusLocal = 'CREDIT APPROVED';
update credit set creditUsed = creditUsed+creditReqAmt
where custId = orderReviewMain.custId;
set ordersAccepted = ordersAccepted + 1;
SIGNAL orderApproved;
end if;
-- Statement included only to demonstrate usage
if (I_want_to_re_loop_NOW = 1)
then iterate start_while;
end if;
update newOrder set status = statusLocal
where current of orderCrs;
-- Statement included only to demonstrate usage
if (I_want_to_leave_the_loop_NOW = 1)
then leave start_while;
end if;
end while end_while_label datacom looplimit 100;
close orderCrs;
end
orderReviewMain@
COMMIT@
-- Confirm contents
SELECT * from newOrder@
DELETE from errTable@
drop procedure supplyHostVarsForCallStmt@
commit@
SIMULATE DATACOM PROCEDURE
supplyHostVarsForCallStmt: begin atomic
declare ordersAccepted, ordersRejected int;
CALL ORDERREVIEW(ordersAccepted, ordersRejected);
datacom dump ordersAccepted, ordersRejected to pxxsql;
end supplyHostVarsForCallStmt@
--
-- Confirm results
SELECT * from newOrder@
select * from errTable@
select * from warehouseToDo@
/*
|
Copyright © 2014 CA.
All rights reserved.
|
|