Introduction to PLSQL execute immediate
PL/ SQL execute immediate statement is used for executing and running the dynamic statements in SQL or block of PL/ SQL code which is anonymous and not known in prior. We can make the use of PLSQL to execute immediate statements when we have to create or build the statements where we are not aware of all the where clauses, table names, restrictions, etc to be put up in prior or also when you cannot specify particular SQL statements or block directly or in advance. This statement prepares the program or statements at runtime and helps you in execution.
In this article, we will study the general syntax of execute immediate clause, its components, and also learn about its implementation with the help of certain examples.
Syntax:
The syntax of execute immediate is as shown below –
Variable for storing statement = EXECUTE IMMEDIATE “string created dynamically”
INTO [name of record | variables which is already defined]
USING [OUT | IN | IN OUT] argument which are bind, [ [OUT | IN | IN OUT] argument which are bind] …
RETURNING/ RETURN INTO [argument which are bind (s)]
The terminologies used in the above syntax are described one by one below –
String created dynamically – This can be an expression, variable, or a string literal which will be PL/ SQL block or even a single SQL query statement. Make sure that the datatype of this should be VARCHAR2 or CHAR and not NVARCHAR2 or NCHAR.
Name of record – This can be a %ROWTYPE record or any user-defined record that holds the values of the selected row.
INTO – This is only used when we have a single row query in SQL and is used for specifying the record or variable into which the values will be retrieved. For each of the column or expression value which is retrieved from the query statement output, there should be the declaration of the corresponding variable with same or compatible datatype into which the values with be returned.
Variables that are already defined – These is the variables that are name assigned to the memory location which will store the value of the selected column.
The argument which is bind – The variables that hold the value which is passed to the dynamic PL/ SQL statement for using it informing the statement or the value which is supposed to be returned from the PL/ SQL statement after it is created dynamically.
RETURNING INTO – This clause is used only in the case of DML statements that contain a returning clause in them. Note that it cannot be used for the BULK COLLECT clause. This helps in the specification of the variables which are bind to the values of the returned column. Make sure that for each of the values being returned by the DML statement there must be a corresponding type compatible variable present inside the RETURNING INTO statement.
USING – The default mode is IN for all the parameters. It helps you to specify the list of bind arguments which can be input or output type.
Variable for storing statement – This is the variables that are name assigned to a memory location that will store the value of the selected column.
Points to be considered while using execute immediately:
- We can specify all the arguments to be bind in the USING clause and b default all of them will be of IN mode parameters. We can place all the arguments of OUT mode in returning clause while using the DML statements having returning clause without mentioning the mode of a parameter as they are by default treated as OUT mode here.
- When we are making the use of USING and RETURNING both in the same PL/ SQL statement then we can only mention IN mode parameters in USING and OUT mode parameters in RETURNING clause.
- We can execute the same dynamic SQL statements again and again for different values of arguments to be bind. The EXECUTE IMMEDIATE clause prepares the dynamic query again each time. Hence, the overhead of repetitive execution of the same dynamic query with different bind arguments will still be there.
- The variable or string argument used for the storing of dynamic query statements in EXECUTE IMMEDIATE cannot have the national character type datatype which includes NVARCHAR2 and NCHAR.
- The string which is created dynamically can hold any SQL statement or PL/ SQL block of code except for the multi-row queries. There can be the occurrence of even the placeholders in the string for the arguments to bind. We cannot make the use of arguments to bind for passing the value of schema name to the dynamic query in PL/ SQL.
Example
Let us consider one example where we will create the dynamic query statements and use the EXECUTE IMMEDIATE to run them.
DECLARE
pl_sql_statement VARCHAR2(200);
block_of_code VARCHAR2(500);
customer_id NUMBER(4) := 1520;
bill_amount NUMBER(7,2);
store_id NUMBER(2) := 50;
store_name VARCHAR2(14) := 'Snehal's General Store';
city_name VARCHAR2(13) := 'Mumbai';
customer_record customer%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE scheme (id NUMBER, amt NUMBER)';
pl_sql_statement := 'INSERT INTO store VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE pl_sql_statement USING store_id, store_name, city_name;
pl_sql_statement := 'SELECT * FROM customer_details WHERE customer_id = :id';
EXECUTE IMMEDIATE pl_sql_statement INTO customer_record USING customer_id;
block_of_code := 'BEGIN customer_details_pkg.raise_bill_amount(:id, :amt); END;';
EXECUTE IMMEDIATE block_of_code USING 7788, 500;
pl_sql_statement := 'UPDATE customer_details SET billAmt = 2000 WHERE customer_id = :1
RETURNING billAmt INTO :2';
EXECUTE IMMEDIATE pl_sql_statement USING customer_id RETURNING INTO bill_amount;
EXECUTE IMMEDIATE 'DELETE FROM store WHERE storeno = :num'
USING store_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;
The output of the execution of the above code in PL/ SQL is as shown below –
Conclusion – PLSQL execute immediate
The EXECUTE IMMEDIATE statement is used in PL/ SQL while working with the dynamic query generation where the query statements to be executed are created at run time. These dynamic statements can be passed any bind arguments and also the values can be retrieved from them after executing it. The execute immediate command prepares the statements by collecting all the values received for bind arguments and parameters along with the query statements and variables that are specified.
Recommended Articles
This is a guide to PLSQL execute immediate. Here we discuss the general syntax of execute immediate clause, its components, and also learn about its implementation with the help of certain examples. You may also have a look at the following articles to learn more –
