ตอนที่ 5 : รู้จัก Stored Procedure บน Oracle การรับส่ง Parameters (Oracle : Stored Procedure)

 CREATE TABLE "CUSTOMER" 
   (	"CUSTOMER_ID" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"NAME" VARCHAR2(50 BYTE), 
	"EMAIL" VARCHAR2(50 BYTE), 
	"COUNTRY_CODE" VARCHAR2(2 BYTE), 
	"BUDGET" NUMBER(18,2), 
	"USED" NUMBER(18,2), 
	 CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTOMER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM" ;

INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000.00, 600000.00);
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C002', 'John  Smith', '[email protected]', 'UK', 2000000.00, 800000.00);
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C003', 'Jame Born', '[email protected]', 'US', 3000000.00, 600000.00);
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C004', 'Chalee Angel', '[email protected]', 'US', 4000000.00, 100000.00);

CREATE TABLE "COUNTRY" 
   (	"COUNTRY_CODE" VARCHAR2(2 BYTE) NOT NULL ENABLE, 
	"COUNTRY_NAME" VARCHAR2(50 BYTE), 
	 CONSTRAINT "COUNTRY_PK" PRIMARY KEY ("COUNTRY_CODE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM" ;

INSERT INTO COUNTRY (COUNTRY_CODE, COUNTRY_NAME) VALUES ('TH', 'Thailand');
INSERT INTO COUNTRY (COUNTRY_CODE, COUNTRY_NAME) VALUES ('UK', 'United Kingdom');
INSERT INTO COUNTRY (COUNTRY_CODE, COUNTRY_NAME) VALUES ('US', 'United States');

CREATE TABLE "AUDIT_LOG" 
   (	"AUDIT_ID" NUMBER(*,0) NOT NULL ENABLE,  
    "CUSTOMER_ID" VARCHAR2(4 BYTE), 
    "LOG_DATE" DATE, 
     "USED" NUMBER(18,2),
	 CONSTRAINT "AUDIT_LOG_PK" PRIMARY KEY ("AUDIT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM" ;

INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('1', 'C001', to_date('01-AUG-15','DD-MON-RR'), '100000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('2', 'C001', to_date('05-AUG-15','DD-MON-RR'), '200000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('3', 'C001', to_date('10-AUG-15','DD-MON-RR'), '300000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('4', 'C002', to_date('02-AUG-15','DD-MON-RR'), '400000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('5', 'C002', to_date('07-AUG-15','DD-MON-RR'), '100000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('6', 'C002', to_date('15-AUG-15','DD-MON-RR'), '300000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('7', 'C003', to_date('20-AUG-15','DD-MON-RR'), '400000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('8', 'C003', to_date('25-AUG-15','DD-MON-RR'), '200000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('9', 'C004', to_date('04-AUG-15','DD-MON-RR'), '100000.00');

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World! ThaiCreate.Com');
END;

EXEC MY_STORED_PROCEDURE();

CREATE OR REPLACE PROCEDURE GET_CUSTOMER
AS
BEGIN
  FOR CusInfo IN (SELECT * FROM CUSTOMER)
  LOOP
      DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
        || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
  END LOOP;
END;

CREATE OR REPLACE PROCEDURE GET_CUSTOMER
AS
  CURSOR CusCuror IS SELECT * FROM CUSTOMER;
BEGIN
    FOR CusInfo IN CusCuror
    LOOP 
      DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
        || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
    END LOOP;
END;

EXEC GET_CUSTOMER();

CREATE OR REPLACE PROCEDURE GET_CUSTOMER(pCustomer OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN pCustomer FOR
       SELECT *
         FROM CUSTOMER;
END;

VAR  pCustomer REFCURSOR;
EXEC GET_CUSTOMER(:pCustomer)
PRINT pCustomer;

CREATE OR REPLACE PROCEDURE GET_CUSTOMER
  (pCountryCode IN VARCHAR2, pCustomer OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN pCustomer FOR
       SELECT *
         FROM CUSTOMER WHERE COUNTRY_CODE = pCountryCode;
END;

VAR  pCustomer REFCURSOR;
EXEC GET_CUSTOMER('US',:pCustomer)
PRINT pCustomer;