-- 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;