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
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.
This uses same table as in the trigger example(student). The procedure is created to insert the name and marks to the student table.
create or replace procedure proc_test(name IN varchar2, marks in number) as
insert into student values(name,marks);
The @ command can be used to execute the code. @ followed by the location and filename.
exec is used to execute the procedure.
SQL> exec proc_test('bibin',39);
PL/SQL procedure successfully completed.
SQL> select * from student;