SQL IN PL/SQL

SQL IN PL/SQL

The only statements allowed directly in pl/sql are DML and TCL.

BINDING
Binding a variable is the process of identifying the storage location associated with an identifier in the program. Binding also involves checking the database for permissions to access the object referenced.
Types of binding

  • Early binding
  • Late binding

Binding during the compiled phase is early binding. In early binding compile phase will take longer because of binding work but the execution is faster. PL/SQL by default uses early binding
Binding during the runtime phase is late binding. In late binding it will shorten the compile phase but lengthens the execution time.

DYNAMIC SQL

  • If you use DDL in pl/sql it validates the permissions and existence if requires during compile time    which makes invalid.
  • We can avoid this by using Dynamic SQL.
  • Dynamic SQL allows you to create a SQL statement dynamically at runtime.
  • Two techniques are available for Dynamic SQL.
  1. Native Dynamic SQL
  2. DBMS_SQL package

USING NATIVE DYNAMIC SQL
USING EXECUTE IMMEDIATE

 USING EXECUTE IMMEDIATE WITH PL/SQL VARIABLES

USING EXECUTE IMMEDIATE WITH BIND VARIABLES AND USING CLAUSE

 

 EXECUTING QUERIES WITH OPEN FOR AND USING CLAUSE

 

 QUERIES WITH EXECUTE IMMEDIATE

 

 VARIABLE NAMES

 GETTING DATA INTO PL/SQL VARIABLES

 

 DML AND RECORDS

 

 RECORD BASED INSERTS

 

 RECORD BASED UPDATES

 

 USING RECORDS WITH RETURNING CLAUSE

 

 USING DBMS_SQL PACKAGE

DBMS_SQL is used to execute dynamic SQL from with in PL/SQL. Unlike native dynamic SQL, it is not built directly into the language, and thus is less efficient. The DBMS_SQL package allows you to directly control the processing of a statement within a cursor, with operations such as opening and closing a cursor, parsing a statement, binding input variable, and defining output variables.

 FORALL STATEMENT

This can be used to get the data from the database at once by reducting the number of context switches which is a transfer of control between PL/SQL and SQL engine.
Syntax:
Forall index_var in
[ Lower_bound..upper_bound |
Indices of indexing_collection |
Values of indexing_collection ]
SQL statement;

FORALL WITH NON-SEQUENTIAL ARRAYS

 

The above program will give error like ‘element at index [2] does not exists.
You can rectify it in one of the two following ways.

USGAGE OF INDICES OF TO AVOID THE ABOVE BEHAVIOUR
This will be used when you have a collection whose defined rows specify which rows in the binding array you would like to processed.

 USGAGE OF VALUES OF TO AVOID THE ABOVE BEHAVIOUR

This will be used when you have a collection of integers whose content identifies the position in the binding array that you want to be processed by the FORALL statement.

 POINTS ABOUT BULK BINDS

  1. Passing the entire PL/SQL table to the SQL engine in one step is known as bulk bind.
  2. Bulk binds are done using the forall statement.
  3. If there is an error processing one of the rows in bulk DML operation, only that row is rolled back.

POINTS ABOUT RETURING CLAUSE

  1. This will be used only with DML statements to return data into PL/SQL variables.
  2. This will be useful in situations like , when performing insert or update or delete if you want to know the data of the table which has been effected by the DML.
  3. Without going for another SELECT using RETURNING clause we will get the data which will avoid a call to RDBMS kernel.

Related posts:

Leave a Reply