パッケージ一覧

 

本サイトで使用する、パッケージを以下に掲げます。

 

得意先のパッケージ(CustomerPackage)

CREATE OR REPLACE PACKAGE CustomerPackage AS

  TYPE rcurCustomers IS REF CURSOR;

  PROCEDURE GetCustomers(

    orcurCustomers OUT rcurCustomers);

  PROCEDURE GetCustomersTop10As(

    orcurCustomers OUT rcurCustomers);

  PROCEDURE GetCustomersGT40(

    orcurCustomers OUT rcurCustomers);

  PROCEDURE InsertCustomers(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2);

  PROCEDURE InsertCustomersCurrVal(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    oCustomerID OUT NUMBER);

  PROCEDURE UpdateCustomers(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER);

  PROCEDURE UpdateCustomersConcurrencyID(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER);

  PROCEDURE UpdateCustomersRc(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    oRowCount OUT NUMBER);

  PROCEDURE UpdateCustomersConcurrencyIDRc(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER,

    oRowCount OUT NUMBER);

  PROCEDURE DeleteCustomers(

    iCustomerID IN NUMBER);

  PROCEDURE DeleteCustomersConcurrencyID(

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER);

  PROCEDURE DeleteCustomersConcurrencyIDRc(

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER,

    oRowCount OUT NUMBER);

END CustomerPackage;

 

CREATE OR REPLACE PACKAGE BODY CustomerPackage AS

  PROCEDURE GetCustomers(

    orcurCustomers OUT rcurCustomers) IS

  BEGIN

    OPEN orcurCustomers FOR

      SELECT *

      FROM Customers

      ORDER BY CustomerID;

  END GetCustomers;

  PROCEDURE GetCustomersTop10As(

    orcurCustomers OUT rcurCustomers) IS

  BEGIN

    OPEN orcurCustomers FOR

      SELECT CustomerID AS ID,

        CompanyName AS 得意先,

        ContactName AS 担当,

        Phone AS 電話

      FROM Customers

      WHERE CustomerID < 11

      ORDER BY CustomerID;

  END GetCustomersTop10As;

  PROCEDURE GetCustomersGT40(

    orcurCustomers OUT rcurCustomers) IS

  BEGIN

    OPEN orcurCustomers FOR

      SELECT *

      FROM Customers

      WHERE CustomerID > 40

      ORDER BY CustomerID;

  END GetCustomersGT40;

  PROCEDURE InsertCustomers(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2) IS

  BEGIN

    INSERT INTO Customers

      (CompanyName, ContactName, Phone)

      VALUES (iCompanyName, iContactName, iPhone);

  END InsertCustomers;

  PROCEDURE InsertCustomersCurrVal(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    oCustomerID OUT NUMBER) IS

  BEGIN

    INSERT INTO Customers

      (CompanyName, ContactName, Phone)

      VALUES (iCompanyName, iContactName, iPhone);

    SELECT Customers_CustomerID_Seq.CURRVAL

    INTO oCustomerID

    FROM DUAL;

  END InsertCustomersCurrVal;

  PROCEDURE UpdateCustomers(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER) IS

  BEGIN

    UPDATE Customers

    SET CompanyName = iCompanyName,

        ContactName = iContactName,

        Phone = iPhone

    WHERE CustomerID = iCustomerID;

  END UpdateCustomers;

  PROCEDURE UpdateCustomersConcurrencyID(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER) IS

  BEGIN

    UPDATE Customers

    SET CompanyName = iCompanyName,

        ContactName = iContactName,

        Phone = iPhone,

        ConcurrencyID = ConcurrencyID+1

    WHERE CustomerID = iCustomerID

        AND ConcurrencyID = iConcurrencyID;

  END UpdateCustomersConcurrencyID;

  PROCEDURE UpdateCustomersRc(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    oRowCount OUT NUMBER) IS

  BEGIN

    UPDATE Customers

    SET CompanyName = iCompanyName,

        ContactName = iContactName,

        Phone = iPhone

    WHERE CustomerID = iCustomerID;

    oRowCount := SQL%ROWCOUNT;

  END UpdateCustomersRc;

  PROCEDURE UpdateCustomersConcurrencyIDRc(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER,

    oRowCount OUT NUMBER) IS

  BEGIN

    UPDATE Customers

    SET CompanyName = iCompanyName,

        ContactName = iContactName,

        Phone = iPhone,

        ConcurrencyID = ConcurrencyID+1

    WHERE CustomerID = iCustomerID

        AND ConcurrencyID = iConcurrencyID;

    oRowCount := SQL%ROWCOUNT;

  END UpdateCustomersConcurrencyIDRc;

  PROCEDURE DeleteCustomers(

    iCustomerID IN NUMBER) IS

  BEGIN

    DELETE

    FROM Customers

    WHERE CustomerID = iCustomerID;

  END DeleteCustomers;

  PROCEDURE DeleteCustomersConcurrencyID(

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER) IS

  BEGIN

    DELETE

    FROM Customers

    WHERE CustomerID = iCustomerID

      AND ConcurrencyID = iConcurrencyID;

  END DeleteCustomersConcurrencyID;

  PROCEDURE DeleteCustomersConcurrencyIDRc(

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER,

    oRowCount OUT NUMBER) IS

  BEGIN

    DELETE

    FROM Customers

    WHERE CustomerID = iCustomerID

      AND ConcurrencyID = iConcurrencyID;

    oRowCount := SQL%ROWCOUNT;

  END DeleteCustomersConcurrencyIDRc;

