|
I need to convert the followinf function to SQL Anywhere. Here is the code: CREATE OR REPLACE FUNCTION f_get_name_address_list (p_name_and_address_id IN varchar2) RETURN SYS_REFCURSOR IS v_ret SYS_REFCURSOR; BEGIN OPEN v_ret FOR SELECT c.name_and_address_id, c.full_name, c.parent_record_id, LEVEL pass FROM s1_name_and_address c START WITH c.name_and_address_id = p_name_and_address_id CONNECT BY NOCYCLE PRIOR c.name_and_address_id = c.parent_record_id; RETURN v_ret; END; How do I handle "Start with" and "Connect by"? I am using SQL Anywhere 11.0.1.2755 |
|
I'm not really familiar with Oracle so I can just give two hints:
|
|
Regarding the return of a cursor, the last query executed will be returned automatically without defining a cursor. Example: CREATE OR REPLACE PROCEDURE get_name_address_list (IN p_name_and_address_id varchar) BEGIN SELECT c.name_and_address_id, c.full_name, c.parent_record_id, LEVEL pass FROM s1_name_and_address c END |
|
See the example below, I hope it will help. Note that Oracle also supports recursive subquery factoring starting from version 11R2. As far as I know, there is no direct equivalent of NOCYCLE clause in SQL Anywhere. The number of recursions is limited by max_recursive_iterations option. Contrary to Oracle, you don't need a refcursor to return a result set from a stored procedure, it occurs automatically. If you return a result set from a procedure then you can query it, i.e. put it into the FROM clause in a query, much like a regular table. create table s1_name_and_address ( name_and_address_id int, full_name varchar(50), parent_record_id int null ); insert into s1_name_and_address values (1, 'test_01', null); insert into s1_name_and_address values (2, 'test_02', null); insert into s1_name_and_address values (3, 'test_03', 2); insert into s1_name_and_address values (4, 'test_04', 2); insert into s1_name_and_address values (5, 'test_05', 3); insert into s1_name_and_address values (6, 'test_06', 2); insert into s1_name_and_address values (7, 'test_07', 1); insert into s1_name_and_address values (8, 'test_08', null); commit; select * from s1_name_and_address with recursive name_and_address ( name_and_address_id, full_name, nest_level ) as ( select name_and_address_id, full_name, 0 nest_level from s1_name_and_address where parent_record_id is null union all select a.name_and_address_id, a.full_name, b.nest_level + 1 from s1_name_and_address a inner join name_and_address b on a.parent_record_id = b.name_and_address_id and a.parent_record_id <> a.name_and_address_id and b.nest_level <= 20 ) select name_and_address_id, full_name, nest_level from name_and_address order by name_and_address_id; |
