Super Dynamism in Cursors is obtained by passing the values as parameters( ie pass by value)
ie., for x in c1(a,b)
Multiple Cursors : These are nothing but nested cursors...........
In Implicit Cursors there is no need of declaration of cursors, by default system will create
a cursor whenever a query is written...and the conditions are like if SQL%NOTFOUND
if SQL%ROWCOUNT > 2 will keep a count of the number of records updated.
SQL is a keyword and cannot be used to name as a cursor name explicitly.
Difference between group functions and single row functions ?
Group Function
Single Row Function
A group function operates
A single row function
on many rows returns one and
result for one row.
returns single result.
Not allowed in Pl/sql procedural Allowed in Pl/Sql Procedural statements
eg SUM(),AVG,MIN,MAX etc
eg UPPER,LOWER,CHR...
Difference between DECODE and TRANSLATE ?
DECODE is value by value
TRANSLATE is character by
character replacement.
replacement.
Ex SELECT DECODE('ABC','A',1,'B',2,'ABC',3) eg SELECT
from dual; o/p 3
TRANSLATE('ABCGH',
'ABCDEFGHIJ', 1234567899)
FROM DUAL; o/p 12378
(DECODE command is used to bring IF,THEN,ELSE logic to SQL.It tests for the IF values(s) and
then aplies THEN value(s) when true, the ELSE value(s) if not.)
Difference between TRUNCATE and DELETE ?
TRUNCATE deletes much faster than DELETE
Truncate
Delete
It is a DDL statement
It is a DML statement
It is a one way trip,cannot
One can Rollback
ROLLBACK
Doesn't have selective features (where clause)
Has
Doesn't fire database triggers
Does
It requires disabling of referential
Does not require
constraints.
What is a CO-RELATED SUBQUERY ?
A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in
the FROM clause of the outer query and the same correlation name as a qualifier of a search
condition in the WHERE clause of the subquery.
eg SELECT field1 from table1 X WHERE field2>(select avg(field2) from table1 Y
where field1=X.field1);
(The subquery in a correlated subquery is revaluated
for every row of the table or view named in the outer query.)
What are various joins used while writing SUBQUERIES ?
Self join-Its a join foreign key of a table references the same table.
Outer Join--Its a join condition used where One can query all the rows of one of the
tables in the join condition even though they don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more tables in which one
|
|
