ch14.htm

Teach yourself Oracle8 in 21 day

start the cluster-creation process. As with almost all the operations you have seen
thus far, this can also be accomplished via the Schema Manager or via the <TT>CREATE
CLUSTER</TT> command.</P>
<P>Cluster creation can actually be thought of as a three-step process. These steps
consist of the following operations:

<DL>
	<DD><B>1. </B>Create the cluster. This first step involves using the Schema Manager
	or the <TT>CREATE CLUSTER</TT> command to create the actual cluster itself. This
	cluster is a logical structure that will hold the clustered tables.
	<P><B>2. </B>Create the tables in the cluster. This can be accomplished using the
	<TT>CREATE TABLE</TT> command with the <TT>CLUSTER</TT> parameter.</P>
	<P><B>3. </B>Create the index on the cluster key. Before any rows can be inserted
	into the tables, the cluster index must be created.
</DL>

<P>The remainder of this section goes through the process of creating the cluster,
the tables, and the index.
<H4><FONT COLOR="#000077"><B>Creating Clusters</B></FONT></H4>
<P>As stated, the cluster-creation process can be accomplished via the Schema Manager
or the <TT>CREATE CLUSTER</TT> command. To create a cluster using the Schema Manager,
right-click the Cluster icon.</P>
<P>From the options available from this menu, select the Create button. This will
invoke the Create Cluster screen, a completed version of which is shown in Figure
14.4.</P>
<P>After you have invoked the Create Cluster screen you must fill in the values for
the cluster name, the schema, the cluster size, whether it is an index cluster or
a hash cluster, and the cluster columns. The size value is important; it specifies
the average size that you expect a cluster key and its associated rows to consume.
This parameter will be used to reserve space for each key value and rows. This does
not limit the amount of data that can be used by a cluster value, but will improve
efficiency.</P>
<P><A NAME="04"></A><A HREF="04.htm"><B>Figure 14.4.</B></A></P>
<P><I>The Create Cluster screen for the <TT>Dogs</TT> table.</I></P>
<P>When you have completed this operation, the cluster will be created. You can also
create a cluster with the <TT>CREATE CLUSTER</TT> command. Here is an example:</P>
<PRE><FONT COLOR="#0066FF">CREATE CLUSTER dogs (owner_id NUMBER(4))
TABLESPACE dogs;
</FONT></PRE>
<P>The complete syntax for the <TT>CREATE CLUSTER</TT> command can be found in the
Oracle documentation. Of course, you can add storage parameters to this command,
such as the storage clause, parallelism, and so on. The next step involved is creating
the clustered tables.
<H4><FONT COLOR="#000077"><B>Creating Clustered Tables</B></FONT></H4>
<P>You can create the cluster tables by using the <TT>CREATE TABLE</TT> command,
as shown in earlier lessons in this book. The <TT>CREATE TABLE</TT> command must
include the <TT>CLUSTER</TT> parameter. To create the two tables (here we'll use
the tables <TT>Dogs</TT> and <TT>Dog_owners</TT> that you saw on Day 12, &quot;Working
with Tables, Views, and Synonyms&quot;) as a cluster, you can use SQL commands with
the Server Manager (see Listings 14.1 and 14.2).
<H4><FONT COLOR="#000000"><B>INPUT:</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 14.1. The SQL for the </B>Dogs<B> table.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">CREATE TABLE &quot;ETW&quot;.Dogs (
ID NUMBER NULL, 
NAME VARCHAR2(40) NULL, 
OWNER_ID NUMBER NULL, 
BREED_ID NUMBER NULL, 
RANK NUMBER NULL, 
NOTES VARCHAR2(80) NULL)  
<TT>CLUSTER dogs(OWNER_ID);</TT></FONT></PRE>
<H4><FONT COLOR="#000000"><B>INPUT:</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 14.2. The SQL for the </B>Dog_owners<B> table.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">CREATE TABLE &quot;ETW&quot;.Dog_owners (
ID NUMBER NOT NULL, 
Last_Name VARCHAR2(40) NULL, 
First_Name VARCHAR2(20) NULL, 
Street VARCHAR2(40) NULL, 
City VARCHAR2(20) NULL, 
State CHAR(2) NULL, 
Zip VARCHAR2(10) NULL, 
Phone VARCHAR2(15) NULL, 
Notes VARCHAR2(80) NULL) 
<TT>CLUSTER dogs(ID);</TT> </FONT></PRE>
<P>Notice that the <TT>CLUSTER</TT> parameter specifies the cluster name and the
cluster-key value. For more information on the <TT>CREATE TABLE</TT> command, refer
to the Oracle documentation.</P>
<P>The final stage involved in creating a cluster is to create the index on the cluster
key. This must be accomplished before any data can be loaded into the tables.
<H4><FONT COLOR="#000077"><B>Creating the Cluster Index</B></FONT></H4>
<P>Creating the cluster index can be accomplished either via the Schema Manager or
the <TT>CREATE INDEX</TT> command. To create the cluster index using the Schema Manager,
right-click the icon of the cluster on which you want to create the index and select
the Create Index On option from the menu that pops up.</P>
<P>This will invoke the Create Index screen that you have seen in previous lessons.
There are, however, a few differences this time. In this screen the Cluster button
has already been selected for you, and you cannot select any columns for indexing.
The cluster index is on the cluster key only. The filled-out Create Index screen
is shown in Figure 14.5.</P>
<P>You can use the Schema Manager to easily create the cluster index. If, however,
you want to use the <TT>CREATE INDEX</TT> command, you also have that option. It
is often convenient to script the entire database-, table-, cluster-, and index-creation
process so that it can be used again or as a template for other database creations.
To create the cluster index using the <TT>CREATE INDEX</TT> command, use the following
syntax:</P>
<PRE><FONT COLOR="#0066FF">CREATE INDEX &quot;ETW&quot;.Dog_owners_IX1 ON CLUSTER  &quot;ETW&quot;.&quot;Dogs&quot;;
</FONT></PRE>
<P>At this point your cluster is created and ready for use. In the next part of this
lesson you will learn how to use procedures, functions, and packages (also known
as stored procedures).</P>
<P><A NAME="05"></A><A HREF="05.htm"><B>Figure 14.5.</B></A></P>
<P><I>The Create Index screen, with information for this example filled in.</I></P>
<P>
<H2><FONT COLOR="#000077"><B>Procedures, Functions, and Packages</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B><I>Procedures</I> and <I>functions</I>
are subprograms made up of PL/SQL code that take a set of parameters given to them
by the calling program and perform a set of actions. The only real difference between
a procedure and a function is that a function will include a single return value.
Both functions and procedures can modify and return data passed to them as a parameter.
Usually, procedures are used unless only one return value is needed.</P>
<P>A procedure or function that has been stored in the library cache is referred
to as a <I>stored procedure</I> or a <I>stored function</I>. A stored procedure or
stored function has the following characteristics:

