PLSQL Datatypes and Variables

  • It supports all SQL datatypes(Scalar Datatypes + Boolean Datatypes)
  • LOBS (CLOB, BLOB, BFile)
  • Composite Datatypes
  • Ref Objects
  • Non PLSQL Variables (or) Bind Variables (or) Host Variables

Variables:

Variable is used to store a single value into memory location.

Syntax of variable:

VariableName Datatype(Size);

Generally we are declaring variables in declare section of the PLSQL Block

Syntax to declare:

A Number(10);

B Varchar2(10);

Storing a value into Variable:

We are storing a value into variable using assignment operator “:=”

Syntax:

VariableName:= Value;

Syntax:

DECLARE

A Number(10);

BEGIN

A:=50;

END;

Syntax Display a message (or) Variable Name:

DBMS_OUTPUT.PUT_LINE(‘message’);

(or)

DBMS_OUTPUT.PUT_LINE(VariableName);

By default buffer value is 2000 bytes

To view the PLSQL Version:

SELECT * FROM V$version;

Syntax for printing message:

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Welcome to Oracle’);

END;

Output=> Welcome to Oracle

Example:

DECLARE

A number(10);

BEGIN

A:=100;

DBMS_OUTPUT.PUT_LINE(A);

END;

Output: 100

Select INTO clause:

This clause is used to retrieve data from table and storing into PLSQL variables, select into clause always returns single record (or) single value at a time.

Syntax:

SELECT column1, column2, column3

INTO Variable1, variable2, variable3

FROM TableName

WHERE Condition;

Select into clause used in executable section of PLSQL Block