|
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.
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? |
