Tuesday 28 February 2012

Unix Basics session 2

Click here for Unix session 1

To go to Session 1 click here
To go to Session 2 click here
To go to Session 3 click here
To go to UNIX basic commands click here
Online sample UNIX TERMINAL click here



Visual editor
                Helps in              
                                Inserting and deleting text
                                Replacing text
                                Moving around a file
                                Finding and substituting string
                                Cutting and pasting text
                                Reading and writing to other text

                While working in vi editor the content of the current file is stored in buffer and we work on that only atlast we may save the changes or quit without saving
Vi editor mode
                Command mode – in this what ever been typed is interpreted as command only cant insert data into file
                Insert mode – to enter into insert mode press ‘I’ in this mode what ever u type is included as text into file to exit from this mode press escape
                Ex command mode – this allows us to give commands the end of the editor we these types of command and these command starts with :
Starting vi deitor
Vi filename – creates a new file
View filename – view file in read only mode
Exiting vi editor
                :w – write the buffer to disk
                :w filename – write the buffer in the given filename
                :wq - write the buffer to disk and quit
                :zz - write the buffer to disk and quit
                :q! – quit without writing edit buffer to disk.
To insert text vi editor
                a – append text after cursor
                A – append text at the end of line
                i – insert before cursor
                I – insert text beginning of line
                o – open a blank line after the current line for the text input
                O - open a blank line before  the current line for the text input
Deleting text
                x- delete charac at cursor
                dh- delete char before cursor
                nx-delete n char at cursor
                dw- delete next word
                db- delete previous word
                dnw- delete n words from cursor
                dnb- delete n words before cursor
                d0- delete to beginning of line
                d$- delete to end of line
                D- delete to end of line
                dd- delete current line
                d(- delete beginning of sentence
                d)- delete to end of sentence
                d{- delete to beginning of paragragh
                d}- delete to end of paragragh
                ndd- delete to lines(starts at the current line)
copy  lines
                yy- copy 1 line to buffer
                nyy- copy n lines ro buffer
                P- out the contents in buffer aft current line
                p- put the contents in buffer before current line
cut and paste
                ndd – cuts/deletes n lines and places it in buffer
                P- out the contents in buffer aft current line
                p- put the contents in buffer before current line
Find and replace
                /str – search forward for str
                ?str – search backward for str
                N – repeat the previous search in reverse direction
                n- find the next occurrence of current string
:s/old/new find and replace first occurrence in current line
:s/old/new/g  find and replace all occurrence in current line
%s/old/new/g find and replace of all occurrence in current file
%s/old/new/gc find and replace of all occurrence in current file with y/n option prompt before every replace.

Setting options
                :Set ai – set auto indent
                 :Set noai– set off auto indent
                :set nu- set line number on
                :set nonu- set off  line number
                :set scroll=n set the number of lines to be scrolled
                :set sw=n set width to n;
                :set wm= set wrapmargin to n .

Unix access and permissions
                Unix is a multi user system.  Each file and directory In unix can be used by many users.               
Permissions  for file or folder are
                r – read
                w- write
                x- execute
permissions are controlled at three level
                u- user
                g- group
                o-others
ls –l displays file/folder permission
                drwxrwxrwx  in this d denotes  directory, rwx denotes (read,write,execute)  permission for user ,  rwx denotes (read,write,execute)  permission for group, , rwx denotes (read,write,execute)  permission for owner
chmod command
                used to change the permission of a file or folder
                chmod a+r jc.txt adds read permission to all users
                chmod o-r jc.txt removes/revokes read permission for others
                chmod og+rx jaya.txt adds read and execute permissions for group and others
                chmod +w * add write permission to all files for users.
Permissions by numerical or octal
                File access permissions can also be given using numerical or octal values
Permission and values:
                r- 4(read only)
                w-2
                x-1
0 – no permission
1 – execute
2 – write only
3 – write and execute (1+2)
4 – read only
5 – read and execute (4+1)
6- read and write(4+2)
7 – read,write and execute(4+2+1)
E.g chmod 777 jayachandran.txt
The above example gives read,write and execute permission to all (owner,user,others)

Process
                Instance of an executing a program is called process.
                A program called SCHEDULER runs in the memory and decides which process should get the CPU

