| |
|
More Resources by
Google: |
|
|
|
|
IMPORTANT-READ
CAREFULLY
In
this tutorial, you will learn how to edit your SQL statement with different
options.
As you
noticed from your previous hands-on, if you made an error while typing a
line of “SQL,” you had to use the “BACKSPACE” key to erase
characters until you reached the mistake. However, this approach only worked
if you were still on the same line in the “SQL” entry buffer.
Now, you are going to learn the “command line” and “notepad”
editing techniques.
-
In the “command line”
technique, you can type in the number corresponding to the line to be
corrected. Then you can
type in the change, delete, or insert command to edit the SQL buffer.
-
If you don’t like
“command line” editing, you can simply type edit, or “ed,” at
the prompt in SQL*PLUS. Oracle
will immediately bring up your favorite text editor, perhaps notepad
You can change your text editor with the define_editor command.
Also
in this Hands-On, we discuss PL/SQL blocks.
Before
going through the Hands-On training, lets take note of the following
important definitions to remember:
-
There are two categories
of PL/SQL blocks: anonymous and named.
-
An anonymous block is
a one time procedure and can not be referenced over and over.
-
A named block can be
referenced as many times as you want.
Procedures, functions, packages and triggers are examples of
named blocks.
-
A PL/SQL block
contains “DECLARATION,” “BODY,” and “EXCEPTION” sections.
-
The “DECLARATION”
section is the place where all PL/SQL variables, types, cursors, and
user defined exceptions would be declared.
-
The “BODY” section
contains the logical flow of your program instructions.
-
The “EXCEPTION”
portion will handle certain types of predefined or user defined
exceptions. It handles
predefined exceptions without explicitly coding error-handling routines
in the procedure. And it
handles user defined exceptions when the exceptions are explicitly
raised.
-
In your hands-on training,
you will learn how to write code for each component a PL/SQL block.
-
An implicit cursor is
a “SQL” statement that is executed in the “BODY” section of the
PL/SQL block. Remember it
returns only one record and must have an “INTO” clause.
-
An explicit cursor is
a named cursor that is defined in the “DECLARATION” section.
It will correspond to a defined SQL statement.
It can return as many records as you need.
-
In order to obtain data
from a cursor, the cursor should be opened and fetched into a
variable. Then the cursor
should be closed.
-
In the “BODY” section of
the PL/SQL block, you can use conditional statement and process flow
such as “if-then-else,” “simple loop,” “while-loop,” and
“for-loop” statements.
-
The “FOR LOOP”
statement handles the steps normally done in the open statement, and
implicitly fetches data from the cursor until there is no row.
It also declares the cursor variable.
The “END LOOP” statement closes the cursor.
-
You can pass parameters
to the cursor as input parameters.
You
will see through out our tutorial, we may intentionally type a syntax error or
perhaps a logical error to demonstrate to you how to solve them. Remember, you
learn from your mistakes. Learn
how to face and patiently solve the problem, rather than get frustrated.
To build a fundamental and solid experience, you should build a habit
of solving your problems with an abundance of patience.
Now,
you should first read your case study, and try to solve the questions. Then play the Hands-On training movie until you become
familiar with the subject. For
more information about this subject, you are encouraged to read from a wide
selection of available books.
Good
luck!
|
| |
|