1) What is GTT ?
a) The statement to create a global temporary table (GTT) is similar to the definition
of an ordinary table with the addition of the keywords GLOBAL TEMPORARY. In the clause
ON COMMIT, you specify if a table is bound to a transaction (DELETE ROWS) or
to a session (PRESERVE ROWS).
When using a global temporary table you need only define the table once
for the entire schema and then any user can insert directly into the table without the need to re-create
the global temporary table.
ex :--create global temporary table
sum_quantity
(sum_qty number)
---a) ON COMMIT :-
---b) DELETE ROWS :-
---c) PRESERVE ROWS:- The rows of the table will be preserved.
/
---What is dynamic sql
ans) Compilation and execution will happend at run time.Its useful forwhen we dont know column names,table names
at run time,oracle olacle uses "late binding " for dynamic sql and "early binding" for statc sql.
Fake compilation will happen at compilation stage but actuval compilation will happend at run time.
Methods of implementing is
1)execute immediate
2) using a package called DBMS_SQL package
we cane use trucate or drop command directly in plsql .So we are using execute immediate
we can pass table name and column name to drop table at run time .
/
--wt is the difference between cursor and ref cursor
nornal cursor :- create one varible and that each variable can hold one particular set of result.
refcursor is reference curosr the most useful for dynamic sql concepts.
ref cursor :it can store different result set at different points of time.
ref cursor is user defined datatype which are very much helpful to return multiple records
from procedure or functions
ex:refcur
Declare
type test is ref cursor;
var test;
vrev emp%rowtype;
vrec2 dept%rowtype;
Begin
open var for 'select * from emp';
Loop
fetch var into vrec;
exit when var%notfound;
dbms_output.put_line(vrec.ename);
End ;
open var for 'select * from dept';
Loop
fetch var into vrec2;
exit when var%notfound;
dbms_output.put_line(vrec2.dname);
End loop;;
close var;
end;
refcursor are 2 types :strong and week
/
--1) wt is the difference between cusroe and ref cusrsor?
A) ref cursor is a pointer to a result set. This is normally used to open a query on the database server,
then leave it up to the client to fetch the result it needs. A ref cursor is also a cursor,
though normally ther term cursor is used when discussing static SQL.
2)In case of a normal explict cursor, the SQL query has to be defined at the time of declaring the cursor itself.
For example :
DECLARE
CURSOR cr is SELECT * FROM TAB;
3)In case of REF Cursor, the cursor declartion is not associated with any SQL query,
it is associated with a query at a later stage which brings in a lot of flexibility as different SQL
queries can be associated with the cursor (one at a time, offcourse) programatically.
For example :
DECLARE
TYPE rc is ref cursor;
cur1 rc;
BEGIN
open cur1 for 'select * from tab';
#some code
End;
1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.
2) A ref cursor is defined at runtime and can be opened dynamically but
a regular cursor is static and defined at compile time.
3) A ref cursor can be passed to another PL/SQL routine (function or procedure)
or returned to a client. A regular cursor cannot be returned to a client application
and must be consumed within same routine.
4) A ref cursor incurs a parsing penalty because it cannot cached but regular
cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.
5) A regular cursor can be defined outside of a procedure or a function as a global package variable.
A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.
6) A regular cursor can more efficiently retrieve data than ref cursor.
A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP.
A ref cursor must use explicit array fetching.
Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients
and when there is NO other efficient/effective means of achieving the goal.
/
----+ve and -ne value query with case
select sum(case when sal<0 Then sal end) nval,
sum(case when sal>0 Then sal end) pval from emp
/
-----+ve and -ne value query with sign
select sum(decode(sign(sal),1,sal)),sum(decode(sign(sal),-1,sal)) from emp
/
---q) what is collection and examples with types
And) A Collection is an ordered group of elements of particular data types. It can be a collection
of simple data type or
complex data type (like user-defined or record types).
Collections are most useful things when a large data of the same type need to be processed or manipulated.
Collections can be populated
and manipulated as whole using 'BULK' option in Oracle.
Collections are classified based on the structure, subscript, and storage as shown below.
Index-by-tables (also known as Associative Array)
Nested tables
Varrays
At any point, data in the collection can be referred by three terms Collection name,
Subscript, Field/Column name as "<collection_name>(<subscript>).<column_name>". You are going
to learn about these above-mentioned collection categories further in the below section.
Varrays
Varray is a collection method in which the size of the array is fixed.
The array size cannot be exceeded than its fixed value. The subscript of the Varray is of a numeric value.
Following are the attributes of Varrays.
Upper limit size is fixed
Populated sequentially starting with the subscript '1'
This collection type is always dense, i.e. we cannot delete any array elements.
Varray can be deleted as a whole, or it can be trimmed from the end.
Since it always is dense in nature, it has very less flexibility.
It is more appropriate to use when the array size is known and to perform similar activities
on all the array elements.
The subscript and sequence always remain stable, i.e. the subscript and count of the collection is always same.
They need to be initialized before using them in programs. Any operation (except EXISTS operation)
on an uninitialized collection will throw an error.
It can be created as a database object, which is visible throughout the database or inside the subprogram,
which can be used only in that subprogram.
The below figure will explain the memory allocation of Varray (dense) diagrammatically.
Subscript 1 2 3 4 5 6 7
Value Xyz Dfv Sde Cxs Vbc Nhu Qwe
Syntax for VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
In the above syntax, type_name is declared as VARRAY of the type 'DATA_TYPE' for the given size limit.
The data type can be either simple or complex type.
Nested Tables
A Nested table is a collection in which the size of the array is not fixed. It has the numeric subscript type.
Below are more descriptions about nested table type.
The Nested table has no upper size limit.
Since the upper size limit is not fixed, the collection, memory needs to be extended each time before we use it.
We can extend the collection using 'EXTEND' keyword.
Populated sequentially starting with the subscript '1'.
This collection type can be of both dense and sparse, i.e. we can create the collection as a dense, and we can also
delete the individual array element randomly, which make it as sparse.
It gives more flexibility regarding deleting the array element.
It is stored in the system generated database table and can be used in the select query to fetch the values.
The subscript and sequence are not stable, i.e. the subscript and the count of the array element can vary.
They need to be initialized before using them in programs. Any operation (except EXISTS operation)
on the uninitialized collection will throw an error.
It can be created as a database object, which is visible throughout the database or inside the subprogram,
which can be used only in that subprogram.
The below figure will explain the memory allocation of Nested Table (dense and sparse) diagrammatically.
The black colored element space denotes the empty element in a collection i.e. sparse.
Subscript 1 2 3 4 5 6 7
Value (dense) Xyz Dfv Sde Cxs Vbc Nhu Qwe
Value(sparse) Qwe Asd Afg Asd Wer
Syntax for Nested Table:
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
In the above syntax, type_name is declared as Nested table collection of the type 'DATA_TYPE'.
The data type can be either simple or complex type.
Index-by-table
Index-by-table is a collection in which the array size is not fixed. Unlike the other collection types, in the index-by-table collection the subscript can consist be defined by the user. Following are the attributes of index-by-table.
The subscript can of integer or strings. At the time of creating the collection, the subscript
type should be mentioned.
These collections are not stored sequentially.
They are always sparse in nature.
The array size is not fixed.
They cannot be stored in the database column. They shall be created and used in any program in that particular
session.
They give more flexibility in terms of maintaining subscript.
The subscripts can be of negative subscript sequence also.
They are more appropriate to use for relatively smaller collective values in which the
collection can be initialized and used within the same subprograms.
They need not be initialized before start using them.
It cannot be created as a database object. It can only be created inside the subprogram,
which can be used only in that subprogram.
BULK COLLECT cannot be used in this collection type as the subscript should be given explicitly
for each record in the collection.
The below figure will explain the memory allocation of Nested Table (sparse) diagrammatically.
The black colored element space denotes the empty element in a collection i.e. sparse.
Subscript (varchar) FIRST SECOND THIRD FOURTH FIFTH SIXTH SEVENTH
Value(sparse) Qwe Asd Afg Asd Wer
Syntax for Index-by-Table
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY YARCHAR2 (10);
In the above syntax, type_name is declared as an index-by-table collection of the type 'DATA_TYPE'.
The data type can be either simple or complex type. The subsciprt/index variable is given as VARCHAR2
type with maximum size as 10.
<<Methods of clooetion>>
1) EXISTS ,count,limit,first,last,prior,next,delete,delete(n)..
/
--<<Difference between Bulk Collect and Culk Bind >>
Difference between Bulk collect and Forall. Bulk collect: is a CLAUSE.
is used to fetch the records from the cursor. Forall: is a STATEMENT. ...
The body of the FORALL statement is a single DML statement -- an INSERT, UPDATE, or DELETE
