Sybase Adaptive Server Anywhere - Page 509

Chapter 9 SQL Statements
491
create function get_row_count(in qry long varchar)
returns int
begin
declare crsr cursor using qry;
declare rowcnt int;
set rowcnt = 0;
open crsr;
lp: loop
fetch crsr;
if SQLCODE <> 0 then leave lp end if;
set rowcnt = rowcnt + 1;
end loop;
return rowcnt;
end
♦ Nested inside another BEGIN..END after the variable has been assigned
a value. For example:
create procedure get_table_name(
in id_value int, out tabname char(128)
)
begin
declare qry long varchar;
set qry = ’select table_name from SYS.SYSTABLE ’
||
’where table_id=’ || string(id_value);
begin
declare crsr cursor using qry;
open crsr;
fetch crsr into tabname;
close crsr;
end
end
♦ SQL/92 Entry level feature.
♦
Sybase Supported by Open Client/Open Server.
♦ The following example illustrates how to declare a scroll cursor in
Embedded SQL:
EXEC SQL DECLARE cur_employee SCROLL CURSOR
FOR SELECT * FROM employee;
♦ The following example illustrates how to declare a cursor for a prepared
statement in Embedded SQL:
EXEC SQL PREPARE employee_statement
FROM ’SELECT emp_lname FROM employee’;
EXEC SQL DECLARE cur_employee CURSOR
FOR employee_statement;
Standards and
compatibility
Examples