Dynamic sql - pl sql, PL-SQL Programming

Assignment Help:

Dynamic SQL:

The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update the sal column in the emp table. In these circumstances, the full text of the UPDATE statement is acknowledged at the compile time. These statements do not change from execution to execution. Therefore, they are known as static SQL statements.

Though, some programs should build and process a variety of the SQL statements at the run time. For illustration, a general-purpose report writer should build various SELECT statements for the various reports it generates. In this situation, the full text of the statement is unknown until the run time. These statements can, and almost certainly will, change from execution to execution. Therefore, they are known as the dynamic SQL statements.

The Dynamic SQL statements are stored in the character strings build by your program at the run time. These strings should contain the text of the valid SQL statement or the PL/SQL block. They can also contain the placeholders for bind arguments. The placeholder is an undeclared identifier, so its name, to which you should prefix a colon, that does not matter. For illustration, the PL/SQL makes no difference between the following strings:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'

'DELETE FROM emp WHERE sal > :s AND comm < :c'

To process the most dynamic SQL statements, you can use the EXECUTE IMMEDIATE statement. Though, to process the multi-row query (SELECT statement), you should use the OPEN-FOR, FETCH, and CLOSE statements.

 


Related Discussions:- Dynamic sql - pl sql

Majority of differences among 9i-10g-11g, Majority of Differences among 9i,...

Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s

Declare keyword description in pl sql, DECLARE : This keyword signals t...

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Theory of eternity of life - origin of life, THEO R Y OF ETERNITY OF LIFE...

THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

Procedures in pl/sql, Procedures   The procedure is a subprogram which...

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Example of null operator - nino rule, Example of Null operator - NiNo Rule ...

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

S, Question 1. Update stock levels when the order is cancelled At times, c...

Question 1. Update stock levels when the order is cancelled At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a

%rowcount, %ROWCOUNT When its cursor or cursor variable is opened, the...

%ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche

Using rollback - , Using ROLLBACK The ROLLBACK statements end the pres...

Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Package specification, The Package Specification The package specificat...

The Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to t

Relational algebra - sql, Relational Algebra - SQL It describes some ...

Relational Algebra - SQL It describes some operators, that together constitute an algebra that is not only relationally complete but also irreducibly so (very nearly- apart f

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd