write procedures in oracle

In Oracle, you can create your own procedures as follows.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name

    [ (parameter [,parameter]) ]

IS

    [declaration_section]

BEGIN

    executable_section

[EXCEPTION

    exception_section]

END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.


http://www.devshed.com/c/a/Oracle/Oracle-Stored-Procedures/2/
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html#procedures

== Mallesh

Malar, if this helps please login to Mark As Answer. | Alert Moderator

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;

OR refer this url

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm

Malar, if this helps please login to Mark As Answer. | Alert Moderator

Hi

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name

    [ (parameter [,parameter]) ]

IS

    [declaration_section]

BEGIN

    executable_section

[EXCEPTION

    exception_section]

END [procedure_name];

Regards

R D Patel

Malar, if this helps please login to Mark As Answer. | Alert Moderator

Hi

for more details please refer this links

http://www.exampledepot.com/egs/java.sql/createprocedureandfunction.html

http://www.techonthenet.com/oracle/procedures.php

regards

R D Patel

Malar, if this helps please login to Mark As Answer. | Alert Moderator

This Docs May Help You.

http://docs.oracle.com/cd/A60725_05/html/comnls/us/pa/cxovie05.htm

Also Use Following Code For Create Procedure.

You need the CREATE PROCEDURE system privilege to create a procedure in your own schema.

Open SQL*Plus from Windows and log on to your database.

From SQL*Plus, open your skeleton.sql file.

SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:

1 CREATE OR REPLACE PROCEDURE skeleton
2 IS
3 BEGIN
4 NULL;
5* END;
SQL>

Execute the contents of the SQL*Plus buffer. Type a front slash and press <enter> like this:

SQL> /

SQL*Plus informs you the procedure has been created successfully and presents the SQL command prompt:

Procedure created.

Malar, if this helps please login to Mark As Answer. | Alert Moderator