Sqlforiin

『壹』 Postgres-存儲過程 return 詳解

https://www.cnblogs.com/Thenext/p/13531947.html
如果返回一個 數字或者字元 比較簡單，那麼多行多列怎麼辦呢，分為以下幾種情況【東西很多，這里只做簡單列舉】

返回多行單列

又分為幾種方式

1. return next，用在 for 循環中

( in_idinteger)RETURNSSETOFvarcharas $$DECLARE    v_name varchar;BEGINforv_namein( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) ) loop

    RETURNNEXT v_name;

  end loop;

  return;END;

$$

LANGUAGE PLPGsql;

注意

1. 循環外還有個 return

2. 需要實現聲明 v_name

2. return query，無需 for 循環

( in_idinteger)RETURNSSETOFvarcharas $$DECLARE    v_rec RECORD;BEGINreturnquery  ( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) );

  return;END;$$LANGUAGE PLPGSQL;

注意：如果 返回類型為 setof，最好用如下方法

RETURNQUERYEXECUTESQL

不要這么用

executesqlinto  out;returnout;

返回多行多列

也有多種方式

1. 使用 return next 和  setof record ，需要 for 循環

( in_idinteger)RETURNSSETOF RECORDas $$DECLARE    v_rec RECORD; BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop

    RETURNNEXT v_rec;

  end loop;

  return;END;

$$

LANGUAGE PLPGSQL;

注意

1. 讀取表的整行數據時才能用 record

2. 如果讀取的數據不是整行，需要自定義 復合數據類型，否則會報如下錯誤

ERROR:  acolumndefinition listisrequiredforfunctions returning "record"

定義復合類型 ，示例如下

createtype myout2as (

road_num int,

freq bigint);createorreplacefunctiontest(cartext, time1text, time2text)returnssetof myout2as $$declare

    array1 text[];

    array2 text[];

    len1 integer;

    len2 integer;

    x integer;

    y integer;

    road_str text;

    car_str text;

    sql text;

    i myout2;

    begin-- vin 號拼接selectregexp_split_to_array(car,',')into array2;

    selectarray_length(array2,1)into len2;

    car_str :='';

    y :=1;

    whiley<= len2 loop

    car_str :=car_str||quote_literal(array2[y])||',';

    y :=y+1;

    end loop;

    -- sql 拼接sql :='select road_number, sum(frequency) from heat_map where date_key >= '''|| time1

    ||'-01'' and date_key <='''|| time2

    ||'-20'' and vin in ('||rtrim(car_str,',')

    ||')group by road_number;';

    --execute sql into out;foriinexecute sql loop

    returnnext i;

    end loop;

    return;end$$ language plpgsql;

在執行時可能會報如下錯誤

ERROR:set-valuedfunctioncalledincontext that cannot accept aset

解決方法

select funcname(arg);--改為select*fromfuncname(arg);

2.  return query，無需 for 循環

( in_idinteger)RETURNSSETOF RECORDas $$DECLARE    v_rec RECORD;BEGINreturnquery  ( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) );

  return;END;

$$

LANGUAGE PLPGSQL;

3. 使用 out 輸出參數

( in_idinteger,out o_idinteger,out o_namevarchar)

RETURNSSETOF RECORDas $$DECLARE    v_rec RECORD;BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop

    o_id  := v_rec.id;

    o_name := v_rec.name;

    RETURNNEXT ;

  end loop;

  return;END;

$$

LANGUAGE PLPGSQL;

總結 - return next && return query 

我們可以看到上面無論是單列多行還是多列多行，都用到了 return next 和 return query 方法

在 plpgsql 中，如果存儲過程返回 setof sometype，則返回值必須在 return next 或者 return query 中聲明，然後有一個不帶參數的 retrun 命令，告訴函數執行完畢；【setof 就意味著 多行】

用法如下

RETURNNEXT expression;RETURN QUERY query;RETURNQUERYEXECUTEcommand-string[ USING expression [, ... ]];

return next 可以用於標量和復合類型數據；

return query 命令將查詢到的一條結果追加到函數的結果集中；

二者在單一集合返回函數中自由混合，在這種情況下，結果將被級聯。【有待研究】

return query execute 是 return query 的變形，它指定 sql 將被動態執行；

returnqueryselectroad_number,sum(frequency)fromheat_mapgroupbyroad_number;--這樣可以sql :='select road_number, sum(frequency) from heat_map group by road_number';returnquery sql;--這樣不行

參考資料：

https://blog.csdn.net/victor_ww/article/details/44415895postgresql自定義類型並返回數組

https://blog.csdn.net/weixin_42767321/article/details/92992935PG return next & return query

https://blog.csdn.net/luojin/article/details/45487373PostgreSQL function返回多列多行

https://www.cnblogs.com/xiongsd/archive/2013/06/05/3118704.html返回結果集多列和單列的例子  

https://www.cnblogs.com/lottu/p/7404722.html PostgreSQL存儲過程(1)-基於SQL的存儲過程

https://blog.csdn.net/pg_hgdb/article/details/79692749Postgresql動態SQL

https://stackoverflow.com/questions/40864464/postgresql-pgadmin-error-return-cannot-have-a-parameter-in-function-returning-s/40864898 postgresql, pgadmin error RETURN cannot have a parameter in function returning set

https://blog.csdn.net/qq_42535651/article/details/92089510postgresql存儲過程輸出參數

https://www.cnblogs.com/winkey4986/p/6437811.html

https://www.cnblogs.com/lottu/p/7405829.html PostgreSQL存儲過程(3)-流程式控制制語句

『貳』 如何給存儲過程，傳一個數組參數

這個是我自己寫的一個例子，你看看：在命令窗口執行以下語句，創建自定義類型NESTEDARRAY。;在存儲過程中使用自定義類型NESTEDARRAY。PROCEDUREGET_ARR_RESULT(INPUTARRAYINNESTEDARRAY,AROUTNESTEDARRAY)ISBEGINAR:=NESTEDARRAY();FORIIN1..INPUTARRAY.COUNTLOOPAR.EXTEND;AR(I):=I||INPUTARRAY(I);ENDLOOP;ENDGET_ARR_RESULT;java代碼：importjava.sql.Connection;importjava.sql.SQLException;importoracle.jdbc.OracleCallableStatement;importoracle.jdbc.OracleTypes;importoracle.sql.ARRAY;importoracle.sql.ArrayDescriptor;importoracle.sql.Datum;/***Java獲取Oracle存儲過程返回自定義類型*@authorluckystar**/{/***@paramargs*/publicstaticvoidmain(String[]args){Connectioncon=null;OracleCallableStatementocs=null;Stringsql="{calltest.GET_ARR_RESULT(?,?)}";try{con=DBUtil.dbUtil.getConnection();ocs=(OracleCallableStatement)con.prepareCall(sql);String[]params={「10001」,」10003」};ArrayDescriptorarrayDesc=ArrayDescriptor.createDescriptor("NESTEDARRAY",con);ARRAYinputArray=newARRAY(arrayDesc,con,params);ocs.setARRAY(1,inputArray);ocs.registerOutParameter(2,OracleTypes.ARRAY,"NESTEDARRAY");ocs.execute();ARRAYarray=ocs.getARRAY(2);Datum[]datum=array.getOracleArray();for(inti=0;i