Introduction to PL/SQL (series 2)

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

PLSQL Haven

Buy Oracle PLSQL

Welcome back from last week. My story starts 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.

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.