END CustomerPackage;

 

 

受注のパッケージ(OrderPackage)

CREATE OR REPLACE PACKAGE OrderPackage AS

  TYPE rcurOrders IS REF CURSOR;

  PROCEDURE GetOrders(

    orcurOrders OUT rcurOrders);

  PROCEDURE GetOrdersByCustomerID(

    orcurOrders OUT rcurOrders,

    iCustomerID IN NUMBER);

  PROCEDURE UpdateOrders(

    iShipName IN VARCHAR2,

    iShippedDate IN DATE,

    iOrderID IN NUMBER);

  PROCEDURE UpdateOrdersRc(

    iShipName IN VARCHAR2,

    iShippedDate IN DATE,

    iOrderID IN NUMBER,

    oRowCount OUT NUMBER);

END OrderPackage;

 

CREATE OR REPLACE PACKAGE BODY OrderPackage AS

  PROCEDURE GetOrders(

    orcurOrders OUT rcurOrders) IS

  BEGIN

    OPEN orcurOrders FOR

      SELECT *

      FROM Orders

      ORDER BY OrderID;

  END GetOrders;

  PROCEDURE GetOrdersByCustomerID(

    orcurOrders OUT rcurOrders,

    iCustomerID IN NUMBER) IS

  BEGIN

    OPEN orcurOrders FOR

      SELECT *

      FROM Orders

      WHERE CustomerID = iCustomerID

      ORDER BY OrderID;

  END GetOrdersByCustomerID;

  PROCEDURE UpdateOrders(

    iShipName IN VARCHAR2,

    iShippedDate IN DATE,

    iOrderID IN NUMBER) IS

  BEGIN

    UPDATE Orders

      SET ShipName = iShipName,

        ShippedDate = iShippedDate

      WHERE OrderID = iOrderID;

  END UpdateOrders;

  PROCEDURE UpdateOrdersRc(

    iShipName IN VARCHAR2,

    iShippedDate IN DATE,

    iOrderID IN NUMBER,

    oRowCount OUT NUMBER) IS

  BEGIN

    UPDATE Orders

    SET ShipName = iShipName,

        ShippedDate = iShippedDate

    WHERE OrderID = iOrderID;

    oRowCount := SQL%ROWCOUNT;

  END UpdateOrdersRc;

END OrderPackage;

 

 

