About procedure and function

mainly about the different between procedure and function.

procedure and function

1.

CREATE OR REPLACE ROCEDURE procedure_name
(optional parameters) IS | AS

        declarative part  

BEGIN

      program body

EXCEPTION

     exception handler

END procedure_name;

2.

CREATE OR REPLACE FUNCTION
student_status(optional parameters)

                  RETURN VARCHAR2 IS

           declarative part

BEGIN

         program body

         RETURN expression;

EXCEPTION

        exception handler code

             that should include a RETURN

END student_student_status;

test:

1.

CREATE OR REPLACE PROCEDURE print_temp

IS

    v_average NUMBER;

    v_sum     NUMBER;

BEGIN

    SELECT AVG(n), SUM(n) INTO v_average, v_sum

    FROM TEMP;

    dbms_output.put_line('Average:'||v_average);

    dbms_output.put_line('Sum:'||v_sum);

END print_temp;

2.

CREATE OR REPLACE FUNCTION tomorrow RETURN DATE

IS

    next_day DATE;

BEGIN

    next_day := SYSDATE + 1;

    RETURN next_day;

END tomorrow;

or as fellow:

FUNCTION tomorrow RETURN DATE IS

BEGIN

    RETURN SYSDATE + 1;

END tomorrow;

3.

build pkg:

CREATE OR REPLACE PACKAGE students_pkg IS

  PROCEDURE print_temp

    (v_average  IN  number,v_sum   in  NUMBER);

  FUNCTION tomorrow(v_sum   in  NUMBER)

  RETURN DATE;

END students_pkg;

/

build pkb:

CREATE OR REPLACE PACKAGE BODY students_pkg IS

  PROCEDURE print_temp

    (v_average   IN  number,v_sum   in  NUMBER)

  IS

  BEGIN

   
dbms_output.put_line('PROCEDURE:print_temp');

  END print_temp;

  FUNCTION tomorrow(v_sum   in  NUMBER)

  RETURN DATE

  IS

    ccount INTEGER;

    next_day DATE;

  BEGIN

    next_day := SYSDATE + 1;

    RETURN next_day;

    end;

END students_pkg;

/

run:

set serveroutput on;

declare

t DATE;

begin

students_pkg.print_temp(1,1);

t:=students_pkg.tomorrow(1);

dbms_output.put_line('data='||to_char(t));

 end;

/

PROCEDURE:print_temp

data=28-MAY-16

Storage procedure is considered as the object exists in the Oracle database program units.

A procedure should be named with a verb.

A procedure usually perform actions such as update the database,write data to a file,or send a message.

Function provides access to the object's state and case information.

In function the RETURN statement is a must.FUNCTION must have a return statement.