SAP HANA 存储过程中的IF While使用案例

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