REM
REM header: objdemo.sql
REM
Rem Copyright (c) 1987, 1997, 1998 by Oracle Corporation
Rem NAME
Rem    OBJDEMO.SQL
Rem  FUNCTION
Rem    Demo program based on the banking application which explains
Rem    PL/SQL Object Types,Collections and Records features.
Rem
Rem  REQUIREMENTS
Rem
Rem   o ORACLE database user SCOTT exists with required demo tables  
Rem   
Rem   o Run exampbld.sql before running this for creating the schema.
Rem
Rem   o Package DBMS_OUTPUT is installed for the database.
Rem
Rem  NOTES ON EXECUTING THIS DEMO PROGRAM
Rem
Rem   o Use SVRMGRL for running this demo program
Rem       (o   It won't work with SQLPLUS)
Rem   o Connect as SCOTT/TIGER and run objdemo.sql 
Rem   
Rem     CONNECT SCOTT/TIGER 
Rem     @objdemo.sql
Rem
Rem  MODIFIED
Rem     sabburi    06/15/98 - create schema -> exampbld.sql
Rem     anataraj   12/19/97 - merging from 804
Rem     mahsubra.in    09/19/97 - CREATION
Rem
/*
** ##############################################################
**
**                  DESCRIPTION OF DEMO EXAMPLE
**
** ##############################################################
** This following banking example illustrates the PL/SQL object features in 
** 8.0 PL/SQL. 
**  
** In this example, the following services are available, namely,
**    - Account opening.
**    - Deposit.
**    - Withdrawal.
**    - Account closing.
**    - Current Balance Query
**    - Verify Account
**  
** In this example we make the following assumptions: 
** 
** 1) A customer can have the following types of accounts: 
**    a) Account_Type_1
**    b) Account_Type_2
**    c) Account_Type_3
** 
**    The facilities offered by each account is given below:
**  
** 2) The customer can have a single account or multiple accounts.  
**    For e.g  
**    A customer can have a account of Account_type_1 only  
**    or  
**    an account of Account_type_1 and an account of Account_type_2 
**    or  
**    an account of Account_type_1,account of Account_type_2 ,account of 
**    Account_type_3. 
**  
**  
** 3) The account number for each type of account will be different. i.e, 
**    If a customer has an account of Account_type_1 and an account of type
**    Account_type_2, the account number for the Account_type_1 account 
**    will be different from the account number of the Account_type_2. 
**  
** 4) Description of account types 
**    a) Account_Type_1 
**       1) Minimum balance is 250. While opening the account, the customer 
**          has to deposit this amount. 
**       2) Withdrawals will not be allowed if the minimum balance goes  
**          below 250. 
**       3) At the time of closing the account, the balance 
**          will be paid to the customer. 
**     
**    b) Account_Type_2 
**       1) No restrictions on minimum balance.  But it should not go below  
**          0 or -ve.
**
**    c) Account_Type_3
**
**       1) At the time OF Closing the account, the customer will pay 
**          the balance
**     
**  
** ##############################################################
**
**                OBJECT TYPES AND THEIR RELATIONSHIP 
**
** ##############################################################
**
**                customer   
**                 /   \     
**                /     \    
**               /       |
**           person    account
**            /        || | | 
**           /        /  \ \ \___________________________
**        address    /    \ \                            \
**                  |      \ \_______________             \
**                  |       \                \             \
**                  |        \                \             \
**                  |         \                \             \
**                  |          \                \             \
**          v_account_type  account_type_1  account_type_2  account_type_3
**                              (REF)          (REF)          (REF)
** 
*/

/*
** ##############################################################
**
**                       CODING STYLE USED 
**
** ##############################################################
**
**      1. Uppercase is used for PL/SQL keywords and lower case for user 
**         defined items.
**      2. Naming convention followed for user defined types:
**         Data Structure Name       Prefixed With
**         ---------------------------------------------
**         Object Type names           o_ 
**         Varray Names                v_ 
**         Nested Table Types          n_ 
**         Object Tables               ot_
**         Relational Tables           rt_
**         Nested Table                nt_ 
**         Local Variables             l_
*/
         
set echo off

CREATE OR REPLACE PROCEDURE run_objdemo is
  l_address o_address;
  l_account_type VARCHAR2(5);
  l_o_account o_account;
  l_limit NUMBER;
  l_ssn VARCHAR2(10);
  l_amount NUMBER;
  l_name VARCHAR2(50);
  l_gender CHAR;
  l_date DATE;
  l_actnum1 INTEGER;
  l_actnum2 INTEGER;
  l_actnum3 INTEGER;
  l_actnum4 INTEGER;
  l_actnum5 INTEGER;
  l_actnum6 INTEGER;


BEGIN

  -- opening procedure.

  DBMS_OUTPUT.PUT_LINE('--- Opening an account for James ---');

  l_o_account := o_account(NULL,v_account_type(NULL),NULL,NULL,NULL);
  l_name := 'James';
  l_date := to_date('20-AUG-77','dd-MON-yy');
  l_gender := 'M';
  l_address := o_address('A1234','SanthomeStreet','Redwood Shores','California',
        '94065'); 
  
  l_ssn := 'A1234';
  l_account_type := 'AT1';
  l_amount := 500;
  

  DBMS_OUTPUT.PUT_LINE('--- Opening an account of type AT1 for James ---'); 

  l_actnum1 := l_o_account.open_account(l_ssn,l_account_type,l_amount,
            l_name,l_date,l_gender,l_address); 
  DBMS_OUTPUT.PUT_LINE('');
    
  l_ssn := 'A1234';
  l_account_type := 'AT2';
  l_amount := 500;

  DBMS_OUTPUT.PUT_LINE('--- Opening an account of type AT2 for James --- '); 

  l_actnum2 := l_o_account.open_account(l_ssn,l_account_type,l_amount,
                null,null,null,null); 
 
  DBMS_OUTPUT.PUT_LINE('');

  l_ssn := 'A1234';
  l_account_type := 'AT3';
  l_amount := 500;

  DBMS_OUTPUT.PUT_LINE('---  Opening an account of type AT3 for James ---'); 

  l_actnum3 := l_o_account.open_account(l_ssn,l_account_type,l_amount,
            null,null,null,null); 
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('---  Deposit 1000 to James account- AT1 ----'); 

  l_o_account.deposit_amount(l_actnum1,1000);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('---  Deposit 4000 to James account- AT1 ----'); 

  l_o_account.deposit_amount(l_actnum1,4000);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('---  Deposit 2000 to James account- AT2 ----'); 

  l_o_account.deposit_amount(l_actnum2,2000);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('--- Withdraw 500 from James account- in AT2 ---');

  l_o_account.withdraw_amount(l_actnum2,500);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('--- Withdraw 1500 from James account- in AT1 ---');

  l_o_account.withdraw_amount(l_actnum1,1500);
  DBMS_OUTPUT.PUT_LINE('');
 

  DBMS_OUTPUT.PUT_LINE('---  Deposit 2500 to James account- AT1 ----'); 

  l_o_account.deposit_amount(l_actnum1,2500);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('--- Withdraw 1500 from James account- in AT3 ---');

  l_o_account.withdraw_amount(l_actnum3,1500);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('--- Withdraw 1750 from James account- in AT1 ---');

  l_o_account.withdraw_amount(l_actnum1,1750);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('---Query for the current balance in James account---'); 
  l_o_account.current_balance(l_actnum1);

  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('--Query the transaction details of James-----');

  l_o_account.trans_details(l_ssn);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('--- Closing the account for James ---'); 
  l_o_account.close_account(l_actnum1);
  DBMS_OUTPUT.PUT_LINE('');

  l_name := 'John';
  l_date := to_date('20-AUG-78','dd-MON-yy');
  l_gender := 'M';
  l_address := o_address('B7896','Concurse Drive','Belmont','California',
                 '94065'); 
  
  l_ssn := 'B7896';
  l_account_type := 'AT2';
  l_amount := 2000;

  DBMS_OUTPUT.PUT_LINE('--- Opening an account of type AT1 for John ---'); 

  l_actnum1 := l_o_account.open_account(l_ssn,l_account_type,l_amount,
                  l_name,l_date,l_gender,l_address); 

  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('---  Withdrawing from John account ---'); 

  l_o_account.withdraw_amount(l_actnum1,1000);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('---  Depositing 3000 in John account ---'); 

  l_o_account.deposit_amount(l_actnum1,3000);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('----Details for John----');
  l_o_account.trans_details('B7896');

END;
/
show errors

spool dm024.lis

set serveroutput on

EXECUTE run_objdemo;

spool off
