Some important Queries

n-th salary:

Select distinct sal from emp a where &n = (select count (distinct sal) from emp where a.sal<= sal);

First ‘n’ maximum salaries

Select sal from emp where

Sal >= (select distinct sal from emp a where &n = (select count(distinct sal) from emp

Where a.sal <= sal));

Selecting columns without duplicate

Select sal from emp a where rowid in (select min(rowid) from emp where a.sal = sal);

Deletion of Dulipcate Rows

Delete emp a where rowid not in (select min(rowid) from emp where a.sal = sal);

n-th row selection :

Select * from table_name x where &n = (select count(rowid) from table_name where x.rowid >= rowid;

Adding 10 minites to sysdate:

Select to_char(sysdate+(10/1440),’dd mm yyyy hh:mi’) from dual;

2-nd maximum salary:

Select max(sal) from emp where sal not in (select max(sal) from emp);

+

Count of occurrence of a particular character in a sentence/paragraph:

Select length(translate(‘saravana is a software programmer.’,’abcdefghijklmnopqrstuvwxyz., ‘,’a’)) from dual;

Query for Outer join:

Select emp.deptno,emp.dname,emp.ename from emp,dept where emp.deptno(+) = dept.deptno;

Example for Start with & Connect by Prior:

Select * from emp start with job = ‘PRESIDENT’ –other conditions also –

connect by prior empno = mgr – other conditons also –

PL/SQL Package

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.

Packages usually have two parts, a specification (always required) (spec) and a body (optional)

Specs contain declaration of objects and are public.

Body contain codes and private declaration and are private to package and optional initialization part.

Use of package

• We can declare a single cursor for multi location use with slight difference, easy to maintain.
• Recursive call of procedure and functions.
• Overloaded procedures and functions by declaring same names but different sets (numbers & types) of parameters.
• Declare global variables, available through out session and can manipulate from different subprograms.
• Useful in passing collections between pro-units

Advantages of PL/SQL Packages

• Encapsulate logically related types, items, and subprograms in a named PL/SQL module.
• We can code and compile a spec without its body then code the body after application design, don’t wait for complete package to be written.
• Hiding private data and implementation detail from end user.
• Public variables and cursors sharing by all subprograms that execute in the environment in same session.
• It (full spec’s and body) loaded into memory (into shared memory pool) when a packaged subprogram for the first time, so that no disk I/O required when another subprogram
• When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.
• If we change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes. This leads to stop cascading dependencies and avoid unnecessary recompiling.

We cannot refer remote packaged variables (using database link), either directly or indirectly

Inside a package, you cannot reference host variables (e.g.: :emp_id, :sal etc.).

A package can contain initialization part and run only once, the first time you reference the package.

Example:-
Create or replce package pack1 as
Function f1(---------) ;
Prodecure p1(-------);
End pack1;

Create or replce package body pack1 as
Num1 number; -- to be used in initialization private
Function f1(---------) as
Begin
Return 123;
End;

Prodecure p1(-------)
Begin
Null;
end;
begin
insert into audit table;
num:=1;
End pack1;

Here bold code act as an initialization part, and will execute only once after calling any sub program in a session.

A public packaged cursor can be manipulated from any subprogram.

You can pin (Stick) frequently accessed packages in the shared memory pool, using the supplied package DBMS_SHARED_POOL. When a package is pinned, it is not aged out by the least recently used (LRU) algorithm that Oracle normally uses. The package remains in memory no matter how full the pool gets or how frequently you access the package.

You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

You cannot apply AUTONOMOUS_TRANSACTION Pragma to an entire package. Instead, you can apply the pragma to each packaged subprogram.

Calculating time difference

SQL> SELECT to_number( to_char(to_date('1','J') +
2 (date1 - date2), 'J') - 1) days,
3 to_char(to_date('00:00:00','HH24:MI:SS') +
4 (date1 - date2), 'HH24:MI:SS') time
5 FROM dates;

DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00

Cursors: Key Points

Cursors
A cursor is a name for a specific private SQL area in which information for processing
the specific statement is kept. PL/SQL uses both implicit and explicit cursors. PL/SQL
implicitly declares a cursor for all SQL data manipulation statements on a set of rows,
including queries that return only one row. For queries that return more than one row,
you can explicitly declare a cursor to process the rows individually.
Cursor attribute for implicit cursor
Sql%found : Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter,
%FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or
more rows, or a SELECT INTO statement returned one or more rows.

%ISOPEN : Oracle closes the SQL cursor automatically after executing its associated SQL
statement. As a result, %ISOPEN always yields FALSE.
%ROWCOUNT Attribute: How Many Rows Affected So Far

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined
exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows
that satisfy the query
The SQL%ROWCOUNT attribute is not related to the state of a transaction. When a
rollback to a savepoint is performed, the value of SQL%ROWCOUNT is not restored to the
old value before the savepoint was taken. Also, when an autonomous transaction is
exited, SQL%ROWCOUNT is not restored to the original value in the parent transaction.
It might be in a different scope

The %NOTFOUND attribute is not useful in combination with the SELECT INTO
statement:
– If a SELECT INTO statement fails to return a row, PL/SQL raises the
predefined exception NO_DATA_FOUND immediately, interrupting the flow of
control before you can check %NOTFOUND.
– A SELECT INTO statement that calls a SQL aggregate function always returns
a value or a null. After such a statement, the %NOTFOUND attribute is always
FALSE, so checking it is unnecessary.

The value of the SQL%ROWCOUNT attribute refers to the most recently executed SQL