Updated March 31, 2023
Introduction to PLSQL bind variables
The following article provides an outline for PLSQL bind variables. Oracle provides the bind variable functionality to the user, in which we can create the variable and that variable we can use in different subprograms as per our requirement. Consider if we want to access bind variables in PL/SQL from SQL*Plus. So at that time, first we need to create the bind variable in SQL*plus then we are able to access that created variable in PL/SQL. Basically, the bind variable is used to store the return code, or we can say that subprogram and that we access whenever we require it. We can create bind variables with different data types.
Syntax
variable variable_name data typeExplanation
Syntax of bind variable is very simple; in the above syntax, we use different parameters such as variable as a keyword, variable_name means the actual name of the variable that we need to create, and data type is used to assign a particular data type as per our requirement.
How to bind variables in PL/SQL?
Now let’s see how we can bind the variable in Pl/SQL as follows.
We need to follow the different steps to bind the variable in Pl/SQL as follows.
- First, we need to declare a bind variable:
In the first step, we need to declare the bind variable by using the VARIABLE command, which can be referred to in PL/SQL.
For example:
VARIABLE sample number;Explanation
See, here, it is very simple to declare the bind variable as well as learn how to declare a bind variable in an Oracle database. You just need to create a command that begins with the keyword VARIABLE and includes the name of your user-defined bind variable, as well as the data type and data width. In Oracle, this is how we declare a bind variable. Notice here there is no need to write any PL/SQL block, or we can also say that section to declare here.
- In the second step, we need to reference the bind variable:
After the declaration of bind variable in SQL* Plus, we can access that variable, or we can say that reference the bind variable in Pl/SQL by using a colon (:) immediately followed by the variable name.
For example:
: sample:=2;Explanation
In the above example, we first type a colon (:) immediately followed by the variable name. This is a very simple way to reference the bind variable. If we want to change the bind variable in SQL*plus, then we need to write the PL/SQL block.
- In the third step, we can display the bind variable:
We already discussed how we can reference the bind variable; now, let’s see how we can display the bind variable by using different methods as follows.
1. By using DBMS_OUTPUT command as follows:
In PL/SQL, this is the easiest way to display the value of any variable. To show the value of your bind variable, just give the name of your bind variable as a parameter to the DBMS_OUTPUT package’s PUT LINE function.
In this method, we must take care of the following point as follows.
PUT_LINE is a statement that must be executed using the PL/SQL block’s execution section. Simply put, you can only run this statement in the execution portion of the PL/SQL block; otherwise, an error will occur.
You have switched on the server output option to see the output of this statement. You may accomplish so simply by writing and running code. So we need to set
set serveroutput on ;2. By using the auto print option as follows:
Setting a session-based parameter AUTO PRINT on is the last option for showing the current value of a bind variable. By doing so, you may see the values of all the bound variables without having to use any special commands like Print or DBMS OUTPUT, which we just saw.
3. By using print option as follows:
To print the bind variable, we need to follow the following syntax as follows.
Syntax
print variable nameExplanation
In the above syntax, we use the print command to display the bind variable. In this syntax, print immediately followed by the variable name as shown in the above syntax.
We can also perform the different variable-related commands as follows.
Suppose we need to list all bind variables at that time; we can use the following command as follows.
VARIABLE;Explanation
By using the above command, we can list all bind variables that we declared in the session. So we can simply write the variable and execute it.
Suppose we need to see the definition of bind variable at that time; we can use the following command as follows.
VARIABLE NAME OF VARIABLE;Explanation
The variable command is used to show the definition of any bind variable that we created in the session.
When SQL statements contain literals, the shared pool is prone to become clogged with similar statements. When you run the same query several times with different literals in the WHERE clause, Oracle parses the statement each time. The execution strategy is often the same, especially when the data isn’t severely distorted. Such a long-consuming parse is a waste of resources: the parsing takes CPU time, and the numerous cursors clog up the common pool.
The solution to this problem is that the bind variable acts as a placeholder in a SQL query that must be updated with a valid value or value address in order for the query to run correctly. You may construct a SQL query that accepts inputs or arguments at run time by utilizing bind variables, and it is a very simple placeholder.
Examples of PLSQL bind variables
Now let’s see the example of a bind variable in PLSQL as follows.
First, we need to create the bind variable as follows.
VARIABLE VALUE NUMBERExplanation
In the above example, we create a bind variable that is the value data type of that variable is an integer. The final result of the above statement we show in the below screenshot as follows.
After that, we need to write the PLSQL block for the bind variable as follows.
BEGIN
: VALUE :=100;
END;
/Explanation
In the above example, we write the PLSQL block for the bind variable, and we put 100 into the bind variable. The final result of the above statement we show below screenshot as follows.
Suppose we need to display the bind variable at that time; we can use the following command as follows.
PRINT VALUEExplanation
The final result of the above statement we show in the below screenshot as follows.
Conclusion
We hope from this article you learn PLSQL bind variables. From the above article, we have learned the basic syntax of bind variables, and we also see different examples of the bind variables. From this article, we learned how and when we use PLSQL bind variables.
Recommended Articles
We hope that this EDUCBA information on “PLSQL bind variables” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
