Stored Procedure/Function

[timmbo]

Hi All,

I am very new to PL/SQL. I am attempting to write my first procedure/function. I want to create a procedure I can call sending the results to a 3rd party application. Am I on the right track? Below is what I have sofar. Any help/suggestions would be most appreciated.

FUNCTION summaryHistory

/*
|| Author:
||
|| Overview:
||
|| Major Modifications:
||
*/

BEGIN

Select Distinct
summary.asOfDate,
summary.acctNum,
summary.itemCount,
summary.currencyCode,
summary.amount,
codes.baiType,
codes.baiDesc,
bank.abaNum
From bank bank,
pdr_acct_summary summary,
bai_type_code codes
Where codes.baiType = summary.baiType
And bank.abaNum = summary.abaNum
And to_char(summary.asofdate, 'mm/dd/yy') = '12/19/01'
Order By summary.acctNum, summary.asofdate, codes.baiDesc

END summaryHistory;

 

[klegal]

Your select looks fine as far as it goes. I would change your date conversion format to 'mm/dd/rr' or 'mm/dd/yyyy' to be on the safe side for y2k purposes.

You need to bear in mind that a function returns a single value, however. They are useful in returning, for example, totals that require complex selects.

total_accounts_receivable := total_ar_function(up_to_date);

where up_to_date is some sort of an effective date that you pass into your function.

If you wish to return multiple values, as it would appear from your select, you need to use a procedure. Procedures all you to pass multiple values(parameters) in and get multiple values out.

Judging from your code, it looks like you will not only receive multiple values from your select but also multiple records. If this is the case, one of your parameters needs to be a pl/sql table.

My advice to you is to pick up a good pl/sql book(there are dozens of them). A title that I have found extremely useful is Oracle PL/SQL Programming - The Essential Guide for Every Oracle Programmer -- Oracle Press.

 

[timmbo]

Thanks for the advice. Will look into it.