ตอนที่ 10 : การใช้ Cursor บน Stored Procedure (Oracle : Stored Procedure)

CURSOR cursor_name
IS
  SELECT_statement;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
  CURSOR curCustomer IS 
    SELECT CUSTOMER_ID, NAME, EMAIL  FROM CUSTOMER;
BEGIN
  DECLARE
    sCustomerID VARCHAR2(4);
    sName VARCHAR(150);
    sEmail VARCHAR(150);
    BEGIN
      OPEN curCustomer;
      LOOP
            FETCH curCustomer INTO sCustomerID, sName, sEmail;
            EXIT WHEN curCustomer%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(sCustomerID || ' ' || sName || ' ' || sEmail);
      END LOOP;
      CLOSE curCustomer;
    END;
END;

EXEC MY_STORED_PROCEDURE();

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
  CURSOR curCustomer IS 
    SELECT CUSTOMER_ID, NAME, EMAIL  FROM CUSTOMER;
BEGIN
  FOR CusInfo IN curCustomer
  LOOP
      DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' || CusInfo.EMAIL );
  END LOOP;
END;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
  CURSOR curCustomer IS 
    SELECT CUSTOMER_ID, NAME, EMAIL  FROM CUSTOMER
      WHERE CUSTOMER_ID = 'C001';
BEGIN
  DECLARE
    sCustomerID VARCHAR2(4);
    sName VARCHAR(150);
    sEmail VARCHAR(150);
    BEGIN
      OPEN curCustomer;
        FETCH curCustomer INTO sCustomerID, sName, sEmail;
              DBMS_OUTPUT.PUT_LINE(sCustomerID || ' ' || sName || ' ' || sEmail);
      CLOSE curCustomer;
    END;
END;

EXEC MY_STORED_PROCEDURE();

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
  CURSOR curCustomer IS 
    SELECT CUSTOMER_ID, NAME, EMAIL  FROM CUSTOMER
      WHERE CUSTOMER_ID = 'C001';
BEGIN
  DECLARE
    BEGIN
      OPEN curCustomer;
        IF curCustomer%NOTFOUND THEN
           DBMS_OUTPUT.PUT_LINE('No Data.'); 
        ELSE
           DBMS_OUTPUT.PUT_LINE('Found Record'); 
        END IF;
      CLOSE curCustomer;
    END;
END;

EXEC MY_STORED_PROCEDURE();