SQL AND PL/SQL

SQL and PL/SQL

For the following explanation I will use the following Table:

TABLE:ROSTER

Attributes/ROW#	NAME	SSN	CLASS	GRADE
1	John	111223333	CS001	87
2	John	111223333	Bio20	92
3	John	111223333	Hist045	75
4	Mary	123456987	Phil089	91
5	Mary	123456987	Art90	71
6	Moe	999876543	Phil1600	98
7	Moe	999876543	Cs1698	97

SQL
An SQL statement consists of three parts:
Select, From, and Where
(Note: Not all three parts must be used. Where is not required)

The Select tells SQL which attribute to select IE: Select NAME
The FROM tells SQL which TABLE or VIEW to Select out of IE: From ROSTER
The Where tells SQL what restrictions to apply IE: Where GRADE > '90'

So we have: Select NAME From ROSTER Where GRADE > '90'
(Notice that in the Where clause the 90 is surrounded by a single quote. This is required because otherwise SQL would look for an Attribute Column called 90. The single quote identifies 90 as a value and not a column.)

This Select, From, Where statement would return the following information:
John
Mary
Moe
Moe
(Notice that 'Moe' is selected twice. This is because his name shows up twice in the Table. To have his Name show up only once you would need to add a modifier to your Select statement IE: Select UNIQUE NAME)

If you want to have more attributes returned, simply add them to your Select statement separated by commas. IE: Select NAME, CLASS From ROSTER Where GRADE > '90'.
Returning:
John Bio20
Mary Phil89
Moe Phil1600
Moe Cs1698

Other SQL commands that will be useful when using SQL-PLUS:
CREATE USER:
CREATE USER name IDENTIFIED BY password;

CREATE TABLE:
CREATE TABLE [user.]table_name
(NAME CHAR(25), SSN NUMBER(9), CLASS CHAR(15));

DROP TABLE:
DROP TABLE table_name;
The same commands can be used for creating and removing views, procedures and functions.

PL/SQL
Procedural Language for Structured Query Language
PL/SQL has a general format:

DECLARE
     declare block
BEGIN
     body block
EXCEPTION
     exception block
END;

The declare block would contain any variables that are desired.
The body block would contain the executable code.
The exception block would contain any error handling you wish to add.
(Note: Only the BEGIN body block END; is required)

Variables
The easiest way to create variables is to declare them explicitly. To do
so simply declare the following:

var_name data_type [constant] [NOT NULL] [[:= || default] initial_value]
example: Student_Name CHAR(25) DEFAULT 'NEW_USER';
Student_Number NUMBER(9) NOT NULL := 9999999;

Cursors
A cursor is a variable that is defined to retrieve data from the database.
It contains an SQL Select statement. There are two types of cursors, Implicit and Explicit.

Implicit:
These cursors are contained in SQL commands that return a single row or no rows.
Examples of this type are INSERT, UPDATE, and DELETE commands.

Explicit:
These cursors are defined by the programmer. The syntax for creating an explicit cursor is:
CURSOR cursor_name IS
Select query
From query;

Once an explicit cursor has been created it can be used in the body of the procedure.
There are three commands that must be used to get a cursor to work correctly. They are
OPEN cursor_name; FETCH cursor_name INTO variable_name; Close cursor_name;

The OPEN .. command activates the cursor at the beginning of the table specified in the
cursor's definition. The FETCH .. INTO .. command retrieves the current row's information
and places it in the variable specified in the command. To retrieve the next row's information
you simply need to make another FETCH .. INTO .. command. The easiest way to accomplish
this is to use some form of looping structure with either an exit requirement or exit call.
The CLOSE command deactivates the cursor.

Example:

DELCARE
	s_name CHAR(25);
	s_ssn NUMBER(9);

	CURSOR name_fetcher IS
	Select NAME, SSN
	FROM ROSTER;
BEGIN
	OPEN name_fetcher;
	FETCH name_fetcher INTO s_name, s_ssn;
	CLOSE name_fetcher;
END;

This would select the NAME and SSN of the first person in ROSTER into the variables
s_name and s_ssn. The variables could then be used to update or insert into a new table,
or be tested against values.

Loops and tests
IF-THEN-ELSE
The format of the testing blocks is very similar to other IF-THEN-ELSE blocks you have already seen.

IF condition THEN
	if_block || NULL;
[ELSIF || ELSE] condition THEN
	else_block || NULL;
END IF;

Looping
There are several types of loop formats available in PL/SQL.
CURSOR FOR LOOP
LOOP
WHILE LOOP
FOR LOOP

CURSOR FOR LOOP is a loop structure designed specifically for CURSORS. With the CURSOR FOR LOOP
you do not need to handle OPEN, FETCH, or CLOSE of the CURSOR; the LOOP handles them implicitly.
Syntax:
FOR row_name IN cursor_name LOOP
loop code
END LOOP;

Example:

FOR Curr_ROW IN name_fetcher LOOP
	IF Curr_ROW.GRADE > 90 THEN
		INSERT INTO DEANSLIST (NAME, SSN)
		VALUES (Curr_ROW.NAME, Curr_ROW.SSN);
	END IF;
END LOOP;

LOOP is a continuous loop that technically will never end. When using this command always
allow for a test that will break out of the loop using the EXIT WHEN condition command.
Syntax:

LOOP
	loop code
	EXIT WHEN (true);
END LOOP;

WHILE LOOP is the same as a regular while loop command. It continues until a certain
criteria has been met.
Syntax:

While condition LOOP
	loop code
END LOOP;

FOR LOOP is the same as a regular for (i = 0; i < 9; i++) looping structure. It will
loop as long as it is within the specified counting range.
Syntax:

FOR count_variable IN [REVERSE] starting_number .. ending_number LOOP;
	loop code
END LOOP;

PL/SQL is often the programming language used when creating procedures and functions
in a database. In Oracle 8 the DECLARE keyword is not required when creating variables
in a function or procedure.

Return to General Directions
Return to Cs 1555