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.

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

Other Insert Patterns:

	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

Other Selection Patterns:

	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;

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.