存储过程 · TUNA-daily

[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); } } } ~~~