Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Monday, February 18, 2013

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
 no emp.empid%type;
 name emp.ename%type;
 sal emp.salary%type;
 ansal number(15);
BEGIN
 no := &empno;
 SELECT ename, 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;
 name emp.ename%type;
BEGIN
 eno := &empno;
 SELECT ename 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;
 SELECT sum(salary) INTO nsal FROM emp GROUP BY deptno where deptno LIKE dno;
 SELECT dname INTO dna FROM dept WHERE deptno LIKE dno;
 dbms_output.put_line('HAI dname:' ||dna ||'sum of salary' ||nsal);
END;


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


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

  IF var_comm IS NULL THEN
    UPDATE emp SET comm = 300
    WHERE empid = var_empno;
  ELSE
    var_comm := var_comm + var_comm * 25;
    UPDATE emp SET comm = var_comm
    WHERE empid = var_empno;
  END IF;
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
  SELECT empid, ename, mgr
  INTO emp_num, emp_name, mgr_num
  FROM emp
  WHERE empid = start_num;

  LOOP
    SELECT empid, mgr, ename
    INTO emp_num, mgr_num, mgr_name
    FROM emp
    WHERE empid = mgr_num;

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

    EXIT WHEN mgr_num IS NULL;

    start_num := emp_num;
    emp_num := mgr_num;
    emp_name := mgr_name;
  END LOOP;

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


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

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





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


BEGIN
 for i in  reverse 1..10
  LOOP
  dbms_output.put_line(i);
 END LOOP;
END;


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


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


BEGIN
 for i in   1..5
  LOOP
  for j in 1..10
   LOOP
   dbms_output.put_line(i||' * '||j||' = '||i*j);
  END LOOP;
   dbms_output.put_line('------------------------');
 END LOOP;
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);
 END if;
end if;
END;


DECLARE
 a emp.ename%TYPE;
 b emp.desig%TYPE;
 c emp.salary%TYPE;
BEGIN
 SELECT ename,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
CREATE OR REPLACE PROCEDURE getName(eid emp.empid%TYPE)
IS
 name emp.ename%TYPE;
BEGIN
 SELECT ename INTO name FROM EMP WHERE empid like eid;
 dbms_output.put_line('Employee Name is : ' || name);
END;   


CREATE OR REPLACE PROCEDURE getName(EID emp.empid%TYPE, name OUT emp.ename%TYPE)
IS
BEGIN
SELECT ename 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;


CREATE OR REPLACE PROCEDURE totsal(dno IN integer, tsal OUT integer, eno OUT integer)
 IS
 BEGIN
  SELECT count(*) INTO eno FROM emp WHERE deptno = dno;
  SELECT sum(salary) INTO tsal FROM emp WHERE deptno = dno;
END;


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


CREATE OR REPLACE PROCEDURE updt(a VARCHAR,b NUMBER)
AS
BEGIN
UPDATE emp SET ename=a WHERE empid LIKE b;
END;


CREATE OR REPLACE PROCEDURE ins(X NUMBER,Y varchar2)
AS                                                    
BEGIN
INSERT INTO emp(SAL, DESIG) VALUES(X,Y,SYSDATE);
END;


CREATE OR REPLACE PROCEDURE del(a number)
AS
BEGIN
DELETE FROM emp WHERE comm=a;
END;



--FUNCTIONS
CREATE OR REPLACE FUNCTION getSq(num IN number)
RETURN number
IS
 sq number;
BEGIN
sq := sqrt(num);
RETURN sq;
END;

SQL> SELECT getsq(9) FROM DUAL;


CREATE OR REPLACE FUNCTION getNames(eno emp.empid%TYPE)
RETURN varchar
IS
  enm emp.ename%TYPE;
BEGIN
 SELECT ename INTO enm FROM emp WHERE empid=eno;
 RETURN enm;
end;

CREATE OR REPLACE FUNCTION getSal(eno IN NUMBER)
 RETURN NUMBER AS
  sal number;
 BEGIN
  SELECT salary INTO sal FROM EMP WHERE empid LIKE eno;
  RETURN sal;
END;


CREATE OR REPLACE FUNCTION getDept(name emp.ename%TYPE)
return number IS
 dno emp.deptno%TYPE;
BEGIN                                                     
 SELECT deptno INTO dno FROM emp WHERE ename = upper(&name);
 return dno;
END;


--TRIGGERS
CREATE OR REPLACE TRIGGER UCASE
BEFORE INSERT OR UPDATE OF ename
ON emp
FOR EACH ROW
BEGIN
  :NEW.ename := upper(:NEW.ename);
END;


CREATE OR REPLACE TRIGGER fri_trig
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
  if(to_char(sysdate,'dy')='fri') then
   Raise_Application_Error(-20001,'Not Possible Because day is friday');
  end if;
END;


CREATE OR REPLACE TRIGGER fri_trig
BEFORE INSERT OR UPDATE OR DELETE
ON emp
DECLARE
 hdt emp.hiredate%TYPE;
