ตอนที่ 7 : การสร้างตัวแปร Declare Variable บน Store Procedure (Oracle : Stored Procedure)

 DECLARE
 sOwner VARCHAR2(150) NOT NULL;

sOwner VARCHAR2(150) NOT NULL := 'Mr.Win';

sName  CUSTOMER.NAME%TYPE;

        DECLARE
          dNumber1 DECIMAL(18,2) := 1234.56;
          dNumber2 DECIMAL(18,2) := 0;
          dTotal DECIMAL(18,2);
        BEGIN
          dNumber2 := 35.5;
          dTotal := dNumber1 * dNumber2;
          DBMS_OUTPUT.PUT_LINE('dTotal = ' || dTotal);
        END; 

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
   DECLARE
    -- Constant
    cSiteName CONSTANT VARCHAR2(100) := 'www.ThaiCreate.Com';    
    
    -- String
    sOwner VARCHAR2(150) NOT NULL := 'Mr.Win';
    
    -- Number 
    nSiteAge NUMBER(3);
    
    -- Declare type from Table/Column
    sCustomerID CUSTOMER.CUSTOMER_ID%TYPE := 'C001';
    sName  CUSTOMER.NAME%TYPE;
    sCountyCode  CUSTOMER.COUNTRY_CODE%TYPE;
    
    BEGIN
      -- Assign variable
      sOwner := 'Mr.Weerachai Nukitram';
      nSiteAge := 12;
      
      -- Assign variable from select query
      SELECT NAME, COUNTRY_CODE INTO  sName, sCountyCode
      FROM CUSTOMER WHERE CUSTOMER_ID = sCustomerID;
      
      DBMS_OUTPUT.PUT_LINE('cSiteName = ' || cSiteName); 
      DBMS_OUTPUT.PUT_LINE('nSiteAge = ' || nSiteAge); 
      DBMS_OUTPUT.PUT_LINE('sOwner = ' || sOwner); 
      
      DBMS_OUTPUT.PUT_LINE('==========='); 
      
      DBMS_OUTPUT.PUT_LINE('sCustomerID = ' || sCustomerID); 
      DBMS_OUTPUT.PUT_LINE('sName = ' || sName); 
      DBMS_OUTPUT.PUT_LINE('sCountyCode = ' || sCountyCode); 
      
      DBMS_OUTPUT.PUT_LINE('==========='); 
        -- Scope & block variable
        DECLARE
          dNumber1 DECIMAL(18,2) := 1234.56;
          dNumber2 DECIMAL(18,2) := 0;
          dTotal DECIMAL(18,2);
        BEGIN
          dNumber2 := 35.5;
          dTotal := dNumber1 * dNumber2;
          DBMS_OUTPUT.PUT_LINE('dTotal = ' || dTotal);
        END; 
        -- Cannot call dNumber1 after end of block
        --DBMS_OUTPUT.PUT_LINE('dNumber1 = ' || dNumber1);    
        
    END;
END;