A Large Order of PL/SQL Please, Easy on the PL by Alan Beaulieu, coauthor of Mastering Oracle SQL 12/13/2002

PL/SQL is a great language. In the eight years that I have been designing and implementing Oracle databases and associated applications, I have used PL/SQL to great advantage in every single project. The problem I have with PL/SQL is not the language itself, but the ease with which a programmer can dive down to the row-level when set manipulation might prove more efficient. Indeed, with the various enhancements to the SQL language made by Oracle over the past five years, complex business logic that would have required a combination of SQL and a procedural language can now generally be handled by SQL alone. To illustrate, we will use PL/SQL to implement a specific business need, but we will attack the problem from two different angles: the business logic perspective, whereby a series of steps are performed against a set of data in order to incorporate multiple business rules, and the data perspective, in which data sets are manipulated in order to absorb multiple business rules simultaneously.

Here's the problem: for various purposes (such as client billing, risk exposure), a financial services firm needs to track the number of trades executed in each country. Depending on the type of security being traded, the data might be tracked either by country code or by currency code. An operational data store tracks the number of trades each day by account, transaction type, and country/currency, and our assignment is to roll up the transaction counts each month to the country level, using a translation table to convert currency codes to country codes [1]. Additionally, transaction types AB and XY always get rolled into the US bucket, regardless of the country/currency stored in the tables. Here's the schema for our problem:

Given the above schema, our task is to roll up the txn_count column for each country from the Account_Currency_Txns and Account_Country_Txns tables and insert rows into the Country_Txn_Summary table. The next two sections will follow the thought processes of two programmers assigned to solve this problem: one coding from the business logic perspective, and the other from the data perspective.

Business Logic Perspective

We need to add each record from the two transaction tables to the appropriate summary bucket. Therefore, for each transaction table, we will sum the number of transactions by country and transaction type, check to see if the transaction type requires that the country code be remapped to US, and then add the transaction count to the appropriate bucket in the Country_Txn_Summary table. To facilitate reuse, we will create two stored procedures within a PL/SQL package: one procedure that loops through each of the transaction tables, and another private procedure that updates the appropriate row in the summary table:

/* package definition */
create or replace package pkg_monthly_summary as
  procedure summarize_txn_data(v_smry_start_date in date);
end pkg_monthly_summary;

/* package body */
create or replace package body pkg_monthly_summary as
  /* forward declaration of private procedure */
  procedure update_txn_bucket(v_cntry_cd in varchar2, 
    v_smry_start_date in date, v_txn_type in varchar2,
    v_txn_count in number);

  /* procedure to loop through transaction records */
  /* and add to appropriate country bucket         */
  procedure summarize_txn_data(v_smry_start_date in date) is
    /* country data cursor */
    cursor cntry_cur(cv_smry_start_date in date) is
    select cntry_cd, txn_type, sum(txn_count) tot_txns
    from account_country_txns
    where exec_date >= cv_smry_start_date
      and exec_date < add_months(cv_smry_start_date, 1)
    group by cntry_cd, txn_type;

    /* currency data - map to country via country_currency table */
    cursor crrncy_cur(cv_smry_start_date in date) is
    select cc.cntry_cd cntry_cd, act.txn_type txn_type, 
      sum(act.txn_count) tot_txns
    from account_currency_txns act, country_currency cc
    where act.exec_date >= cv_smry_start_date
      and act.exec_date < add_months(cv_smry_start_date, 1)
      and act.crrncy_cd = cc.crrncy_cd
    group by cc.cntry_cd, act.txn_type;
  begin
    /* add country records to buckets */
    for cntry_rec in cntry_cur(v_smry_start_date) loop
      update_txn_bucket(cntry_rec.cntry_cd, v_smry_start_date, 
        cntry_rec.txn_type, cntry_rec.tot_txns);
    end loop;

    /* add currency records to buckets */
    for crrncy_rec in crrncy_cur(v_smry_start_date) loop
      update_txn_bucket(crrncy_rec.cntry_cd, v_smry_start_date, 
        crrncy_rec.txn_type, crrncy_rec.tot_txns);
    end loop;

    commit;
  exception
    when OTHERS then
      rollback;
      raise_application_error(-20001, 'Oops');
  end summarize_txn_data;

  /* procedure to update appropriate country summary bucket */
  procedure update_txn_bucket(v_cntry_cd in varchar2, 
    v_smry_start_date in date, v_txn_type in varchar2,
    v_txn_count in number) is
    v_targ_cntry_cd varchar2(4);
  begin
    /* map all AB and XY transactions to country US */
    if v_txn_type = 'AB' or v_txn_type = 'XY' then
      v_targ_cntry_cd := 'US';
    else
      v_targ_cntry_cd := v_cntry_cd;
    end if;

    /* attempt to update existing bucket */
    update country_txn_summary
    set txn_count = txn_count + v_txn_count
    where cntry_cd = v_targ_cntry_cd
      and year = to_char(v_smry_start_date, 'YYYY')
      and month = to_char(v_smry_start_date, 'MM');

    if SQL%ROWCOUNT = 0 then
      /* bucket doesn't exist yet, so create it */
      insert into country_txn_summary (cntry_cd, 
        year, month, txn_count)
      values (v_targ_cntry_cd, to_char(v_smry_start_date, 'YYYY'),
        to_char(v_smry_start_date, 'MM'), v_txn_count);
    end if;
  end update_txn_bucket;
end pkg_monthly_summary;

To summarize data for the month of October, 2002, we would execute the following:

pkg_monthly_summary.summarize_txn_data(to_date('01-OCT-2002','DD-MON-YYYY'))

Pages: 1, 2

Next Page