比如有如下函数,拼接字符串:
CREATE OR REPLACE Function translate_order_right_user_fun (
activity_instance_id_ in varchar2 default ''
) RETURN varchar2 IS
return_value varchar2(4000):='';
str_length number;
workitem_tmp wfs_workitem%ROWTYPE;
CURSOR mycur is select * from wfs_workitem w where w.activity_instance_id=activity_instance_id_ order by w.assit_order;
BEGIN
open mycur;
LOOP
FETCH mycur INTO workitem_tmp;
EXIT WHEN mycur%NOTFOUND;
return_value:=(return_value||('{WORKITEM_ID:"'||workitem_tmp.WORKITEM_ID|| '",USER_ID:"'||workitem_tmp.user_id ||'",FINISH_FLAG:"'||workitem_tmp.finish_flag|| '",ASSIT_ORDER:"'||workitem_tmp.assit_order||'"},'));
END LOOP;
CLOSE mycur;
SELECT length(return_value) into str_length FROM DUAL;
select substr(return_value,1,str_length-1) into return_value from dual;
RETURN return_value;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm);
END;
在数据量小的时候没有问题,但是如果拼接出来的字符串超过了varchar2存储的长度之后,函数就会报错,怎么办,其实在这我们可以用clob类型,直接使用!
CREATE OR REPLACE Function translate_order_right_user_fun (
activity_instance_id_ in varchar2 default ''
) RETURN clob IS
return_value clob:='';
str_length number;
workitem_tmp wfs_workitem%ROWTYPE;
CURSOR mycur is select * from wfs_workitem w where w.activity_instance_id=activity_instance_id_ order by w.assit_order;
BEGIN
open mycur;
LOOP
FETCH mycur INTO workitem_tmp;
EXIT WHEN mycur%NOTFOUND;
return_value:=(return_value||('{WORKITEM_ID:"'||workitem_tmp.WORKITEM_ID|| '",USER_ID:"'||workitem_tmp.user_id ||'",FINISH_FLAG:"'||workitem_tmp.finish_flag|| '",ASSIT_ORDER:"'||workitem_tmp.assit_order||'"},'));
END LOOP;
CLOSE mycur;
SELECT length(return_value) into str_length FROM DUAL;
select substr(return_value,1,str_length-1) into return_value from dual;
RETURN return_value;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm);
END;
执行发现没有任何问题了。