-- run 1_datatype_data.sql before running this script

ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation;

drop procedure while_proc; 
CREATE PROCEDURE while_proc LANGUAGE SQLSCRIPT AS 
    v_index1 INT := 0; 
    v_index2 INT := 0; 
    v_msg    VARCHAR(200) := ''; 
BEGIN 
    init_proc();

    WHILE :v_index1 < 5 DO 
        v_msg := 'Here is ' || :v_index1 || '.'; 
        ins_msg_proc(:v_msg); 
        v_index1 := :v_index1 + 1; 
    END WHILE;

    v_index1 := 0; 
    WHILE :v_index1 < 5 DO 
        v_index2 := 0; 
        WHILE :v_index2 < 5 DO 
            v_msg := 'Here is '|| :v_index1 || '-' || :v_index2 || '.'; 
            ins_msg_proc(:v_msg); 
            v_index2 := :v_index2 + 1; 
        END WHILE; 
        v_index1 := :v_index1 + 1; 
    END WHILE; 
END;

CALL while_proc(); 
SELECT message FROM message_box;

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

drop procedure upsert_proc; 
CREATE PROCEDURE upsert_proc (IN v_isbn VARCHAR(20)) LANGUAGE SQLSCRIPT 
AS 
    found INT := 1; 
BEGIN 
    init_proc(); 
    WHILE :found <> 0 DO 
        SELECT count(*) INTO found FROM books WHERE isbn = :v_isbn; 
        IF :found IS NULL THEN 
            ins_msg_proc('result of count(*) cannot be NULL'); 
        ELSE 
            ins_msg_proc('result of count(*) not NULL - as expected'); 
        END IF; 
            
        IF :found = 0 THEN 
            INSERT INTO books VALUES (:v_isbn, 'In-Memory Data Management', 1, 1, '2011', 42.75, 'EUR');    
        END IF; 
    END WHILE; 
END;

call upsert_proc('''978-3-642-19362-0'''); 
SELECT * FROM books; 
SELECT message FROM message_box;

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

drop procedure for_proc; 
CREATE PROCEDURE for_proc LANGUAGE SQLSCRIPT AS 
    v_index1 INT; 
    v_index2 DECIMAL(5,2); 
    v_msg    VARCHAR(200); 
BEGIN 
    init_proc();

    FOR v_index1 IN -2 .. 2 DO 
        FOR v_index2 IN REVERSE 0.5 .. 5.5 DO 
            v_msg := 'Here is '|| :v_index1 || '-' || :v_index2 || '.'; 
            ins_msg_proc(:v_msg); 
        END FOR; 
    END FOR; 
END;

CALL for_proc(); 
SELECT message FROM message_box;