thinkphp sqlserver存储过程举例

公司要做一个编码查询功能,而数据库中给出了存储过程的方法,只要我这边调用就可以,以前一点没接触过,通过网上搜索,以及公司维护数据库的一起处理,将这个小问题解决

所有的备注内容写在了代码中,可从代码中查看

数据库配置代码config.php

<?php
return array(
		
	
	/* 数据库设置 */
    'DB_TYPE'               =>  'sqlsrv',     // 数据库类型
    'DB_HOST'               =>  '60.6.112.34', // 服务器地址
    'DB_NAME'               =>  'qr',          // 数据库名
    'DB_USER'               =>  'aa',      // 用户名
    'DB_PWD'                =>  'eri123456',          // 密码
    'DB_PORT'               =>  '1433',        // 端口
);

实现存储过程完成方法代码

<?php
	namespace Home\Controller;
	use Think\Controller;
	class SesController extends Controller
	{
		public function search()
		{
			
			if(IS_POST)
			{
				$post = I('post.');
				$post = $post['name'];
				$db = M (""); 
				
				
				//DECLARE声明变量 SET赋值 exec执行
				//bjjhdl_10是存储名称  @vaa03是存储过程的传入值名				
				//bjjhdl_10 @vaa03这段网上是写成bjjhdl_10(@vaa03)的格式,但使用中发现会出错
				$sql = "DECLARE @vaa03 int SET @vaa03 = '$post'; exec bjjhdl_10 @vaa03 ";
				$data = $db -> query($sql);
				
	      $jl = $data[0][""];
	      
	      $this->assign('jl',$jl);
	      //提交后显示结果页
	      $this->display('/jl/ok');
	      
		}else{
				//默认显示查询窗口页
				$this->display('/jl/jl'); 
				}
		}
	}
?>

调试中出现的问题

$sql = "DECLARE @vaa03 int SET @vaa03 = '$post'; exec bjjhdl_10 @vaa03 ";这段如果未加DECLARE @vaa03 int 对变量进行声明,那么就会报错,报错如下:

137:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]必须声明标量变量 "@vaa03"。 [ SQL语句 ] : SET @vaa03 = '09007527'; exec bjjhdl_10 @vaa03

jl.html模板代码

主要是限定了只能使用数字及数字输入长度,下面使用a做按钮是因为,这是我找了我以前写的一个内容直接用的
另外,因为最终要使用的输入界面还定下来,jl.html和ok.html只是测试时用的模板,但功能基本上是这样
<!DOCTYPE html>
<html>
	<head>
		<meta charset=utf-8>
		<title>请输入查询内容</title>
		<script language="javascript" src="/skin/libs/jquery/jquery.min.1.7.2.js"></script>
		
	</head>
	<body>
		<form action="" method="post">

			 <input type="number" name="name" autofocus="autofocus" max="8" placeholder="编号" oninput="if(value.length>9)value=value.slice(0,9)">
			 <p>
				<a href="javascript:;" class="confirm">确定</a>
			</p>

 </form>
 <script>			
			//jQuery
			$(function()
			{
				$('.confirm').on('click',function(){
					$('form').submit();
				});
			});
		</script>
	</body>
</html>

ok.html模板代码

这个加入了10秒返回功能
<!DOCTYPE html>
<html>
	<head>
		<meta charset=utf-8>
		<title>查询结果</title>
		<meta http-equiv=refresh  content="5;url=/thinkphp/index.php/home/ses/search/" >
		<script language="javascript" src="/skin/libs/jquery/jquery.min.1.7.2.js"></script>
		<script>
			function run(){
			 var s = document.getElementById("dd");
			 if(s.innerHTML == 0){
			  window.location.href='regform.shtml';
			  return false;
			 }
			 s.innerHTML = s.innerHTML * 1 - 1;
			}
			window.setInterval("run();", 1000);
		</script>
		
	</head>
	<body>
		<div>			
			<li>{$jl}</li>
			<li><a href="/thinkphp/index.php/home/ses/search/">点击返回</a></li>
			<li>页面<span id="dd">10</span>秒后返回</li>
		</div>

	</body>
</html>

数据库中存储过程的测试代码

