iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 21

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