Area Chart Stacked In Oracle APEX

Опубликовано: 28 Сентябрь 2024
на канале: Tech Mining

455

In this tutorial series, we will be looking at different types of charts in Oracle APEX, starting with the Area Chart Stacked.

[EBA_DEMO_CHART_ORDERS] CREATE TABLE
========================================================
CREATE TABLE "EBA_DEMO_CHART_ORDERS"
( "ORDER_ID" NUMBER NOT NULL ENABLE,
"PRODUCT_ID" NUMBER,
"QUANTITY" NUMBER(8,0),
"CUSTOMER" VARCHAR2(30),
"SALES_DATE" DATE,
"CREATED" TIMESTAMP (6) WITH TIME ZONE,
"CREATED_BY" VARCHAR2(255),
"UPDATED" TIMESTAMP (6) WITH TIME ZONE,
"UPDATED_BY" VARCHAR2(255),
CONSTRAINT "EBA_DEMO_CHART_ORDER_PK" PRIMARY KEY ("ORDER_ID")
USING INDEX ENABLE
) ;

ALTER TABLE "EBA_DEMO_CHART_ORDERS" ADD CONSTRAINT "EBA_DEMO_CHART_ORDER_FK" FOREIGN KEY ("PRODUCT_ID")
REFERENCES "EBA_DEMO_CHART_PRODUCTS" ("PRODUCT_ID") ON DELETE CASCADE ENABLE;

CREATE INDEX "EBA_DEMO_CHART_ORD_1" ON "EBA_DEMO_CHART_ORDERS" ("QUANTITY")
;

CREATE INDEX "EBA_DEMO_CHART_ORD_2" ON "EBA_DEMO_CHART_ORDERS" ("PRODUCT_ID")
;

CREATE OR REPLACE EDITIONABLE TRIGGER "BIU_EBA_DEMO_CHART_ORDERS"
before insert or update on eba_demo_chart_orders
for each row
begin
if :new."ORDER_ID" is null then
select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.order_id from dual;
end if;
if inserting then
:new.created := current_timestamp;
:new.created_by := nvl(wwv_flow.g_user,user);
:new.updated := current_timestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
end if;
if inserting or updating then
:new.updated := current_timestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
end if;
end;
/

ALTER TRIGGER "BIU_EBA_DEMO_CHART_ORDERS" ENABLE;

[EBA_DEMO_CHART_PRODUCTS] CREATE TABLE
========================================================
CREATE TABLE "EBA_DEMO_CHART_PRODUCTS"
( "PRODUCT_ID" NUMBER NOT NULL ENABLE,
"PRODUCT_NAME" VARCHAR2(50),
"PRODUCT_DESCRIPTION" VARCHAR2(2000),
"LIST_PRICE" NUMBER(8,2),
"CREATED" TIMESTAMP (6) WITH TIME ZONE,
"CREATED_BY" VARCHAR2(255),
"UPDATED" TIMESTAMP (6) WITH TIME ZONE,
"UPDATED_BY" VARCHAR2(255),
CONSTRAINT "EBA_DEMO_CHART_PROD_PK" PRIMARY KEY ("PRODUCT_ID")
USING INDEX ENABLE
) ;

CREATE INDEX "EBA_DEMO_CHART_PROD_1" ON "EBA_DEMO_CHART_PRODUCTS" ("LIST_PRICE")
;

CREATE OR REPLACE EDITIONABLE TRIGGER "BIU_EBA_DEMO_CHART_PRODUCTS"
before insert or update on eba_demo_chart_products
for each row
begin
if :new."PRODUCT_ID" is null then
select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.product_id from dual;
end if;
if inserting then
:new.created := current_timestamp;
:new.created_by := nvl(wwv_flow.g_user,user);
:new.updated := current_timestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
end if;
if inserting or updating then
:new.updated := current_timestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
end if;
end;

/
ALTER TRIGGER "BIU_EBA_DEMO_CHART_PRODUCTS" ENABLE;

[Sample Data] EBA_DEMO_CHART_ORDERS
=============================================
ORDER_ID PRODUCT_ID QUANTITY CUSTOMER SALES_DATE
1 1 42 Store A 04/08/2025
2 2 55 Store A 04/09/2025
3 3 36 Store A 04/11/2025
4 4 22 Store A 04/12/2025
5 5 42 Store A 04/14/2025
6 1 32 Acme Store 04/03/2025
7 2 39 Acme Store 04/04/2025
8 3 36 Acme Store 04/07/2025
9 4 27 Acme Store 04/13/2025
10 5 50 Acme Store 04/14/2025
11 1 34 Shop C 04/08/2025
12 2 30 Shop C 04/09/2025
13 3 50 Shop C 04/11/2025
14 4 46 Shop C 04/12/2025
15 5 36 Shop C 04/14/2025
16 1 74 Deli 04/02/2025
17 2 42 Deli 04/04/2025
18 3 70 Deli 04/06/2025
19 4 46 Deli 04/08/2025
20 5 22 Deli 04/10/2025

[Sample Data] EBA_DEMO_CHART_PRODUCTS
=============================================
PRODUCT_ID PRODUCT_NAME PRODUCT_DESCRIPTION LIST_PRICE
1 Apples Red pink lady apples 1.2
2 Bananas Bunches of yellow bananas 3.8
3 Cantaloupe Coral colored melon 2.95
4 Dates Dried dates 3.3
5 Grapes Punnet of Red Seedless Grapes 2.05

Series SQL Query
=============================================
select a.product_name, b.quantity, b.customer, a.product_description
from eba_demo_chart_products a, eba_demo_chart_orders b
where a.product_id = b.product_id
and b.customer = 'Deli'

Acme Store SQL Query
======================================
select a.product_name, b.quantity, b.customer,a.product_name || ' ' || a.product_description || ' br/ X: ' ||
a.list_price || ' br/ Y: ' || a.product_id "shortDesc", a.product_description
from eba_demo_chart_products a, eba_demo_chart_orders b
where a.product_id = b.product_id
and b.customer = 'Acme Store'

Dynamic Actions Code
===============================================
apex.region("area1").widget().ojChart({stack: 'on'});