Oracle DBA FAQ - Understanding PL/SQL Language Basics

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5

(Continued from previous part...)

What Is a Procedure?

A procedure is a named program unit. It consists of three parts:

Declaration Part - Defining the procedure 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.

Here how a complete procedure should look like:

PROCEDURE name (parameter_1, parameter_2) AS
  -- Declaration statements
BEGIN
  -- Executable statements
EXCEPTION
  -- Error handling statements
END;

What Is a Function?

A function is a named program unit. It consists of three parts:

Declaration Part - Defining the function name, calling parameters, return value type, 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.

Here how a complete procedure should look like:

FUNCTION name (parameter_1, parameter_2) RETURN type AS
  -- Declaration statements
BEGIN
  -- Executable statements
  RETURN value;
EXCEPTION
  -- Error handling statements
END;

How To Declare a Local Variable?

A local variable can be defined in the declaration part with a declaration statement, which is a variable name followed a data type identifier. Below are some examples of declaration statements:

PROCEDURE proc_var_1 AS
  domain VARCHAR2(80);
  price REAL;
  is_for_sale CHAR;
BEGIN
  -- Executable statements
END;

How To Initialize Variables with Default Values?

There are two ways to assign default values to variables at the time of declaration:

Using key word DEFAULT - Appending "DEFAULT value" to the end of declaration statements.
Using assignment operator - Appending ":= value" to the end of declaration statements.

The script below show you some examples of declaration statements with default values:

PROCEDURE proc_var_1 AS
  domain VARCHAR2(80) := 'fyicenter.com';
  price REAL DEFAULT 999999.99;
  is_for_sale CHAR := 'N';
BEGIN
  -- Executable statements
END;

How To Assign Values to Variables?

You can use assignment statements to assign values to variables. An assignment statement contains an assignment operator ":=", which takes the value specified on the right to the variable on left. The script below show you some examples of assignment statements:

PROCEDURE proc_var_2 AS
  is_done BOOLEAN;
  counter NUMBER := 0;
  message VARCHAR2(80); 
BEGIN
  is_done := FASLE;
  counter := counter + 1;
  message := 'Hello world!';
END;

(Continued on next part...)

Part: 1 2 3 4 5

More...