BEGIN
  if(to_char(hdt,'dy')='fri') then
   Raise_Application_Error(-20001,'Not Possible Because day is friday');
  end if;
END;


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

BEGIN
 SELECT ename, 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;
 name emp.ename%TYPE;

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

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

END;


DECLARE
 num1 integer;
 num2 integer;
 exp EXCEPTION;

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

 IF num1 > num2 THEN  dbms_output.put_line('NUM1 is Greater');
 ELSE   RAISE exp;
END IF;

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

END;


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

BEGIN
 eno := &en;

 SELECT empid INTO eid FROM emp WHERE empid=eno;

 IF(eno = empid) THEN
  select ename from emp where empid = eno;
  dbms_output.put_line(ename);
 ELSE
  RAISE excp;
 END IF;

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

END;


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

BEGIN
 eno := &en;

 SELECT empid 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
  RAISE excp;
 END IF;

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

END;



--CURSORS
DECLARE
 name emp.ename%TYPE;
 CURSOR emp_cursor IS
 SELECT ename FROM emp;

BEGIN
 OPEN emp_cursor;
   FETCH emp_cursor INTO name;
 CLOSE emp_cursor;

dbms_output.put_line(name);

END;


DECLARE
 name emp.ename%TYPE;
 CURSOR emp_cursor IS
 SELECT ename FROM emp;

BEGIN
 OPEN emp_cursor;
   LOOP
    FETCH emp_cursor INTO name;
     dbms_output.put_line(name);
     EXIT WHEN emp_cursor%NOTFOUND;
    END LOOP;
 CLOSE emp_cursor;

dbms_output.put_line(name);

END;


DECLARE
 name emp.ename%TYPE;
 CURSOR emp_cursor IS
 SELECT ename FROM emp;

BEGIN
 OPEN emp_cursor;
   LOOP
    FETCH emp_cursor INTO name;
     IF emp_cursor%FOUND THEN
      dbms_output.put_line(name);
     ELSE
      EXIT;
     END IF;
   END LOOP;
 CLOSE emp_cursor;
END;


DECLARE
 name emp.ename%TYPE;
 CURSOR emp_cursor IS
 SELECT ename FROM emp;

BEGIN
 OPEN emp_cursor;
   LOOP
    FETCH emp_cursor INTO name;
     IF emp_cursor%ROWCOUNT > 10 THEN
    commit;
     END IF;
end loop;
CLOSE emp_cursor;

dbms_output.put_line('name');

END;


DECLARE CURSOR emp1 IS
SELECT ename, desig, salary FROM emp WHERE deptno = &deptno;

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

BEGIN
 OPEN emp1;
 LOOP
  dbms_output.put_line(A||' '||B||' '||C);
  FETCH emp1 INTO A,B,C ;
  EXIT WHEN emp1%NOTFOUND;
 END LOOP;
CLOSE emp1;
END;


DECLARE CURSOR emp2(job emp.desig%TYPE)
IS
 SELECT ename, 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;
 OPEN emp2(job);
 LOOP
  dbms_output.put_line(A||' '||B||' '||C);
  FETCH emp2 INTO A,B,C ;
  EXIT WHEN emp2%NOTFOUND;
 END LOOP;
CLOSE emp2;
END;


DECLARE CURSOR emp3 is
 SELECT * FROM dept;
BEGIN
 FOR i IN emp3
 LOOP
  dbms_output.put_line(i.DEPTNO||' '||i.Dname||'   '||i.LOC);
 END LOOP;
END;



--GRANTS
CREATE USER tss IDENTIFIED BY tss;
CREATE USER demo IDENTIFIED BY demo;
GRANT ALL ON TAB TO TSS WITH GRANT OPTION;

GRANT SELECT ON emp to demo;
GRANT INSERT ON dept TO demo;

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

GRANT ALL ON emp TO PUBLIC;
REVOKE INSERT ON dept FROM demo;


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

create directory "images" as 'D:\TSS\IMAGES';
   

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


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

CREATE TABLE OBJ(NAME VARCHAR2(10),PIC BFILE);

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


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


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


Create table Cust(
 No Number(2),
 Name Varchar2(20),
 Adds Address,
 Ph   Phone
);

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

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


Create type odate as Object(
    Bdate date, 
    member function age return number
 );

Create Type Body odate as
    member function age
    return number
    is
    begin
   return(to_char(sysdate,'yyyy')-to_char(Bdate,'yyyy'));
   end;
end;


create table stud(name varchar2(20),
        date1 odate);

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

Select Name,a.date1.bdate from stud a;
Select Name,a.date1.bdate ,a.date1.age() from stud a;



--PACKAGE
CREATE OR REPLACE PACKAGE emps AS

 FUNCTION getName(eno emp.empid%TYPE)
  RETURN VARCHAR
  AS
   name emp.ename%TYPE;
  BEGIN
   SELECT ename INTO name FROM emp WHERE empid LIKE eno;
   RETURN name;
 END;

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

END;


Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 11:16:00 PM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect