DeFuser.Tk Blog

PL/SQL Placeholders can be any variables,constants,cursors and records.Placeholders is temporary storage area, with the help of placeholders program manipulate the data during execution of a PL/SQL block. With the respect of data you want to store you can define the placeholder with the name of data type.some of them or written below    

• varchar2
• char
• date
• long
• number
• Blob
• Nclob

etc PL/SQL VARIABLES : Variables store the value and it can be changed with the help of PL/SQL Block    

1. Variable_name is a name of the variable.
2. Datatype will be the valid PL/SQL DATA TYPE.
3. NOT NULL is an optional,if we declare the variable with NOT NULL then we have to give it his initial value.
4. Value can be anything as a data.
5. All the declared variables will be ended with semicolon.

1 Example

DECLARE payment number(10);

  Here the word " payment " is the variable name and the word " number " is the PL/SQL valid Data type whereas the digits between parentheses show that the number will be maximum 10 digit long.

  2 Example with NOT NULL

DECLARE partyname varchar2(20) NOT NULL :=" Packers Mart Ltd ";

Here you can see we declared the variable name " partyname " with data type " varchar2 " maximum length of 20 and we used NOT NULL there for now we have to give it the initial value like as " Packers Mart Ltd " later we can change it.

 There are two ways that can assign the value of a variable

1. Directly assign
2. Assign by database columns

Directly Assign The method of directly assigning the value of variable is

variable_name := value;

Assign by database columns

 We can assign the value from database columns to the variable here is an example

SELECT column_name
INTO variable_name
From table_name [condition] ;

at the end of the program we will write the backward slash " / " in a PL/SQL block for indication to execute the below program

  example

DECLARE
user_name varchar2(20); // SEE NOTE # 1
user_number number(20) = 3247683005 ; // SEE NOTE # 2BEGIN SELECT u_name
INTO user_name
FROM users
WHERE u_number=user_number;
dbms_output.put_line(user_name);
dbms_output.put_line('The user whom number is' || user_number || ' has name ' || user_name ) ;
END; /

NOTE # 1: Here we declare the variable named " user_name " and datatype is " varchar2 " with maximum length 20.
  NOTE # 2: here we declare the variable named " user_number " and datatype is " number " with maximum length 20.