Below is a simple stored procedure which deletes an invoice.
- variable declaration placement
- the syntax for comments /* --- */
- ALL select statements must have an into statement for the
result set. Oracle stored procedures must use "out" variables to return
results to client programs.
- the declaration of INV_ID1 uses the column def as a prototype
CREATE OR REPLACE PROCEDURE PROC_DELETE_INVOICE
( USERID1 VARCHAR2, INV_ID1 INVOICE.INV_ID%TYPE )
INV_COUNT NUMBER ;
INV_COUNT := 0;
/* check if invoice exists */
WHERE INV_ID = INV_ID1 ;
IF INV_COUNT > 0 THEN
DELETE FROM INVOICE WHERE INV_ID = INV_ID1 ;
END IF ;