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