Basic Walkthrough Oracle Part-2

 

Basic Walkthrough Oracle Part-2

This part will provide simple introduction to ORACLE. Some might have some experience with relational database systems, while others might not have even thought about it. In simplest form RDBS (relational data base systems) is a database package that manages relational data. Now relational data is all about properties of an object that needs to be related via any other properties. Suppose we talk about a person. A person has name, last name, phones and may have vehicles. The person may have an income. The person may have expenses. The person may have choices and options. So, all the properties are linked to each other via a person. This way or that way, whatever is linked via another data is usually called relational data. The database that provides the relational data interlink (interface) is usually called relational database.

                So, ORACLE is a Relational Database. Like other databases it also provides few database objects that help us save, organize and retrieve data. Other databases provide some other features and may to some length, while ORACLE has a reputation of providing a reliable database system that can easily be handled. The best part is that now ORACLE Express-C server is free to use an is highly suitable for new learners and matured developers. Let us discuss about some of ORACLE objects.

Tables

                Tables are the first one that I can think of. And mostly used right from simplest database to most complicated system. I our walkthrough we will be dealing with one simple table that will be very simple to understand and you will never need to ask the question “what is a table??” Definition wise a table is a simple collection of data in rows and columns. The first table I ever came across was a dBase table that the dBase guys used to call ‘database’ instead of ‘table’.

Views

                Second object is usually views. The views are actually a logical combination of columns from different tables. These columns usually linked via a link id. Link id is normally a common column among related tables to indicate the object that these columns should relate. From user’s perspective the views are same as tables (not in dBase). You use same command to retrieve data from views as from tables. While we will be using simplest form of DB application; with one table only; we won’t be dealing with views a lot except simple usage example towards the end of series.

Triggers

                Triggers are another object that play a vital role in database life. As the name the triggers get triggered when an event is sensed. Database manager will execute the code in trigger body to perform some actions described in the trigger when an event described in trigger header occurs. Normally triggers are called when insert, update or delete is performed on a table. So, the triggers are created on tables.

Procedures

                Procedures are another part that plays an important role in data manipulation.  These are similar to procedures, sub routines and functions. These procedures are written and when we create these; database compiles and stores on server machine. These are later called to do actions as preplanned in the Procedure Body.

Packages

                Packages are little bigger than procedures. These are almost same as procedures. These packages are also compiled and later bound to the database. Any errors are usually phased out in compile time.

Sequences

                Sequences are some numerical series created by database based on criteria provided by the creator. These helps numbering the data being entered into the database automatically.

                In fact, for introductory walkthrough, you do not need to remember all these. More you learn, more will you discover the objects. All objects re for some purpose; and if you are so simple, you may not need to learn all. But they are there, just in case you need these any time.

Let us start exploring some of these. We will create few basic tables to study behavior of some of objects. Tables will be created quickly and we will study other objects briefly.

To start with let us take a look at ways we can issue commands to Oracle. First method is to use sqlplus in the command window. Go to start>All Programs>Accessories>Command Prompt. Run sqlplus.exe in the command window. SQL commands being entered at command line are not executed until a semicolon is reached.

 

 

You are connected to XE database as you log in automatically.

 

In oracle it is best practice to create a new user for your specific task. The permissions (GRANTs) should be given as per need. For simplicity we will make the user a DBA.

While that is it about setting up, we have oracle database up and running. Let’s have some practice with creating some tables and setup some working system. Let us use SQL Plus for creating tables view and triggers. So, open ‘cmd’ window start sqlplus and login as user1 to execute following commands one by one.

CREATE TABLE USER1.TRANS (     ID      INT NOT NULL,     AMOUNT  DECIMAL(6,2) NOT NULL,     CODE    VARCHAR2(4) );   CREATE TABLE USER1.TOTAL (  WEEKNUM    SMALLINT        NOT NULL,  INCOME     DECIMAL (6, 2)  NOT NULL,  EXPENSE    DECIMAL (6, 2)  NOT NULL,  TIMEUPDATE TIMESTAMP       NOT NULL  );

 

After creating these two tables, you will create a trigger that will update your TOTAL table. See first line for syntax to create trigger when you name the code file as trig1.sql.

 

@trig1.sql

create trigger user1.update_total after insert on user1.trans for each row declare       wknum int;       weekn int; BEGIN       IF :NEW.AMOUNT>0 THEN             select cast(to_char(CURRENT_DATE,'ww')as INT) into weekn from dual;             select (select weeknum from user1.total where weeknum=weekn) into wknum from dual;                               if wknum is not null               then                   if lower(:new.code)='in' then update user1.total set income= income + :new.amount, timeupdate=current_timestamp where weeknum=weekn; end if;                   if lower(:new.code)='out' then update user1.total set expense= expense + :new.amount, timeupdate=current_timestamp where weeknum=weekn;end if ;               else                   if lower(:new.code)='in' then insert into user1.total values(weekn,:new.amount,0,current_timestamp);end if ;                   if lower(:new.code)='out' then insert into user1.total values(weekn,0,:new.amount,current_timestamp);end if ;               end if;       END IF; END; /

                After creating these three objects try entering data into TRANS table as below. You will see that TOTAL table will be populated automatically. This is feature of the database; not the program. The TOTAL table will create one row for each week. When week changes it will generate another row. This table will reflect weekly income and expenses. On regular basis you will enter all transactions into TRANS table as below. Remember to enter code as either ‘in’ for income or ‘out’ for expenses. Any other code will be accepted but will be useless.

insert into USER1.TRANS values (1,123.45,’in’); insert into TRANS values (2,34.40,’out’); insert into TRANS values (3,100,’in’); insert into TRANS values (4,15,’out’); insert into TRANS values (5,.75,’in’);

            And lastly check contents of TOTAL table

 

set lines 100; select * from total ;   WEEKNUM INCOME   EXPENSE  TIMEUPDATE ------- -------- -------- --------------------------      22    224.2     49.4 2009-05-28-16.23.42.000000     1 record(s) selected.

Cool, get the calculator and see if the calculation is correct. Last update stamp only shows the last time when the data was entered.

                In fact, we have created a very useful utility. This can further be customized as required. You will see how triggers can help us validate data while data entry in following sections.