oracle dynamic sql
DESCRIPTION
this is ppt from satyam webinar....TRANSCRIPT
Dynamic SQLDynamic SQLMoiz Lokhandwala
Business Learning Center
Business Learning Center 1Satyam Confidential
ObjectivesObjectives
SQL Statement ProcessingBindingProblem With Static SQLNative Dynamic SQL..The Real HeroCursor OverviewOracle Supplied Package:- DBMS_SQLThe Unknown
Business Learning Center 2Satyam Confidential
Static SQL Statement ProcessingStatic SQL Statement Processing
Business Learning Center Satyam Confidential 3
Compile
Time
Dynamic SQL Statement ProcessingDynamic SQL Statement Processing
Business Learning Center Satyam Confidential 4
Run
Ti
me
Binding In PL\SQLBinding In PL\SQL
• Oracle needs values for any variables listed in the statementfor example:-
select empno,ename from emp where id=emp_id;
Oracle needs a value for emp_id. The process of obtaining these values is called
binding variables.
• Compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time.
Business Learning Center Satyam Confidential 5
Binding In PL\SQLBinding In PL\SQL
• Static Binding Binding at compile time, called static or early binding. Increases efficiency
• Dynamic Binding Binding at run time, called dynamic or late binding. Increases flexibility
Business Learning Center Satyam Confidential6
Some LimitationsSome Limitations
For Example.
CREATE PROCEDURE create_table AS
BEGIN
CREATE TABLE dept (deptno NUMBER(2), ...); -- illegal Table is undefined until the procedure is executed at run time.
…
END;
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
DROP TABLE table_name; -- illegal Table name is unknown until the procedure is executed.
...
END;
Business Learning Center Satyam Confidential 7
LimitationLimitation
Business Learning Center Satyam Confidential 8
• Static SQL
Can’t Use in PL\SQL Directly
• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE. • Session control language (SCL) statements, such as ALTER SESSION and SET ROLE.
Overcoming LimitationOvercoming Limitation
• Dynamic SQL
Business Learning Center Satyam Confidential 9
Static SQLStatic SQL• Static SQL statements do not change from execution to execution.• The full text of static SQL statements are known at compilation.
• Provides Benefits SQL statements reference valid database objects. Necessary privileges are in place to access the database objects. Performance of static SQL is generally better than dynamic SQL.
• Problem With Static SQL
Full text of the SQL statements may not known. Program may accept user input. Program may need to complete some processing work to determine the correct course of action
Business Learning Center Satyam Confidential 10
One More ExampleOne More Example
Business Learning Center Satyam Confidential 11
• Table name is Unknown
TechniquesTechniques
• EXECUTE IMMEDIATE
• DYNAMIC CURSORS
• DBMS_SQL
Business Learning Center Satyam Confidential 12
Execute ImmediateExecute Immediate
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block.
Business Learning Center Satyam Confidential 13
EXECUTE IMMEDIATE dynamic_string[INTO {define_variable[, define_variable]... | record}][USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];
Dynamic_string String expression that represents a SQL statement or PL/SQL block. Define_variable Stores a selected column value. Record is a user-defined or %ROWTYPE record that stores a selected row. Input bind_argument An expression whose value is passed to the dynamic SQL statement or PL/SQL block. Output bind_argument Variable that stores a value returned by the dynamic SQL statement or PL/SQL block.
• Into:- declare v_emp_sal emp.emp_sal%type; v_emp_name emp.emp_name%type;
v_emp_id emp.emp_id%type; begin Select emp_name,sal into v_emp_name,v_sal from emp where id=v_emp_id; end;
• Record:- begin create record v_emo_rec (id varchar2(10),name varchar2(20));
--catching data into record. select emp_id_emp_name into v_emp_rec from emp where id=10000; end;
• Returning …..into :- declare v_emp_name emp.emp_name%type; begin update emp set sal=sal*0.1 where emp_id=1000 returning emp_name into v_emp_name; end;
Business Learning Center Satyam Confidential 14
Understand through Example.Understand through Example.DECLARE
sql_stmt VARCHAR2(200);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
emp_rec emp%ROWTYPE;
begin
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
End;
• You can place all bind arguments in the USING clause by default mode is IN.
• The INTO clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.
• For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT.
• At run time, bind arguments replace corresponding placeholders in the dynamic string. So, every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause
15Business Learning Center Satyam Confidential
The ProblemThe Problem• Function returns column value for a given column name from specified table. ,which passed at
runtime
function F_Get_Emp_Col_Value(table_name,colmn_name,cond_col,cond_value) return varchar2 is v_out_tx varchar2(4000); v_sql_tx varchar2(32000);Begin
v_sql_tx:=' select '||colmn_name|| ' from '||table_name|| ' where '||cond_col||'='||cond_value; -- Use concatenation method
EXECUTE IMMEDIATE v_sql_tx INTO v_out_tx;
return v_out_tx;end;
• Imagine running the example several times: f_get_Column_tx('emp','eName','empNo',7896) f_get_Column_tx('emp','eName','empNo',4564) f_get_Column_tx('emp','eName','empNo',4546)
• Result:o SGA is occupied with exactly the same statements.
select eName from emp where empNo=7896
select eName from emp where empNo=4564 select eName from emp where empNo=4546
o CPU time is spent re-parsing exactly the same statement.
16Business Learning Center Satyam Confidential
The SolutionThe Solution• Use bind variables
o Bind variables CAN only be used to supply values to be passed to SQL code.o Bind variables are substituted with real values AFTER parsing
function F_Get_Emp_Col_Value(table_name,colmn_name,cond_col,cond_value) return varchar2 is v_out_tx varchar2(4000); v_sql_tx varchar2(32000);Begin
v_sql_tx:=' select '||colmn_name|| ' from '||table_name|| ' where '||cond_col||'='||:bind1;
--Binding involves the use of placeholders and the USING clause
EXECUTE IMMEDIATE v_sql_tx INTO v_out_tx using cond_value;
return v_out_tx;end;
• You cannot bind in the names of schema elements (tables, columns, and so on) or chunks of the SQL statement (such as the WHERE clause). For those parts of your string, you must use concatenation.
17Business Learning Center Satyam Confidential
Things To KnowThings To Know
• Dynamic SQL does not support any PL/SQL data types.
o Define variables and bind arguments cannot be Booleans or index-by tables.
Exception: You can use RECORD as an output of a dynamic query(in the INTO clause).
• Dynamic SQL does support all Built in and user-defined SQL data types.
o Define variables and bind arguments can be collections, LOBs, instances of an object
type, and refs.
18Business Learning Center Satyam Confidential
Dynamic SQL: The Real HeroDynamic SQL: The Real Hero
• Makes it possible to build and process complete SQL and PL/SQL statements as strings at runtime.
• For example, a reporting application in a data warehouse environment might not know the exact table name until runtime. These tables might be named according to the starting month and year of the quarter, for example INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, and so on. You can use dynamic SQL in your reporting application to specify the table name at runtime.
• Within PL/SQL, you can execute any kind of SQL or PL/SQL statement (even data definition and data control statements) without resorting to cumbersome programmatic approaches.
• Similar to DBMS_SQL, but easier to use.
• Dynamic SQL blends seamlessly into your programs, making them more efficient, readable, and concise.
Business Learning Center Satyam Confidential 19
Traditional WayTraditional Way• An application that takes an event number and dispatches to a handler for the event. The name of the handler is in the form EVENT_HANDLER_event_num, where event_num is the number of the event.
CREATE OR REPLACE PROCEDURE event_handler_1(param number) AS BEGIN -- process event RETURN; END;
CREATE OR REPLACE PROCEDURE event_handler_2(param number) AS BEGIN -- process event RETURN; END;
CREATE OR REPLACE PROCEDURE event_handler_2(param number) AS BEGIN -- process event RETURN; END;
CREATE OR REPLACE PROCEDURE event_dispatcher (event number, param number) IS BEGIN IF (event = 1) THEN EVENT_HANDLER_1(param); ELSIF (event = 2) THEN EVENT_HANDLER_2(param); ELSIF (event = 3) THEN EVENT_HANDLER_3(param); END IF; END;
Business Learning Center Satyam Confidential 20
Using Execute ImmediateUsing Execute Immediate
• Using native dynamic SQL, you can write a smaller, more flexible event dispatcher.
CREATE OR REPLACE PROCEDURE event_dispatcher (event NUMBER, param NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'BEGIN EVENT_HANDLER_' || to_char(event) || '(:1) ; END; ' USING param;
END;
Business Learning Center Satyam Confidential 21
ScenarioScenarioThe database in this scenario is a company's human resources database (named hr) with the following data model:
• A master table Offices contains the list of all company locations.
Offices
• Multiple emp_location tables contain the employee information, where location is the name of city where the office is located. For example, a table named emp_houston contains employee information for the company's Houston office, while a table named emp_boston contains employee information for the company's Boston office.
Business Learning Center Satyam Confidential 22
Location
USAINDIAKUWAIT
EMPNO ENAME JOB SAL DEPTNO
1 Sameer Sales-Man 20000 12
2 Kailash Executive 25000 10
DDL Operation Using Native DDL Operation Using Native Dynamic SQLDynamic SQL
• The EXECUTE IMMEDIATE statement can perform DDL operations.
• Truly dynamic SQL occurs when you literally construct the SQL statement from runtime variable values.
For example, the following procedure adds an office location:CREATE OR REPLACE PROCEDURE add_location (loc VARCHAR2)
IS
BEGIN
-- insert new location in master table
INSERT INTO offices VALUES (loc);
-- create an employee information table
EXECUTE IMMEDIATE 'CREATE TABLE ' || 'emp_' || loc || '( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), deptno
NUMBER(2) )';
END;
23Business Learning Center Satyam Confidential
Single-Row Query Using Native Single-Row Query Using Native Dynamic SQLDynamic SQL
• The EXECUTE IMMEDIATE statement can perform dynamic single-row queries. You can specify bind variables in the USING clause and fetch the resulting row into the target specified in the INTO clause of the statement.
• The following function retrieves the number of employees at a particular location performing a specified job:
CREATE OR REPLACE FUNCTION get_num_of_employees (loc VARCHAR2, job VARCHAR2) RETURN NUMBER
IS
query_str VARCHAR2(1000);
num_of_employees NUMBER;
BEGIN
query_str := 'SELECT COUNT(*) FROM ' || ' emp_' || loc || ‘ WHERE job = :job_title ';
EXECUTE IMMEDIATE query_str INTO num_of_employees USING job;
RETURN num_of_employees;
END;
24Business Learning Center Satyam Confidential
Dynamic CursorDynamic Cursor
Business Learning Center Satyam Confidential 25
Cursor
100 King AD_PRES101 Kochhar AD_VP
102 De Haan AD_VP
. . .
139 Seo ST_CLERK
140 Patel ST_CLERK
Active Set
Table
• Create a named SQL area
DECLAREDECLARE
• Identify the active set
OPENOPEN
• Load the current row into variables
FETCHFETCH EMPY?EMPY?• Return to FETCH if
rows are found
• Test for existing rows
No
• Release the active set
CLOSECLOSE
Yes
Explicit Cursor:-
Cursor C is Select id, name,job from emp where sal>1000
Dynamic CursorDynamic CursorREF Cursor:-• A REF CURSOR is basically a data type. A variable created based on such a data type is
generally called a cursor variable. It’s a cursor variable.
• Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself.
• In PL/SQL, a pointer has data type REF, where REF is short for REFERENCE Therefore, a cursor variable has data type REF CURSOR..
• Cursor always refers to the same query work area, a Cursor variable can refer to different work areas.
• A cursor is static, A cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query.
26Business Learning Center Satyam Confidential
Define a REF CURSOR type:
TYPE REF_TYPE IS REF CURSOR [return return type]
Define a REF CURSOR type:
TYPE REF_TYPE IS REF CURSOR [return return type]
Multiple-Row Query Using Native Multiple-Row Query Using Native Dynamic SQL Dynamic CursorDynamic SQL Dynamic Cursor
• The OPEN-FOR, FETCH, and CLOSE statements can perform dynamic multiple-row queries. For example, the following procedure lists all of the employees with a particular job at a
specified location.
CREATE OR REPLACE PROCEDURE list_employees(loc VARCHAR2, job VARCHAR2) IS TYPE cur_typ IS REF CURSOR; C cur_typ; query_str VARCHAR2(1000); emp_name VARCHAR2(20); emp_num NUMBER; BEGIN query_str := 'SELECT ename, empno FROM emp_' || loc || ' WHERE job = :job_title'; -- find employees who perform the specified job OPEN C FOR query_str USING job; LOOP FETCH C INTO emp_name, emp_num; EXIT WHEN C%NOTFOUND; -- process row here END LOOP; CLOSE C;
END;
27Business Learning Center Satyam Confidential
Things to knowThings to know
• The code can be passed as a variable/string.
• The variable/string cannot exceed 32K.
• PL/SQL - semicolon at the end; don’t return anything
execute immediate 'begin p_test; end;';
• SQL – no semicolon at the end; must return the result into the variable of corresponding type (if anything is returned):
execute immediate 'select 1 from dual‘ into a;
Business Learning Center Satyam Confidential 28
DBMS_SQLDBMS_SQL• Predecessor of native dynamic SQL
• The DBMS_SQL package is a PL/SQL library that offers an API to execute SQL statements dynamically.
• Programs that use the DBMS_SQL package make calls to this package to perform dynamic SQL operations.
• The DBMS_SQL package has procedures to open a cursor, parse a cursor, supply binds, and so on.
Business Learning Center Satyam Confidential 29
DBMS_SQL:- Execution FlowDBMS_SQL:- Execution Flow
Business Learning Center Satyam Confidential 30
OPEN_CURSOR
PARSE
DEFINE_COLUMNDEFINE_ARRAY
EXECUTE_AND_FETCH
COLUMN_VALUEVARIABLE_VALUE
CLOSE_CURSOR
BIND_VARIABLEBIND_ARRAY
Some Methods SyntaxSome Methods Syntax• OPEN_CURSOR , CLOSE_CURSOR Function:-
Opens a new cursor. When you no longer need this cursor, you must close it explicitly by calling CLOSE_CURSOR.
Syntax
DBMS_SQL.OPEN_CURSOR RETURN INTEGER;
Returns the cursor ID number of the new cursor
• PARSE Procedure BIND_VARIABLE
Syntax Syntax
DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2, DBMS_SQL.BIND_VARIABLE ( c IN INTEGER, name IN language_flag IN INTEGER) VARCHAR2, value IN <datatype>) ;
Business Learning Center Satyam Confidential 31
Some Methods Syntax Cont..Some Methods Syntax Cont..• EXECUTE_AND_FETCH Function
Syntax DBMS_SQL.EXECUTE_AND_FETCH ( c IN INTEGER, exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;
• The EXECUTE_AND_FETCH function returns the number of rows actually fetched.
• VARIABLE_VALUE Procedure
Syntax DBMS_SQL.VARIABLE_VALUE ( c IN INTEGER, name IN VARCHAR2, value OUT <datatype>);
Business Learning Center Satyam Confidential 32
DBMS_SQL ExampleDBMS_SQL Example
Business Learning Center Satyam Confidential 33
DBMS_SQL ExampleDBMS_SQL Example
Business Learning Center Satyam Confidential 34
DBMS_SQL ExampleDBMS_SQL Example
Business Learning Center Satyam Confidential 35
Native Dynamic SQL Vs DBMS_SQLNative Dynamic SQL Vs DBMS_SQL
Business Learning Center Satyam Confidential 36
Native Dynamic Sql DBMS_SQL
Native dynamic SQL is integrated with SQL The DBMS_SQL package is a PL/SQL library that offers an API to execute SQL statements dynamically
Native dynamic SQL code is typically more compact and readable
Equivalent code is more lengthy and complex.
Programs that use native dynamic SQL are much faster than programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent DBMS_SQL calls.
The DBMS_SQL package is based on a procedural API and incurs high procedure call and data copy overhead.
Native Dynamic SQL Supports User-Defined Types The DBMS_SQL package does not support user-defined objects, collections, and REFs.
Native dynamic SQL is not supported in client-side Programs. DBMS_SQL is Supported in Client-Side Programs
Native Dynamic Sql statements can’t be larger than 32KB. DBMS_SQL Supports SQL Statements Larger than 32KB
Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation.
DBMS_SQL Lets You Reuse SQL Statements.The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.
The ProblemThe Problem
Business Learning Center Satyam Confidential 37
• It is difficult to build a system that actually meets the stated requirements.
• It is very difficult to build a system that doesn’t require massive changes within a short period of time.
• It is impossible to build a system that will not be obsolete sooner or later.
UnknownsUnknowns
Business Learning Center Satyam Confidential 38
• What elements are involved?– Example: The monthly summary table may not exist.
• What should be the search criteria?– Example: Always one type of Search condition require?
• How you should proceed?– Example: Is a hash join hint the best option?
• Whether or not you can proceed?– DDL is still prevented in PL/SQL, isn’t it?
Where To Use Dynamic SQLWhere To Use Dynamic SQL
• Many types of applications need to use dynamic queries Example:-
o Applications that allow users to input or choose query search or sorting criteria at runtime
o Applications that allow users to input or choose optimizer hints at run time o Applications that query a database where the data definitions of tables are
constantly changing o Applications that query a database where new tables are created often
Business Learning Center Satyam Confidential 39
Questions & Open DiscussionsQuestions & Open Discussions
Business Learning Center Satyam Confidential 40
Please provide your valuable feedback to
Venkataramana Kolla
Email : [email protected]
Contact Information
Moiz Lokhandwala
Email : moiz [email protected]:- 09393706292.
Business Learning Center Satyam Confidential 41