add

About Me

My photo
Oracle Apps - Techno Functional consultant

Monday, February 18

Oracle PL SQL Codes





--PL-SQL
DECLARE
 a integer;
BEGIN
 a :=#
 dbms_output.put_line('SUM is : ' ||a);
END;


DECLARE
 a integer;
 b integer;
 c integer;
BEGIN
 a :=&num1;
 b :=&num2;
 c:=(a+b);
 dbms_output.put_line('SUM is : ' || c);
END;


DECLARE
 noemp.empid%type;
 nameemp.ename%type;
 sal emp.salary%type;
 ansal number(15);
BEGIN
 no:= &empno;
 SELECTename, salary INTO name, sal FROM emp WHERE empid like no;
 ansal :=sal * 12;
 dbms_output.put_line('Annual Salary is : ' || ansal);
END;


DECLARE
 eno emp.empid%type;
 nameemp.ename%type;
BEGIN
 eno :=&empno;
 SELECTename INTO name FROM emp WHERE empid like eno;
 dbms_output.put_line(ename);
END;


DECLARE
 dno emp.deptno%type;
 sal emp.salary%type;
 dna dept.dname%type;
 nsal number(15);
BEGIN
 dno :=&deptno;
 SELECTsum(salary) INTO nsal FROM emp GROUP BY deptno where deptno LIKE dno;
 SELECTdname INTO dna FROM dept WHERE deptno LIKE dno;
 dbms_output.put_line('HAI dname:' ||dna ||'sum of salary' ||nsal);
END;


DECLARE
 n number;
BEGIN
 forn in 1..5 LOOP
  dbms_output.put_line(n);
 ENDLOOP;
END;


DECLARE
  var_empno NUMBER(4) ;
  var_comm emp.comm%TYPE;
BEGIN
var_ empno:=&a; 
SELECTcomm INTO var_comm FROM emp
  WHEREempid = var_empno;

  IFvar_comm IS NULL THEN
    UPDATEemp SET comm = 300
    WHEREempid = var_empno;
  ELSE
    var_comm :=var_comm + var_comm * 25;
    UPDATEemp SET comm = var_comm
    WHEREempid = var_empno;
  ENDIF;
dbms_output.put_line('Commission for ' || var_empno || ' has been updated to '|| var_comm);
END;


DECLARE
  mgr_num  emp.mgr%TYPE;
  emp_num  emp.empid%TYPE;
  emp_name  emp.ename%TYPE;
  mgr_name  emp.ename%TYPE;
  start_num  NUMBER(4) := 7274;

BEGIN
  SELECTempid, ename, mgr
  INTOemp_num, emp_name, mgr_num
  FROMemp
  WHEREempid = start_num;

  LOOP
    SELECTempid, mgr, ename
    INTOemp_num, mgr_num, mgr_name
    FROMemp
    WHEREempid = mgr_num;

    dbms_output.put_line(start_num ||' '|| emp_name ||' '|| mgr_name);

    EXITWHEN mgr_num IS NULL;

    start_num :=emp_num;
    emp_num :=mgr_num;
    emp_name :=mgr_name;
  ENDLOOP;

  dbms_output.put_line(emp_num ||' '|| mgr_name);
 
END;


DECLARE
 a number(2);
 b number(2);
BEGIN
 a:=&a;
 b:=&b;

 ifa>b then
  dbms_output.put_line('a is bigger '||a);
 else
  dbms_output.put_line('b is bigger'||b);
 ENDif;
END;





BEGIN
 fori  in  1..10
  LOOP
  dbms_output.put_line(i);
 ENDLOOP;
END;


BEGIN
 fori in  reverse1..10
  LOOP
  dbms_output.put_line(i);
 ENDLOOP;
END;


DECLARE
 i number:=1;
BEGIN
 LOOP
  i:=i+1;
  dbms_output.put_line(i);
  exitwhen i>=10;
 ENDLOOP;
END;


DECLARE
 i number:=0;
BEGIN
 whilei<=10
  LOOP
  dbms_output.put_line(i);
  i:=i+2;
 ENDLOOP;
END;


BEGIN
 fori in   1..5
  LOOP
  forj in 1..10
   LOOP
   dbms_output.put_line(i||' * '||j||' = '||i*j);
  ENDLOOP;
   dbms_output.put_line('------------------------');
 ENDLOOP;
END;


DECLARE
 p number;
 t number;
 r number;
 si number(6,2);