受注明細のパッケージ(OrderDetailPackage)

CREATE OR REPLACE PACKAGE OrderDetailPackage AS

  TYPE rcurOrderDetails IS REF CURSOR;

  PROCEDURE GetOrderDetails(

    orcurOrderDetails OUT rcurOrderDetails);

  PROCEDURE GetOrderDetailsByOrderID(

    orcurOrderDetails OUT rcurOrderDetails,

    iOrderID IN NUMBER);

END OrderDetailPackage;

 

CREATE OR REPLACE PACKAGE BODY OrderDetailPackage AS

  PROCEDURE GetOrderDetails(

    orcurOrderDetails OUT rcurOrderDetails) IS

  BEGIN

    OPEN orcurOrderDetails FOR

      SELECT *

      FROM OrderDetails

      ORDER BY OrderID, ProductID;

  END GetOrderDetails;

  PROCEDURE GetOrderDetailsByOrderID(

    orcurOrderDetails OUT rcurOrderDetails,

    iOrderID IN NUMBER) IS

  BEGIN

    OPEN orcurOrderDetails FOR

      SELECT OrderDetails.*, Products.ProductName

      FROM Products

      INNER JOIN OrderDetails

      ON Products.ProductID = OrderDetails.ProductID

      WHERE OrderDetails.OrderID = iOrderID

      ORDER BY OrderDetails.ProductID;

  END GetOrderDetailsByOrderID;

END OrderDetailPackage;

 

商品のパッケージ(ProductPackage)

CREATE OR REPLACE PACKAGE ProductPackage AS

  TYPE rcurProducts IS REF CURSOR;

  PROCEDURE GetProducts(

    orcurProducts OUT rcurProducts);

  PROCEDURE GetProductsTop10(

    orcurProducts OUT rcurProducts);

  PROCEDURE GetProductsByCategoryID(

    orcurProducts OUT rcurProducts,

    iCategoryID IN NUMBER);

  PROCEDURE UpdateProducts(

    iProductName IN VARCHAR2,

    iCategoryID IN NUMBER,

    iProductID IN NUMBER);

  PROCEDURE UpdateProductsRc(

    iProductName IN VARCHAR2,

    iCategoryID IN NUMBER,

    iProductID IN NUMBER,

    oRowCount OUT NUMBER);

  PROCEDURE UpdateProductsDiscontinued(

    iProductName IN VARCHAR2,

    iDisContinued IN NUMBER,

    iProductID IN NUMBER);

  PROCEDURE UpdateProductsDiscontinuedRc(

    iProductName IN VARCHAR2,

    iDisContinued IN NUMBER,

    iProductID IN NUMBER,

    oRowCount OUT NUMBER);

END ProductPackage;

 

