storedprocedure.sql

从vc连接到oracle数据库。包括连接

 --***********************************
 --Definition of package specification
 --***********************************  
CREATE OR REPLACE PACKAGE oraoledb AS

 /**
 *  @author  Chandar
 *  @version 1.0
 *   Development Environment        :  Oracle 8i
 *  Name of the File               :  StoredProcedure.sql
 *  Creation/Modification History  :
 *                                  07-Aug-2001     Created
 *  Overview of Package:
 *  This package specification contains definition of a ref cursor type .
 *  The variables of this type will be used by stored procedures in the application
 *  to send multiple records from database to application
 **/      
  TYPE m_refcur IS REF CURSOR;
END oraoledb;
/

--*******************************************************************
 --Procedure to get the product information using ref cursor arguments
 --*******************************************************************
CREATE OR REPLACE PROCEDURE getProductInfo(p_orderable OUT
 oraoledb.m_refcur,p_udevelopment OUT oraoledb.m_refcur) IS
 
 /**
 *  @author  Chandar
 *  @version 1.0
 *  Development Environment        :  Oracle 8i
 *  Name of the File               :  StoredProcedure.sql
 *  Creation/Modification History  :
 *                                  07-Aug-2001     Created
 *  Overview of the Procedure:

 *  This procedure is called from the function GetProductInformation in OLEHandler.cpp file in OleMR.dsw
 *  VC++ application.
 *  It gets the information of products of two categories ('Orderable' and 'UnderDevelopment' in two 
 *  different REF CURSOR variables

 *  Parameters:
 *  @p_orderable - OUT - A Ref Cursor variable passed to GetProductInfo function in OLEHandler.cpp file.
 *  @p_udevelopment - OUT- A Ref Cursor variable passed to GetProductInfo function in OLEHandler.cpp file.
 **/

 BEGIN

    --get records for products  with category as 'Orderable' in p_orderable ref cursor
    OPEN p_orderable FOR SELECT
    product_name,category,supplier_name,min_price FROM
    supplier,product_information WHERE product_status='orderable'
    AND supplier.supplier_id=product_information.supplier_id ORDER BY min_price DESC;

    --get records for products  with category as 'Under Development' in p_udevelopment ref cursor
    OPEN p_udevelopment FOR SELECT
    product_name,category,supplier_name,min_price FROM
    supplier,product_information WHERE product_status='under development' AND
    supplier.supplier_id=product_information.supplier_id ORDER BY  min_price DESC;
 
END getProductInfo;
/

--*******************************************************************************
--Procedure to insert the  product name and description using NVarchar2 variables
--*******************************************************************************
CREATE OR REPLACE PROCEDURE insertTranslatedDescription(p_languageid IN VARCHAR2,p_productid IN NUMBER,
p_translatedname IN NVARCHAR2,p_translateddescription IN NVARCHAR2,p_check OUT NUMBER)
IS
 
 /**
 *  @author  Chandar
 *  @version 1.0
 *  Development Environment        :  Oracle 8i
 *  Name of the File               :  StoredProcedure.sql
 *  Creation/Modification History  :
 *                                  07-Aug-2001     Created
 *  Overview of the Procedure:

 *  This procedure is called from the function InsertDescription in OLEHandler.cpp file in OleMR.dsw
 *  VC++ application.
 *  It inserts the product name and description of selected product in selected language  
 *  If the data already exists in table, it is  updated. The parameter p_check return zero if data
 *  is inserted else returns 1.

 *  Parameters:
 *  @p_languageid - IN - Language Id passed from InsertDescription function in OLEHandler.cpp file.
 *  @p_productid - IN - Product Id passed from InsertDescription function in OLEHandler.cpp file.
 *  @p_translatedname - IN - Translated name of product passed from InsertDescription function in OLEHandler.cpp file.
 *  @p_translated description - IN - Translated description of product  passed from InsertDescription function
 *                                   in OLEHandler.cpp file.
 *  @p_check - OUT -  Passed from InsertDescription function in OLEHandler.cpp file.
 **/

BEGIN
p_check:=0;

 --check if the name and description for selected product and language already exist
SELECT COUNT(*) INTO p_check FROM product_descriptions WHERE  product_id=p_productid AND language_id=p_languageid;

IF (p_check<1) THEN

  --if it does not exist insert into table
  INSERT INTO product_descriptions
                         VALUES(p_productid,p_languageid,p_translatedname,p_translateddescription);
ELSE
UPDATE product_descriptions set translated_name=p_translatedname,translated_description=p_translateddescription where 
        product_id=p_productid and language_id=p_languageid;

END IF;

END insertTranslatedDescription;
/