Procedure Example in Oracle

As Salamo alaikum wa rahmatullah

Select deptno, ename, sal, sum(sal) over(partition by
deptno order by deptno, ename) CUM_SAL
from
scott.emp;



-- exmaple of anonimous body
Declare
vname varchar2(20);
begin
select ename into vname from emp where empno=7788;
dbms_output.put_line(vname);
end;


-- Example of Implicit cursor
Declare
emp_row emp%RowType;
Begin
Select * into emp_row from emp where empno=7790;
if sql%notfound then
dbms_output.put_line('Record not found');
else
dbms_output.put_line('Record found');
end if;
end;
-- Procedure without parameter

create or replace Procedure Proc_Test
as
v_sal number(18,3);
Begin
Select Sal into v_sal from emp where empno=7788;
dbms_output.put_line(v_sal);
end Proc_Test;


-- Procedure with one IN parameter
create or replace Procedure Proc_Test_Inparameter (eno IN number)
as
vname emp.ename%type;
Begin
select ename into vname from emp where empno=eno;
dbms_output.put_line(vname);
Exception
when others then
dbms_output.put_line('Wrong number');
End Proc_Test_Inparameter;


-- Procedure with one IN one Out parameter

Create or replace procedure Proc_Test_InOutParam(eno IN number, enm OUT varchar2)
As
Begin
Select ename into enm from emp where empno=eno;
Exception
when others then
enm:='no data found';
End Proc_Test_InOutParam;


-- Procdure for INOUT parameter
create or replace procedure Proc_Test_InOutParam1(en IN OUT varchar)
As
Begin
Select job into en from emp where ename=en;
End Proc_Test_InOutParam1;



Declare
nm varchar2(100);
Begin
--Proc_Test;
--Proc_Test_Inparameter(7371);
Proc_Test_InOutParam(7788,nm);
dbms_output.put_line(nm);
nm:='SMITH';
Proc_Test_InOutParam1(nm);
dbms_output.put_line(nm);
End;



--
Ma Asalaam
Passion 4 Oracle