Dynamic SQL is a programming methodology for generating and running statements at run-time. It is mainly used to write the general-purpose and flexible programs where the SQL statements will be created and executed at run-time based on the requirement.
In this tutorial, you will learn-
PL/SQL provides two ways to write dynamic SQL
- NDS – Native Dynamic SQL
Native Dynamic SQL is the easier way to write dynamic SQL. It uses the 'EXECUTE IMMEDIATE' command to create and execute the SQL at run-time. But to use this way, the datatype and number of variable that to be used at run time needs to be known before. It also gives better performance and less complexity when compares to DBMS_SQL.
- The above syntax shows EXECUTE IMMEDIATE command.
- Clause INTO is optional and used only if the dynamic SQL contains a select statement that fetches values. The variable type should match with the variable type of the select statement.
- Clause USING is optional and used only if the dynamic SQL contains any bind variable.
Example1: In this example, we are going to fetch the data from emp table for emp_no '1001' using NDS statement.
- Code line 2-6: Declaring variables.
- Code line 8: Framing the SQL at run-time. SQL contains the bind variable in where condition ':empno'.
- Code line 9: Executing the framed SQL text (which is done in code line 8) using the NDS command 'EXECUTE IMMEDIATE'
- The variables in 'INTO' clause (lv_emp_name, ln_emp_no, ln_salary, ln_manager) is used to hold the fetched values from the SQL query (emp_name, emp_no, salary, manager)
- 'USING' clause gives the values to the bind variable in the SQL query (:emp_no).
- Code line 10-13: Displaying the fetched values.
PL/SQL provide the DBMS_SQL package that allows you to work with dynamic SQL. The process of creating and executing the dynamic SQL contains the following process
- OPEN CURSOR: The dynamic SQL will execute in the same way as a cursor. So in order to execute the SQL statement, we must open the cursor.
- PARSE SQL: The next step is to parse the dynamic SQL. This process will just check the syntax and keep the query ready to execute.
- BIND VARIABLE Values: The next step is to assign the values for bind variables if any.
- DEFINE COLUMN: The next step is to define the column using their relative positions in the select statement.
- EXECUTE: The next step is to execute the parsed query.
- FETCH VALUES: The next step is to fetch the executed values.
- CLOSE CURSOR: Once the results are fetched, the cursor should be closed.
Example1: In this example, we are going to fetch the data from emp table for emp_no '1001' using DBMS_SQL statement.
- Code line 1-9: Variable declaration.
- Code line 10: Framing the SQL statement.
- Code line 11: Opening the cursor using DBMS_SQL.OPEN_CURSOR. It will return the cursor id which is opened.
- Code line 12: After the cursor is opened, the SQL is parsed.
- Code line 13: Bind variable '1001' is assigning to the cursor id instead ':empno'.
- Code line 14-17: Defining the column name based on their relative position in the SQL statement. In our case, the relative position is (1) emp_name, (2) emp_no (3) salary (4) manager. So based on this position we are defining the target variable.
- Code line 18: Executing the query using DBMS_SQL.EXECUTE. It returns the number of records processed.
- Code line 19-33: Fetching the records using a loop and displaying the same.
- Code line 20: DBMS_SQL.FETCH_ROWS will fetch one record from the rows processed. It can be called repeatedly to fetch all the rows. If it cannot fetch rows, it will return 0, thus exiting the loop.
In this section, we have discussed dynamic SQL and the ways to execute DYNAMIC SQL. We have also seen the different steps in executing the dynamic SQL in both the ways. We have also seen the examples in which the same scenario is handled in both NDS and DBMS_SQL ways to perform execution at run-time.