PL/SQL-PROCEDURES - 1

[debasisdas]

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
==================================================

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.table_name);
END LOOP;
END;
 

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

[sunaynajoon]

can any 1 tell me advantages of using stored procedures in postgresql.........