DAO (Data access object)

With OraTool you can generate Pl/SQL to make CRUD operation on Table:

DAO_Select: yuo can get record from key
DAO_Insert: for insert record into table
DAO_Delete: for delete record from table
DAO_Update: for update record into table
DAO_Log: to trace record (dbms_output). Useful for debug.

How to use DAO

We have table Users to store system user data and a table Log_Access for trace system access:

-- Create Table
Create Table Users
      (Iduser NUMBER(38,0),
       Username VARCHAR2(10),
       Password VARCHAR2(10),
       Pagesvisited NUMBER(38,0))
tablespace USERS
Storage (initial 64K) ;

-- Primary Key on table Users
Alter Table Users Add Constraint USERS_PK Primary Key (IDUSER);

-- Create Table
Create Table Log_Access
      (Idlog NUMBER(38,0),
       Iduser NUMBER(38,0),
       Datetime DATE,
       Page VARCHAR2(100))
tablespace USERS
Storage (initial 64K) ;

-- Primary Key on table Log_Access
Alter Table Log_Access Add Constraint LOG_ACCESS_PK Primary Key (IDLOG);

-- Foreign Key on table Log_Access
Alter Table Log_Access Add Constraint LOG_ACCESS_FK1 Foreign Key (IDUSER)
references USERS;

Now we can create PL/SQL script to calculate statistics and save result into Users table.

First create DAO Packaget with DAO Gen button and then:

Declare
  Cursor cUsers is
    Select *
    From Users;

Begin
  For rUsers in cUsers Loop
    -- Calculate the number of pages visited
    Select count(*)
    Into rUsers.PagesVisited
    from Log_Access
    where Iduser = rUsers.Iduser;
    
    -- Update Users using Dao
    Dao_update.Users (rUsers, rUsers);
    
    -- Trace rUsers record
    Dao_Log.Users (rUsers);
  End Loop;
End;