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.