Oracle DBA FAQ - Understanding PL/SQL Language Basics--tutorial.jcwcn.com

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:

What Is PL/SQL Language Case Sensitive?
How To Place Comments in PL/SQL?
What Are the Types PL/SQL Code Blocks?
What Is an Anonymous Block?
What Is a Named Program Unit?
What Is a Procedure?
What Is a Function?
How To Declare a Local Variable?
How To Initialize Variables with Default Values?
How To Assign Values to Variables?
What Are the Arithmetic Operations?
What Are the Numeric Comparison Operations?
What Are the Logical Operations?
How Many Categories of Data Types?
How Many Scalar Data Types Are Supported in PL/SQL?
How To Convert Character Types to Numeric Types?
What Are the Execution Control Statements?
How To Use "IF" Statements on Multiple Conditions?
How To Use "WHILE" Statements?
How To Use "FOR" Statements?
What Is NULL in PL/SQL?
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.

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;
What Are the Arithmetic Operations?
There are 4 basic arithmetic operations on numeric values as shown in the following sample script:
PROCEDURE proc_arithmetic AS
  addition NUMBER; 
  subtraction NUMBER;
  multiplication NUMBER;
  division NUMBER;
BEGIN
  addition := 7 + 8; 
  subtraction := addition - 7; 
  multiplication := subtraction * 5; 
  division := multiplication / 8;
  -- division should contain 5 now
END;
What Are the Numeric Comparison Operations?
PL/SQL supports 6 basic numeric comparison operations as shown in the following sample script:
PROCEDURE proc_comparison AS
  res BOOLEAN; 
BEGIN
  res := 1 = 2;
  res := 1 < 2;
  res := 1 > 2;
  res := 1 <= 2;
  res := 1 >= 2;
  res := 1 <> 2;
  -- more statements
END;
What Are the Logical Operations?
PL/SQL supports 3 logical operations as shown in the following sample script:
PROCEDURE proc_comparison AS
  x BOOLEAN := TRUE; 
  y BOOLEAN := FALSE;
  res BOOLEAN; 
BEGIN
  res = x AND y;
  res = x OR y; 
  res = NOT x;
  -- more statements
END;
How Many Categories of Data Types?
PL/SQL data types are grouped into 4 categories:
Scalar Data Types: A scalar data type holds a single value. 
Composite Data Types: A composite data type has internal components, such as the elements of an array. 
LOB Data Types: A LOB data type holds a lob locator that specify the location of a large object. 
Reference Data Types: A reference data type holds a pointer that points to another data object. 
How Many Scalar Data Types Are Supported in PL/SQL?
PL/SQL supports many scalar data types divided into 4 groups:
Numeric Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT. 
Character Types: CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2. 
Boolean Types: BOOLEAN. 
Date Types: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND. 
How To Convert Character Types to Numeric Types?
You can convert character types to numeric types in two ways:
Explicitly by using TO_NUMBER() function. 
Implicitly by putting character data in a numeric operation. 
The sample script below shows you how to convert character types to numeric types: 
PROCEDURE proc_convert_1 AS
  start_time CHAR(5);
  finish_time CHAR(5);
  elapsed_time NUMBER(5);
BEGIN
  start_time := '12052';
  finish_time := '15314';
  elapsed_time := TO_NUMBER(finish_time)
    - TO_NUMBER(start_time);
  elapsed_time := finish_time - start_time; -- same as above
END;
What Are the Execution Control Statements?
PL/SQL supports three groups of execution control statements:
IF Statements - Conditionally executes a block of statements. 
CASE Statements - Selectively executes a block of statements. 
LOOP Statements - Repeatedly executes a block of statements. 
GOTO Statements - Unconditional changes the execution flow to a specified statement. 
The script below shows some execution control statements:
DECLARE
  total NUMBER;
BEGIN
  total := 0;
  LOOP
    total := total+1;
    IF total >= 10 THEN
      GOTO print;
    END IF;
  END LOOP;
  <>
  DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;
This script should print this:
Total counts: 10
` How To Use "IF" Statements on Multiple Conditions?
If you have multiple blocks of codes to be executed based on different conditions, you can use the "IF ... ELSIF" statement. Here is a sample script on IF statements:
DECLARE
  day VARCHAR2;
BEGIN
  day := 'SUNDAY';
  IF day = 'THURSDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Checking log files.');
  ELSIF day = 'TUESDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Helping developers.');
  ELSIF day = 'FRIDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Reading some papers.');
  END IF;
END;
This script should print this:
Reading some papers.
How To Use "WHILE" Statements?
If you have a block of codes to be executed repeatedly based a condition, you can use the "WHILE ... LOOP" statement. Here is a sample script on WHILE statements:
DECLARE
  total NUMBER;
BEGIN
  total := 0;
  WHILE total < 10 LOOP
    total := total+1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;
This script should print this:
Total counts: 10
How To Use "FOR" Statements?
If you have a block of codes to be executed repeatedly over a range of values, you can use the "FOR ... LOOP" statement. Here is a sample script on FOR statements:
DECLARE
  total NUMBER := 0;
BEGIN
  FOR i IN 1..10 LOOP
    total := total + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total: ' || TO_CHAR(total));
END;
Note that temporary variable "i" used in the FOR loop needs no declaration. This script should print this:
Total: 55
What Is NULL in PL/SQL?
NULL is a reserved key word and it stands for two things in PL/SQL:
NULL is an executable statement, and means doing nothing. 
NULL is a data balue, and means no value. 
The following sample script shows you examples of using NULL keyword:
DECLARE
  next_task CHAR(80);
BEGIN
  next_task := NULL; -- NULL value

  IF next_task IS NOT NULL THEN
     DBMS_OUTPUT.PUT_LINE('I am busy.');
  ELSE
     DBMS_OUTPUT.PUT_LINE('I am free.');
  END IF;
  
  IF next_task IS NULL THEN
     NULL; -- NULL statement
  ELSE
     DBMS_OUTPUT.PUT_LINE('... working on ' || next_task);
  END IF;
END;
This script should print this:
I am free.
How To Test NULL Values?
There ate two special comparison operators you can use on NULL values:
"variable IS NULL" - Returns TRUE if the variable value is NULL. 
"variable IS NOT NULL" - Return TRUE if the variable value is not NULL. 
The following sample script shows you examples of comparing NULL values:
DECLARE
  next_task CHAR(80);
BEGIN
  next_task := NULL;

  IF next_task IS NOT NULL THEN
     DBMS_OUTPUT.PUT_LINE('I am busy.');
  ELSE
     DBMS_OUTPUT.PUT_LINE('I am free.');
  END IF;
  
  IF next_task IS NULL THEN
     NULL;
  ELSE
     DBMS_OUTPUT.PUT_LINE('... working on ' || next_task);
  END IF;
END;
Note that "variable = NULL" is not a valid operation. This script should print this:
I am free.

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

Oracle DBA FAQ - Understanding PL/SQL Language Basics

relation tutorial

Category

New

Hot