PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL
statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
The implicit cursor is used to process INSERT, UPDATE,
DELETE, and SELECT INTO statements. During the processing of
an implicit cursor,Oracle automatically performs the OPEN,
FETCH, and CLOSE operations.
Where as in explicit cursors,the process of its working is
done in 4 steps namely DECLARE a cursor,OPEN a cursor,
FETCH from cursor and CLOSE a
IMPLICT CURSOR:- Automatically porvide by oracle which
perform DML statements. queries returns only one row.
EXPLICT CURSOR:- Defined by user. queries returns more
Explicit Cursor:-We are not able to Handle NO_DATA_FOUND
Implicit Cursor:-We are able to Handle NO_DATA_FOUND
the following data types are used to store graphics or binary data
2)binary large object(BLOB)
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.
Function and Procedure both are PL/SQL blocks, main difference between function and procedure is -
Function has to return some value using return clause whereas procedure may or may not return any value( no out parameter).
We can use functions in SQL query but can't use procedure.
Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.
it's a temporary and logical memory allocation where we can put several tables.
select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') <
group by level;
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
-- For the second lowest salary:
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
select max(sal)"nth min sal" from(select distinct sal from emp order by sal) where rownum<=&N
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Enter value for n: 2
EXISTS is faster than IN
Because exists returns Boolean value whereas IN returns only value.
UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to
output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE
INSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')