Reading:  

To the point guide to PL/SQL


Variables

PL/SQL variable is a name of a storage place in the program. The variable declaration specifies name of variable, data type and size of it.

Syntax of Variable Declaration:

variable_name datatype[Size] [NOT NULL] := [ the default value ];


Here are some key details for the code above

  • variable_name: Predefined variable name.
  • Datatype: valid data type of PL/SQL.
  • Size: data type size to store the maximum size value.
  • NOT NULL: By using this option the variable can't be accept value as NULL.
  • Value: User can initialize the variable value.

An Example for our variable would be 

Stud_id number(5) NOT NULL := 2; // This value cannot be null or blank
Stud_name varchar2(25) := 'Nimrit'; // Here the variable has been initialized at the time of declaration

Variables Scope:

The variable scope is identified the area range which user can reference the variable. Two types of scopes:

  • Local variables: These variables are declared inside inner block and it cannot be referenced by the outside blocks.
  • Global variables: These variables are declared in outer block and it can be reference by inner blocks and also by itself.

Example:

These two First_num1 and Second_num2 variables are declared in the outer block known as Global variables, these global variables can be accessed anywhere in the block and one more variable declared num_division inside the inner block known as local variable and this 'num_division' variable cannot access by outer block.

DECLARE
	First_num1 number := 5;
	Second_num2 number := 10;
BEGIN
	DECLARE 
		num_division number; 
	BEGIN 
	   num_division := Second_num2/First_numb1; 
	  dbms_output.put_line('Division is: ' || num_division);
          END;  --after this block we cannot  access num_division variable
END;     /

 

Above will result in 

Division is 2

Description

This tutorial focus on PL/SQL and covers the below topics

  • What is PL/SQL?
  • Environment Setup
  • Variables
  • Data Types 
  • Constants 
  • Operators
  • Conditions
  • Loops
  • Strings
  • Arrays
  • Procedures
  • Functions
  • Cursors
  • Records
  • Exceptions
  • Packages
  • Triggers
  • Collections
  • Transactions
  • Date & Time
  • Object Oriented
  • DBMS Output

If you found any error with any of the docs please let us know.

 



Learning Objectives

Learn PL/SQL from a beginners perspective, this guide can also help you if you are trying to brush up your PLSQL skills

Author: Subject Coach
Added on: 20th Apr 2015

You must be logged in as Student to ask a Question.

None just yet!