Introduction to PL/SQL Series 1

Author David Nishimoto
Introduction to PLSQL (series 1)
PLSQL Haven

Buy Oracle PLSQL

I began professional programming using Oracle PLSQL in 1998, as part of, an centralized budget project. The budget project was part of an Y2K project. Its purpose was to control budget appropriations, through out the world, for this particular organization. Contractors office space included: a table, two computers, and our chairs. From our work-area, the view was great for we sat infront, of two windows, overlooking the city. The machines contained software, such as, visual basic, SQL Plus, and windows NT. All the necessary software, to build a world wide enterprise software managing the organizations budget. With over a million records, Oracle was the preferred database storage software. I provide the best volume managment and could be designed nicely to meet centralization issues. Time was ticking and the software needed to be built before the turn of the century. There is nothing better than the challenge to build a new software system. The adrenal was plumping and designs were being materialized into software. The big picture needed to be built. So starting on white boards, the team talked and sketched diagrams showing how the data was to be stored.

The buzz word, "relational databases" emmerged in the conversation. Jargon like rows, columns, primary keys, foreign keys, one to many, many to many, joins, and constraints were exchanged between developers and database administrator. In a relational database, the data is stored as tables. Tables contain fields describing the type of date, container name of the data, size, decimal precision, and required or not required constraints. Technically, a table is an object residing in a schema. A schema can contain other objects like indexes, views, snapshots, functions, procedures, packages, sequences, and clusters.

CREATE TABLE my_table_name( 
{field1} VARCHAR2(10) NOT NULL, 
{field2} NUMBER(10) ) 
PCTFREE 30 PCTUSED 60 TABLESPACE a_tablespace_name 
STORAGE ( INITIAL integer NEXT integer ); 

Wew! I have a table that needs to be created in a schema. Ok, so what type of information does the database administrator need. Well here how the table was added to the schema.

1) Define each field name, field type, size, required for the tables 
2) Define and create the primary key constraints 
3) Define and create the foreign key constraints 
4) Define and create the indexes 

There is alot of planning. The data needed to analyzed to determine what data kept the field information kept the data records unique. This field was called the primary key. The primary key is always unique and provides a field or fields associated creating the primary key index. The foreign key is a field reference into another table. The foreign key points back to the primary key of another table. Foreign keys prevent dependant tables from added orphan records. Orphan records are bad because they jeapordize the integrity of your data. Bad data makes the system unreliable. An example of an primary key and foreign key relationship would exist between an employee table and an address table. The primary key in the employee table could be the employee identification number and the foreign key in the address book is the employee identification number linking back to the employee table.

Primary Key Constraint 
ALTER TABLE employee ADD 
( CONSTRAINT EMPLOYEE_EMPLOYEEID_PK PRIMARY KEY (EMPLOYEEID) 
USING INDEX 
TABLESPACE a_tablespace_name 
PCTFREE 10 STORAGE 
(INITIAL 20K NEXT 20K PCTINCREASE 0) 
) 
Foreign Key Constraint 
ALTER TABLE address 
ADD ( CONSTRAINT (employeeid_pk) 
FOREIGN KEY (employeeid ) REFERENCES employee ( employeeid ) 

So my constraints are created and data integrity looks good. The next problem was performance since the database tables contained millions of records. During query extraction of data full table scans over a million records was very time consuming. The user didn't want to wait for over a few seconds to receive their data. I was important for the user to have real time response when they pressed a button. The database adminstrator analyzed what fields had criteria applied to them and created an index. An index is a object containing only fields that are being search. A index contains a pointer to the physical table records. So searching an index for a data match is much faster.

CREATE {UNIQUE} 
name_idx 
ON employee ( name ) 
PCTFREE 10 TABLESPACE (table_space_name) 
STORAGE (INITIAL 20K NEXT 20K PCTINCREASE 0); 

If UNIQUE is specified the key in the index is force to be unique otherwise the index is assumed to be non-unique. Wow! Queries ran alot faster. SQL Plus has commands that measure query execution time in milliseconds.

In summary, planout and sketch, what your data will look like. Decide how the data will be stored. Apply rules of normalization (www.listensoftware.com/ORACLE.HTML) to move data around so rules of a relational database can be applied to your data. Once your table schema design has been completed start writing oracle scripts to create your schema. Try loading your data. If constraint problem occur you can enable and disable your constraints and indexes. Its more efficient to load your data into the table with index and constraints disabled. After the data has been loaded then enable your indexes and constraints.

Next week, I'll be describing Select, Insert, Update, and Delete; all the CRUID about data manipulation. David Nishimoto LSS