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
- Variable_name is a name of the variable.
- Datatype will be the valid PL/SQL DATA TYPE.
- NOT NULL is an optional,if we declare the variable with NOT NULL then we have to give it his initial value.
- Value can be anything as a data.
- 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
- Directly assign
- 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.