Platform For Learning Programming Language: PL-SQL Fundamentals

Character Sets and Lexical Units

PL/SQL supports two character sets: the database character set, which is used for identifiers and source code, and the national character set (NCHAR and NVARCHAR2), which is used for national language data..

PL/SQL keywords are not case-sensitive, so lower-case letters are equivalent to corresponding upper-case letters except within string and character literals.

PL/SQL programs are written as lines of text which contains groups of characters known as lexical units:

Delimiters (simple and compound symbols)
Identifiers, which include reserved words
Literals
Comments

You must separate adjacent identifiers by a space or punctuation. For example:

IF x > y THEN high := x; END IF;  -- correct
IF x > y THEN high := x; ENDIF;   -- incorrect

You cannot embed spaces inside lexical units (except string literals and comments). For example:

count := count + 1;   -- correct
count : = count + 1;  -- incorrect

PL/SQL Delimiters:

Symbol	Meaning	Symbol	Meaning
`+`	addition operator	`%`	attribute indicator
`'`	character string delimiter	`.`	component selector
`/`	division operator	`(`	expression or list delimiter
`)`	expression or list delimiter	`:`	host variable indicator
`,`	item separator	`*`	multiplication operator
`"`	quoted identifier delimiter	`=`	relational operator
`<`	relational operator	`>`	relational operator
`@`	remote access indicator	`;`	statement terminator
`-`	subtraction/negation operator	`:=`	assignment operator
`=>`	association operator	`\|\|`	concatenation operator
`**`	exponentiation operator	`..`	range operator
`<<`	label delimiter (begin)	`>>`	label delimiter (end)
`/*`	multi-line comment delimiter (begin)	`*/`	multi-line comment delimiter (end)
`<>`	relational operator	`!=`	relational operator
`~=`	relational operator	`^=`	relational operator
`<=`	relational operator	`>=`	relational operator
`--`	single-line comment indicator

Identifiers

You use identifiers to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages.

The minimum length of an identifier is one character; the maximum length is 30 characters.

