m:m/m:1

CUSTOMER：顧客

CREATE TABLE POWERFUL.CUSTOMER
(
    CUSTOMER_NO                    NUMBER(5,0) NOT NULL,
    CUSTOMER_NAME                  VARCHAR2(50) NOT NULL,
    CONSTRAINT PK_CUSTOMER_NO PRIMARY KEY (CUSTOMER_NO)
)
/

order：受注

CREATE TABLE POWERFUL.ORDER_EVENT
(
    ORDER_NO                       NUMBER(10,0) NOT NULL,
    CUSTOMER_NO                    NUMBER(5,0) NOT NULL,
    MONEY                          NUMBER(12,0) NOT NULL,
    TIME_STAMP                     DATE NOT NULL,
    CONSTRAINT FK_CUSTOMER_NO FOREIGN KEY (CUSTOMER_NO) REFERENCES POWERFUL.CUSTOMER (CUSTOMER_NO),
    CONSTRAINT PK_ORDER_NO PRIMARY KEY (ORDER_NO)
)
/
CREATE INDEX POWERFUL.IDX_ORDER
ON POWERFUL.ORDER_EVENT
(TIME_STAMP, CUSTOMER_NO)
/

DEMAND：請求

CREATE TABLE POWERFUL.DEMAND
(
    DEMAND_NO                      NUMBER(10,0) NOT NULL,
    CUSTOMER_NO                    NUMBER(5,0) NOT NULL,
    TIME_STAM                      DATE NOT NULL,
    MONEY                          NUMBER(12,0) NOT NULL,
    CONSTRAINT FK_ORDER_CUST_NO FOREIGN KEY (CUSTOMER_NO) REFERENCES POWERFUL.CUSTOMER (CUSTOMER_NO),
    CONSTRAINT PK_DEMAND_NO PRIMARY KEY (DEMAND_NO)
)
/

ORDER_DEMAND:受注・請求対応表

CREATE TABLE POWERFUL.ORDER_DEMAND
(
    ORDER_NO                       NUMBER(10,0) NOT NULL,
    DEMAND                         NUMBER(10,0) NOT NULL,
    CONSTRAINT PK_ORDER_DEMAND PRIMARY KEY (ORDER_NO, DEMAND)
)
/

MAKE_DEMAND：請求作成

請求日に顧客へ請求する。

CREATE OR REPLACE PROCEDURE POWERFUL.PR_MAKE_DEMAND(D1 IN DATE)
IS

	CURSOR C1 IS
	SELECT 
		CUSTOMER_NO,
		SUM(MONEY) MONEY
	FROM 
		ORDER_EVENT 
	WHERE 
		TIME_STAMP = D1
	GROUP BY 
		CUSTOMER_NO;
	CUST_NO		ORDER_EVENT.CUSTOMER_NO%TYPE;


	CURSOR C2 IS
	SELECT
		ORDER_NO
	FROM
		ORDER_EVENT
	WHERE 	
		TIME_STAMP = D1
		AND CUSTOMER_NO = CUST_NO;

BEGIN
	FOR CUST_REC IN C1 LOOP
		INSERT INTO DEMAND
		VALUES(
			SEQ_DEMAND_NO.NEXTVAL,
			CUST_REC.CUSTOMER_NO,
			TO_CHAR(SYSDATE,'YYYY/MM/DD'),
			CUST_REC.MONEY);
		CUST_NO := CUST_REC.CUSTOMER_NO;
		FOR ORDER_REC IN C2 LOOP
			INSERT INTO ORDER_DEMAND
			VALUES(
			ORDER_REC.ORDER_NO,
			SEQ_DEMAND_NO.CURRVAL);
		END LOOP;
	END LOOP;
END;
/

VIEW

CREATE INDEX POWERFUL.IDX_ORDER_DEMAND_001
ON POWERFUL.ORDER_DEMAND
(ORDER_NO)
/
CREATE INDEX POWERFUL.IDX_ORDER_DEMAND_002
ON POWERFUL.ORDER_DEMAND
(DEMAND)
/
CREATE INDEX POWERFUL.IDX_DEMAND_CUST_NO
ON POWERFUL.DEMAND
(CUSTOMER_NO)
/
CREATE INDEX POWERFUL.IDX_ORDER_CUST_NO
ON POWERFUL.ORDER_EVENT
(CUSTOMER_NO)
/

VIEW：請求

CREATE OR REPLACE VIEW POWERFUL.V_DEMAND
    (DEMAND,TIME_STAM,MONEY,CUSTOMER_NO,CUSTOMER_NAME)
AS
SELECT 
	ORDER_DEMAND.DEMAND
	,DEMAND.TIME_STAM
	,DEMAND.MONEY
	,CUSTOMER.CUSTOMER_NO
	,CUSTOMER.CUSTOMER_NAME
FROM 
	ORDER_DEMAND
	,DEMAND
	,CUSTOMER
WHERE
	ORDER_DEMAND.DEMAND = DEMAND.DEMAND_NO
	AND DEMAND.CUSTOMER_NO = CUSTOMER.CUSTOMER_NO  
/

VIEW：受注

CREATE OR REPLACE VIEW POWERFUL.V_ORDER
    (ORDER_NO,TIME_STAMP,MONEY,CUSTOMER_NO,CUSTOMER_NAME)
AS
SELECT 
	ORDER_DEMAND.ORDER_NO
	,ORDER_EVENT.TIME_STAMP
	,ORDER_EVENT.MONEY
	,CUSTOMER.CUSTOMER_NO
	,CUSTOMER.CUSTOMER_NAME
FROM 
	ORDER_DEMAND
	,ORDER_EVENT
	,CUSTOMER
WHERE
	ORDER_DEMAND.ORDER_NO = ORDER_EVENT.ORDER_NO
	AND ORDER_EVENT.CUSTOMER_NO = CUSTOMER.CUSTOMER_NO  
/

SEQ_CUSTOMER_NO

CREATE SEQUENCE POWERFUL.SEQ_CUSTOMER_NO
INCREMENT BY 1
START WITH 51020
MAXVALUE 99999
NOMINVALUE
NOCYCLE 
CACHE 20
NOORDER 
/
CREATE SEQUENCE POWERFUL.SEQ_DEMAND_NO
INCREMENT BY 1
START WITH 2000000020
MAXVALUE 9999999999
NOMINVALUE
NOCYCLE 
CACHE 20
NOORDER 
/
CREATE SEQUENCE POWERFUL.SEQ_JUCHUU_NO
INCREMENT BY 1
START WITH 1000000000
MAXVALUE 9999999999
NOMINVALUE
NOCYCLE 
CACHE 20
NOORDER 
/
CREATE SEQUENCE POWERFUL.SEQ_ORDER_NO
INCREMENT BY 1
START WITH 1000000040
MAXVALUE 9999999999
NOMINVALUE
NOCYCLE 
CACHE 20
NOORDER 
/
CREATE SEQUENCE POWERFUL.SEQ_SEIKYUU_NO
INCREMENT BY 1
START WITH 1000000000
MAXVALUE 9999999999
NOMINVALUE
NOCYCLE 
CACHE 20
NOORDER 
/

other

BEGIN
 for i IN 1 ..1000 LOOP
 INSERT INTO CUSTOMER VALUES(SEQ_CUSTOMER_NO.NEXTVAL,'CUST0' || I);
 end loop;
END;
/