[TOC] ## 1. 基礎 百度百科是這么描述存儲過程的：存儲過程（Stored Procedure）是在大型數據庫系統中，一組為了完成特定功能的SQL語句集，存儲在數據庫中，首次編譯后再次調用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數（如果有）來執行它。它是數據庫中的一個重要對象，任何一個設計良好的數據庫應用程序都應該用到存儲過程。 PL/pgSQL是一個塊結構語言。函數定義的所有文本都必須是一個塊。一個塊用下面的方法定義： ~~~ CREATE [OR REPLACE] FUNCTION function_name() [RETURNS xx] AS $$ [ <<label>> ] [DECLARE declarations] BEGIN statements END [ label ]; ~~~ 1. 其中括號部分為可選部分 2. DECLARE用來聲明變量，聲明部分中的每一個declaration和每一條statement都由一個分號終止 3. 塊支持嵌套，嵌套時子塊的END后面必須跟一個分號，最外層的塊END后可不跟分號 4. BEGIN后面不必也不能跟分號 5. END后跟的label名必須和塊開始時的標簽名一致 6. 所有關鍵字都不區分大小寫。標識符被隱含地轉換成小寫字符，除非被雙引號包圍 7. 聲明的變量在當前塊及其子塊中有效，子塊開始前可聲明并覆蓋（只在子塊內覆蓋）外部塊的同名變量 8. 變量被子塊中聲明的變量覆蓋時，子塊可以通過外部塊的label訪問外部塊的變量 9. 返回值可以為空：void 參數聲明 ### 1.1 僅類型列表 func(type1,type2) 只聲明參數的類型，在sql中要以$1,$2...$n順序引用參數，為了引用更直觀，可以給位置參數取別名 ~~~ CREATE OR REPLACE FUNCTION discount(NUMERIC) RETURNS NUMERIC AS $$ DECLARE total ALIAS FOR $1; BEGIN RETURN total * 0.8; END; $$ LANGUAGE PLPGSQL; ~~~ ### 1.2 顯式聲明參數名類型 func(name1 type1,name2 type2） ~~~ CREATE OR REPLACE FUNCTION discount( count NUMERIC) RETURNS NUMERIC AS $$ BEGIN RETURN count * 0.8; END; $$ LANGUAGE PLPGSQL; ~~~ ### 1.3 EXECUTE語句執行動態命令 ~~~ CREATE OR REPLACE FUNCTION execute(filter_text INT) RETURNS TABLE(col1 INTEGER, col2 TEXT, col3 timestamp) # 返回值類型、數量要和表對應 AS $$ BEGIN RAISE NOTICE '$1 is %,filter is %', $1,filter_text; RETURN QUERY EXECUTE 'SELECT * FROM tbl_test where id = $1' USING filter_text; END; $$ LANGUAGE PLPGSQL; ~~~ ### 1.4 java 調用存儲過程 ~~~ package com.aixin.tuna.postgresqltest; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.alibaba.fastjson.TypeReference; import netscape.javascript.JSObject; import java.math.BigDecimal; import java.security.spec.PSSParameterSpec; import java.sql.*; import java.util.Iterator; import java.util.List; import java.util.Map; /** * Created by dailin on 2018/7/10. */ public class RunFunction { public static void main(String[] args) throws Exception { RunFunction testFunction = new RunFunction(); try { String parames = "{\"param\":[{\"in\":6},{\"out\":\"int\"},{\"out\":\"string\"}]}"; testFunction.executeQuery("sales_tax3",parames); } catch (Exception e) { e.printStackTrace(); } } /** * 調用存儲過程 * * @param function 函數名 * @return * @throws Exception */ public void executeQuery(String function,String parames) throws Exception { String url = "jdbc:postgresql://192.168.56.10:5432/postgres"; String user = "postgres"; String password = "tuna"; Class.forName("org.postgresql.Driver"); Connection connection = DriverManager.getConnection(url, user, password); Map<String, Object> paramsListMap = JSON.parseObject(parames, new TypeReference<Map<String, Object>>() {}); List<JSONObject> paramsList = (List<JSONObject>) paramsListMap.get("param"); String sql = "{CALL " + function + "("; String exesql = null; if (paramsList.size() != 0) { for (int i = 0; i < paramsList.size(); i++) { sql += "?,"; } exesql = sql.substring(0, sql.length() - 1); exesql += ")}"; } else { exesql = sql + ")}"; } CallableStatement callableStatement = connection.prepareCall(exesql); for (int i = 1; i <= paramsList.size(); i++) { //遍歷設置sql參數 JSONObject currentParam = paramsList.get(i - 1); if(currentParam.get("in") != null) { Object in = currentParam.get("in"); testType(callableStatement,i,in); } else { String outValue = currentParam.getString("out"); setOutType(callableStatement,i,outValue); } } callableStatement.execute(); int anInt = callableStatement.getInt(2); String anInt1 = callableStatement.getString(3); System.out.println("get value:" + anInt); System.out.println("get value:" + anInt1); } public void testType(CallableStatement call,int order,Object in) throws SQLException { if (in instanceof Integer) { call.setInt(order, ((Integer) in)); } else if (in instanceof String) { call.setString(order, ((String) in)); } else if (in instanceof Boolean) { call.setBoolean(order, ((Boolean) in)); } else if (in instanceof Double) { call.setDouble(order, ((Double) in)); } } public void setOutType(CallableStatement call,int order,String target) throws SQLException { if (target.equals("string")) { call.registerOutParameter(order,Types.VARCHAR); } else if (target.equals("int")) { call.registerOutParameter(order,Types.INTEGER); } else if (target.equals("date")) { call.registerOutParameter(order,Types.DATE); } } } ~~~