Bind Variables

Bind Variables also we call as host variables .Check the use of bind variable

Bind Variables in SQL*PLUS:

You declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement(declare bind variable, assign value into bind variable and use bind variable in select statement)

SQL> variable deptno number

SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;

Bind Variables in PL/SQL:

PL/SQL itself takes care of most of the issues to do with bind variables, in fact every reference(p_empno) to a PL/SQL variable is a bind variable.

create or replace procedure upd_sal(p_empno in number)

as

  begin

    update emp

    set sal=sal*2

    where empno = p_empno;

    commit;

  end;

/

Bind Variables in Dynamic SQL:

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

create or replace procedure upd_sal(p_empno in number)

as

  begin

    execute immediate

     'update emp set

     sal = sal*2 where empno = :1' using p_empno;

  commit;

  end;

/

Constant In PL/SQL

•  We are using CONSTANT part of a PL/SQL declaration.
•  A constant is declared using the CONSTANT keyword.
•  CONSTANT requires an initial value.
•  CONSTANT remains unchanged throughout the program.

Syntax :
 constant_name CONSTANT datatype := VALUE;

Example:
 If you want to write a program which will calculate the interest of the bank. Bank interest we can declare  and we use it throughout in our program. If the interest got change then we need to change the CONSTANT value no need to change throughout the program.

CONSTANT remains unchanged throughout the program:

DECLARE   v_string  VARCHAR2(20);   n_number  NUMBER(10);   v_con_string CONSTANT VARCHAR2(20) := 'oracleguide4u';  BEGIN   v_string := 'oracleguide';   n_number := 1;   v_con_string := 'This will fail';   * ERROR at line 10: ORA-06550: line 10, column 3: PLS-00363: expression 'V_CON_STRING' cannot be used as an assignment target

DECLARE   v_string  VARCHAR2(20);   n_number  NUMBER(10);   v_con_string CONSTANT VARCHAR2(20) := 'oracleguide4u';  BEGIN   v_string := 'oracleguide';   n_number := 1;  END;

Data Type In PL/SQL

Every Variable has data type. Oracle provides many predefined data types or sub type.

PL/SQL supports four data type:

1. Scalar Data Type
2. Composite Data Type
3. Reference Data Type
4. LOB Data Type

1. Scalar Data Type:
It holds a single value and it supports Boolean type.

Scalar Data Types are classified into four categories :

Data Type	Description
Numeric	Numeric data type is used for arithmetic operations like PLS_INTEGER, BINARY_INTEGER,NUMBER etc.
Character	Alphanumeric value which represent single character or strings of characters or sub type like CHAR,VARCHAR2,NCHAR,NVARCHAR2 etc
Boolean	Boolean data type are used for logical operations like TRUE and FALSE.
Datetime	Date data type is used for store the date and time.

Example Of Sub type:

DECLARE
   SUBTYPE NAME IS char(20);
   SUBTYPE MSG IS varchar2(100);
   USER_NAME NAME;
   WEL_MSG MSG;
BEGIN
   USER_NAME := 'Reader ';
   WEL_MSG := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || USER_NAME||' '|| WEL_MSG);
END;

2. Composite Data Type:
In Composite datatype we can store whole row, if one row have three columns so we can store three values in a composite datatype variable at a time. 

Type Of Composite Data Type :

• RECORDS 
• COLLECTIONS

3. Reference Data Type:
It holds values , acting as pointers which pointers to other data items like REF CURSOR.


4. LOB Data Type:
LOB data type hold values called Locators, which specifying the location of large objects like graphic images, video clips, and sound wave forms.

Comments In PL/SQL

PL/SQL supports two type of comments

• Single line comments(-- comments)
• Multi line comments(/*comments*/).

Single line and Multi line comments example:

BEGIN     --This is oracle guide blogs [this is single line comments] null;     /* I am trying my level best to provide the right information.     If you find any thing contradictory please share with us.     We will review and take the corrective action.[this is multi line comments]*/ END;

• PL/SQL compiler ignores comments.
• Comments Should appear within a statements at the end of a line.
• Standard code always refer the Multi line comments . 

Anchored Variable Declarations(%TYPE)

• When we are declaring anchore data type variable in PL/SQL, that mean PL/SQL to set the datatype of that variable from the datatype of another element.
• The '%TYPE' variables is use to anchore PL/SQL variables to the database type columns directly.

Syntax:
<variable name> <type attribute>%TYPE ;
type attribute is nothing but previously declare PL/SQL variable or Table.column name.

Declarations with %TYPE:

Anchored Variable Declarations(%TYPE):

