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