CREATE OR REPLACE PACKAGE BODY ProductPackage AS

  PROCEDURE GetProducts(

    orcurProducts OUT rcurProducts) IS

  BEGIN

    OPEN orcurProducts FOR

      SELECT *

      FROM Products

      ORDER BY ProductID;

  END GetProducts;

  PROCEDURE GetProductsTop10(

    orcurProducts OUT rcurProducts) IS

  BEGIN

    OPEN orcurProducts FOR

      SELECT *

      FROM Products

      WHERE ProductID < 11

      ORDER BY ProductID;

  END GetProductsTop10;

  PROCEDURE GetProductsByCategoryID(

    orcurProducts OUT rcurProducts,

    iCategoryID IN NUMBER) IS

  BEGIN

    OPEN orcurProducts FOR

      SELECT *

      FROM Products

      WHERE CategoryID = iCategoryID

      ORDER BY ProductID;

  END GetProductsByCategoryID;

  PROCEDURE UpdateProducts(

    iProductName IN VARCHAR2,

    iCategoryID IN NUMBER,

    iProductID IN NUMBER) IS

  BEGIN

    UPDATE Products

    SET ProductName = iProductName,

        CategoryID = iCategoryID

    WHERE ProductID = iProductID;

  END UpdateProducts;

  PROCEDURE UpdateProductsRc(

    iProductName IN VARCHAR2,

    iCategoryID IN NUMBER,

    iProductID IN NUMBER,

    oRowCount OUT NUMBER) IS

  BEGIN

    UPDATE Products

    SET ProductName = iProductName,

        CategoryID = iCategoryID

    WHERE ProductID = iProductID;

    oRowCount := SQL%ROWCOUNT;

  END UpdateProductsRc;

  PROCEDURE UpdateProductsDiscontinued(

    iProductName IN VARCHAR2,

    iDisContinued IN NUMBER,

    iProductID IN NUMBER) IS

  BEGIN

    UPDATE Products

    SET ProductName = iProductName,

        DisContinued = iDisContinued

    WHERE ProductID = iProductID;

  END UpdateProductsDiscontinued;

  PROCEDURE UpdateProductsDiscontinuedRc(

    iProductName IN VARCHAR2,

    iDisContinued IN NUMBER,

    iProductID IN NUMBER,

    oRowCount OUT NUMBER) IS

  BEGIN

    UPDATE Products

    SET ProductName = iProductName,

        DisContinued = iDisContinued

    WHERE ProductID = iProductID;

    oRowCount := SQL%ROWCOUNT;

  END UpdateProductsDiscontinuedRc;

END ProductPackage;

 

商品区分のパッケージ(CategoryPackage)

CREATE OR REPLACE PACKAGE CategoryPackage AS

  TYPE rcurCategories IS REF CURSOR;

  PROCEDURE GetCategories(

    orcurCategories OUT rcurCategories);

END CategoryPackage;

 

CREATE OR REPLACE PACKAGE BODY CategoryPackage AS

  PROCEDURE GetCategories(

    orcurCategories OUT rcurCategories) IS

  BEGIN

    OPEN orcurCategories FOR

       SELECT * FROM Categories

       ORDER BY CategoryID;

  END GetCategories;

END CategoryPackage;

 

仕入先のパッケージ(SupplierPackage)

CREATE OR REPLACE PACKAGE SupplierPackage AS

  TYPE rcurSuppliers IS REF CURSOR;

  PROCEDURE GetSuppliers(

    orcurSuppliers OUT rcurSuppliers);

END SupplierPackage;

 

CREATE OR REPLACE PACKAGE BODY SupplierPackage AS

  PROCEDURE GetSuppliers(

    orcurSuppliers OUT rcurSuppliers) IS

  BEGIN

    OPEN orcurSuppliers FOR

      SELECT *

      FROM Suppliers

      ORDER BY SupplierID;

  END GetSuppliers;

END SupplierPackage;

 

 

社員のパッケージ(EmployeePackage)

CREATE OR REPLACE PACKAGE EmployeePackage AS

  TYPE rcurEmployees IS REF CURSOR;

  PROCEDURE GetEmployees(

    orcurEmployees OUT rcurEmployees);

END EmployeePackage;

 

CREATE OR REPLACE PACKAGE BODY EmployeePackage AS

  PROCEDURE GetEmployees(

    orcurEmployees OUT rcurEmployees) IS

  BEGIN

    OPEN orcurEmployees FOR

       SELECT * FROM Employees

       ORDER BY EmployeeID;

  END GetEmployees;

END EmployeePackage;

 

都道府県のパッケージ(RegionPackage)

CREATE OR REPLACE PACKAGE RegionPackage AS

  TYPE rcurRegion IS REF CURSOR;

  PROCEDURE GetRegions(

    orcurRegion OUT rcurRegion);

END RegionPackage;

 

CREATE OR REPLACE PACKAGE BODY RegionPackage AS

  PROCEDURE GetRegions(

    orcurRegion OUT rcurRegion) IS

  BEGIN

    OPEN orcurRegion FOR

      SELECT *

      FROM Region;

  END GetRegions;

END RegionPackage;