The following package was generated by Scriptoria for Oracle from all
Query Definitions in the Microsoft Northwind Traders sample database.
CREATE OR REPLACE PACKAGE BODY NORTHWIND.PKG_NORTHWIND
AS
PROCEDURE ALPHABETICAL_LIST_OF_PRODUCTS
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT PRODUCTS.*,
CATEGORIES.CATEGORYNAME
FROM NORTHWIND.CATEGORIES,
NORTHWIND.PRODUCTS
WHERE (CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID)
AND (
(((PRODUCTS.DISCONTINUED)=0))
)
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE CATEGORY_SALES_FOR_1997
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT PRODUCT_SALES_FOR_1997.CATEGORYNAME,
Sum(PRODUCT_SALES_FOR_1997.PRODUCTSALES) AS CATEGORYSALES
FROM (
SELECT DISTINCT CATEGORIES.CATEGORYNAME,
PRODUCTS.PRODUCTNAME,
Sum(TO_NUMBER(ORDER_DETAILS.UNITPRICE*QUANTITY*(1-DISCOUNT)/100)*100) AS PRODUCTSALES,
'Qtr ' || TO_CHAR(TO_CHAR(SHIPPEDDATE,'Q')) AS SHIPPEDQUARTER
FROM NORTHWIND.CATEGORIES,
NORTHWIND.PRODUCTS,
NORTHWIND.ORDERS,
NORTHWIND.ORDER_DETAILS
WHERE (CATEGORIES.CATEGORYID = PRODUCTS.CATEGORYID
AND ORDERS.ORDERID = ORDER_DETAILS.ORDERID
AND PRODUCTS.PRODUCTID = ORDER_DETAILS.PRODUCTID)
AND (
(((ORDERS.SHIPPEDDATE) Between TO_DATE(19970101000000,'YYYYMMDDHH24MISS') AND TO_DATE(19971231000000,'YYYYMMDDHH24MISS')))
)
GROUP BY CATEGORIES.CATEGORYNAME,
PRODUCTS.PRODUCTNAME,
'Qtr ' || TO_CHAR(TO_CHAR(SHIPPEDDATE,'Q'))
) PRODUCT_SALES_FOR_1997
GROUP BY PRODUCT_SALES_FOR_1997.CATEGORYNAME
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE CURRENT_PRODUCT_LIST
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT PRODUCT_LIST.PRODUCTID,
PRODUCT_LIST.PRODUCTNAME
FROM NORTHWIND.PRODUCTS PRODUCT_LIST
WHERE (
(((PRODUCT_LIST.DISCONTINUED)=0))
)
ORDER BY PRODUCT_LIST.PRODUCTNAME
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE CUSTOMERS_AND_SUPPLIERS_BY_CIT
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT CITY,
COMPANYNAME,
CONTACTNAME,
'Customers' AS RELATIONSHIP
FROM NORTHWIND.CUSTOMERS
UNION SELECT CITY,
COMPANYNAME,
CONTACTNAME,
'Suppliers'
FROM NORTHWIND.SUPPLIERS
ORDER BY CITY,
COMPANYNAME
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE EMPLOYEE_SALES_BY_COUNTRY
(
crsrResultSet IN OUT refCursor,
BEGINNING_DATE DATE,
ENDING_DATE DATE
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT EMPLOYEES.COUNTRY,
EMPLOYEES.LASTNAME,
EMPLOYEES.FIRSTNAME,
ORDERS.SHIPPEDDATE,
ORDERS.ORDERID,
ORDER_SUBTOTALS.SUBTOTAL AS SALEAMOUNT
FROM NORTHWIND.EMPLOYEES,
NORTHWIND.ORDERS,
(
SELECT DISTINCT ORDER_DETAILS.ORDERID,
Sum(TO_NUMBER(UNITPRICE*QUANTITY*(1-DISCOUNT)/100)*100) AS SUBTOTAL
FROM NORTHWIND.ORDER_DETAILS
GROUP BY ORDER_DETAILS.ORDERID
) ORDER_SUBTOTALS
WHERE (ORDERS.ORDERID=ORDER_SUBTOTALS.ORDERID
AND EMPLOYEES.EMPLOYEEID=ORDERS.EMPLOYEEID)
AND (
(((ORDERS.SHIPPEDDATE) Between BEGINNING_DATE AND ENDING_DATE))
)
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE INVOICES
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT ORDERS.SHIPNAME,
ORDERS.SHIPADDRESS,
ORDERS.SHIPCITY,
ORDERS.SHIPREGION,
ORDERS.SHIPPOSTALCODE,
ORDERS.SHIPCOUNTRY,
ORDERS.CUSTOMERID,
CUSTOMERS.COMPANYNAME,
CUSTOMERS.ADDRESS,
CUSTOMERS.CITY,
CUSTOMERS.REGION,
CUSTOMERS.POSTALCODE,
CUSTOMERS.COUNTRY,
FIRSTNAME || ' ' || LASTNAME AS SALESPERSON,
ORDERS.ORDERID,
ORDERS.ORDERDATE,
ORDERS.REQUIREDDATE,
ORDERS.SHIPPEDDATE,
SHIPPERS.COMPANYNAME,
ORDER_DETAILS.PRODUCTID,
PRODUCTS.PRODUCTNAME,
ORDER_DETAILS.UNITPRICE,
ORDER_DETAILS.QUANTITY,
ORDER_DETAILS.DISCOUNT,
TO_NUMBER(ORDER_DETAILS.UNITPRICE*QUANTITY*(1-DISCOUNT)/100)*100 AS EXTENDEDPRICE,
ORDERS.FREIGHT
FROM NORTHWIND.SHIPPERS,
NORTHWIND.PRODUCTS,
NORTHWIND.EMPLOYEES,
NORTHWIND.CUSTOMERS,
NORTHWIND.ORDERS,
NORTHWIND.ORDER_DETAILS
WHERE (CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID
AND EMPLOYEES.EMPLOYEEID=ORDERS.EMPLOYEEID
AND ORDERS.ORDERID=ORDER_DETAILS.ORDERID
AND PRODUCTS.PRODUCTID=ORDER_DETAILS.PRODUCTID
AND SHIPPERS.SHIPPERID=ORDERS.SHIPVIA)
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERSCUSTOMERID_AUTOLKUP
(
crsrResultSet IN OUT refCursor,
P_CUSTOMERID VARCHAR2
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT ADDRESS,
CITY,
REGION,
POSTALCODE,
COUNTRY
FROM NORTHWIND.CUSTOMERS
WHERE (
CUSTOMERID = P_CUSTOMERID
)
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_CUSTOMERID
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT CUSTOMERS.CUSTOMERID,
CUSTOMERS.COMPANYNAME
FROM NORTHWIND.CUSTOMERS
ORDER BY CUSTOMERS.COMPANYNAME
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_DELETE
(
p_OrderID NUMBER,
p_OrigCustomerID VARCHAR2,
p_OrigEmployeeID NUMBER,
p_OrigOrderDate DATE,
p_OrigRequiredDate DATE,
p_OrigShippedDate DATE,
p_OrigFreight NUMBER,
p_OrigShipName VARCHAR2,
p_OrigShipAddress VARCHAR2,
p_OrigShipCity VARCHAR2,
p_OrigShipRegion VARCHAR2,
p_OrigShipPostalCode VARCHAR2,
p_OrigShipCountry VARCHAR2,
p_OrigShipVia NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
SELECT NVL(COUNT(*), 0) INTO p__ROWS_RETURNED FROM ORDERS WHERE (ORDERID = p_OrderID) AND ((p_OrigCustomerID IS NULL AND CUSTOMERID IS NULL) OR CUSTOMERID = p_OrigCustomerID And (p_OrigEmployeeID IS NULL AND EMPLOYEEID IS NULL) OR EMPLOYEEID = p_OrigEmployeeID And (p_OrigOrderDate IS NULL AND ORDERDATE IS NULL) OR ORDERDATE = p_OrigOrderDate And (p_OrigRequiredDate IS NULL AND REQUIREDDATE IS NULL) OR REQUIREDDATE = p_OrigRequiredDate And (p_OrigShippedDate IS NULL AND SHIPPEDDATE IS NULL) OR SHIPPEDDATE = p_OrigShippedDate And (p_OrigFreight IS NULL AND FREIGHT IS NULL) OR FREIGHT = p_OrigFreight And (p_OrigShipName IS NULL AND SHIPNAME IS NULL) OR SHIPNAME = p_OrigShipName And (p_OrigShipAddress IS NULL AND SHIPADDRESS IS NULL) OR SHIPADDRESS = p_OrigShipAddress And (p_OrigShipCity IS NULL AND SHIPCITY IS NULL) OR SHIPCITY = p_OrigShipCity And (p_OrigShipRegion IS NULL AND SHIPREGION IS NULL) OR SHIPREGION = p_OrigShipRegion And (p_OrigShipPostalCode IS NULL AND SHIPPOSTALCODE IS NULL) OR SHIPPOSTALCODE = p_OrigShipPostalCode And (p_OrigShipCountry IS NULL AND SHIPCOUNTRY IS NULL) OR SHIPCOUNTRY = p_OrigShipCountry And (p_OrigShipVia IS NULL AND SHIPVIA IS NULL) OR SHIPVIA = p_OrigShipVia);
IF p__ROWS_RETURNED = 1 THEN
DELETE ORDERS
WHERE ORDERID = p_OrderID;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_EMPLOYEEID
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT EMPLOYEES.EMPLOYEEID,
LASTNAME || ', ' || FIRSTNAME AS SALESPERSON
FROM NORTHWIND.EMPLOYEES
ORDER BY LASTNAME || ', ' || FIRSTNAME
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_INSERT
(
p_CustomerID VARCHAR2,
p_EmployeeID NUMBER,
p_OrderDate DATE,
p_RequiredDate DATE,
p_ShippedDate DATE,
p_ShipVia NUMBER,
p_Freight NUMBER,
p_ShipName VARCHAR2,
p_ShipAddress VARCHAR2,
p_ShipCity VARCHAR2,
p_ShipRegion VARCHAR2,
p_ShipPostalCode VARCHAR2,
p_ShipCountry VARCHAR2,
p_OrderID OUT NUMBER
)
AS
BEGIN
INSERT INTO ORDERS(CUSTOMERID, EMPLOYEEID, ORDERDATE, REQUIREDDATE, SHIPPEDDATE, SHIPVIA, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE, SHIPCOUNTRY)
VALUES(p_CustomerID, p_EmployeeID, p_OrderDate, p_RequiredDate, p_ShippedDate, p_ShipVia, p_Freight, p_ShipName, p_ShipAddress, p_ShipCity, p_ShipRegion, p_ShipPostalCode, p_ShipCountry);
COMMIT;
SELECT ORDERS_SQ.CurrVal INTO p_OrderID FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_QRY
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT ORDERS.ORDERID,
ORDERS.CUSTOMERID,
ORDERS.EMPLOYEEID,
ORDERS.ORDERDATE,
ORDERS.REQUIREDDATE,
ORDERS.SHIPPEDDATE,
ORDERS.SHIPVIA,
ORDERS.FREIGHT,
ORDERS.SHIPNAME,
ORDERS.SHIPADDRESS,
ORDERS.SHIPCITY,
ORDERS.SHIPREGION,
ORDERS.SHIPPOSTALCODE,
ORDERS.SHIPCOUNTRY,
CUSTOMERS.COMPANYNAME,
CUSTOMERS.ADDRESS,
CUSTOMERS.CITY,
CUSTOMERS.REGION,
CUSTOMERS.POSTALCODE,
CUSTOMERS.COUNTRY
FROM NORTHWIND.CUSTOMERS,
NORTHWIND.ORDERS
WHERE (CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID)
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_SUBFORM_DELETE
(
p_OrderID NUMBER,
p_ProductID NUMBER,
p_OrigUnitPrice NUMBER,
p_OrigQuantity NUMBER,
p_OrigDiscount NUMBER,
p_OrigProductID NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
SELECT NVL(COUNT(*), 0) INTO p__ROWS_RETURNED FROM ORDER_DETAILS WHERE (ORDERID = p_OrderID AND PRODUCTID = p_ProductID) AND (UNITPRICE = p_OrigUnitPrice And QUANTITY = p_OrigQuantity And DISCOUNT = p_OrigDiscount And PRODUCTID = p_OrigProductID);
IF p__ROWS_RETURNED = 1 THEN
DELETE ORDER_DETAILS
WHERE ORDERID = p_OrderID AND PRODUCTID = p_ProductID;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_SUBFORM_INSERT
(
p_OrderID NUMBER,
p_ProductID NUMBER,
p_UnitPrice NUMBER,
p_Quantity NUMBER,
p_Discount NUMBER
)
AS
BEGIN
INSERT INTO ORDER_DETAILS(ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT)
VALUES(p_OrderID, p_ProductID, p_UnitPrice, p_Quantity, p_Discount);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_SUBFORM_PRODUCTID
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT PRODUCTID,
PRODUCTNAME,
DISCONTINUED
FROM NORTHWIND.PRODUCTS
ORDER BY PRODUCTNAME
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_SUBFORM_SUBFRM
(
crsrResultSet IN OUT refCursor,
P_ORDERID NUMBER
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT ORDER_DETAILS.ORDERID,
ORDER_DETAILS.PRODUCTID,
PRODUCTS.PRODUCTNAME,
ORDER_DETAILS.UNITPRICE,
ORDER_DETAILS.QUANTITY,
ORDER_DETAILS.DISCOUNT,
TO_NUMBER(ORDER_DETAILS.UNITPRICE*QUANTITY*(1-DISCOUNT)/100)*100 AS EXTENDEDPRICE
FROM NORTHWIND.PRODUCTS,
NORTHWIND.ORDER_DETAILS
WHERE (PRODUCTS.PRODUCTID=ORDER_DETAILS.PRODUCTID)
AND (
(P_ORDERID = ORDERID)
)
ORDER BY ORDER_DETAILS.ORDERID
;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;
PROCEDURE ORDERS_SUBFORM_UPDATE
(
p_OrderID NUMBER,
p_ProductID NUMBER,
p_OrigUnitPrice NUMBER,
p_NewUnitPrice NUMBER,
p_OrigQuantity NUMBER,
p_NewQuantity NUMBER,
p_OrigDiscount NUMBER,
p_NewDiscount NUMBER,
p_OrigProductID NUMBER,
p_NewProductID NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
SELECT NVL(COUNT(*), 0) INTO p__ROWS_RETURNED FROM ORDER_DETAILS WHERE (ORDERID = p_OrderID AND PRODUCTID = p_ProductID) AND (UNITPRICE = p_OrigUnitPrice And QUANTITY = p_OrigQuantity And DISCOUNT = p_OrigDiscount And PRODUCTID = p_OrigProductID);
IF p__ROWS_RETURNED = 1 THEN
UPDATE ORDER_DETAILS
SET UNITPRICE = p_NewUnitPrice, QUANTITY = p_NewQuantity, DISCOUNT = p_NewDiscount, PRODUCTID = p_NewProductID
WHERE ORDERID = p_OrderID AND PRODUCTID = p_ProductID;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END ORDERS_SUBFORM_UPDATE;
PROCEDURE ORDERS_UPDATE
(
p_OrderID NUMBER,
p_OrigCustomerID VARCHAR2,
p_NewCustomerID VARCHAR2,
p_OrigEmployeeID NUMBER,
p_NewEmployeeID NUMBER,
p_OrigOrderDate DATE,
p_NewOrderDate DATE,
p_OrigRequiredDate DATE,
p_NewRequiredDate DATE,
p_OrigShippedDate DATE,
p_NewShippedDate DATE,
p_OrigFreight NUMBER,
p_NewFreight NUMBER,
p_OrigShipName VARCHAR2,
p_NewShipName VARCHAR2,
p_OrigShipAddress VARCHAR2,
p_NewShipAddress VARCHAR2,
p_OrigShipCity VARCHAR2,
p_NewShipCity VARCHAR2,
p_OrigShipRegion VARCHAR2,
p_NewShipRegion VARCHAR2,
p_OrigShipPostalCode VARCHAR2,
p_NewShipPostalCode VARCHAR2,
p_OrigShipCountry VARCHAR2,
p_NewShipCountry VARCHAR2,
p_OrigShipVia NUMBER,
p_NewShipVia NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
SELECT NVL(COUNT(*), 0) INTO p__ROWS_RETURNED FROM ORDERS WHERE (ORDERID = p_OrderID) AND ((p_OrigCustomerID IS NULL AND CUSTOMERID IS NULL) OR CUSTOMERID = p_OrigCustomerID And (p_OrigEmployeeID IS NULL AND EMPLOYEEID IS NULL) OR EMPLOYEEID = p_OrigEmployeeID And (p_OrigOrderDate IS NULL AND ORDERDATE IS NULL) OR ORDERDATE = p_OrigOrderDate And (p_OrigRequiredDate IS NULL AND REQUIREDDATE IS NULL) OR REQUIREDDATE = p_OrigRequiredDate And (p_OrigShippedDate IS NULL AND SHIPPEDDATE IS NULL) OR SHIPPEDDATE = p_OrigShippedDate And (p_OrigFreight IS NULL AND FREIGHT IS NULL) OR FREIGHT = p_OrigFreight And (p_OrigShipName IS NULL AND SHIPNAME IS NULL) OR SHIPNAME = p_OrigShipName And (p_OrigShipAddress IS NULL AND SHIPADDRESS IS NULL) OR SHIPADDRESS = p_OrigShipAddress And (p_OrigShipCity IS NULL AND SHIPCITY IS NULL) OR SHIPCITY = p_OrigShipCity And (p_OrigShipRegion IS NULL AND SHIPREGION IS NULL) OR SHIPREGION = p_OrigShipRegion And (p_OrigShipPostalCode IS NULL AND SHIPPOSTALCODE IS NULL) OR SHIPPOSTALCODE = p_OrigShipPostalCode And (p_OrigShipCountry IS NULL AND SHIPCOUNTRY IS NULL) OR SHIPCOUNTRY = p_OrigShipCountry And (p_OrigShipVia IS NULL AND SHIPVIA IS NULL) OR SHIPVIA = p_OrigShipVia);
IF p__ROWS_RETURNED = 1 THEN
UPDATE ORDERS
SET CUSTOMERID = p_NewCustomerID, EMPLOYEEID = p_NewEmployeeID, ORDERDATE = p_NewOrderDate, REQUIREDDATE = p_NewRequiredDate, SHIPPEDDATE = p_NewShippedDate, FREIGHT = p_NewFreight, SHIPNAME = p_NewShipName, SHIPADDRESS = p_NewShipAddress, SHIPCITY = p_NewShipCity, SHIPREGION = p_NewShipRegion, SHIPPOSTALCODE = p_NewShipPostalCode, SHIPCOUNTRY = p_NewShipCountry, SHIPVIA = p_NewShipVia
WHERE ORDERID = p_OrderID;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END ORDERS_UPDATE;
PROCEDURE ORDER_DETAILS_EXTENDED
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
OPEN crsrResultSet FOR
SELECT DISTINCT ORDER_DETAILS.ORDERID,
ORDER_DETAILS.PRODUCTID,
PRODUCTS.PRODUCTNAME,
ORDER_DETAILS.UNITPRICE,
ORDER_DETAILS.QUANTITY,
ORDER_DETAILS.DISCOUNT,
TO_NUMBER(ORDER_DETAILS.UNITPRICE*QUANTITY*(1-DISCOUNT)/100)*100 AS EXTENDEDPRICE
FROM NORTHWIND.PRODUCTS,
NORTHWIND.ORDER_DETAILS
WHERE (PRODUCTS.PRODUCTID=ORDER_DETAILS.PRODUCTID)
ORDER BY ORDER_DETAILS.ORDERID
;