SAP HANA存储过程中的游标使用(Cursor)
-- run 1_datatype_data.sql before running this scriptALTER 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;
页:
[1]