Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Understanding PL/SQL Language Basics

By: FYIcenter.com

Part:   1   2  3  4  5 

A collection of 22 FAQs on PL/SQL language basics or DBA and developers. It can also be used as learning tutorials on defining variables, assigning values, using "loop" statements, setting "if" conditions, and working with null values. Topics included in this FAQ are:

1. What Is PL/SQL Language Case Sensitive?
2. How To Place Comments in PL/SQL?
3. What Are the Types PL/SQL Code Blocks?
4. What Is an Anonymous Block?
5. What Is a Named Program Unit?
6. What Is a Procedure?
7. What Is a Function?
8. How To Declare a Local Variable?
9. How To Initialize Variables with Default Values?
10. How To Assign Values to Variables?
11. What Are the Arithmetic Operations?
12. What Are the Numeric Comparison Operations?
13. What Are the Logical Operations?
14. How Many Categories of Data Types?
15. How Many Scalar Data Types Are Supported in PL/SQL?
16. How To Convert Character Types to Numeric Types?
17. What Are the Execution Control Statements?
18. How To Use "IF" Statements on Multiple Conditions?
19. How To Use "WHILE" Statements?
20. How To Use "FOR" Statements?
21. What Is NULL in PL/SQL?
22. How To Test NULL Values?

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. Most of the sample scripts in this FAQ collection are written in anonymous block format. It is also assumed that you know how to create and run anonymous blocks with SQL*Plus or SQL Developer.

What Is PL/SQL Language Case Sensitive?

PL/SQL language is not case sensitive:

• Reserved words are not case sensitive. For example: CASE and Case are identical.
• Variable names and other names are not case sensitive. For example: TOTAL_SALARY and total_salary are identical.

But values in string literals are case sensitive. For example: 'DBA' and 'dba' are different.

How To Place Comments in PL/SQL?

There are two ways to place comments into PL/SQL codes:

• SQL Statement Style: Starts you comment any where in the line but prefixed with '--'. The comment ends at the end of the line.
• C Language Style: Starts you comment any where in the line with '/*' and ends it with '*/' in the same line or some lines below.

Here is some example of PL/SQL comments:

BEGIN
  -- This is a comment
  /* To do:
     Need to write some codes here 
  */
END;

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.

What Is an Anonymous Block?

An anonymous block is a PL/SQL code block with no name. It consists of three parts:

• Declaration Part - Defining local variables and local procedures. Declaration part is optional.
• Execution Part - Defining execution logic with executable statements. Execution part is required.
• Exception Part - Defining error handling logics. Exception part is optional.

Here how a complete anonymous block should look like:

DECLARE 
  -- Declaration statements
BEGIN
  -- Executable statements
EXCEPTION
  -- Error handling statements
END;

What Is a Named Program Unit?

A named program unit is a PL/SQL code block with an name. It consists of three parts:

• Declaration Part - Defining the program unit name, calling parameters, local variables and local procedures. Declaration part is required.
• Execution Part - Defining execution logic with executable statements. Execution part is required.
• Exception Part - Defining error handling logics. Exception part is optional.

There are two types of named program units:

• Procedure - Has no return values.
• Function - Has a return value.

(Continued on next part...)

Part:   1   2  3  4  5