PL SQL Tutorial
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
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
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 columnAnchored 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 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
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;
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
ADS