BEGIN
 p:=&p;
 t:=&t;
 r:=&r;
 si:=p*t*r/100;
 dbms_output.put_line('the simple int  is '|| si);
END;


DECLARE
 a number;
 b number;
 c number;
BEGIN
 a :=&a;                                             
 b :=&b;
 c :=&c;

 if(a>b AND a>c )then                           
dbms_output.put_line('a is bigger '||a);
 else
 if(b>c AND b>a) then
  dbms_output.put_line('b is bigger '||b);
 else
  dbms_output.put_line('c is bigger '||c);
 ENDif;
endif;
END;


DECLARE
 a emp.ename%TYPE;
 b emp.desig%TYPE;
 c emp.salary%TYPE;
BEGIN
 SELECTename,desig,salary INTO a,b,c FROM emp WHERE ename like '&ename';
 dbms_output.put_line(A||' '||B||' '||C);
END;


DECLARE
 R emp%ROWTYPE;
BEGIN
 SELECT*  INTO R FROM emp WHERE ename like '&ename';
 dbms_output.put_line(R.ename||' '||R.desig||' '||R.salary||' '||R.deptno||' '||R.mgr);
END;



--PROCEDURES
CREATEOR REPLACE PROCEDURE getName(eid emp.empid%TYPE)
IS
 nameemp.ename%TYPE;
BEGIN
 SELECTename INTO name FROM EMP WHERE empid like eid;
 dbms_output.put_line('Employee Name is : ' || name);
END;   


CREATEOR REPLACE PROCEDURE getName(EID emp.empid%TYPE, name OUT emp.ename%TYPE)
IS
BEGIN
SELECTename INTO name FROM EMP WHERE empid = eid;
END;


DECLARE
 eid emp.empid%TYPE;
 enm emp.ename%TYPE;
BEGIN
 getName(&eid, enm);
 dbms_output.put_line('Employee Name is : ' || enm);
END;


CREATEOR REPLACE PROCEDURE totsal(dno IN integer, tsal OUTinteger, eno OUTinteger)
 IS
 BEGIN
  SELECTcount(*) INTO eno FROM emp WHERE deptno = dno;
  SELECTsum(salary) INTO tsal FROM emp WHERE deptno = dno;
END;


DECLARE
 ts integer;
 nointeger;
BEGIN
 totsal(&dept, ts, no);
 dbms_output.put_line('Total Salary : ' || ts);
 dbms_output.put_line('Total Employees : ' || no);
END;


CREATEOR REPLACE PROCEDURE updt(a VARCHAR,b NUMBER)
AS
BEGIN
UPDATEemp SET ename=a WHERE empid LIKE b;
END;


CREATEOR REPLACE PROCEDURE ins(X NUMBER,Y varchar2)
AS                                                    
BEGIN
INSERTINTO emp(SAL, DESIG) VALUES(X,Y,SYSDATE);
END;


CREATEOR REPLACE PROCEDURE del(a number)
AS
BEGIN
DELETEFROM emp WHERE comm=a;
END;



--FUNCTIONS
CREATEOR REPLACE FUNCTION getSq(num IN number)
RETURNnumber
IS
 sq number;
BEGIN
sq := sqrt(num);
RETURNsq;
END;

SQL>SELECT getsq(9) FROM DUAL;


CREATEOR REPLACE FUNCTION getNames(eno emp.empid%TYPE)
RETURNvarchar
IS
  enm emp.ename%TYPE;
BEGIN
 SELECTename INTO enm FROM emp WHERE empid=eno;
 RETURNenm;
end;

CREATEOR REPLACE FUNCTION getSal(eno IN NUMBER)
 RETURNNUMBER AS
  sal number;
 BEGIN
  SELECTsalary INTO sal FROM EMP WHERE empid LIKE eno;
  RETURNsal;
END;


CREATEOR REPLACE FUNCTION getDept(name emp.ename%TYPE)
returnnumber IS
 dno emp.deptno%TYPE;
BEGIN                                                     
 SELECTdeptno INTO dno FROM emp WHERE ename = upper(&name);
 returndno;
END;


--TRIGGERS
CREATEOR REPLACE TRIGGER UCASE
BEFOREINSERT OR UPDATE OF ename
ONemp
FOREACH ROW
BEGIN
  :NEW.ename := upper(:NEW.ename);
END;


