PROCEDURE:-IT IS A COMPILED BLOCK OF CODE WHICH IS STORED AS AN OBJECT WITHIN THE DATABASE. IT MAY OR MAY NOT RETURN ANY VALUE OR MIGHT RETURN MORE THAN ONE VALUE.
syntax
------------
CREATE [OR REPLACE] PROCEDURE PROCEDURENAME([PARAMETER PARAMETER MODE DATATYPE [,PARAMETER...,. ...]])
{IS/AS}
[LOCAL DECLARATION];
BEGIN
EXECUTABLE STATMENT;
[EXCEPTION
EXCEPTION HANDLER]
END [PROCEDURENAME];
SAMPLE EXAMPLE TO SHOW PARAMETER LESS PROCEDURE
=============== =============== =============== =====
[CODE=oracle]
CREATE OR REPLACE PROCEDURE list_tables AS
BEGIN
dbms_output.put _line('These are the tables you own:');
FOR item IN (SELECT table_name FROM user_tables)
LOOP
dbms_output.put _line(item.tabl e_name);
END LOOP;
END;
[/CODE]
Parameters
===========
1.formal---declared In The Defination Of The Procedure.they Receive The Value.
2.actual---passed Within Parenthesis,to Execute The Procedure.
Parameter Mode--it Specifies What Can Be Done With The Parameters.
In/out/in Out
------------------------
In--(default)-it Lets To Pass A Value To The Subprogram Being Called.the Value Can't Be Changed Inside The Subprogram.it Is Like A Constant.it Can't Be Assigned Any Value.
Out--it Lets The Subprogram Pass A Value To The Caller.inside The Subprogram The Out Parameter Is An Un-initialised Variable.
Subprogram Has To Place A Value In The Out Parameter. What Ever Changes Are Made To The Out Parameter Are Made Available To The Actual Parameter.the Actual Parameter Corresponding To The Out Parameter Must Be A Variable.
In Out--it Is Same As In And Out Both Together.it Can Get A Value From The Calling Procedure And Can Return A Value To The Calling Procedure.the Value Of This Type Of Paramater Can Be Used In Subprogram And The Actual Parameter Must Be An Initialised Variable.
Also check PL/SQL-PROCEDURES - 2
syntax
------------
CREATE [OR REPLACE] PROCEDURE PROCEDURENAME([PARAMETER PARAMETER MODE DATATYPE [,PARAMETER...,. ...]])
{IS/AS}
[LOCAL DECLARATION];
BEGIN
EXECUTABLE STATMENT;
[EXCEPTION
EXCEPTION HANDLER]
END [PROCEDURENAME];
SAMPLE EXAMPLE TO SHOW PARAMETER LESS PROCEDURE
=============== =============== =============== =====
[CODE=oracle]
CREATE OR REPLACE PROCEDURE list_tables AS
BEGIN
dbms_output.put _line('These are the tables you own:');
FOR item IN (SELECT table_name FROM user_tables)
LOOP
dbms_output.put _line(item.tabl e_name);
END LOOP;
END;
[/CODE]
Parameters
===========
1.formal---declared In The Defination Of The Procedure.they Receive The Value.
2.actual---passed Within Parenthesis,to Execute The Procedure.
Parameter Mode--it Specifies What Can Be Done With The Parameters.
In/out/in Out
------------------------
In--(default)-it Lets To Pass A Value To The Subprogram Being Called.the Value Can't Be Changed Inside The Subprogram.it Is Like A Constant.it Can't Be Assigned Any Value.
Out--it Lets The Subprogram Pass A Value To The Caller.inside The Subprogram The Out Parameter Is An Un-initialised Variable.
Subprogram Has To Place A Value In The Out Parameter. What Ever Changes Are Made To The Out Parameter Are Made Available To The Actual Parameter.the Actual Parameter Corresponding To The Out Parameter Must Be A Variable.
In Out--it Is Same As In And Out Both Together.it Can Get A Value From The Calling Procedure And Can Return A Value To The Calling Procedure.the Value Of This Type Of Paramater Can Be Used In Subprogram And The Actual Parameter Must Be An Initialised Variable.
Also check PL/SQL-PROCEDURES - 2
Comment