DECLARE /*V_EMPNAME is referring the table.column name*/ V_EMPNAME  EMP.ENAME%TYPE; /*V_EMPNAME is referring the previously declare PL/SQL variable*/ V_FULL_NAME    V_EMPNAME%TYPE;           BEGIN NULL; END;

Nesting Usages of the %TYPE Attribute:
See the below nest usages of %TYPE example

DECLARE    /* The base variable */    V_EMPNAME  EMP.ENAME%TYPE;    /* Anchored to V_EMPNAME */    V_FST_NAME    V_EMPNAME%TYPE;       /* Anchored to V_FST_NAME*/    V_FULL_NAME    V_FST_NAME%TYPE;    BEGIN null; END;

Why we will use %TYPE?
If the data type of a particular column is changing in the back end process of the serever,
either with data type or width, will never effect the PL/SQL blocks or it will not effect any operations.

Blocks In PL/SQL

A PL/SQL program are logical blocks and it can nested with one another.

The basic part of PL/SQL block are

• Declarative Part
• Executable Part
• Exception Handling Part

Type Of PL/SQL Block:

1. Anonymous Block
2. Named Block
3. Sub Program Block

1. Anonymous Block:

• Anonymous block known as un-named block.
• The anonymous block cannot be called by any other block.
• Anonymous block serve as scripts that execute PL/SQL statements .

Example Of Anonymous Block:

DECLARE /*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

2. Named Block:
They have all the features as specified for the Anonymous block but the only difference is that each block can be named if necessary.

Example Of Named Block:

<<FirstNameBlock>>  DECLARE /*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

3. Sub Program Block:

• Sub Program Blocks declare as procedure or function.
• It also called as Named block.
• The Sub Program block can be called by any other block.
• Sub Program Block helps developer to reuse the code(Code Reusability ).

Example Of  Sub Program Block:

	CREATE OR REPLACE PROCEDURE PROGRAM_BLOCK_EXAMPLE AS/*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

Variable In PL/SQL

---

SELECT INTO clause is used to place the returned data into predefined variables.

Use Of Variables:

• Reusability.
• Temporary Storage.
• Data Manipulation.
• Easy Of Maintenance.

Declare PL/SQL variables:

• All variables are declared and initialized only in the DECLARATIVE section.
• Maximum length  of variable is 30 characters .
• Variable name should be meaningful.
• PL/SQL is case-insensitive, which means v_all_data and V_ALL_DATA refer to the same variable.
• To declare the variable , variable name followed by data type and terminated by semicolon';'.

Variable naming convention:
You should follow the below variable naming convention , which is highly recommended for a standard code. 

Prefix	Data Type
v_	VARCHAR2
n_	NUMBER
t_	TABLE
r_	ROW
d_	DATE
b_	BOOLEAN

Assign value to a variable:
    In PL/SQL assigning values to a variable in three way

• By using assignment operator (:=).
• By fetching data base values (using INTO ).
• By passing IN,OUT or IN OUT to a subprogram.

Syntax:

VARIABLE_NAME DATATYPE  [NOT NULL :=DEFAULT VALUE];

Example:

DECLARE /*Declare variable , variable name V_EMPLOYEE_NAME*/  V_EMPLOYEE_NAME VARCHAR2(100);  BEGIN SELECT ENAME INTO V_EMPLOYEE_NAME FROM EMP WHERE EMPNO=7369;  DBMS_OUTPUT.PUT_LINE('Employee name is '||V_EMPLOYEE_NAME); END;

Base on variable declaration we can classify into two types

Local Variables: Local variable declare in inner block which cannot be referenced by outside blocks. 

Global Variables: Global variable declare in outer block and which can referenced by all inner blocks.

Example Of Global and Local Variables :

DECLARE  /*Declare global variable*/  V_GLOBAL_VAR varchar2(100):='Rabindra';  BEGIN DBMS_OUTPUT.PUT_LINE('Global variable in outer block :' ||V_GLOBAL_VAR);  declare   /*Declare local variable*/   V_LOCAL_VAR varchar2(100):='Oracle';  begin  /*Now i can access global variable in inner block*/  DBMS_OUTPUT.PUT_LINE('Global variable in inner block: '||V_GLOBAL_VAR);  DBMS_OUTPUT.PUT_LINE('Local variable in inner block: '||V_LOCAL_VAR);  end;  /*We cannot access Local variable in outer block, it will through error comment below DBMS_OUTPUT.PUT_LINE*/  --DBMS_OUTPUT.PUT_LINE('Local variable in outer block: '||V_LOCAL_VAR);  END;