What Is a Procedure?

What Is a Procedure?

A procedure: Is a type of subprogram that performs an action
Can be stored in the database as a schema object
Promotes reusability and maintainability

CREATE [OR REPLACE] PROCEDURE procedure_name
 [(parameter1 [mode] datatype1,
   parameter2 [mode] datatype2, ...)]
IS|AS
  [local_variable_declarations; …]
BEGIN  -- actions;
END [procedure_name];


sql> create table dept as select * from departments;


CREATE PROCEDURE add_dept IS
 dept_id dept.department_id%TYPE;
 dept_name dept.department_name%TYPE;
BEGIN
 dept_id:=280;
 dept_name:='ST-Curriculum';
 INSERT INTO dept(department_id,department_name)
 VALUES(dept_id,dept_name);
 DBMS_OUTPUT.PUT_LINE(' Inserted '|| SQL%ROWCOUNT ||' row ');
END;

To invoke a procedure in iSQL*Plus, use the following EXECUTE command:

begin

add_dept;

End;