具体的执行代码貌似还没写成,只放了这段测试的,因为这一段含有一些其它的,将就看吧
USE [BJAE]
GO
/****** Object:  StoredProcedure [dbo].[BJJHDL_10]    Script Date: 2019-04-04 10:05:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BJJHDL_10] 
--@vaa07 INT,
@VAA03 varchar(10)
--@bck01 INT,
--@smsg varchar(2048)
as
DECLARE @vaa07 INT
DECLARE @Debug_VAA03 INT
DECLARE @bck01 INT
declare @smsg varchar(2048)
set @Debug_VAA03=@VAA03
set @smsg = ''
set @bck01='10'




SELECT  
DISTINCT @vaa07=A.VAA07
FROM VAJ1 A 
LEFT JOIN VAA1 C ON C.VAA01=A.VAA01
LEFT JOIN BBY1 D ON D.BBY01=A.BBY01
WHERE  CONVERT(varchar(100), VAj47, 23)=CONVERT(varchar(100), GETDATE(), 23)
and d.bby05<>'彩色打印照片' AND VAJ37>0 
AND VAA03=@VAA03 
AND  A.BCK01D=@bck01



if not exists (
SELECT  
*
FROM VAJ1 A 
LEFT JOIN VAA1 C ON C.VAA01=A.VAA01
LEFT JOIN BBY1 D ON D.BBY01=A.BBY01
WHERE  CONVERT(varchar(100), VAj47, 23)=CONVERT(varchar(100), GETDATE(), 23)
and d.bby05<>'彩色打印照片' AND VAJ37>0 
AND VAA03=@VAA03 
AND  A.BCK01D=@bck01)
BEGIN 
  SELECT '未找到当日的缴费记录'
  --set @smsg ='未找到当日的缴费记录'
  --RAISERROR(@smsg, 16, 1) with nowait
end
else
if exists (select * from BJJHDL where vaa07=@vaa07) 
  BEGIN 
  set @smsg ='已经报道过了'
  RAISERROR(@smsg, 16, 1) with nowait
end
else

if not exists (select * from BJJHDL where vaa07=@vaa07) 
BEGIN 
SELECT  
DISTINCT
C.VAA05
,C.VAA03
--,@vaa07=A.VAA07
,A.VAA07
,A.VAA01
,A.BCK01D
INTO #A
FROM VAJ1 A 
LEFT JOIN VAA1 C ON C.VAA01=A.VAA01
LEFT JOIN BBY1 D ON D.BBY01=A.BBY01
WHERE  CONVERT(varchar(100), VAj47, 23)=CONVERT(varchar(100), GETDATE(), 23)
and d.bby05<>'彩色打印照片' AND VAJ37>0 
AND VAA03=@VAA03  
AND  A.BCK01D=@bck01

DECLARE @ID INT
SELECT @ID=MAX(ISNULL(ID,10))+1 FROM BJJHDL WHERE BCK01=@bck01 and CONVERT(varchar(100), time, 23)=CONVERT(varchar(100), GETDATE(), 23) 

INSERT INTO BJJHDL(VAA05,VAA07,VAA01,BCK01,TIME,ID)
SELECT  VAA05,@vaa07,VAA01,BCK01D,GETDATE(),@ID FROM #A

update vac1 set vac10=@ID where VAC01=@vaa07

SELECT  * FROM BJJHDL WHERE BCK01=10  and vaa07=@vaa07 


DROP TABLE #A
end

/*
select id,c.VAA05,d.BBY05 from BJJHDL 
left join VAJ1 a on a.VAA07=BJJHDL.vaa07
LEFT JOIN VAA1 C ON C.VAA01=A.VAA01
LEFT JOIN BBY1 D ON D.BBY01=A.BBY01
where BCK01D='10' and CONVERT(varchar(100), time, 23)=CONVERT(varchar(100), GETDATE(), 23) 
and  d.bby05<>'彩色打印照片'
order by ID

*/
--delete VAD1 where VAD03='2019-02-19 00:00:00.000'

评论

大侠名号:   验证暗号: 点击我更换图片

修远兮

在这里记录每一点自己需要的知识

添加创始人微信,一起交流心得

推荐文章RECOMMEND