<UL>
	<LI>It has a name--This is the name by which the stored procedure or function is
	called and referenced.
	<P>
	<LI>It takes parameters--These are the values sent to the stored procedure or function
	from the application.
	<P>
	<LI>It returns values--A stored procedure or function can return one or more values
	based on the purpose of the procedure or function.
	<P>
	<LI>It is stored in the data dictionary--The stored procedure or function is stored
	in a parsed form in the data dictionary.
</UL>

<H3><FONT COLOR="#000077"><B>Procedures</B></FONT></H3>
<P>A <I>procedure</I> is a set of PL/SQL statements that form a subprogram. The subprogram
is designed and created to perform a specific operation on data in your database.
A procedure takes zero or more input parameters and returns zero or more output parameters.
The syntax of a procedure is as follows:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">PROCEDURE procedure_name [( parameter_declaration )] IS
    [local declarations]
BEGIN
    PL/SQL Statements
[EXCEPTION
    Optional Exception Handler(s)]
END [procedure_name];
</FONT></PRE>
<P>In this syntax, the parameter_declaration has the following format:</P>
<PRE><FONT COLOR="#0066FF">parameter_name [IN | OUT | IN OUT] datatype
</FONT></PRE>
<P>The parameter qualifiers have the following meanings:

<UL>
	<LI><TT>IN</TT>--This parameter is used as an input value only.
	<P>
	<LI><TT>OUT</TT>--This parameter is used as an output value only.
	<P>
	<LI><TT>IN OUT</TT>--This parameter is used as both an input and an output variable.
</UL>

<P>The procedure is made up of two parts: the declaration and the body of the procedure.
The declaration begins with the keyword <TT>PROCEDURE</TT> and ends with the last
parameter declaration. The body begins with the keyword <TT>IS</TT> and ends with
the keyword <TT>END</TT>.</P>
<P>The declaration section is used to define which variables are passed to the procedure
and which values are returned from the procedure back to the calling program. The
body of the procedure is where the real work is done. The body is made up of the
PL/SQL statements that perform the desired task.
<H3><FONT COLOR="#000077"><B>Functions</B></FONT></H3>
<P>A <I>function</I>, like a procedure, is a set of PL/SQL statements that form a
subprogram. The subprogram is designed and created to perform a specific operation
on data in your database. A function takes zero or more input parameters and returns
just one output value. If more than one output value is required, a procedure should
be used. The syntax of a function is as follows:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">FUNCTION function_name [( parameter_declaration )] RETURN datatype IS
    [local declarations]
BEGIN
    PL/SQL Statements
[EXCEPTION
    Optional Exception Handler(s)]
END [function_name];
</FONT></PRE>
<P>The parameter_declaration has the same format as it does with a procedure:</P>
<PRE><FONT COLOR="#0066FF">parameter_name [IN | OUT | IN OUT] datatype
</FONT></PRE>
<P>The parameter qualifiers have the following meanings:

<UL>
	<LI><TT>IN</TT>--This parameter is used as an input value only.
	<P>
	<LI><TT>OUT</TT>--This parameter is used as an output value only.
	<P>
	<LI><TT>IN OUT</TT>--This parameter is used as both an input and an output variable.
</UL>

<P>As with a procedure, a function is made up of two parts: the declaration and the
body. The declaration begins with the keyword <TT>FUNCTION</TT> and ends with <TT>RETURN</TT>
statement. The body begins with the keyword <TT>IS</TT> and ends with the keyword
<TT>END</TT>.</P>
<P>The declaration section is used to define which variables are passed to the function
and which values are returned from the function back to the calling program. The
body of the function is where the real work is done. The body is made up of the PL/SQL
statements that perform the desired task.</P>
<P>The difference between a procedure and a function is the return value. A function
has the return declaration as well as a <TT>RETURN</TT> function within the body
of that function that returns a value. This <TT>RETURN</TT> function is used to pass
a return value to the calling program. If you do not intend to return a value to