Java+SQL+MySQL+Hive存储过程汇总

不念不忘少年蓝@ 2023-02-13 05:42 35阅读 0赞

# Java+SQL+MySQL+Hive存储过程汇总 #

### 存储过程概念 ###

在我们做一个复杂的项目时，会多次涉及到与数据库的连接，这时我们会一次次的来用SQL语句进行对数据库的连接，但如果我们这时用存储过程的话就只需要连接一次数据库就可以了，从而省去大量的SQL语句。

### 存储过程的优点 ###

1、加快运行速度

对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对齐进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通快。(就相当于操作系统中的TLB快表)

2、减少网络传输

存储过程直接就再数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其他服务器，所以会减少一定的网络传输，但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的，而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。

3、提高可维护性

存储过程有时候比程序更容易维护，这时因为可以实时更新DB端的存储过程，有些bug，直接改存储过程里的业务逻辑就搞定了。

4、增强安全性

提高代码的安全，防止SQL注入，这一点sql语句也可以做到。

5、增加可扩展性

应用程序和数据库操作分开，独立进行，而不是相互在一起，方便以后的扩展和DBA维护优化。

### 存储过程的缺点 ###

1、数据更改困难

如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数等等 ;  
2、可移植性差

由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

## 一、JAVA-存储过程调用 ##

### 1、基本调用 ###

