PL/SQL Tutorial

PL/SQL Variables: Declaration, Initialization and Scope

PL/SQL Variables

PL/SQL Variables and It’s Uses

Variables are the placeholder used for:

  • Temporary storage of data.
  • Manipulation of stored data.
  • re-usabilityPL/SQL Variables

Points to consider for Variables in PL/SQL

  • Variables are declared and initialized in the declarative section.
  • Variables are used and assigned new values in the executable section.
  • Variables are passed as parameters to PL/SQL subprograms.
  • Variables are used to hold the output of PL/SQL subprograms.

Naming rules for PL/SQL Variables

In PL/SQL Variables Name:

  • Can include numbers and letters.
  • Must start with a letter.
  • Can include special characters (such as $, _, and # ).
  • Should not contain more than 30 characters.
  • Should not contain reserved words (like INTO, SELECT, FROM, IF etc.)

Declaring and Initializing PL/SQL Variable

Variables are declared and initialized in the declarative section. Below is the syntax:

identifier[CONSTANT] datatype[NOT NULL] [:= | DEFAULT expr];

Example:

[pastacode lang=”sql” manual=”DECLARE%0Av_hiredate%20%20%20%20%20DATE%3B%0Av_deptno%20%20%20%20%20%20%20NUMBER(2)%20NOT%20NULL%20%3A%3D%2010%3B%0Av_location%20%20%20%20%20VARCHAR2(13)%20%3A%3D%20’Texas’%3B%0Ac_comm%20%20%20%20%20%20%20%20%20CONSTANT%20NUMBER%20%3A%3D%20100%3B” message=”Variable declaration and initialization in PL/SQL” highlight=”” provider=”manual”/]


PL/SQL Variables Scope: Local & Global Variable

PL/SQL have two type of scopes: local scope and global scope

Local variables – Variables declared in inner block and can’t be referenced by the outside blocks.
Global variables – Where as variables declared in an outer block and can be referencing by itself in inner blocks.

[pastacode lang=”sql” manual=”DECLARE%20%0A%20%20%20–%20Global%20variables%20%20%0A%20%20%20num1%20number%20%3A%3D%2095%3B%20%20%0A%20%20%20num2%20number%20%3A%3D%2085%3B%20%20%0ABEGIN%20%20%0A%20%20%20dbms_output.put_line(‘Outer%20Variable%20num1%3A%20’%20%7C%7C%20num1)%3B%20%0A%20%20%20dbms_output.put_line(‘Outer%20Variable%20num2%3A%20’%20%7C%7C%20num2)%3B%20%0A%20%20%20DECLARE%20%20%0A%20%20%20%20%20%20–%20Local%20variables%20%0A%20%20%20%20%20%20num1%20number%20%3A%3D%20195%3B%20%20%0A%20%20%20%20%20%20num2%20number%20%3A%3D%20185%3B%20%20%0A%20%20%20BEGIN%20%20%0A%20%20%20%20%20%20dbms_output.put_line(‘Inner%20Variable%20num1%3A%20’%20%7C%7C%20num1)%3B%20%0A%20%20%20%20%20%20dbms_output.put_line(‘Inner%20Variable%20num2%3A%20’%20%7C%7C%20num2)%3B%20%0A%20%20%20END%3B%20%20%0AEND%3B%20%0A%2F%20″ message=”Global and Local Variable in PL/SQL” highlight=”” provider=”manual”/]

Output:

Outer Variable num1: 95 
Outer Variable num2: 85 
Inner Variable num1: 195 
Inner Variable num2: 185  

PL/SQL procedure successfully completed.

PL/SQL: Delimiters in String Literals

[pastacode lang=”sql” manual=”DECLARE%0Av_event%20VARCHAR2(15)%3B%0ABEGIN%0Av_event%20%3A%3D%20q’!Father’s%20day!’%3B%0ADBMS_OUTPUT.PUT_LINE(‘3rd%20Sunday%20in%20June%20is%20%3A%0A’%7C%7C%20v_event%20)%3B%0Av_event%20%3A%3D%20q’%5BMother’s%20day%5D’%3B%0ADBMS_OUTPUT.PUT_LINE(‘2nd%20Sunday%20in%20May%20is%20%3A%0A’%7C%7C%20v_event%20)%3B%0AEND%3B%0A%2F” message=”PL/SQL Program contains Delimiters in String Literals” highlight=”” provider=”manual”/]

Output:

anonymous block completed
3rd 3rd Sunday in June is : Father's day
2nd Sunday in May is : Mother's day

Types of Variables

PL/SQL Variables

  • Scalar
  • Composite
  • Reference
  • Large object (LOB)

Non-PL/SQL variables

  • Bind variables

 Note:

  • Avoid using column names as identifiers.
  • Use the NOT NULL constraint when the variable must hold a value.
  • Initialize variables with the assignment operator (:=) or the DEFAULT keyword.
  • Initialize variables designated as NOT NULL and CONSTANT.

In Next Tutorial Learn about Datatypes of PL/SQL.

Leave a Reply