|
"Clarity of mind
means clarity of passion, too; this is why a great and clear
mind loves ardently and sees distinctly what it loves." Blaise
Pascal (1623 - 1662) |
IMPORTANT-READ CAREFULLY
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.
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.
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.
"CREATE PROCEDURE"
privilege gives the user permission to write the program.
"EXECUTE" privilege
gives the user permission to run the program.
A "Parameter" is a
value passed in and out of the program. Direction should be stated for
parameter passing in procedures and functions. The "IN," "OUT,"
and "IN OUT," are possibilities for parameter passing directions.
A named block such as a
procedure or a function can have no or many Parameters.
A Function must always have
a return value. Use "FUNCTION" if you have only one output
parameter from your stored procedure.
Procedure Builder is an
Oracle Back-End tool. It will be used for writing and debugging both
server- and client-side PL/SQL programs.
Server-side is where your
database is located. If you store your PL/SQL programs in the database
server, we say "that it is stored on the server-side."
Client-side is your PC. You
access to the database remotely. If you store your PL/SQL programs in
the local machine, we say "that it is stored in the client-side."
The "Program unit"
editor is used to write a PL/SQL stored procedure for client-side. You
use the compile option to compile and save a stored procedure locally.
The "Stored Program Unit"
editor" is used to write a PL/SQL stored procedure for server-side.
You use the save option to compile and save a stored procedure on the
database server.
The "Breakpoints" in
"Procedure Builder" must be used for debugging programs. A
breakpoint allows you to stop PL/SQL execution and test the contents
of your runtime variables stack.
Object permission must be
granted to a user directly not via a role if the user wants to pass
the permission through its stored procedures to other users.
Object Dependency is when a
PL/SQL block relies on availability of another database object such as
a table, procedure, function, or sequence.
The "DBA_DEPENDENCIES"
dictionary view will be used to see all object dependencies on the
Oracle database.
Objects and procedure
dependencies are enforced using timestamps in Oracle by default. A
timestamp is a character string of the date and time that an object
was last modified or compiled.
A package is a collection
of many program units into one database object. Packages contain two
parts: a specification and a body.
Package specification
contains program unit definitions, type definitions, exceptions, and
constants only.
The Package body contains
all program logic for all program units plus any program logic for
private program unit.
Private program units
cannot be called from outside the package body.
Public program units are
those whose specification appear in the package specification and can
be called from outside the package body.
You must first compile
package specification before its package body.
A database trigger is a set
of PL/SQL statements that execute each time an event such as an
update, insert, or delete statement occurs on the database. They are
similar to stored PL/SQL statements. They are stored in the database
and attached to a table.
There are two types of
database triggers: statement triggers and row triggers.
A statement trigger will
fire only once for a triggering statement.
A row trigger fires once
for every row affected by a trigger statement.
Triggers can be set to fire
either before or after Oracle processes the triggering insert, update,
or delete statement.
The keywords updating,
deleting, or inserting can be used when multiple triggering events are
defined.
Once you create the
trigger. It is enabled and ready to execute. You can enable or disable
the trigger. Remember that No special privileges other than permission
to access to the table is needed to run the trigger.
|
"A person is never
happy except at the price of some ignorance." Anatole France
(1844 - 1924) |
Questions:
Q: Describe the command
line technique?
Q: How do you use the
define_editor command?
Q: How many categories of
PL/SQL blocks are there?
Q: What is an anonymous
block?
Q: What is a named block?
Q: What does a PL/SQL block
contain?
Q: What is the EXCEPTION
section in the PL/SQL block?
Q: What are the differences
between an implicit cursor and explicit cursor?
Q: How do you obtain data
from a cursor?
Q: What is a LOOP in the
PL/SQL block and how many types are there?
Q: What is advantage of the
FOR LOOP statement verses of the simple LOOP statement?
Q: How do you pass a
parameter to a PL/SQL procedure or function?
Q: What does the procedure
builder tool?
Q: Describe the server-side
and client-side machine?
Q: What is the Breakpoint
in the Oracle Procedure builder tool?
Q: What is the "DBA_DEPENDENCIES"
dictionary view?
Q: What is a PL/SQL
package?
Q: What is a package
specification?
Q: What is a package body?
Q: What is a public and
private procedure in the PL/SQL package?
Q: In what sequence do you
compile a package body and specification?
Q: What is a database
trigger?
Q: What are the differences
between the statement and row triggers in the Oracle database?
Q: What do the UPDATING,
DELETING, or INSERTING keywords?
Q: How do you enable,
disable, and drop a trigger in the Oracle database?
|