Database stored Procedures

1. Create a stored procedure

CREATE PROCEDURE procedure name ([parameter 1, Parameter 2,...])

As

<pl/sql>;

CREATE PROCEDURE transfer (inaccount int, outaccount int, amount FLOAT) as declare totaldeposit    Float;begin    Select Total to Totaldeposit from account where Accountnum=outaccount;    If totaldeposit is null and then        rollback;        return;    End If;    If Totaldeposit < amount then        rollback;        return;    End If;    Update account set total=total-amount where Accountnum=outaccount;    Update account set total=total+amount where Accountnum=inaccount;    Commit;end;

2. Renaming stored Procedures

Alter PROCEDURE procedure name 1 Rename to procedure name 2;

3. Execute the Stored procedure

Call/perform procedure Process Name ([parameter 1, Parameter 2,...]);

4. Delete a stored procedure

drop procedure process name ();

Database stored Procedures