Oracle PLSQL Tutorials for Beginners 3 : Oracle PLSQL Procedure with ex...

/******************************************************************************
                  SAMPLE PLSQL PROCEDURE
                  =>   IN Parameter -- Passed By Reference
                  =>   OUT Parameter  -- Passed By Value
                  =>   IN OUT Parameter   -- Passed By Value
                  =>   Position Based Parameters Notation.
                  =>   Named Based Parameters  Notation. (=>) Arrow Symbol
                  =>   Mixed Parameters Notaion.
                  =>   NOCOPY Clause for changing Pass by value to reference.
******************************************************************************/
CREATE OR REPLACE  PROCEDURE First_PROC
AS
BEGIN
   dbms_output.put_line(' Welcome To First PLSQL Procedure...');
END;
/

exec first_proc;

/*****************************************************************************
                           IN & OUT Parameter
*****************************************************************************/

DECLARE
   V_Num1 number;
   V_Num2 number;
   V_Num3 number;
PROCEDURE findMax(A IN number, B IN number, C OUT number) IS
BEGIN
   IF A > B THEN
      C:= A;
   ELSE
      C:= B;
   END IF;
END;  
BEGIN
   V_Num1:= 12;
   V_Num2:= 55;
   --- Position based Notation
   findMax(V_num1, V_Num2, V_NUM3);
   dbms_output.put_line(' Position Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
   --- Named Based Notation using Arrow =>
   findMax(c=>V_NUM3,b=>V_Num2,a=>V_num1 );
   dbms_output.put_line(' Named Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
   -- Mixed Notation
   findMax( V_num1, c=>V_NUM3, b=>V_Num2);
   dbms_output.put_line(' MIXED Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
END;
/


/******************************************************************************
                        IN OUT Parameter Example
******************************************************************************/

DECLARE
  -- Declaring A Variable for Taking output from Procedure..
   a number;
  -- Declaring Procedure in Declaration Block for IN OUT Parameter....
 
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
/** Inside Procedure Doing Calculation for Square Root... */
  x := x * x;
END; 
-- Ending Procedure....
BEGIN
   --Declaring A Value for In Put Parameter for Above Proc..
   a:= 5;
  /**  Calling Procedure  **/
   dbms_output.put_line('Calling PLSQL Procedure here....With IN OUT Parameter');
   dbms_output.put_line('...................');
   squareNum(a);
   /* Printing OUT Parameter Value using DBMS_OUTPUT Package */
   dbms_output.put_line(' Square of (5): ' || a);
   dbms_output.put_line('...................');

END;
/

/******************************************************************************
                      Creating Procedure in DB
                      => TYPE --- Record Type
                      => ROWTYPE  -- Collection Type
******************************************************************************/
varchar2(30)

CREATE OR REPLACE PROCEDURE Get_emp_rec_proc (Emp_number  IN  Emp.Empno%TYPE,
                       Emp_ret     OUT Emp%ROWTYPE) IS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM emp
      WHERE Empno = Emp_number;
END;
/
/******************************************************************************
                      Executing Procedure with Parameters....                    
******************************************************************************/

DECLARE
   Emp_row      Emp%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp table
BEGIN
   Get_emp_rec_proc(7499, Emp_row);   -- call for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);
   DBMS_OUTPUT.NEW_LINE;
END;
/

/******************************************************************************
                      OUT &  IN OUT Parameter WITH NOCOPY Example
******************************************************************************/


create or replace procedure No_Copy_Proc
               (PI_Name     in             varchar2,
                PO_Date     out    nocopy  date,
                PIO_Status in out nocopy  varchar2) is
begin
   PO_Date := sysdate;
   PIO_Status := PI_name || ' ::: Sample IN OUT Parameter NOCOPY Testing';
end;
/

DECLARE
  V_DATE DATE;
  V_Status Varchar2(200);
BEGIN
No_Copy_Proc('Raveendra Reddy',V_DATE,V_Status);
dbms_output.put_line(V_DATE);
dbms_output.put_line(V_Status);

END;