Process ID
In unix every process has a unique id called process id.
Command ps:
                Ps – shows list of process running with its id time and command
                optiions
                                -a process of all users
                                -u username process of a particular user
                                -t tty –process of a particular terminal
                                -f full listing of information
                                -e listing of the system process (Daemons)
Kill command
                Kill – command is used to send a signal to a process mostly this will be kill signal.
                Syntax
                                Kill [signal] pid
                Signal
                STOP
                CONT
                -l
SHELL
                When ever you log in you are placed in a shell, shell works as an interpreter , every command we enter is passed to operating system.
                To determine your shell
                                Echo $SHELL
Types of shell
                Bourne shell
                C shell
                Korn shell
                Tc shell
                Bourne again shell

Communication in UNIX
                Write – two way communication with any person who is currently logged on.
                                write – username
                mesg – controls the messages sent by other users
                                mesg n – prevents others user to write to your terminal.
                                mesg y – allows other users to write to your terminal
                mail – to send mail to user you can send mail even if that user is not logged in.
                                mail username
                                subject: <subject of mail>
                                  <text>
                               <ctrl+d to exit from mail >
                                to do this you need mailx in your system

Unix Basics


For best entertainment links

To go to Session 1 click here
To go to Session 2 click here
To go to Session 3 click here
To go to UNIX basic commands click here
Online sample UNIX TERMINAL click here


Unix basics
File types
1.ordinary files – contains text or image does not have any other files in it
2.directories – branching points in hierarchy level directory tree. May contain other files , special files, other directories
3.special files – any devices connected to unix like(tape drives,printer, and terminal)
                By default unix consider terminal as stdin as well as stdout(standard input and output)
                Special files are normally stored under the directory /dev
4.pipes – temporary file used to link two commands together. (E.g) cat jai1.txt|more output of cat command is stored in a temp file pipe and sent to more command as input.
Symbol
File type
-
Normal file
D
directory
|
Symbolic link
B
Block device file
C
Character device FILE
P
FIFO or named pipe

File names
Unix file name can be upto 256 char, allows to use most char except spaces, tab spaces, and special symbols, unix is case sensitive.
Extensions can be used to identify the type of files.
.a archive file
.c c program fiile
.f forton file
.o object/executable file
.z compressed file
Hidden files begin with .(dot) example .login .mailrc .mwmrc
File names are unique that is under one directory two files cant have the same name.
Path names:
Path – location of a specific file or folder
Types of pathname
                1.absolute pathname -  starts with the root folder /
                2.relative path name – does not start with root folder (E.g) ../bin ~/proj/rep.text
 Unix commands:
                OS is a set of programmed instruction that tells how to work.
An user interaction with OS can take place via command line interpreter (or shell) /terminal window.
Command cat – concatenate
                This command concatenate file or files and puts it in the standard output device.
e.g 1:     cat file1
                cat file1 file2 concatenate file1 and file2 and puts in standard output.
                Cat file1 file2 > file3 concatenate file1 and file2 and makes the file3.
Ls or directory command
                Lists files and sub-directories in directory
                                ls – contents of directory
                                ls filename
                                ls directory (lists contents of directory)
                                ls –a list all files including hidden one
                                ls – al detail list of contents
                                ls –F marks directories with / and executable files as *
                                ls *.doc list all files with extention doc
                                ls –lt list with time of last modification
                                ls –lut show time of last time access
more command
                one screen at a time
                                some helpful tips using more command is
                                                h for help
                                                space for page
                                                b for back
                                                q for quit
                                                /string to search string
                E.g  cat file1.c|more

cp command
                cp command used to copy files. This over writes a file until unless specified.
                Syntax : cp [options]  <source file/s> <destination directory/file>\
               
Options
function
-i
Promotes before overwriting
-r
Copies entire directory

cp f1 f2 copies f1 to f2
cp  *.text dir copies all files with extention .text to directory dir
cp f1 f2 f3 f4 here f4 is taken as destination f1,f2,f3 as source
cp f1 dir copies file f1 to dir directory

cmp command
                cmp command used to compare two files and find is that files identical
                syntax:
                                cmp [options] file1 file2
                                E.g:         cmp jc.txt jaya.txt
                                                cmp –l jc.txt jaya.txt
