PL-SQL
Oracle PL/SQL Trigger Tutorial: Instead of, Compound [Example]
What is Trigger in PL/SQL? TRIGGERS are stored programs that are fired by Oracle engine...
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
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 a run time need to be known before. It also gives better performance and less complexity when compares to DBMS_SQL.
Syntax
EXECUTE IMMEDIATE(<SQL>) [INTO<variable>] [USING <bind_variable_value>]
Example 1: In this example, we are going to fetch the data from emp table for emp_no '1001' using NDS statement.
DECLARE lv_sql VARCHAR2(500); lv_emp_name VARCHAR2(50): ln_emp_no NUMBER; ln_salary NUMBER; ln_manager NUMBER; BEGIN ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE emp_no=:empmo:; EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_manager USING 1001; Dbms_output.put_line('Employee Name:‘||lv_emp_name); Dbms_output.put_line('Employee Number:‘||ln_emp_no); Dbms_output.put_line(‘Salary:'||ln_salaiy); Dbms_output.put_line('Manager ID:‘||ln_manager); END; /
Output
Employee Name : XXX Employee Number: 1001 Salary: 15000 Manager ED: 1000
Code Explanation:
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.
Example 1: In this example, we are going to fetch the data from emp table for emp_no '1001' using DBMS_SQL statement.
DECLARE lv_sql VARCHAR2(500); lv_emp_name VARCHAR2(50); ln_emp_no NUMBER; ln_salary NUMBER; ln_manager NUMBER; ln_cursor_id NUMBER; ln_rows_processed; BEGIN lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE emp_no=:empmo’; in_cursor_id:=DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE); DBMS_SQL.BIXD_VARLABLE(ln_cursor_id:‘empno‘,1001); DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name); DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,ln_emp_no); DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,3,ln_salary); DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,4,ln_manager); ln_rows__processed:=DBMS_SQL.EXECUTE(ln_cursor_id);
LOOP IF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0 THEN EXIT; ELSE DBMS_SQL.COLUMN_VALUE(ln_cursor_id,1,lv_emp_name); DBMS_SQL.COLUMN_VALUE(ln_cursor_id,2,ln_emp_no); DBMS_SQL.COLUMN_VALUE(ln_cursor_id,3,In_salary); DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager); Dbms_output.put_line('Employee Name:‘||lv_emp_name); Dbms_output.put_line('Employee Number:l‘||ln_emp_no); Dbms_output.put_line(‘Salary:‘||ln_salary); Dbms_output.put_line('Manager ID :‘| ln_manager); END IF; END LOOP; DBMS_SQL.CLOSE_ClIRSOR(ln_cursor_id); END: /
Output
Employee Name:XXX Employee Number:1001 Salary:15000 Manager ID:1000
Code Explanation:
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.
What is Trigger in PL/SQL? TRIGGERS are stored programs that are fired by Oracle engine...
In this tutorial, we are going to learn how to use SQL in PL/SQL. SQL is the actual component that...
What is BULK COLLECT? BULK COLLECT reduces context switches between SQL and PL/SQL engine and...
What are Loops? Loops allows a certain part of the code in a program to get executed for the...
In this tutorial, we will introduce SQL* Plus and learn how to connect it to the database. After...
What is CASE Statement? A CASE statement is similar to IF-THEN-ELSIF statement that selects one...