| Robert 2007-10-11, 9:55 pm |
| On Thu, 11 Oct 2007 21:11:04 -0300, Clark F Morris <cfmpublic@ns.sympatico.ca> wrote:
>On Thu, 11 Oct 2007 16:59:45 +1300, "Pete Dashwood"
><dashwood@removethis.enternet.co.nz> wrote:
>
>
>How you use stored procedures? How difficult would it be to change
>existing embedded SQL to use the stored procedures?
It's easy. Suppose your program now reads:
01 customer-id pic x(14).
01 customer-name pic x(50).
EXEC SQL WHENEVER SQLERROR DO
DISPLAY 'SQL Error on ' customer-id space SQLCODE space SQLERRMC
STOP RUN
END-EXEC.
EXEC SQL
SELECT customer_name INTO :customer-name from customers
WHERE customer_id = :customer-id
END-EXEC.
The simplified version will read:
EXEC SQL WHENEVER SQLERROR DO
DISPLAY 'SQL Error on ' customer-id space SQLCODE space SQLERRMC
STOP RUN
END-EXEC.
EXEC SQL
CREATE OR REPLACE PACKAGE customer_package AS
PROCEDURE get_customer_name(
customer_id_hv IN varchar2,
customer_name_hv OUT varchar2)
END customer_package;
END-EXEC.
EXEC SQL
CREATE OR REPLACE PACKAGE BODY customer_package AS
PROCEDURE get_customer_name(
customer_id_hv IN varchar2,
customer_name_hv OUT varchar2)
IS
BEGIN
SELECT customer_name INTO customer_name_hv FROM customers
WHERE customer_id = customer_id_hv;
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in get_customer_name');
RAISE;
END customer_package;
END-EXEC.
EXEC SQL
EXECUTE
BEGIN
customer_package.get_customer_name(:customer-id, :customer-name);
END;
END-EXEC.
or
EXEC SQL
CALL customer_package.get_customer_name(:customer-id, :customer-name)
END-EXEC.
I trust you'll agree the improvement is dramatic.
>Is there a net
>increase in CPU or other resource used?
It has to parse and execute the call, find the package/procedure, check permissions on
both, typecast bind variables into PL/SQL, dispatch the procedure, (run the procedure,
same speed as before), check for error handling inside the procedure, exit the procedure,
(return to Cobol, same as before).
>The idea sounds good and
>might get around some of the frustrations I have had in my limited use
>of SQL in COBOL.
It will add to your frustrations after they remove the package setup from your program,
because you will not be able to see what get_customer_name is doing and your text editor
won't be able to link to it.
On the plus side, when get a support call at 3am, you can type this to see what the
program sees (if using SQLPLUS):
variable customer_name_hv varchar2
execute customer_package.get_customer_name('12345', :customer-name_hv)
print customer_name_hv
Followed by:
SELECT text FROM user_source WHERE name like '%get_customer_name'
You no longer have to type long-winded SQL like this:
select * from customers where customer_id = '12345'
|