sqlserver学习2---java执行存储过程

一、存储过程

　　1、新增操作存储过程

--------------1、新建 增加学生的存储过程----------------------------
set IDENTITY_INSERT student on
go
create procedure addStu(@maxcount BIGINT)
as
begin 
    DECLARE @count int;
    set @count = 0;
    while(@count<@maxcount)
    begin
        if(@count=2)
        begin
        insert into student values('小叶','女',20+@count,GETDATE(),GETDATE());
        end
        else if(@count%3=0)
        begin
        insert into student values('小啦啦啦啦啦3','女',10+@count,GETDATE(),GETDATE());
        end
        else
        insert into student values('小花'+cast(@count as varchar(1024)),'男',20+@count,CONVERT(VARCHAR(10),GETDATE(),120),DATEADD(MONTH,2,GETDATE()));
        set @count = @count +1;
    end
end
go
set IDENTITY_INSERT student off

　　2、执行存储过程

--执行存储过程
exec addStu 20
--删除存储过程
drop procedure addStu

　　3、执行语句块

----------------执行语句块---------------------------------------------------------------------
declare @STR NVARCHAR(4000);
declare @count int;
declare @time int;
set @count = 100;
begin
--将表student中最大id的值赋值给变量@count
select @count = max(ID)  from student;
--201810
select @time = CAST(CONVERT(VARCHAR(6),MAX(CREATE_TIME),112)AS INT) from student;
set @STR = 'select * from dbo.student where id <='+cast(@count as NVARCHAR(4000));
--执行sql语句：@STR
EXEC SP_EXECUTESQL @STR
end
go

　　4、修改存储过程

-----------------------修改存储过程---------------------------------------------------------------
 ALTER PROCEDURE PROC_INSERT_DATA_DETAIL
          @DealerID varchar(50), 
          @FieldName varchar(2000),
          @FieldValue varchar(2000)
      AS
      BEGIN
          DECLARE @Count INT
          DECLARE @StrSQL VARCHAR(2000)
          SET @Count = (SELECT COUNT(*) FROM myDATA_Details WHERE DealerID = @DealerID)
 
         IF (@COUNT>0)
             BEGIN
                 SET @StrSQL = 'UPDATE myDATA_Details SET '+ @FieldName + ' = ''' +@FieldValue + ''' WHERE DealerID = '+ @DealerID
                 EXEC(@StrSQL)
             END
         ELSE
             BEGIN
                 INSERT INTO myDATA_Details (DealerID) VALUES (@DealerID)
                 SET @StrSQL = 'UPDATE myDATA_Details SET '+ @FieldName + ' = ''' +@FieldValue + ''' WHERE DealerID = '+ @DealerID
                 EXEC(@StrSQL)
             END
     END

　　5、java执行存储过程

package com.xiamgmu.base.utils;

import java.sql.CallableStatement;
import java.sql.Connection;

public class UtilsPro {
    /**
     * 创建新建的存储过程
     * @param args
     * String procedure = "{call addStu(100)}";
     */
    public static void runPrcsummone(String procedure){
        Connection conn = null;
        CallableStatement cstmt = null;
        
        try {
            conn = UtilsDao.getConnection();
            //预处理存储过程
            cstmt = conn.prepareCall(procedure);
            //执行存储过程
            cstmt.execute();
        } catch (Exception e) {
            // TODO: handle exception
        }finally
        {
            try
            {
                if(cstmt != null)
                {
                    cstmt.close();
                }
                if(conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception e)
            {
                // TODO: handle exception
            }
        }
    }
    

    public static void main(String[] args) {
        UtilsPro pro = new UtilsPro();
        String procedure = "{call addStu(100)}";
        UtilsPro.runPrcsummone(procedure);
    }