|
More Resources by
Google: |
|
|
|
|
IMPORTANT-READ
CAREFULLY
In
this tutorial, you will see a substantial amount of Hands-On training on how to
develop PL/SQL blocks.
Before
going through your Hands-On training, lets take note of the following
important definitions to remember:
-
“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.
-
In Section 1, you will
learn how to create a procedure and function.
-
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.
-
In Section 2, you are
going to debug a procedure that calculates the factorial of a number.
This program doesn’t work and your assignment is to debug and fix
the program.
-
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.
-
In Section 3, you are
going to learn how to grant object privileges and experience some object
dependencies.
-
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.
-
In Section 4, you will
experience how to develop, compile and run a package, as well as create
Public and Private objects in a package
-
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.
-
In Section 5, you will
experience how to develop, drop, compile and run a trigger.
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 the subject, you are encouraged to read from a wide selection
of available books.
Good
luck! |