Procedure in Plsql

A procedure in oracle is program Unit that performs a particular task it also called as subprogram.this procedure can be

be invoked by another procedure or program which is called the calling program.

It can be created in pl sql block,also inside a package,and schema level.


Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < procedure_body >
END procedure_name;

The foll example creates a simple procedure that displays Good Morning On the screen when it executed.

Create or replace procedure pro1()
AS
begin

dbms_output.put_line('Good Morning');

End;



When above code will executed it will show the foll output.

procedure created.

Execute pro1;

Good Morning
Pl sql Procedure successfully completed




Example 2.

Here is an example of procedure that uses a Named Defined Procedure


Create [or Replace] Function  function_name
[(parameter[,Parameter])]
Return  return datatype
Is|As
[declaration_section]
Exception_name EXCEPTION;
Begin
Executable section
Raise exception_name;
Exception
When exception_name then
[statements]
When others Then
[statements]
End [function_name];


Syntax for Procedure:

Create [or Replace] Procedure Procedure_name
[(parameter [,parameter])]
is

[declaration_section]

exception_section]

exception_name EXCEPTION;

begin

executable_section
Raise exception_name;
EXCEPTION
when exception_name then
[statements]
when others Then
[statements]
End [Procedure_name];




CREATE OR REPLACE PROCEDURE add_new_emp
   (empno  IN NUMBER, fname in varchar2 )
IS
   no_emp EXCEPTION;
BEGIN
   IF empno = 0 THEN
      RAISE no_emp;
   ELSE
      INSERT INTO emp (employee_id, first_name )
      VALUES ( empno, fname );
   END IF;
EXCEPTION
   WHEN no_emp THEN
      raise_application_error (-20001,'You must have sales in order to subm
e order.');
   WHEN OTHERS THEN
      raise_application_error (-20002,'An error has occurred inserting an o
');
END;
/