PROCEDURE AND FUNCTIONS

Procedure :
A Procedure is a group of PLSQL statements.A procedure is a subprogram that performs a specific action. The procedure declare and define inside a PL/SQL block or package.
A stored procedure is a set of SQL statements with an assigned name. It stored in a RDBMS as a group, so it can be reused and shared by multiple programs.

Syntax :
  Create or replace procedure procedure_name
  [(argument[mode1]datatype1,argument2[mode2]datatype2,..);
  Is|as
  Procedure_body;

Invoking the procedure :
  Begin
  Procedure_name;
  End;
  /
  Exec procedure_name;
  Procedure
  SQL> set serveroutput on;
  SQL> create procedure tesdb_mesg_p1 as
  Begin
  return('welcome to TESDB');
  end;
  /
  exec tesdb_mesg_p1;
  Welcome to TESDB
  PL/SQL procedure successfully completed.

Two-type of procedure :
Static procedure------without arguments.
Dynamic procedure-----with arguments.

Arguments :
In---read----(default) always receives values
Out---write---always sends values.
Inout---read/write----receives and sends values.

Procedure – IN Parameter :
  SQL> create or replace procedure tesdb_p2(v_dno in dept.deptno%type)

  is
  begin
  update dept set loc=‘Washington’ where deptno=v_dno;
  end tesdb_p2;
  / 
  Procedure created
  SQL> exec tesdb_p2(40);

  PL/SQL procedure successfully completed.

  SQL> select * from dept;
    DEPTNO            DNAME            LOC
    ---------     ----------------  --------------
      10             ACCOUNTING       NEW YORK
      20             RESEARCH           DALLAS
      30             SALES             CHICAGO
      40           OPERATIONS         Washington

Procedure – OUT Parameter : 
  SQL> create or replace procedure tesdb_p3 
  (v_dno in dept.deptno%type)
  (v_name out dept.dname%type)
  is
  begin
  select dname into v_name
  from dept where deptno=v_dno;
  end tesdb_p3;
  /
  Procedure created

  SQL> declare
  name varchar2(10);
  begin
  tesdb_p3(&v_dno, v_name)
  dbms_output.put_line(v_name);
  end;
  / 
  Enter value for v_dno: 30
  old 4: tesdb_p3(&v_dno, v_name);
  new 4: tesdb_p3(30, v_name);
  SALES
  PL/SQL procedure successfully completed

Procedure – IN OUT Parameter : 
  SQL> create or replace procedure tesdb_p4
  (v_id in out number)
  is
  begin
  select sal into v_id
  from emp where empno=v_id;
  end tesdb_p3;
  /
  Procedure created

  SQL> declare
  eid number := &v_id;
  begin
  tesdb_p4(&v_id)
  dbms_output.put_line(v_id);
  end;
  /
  Enter value for v_id: 7788
  old 2: v_id number:= &eid;
  new 2: v_id number:=7788;
  3000
  PL/SQL procedure successfully completed

  SQL> select object_name, object_type from user_objects where object_type='PROCEDURE‘; 

          OBJECT_NAME          OBJECT_TYPE
      ------------------- -------------------
          P1                  PROCEDURE
          P2                  PROCEDURE
          P3                  PROCEDURE
          P4                  PROCEDURE
  View to procedure

  SQL> select text from user_source

      2 where name='P1';
      TEXT
      --------------------------------------------------
      procedure p1 as
      begin

  dbms_output.put_line('welcome to TESDB');
  end;

Function :
Function must return a value.Functions can have only input parameters. Functions can be called from Procedure whereas Procedures cannot be called from Function.
A stored function is a set of PL/SQL statements you can call by name. It also called an user function or user-defined function. Functions very similar to procedures, except that a function returns a value to called. User functions can be used as part of a SQL expression.

Syntax : 
  create [or replace] function function_name
  [(argument [mode1] datatype1, argument2 [mode2] datatype2,..):
  is|as
  return datatype
  function_body;

  invoking the function
  declare
  variable datatype;
  begin
  variable:= function_name;
  end;
  /

Executing function in 3 methods :
They are 
  select function_name from dual;

  VARIABLE:=function_name();

  Dbms_output.put_line(function_name);

Function : 
SQL> create or replace function tesdb_f1 
  return char 
  as
  begin
  return('welcome to function in plsql');
  end;
  /
  Function created.

  SQL> select tesdb_f1() from dual; 

      TESDB_F1()
      ----------------------------------------
      welcome to function in plsql Function

      Function created.

Function using argument passing method :
  SQL> create or replace function f2 (eid in emp.empno%type)
  return number
  is
  fsal emp.sal%type;
  begin
  select sal into fsal from emp where empno=eid;
  return fsal;
  end f2;
  /
  SQL> select f2(7788) from dual;
  F2(7788)
  3000

Function : 
  SQL> select text from user_source where name=‘TESDB_F1';

    TEXT
    -------------------------------------------------
    function tesdb_f1

  return char
  as
  begin
  return('welcome to function in plsql');
  End;
  6 rows selected.

Difference between Procedure & Function : 
                Procedure 	                                            Function
   ---------------------------------------            -----------------------------------------------------
    stored in database ready to execute.	            Function can stored in procedure and return value.
    It can return zero, single or multiple values.	    It can return one value which is mandatory.
    We can use transaction in stored procedure	        we can't use transaction in UDF
    It have input/output parameter	                    Only input parameter
    We can called function from procedure.	            we can't call procedure from function.
    We can't use select/where/having statement	        we can use UDF in select/where/having statement


(PL/SQL - Create Packages)