Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Oracle » PL-SQL » Coding »

Creating a stored procedure

     



Below is a simple stored procedure which deletes an invoice.

Note:

- 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 )
AS
INV_COUNT NUMBER ;

BEGIN

INV_COUNT := 0;

/* check if invoice exists */

SELECT COUNT(*)
INTO INV_COUNT
FROM INVOICE
WHERE INV_ID = INV_ID1 ;

IF INV_COUNT > 0 THEN

DELETE FROM INVOICE WHERE INV_ID = INV_ID1 ;
COMMIT ;

END IF ;

END ;










Oracle : Related Topics

Oracle : PL-SQL : Displaying output
Oracle : PL-SQL : Output variables
Oracle : PL-SQL : Arrays and structures
Oracle : PL-SQL : Conditionals
Oracle : PL-SQL : Looping in PL-SQL
Oracle : PL-SQL : Packages
Oracle : PL-SQL : Exception Handling
Oracle : PL-SQL : Sleep and Wait
Oracle : PL-SQL : Date Manipulation
Oracle : PL-SQL : Parallel Query Processing
Oracle : PL-SQL : Exit if error, in a SQL script
Oracle : PL-SQL : Cursors
Oracle : PL-SQL : Complex IF/THEN Processing
Oracle : PL-SQL : Creating triggers
Oracle : PL-SQL : Ranking Data
Oracle : PL-SQL : How to do an outer join in Oracle
Oracle : PL-SQL : Loops in PL-SQL : Hierarchy

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.