Create Function PL/Sql

Function returns a value to the caller.

As a procedure, a function has two parts: the specification and the body.

Implementation is placed in the body part.

The following statement creates the function get_app_rec. This is the body of the function.

  FUNCTION get_app_rec
  (p_app_no  IN  appointment.app_no%TYPE)
  RETURN appointment%ROWTYPE
   IS
      v_rec                  appointment%ROWTYPE;
   BEGIN
      SELECT *
        INTO v_rec
        FROM appointment
       WHERE app_no = p_app_no
      ;
      RETURN v_rec;
   END;

Spec of the function is :

   FUNCTION get_app_rec
      (p_app_no  IN  appointment.app_no%TYPE)
   RETURN appointment%ROWTYPE;

Create Procedure PL/Sql

Procedure does not return a value to the caller.

Usually insert/update/delete operations are placed in a procedure.

A procedure has two parts: the specification and the body. Implementation is placed in the body part.

The following statement creates the procedure insert_visit_log. This is the body of the procedure.

  PROCEDURE insert_visit_log
      (p_visit_no  IN  visit_log.visit_no%TYPE,
       p_aim_code  IN  visit_log.aim_code%TYPE,
       p_op_name   IN  visit_log.op_name%TYPE,
       p_date      IN  visit_log.v_date%TYPE)
   IS
   BEGIN
      INSERT INTO visit_log
             (visit_no,aim_code,op_name,v_date)
      VALUES (p_visit_no, p_aim_code,p_op_name,p_date)
      ;
   END;

Spec of the procedure is :

  PROCEDURE insert_visit_log
      (p_visit_no  IN  visit_log.visit_no%TYPE,
       p_aim_code  IN  visit_log.aim_code%TYPE,
       p_op_name   IN  visit_log.op_name%TYPE,
       p_date      IN  visit_log.v_date%TYPE);

Create Sequence Pl/Sql

Sequence is usually used to generate unique primary key values for a relational database table.

Because a sequence is an independent db object, it is created and used independent of a database table.

Same sequence can be used for different tables.

The following statement creates the sequence visitor_seq.

CREATE SEQUENCE VISITOR_SEQ
  INCREMENT BY 1
  START WITH 1
  MINVALUE 1
  MAXVALUE 9999999999
  NOCYCLE
  NOORDER
  ; 

In order to drop previously created sequence issue following pl/sql statement.

DROP SEQUENCE VISITOR_SEQ;