Site is too busy. Please visit the index page again in a moment.

 Oracle DBA FAQ - Introduction to PL/SQL Home >> FAQs/Tutorials >> Oracle DBA
FAQ >> Index Oracle DBA FAQ - Introduction to
PL/SQL By: FYIcenter.com Part:   1   2  3  4  A collection of 17
FAQs to introduce PL/SQL language for DBA and developers. This FAQ can
also be used as learning tutorials on creating procedures, executing
procedures, using local variables, controlling execution flows, passing
parameters and defining nested procedures. Topics included in this FAQ
are: What Is PL/SQL? What Are the Types PL/SQL Code Blocks? How To Define
an Anonymous Block? How Many Anonymous Blocks Can Be Defined? How To Run
the Anonymous Block Again? What Is Stored Program Unit? How To Create a
Stored Program Unit? How To Execute a Stored Program Unit? How Many Data
Types Are Supported? What Are the Execution Control Statements? How To Use
SQL Statements in PL/SQL? How To Process Query Result in PL/SQL? How To
Create an Array in PL/SQL? How To Manage Transaction Isolation Level? How
To Pass Parameters to Procedures? How To Define a Procedure inside Another
Procedure? What Do You Think about PL/SQL? Sample scripts used in this FAQ
assumes that you are connected to the server with the HR user account on
the default database instance XE. See other FAQ collections on how to
connect to the server. What Is PL/SQL? PL/SQL is a modern,
block-structured programming language. It provides several features that
make developing powerful database applications very convenient. For
example, PL/SQL provides procedural constructs, such as loops and
conditional statements, that are not available in standard SQL. PL/SQL
code runs on the server, so using PL/SQL lets you centralize significant
parts of your database applications for increased maintainability and
security. It also enables you to achieve a significant reduction of
network overhead in client/server applications. What Are the Types PL/SQL
Code Blocks? There are 3 types of PL/SQL code blocks: Anonymous Block - A
block of codes with no name. It may contain a declaration part, an
execution part, and exception handlers. Stored Program Unit - A block of
codes with a name. It is similar to an anonymous block. But it can take
parameters and return values. Trigger - A block of code that can be
defined to fire based an specific event. How To Define an Anonymous Block?
An anonymous block must have an execution part, which is a group of other
PL/SQL statements enclosed in the BEGIN ... END statement. Here is a
script on how to define a simple anonymous block with SQL*Plus: SQL> set
serveroutput on; SQL> begin 2 dbms_output.put_line('Hello world!'); 3 end;
4 / Hello world! PL/SQL procedure successfully completed. "set serveroutput
on;" allows dbms_output.put_line() to work. "/" runs the anonymous block,
which print the "Hello world!" message. How Many Anonymous Blocks Can Be
Defined? An anonymous block is stored in the user's current session
without any name. So you can only define one anonymous block at any time.
If you define another anonymous block, the new block will replace the
previously defined block, as shown in the following script: SQL> set
serveroutput on; SQL> begin 2 dbms_output.put_line('Hello world!'); 3 end;
4 / Hello world! PL/SQL procedure successfully completed. SQL> begin 2
dbms_output.put_line('This is a PL/SQL FAQ.'); 3 end; 4 / This is a PL/SQL
FAQ. PL/SQL procedure successfully completed. (Continued on next part...)
Part:   1   2  3  4  Selected Developer
Jobs: More...