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 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;

(Continued on next part...)

Part: 1 2 3 4 5

More...