mv command
                move command mv moves file(s) to specified directory/file. To do this destination should have permission.
                mv file1 file2
                mv *.txt dir moves all files with extension .txt to dir directory
rm command
                 removes or deletes files or folders if permission allowed.
                rm file1 removes file
                rm ja?.txt deletes all text files with first two char ja and last four char .txt
                rm –i * deletes all files in the current directory but asks for each file before deleting
                rm –r/dir recursively delete all files including the directory
date,cal command
                date displays the date and time
                cal displays the current month calendar
                cal 2012 displays the calendar 2012
                cal march 2012 displays march calendar of 2012
                the above can also be given as cal 3 2012
who command
                shows all the user who have logged in
                                with login name,terminal type and number,date and time (* note this info is displayed to users those who r not logged in from the server)
                whoami – displays self user name
                who –q who without detail.
Command finger
                This utility used to get list of user names,full name,terminal name,write status,idle time,login time,machine address.
Word count command
                wc filename list the number of lines,words,char from a file
head command
                Displays first n lines of a file
Tail command
                Displays last n lines of a file
Uname command
                Displays the name of the current system
                Uname [-a] [-i] [-m] [-p][-r] [-s] [-v][-x][-s systemname]
Clear
                To clear the screen
File
                Determines the type of file
Which
                Determines where is the executable for file for the given program
                E.g – which perl , which java
Whereis
                Locates binary,source and manual page files for a command
                Syntax : whereis [-bmsu][-BMS directory] file name
                -b  searches only for binaries
                -m  searches only manual sections
                -s searches only for sources
Directory Related commands
                Pwd – current working directory
                Mkdir – make directory
                Rmdir – remove directory
Redirection and piping
                Redirection – redirecting a command result to a file
                Piping – result of one command is sent as input for another command
Redirection operators
Ø  Indicates redirection of out put., send the output of a command to a file or printer.,
>> indicates redirection of out put of a command to a file which will be appended at last.
Unix wont show the out put in screen/std out put but redicted to a file
E.g
                Cat >file
                Cat file>file1
                Cat file>>file1

<  Indicates redirection of in put., takes the input from a file for a command rather than keybord.,
E.g
                Cat<file
Piping Example
                $ls |wc –l
                                Output of ls is given as output of wc command.
                Pipe with redirection
                                $ls|wc –l>countfile
                $who|sort
                $who|sort>sorted

Click here for Unix Session 2

Sunday 19 February 2012

pl/sql best examples session 3

click here for session1
click here for session 2


Type creation(this is similar to structure )
Type example in pl/sql
declare
type t_numbers is record
(
  a number(5),
  b number(5),
  c number(5)
); vnum t_numbers;
v_b number(5):=10;
begin
            vnum.a:=5;
            vnum.b:=5;
 dbms_output.put_line(vnum.a+v_b);
end;
/
 Boolean type example
Declare
            A Boolean:=TRUE;
            B Boolean:=FALSE;
            C Boolean:=NULL;
            AB VARCHAR2(4):=’TRUE’;
            BB VARCHAR2(5):=’FALSE’;
            CB VARCHAR2(4):=’NULL’;
Begin
            IF(A AND A) THEN
                        dbms_output.put_line(AB||‘ AND’||AB||’  IS TRUE’);
            END IF;
            IF(A AND B) THEN
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS TRUE’);
            ELSE
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS FALSE’);
            END IF;
            IF(A AND C) THEN
                        dbms_output.put_line(AB||‘ AND’||CB||’  IS TRUE’);
            ELSE 
IF((A AND C) IS NULL) THEN
                                    dbms_output.put_line(AB||‘ AND’||BB||’  IS null’);
                        END IF;
            END IF;
IF(A AND A) THEN
                        dbms_output.put_line(AB||‘ AND’||AB||’  IS TRUE’);
            END IF;
            IF(A AND B) THEN
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS TRUE’);
            ELSE
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS FALSE’);
            END IF;
            IF(A AND C) THEN
                        dbms_output.put_line(AB||‘ AND’||CB||’  IS TRUE’);
            ELSE 
IF((A AND C) IS NULL) THEN
                                    dbms_output.put_line(AB||‘ AND’||BB||’  IS null’);
                        END IF;
            END IF;
end ;

Declare
            A Boolean:=TRUE;
            B Boolean:=FALSE;
            C Boolean:=NULL;
            AB VARCHAR2(4):=’TRUE’;
            BB VARCHAR2(5):=’FALSE’;
            CB VARCHAR2(4):=’NULL’;
Begin
            Case
                        While (a and a) then dbms_output.put_line(AB||‘ AND’||AB||’  IS TRUE’);
                        While ((a and b)=false) then dbms_output.put_line(AB||‘ AND’||BB||’  IS  False’);
                        While ((a and c) is NULL) then dbms_output.put_line(AB||‘ AND’||CB||’  IS  NULL’);
                        While ((b and c) is NULL) then dbms_output.put_line(BB||‘ AND’||CB||’  IS  NULL’);
                        While ((b and b )=false) then dbms_output.put_line(BB||‘ AND’||BB||’  IS  FALSE’);
                        While ((c and c) is NULL) then dbms_output.put_line(CB||‘ AND’||CB||’  IS  NULL’);

                        While (a or a)
                        While (a or b)
                        While ((a or c) is NULL)
                        While ((b or c) is NULL)
                        While ((b or b )=false)
                        While ((c or c) is NULL)
--u can use as many condition as u want--

Create,drop,truncate in plsql / ddl in plsql /execute immediate
in pl/sql
Using execute immediate
BEGIN
     EXECUTE IMMEDIATE
    'CREATE TABLE ' || 'emp1' ||
    '(
        empno   NUMBER(4) NOT NULL,
        ename   VARCHAR2(10),
        job     VARCHAR2(9),
        sal     NUMBER(7,2),
        deptno  NUMBER(2)
    )';
EXECUTE IMMEDIATE
    'drop ’ || ‘ table' || ' emp1';
EXECUTE IMMEDIATE
    'truncate ’ || ‘table’||' emp1';
END;
/
Drop table in pl/sql
BEGIN
     EXECUTE IMMEDIATE
    'drop ’ || ‘ table' || ' emp1';
END;
/
Insert in pl/sql
Begin
            Insert into emp1 values(‘&empno’,’&ename’,’&job’,’&sal’,’&deptno’);
End ;
/





Cursor with for and %rowtype example in pl/sql
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            For I in 1..cnt loop
                        Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
            end loop;
            close empd;
end;
/          









Cursor using loop and %rowtype
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
I number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            I:=1;
            Loop
                        Exit when i>cnt ;
                        Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
            i:=i+1;
            end loop;
            close empd;
end;
/          




Cursor Using while loop and row type

Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
I number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            I:=1;
            While(I < cnt) Loop
                        Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
            i:=i+1;
            end loop;
            close empd;
end;
/          





Cursor Using %rowcount,%isopen,%notfound,%found
Declare
            Cursor empcursor is select * from emp;
            Edetail emp%rowtype;
Cnt number;


Begin
            Select count(*) into cnt from emp;
            If(empcursor%isopen) then
                        Dbms_output.put_line(‘Cursor is already opened’);
            Else
                        Open empcursor;
Dbms_output.put_line(‘Cursor Opened succesfully’);
            End if;
Dbms_output.put_line(‘Total No. of records before fetch are:’||empcursor%rowcount);
For  I in 1..cnt loop
If(empcursor%found)
            Dbms_output.put_line(‘Record available’);
Else
            Dbms_output.put_line(‘Record not available’);
End if;
Fetch empcursor into edetail;
Dbms_output.put_line(‘Total No. of records are:’||empcursor%rowcount);
            end loop;
If(empcursor%found)
            Dbms_output.put_line(‘Record available’);
Else
            Dbms_output.put_line(‘Record not available’);
End if;
Close empcursor;
End;
/


Cursor with parameter
Declare
            Cursor empcursor(eno number) is select * from emp where empno=eno ;
            Edetail emp%rowtype;
Cnt number;
Begin
            Open empcursor(102);
            Select count(*) into cnt from emp where empno=102;
            For I in 1..cnt loop
                        Fetch Empcursor into edetail;
                        dbms_output.put_line(‘Employee number:  ’||edetail.EMPNO||’  Employee name ’||edetail. ENAME||’  Employee Salary :  ’||edetail. SAL||’  Employee works for : ’ ||edetail. DEPTNO||’  Employee Date of joining ’||edetail.DOJ||’  Manager ID ’||edetail. MGR);
            end loop;
            end;
/
Bind variable in plsql
Sql>variable c number; //this is a bind variable
begin
:c:=:c+20;
 dbms_output.put_line(:c);
end;
/


Exception handling in plsql
declare
 sal number(6);
begin
 select emp.sal into sal from emp;
dbms_output.put_line (SAL/0);
exception
 when TOO_MANY_ROWS then
dbms_output.put_line ('A SELECT...INTO wrote many rows');
            when  ZERO_DIVIDE then
dbms_output.put_line (‘DIVIDE BY ZERO ERROR’);
            WHEN OTHERS THEN
dbms_output.put_line (‘Some other Exception raised’);

end;
/


User defined exceptions raising user exception in plsql
Declare
            Abcd exception;
Begin
            Delete emp where empno=110;
            If(sql%notfound) then
                        Raise abcd;
            End if;
            EXCEPTION
                        When abcd then
                                    dbms_output.put_line (‘No row deleted’);
End;
/

Using  RAISE_APPLICATION_ERROR ( ) in plsql

Begin
            Delete emp where empno=110;
            If(sql%notfound) then

                        RAISE_APPLICATION_ERROR ( ‘-20999’,’No rows deleted’);

            End if;
End;
/

RAISE_APPLICATION_ERROR and handling it
declare
 SALY  NUMBER;
BEGIN
 SELECT SAL INTO SALY FROM EMP WHERE EMPNO=102;
 IF(SALY>=20000) THEN
  RAISE_APPLICATION_ERROR('-20988','YOU ARE GETTING MORE THAN WHAT U SHOULD GET');
 END IF;
EXCEPTION
            WHEN OTHERS THEN
dbms_output.put_line (‘GET PERMISSION TO GET MORE THAN 19999’);
END;
/

Scope example in plsql
Declare
            I number;
Begin
            I:=10;
            Dbms_output.put_line(i);
            Declare
                        j number:=20;
            Begin
                        Dbms_output.put_line(j);
            End;
            Dbms_output.put_line(j);
End;
/




declare
            var number(10):=5;
            i number:=0;
begin
            for i in 1..10 loop
                        I:=I+1;
                        dbms_output.put_line(i);
            end loop;
end;                
/

Function example in plsql
Declare
            Arg1 number(10):=5;
            Arg2 number(10):=5;
            Arg3 number(10);
Begin
            Arg3:=addd(arg1,arg2);
            Dbms_output.put_line(arg3);
End;
/
Create OR REPLACE function addd(a number,b number) RETURN NUMBER IS
BEGIN

                                                    RETURN A+B;
END;
/
SHOW ERROR;                         

Create OR REPLACE function addd(a number,b number) RETURN NUMBER IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘inside function add’);
                                                    RETURN A+B;
END;
/
This is wrong since using out in function parameter a function can have only in not out or in out
Create OR REPLACE function DISP1( var out  varchar2(30))  RETURN VARCHAR2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘HI I GOT OUT PUT’);
Var:=’jayachandran’;
                                                    RETURN var;
END;
/
Show error;

Cursor with for to display all data of the table
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
Begin
           
            Open empd;

                        Select count(*) into cnt from emp;
            For I in 1..cnt loop
                                    Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
           
end loop;
close empd;

            end;
/                       

Procedure with in and out
CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT varchar2)
IS
BEGIN
  SELECT ename INTO emp_name
  FROM emp WHERE empno = id;
END;                                    
/


declare
            Empname varchar2(50);
Begin
            Emp_name(102,empname);
            Dbms_output.put_line(empname);
End;
/

Function with out return in plsql (this function will raise application error but will do the work inside it)
Create OR REPLACE function sayhi(nam varchar) return varchar  IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘hjhkjhkjhk’);
END;
/

Create or replace procedure displa(c in number) is
begin
                                                    dbms_output.put_line(c);
