Procedure

The PL stands for Procedural Language, which means you can have things like IF statements, loops, variables, and other procedural things along with declarative SQL statements.The general format of a create procedure statement is this:

CREATE OR REPLACE

PROCEDURE procedure_name ( parameters ) AS

BEGIN

procedure_body

END;

The parameters (or arguments) are optional. You don’t have to specify anything (not even the parenthesis). Unlike the type specifier in a PL/SQL variable declaration, the type specifier in a parameter declaration must be unconstrained. For example, CHAR(10) and VARCHAR(20) are illegal; CHAR or VARCHAR should be used instead. The actual length of a parameter depends on the corresponding argument that is passed in when the procedure is invoked.

The DECLARE section should not start with the keyword DECLARE. Rather, following AS.

Example:

This uses same table as in the trigger example(student). The procedure is created to insert the name and marks to the student table.

proc.sql

=======

create or replace procedure proc_test(name IN varchar2, marks in number) as

BEGIN

insert into student values(name,marks);

END

;

/

The @ command can be used to execute the code. @ followed by the location and filename.

SQL> @/home/jestinjoy/proc.sql

Procedure created.

exec is used to execute the procedure.

SQL> exec proc_test('bibin',39);

PL/SQL procedure successfully completed.

SQL> select * from student;

NAME MARKS

------------------------------ ----------

arun 44

jacob 40

bibin 39