Welcome

PL SQL Tutorial

---

PL SQL Variables

In PL/SQL a every for string literal,constant or number,date,time etc., memory will be allocated, Name is assigned to this memory location is called as a Variable. PL-SQL is statically typed programming language. every variables must be declared before its use. Pl/SQL variables are case-insensitive. Every variable must follow following guidlines

1. Variable Name should start with Alphabet, can contain upto 30 characters length
2. Multi-word variable can be seperated by underscore character
3. Variable can contain numbers.
4. Variables can contain $(dollar sign) and Hash(#) character
5. Variable names can contain spaces but it should be surrounded by Double Quotation Marks("")

PL/SQL Datatypes

• SQL DATA TYPES
• BOOLEAN
• PLS_INTEGER
• REF CURSOR

BOOLEAN Data Type

    BOOLEAN data type represents logical values.values are TRUE,FALSE and NULL.

In PL/SQL variables must be declared before its use. Optionally variables can be assigned with default values

DECLARE Section is used to declare a Variable or in the Package as a Global Variable

Declaring a Variable Syntax:

	variable_name  [CONSTRAINT] dataType [NOT NULL] [{:= | DEFAULT } initial value]
		

Declaring variables

			DECLARE
			  	COUNTRY_CODE  VARCHAR2(3) NOT NULL;
			  	MOBILE# NUMBER(10);
			  	JOIN_DATE DATE default SYSDATE;
			  	HEIGHT NUMBER(5,2);
			  	CURRENCY$  CHAR(1);
			BEGIN
				
			END;
		

Variable Initialization vs assigment

    Variables set to default values or with intial values in the DECLARE section is called 'variable initialization'. variable set to new value in Excecution Section, is called as 'variable assignment'

			DECLARE
			    A NUMBER := 10; -- initialization
			    B NUMBER := 0;  -- initialization
			    C NUMBER ;      -- neither intialized ,nor set to DEFAULT value
			BEGIN
				B:=22;
				C:=32;
			END;
			-- here B & C variables are assigned with new values. A is initialized with 10;
		

Note:Uninitialized variables are by default set to NULL

Anchored Declarations

    Declaring a variable with %TYPE or %ROWTYPE. %TYPE attribute used for basic data types in PL/SQL.%ROWTYPE is used for composite types like records, cursors etc., %TYPE attribute to anchor the datatype of a scalar variable or of another variable.

DECLARE

    --Refering a  variable using %TYPE  attribute
    -- today is of type date, where as nextDay is of today means indirectly referencing/specifying DATE type
			     
     today  DATE;
     nextDay today%TYPE
			     
     --refering  a table column
			     
     salary  scott.emp.sal%TYPE;

      --scott schema has  emp table has sal column			     

Anchored declartions are easy to specify Type, if varible is going to hold column values, it should match column specification(width,type,scale,precision etc.,) , it avoids many runtime errors.

SELECT INTO Clause

   Select into clause is used to assign a value to a variable(s).

     			SQL>SET SERVEROUTPUT ON;
     			
     			DECLARE
     			   today DATE;
     			
     			BEGIN
     			
     				SELECT SYSDATE INTO TODAY FROM DUAL;
	     			DBMS_OUTPUT.PUT_LINE(TODAY)     			;

     			END;
     			
     			SQL>/
     			09-10-22
     		

In above example single variable is used, select into can be used to set multiple variables at a time.,as shown below

DECLARE
                EMP_NAME  EMP.ENAME%TYPE; 
                EMP_JOB   EMP.JOB%TYPE;
                EMP_SALARY   EMP.SAL%TYPE;
                EMP_DEPARTMENT EMP.DEPTNO%TYPE;
                
BEGIN
     			
     		SELECT ENAME,JOB,SAL,DEPTNO INTO EMP_NAME,EMP_JOB,EMP_SALARY,EMP_DEPARTMENT  FROM EMP
                WHERE ENAME = 'KING';

                DBMS_OUTPUT.PUT_LINE(EMP_NAME||' '||EMP_JOB||' '||EMP_SALARY||' '||EMP_DEPARTMENT);
END;

SQL>/
KING PRESIDENT 5000 10

     		
     		

Get Database name using SELECT INTO FROM V$DATABASE

declare
 platform varchar2(30);
 dbid number;
 database_name varchar2(30);
 created_date  varchar2(30);
begin
select platform_name,dbid,name,created into
       platform,dbid,database_name,created_date from v$database;
dbms_output.put_line('Database Name'||' '||database_name);
end;

Constant variables

     The constant variable with in the declaration must have a initial value. Changing the value of a constant variable is a compile time error. This is also called as Read-Only Variables in PL/SQL Programs.

	PI CONSTANT NUMBER(6,5)  := 3.14285;

Assigning a value to constant variable in the execution section, gives following error expression 'PI' cannot be used as an assignment target