write procedures in oracle

Posted by Malar under Others on 7/7/2012 | Points: 10 | Views : 1119 | Status : [Member] | Replies : 5
how to write procedures in oracle
how to call in c#




Responses

Posted by: Mallesh on: 7/7/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Ranjeet_8 on: 7/11/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
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

Posted by: Patel28rajendra on: 7/11/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Patel28rajendra on: 7/11/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Ketvin on: 7/12/2012 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response