The first character must be a letter, but each later character can be either a letter, numeral, dollar sign ($), underscore (_), or number sign (#).

Characters that are not allowed in identifiersare ampersand (&),hyphen (-), lash (/) and space.

Predefined Identifiers globally declared in package STANDARD, such as the exception INVALID_NUMBER, can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.

Quoted Identifiers lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes.

Both reserved words and keywords have special meaning in PL/SQL. The difference between reserved words and keywords is that you cannot use reserved words as identifiers. You can use keywords as as identifiers, but it is not recommended.

Literals

Numeric Literals: Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. An integer literal is an optionally signed whole number without a decimal point. For example:

030   6   -14   0   +32767  -1E38   -9.5e-3 			-- Integer Literal
6.6667   0.0   -12.0   3.14159   +8300.00   .5   25.0  2.0f  2.0d -- Real Literal

String Literals: A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes. All string literals except the null string ('') have data type CHAR.. For example:

'Hello, world!'
'XYZ Corporation'
'10-NOV-91'
'He said "Life is like licking honey from a thorn."'
'$1,000,000'

PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:
'baker'
'Baker'

To represent an apostrophe within a string, you can write two single quotes, which is not the same as writing a double quote:
'I''m a string, you''re a string.'

Character Literals:A character literal is an individual character enclosed by single quotes ('). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. For example:

'Z'   '%'   '7'   ' '   'z'   '('

BOOLEAN Literals:BOOLEAN literals are the predefined values TRUE, FALSE, and NULL. NULL stands for a missing, unknown, or inapplicable value. Remember, BOOLEAN literals are values, not strings. For example, TRUE is no less a value than the number 25.

Comments

The PL/SQL compiler ignores comments. Adding comments to your program promotes readability and aids understanding. There are two types of comments.

 -- single line comment indicator
/* multimple line 
comment indicator
*/

Declarations

Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot.

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its data type, and name the storage location so that you can reference it.

Variables:

Variables are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL.

DECLARE
	birthday   DATE;
	emp_count  SMALLINT := 0;
	pi         REAL := 3.14159;
	radius     REAL := 1;
	area       REAL := pi * radius**2;
BEGIN
	NULL;
END;
/

Constants:

To declare a constant, put the keyword CONSTANT before the type specifier.A constant must be initialized in its declaration. Constants are initialized every time a block or subprogram is entered.

DECLARE
	credit_limit      CONSTANT REAL    := 5000.00;
	max_days_in_year  CONSTANT INTEGER := 366;
	urban_legend      CONSTANT BOOLEAN := FALSE;
BEGIN
	NULL;
END;
/

Using DEFAULT:

You can use the keyword DEFAULT instead of the assignment operator to initialize variables. You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

DECLARE
	blood_type CHAR DEFAULT 'O';         -- Same as blood_type CHAR := 'O';
	hours_worked    INTEGER DEFAULT 40;  -- Typical value
	employee_count  INTEGER := 0;        -- No typical value
BEGIN
	NULL;
END;
/

Using NOT NULL:

A declaration can impose the NOT NULL constraint, which prevents you from assigning a null value to the variable. Because variables are initialized to NULL by default, a declaration that specifies NOT NULL must also specify a default value.

PL/SQL subtypes NATURALN, POSITIVEN, and SIMPLE_INTEGER are predefined as NOT NULL. When declaring a variable of one of these subtypes, you can omit the NOT NULL constraint, and you must specify a default value.

DECLARE
	acct_id INTEGER(4) NOT NULL := 9999;
	a NATURALN                  := 9999;
	b POSITIVEN                 := 9999;
	c SIMPLE_INTEGER            := 9999;
BEGIN
	NULL;
END;
/

Using the %TYPE Attribute:

The %TYPE attribute lets you declare a constant, variable, field, or parameter to be of the same data type a previously declared variable, field, record, nested table, or database column. If the referenced item changes, your declaration is automatically updated.

An item declared with %TYPE (the referencing item) always inherits the data type of the referenced item. The referencing item inherits the constraints only if the referenced item is not a database column. The referencing item inherits the default value only if the referencing item is not a database column and does not have the NOT NULL constraint.

DECLARE
	credit  PLS_INTEGER RANGE 1000..25000;
	debit   credit%TYPE;  -- inherits data type

	name        VARCHAR2(20) := 'JoHn SmItH';
	upper_name  name%TYPE;  -- inherits data type and default value
	lower_name  name%TYPE;  -- inherits data type and default value
	init_name   name%TYPE;  -- inherits data type and default value
BEGIN
	DBMS_OUTPUT.PUT_LINE ('name: ' || name);
	DBMS_OUTPUT.PUT_LINE ('upper_name: ' || UPPER(name));
	DBMS_OUTPUT.PUT_LINE ('lower_name: ' || LOWER(name));
	DBMS_OUTPUT.PUT_LINE ('init_name:  ' || INITCAP(name));
END;
 /

If you add a NOT NULL constraint to the variable name in Example 2-10, and declare another variable that references it, you must specify a default value for the new item. For example:

Incorrect Way:
	name    VARCHAR2(20) NOT NULL := 'JoHn SmItH';
    same_name   name%TYPE;
	
Correct Way:
	name        VARCHAR2(20) NOT NULL := 'JoHn SmItH';
    upper_name  name%TYPE := UPPER(name);
    lower_name  name%TYPE := LOWER(name);
    init_name   name%TYPE := INITCAP(name);

The %TYPE attribute is particularly useful when declaring variables that refer to database columns. When you use table_name.column_name.%TYPE to declare a data item, you need not know the referenced data type or its attributes (such as precision, scale, and length), and if they change, you need not update your code. For example:


CREATE TABLE employees_temp (
	empid  NUMBER(6) NOT NULL PRIMARY KEY,
	deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid
	CHECK (deptid BETWEEN 100 AND 200),
	deptname VARCHAR2(30) DEFAULT 'Sales'
);

DECLARE
   v_empid    employees_temp.empid%TYPE;
   v_deptid   employees_temp.deptid%TYPE;
   v_deptname employees_temp.deptname%TYPE;
BEGIN
  v_empid := NULL;  -- Null constraint not inherited
  v_deptid := 50;   -- Check constraint not inherited
  DBMS_OUTPUT.PUT_LINE
  ('v_deptname: ' || v_deptname);  -- Default value not inherited
END;
/
-- where employees_temp is a table in database

Using the %ROWTYPE Attribute:

The %ROWTYPE attribute lets you declare a record that represents a row in a table or view. For each column in the referenced table or view, the record has a field with the same name and data type. To reference a field in the record, use record_name.field_name.The record fields do not inherit the constraints or default values of the corresponding columns.

DECLARE
	emprec  employees_temp%ROWTYPE;
BEGIN
	emprec.empid := NULL;  -- Null constraint not inherited
	emprec.deptid := 50;   -- Check constraint not inherited
	DBMS_OUTPUT.PUT_LINE
	('emprec.deptname: ' || emprec.deptname);
	-- Default value not inherited
END;
/

-- Declaring a Record that Represents a Subset of Table Columns
DECLARE
	CURSOR c1 IS
      SELECT department_id, department_name
       FROM departments;
 
    dept_rec c1%ROWTYPE;  -- includes subset of columns in table
	
-- Declaring a Record that Represents a Row from a Join
CURSOR c2 IS
     SELECT employee_id, email, employees.manager_id, location_id
       FROM employees, departments
         WHERE employees.department_id = departments.department_id;
  
    join_rec  c2%ROWTYPE;  -- includes columns from two tables

Aggregate Assignment:

A %ROWTYPE declaration cannot include an initialization clause, but there are two ways to assign values to all fields of a record at once:

If their declarations refer to the same table or cursor, you can assign one record to another.

DECLARE
  dept_rec1  departments%ROWTYPE;
  dept_rec2  departments%ROWTYPE;
 
  CURSOR c1 IS SELECT department_id, location_id FROM departments;

  dept_rec3 c1%ROWTYPE;
  dept_rec4 c1%ROWTYPE;
 
BEGIN
  dept_rec1 := dept_rec2;  -- declarations refer to same table
  dept_rec3 := dept_rec4;  -- declarations refer to same cursor
  dept_rec2 := dept_rec3;  -- Incorrect way
 END;
 /

Use the SELECT or FETCH statement to assign a list of column values to a record.

DECLARE
   dept_rec departments%ROWTYPE;
BEGIN
  SELECT * INTO dept_rec FROM departments
  WHERE department_id = 30 AND ROWNUM < 2;
END;
/

Naming Conventions

The same naming conventions apply to PL/SQL constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example:

Simple—procedure name only:
```
raise_salary(employee_id, amount);
```

emp_actions.raise_salary(employee_id, amount);

Remote—procedure name followed by the remote access indicator (@) and a link to the database on which the procedure is stored:
```
raise_salary@newyork(employee_id, amount);
```

Qualified and remote:

emp_actions.raise_salary@newyork(employee_id, amount);

Scope

Within the same scope, all declared identifiers must be unique. Even if their data types differ, variables and parameters cannot share the same name.

DECLARE
  id  BOOLEAN;
  id  VARCHAR2(5);  -- duplicate identifier
BEGIN
  id := FALSE;
END;
/
Error report:
ORA-06550: line 5, column 3:
PLS-00371: at most one declaration for 'ID' is permitted

Case Sensitivity

Like all identifiers, the names of constants, variables, and parameters are not case sensitive.

DECLARE
  zip_code INTEGER;
  Zip_Code INTEGER;
BEGIN
  zip_code := 90120;
END;
/
Error report:
ORA-06550: line 5, column 3:
PLS-00371: at most one declaration for 'ZIP_CODE' is permitted

Scope and Visibility of PL/SQL Identifiers

References to an identifier are resolved according to its scope and visibility. The scope of an identifier is the region of a PL/SQL unit from which you can reference the identifier. The visibility of an identifier is the region of a PL/SQL unit from which you can reference the identifier without qualifying it.

An identifier declared in a PL/SQL unit is local to that unit and global to its subunits. If a subunit redeclares a global identifier, then inside the subunit, both identifiers are in scope, but only the local identifier is visible. To reference the global identifier, the subunit must qualify it.

You cannot declare an identifier twice in the same PL/SQL unit, but you can declare the same identifier in two different units. The two items represented by the identifier are distinct, and changing one does not affect the other.

SET SERVEROUTPUT ON;
DECLARE
  a CHAR;  -- Scope of a (CHAR) begins
  b REAL;  -- Scope of b begins
BEGIN
  -- Visible: a (CHAR), b

  DECLARE
    a INTEGER;  -- Scope of a (INTEGER) begins
    c REAL;     -- Scope of c begins
   BEGIN
    -- Visible: a (INTEGER), b, c
     NULL;
   END;          -- Scopes of a (INTEGER) and c end

  DECLARE
    d REAL;     -- Scope of d begins
  BEGIN
    -- Visible: a (CHAR), b, d
    NULL;
   END;          -- Scope of d ends
 
   -- Visible: a (CHAR), b
END;            -- Scopes of a (CHAR) and b end
/

Qualifying a Redeclared Global Identifier with a Block Label:

SET SERVEROUTPUT ON;
<>
  DECLARE
    birthdate DATE := '09-AUG-70';
  BEGIN
    DECLARE
      birthdate DATE;
    BEGIN
      birthdate := '29-SEP-70';
  
      IF birthdate = outer.birthdate THEN
        DBMS_OUTPUT.PUT_LINE ('Same Birthday');
      ELSE
        DBMS_OUTPUT.PUT_LINE ('Different Birthday');
      END IF;
    END;
  END;
  /

CASE Expressions

There are two types of expressions used in CASE statements:

Simple CASE Expression: A simple CASE expression selects a result from one or more alternatives, and returns the result. The CASE expression uses a selector, an expression whose value determines which alternative to return.

SET SERVEROUTPUT ON;
DECLARE
    grade CHAR(1) := 'B';
   appraisal VARCHAR2(20);
  BEGIN
    appraisal :=
      CASE grade
        WHEN 'A' THEN 'Excellent'
        WHEN 'B' THEN 'Very Good'
        WHEN 'C' THEN 'Good'
        WHEN 'D' THEN 'Fair'
        WHEN 'F' THEN 'Poor'
        ELSE 'No such grade'
      END;
    DBMS_OUTPUT.PUT_LINE
      ('Grade ' || grade || ' is ' || appraisal);
  END;
  /

Searched CASE Expression: A searched CASE expression lets you test different conditions instead of comparing a single expression to various values.A searched CASE expression has no selector. Each WHEN clause contains a search condition that yields a BOOLEAN value, so you can test different variables or multiple conditions in a single WHEN clause.

SET SERVEROUTPUT ON;
DECLARE
    grade      CHAR(1) := 'B';
    appraisal  VARCHAR2(120);
    id         NUMBER  := 8429862;
    attendance NUMBER := 150;
    min_days   CONSTANT NUMBER := 200;
  
    FUNCTION attends_this_school (id NUMBER)
     RETURN BOOLEAN IS
   BEGIN
     RETURN TRUE;
   END;
 
 BEGIN
   appraisal :=
     CASE
       WHEN attends_this_school(id) = FALSE
         THEN 'Student not enrolled'
       WHEN grade = 'F' OR attendance < min_days
         THEN 'Poor (poor performance or bad attendance)'
       WHEN GRADE = 'A' THEN 'Excellent'
       WHEN grade = 'B' THEN 'Very Good'
      WHEN grade = 'C' THEN 'Good'
      WHEN grade = 'D' THEN 'Fair'
      ELSE 'No such grade'
    END;
  DBMS_OUTPUT.PUT_LINE
    ('Result for student ' || id || ' is ' || appraisal);
END;
/

Handling NULL Values in Comparisons and Conditional Statements

When using NULL values, remember the following rules:
Comparisons involving NULL values always yield NULL.
Applying the logical operator NOT to a NULL value yields NULL.
In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed.
If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. Instead, use a searched CASE syntax with WHEN expression IS NULL.

SET SERVEROUTPUT ON;
DECLARE
    x NUMBER := 5;
    y NUMBER := NULL;
    z NUMBER := NULL;
  BEGIN
    IF x != y THEN  -- yields NULL, not TRUE
      DBMS_OUTPUT.PUT_LINE('x != y');  -- not executed
    ELSIF x = y THEN -- also yields NULL
      DBMS_OUTPUT.PUT_LINE('x = y');
    ELSE
     DBMS_OUTPUT.PUT_LINE
       ('Can''t tell if x and y are equal or not.');
    END IF;
    
    IF y = z THEN  -- yields NULL, not TRUE
        DBMS_OUTPUT.PUT_LINE('y = z');  -- not executed
      ELSIF y != z THEN  -- yields NULL, not TRUE
        DBMS_OUTPUT.PUT_LINE('y != z');  -- not executed
      ELSE
       DBMS_OUTPUT.PUT_LINE('Can''t tell if two NULLs are equal');
     END IF;
 END;
 /

Next Article: PL/SQL Datatypes

For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)

Platform For Learning Programming Language

PL-SQL Fundamentals