Procedure and function Syntax and Example

SAMPLE PROCEDURE AND FUNCTION

CREATE OR REPLACE PROCEDURE xxx_827 (c IN NUMBER, d IN NUMBER)
IS
   a   NUMBER;
   b   NUMBER;
BEGIN
   a := c;
   b := d;

   IF a > b
   THEN
      DBMS_OUTPUT.put_line ('A is greater  ' || a);
   ELSE
      DBMS_OUTPUT.put_line ('B is greater  ' || b);
   END IF;
END;
                          3 Number procedure
                 ------------------------------------------

create or replace procedure xx_310(a in number,b in number,c in number)as
begin
if a > b
then
dbms_output.PUT_LINE('a   is greater' || a);
elsif a > c
then
dbms_output.PUT_LINE('b   is greater' || b);
else
dbms_output.PUT_LINE('c   is greater' || c);
end if;
end;

exec xx_310 (2,3,5)
 
                                 
                          2 Number procedure
                 ------------------------------------------
                                 
create or replace procedure ela_sys(a in number,b in number)is
begin
if a> b
then
dbms_output.PUT_LINE('a is greater'||a);
else
dbms_output.PUT_LINE('b is greater'||b);
end if;
end;
                               
                          4  Number procedure
                 ------------------------------------------

create or replace procedure ela_XX(a in number,b in number,c in number,d in number)is
begin
if a > b
then
dbms_output.PUT_LINE('a is greater'||a);
elsif a > c
then
dbms_output.PUT_LINE('b is greater'||b);
elsif a > d
then
dbms_output.PUT_LINE('c is greater'||c);
else
dbms_output.PUT_LINE('d is greater'||d);
end if;
end;

exec ela_xx(2,3,4,5)

USED TO STORED ONLEY ONE COLUMN IN TABLE  procedure
=============================================

create or replace procedure XX_310(errbuf in number,retcode out number) is
cursor ela is select ename from emp;
india ela%rowtype;
begin
 open ela;
 loop
 fetch ela into india;
 insert into elango_827(ename)
 values(india.ename);
 end loop;
 close ela;
  end;

TOTAL TABLE CHANGE
==========================

create or replace procedure XXX_310(errbuf in number,reccode out number)is
cursor ela2 is select * from emp;
begin
for key1 in ela2
loop
insert into dhana_827(empno,ename,job,mgr,hiredate,sal,comm,deptno,enddate,email)
values(key1.empno,key1.ename,key1.job,key1.mgr,key1.hiredate,key1.sal,key1.comm,key1.deptno,key1.enddate,key1.email);
end loop;
end;


                                CREATING PROCEDURE
                             ---------------------------------------------

 create or replace procedure XX_827 is
 begin
   insert into india values('elango',827);
   commit;
  end;
 

  execute XX_827;
 
 select * from india;

  create or replace procedure XX_827 is
 begin
   insert into india values('&ename',&empno);
   commit;
  end;

 execute xx_827;




                          FUNCTION
                -------------------------------------


create or replace function ela7(eno  ela.e_id%type) return number is
sal ela.salary%type;
begin
      select salary into sal from ela where e_id=eno;
      return(sal);
end;


select fun2(4,5)from dual;




PACKAGE
-------------------

CREATE OR REPLACE PACKAGE  xx_82777 is
procedure xx_310(a in number,b in number,c in number);
end xx_82777;

CREATE OR REPLACE PACKAGE body xx_82777 is
 procedure xx_310(a in number,b in number,c in number)as
begin
if a > b
then
dbms_output.PUT_LINE('a   is greater' || a);
elsif a > c
then
dbms_output.PUT_LINE('b   is greater' || b);
else
dbms_output.PUT_LINE('c   is greater' || c);
end if;
end;
end xx_82777;


COMPILING PACKAGES
==========================
1SQL> Alter package PKG compile;
2SQL> Alter package PKG compile specification;
3SQL> Alter package PKG compile body;




WE CAN CALL THE FUNCTION 2 PROCEDURE IN VALUES
================================================>

create or REPLACE function call_pro(a in number,b in number) return number is
c number;
BEGIN
c:=a+b;
RETURN c;
dbms_output.put_line('the counted value is'   ||c);
end;

select call_pro(7,7) from dual;

create or REPLACE PROCEDURE call_fun (a in number,b in number,c out number)is
d number;
begin
c:=a+b;
d:=call_pro(a,b);
dbms_output.put_line('the counted value is'   ||d*c);
end;