click here for session1
click here for session 2
Using RAISE_APPLICATION_ERROR ( ) in plsql
RAISE_APPLICATION_ERROR ( ‘-20999’,’No rows deleted’);
Go to session-1
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' ||
EXECUTE IMMEDIATE
'CREATE TABLE ' || 'emp1' ||
'(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(7,2),
deptno NUMBER(2)
)';
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(7,2),
deptno NUMBER(2)
)';
EXECUTE IMMEDIATE
'drop ’ || ‘ table' || ' emp1';
'drop ’ || ‘ table' || ' emp1';
EXECUTE IMMEDIATE
'truncate ’ || ‘table’||' emp1';
'truncate ’ || ‘table’||' emp1';
END;
/
/
Drop table in pl/sql
BEGIN
EXECUTE IMMEDIATE
'drop ’ || ‘ table' || ' emp1';
END;
/
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;
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 thendbms_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
Keep Rocking with some more clear picture jaya...
ReplyDeletehi...
ReplyDeleteI request you to elaborate your Entertainment link based on some social awareness esp for youths to bring much more brighter country...
Jai Hind....