SAP HANA存储过程中的游标使用(Cursor) 沧海

-- run 1_datatype_data.sql before running this script

ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation;

drop procedure cursor_proc;
CREATE PROCEDURE cursor_proc LANGUAGE SQLSCRIPT AS
    v_isbn    VARCHAR(20);
    v_title   VARCHAR(50) := '';
    v_price   decimal(5,2) := 0;
    v_crcy    VARCHAR(3) := 'XXX';
    v_msg     VARCHAR(200);
    CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR
        SELECT isbn, title, price, crcy FROM books
        WHERE isbn = :v_isbn ORDER BY isbn;
BEGIN
    init_proc();

    IF c_cursor1%ISOPEN    THEN
        ins_msg_proc('WRONG: cursor open');
    ELSE
        ins_msg_proc('OK: cursor not open');   
    END IF;
   
    OPEN c_cursor1('978-3-86894-012-1');

    IF c_cursor1%ISOPEN    THEN
        ins_msg_proc('OK: cursor open');
    ELSE
        ins_msg_proc('WRONG: cursor not open');   
    END IF;

    FETCH c_cursor1 INTO v_isbn, v_title, v_price, v_crcy;
    IF c_cursor1%FOUND THEN
        ins_msg_proc('OK: cursor contains valid data');
    ELSE
        ins_msg_proc('WRONG: cursor contains no valid data');
    END IF;

    ins_msg_proc(:v_title || ' identified by isbn ' || :v_isbn || ' costs ' || :v_price || ' ' || :v_crcy);

    FETCH c_cursor1 INTO v_isbn, v_title, v_price, v_crcy;
    IF c_cursor1%NOTFOUND
    THEN
        ins_msg_proc('OK: cursor contains valid data');
    ELSE
        ins_msg_proc('WRONG: cursor contains no valid data');
         ins_msg_proc(:v_title || ' identified by isbn ' || :v_isbn || ' costs ' || :v_price || ' ' || :v_crcy);
    END IF;

    IF c_cursor1%ISOPEN
    THEN
        ins_msg_proc('OK: cursor open');
    ELSE
        ins_msg_proc('WRONG: cursor not open');   
    END IF;
    CLOSE c_cursor1;
    IF c_cursor1%ISOPEN
    THEN
        ins_msg_proc('WRONG: cursor open');
    ELSE
        ins_msg_proc('OK: cursor not open');   
    END IF;
END;

call cursor_proc();
select message from message_box;

-------------------------

DROP PROCEDURE foreach_proc;
CREATE PROCEDURE foreach_proc() LANGUAGE SQLSCRIPT AS
    v_isbn    VARCHAR(20) := '';
    CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR
       SELECT isbn, title, price, crcy FROM books
       ORDER BY isbn;
-- (see Bug 9197)  WHERE isbn = :v_isbn
BEGIN
    init_proc();
    FOR cur_row as c_cursor1('978-3-86894-012-1') DO
        ins_msg_proc('book title is: ' || cur_row.title);
    END FOR;
END;

call foreach_proc();
select message from message_box;