CREATEOR REPLACE TRIGGER fri_trig
BEFOREINSERT OR UPDATE OR DELETE
ONemp
BEGIN
  if(to_char(sysdate,'dy')='fri') then
   Raise_Application_Error(-20001,'Not Possible Because day is friday');
  endif;
END;


CREATEOR REPLACE TRIGGER fri_trig
BEFOREINSERT OR UPDATE OR DELETE
ONemp
DECLARE
 hdt emp.hiredate%TYPE;
BEGIN
  if(to_char(hdt,'dy')='fri') then
   Raise_Application_Error(-20001,'Not Possible Because day is friday');
  endif;
END;


--EXCEPTIONS
DECLARE
 a varchar2(10);
 b varchar2(10);
 c varchar2(10);

BEGIN
 SELECTename, job, sal INTO a,b,c FROM emp WHERE deptno= '&deptno';
 dbms_output.put_line(A||' '||B||' '||C);

EXCEPTION
 WHEN  TOO_MANY_ROWS THEN
 dbms_output.put_line('USE CURSOR');

END;


DECLARE
 eno emp.empno%TYPE;
 nameemp.ename%TYPE;

BEGIN
 eno :=&num;
 SELECTename INTO name FROM emp WHERE empno LIKE eno;

EXCEPTION
 WHENNO_DATA_FOUND THEN
 dbms_output.put_line('NO DATA FOUND !!!');

END;


DECLARE
 num1 integer;
 num2 integer;
 expEXCEPTION;

BEGIN
 num1 :=&n1;
 num2 :=&n2;

 IFnum1 > num2 THEN  dbms_output.put_line('NUM1 is Greater');
 ELSE   RAISE exp;
ENDIF;

EXCEPTION
 WHENexp THEN   dbms_output.put_line('NUM2 is Greater');

END;


DECLARE
 eno number;
 excp exception;
 eid emp.empid%TYPE;

BEGIN
 eno :=&en;

 SELECTempid INTO eid FROM emp WHERE empid=eno;

 IF(eno = empid) THEN
  selectename from emp where empid = eno;
  dbms_output.put_line(ename);
 ELSE
  RAISEexcp;
 ENDIF;

EXCEPTION
 WHENexcp THEN
  dbms_output.put_line(' id doesnt match');

END;


DECLARE
 eno number;
 excp exception;
 eid emp.empid%TYPE;

BEGIN
 eno :=&en;

 SELECTempid INTO eid FROM emp WHERE empid=eno;

 IF(eid = eno) THEN
  dbms_output.put_line('Emp ID : ' || eid);
  dbms_output.put_line('Emp No : ' || eno);
 ELSE
  RAISEexcp;
 ENDIF;

EXCEPTION
 WHENexcp THEN
 dbms_output.put_line('id do not match');

END;



--CURSORS
DECLARE
 nameemp.ename%TYPE;
 CURSORemp_cursor IS
 SELECTename FROM emp;

BEGIN
 OPENemp_cursor;
   FETCHemp_cursor INTO name;
 CLOSEemp_cursor;

dbms_output.put_line(name);

END;


DECLARE
 nameemp.ename%TYPE;
 CURSORemp_cursor IS
 SELECTename FROM emp;

BEGIN
 OPENemp_cursor;
   LOOP
    FETCHemp_cursor INTO name;
     dbms_output.put_line(name);
     EXITWHEN emp_cursor%NOTFOUND;
    ENDLOOP;
 CLOSEemp_cursor;

dbms_output.put_line(name);

END;


DECLARE
 nameemp.ename%TYPE;
 CURSORemp_cursor IS
 SELECTename FROM emp;

BEGIN
 OPENemp_cursor;
   LOOP
    FETCHemp_cursor INTO name;
     IFemp_cursor%FOUND THEN
      dbms_output.put_line(name);
     ELSE
      EXIT;
     ENDIF;
   ENDLOOP;
 CLOSEemp_cursor;
END;


DECLARE
 nameemp.ename%TYPE;
 CURSORemp_cursor IS
 SELECTename FROM emp;

BEGIN
 OPENemp_cursor;
   LOOP
    FETCHemp_cursor INTO name;
     IFemp_cursor%ROWCOUNT > 10 THEN
    commit;
     ENDIF;
endloop;
CLOSEemp_cursor;

dbms_output.put_line('name');

END;


DECLARECURSOR emp1 IS
SELECTename, desig, salary FROM emp WHERE deptno = &deptno;