End;
Create or replace procedure dees(c in out number) is
Begin
                                                    C:=24;
End;
/

Create or replace procedure dees is
Begin
                                                    Dbms_output.put_line(‘This is in procedure’);
End;
/

Procedure with %type and %rowtype:
CREATE OR REPLACE PROCEDURE TAB_DIS(EMPNUM EMP.EMPNO%TYPE) IS
RECRD EMP%ROWTYPE;
BEGIN
SELECT * INTO RECRD FROM EMP WHERE EMPNO = EMPNUM;     
DBMS_OUTPUT.PUT_LINE(RECRD.EMPNO||RECRD.ENAME||RECRD.SAL||RECRD.DEPTNO||RECRD.DOJ);
DELETE FROM EMP WHERE EMPNO=EMPNUM;
DBMS_OUTPUT.PUT_LINE(‘THE ABOVE RECORD DELETED’);
END;
/
DECLARE
                                             EMPNUMBER NUMBER;
BEGIN
                                             TAB_DIS(&EMPNUMBER);
END;
/


Trigger

CREATE OR REPLACE TRIGGER releaseemp
BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
INSERT INTO oldemp(EMPNO, ENAME, SAL, DEPTNO, DOJ, MGR)
VALUES(:old.EMPNO,:old.ENAME,:old.sal,:old.deptno,:old.doj,:old.mgr);
END;


CREATE OR REPLACE TRIGGER emp_insert
after insert ON emp
FOR EACH ROW
Declare
            Dptno dept.deptno%type;
            Cunt number;
BEGIN
            Dptno:=:new.deptno;
            Comit();
            Select noemp into cunt from dept where deptno=dptno;
            If(cunt is null) then
                        Cunt:=1;
            Else
                        Cunt:=cunt+1;
            End if;
            Update dept set noemp=cunt where deptno=dptno;
END;
AUTONOMOUS_TRANSACTION in plsql
Create or replace procedure comit(CUNT IN OUT NUMBER) as PRAGMA AUTONOMOUS_TRANSACTION;
CNT NUMBER;
Begin
                                                                   CNT:=CUNT;
                                                                   Commit;
                                                                   select count(DEPTNO) INTO CUNT from emp where deptno=CNT;
                                                                  
End;
/

CREATE OR REPLACE TRIGGER emp_insert1
after insert ON emp
FOR EACH ROW
Declare
 Dptno dept.deptno%type;
 Cunt number;
BEGIN
 Dptno:=:new.deptno;
 CUNT:=DPTNO;
Comit(CUNT); --uses the AUTONOMOUS_TRANSACTION u cant commit in trigger--

  update dept set noemp= CUNT where deptno=dptno;
END;
/
Insert into emp values(&EMPNO,’&ENAME’,SAL,& DEPTNO,’& DOJ’,& MGR);

Insert into emp values(111,'chandran',null,30,'19-jan-12',101);


To display 5th max value in plsql using cursor
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp ORDER BY SAL DESC;
            Cnt number;
I number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            I:=1;
            Loop
                        Exit when i>cnt ;
                        Fetch empd into edetail;
                        IF(I=5) THEN
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
                        END IF;
            i:=i+1;
            end loop;
            close empd;
end;

To display 5th max value in plsql using sql alone
SELECT DISTINCT ename,SAL FROM emp A WHERE &N=(SELECT COUNT (DISTINCT B.SAL
)
FROM emp B WHERE A.SAL<=B.SAL);
Package example in sql

CREATE OR REPLACE PACKAGE bank_account
IS
                                                                   function addd(n number,n1 number) return number;
                                                                   function addd(CH VARCHAR2,CH1 VARCHAR2) RETURN VARCHAR2
END;
CREATE OR REPLACE PACKAGE BODY bank_account
IS
function addd(n number,n1 number) return number is
                                                                   n3 number;
                                                                   begin
                                                                    N3:=N+N1;
                                                                   RETURN N3;
                END;
function addd(CH VARCHAR2,CH1 VARCHAR2) RETURN VARCHAR2 IS
                                                                    CH2 VARCHAR2(30);
                                                                    BEGIN
                                                                     CH2:=CH||CH1;
                                                                    RETURN CH2;
                END;
END;


Go to session-1