David Nishimoto
Introduction to PL/SQL (series 2)
Insert,select,update,delete

Welcome back from last week. My story start on a hot July day. Remember we've setup camp and are starting to build our Oracle application. A semi cool breeze is flowing through the room on the twenty fifth floor. Its very hot and the sounds of cars driving by could be heard in the distance.

The Oracle DBA has given the developers a username, password, and instance name to connect to the new oracle instance, he has built. Racing to open SQL Plus, I enter in the username, password, and instance name as my finger rapid type out the security characters. All necessary SQL Net configurations have been setup prior to arrival on the development machines. There is a brief delay and a SQL prompt appears. At the SQL prompt, I'm excited and anticipating seeing the tables we've designed and the DBA has created. So I enter in the following command:

SQL> select * from tables;
employee
address
Cool, there my tables
Ok, I need to see the database fields.
SQL> DESCRIBE employee
	SQL Plus lists the field name, type, length, and data constraint info
	...
SQL> DESCRIBE address
	SQL Plus lists the field name, type, length, and data constraint info
	...

There is a very empowering feeling generated when realizing the data schema was ready for development. The tables created and configured to store data. The most efficient way to load an Oracle table is to use Oracle Loader. The scripts are simple and the tool transacts inserts using batch inserts. Its very fast. But before ramming the data into the tables, I needed to take a test drive first.

Insertion

How do we get data into the database? SQL has a very handy command called "insert". A safe way to use the insert command is the first type the field names and then type of the field values. Each field name must have a field value.

	Here's a simple list of rules for inserting data:
	1. Single apostrophes in a string phrase must be replaced with double single apostrophes
	' -> ''
	2. Date Field values must be formated in the NLS_DATE format when using the
	TO_DATE function.
	3. Data must be the same data types
		a. character data can not be stored in a numeric field
SQL>Edit sql_script
	insert into employee(id,first,mi,last) values (1,'David','S','Nishimoto');
	insert into address(addressid, employeeid, street,city,state,zip,lastupdated) 
	value (1,1,'abc street','abc','ut',84404,TO_CHAR(SYSDATE,'mm/dd/yyyy'));
SQL>@sql_script
SQL>1 row inserted
SQL>1 row inserted

	Pattern 1: Insert-Select (records matching a selection criteria can be inserted into
	another a table with matching fields)
	insert into employee
	(
	id,
	first,
	mi,
	last
	)
	select 
	id,
	first,
	mi,
	last
	from
	employee_load
	where
	id<1000
	;

Selection

How do I view at my data? SQL Plus was originally a report generator. Its still possible to create a report using the SQL Plus command language. The power of a relational database comes from the design. Relational databases are designed to be easy to retrieve data. SQL itself represents a mathematic grammer representing insertions, unions, compliments of set theory. Don't worry things won't get that rigorous. However, there are some very comprehensive database theory books on the market. Here's something simple.

SQL>Set linesize 1000
SQL>Set pagesize 60
SQL> Column id heading 'Employee Id' format 999
SQL> Column last heading 'Last Name' format a40 truncate
SQL>select id, last from employee;
The output will be two headings: "Employee Id" and "Last Name" followed by another row
with the values 1 and Nishimoto formatted under the appropriate columns..
SQL Plus Commands
See http://www.listensoftware.com/sqlplus.html for a listing of SQL Plus commands.
Select Joins:
http://www.listensoftware.com/plsql_joins.htm

	Pattern 1: In (equivalent to "and" criteria)
		select * from employee where last in('Nishimoto','Smith');
	Pattern 2: Between
		select * from employee where id between 1 and 5;
	Pattern 3: Exists
		select * from employee A
		where
		exists
		(
			select '' from address B
			where A.id=B.employeeid
		)
		Return all employee records which have an address record.
	Pattern 4: Not Exists
		select * from employee A
		where
		not exists
		(
			select '' from address B
			where A.id=B.employeeid
		)
		Return all employee records not having an address record.
	Pattern 5: And, or, not
		select * from employee where
		not (id=1);
		Return all employee records except one with an id of 1
		select a.first,a.last,b.city from employee A, address B
		where	a.id=b.employeeid;
		Return employee and address information where
		an employee has an address record.
	Pattern 6: In Subquery
		select * from employee where
		id in
		(
		select employeeid from address
		);
		Returns all employees with an address record	
	Pattern 7: Create a View
	Create view employee_vw as select * from employee;

Update

The update command is probably least most understood of the sql commands. First, I personally like to test a select statement with the criteria before using it in a update statement. Why? Should a mistake occur during execution or criteria creation, the rollback statement has the capability to reverse changes, assuming a commit has not occurred. The commit command moves temporary data to the physical database. Rollback reverse changes stored in the rollback segment.

Update provides a method to change field values in a table. Changes can be applied simulateous to all fields or a selection of fields and to all table rows or a selection of table rows. Note, an update field can receive only one value. However, this value can be returned by a select statement, function, or an literal.

	Pattern 1: Field value being assigned a value by a sql
	Update Statistics set Visits = 
	(select COUNT(Patient_Id) from schedule 
	where as	
	billing_confirmed = 1 and schedule_date=#1/1/97#) 
	Pattern 2: Field value being assigned a value by a function
	Update employee set name=fncFullName(first,mi,last)
	where id<100;
	Pattern 3: Using the Exists Criteria
	Update employee A set hasAddressFlag=1
	where exists
	(
		select '' from address B
		where A.id=B.employeeid
	);
	Pattern 4: Update a view
	update employee_vw
	set first='Mr. David'
	where id=1;

Delete

The delete command remove records from a table. Delete moves records to the rollback segment enabling rollback capability. Performance improvements can be realized by using the truncate command to bypass the rollback segment. Truncate will permenantly delete the record and no rollback capability will exist.

	Delete can only remove records from one table at a time.
	Pattern 1: where
		delete from employee
		where id=1
	Pattern 2: delete from a view
		delete from employee_vw
		where id=1

Commit and Rollback

SQL>Rollback 
Reverse update or delete changes to a table 	
SQL>Commit
Force the rollback segment to update the physical record blocks on the instance.

In summary, those July days turned into cold winter days. I worked from early in the morning until late at night; leaving the office building, I watch as families pass through busy intersections on my way to the car. It was late and the street was badly lite. I was anxious to return home to my family. It had been a good day and the application was coming along nicely. PL/SQL was really cool. I kept thinking about the power of SQL as I walk up the hill to my car and drove home. The man who designed and created sql was brilliant and it had made a profound connection with me. Next Week, I'll cover transaction processing.