A VARCHAR2(10);
B VARCHAR2(10);
C EMP.SALARY%TYPE;

BEGIN
 OPENemp1;
 LOOP
  dbms_output.put_line(A||' '||B||' '||C);
  FETCHemp1 INTO A,B,C ;
  EXITWHEN emp1%NOTFOUND;
 ENDLOOP;
CLOSEemp1;
END;


DECLARECURSOR emp2(job emp.desig%TYPE)
IS
 SELECTename, desig, salary FROM emp WHERE desig LIKE upper('&job');
 A VARCHAR2(10);
 B VARCHAR2(10);
 C NUMBER(10);
 job  emp.desig%TYPE;
BEGIN
 job:=job;
 OPENemp2(job);
 LOOP
  dbms_output.put_line(A||' '||B||' '||C);
  FETCHemp2 INTO A,B,C ;
  EXITWHEN emp2%NOTFOUND;
 ENDLOOP;
CLOSEemp2;
END;


DECLARECURSOR emp3 is
 SELECT* FROM dept;
BEGIN
 FORi IN emp3
 LOOP
  dbms_output.put_line(i.DEPTNO||' '||i.Dname||'   '||i.LOC);
 ENDLOOP;
END;



--GRANTS
CREATEUSER tss IDENTIFIED BY tss;
CREATEUSER demo IDENTIFIED BY demo;
GRANTALL ON TAB TO TSS WITH GRANT OPTION;

GRANTSELECT ON emp to demo;
GRANTINSERT ON dept TO demo;

SELECT* FROM tss.emp;
INSERTINTO tss.emp VALUES(111,'AAA');

GRANTALL ON emp TO PUBLIC;
REVOKEINSERT ON dept FROM demo;


--LOBS(LARGE OBJECTS)
--Creating Virtual Directory
---------------------------
USER:SYSTEM
PASSWORD:MANAGER

createdirectory "images" as 'D:\TSS\IMAGES';
   

--GRANTING Privileges to the user
--------------------------------
 GRANTREAD ON DIRECTORY "IMAGES" TO SCOTT;


--Creating Table
--------------
USER:SCOTT
PASSWORD:TIGER

CREATETABLE OBJ(NAME VARCHAR2(10),PIC BFILE);

--Inserting Values
----------------
INSERTINTO OBJ VALUES('PIC1',BFILENAME('IMAGES','D:\TSS\IMAGES\PIC1.JPG'));


--OBJECTS
Createtype Address as Object(
  Street Varchar2(20),
  City  Varchar2(20)
);


Createtype Phone as Object(
  Phone1 NUMBER(20),
  Phone2 NUMBER(20),
  Phone3 NUMBER(20),
  Phone4 NUMBER(20),
  Phone5 NUMBER(20)
);


Createtable Cust(
 NoNumber(2),
 NameVarchar2(20),
 Adds Address,
 Ph   Phone
);

Insert  into Cust Values (1,'Ramesh',address('kalinga','Vsp'),Phone(5542338,2793584,null,null,null));

SelectNo,Name from cust;
SelectNo,Name,Street from cust;  //error
SelectNo,Name,a.adds.Street,a.ph.phone1 from cust a;


Createtype odate as Object(
    Bdate date, 
    memberfunction age return number
 );

CreateType Body odate as
    memberfunction age
    returnnumber
    is
    begin
   return(to_char(sysdate,'yyyy')-to_char(Bdate,'yyyy'));
   end;
end;


createtable stud(name varchar2(20),
        date1 odate);

 Insertinto Stud values ('Ramesh',Odate('15-dec-1977'));
 Insertinto Stud values ('Prasad',Odate('10-dec-1986'));

SelectName,a.date1.bdate from stud a;
SelectName,a.date1.bdate ,a.date1.age() from stud a;



--PACKAGE
CREATEOR REPLACE PACKAGE emps AS

 FUNCTIONgetName(eno emp.empid%TYPE)
  RETURNVARCHAR
  AS
   nameemp.ename%TYPE;
  BEGIN
   SELECTename INTO name FROM emp WHERE empid LIKE eno;
   RETURNname;
 END;

 PROCEDUREsetSal(name emp.ename%TYPE);
  IS
  BEGIN
   UPDATEemp SET salary = 20000 WHERE ename LIKE name;
   dbms_output.put_line(' DATA UPDATED !!!');
 END;

END;


Posted by Jithendra Kumar B
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)