如何接收json函数返回值 | 达梦技术社区

a varchar2(100):=''; type strArray is table of varchar2(4020); l_rowid_array strarray:=strarray(); l_data_array strarray:=strarray(); begin select to_char(json_value(VALUE, '$.data')) into l_data_array from jsonb_array_elements(json_query('{"code":200,"enkey":"5tAWmQk6aMCaeq6YG7TjnA==","data":[{"id":"AAAT9dAAMAAAdSDAAA","data":"Az2C4qgm-Bj8zsPOCCmawQ=="},{"id":"AAAT9dAAMAAAdSDAAB","data":"iljhbiiDzBJVyLw0oMhN2g=="},{"id":"AAAT9dAAMAAAdSEAAA","data":"eYPODQmm-NAk1ZEbzsni0A=="}],"flag":"ENCRYPT","id":"","sessionId":"18c4caf4","err":""}','$.data' returning varchar2)); end;

改成如下格式，，使用游标的方式是可以

	 a varchar2(100):='';
	 type strArray is table of varchar2(4020);
	 l_rowid_array strarray:=strarray();
	  l_data_array strarray:=strarray();
	  TYPE MYCUR IS REF CURSOR;
	  ret_cur MYCUR;
	  l_sql text:='select json_value(VALUE, ''$.id'') as id,json_value(VALUE, ''$.data'') as data
  					from jsonb_array_elements(json_query(''{"data":[{"id":"AAAT9dAAMAAAdSDAAA","data":"Az2C4qgm-Bj8zsPOCCmawQ=="},{"id":"AAAT9dAAMAAAdSEAAA","data":"eYPODQmm-NAk1ZEbzsni0A=="}]}'',''$.data'' returning varchar2))';
 begin
  	OPEN ret_cur FOR l_sql;
    FETCH ret_cur BULK COLLECT INTO l_rowid_array,l_data_array;
    CLOSE ret_cur;
    for i in l_data_array.first..l_data_array.last loop
    	print l_data_array(i);
    end loop;
 end;