Example of a Sample Procedure In PLSQL

Topic Introduction: This tutorial will give an Example of a Sample Procedure In PLSQL.

Procedure without Parameter

CREATE OR REPLACE PROCEDURE Proc_Add_Department

IS

    v_dept_id     departments.department_id%TYPE;

    v_dept_name   departments.department_name%TYPE;

BEGIN

    v_dept_id := 155;

    v_dept_name := 'test_department';

    INSERT INTO departments (department_id, department_name)

         VALUES (v_dept_id, v_dept_name);

    DBMS_OUTPUT.put_line ('Inserted ' || SQL%ROWCOUNT || ' row ');

END;

Invoking Procedure without Parameter

Method #1

BEGIN

    Proc_Add_Department;

END;

Method #2

EXEC Proc_Add_Department;

Method #3

EXECUTE Proc_Add_Department;

Check Data

SELECT department_id, department_name

  FROM departments

 WHERE department_id = 155;

Drop or Remove Procedure

DROP PROCEDURE Proc_Add_Department;

Procedure with Parameter

CREATE OR REPLACE PROCEDURE Proc_Add_Department(p_dept_id      NUMBER,

                                            p_dept_name    VARCHAR2)

IS

BEGIN

    INSERT INTO departments (department_id, department_name)

         VALUES (p_dept_id, p_dept_name);

    DBMS_OUTPUT.put_line ('Inserted ' || SQL%ROWCOUNT || ' row ');

END;

Invoking Procedure with Parameter

Method #1

BEGIN

    Proc_Add_Department(155,'test_department');

END;

Method #2

EXEC Proc_Add_Department(155,'test_department');

Method #3

EXECUTE Proc_Add_Department(155,'test_department');