public static void main(String[] args ){ 
        String driver = "oracle.jdbc.driver.OracleDriver";
        String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        try { 
          Class.forName(driver);
          conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
          CallableStatement proc = null;
          proc = conn.prepareCall("{ call hyq.testc(?) }");
          proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
          proc.execute();
          rs = (ResultSet)proc.getObject(1);
          while(rs.next())
    
    //调用存储过程 删除流程相关记录 
                String procdure = "{Call sp_deleteInstByRootID(?)}";  
                CallableStatement cs = this.getHibernateTemplate().getSessionFactory().getCurrentSession().connection().prepareCall(procdure);  
                //this.getSession().connection().prepareCall(procdure).setString(0, orgdefid); 
                cs.setString(1, procinstid);  
                cs.execute();  
       
    
    ```````````````````````````````````````````````````````````````````````````````````````
    ibatis配置文件：
    <parameterMap id="parameterMapCesu" class="java.util.Map"> 
    
         
    
        <parameter property="cesuDate" jdbcType="VARCHAR2"  javaType="java.lang.String" mode="IN" /> 
    
        <parameter property="csCount" jdbcType="DOUBLE" javaType="java.lang.Long" mode="OUT"/>
    
    </parameterMap> 
    
     
    
    <procedure id="cesu" parameterMap="parameterMapCesu">  
    
        { call handiwork(?,?)}          
    
    </procedure>
    
    
    ```````````````````````````````````````````````````````````````````````````````````````
    Java代码:
    public Long doTest(final String cesuDate) { 
        return (Long) this.getSqlMapClientTemplate().execute(new SqlMapClientCallback() { 
            @SuppressWarnings("unchecked")
            public Object doInSqlMapClient(SqlMapExecutor executor)
                    throws SQLException { 
                long csCount = 0;
                try {    
                    //设置存储过程参数
                    Map cesu = new HashMap();
                    cesu.put("cesuDate", cesuDate);//输入参数
                    cesu.put("csCount", 0);//输出参数
                    //调用存储过程
                    executor.queryForObject("test.cesu",cesu);//ibatis文件的namespace是test
                    csCount = (Long)cesu.get("csCount");//获取返回值 
                    return csCount;
                } catch (Exception e) {    
                    e.printStackTrace();   
                    return null;
                } 
            }
        });
    }

### 2、框架调用 ###

#### a）Hibernate ####

hibernate中调用存储过程:
    /** * 直接调用存储过程 * @param procString * @author kongqz * @throws Exception * @date 2009-03-03 * **/  
    public void callProcedure(String procString,List<Object> params) throws Exception {   
        CallableStatement stmt = null;  
        try {   
            stmt = this.getSession().connection().prepareCall(procString);  
            if (params != null){   
                int idx = 1;  
                for (Object obj : params) {   
                    if (obj != null) {   
                        stmt.setObject(idx, obj);  
                    } else {   
                        stmt.setNull(idx, Types.NULL);  
                    }  
                    idx++;  
                }  
            }  
            stmt.execute();  
        } catch (SQLException e) {   
            e.printStackTrace();  
            throw new Exception("调用存储过程的时候发生错误[sql = " + procString + "]", e);  
              
        }  
    }  
    
    或
    
    tx = session.beginTransaction();
    Connection con=session.connection();
    
    String procedure = "{call batchUpdateStudent(?) }";
    CallableStatement cstmt = con.prepareCall(procedure);
    cstmt.setInt(1,0); //把年龄参数设为0
    cstmt.executeUpdate();
    tx.commit();

#### b）Spring ####

spring调用存储过程:
    1.继承StoredProcedure
    
    org.springframework.jdbc.object.StoredProcedure是对应存储过程调用的操作对象，它通过其父类
    
    org.springframework.jdbc.object.SqlCall获得相应的底层API支持(CallableStatementCreator)， 然
    
    后在此基础之上构建了调用存储过程的执行方法。
    
    2、重写父类的execute（）方法。将存储过程的参数封装成Map类型的传入该方法
    
    
    3、写一个方法来封装存储过程的方法及把参数放到Map里面.
    
    如：
      Map paraMap = new HashMap();
      paraMap.put(IN_PARAMETER_NAME, tableName);
      paraMap.put(INOUT_PARAMETER_NAME, v);
    注意：key值一定要与前面构造函数里面声明的参数一致。
    
    
    4、execute（）返回的map值要取到里面的value值，可以用前面构造函数声明时候用到的key值去取。
    　　如：(String)resultMap.get(OUT_PARAMETER_NAME);就得到了存储过程的返回值。
    
    示例如下：
    
    public class xxxxProcedure extends StoredProcedure { 
        private static final String PROCEDURE_NAME = "xxxx";
    
        protected xxxxProcedure() { 
            /* empty */
            }
        
        public xxxxProcedure(JdbcTemplate jdbcTemplate) { 
            super(jdbcTemplate, PRO_NAME);
            declareParameter(new SqlParameter("xxxx", Types.VARCHAR));
            declareParameter(new SqlParameter("xxxx", Types.VARCHAR));
            declareParameter(new SqlParameter("xxxx", Types.NUMERIC));
        }
    
        public void execute(String xxxx,String xxxx,int xxxx) { 
            Map<String, Object> paramsIn = new HashMap<String, Object>();
            paramsIn.put("xxxx", xxxx);
            paramsIn.put("xxxx", xxxx);
            paramsIn.put("xxxx", xxxx);
            super.execute(paramsIn);
        }
    
    }

#### c）Mybatis ####

在mybatis中调用存储过程，然后获取该结果集：
    1、xml配置文件
    
    <?xml version="1.0" encoding="UTF-8" ?>  
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.ifeng.iis.bean.iis.Report" >
     <resultMap type="java.util.HashMap" id="resultMap">
          <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
          <result column="name" property="name" javaType="java.lang.String" jdbcType="VARCHAR"/>
          <result column="repDate" property="repDate" javaType="java.lang.String" jdbcType="VARCHAR"/>
          <result column="summ" property="summ" javaType="java.lang.Long" jdbcType="BIGINT"/>
        </resultMap>
        
     <select id="test123" parameterType="java.util.Map"  resultMap="resultMap" statementType="CALLABLE" >  
          { call pro_sql_data(
          #{ obj,jdbcType=VARCHAR,mode=IN}
        )
          }
        </select>  
    </mapper>
    
    Java代码:
    public String query(String param) throws Exception { 
      logger.info(param);
      Map queryMap = new HashMap();
      queryMap.put("obj", param);
      //List<Map> listIis1 = reportDao.select4MapParam(queryMap, "currentSql");
      
      List<Map> listIis2 =reportDao.select4MapParam(queryMap,"test123");
      
      return JSONArray.fromObject(listIis2).toString();
     }

### 3、Oracle实例 ###

四步：
    1、创建一个JDBC数据库连接工具类
    2、调用存储过程
    3、调用存储方法
    4、调用存储过程，一个输入参数，返回一个查询结果集合

1、创建一个JDBC数据库连接工具类

package com.test.db;
     
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
     
    public class JDBCUtils { 
     
    	private static String driver = "oracle.jdbc.OracleDriver";
    	private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    	private static String user = "数据库连接名";
    	private static String password = "数据库连接密码";
    	
    	//注册数据库驱动
    	static{ 
    		try { 
    			Class.forName(driver);
    		} catch (Exception e) { 
    			throw new ExceptionInInitializerError(e);
    		}
    	}
    	
    	/** * 获取数据库连接 * @return */
    	public static Connection getConnection(){ 
    		try { 
    			return DriverManager.getConnection(url,user,password);
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}
    		return null;
    	}
    	
    	/** * 释放数据库连接资源 * @param conn * @param st * @param rs */
    	public static void release(Connection conn,Statement st,ResultSet rs){ 
    		if (rs!=null) { 
    			try { 
    				rs.close();
    			} catch (SQLException e) { 
    				e.printStackTrace();
    			}finally{ 
    				rs = null;
    			}
    		}
    		
    		if (st!=null) { 
    			try { 
    				st.close();
    			} catch (SQLException e) { 
    				e.printStackTrace();
    			}finally{ 
    				st = null;
    			}
    		}
    		
    		if (conn!=null) { 
    			try { 
    				conn.close();
    			} catch (SQLException e) { 
    				e.printStackTrace();
    			}finally{ 
    				conn = null;
    			}
    		}
    	}
    }

2、调用存储过程

package com.test.demo;
     
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import org.junit.Test;
    import oracle.jdbc.internal.OracleTypes;
    import oracle.jdbc.oracore.OracleType;
    import com.hwb.db.JDBCUtils;
     
    /** * 调用存储过程 一个输入参数，多个 输出参数 * @author Administrator * */
    public class ProcedureTest { 
     
    // create or replace procedure selectUserById(uid in number , uName out VARCHAR2 , uAge out number , uSex out char)
     
    	@Test
    	public void testProcedure(){ 
    		
    		String sql = "{call selectUserById(?,?,?,?)}";
    		
    		Connection conn = null;
    		CallableStatement call = null;
    		try { 
    			//得到一个数据库连接
    			conn = JDBCUtils.getConnection();
    			//通过连接创建出statement
    			call = conn.prepareCall(sql);
    			//对于in参数，赋值
    			call.setInt(1, 2);  // (第几个问号,要赋的值)
    			//对out参数，声明
    			call.registerOutParameter(2, OracleTypes.VARCHAR);  //(第几个问号，声明的类型)
    			call.registerOutParameter(3, OracleTypes.NUMBER);
    			call.registerOutParameter(4, OracleTypes.CHAR);
    			
    			//执行调用
    			call.execute();
    			
    			//取出结果
    			String userName = call.getString(2);
    			int userAge = call.getInt(3);
    			String userSex = call.getString(4);
    			
    			System.out.println("用户姓名："+userName+"\n\t年龄:"+userAge+"\n\t性别："+userSex);
    			
    		} catch (Exception e) { 
    			e.printStackTrace();
    		}finally{ 
                        //关闭连接，释放资源
                       JDBCUtils.release(conn, call, null);
    		}
    		
    	}
    }

3、调用存储方法

package com.test.demo;
     
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import oracle.jdbc.internal.OracleTypes;
    import org.junit.Test;
    import com.hwb.db.JDBCUtils;
     
    /** * 调用存储函数，一个输入参数，一个输出参数 * @author Administrator * */
    public class FunctionTest { 
     
    	/** * create or replace function selectAge(eno in number) return number */
    	@Test
    	public void testFunction(){ 
    		//{？= call <procedure-name>[<arg1>,<arg2>...]}
    		String sql = "{call selectAge(?)}";
    		Connection conn = null;
    		CallableStatement call = null;
    		try { 
    			//得到数据库连接
    			conn = JDBCUtils.getConnection();
    			
    			//通过数据库连接创建statement
    			call = conn.prepareCall(sql);
    			
    			//对于输出参数，声明
    			call.registerOutParameter(1, OracleTypes.NUMBER);
    			
    			//对于输入参数，赋值
    			call.setInt(2, 3);
    			
    			//执行调用
    			call.execute();
    			
                            //获取返回的结果
    			int age = call.getInt(1);
    			
    			System.out.println("该用户年龄："+age);
    		} catch (Exception e) { 
    			e.printStackTrace();
    		}finally{ 
    			JDBCUtils.release(conn, call, null);
    		}
    	}
    }

4、调用存储过程，一个输入参数，返回查询结果集合

package com.hwb.demo;
     
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import org.junit.Test;
    import oracle.jdbc.internal.OracleCallableStatement;
    import oracle.jdbc.internal.OracleTypes;
     
    import com.hwb.db.JDBCUtils;
    /** * 存储过程 一个输入参数，返回一个查询结果集合 * @author Administrator * */
    public class CursorTest { 
     
    	/** * create or replace package Mypackage as procedure queryUserList(uid in number,userList out usercursor); end mypackage; */
    	@Test
    	public void testCursor(){ 
    		String sql = "{call Mypackage.queryUserList(?,?) }";
    		
    		Connection conn = null;
    		CallableStatement call = null;
    		ResultSet rs = null;
    		try { 
    			//得到数据库连接
    			conn = JDBCUtils.getConnection();
    			//通过数据库连接创建statement
    			call = conn.prepareCall(sql);
    			
    			//对于输入参数，赋值
    			call.setInt(1, 1);
    			//对于输出参数，声明
    			call.registerOutParameter(2, OracleTypes.CURSOR);
    			//执行调用
    			call.execute();
    			//将CallableStatement 强转成 OracleCallableStatement 用来获取光标类型Cursor，并得到结果ResultSet
    			rs = ((OracleCallableStatement)call).getCursor(2);
    			//遍历 ResultSet
    			while (rs.next()) { 
    				//根据类型和列名取值
    				int id = rs.getInt("user_id");    //括号内为 列名
    				String user_name = rs.getString("user_name");
    				int age = rs.getInt("user_age");
    				String sex = rs.getString("user_sex");
    				System.out.println("查询到的用户信息：\n\tid:"+id+"\n\t姓名："+user_name
    						+"\n\t年龄："+age+"\n\t性别："+sex);
    			}
    			
    			
    		} catch (Exception e) { 
    			e.printStackTrace();
    		}finally{ 
    			JDBCUtils.release(conn, call, rs);
    		}
    	}
    }

## 二、SQL-Sever ##

### 1.概念 ###

一组为完成特定功能的SQL语句集，存储在数据库中，经过第一次编辑后再次调用不需再次编辑，用户通过指定存储过程的名称并给出参数（如果有参数）来执行它，它可包括逻辑控制语句和数据操作语句，它可接受参数、输出参数、返回单个或多个结果集以及返回值。

Transact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。

### 2、系统存储过程 ###

#### a）简介 ####

系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“ **sp\_** ”开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

#### b）常用系统存储过程 ####

--查看数据库
    exec sp_databases;
    
    --查看表
    exec sp_tables;
    
    --查看列
    exec sp_columns student;
    
    --查看索引
    exec sp_helpIndex student;
    
    --约束
    exec sp_helpConstraint student;
    exec sp_stored_procedures;
    
    --查看存储过程创建、定义语句
    exec sp_helptext 'sp_stored_procedures';
    
    --修改表、索引、列的名称
    exec sp_rename student, stuInfo;
    
    --更改数据库名称
    exec sp_renamedb myTempDB, myDB;
    
    --更改登录名的默认数据库
    exec sp_defaultdb 'master', 'myDB';
    
    --数据库帮助，查询数据库信息
    exec sp_helpdb;
    exec sp_helpdb master;

#### c）示例 ####

--表重命名
    exec sp_rename 'stu', 'stud';
    select * from stud;
    
    --列重命名
    exec sp_rename 'stud.name', 'sName', 'column';
    exec sp_help 'stud';
    
    --重命名索引
    exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
    exec sp_help 'student';
    
    --查询所有存储过程
    select * from sys.objects where type = 'P';
    select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

### 3、用户自定义存储过程(使用) ###

#### I）创建语法 ####

create proc | procedure pro_name
        [{@参数数据类型} [=默认值] [output],
         {@参数数据类型} [=默认值] [output],
         ....
        ]
    as
        SQL_statements

#### II）修改 ####

alter proc proc_get_student
    as
    select * from student;

#### a）创建不带参存储过程 ####

---创建存储过程
    if (exists (select * from sys.objects where name = 'proc_get_student'))
        drop proc proc_get_student
    go
    create proc proc_get_student
    as
        select * from student;
    
    --调用、执行存储过程
    exec proc_get_student;

#### b）带参数的存储过程 ####

--带参存储过程
    if (object_id('proc_find_stu', 'P') is not null)
        drop proc proc_find_stu
    go
    create proc proc_find_stu(@startId int, @endId int)
    as
        select * from student where id between @startId and @endId
    go
    
    exec proc_find_stu 2, 4;

#### c）带通配符参数存储过程 ####

if (object_id('proc_findStudentByName', 'P') is not null)
        drop proc proc_findStudentByName
    go
    create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
    as
        select * from student where name like @name and name like @nextName;
    go
    
    exec proc_findStudentByName;
    exec proc_findStudentByName '%o%', 't%';

#### d）带输出参数存储过程 ####

if (object_id('proc_getStudentRecord', 'P') is not null)
        drop proc proc_getStudentRecord
    go
    create proc proc_getStudentRecord(
        @id int, --默认输入参数
        @name varchar(20) out, --输出参数
        @age varchar(20) output--输入输出参数
    )
    as
        select @name = name, @age = age  from student where id = @id and sex = @age;
    go
    
    -- 
    declare @id int,
            @name varchar(20),
            @temp varchar(20);
    set @id = 7; 
    set @temp = 1;
    exec proc_getStudentRecord @id, @name out, @temp output;
    select @name, @temp;
    print @name + '#' + @temp;

#### e）不缓存存储过程 ####

--WITH RECOMPILE 不缓存
    if (object_id('proc_temp', 'P') is not null)
        drop proc proc_temp
    go
    create proc proc_temp
    with recompile
    as
        select * from student;
    go
    
    exec proc_temp;

#### f）加密存储过程 ####

--加密WITH ENCRYPTION 
    if (object_id('proc_temp_encryption', 'P') is not null)
        drop proc proc_temp_encryption
    go
    create proc proc_temp_encryption
    with encryption
    as
        select * from student;
    go
    
    exec proc_temp_encryption;
    exec sp_helptext 'proc_temp';
    exec sp_helptext 'proc_temp_encryption';

#### g） 带游标参数存储过程 ####

if (object_id('proc_cursor', 'P') is not null)
        drop proc proc_cursor
    go
    create proc proc_cursor
        @cur cursor varying output
    as
        set @cur = cursor forward_only static for
        select id, name, age from student;
        open @cur;
    go
    --调用
    declare @exec_cur cursor;
    declare @id int,
            @name varchar(20),
            @age int;
    exec proc_cursor @cur = @exec_cur output;--调用存储过程
    fetch next from @exec_cur into @id, @name, @age;
    while (@@fetch_status = 0)
    begin
        fetch next from @exec_cur into @id, @name, @age;
        print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
    end
    close @exec_cur;
    deallocate @exec_cur;--删除游标

#### h）分页存储过程 ####

---存储过程、row_number完成分页
    if (object_id('pro_page', 'P') is not null)
        drop proc proc_cursor
    go
    create proc pro_page
        @startIndex int,
        @endIndex int
    as
        select count(*) from product
    ;    
        select * from (
            select row_number() over(order by pid) as rowId, * from product 
        ) temp
        where temp.rowId between @startIndex and @endIndex
    go
    --drop proc pro_page
    exec pro_page 1, 4
    --
    --分页存储过程
    if (object_id('pro_page', 'P') is not null)
        drop proc pro_stu
    go
    create procedure pro_stu(
        @pageIndex int,
        @pageSize int
    )
    as
        declare @startRow int, @endRow int
        set @startRow = (@pageIndex - 1) * @pageSize +1
        set @endRow = @startRow + @pageSize -1
        select * from (
            select *, row_number() over (order by id asc) as number from student 
        ) t
        where t.number between @startRow and @endRow;
    
    exec pro_stu 2, 2;

### 4、Raiserror ###

Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。

#### I）语法解析 ####

Raiserror({msg_id | msg_str | @local_variable}
      {
       , severity, state}
      [,argument[,…n]]
      [with option[,…n]]
    )
    
    # msg_id:在sysmessages系统表中指定的用户定义错误信息。
    # msg_str:用户定义的信息，信息最大长度在2047个字符。
    # state：介于1至127直接的任何整数。State默认值是1。
    # severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。
        任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。

#### II）示例 ####

raiserror('is error', 16, 1);
    select * from sys.messages;
    --使用sysmessages中定义的消息
    raiserror(33003, 16, 1);
    raiserror(33006, 16, 1);

## 三、Mysql ##

5.0版本开始支持存储过程

### 1、简介 ###

#### I）概念 ####

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

#### II）优点 ####

存储过程可封装，并隐藏复杂的商业逻辑。

存储过程可以回传值，并可以接受参数。

存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

存储过程可以用在数据检验，强制实行商业逻辑等。

#### III）缺点 ####

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

存储过程的性能调校与撰写，受限于各种数据库系统。

#### V）链接 ####

这位大哥写的很详细也有运行效果图，链接

https://blog.csdn.net/qq_33157666/article/details/87877246

常见函数，此文也是摘自这位老哥的，链接

https://blog.csdn.net/Servlet905/article/details/73087819

菜鸟教程，链接

https://www.runoob.com/w3cnote/mysql-stored-procedure.html

循环函数，此文有一些是摘自这位老哥的，链接

https://www.jianshu.com/p/5b058fbbda19

### 2、简单使用 ###

#### a）不带参数的存储过程 ####

-- 创建
    create procedure GetUsers()
    begin
      select * from users;
    end;
    
    -- 调用
    call GetUsers();
    
    -- 删除
    drop procedure if exists GetUsers;

#### b）带参数的存储过程 ####

示例1：

-- 创建
    create procedure GetScores(
        out  minScore  decimal(8,2),
        out  avgScore  decimal(8,2),
        out  maxScore  decimal(8,2))
    begin
        select min(score)  into  minScore  from user;
        select avg(score)  into  avgScore  from user;
        select max(score)  into  maxScore  from user;
    end;
    
    -- 调用
    --mysql中变量都必须以@开始
    call  GetScores(@minScore,@avgScore,@maxScore);

注：该调用并没有任何输出，只把调用的结果赋值给了调用时出入的变量：@minScore,@avgScore,@maxScore，  
如需显示，需用：

select @minScore,@avgScore,@maxScore;

示例2：

-- 创建：输入一个用户ID，返回该用户的名字
    create  procedure  GetNameByID(
        in userID  int
        out  userName  varchar(200))
    begin
        select name from user
        where  id = userID
        into  userName;
    end;
    
    -- 调用
    call  GetNameByID(1,@userName)
    select  @userName;

#### c）一定逻辑的存储过程 ####

-- 创建
    delimiter 
    create  procedure  GetPriceByID(
        in prodID  int,
        in isDisc  boolean,
        out  prodPrice  decimal(8,2))
    begin
        declare   tempPrice  decimal(8,2);#声明变量
        declare   prodDiscRate  decimal(8,2);
        set  prodDiscRate = 0.88;
    
        select  price  from  products
        where  id = prodID
        into  tempPrice;
    
        if  isDisc  then
            select  tempPrice * prodDiscRate  into  tmpPrice;
        end  if;
    
        select  tmpPrice  into  prodPrice;
    
    end;
    
    
    -- 调用
    call  GetPriceByID(1,ture,@prodPrice);
    select  @prodPrice;

#### d）变量–声明和赋值 ####

create procedure test2()
    begin
      -- 使用 declare语句声明一个变量
      declare username varchar(32) default '';
      
      -- 使用set语句给变量赋值
      set username='xiaoxiao';
      
      -- 将users表中id=1的名称赋值给username
      select name into username from users where id=1;
      
      -- 返回变量
      select username;
      
    end;
    
    
    -- 概括 --
    -- 变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用；
    -- 变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能制定默认值、字符集和排序规则等；
    -- 变量可以通过set来赋值，也可以通过select into的方式赋值；
    -- 变量需要返回，可以使用select语句，如：select 变量名。

#### e）变量–作用域 ####

-- 创建一个存储过程，用来统计用户表和订单表中行数数量和订单表中最大/最小金额
    
    -- begin end 结构 ( () () )
    create procedure test()
      begin
        begin
          # 用户表中的行数数量
          declare userscount int default 0;
    
          # 订单表中的行数数量
          declare ordercount int default 0;
    
          # count计算
          select count(*) into userscount from users;
          select count(*) into ordercount from orders;
    
          # 返回用户表中的数量、订单表中的数量
          select userscount,ordercount;
        end;
    
        begin
          declare moneyMax int default 0;
          declare moneyMin int default 0;
    
          select max(money)into moneyMax from orders;
          select min(money)into moneyMin from orders;
    
          select moneyMin,moneyMax;
        end;
    
      end;
      
    -- 存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束;
    -- 需要多个块之间传值，可以使用全局变量，即放在所有代码块之前;
    -- 传参变量是全局的，可以在多个块之间起作用。

#### f）参数–IN、OUT、INOUT ####

基本语法：

存储过程中的参数类型有：IN、OUT、INOUT

create procedure 名称( [IN|OUT|INOUT] 参数名 参数数据类型 )
    begin
    .........
    end

-- 编写存储过程,传入id,根据id返回name
    
    create procedure test4(userId int)
      begin
        declare username varchar(32) default '';
        declare ordercont int default 0;
        select name into username from teacher where id=userId;
        select username;
      end;
    
    call test4(1)
    
    -- 传入参数：类型为in,表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,那么默认就是in类型。
    -- IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回。
    -- 如果调用存储过程中需要修改和返回值，可以使用OUT类型参数。

OUT

-- 调用存储过程时，传入userId返回该用户的name
    
    create procedure test5(in userId int,out username varchar(32))
      begin
        select name into username from users where id = userId;
      end;
      
    -- 传出参数：在调用存储过程中，可以改变其值，并可返回；
    -- out是传出参数，不能用于传入参数值；
    -- 调用存储过程时，out参数也需要指定，但必须是变量，不能是常量；
    -- 如果既需要传入，同时又需要传出，则可以使用INOUT类型参数

INOUT

-- 调用存储过程时，传入userId和userName,既是传入参数也是传出参数
    
    create procedure test6(inout userId int,inout username varchar(32))
    begin
        set userId=2;
        set username='';
        select id,name into userId,username from users where id=userId;
    end;
    
    -- 可变变量INOUT: 调用时可传入值，在调用过程中，可修改其值，同时也可返回值；
    -- INOUT参数集合了IN和OUT类型的参数功能；
    -- INOUT调用时传入的是变量，而不是常量。

### 3、条件语句 ###

基本结构：

1、条件语句基本结构
    if() then...else...end if;
    
    2、多条件判断语句
    if() then...
    elseif() then...
    else ...
    end if;

#### a）简单条件语句示例 ####

-- 编写存储过程，如果用户userId是偶数则返回username,否则返回userId
    
    create procedure test7(in userId int)
      begin
        declare username varchar(32) default '';
        if (userId % 2 = 0)
          then
            select name into username from users where id = userId;
            select username;
          else
            select userId;
          end if;
      end;

#### b）多条件语句示例 ####

-- 根据用户传入的uid参数判断
    	-- 若用户状态status为1，则给用户score加10分；
    	-- 若用户状态status为2，则给用户score加20分；
    	-- 其他情况加30分；
    	
    create procedure test8(in userid int)
    begin
       declare my_status int default 0;
       select status into my_status from users where id=userid;
       if(my_status=1)
       then 
           update users set score=score+10 where id=userid;
        elseif(my_status=2)
        then 
           update users set score=score+20 where id=userid;
        else 
           update users set score=score+30 where id=userid;
        end if;
    end;

### 4、控制语句 ###

#### 创建简单存储 ####

create procedure mypro(in a int, in b int , out sum int)
    begin
        set sum = a + b;
    end;
    
    # 上面是一个简单的 a+b=sum 的存储过程
    # begin end 就可以理解为一般写方法套的{}，设置变量前面需加上set
    # 调用存储过程语法为call存储过程名(对应的参数)
    # select 返回值就能查询存储过程的返回值了
    # in 表示传入的参数在存储过程里面改变了，在外面查询还是之前的值，不会改变，
    # inout 则反之，参数在存储过程里面受到了修改，在外面查询就是修改后的值了，out就是返回值

![在这里插入图片描述][20200527165259525.png]

#### a）if ####

create procedure mypro2(in type int)
    begin
        declare c varchar(500);
        if type = 0 then
            set c = 'type is 0';
        elseif type =1 then
            set c = 'type is 1';
        else
            set c = 'type is not 0 also is not 1';
        end if;
        select c;
    end;
    
    # declare是声明变量区，在存储过程中声明变量需在前面加上declare
    # if elseif else end if; 是if控制语句的语法，意思一目了然，由end if;来结束语句

![在这里插入图片描述][20200527165148364.png]

#### b）case ####

基本语法：

case ...
    when ... then....
    when.... then....
    else ... 
    end case;

示例1：

create procedure mypro3(in type int)
    begin
        declare c varchar(500);
        case type
        when 0 then
            set c = 'type is 0';
        when 1 then
            set c = 'type is 1';
        else
            set c = 'type is not 0 also is not 1';
        end case;
        select c;
    end;
    
    # case when then else end case; 是case控制语句的语法由end case;来结束语句

![在这里插入图片描述][20200527165138803.png]

示例2：

-- users表中，根据userid获取status值，
    	-- 如果status为1，则修改score为10；
    	-- 如果status为2，则修改为20，
    	-- 如果status3，则修改为30；
    	-- 否则修改为40。
    	
     create procedure testcate(userid int)
        begin 
            declare my_status int default 0;
            select status into my_status from users where id=userid;
     
            case my_status
                when 1 then update users set score=10 where id=userid;
                when 2 then update users set score=20 where id=userid;
                when 3 then update users set score=30 where id=userid;
                else update users set score=40 where id=userid;
            end case;
        end;

#### c）while ####

基本语法：

while(表达式) do 
    ......  
    end while;

示例1：

drop procedure if exists mypro4;
    create procedure mypro4(in number int)
    begin
        declare i int default 0;
        declare j int default 0;
        while i <= number do
            set j = j + i;
            set i = i + 1;
        end while;
        select j;
    end;
    
    # while 循环条件 do end while;由end while来结束语句

示例2：

-- 使用循环语句，向表test1(id)中插入10条连续的记录
    
    create procedure test9()
    begin
      declare i int default 0;
      while(i<10) do 
        begin 
            select i;
            set i=i+1;
            insert into test1(id) values(i);
         end;
      end while;
    end;

#### d）repeat ####

基本语法：

repeat...until...end repeat;

示例：

-- 给test1中的id字段插入数据，从1到10
    
    create procedure test10()
    begin
        declare i int default 0;
        repeat 
        begin 
            select i;
            set i=i+1;
            insert into test1(id) values(i);
        end;
        until i>=10 -- 如果i>=10,则跳出循环
        end repeat;
    end;
    
    -- until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式;
    -- 只有当until语句为真是，循环结束。

### 5、存储过程中常见函数 ###

#### a）字符串类 ####

CHARSET(str) //返回字串字符集
    CONCAT (string2  [,... ]) //连接字串
    INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
    LCASE (string2 ) //转换成小写
    LEFT (string2 ,length ) //从string2中的左边起取length个字符
    LENGTH (string ) //string长度
    LOAD_FILE (file_name ) //从文件读取内容
    LOCATE (substring , string  [,start_position ] ) 同INSTR,但可指定开始位置
    LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
    LTRIM (string2 ) //去除前端空格
    REPEAT (string2 ,count ) //重复count次
    REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
    RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
    RTRIM (string2 ) //去除后端空格
    STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
    SUBSTRING (str , position  [,length ]) //从str的position开始,取length个字符,
    注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
    mysql> select substring('abcd',0,2);
    +-----------------------+
    | substring('abcd',0,2) |
    +-----------------------+
    |                       |
    +-----------------------+
    1 row in set (0.00 sec)
    
    
    mysql> select substring('abcd',1,2);
    +-----------------------+
    | substring('abcd',1,2) |
    +-----------------------+
    | ab                    |
    +-----------------------+
    1 row in set (0.02 sec)
    
    TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
    UCASE (string2 ) //转换成大写
    RIGHT(string2,length) //取string2最后length个字符
    SPACE(count) //生成count个空格

#### b）数字类 ####

ABS (number2 ) //绝对值
    BIN (decimal_number ) //十进制转二进制
    CEILING (number2 ) //向上取整
    CONV(number2,from_base,to_base) //进制转换
    FLOOR (number2 ) //向下取整
    FORMAT (number,decimal_places ) //保留小数位数
    HEX (DecimalNumber ) //转十六进制
    注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
    也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
    LEAST (number , number2  [,..]) //求最小值
    MOD (numerator ,denominator ) //求余
    POWER (number ,power ) //求指数
    SIGN (number2 ) //返回符号,正负或0
    SQRT(number2) //开平方
    RAND([seed]) //随机数
    ROUND (number  [,decimals ]) //四舍五入,decimals为小数位数]
    注：返回类型并非均为整数，如：
    (1)默认变为整形值
    mysql> select round(1.23);
    +-------------+
    | round(1.23) |
    +-------------+
    |           1 |
    +-------------+
    1 row in set (0.00 sec)
    
    mysql> select round(1.56);
    +-------------+
    | round(1.56) |
    +-------------+
    |           2 |
    +-------------+
    1 row in set (0.00 sec)
    
    (2)可以设定小数位数，返回浮点型数据
    mysql> select round(1.567,2);
    +----------------+
    | round(1.567,2) |
    +----------------+
    |           1.57 |
    +----------------+
    1 row in set (0.00 sec)

#### c）日期时间类 ####

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
    CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
    CURRENT_DATE (  ) //当前日期
    CURRENT_TIME (  ) //当前时间
    CURRENT_TIMESTAMP (  ) //当前时间戳
    DATE (datetime ) //返回datetime的日期部分
    DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
    DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
    DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
    DATEDIFF (date1 ,date2 ) //两个日期差
    DAY (date ) //返回日期的天
    DAYNAME (date ) //英文星期
    DAYOFWEEK (date ) //星期(1-7) ,1为星期天
    DAYOFYEAR (date ) //一年中的第几天
    EXTRACT (interval_name  FROM date ) //从date中提取日期的指定部分
    MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
    MAKETIME (hour ,minute ,second ) //生成时间串
    MONTHNAME (date ) //英文月份名
    NOW (  ) //当前时间
    SEC_TO_TIME (seconds ) //秒数转成时间
    STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
    TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
    TIME_TO_SEC (time ) //时间转秒数]
    WEEK (date_time [,start_of_week ]) //第几周
    YEAR (datetime ) //年份
    DAYOFMONTH(datetime) //月的第几天
    HOUR(datetime) //小时
    LAST_DAY(date) //date的月的最后日期
    MICROSECOND(datetime) //微秒
    MONTH(datetime) //月
    MINUTE(datetime) //分

### 6、自定义函数 ###

函数和存储过程最大的区别是函数必须有返回值，否则会报错。

示例1：

-- 一个简单的函数
    
    create function getusername(userid int) returns varchar(32)
        reads sql data  -- 从数据库中读取数据，但不修改数据
        begin
            declare username varchar(32) default '';
            select name into username from users where id=userid;
            return username;
        end;
       
    -- 创建函数使用create function 函数名(参数) returns 返回类型；
    -- 函数体放在begin和end之间；
    -- returns指定函数的返回值；
    -- 函数调用使用select getusername()。

示例2：

-- 根据userid，获取accoutid,id,name组合成UUID作为用户的唯一标识
    
    create function getuuid(userid int) returns varchar(64)
      reads sql data  -- 从数据库中读取数据，但不修改数据
      begin
          declare uuid varchar(64) default '';
          select concat(accontid,'_',id,'_',name) into uuid from users where id=userid;
          return uuid;
      end;

### 7、其他知识点 ###

其他知识点涉及内容如下

#### a）存储过程中游标 ####

#### b）触发器 ####

#### c）存储过程+event事件 ####

大神链接：
    	https://blog.csdn.net/qq_33157666/article/details/87877246

## 四、Hive ##

### HPL/SQL的安装和使用 ###

链接：

https://blog.csdn.net/u014307117/article/details/88726349
    https://www.cnblogs.com/kinginme/p/7267638.html

Hive从版本2.0之后开始支持存储过程HPL/SQL

支持：

Oracle PL/SQL，ANSI/ISO SQL/PSM（IBM DB2，MySQL，Teradata i.e）PostgreSQL PL/pgSQL（Netezza），Transact-SQL（Microsoft SQL Server and Sybase）

附：  
1、不同的SQL存储细节不尽相同，所以做个汇总方便学习和比较；  
2、本片是汇总个位大神的知识点，上面都附有详细链接。

[20200527165259525.png]: https://img-blog.csdnimg.cn/20200527165259525.png
[20200527165148364.png]: https://img-blog.csdnimg.cn/20200527165148364.png
[20200527165138803.png]: https://img-blog.csdnimg.cn/20200527165138803.png