PRODUCTSBYCUSTOMER


<DEFAULT SERVER>   >   <DEFAULT DATABASE>   >   <DEFAULT SCHEMA>   >   PRODUCTSBYCUSTOMER   

Procedure body PRODUCTSBYCUSTOMER(CUSTOMER NUMBER,P_REF SYS_REFCURSOR)

Name Datatype Precision Description
CUSTOMER NUMBER NUMBER
P_REF SYS_REFCURSOR SYS_REFCURSOR
Name Types Value
No Data
Name Definition
No Data

CREATE OR REPLACE Procedure PRODUCTSBYCUSTOMER( customer IN NUMBER,p_ref  OUT SYS_REFCURSOR  )

IS

BEGIN

   OPEN p_ref FOR
      SELECT product.*
    FROM SALES_ORDER_ITEMS
    join customer c on customerId = c.id
    join SALES_ORDER so on so.Id = SALES_ORDER_ID
    join product p on PROD_ID = p.id
    
    
    WHERE c.customerId = customer;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END

     
Name Types Description
CUSTOMER Table Table
DERIVEDTABLE Table Table
<DEFAULT SCHEMA>.PRODUCT Table Table
<DEFAULT SCHEMA>.SALES_ORDER Table Table
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS Table Table
DBMS_STANDARD.RAISE_APPLICATION_ERROR Function PL/SQL System Function
CUSTOMERID Column Virtual column
ID Column Virtual column
NUMBER PL/SQL System type PL/SQL System Object
SYS_REFCURSOR PL/SQL System type PL/SQL System Object
CUSTOMERID Undefined Item
OTHERS Undefined Item
SQLCODE Undefined Item
SQLERRM Undefined Item
<DEFAULT SCHEMA>.SALES_ORDER.ID Primary Key Column
<DEFAULT SCHEMA>.PRODUCT.ID Primary Key Column
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS.PROD_ID Foreign Key Column
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS.SALES_ORDER_ID Foreign Key Column

     
Name Types Description
w_resp_customer_search.cb_products.clicked Event event clicked()