整合营销服务商

电脑端+手机端+微信端=数据同步管理

免费咨询热线:

sqlserver存储过程sp_send_dbmail邮件(html)实际应用

段时间因工作需求,特地学习了下sp_send_dbmail的使用,好不容易完工来和大家分享一下,不谈理论,只管实践!

如下是实际需求:

-- =============================================
-- Title: 集团资质一览表
-- Description1:<1、距离到期日期1年内和已过期的发到期提醒>
-- Description2:<2、表头【非附件】:公司名称、发证部门、证书名称、类别、等级、到期日期、预警级别>
-- Description3:<3、预警级别:假设距离到期日期月数为N。一级:N<=3;二级:3<N<=6;三级:N>=6>
-- Description4:<4、提醒人员:邮件提醒
-- =============================================

在这里sp_send_dbmail的参数不去做详述(我也不懂~),实际过程中我们需要用到的并不多,只需下面几行就能发送html格式的邮件了

Exec dbo.sp_send_dbmail 
@profile_name='crm***', --发件人姓名 
@recipients='156240***@qq.com', --邮箱(多个用;隔开)
@body=@tableHTML, --消息主体 
@body_format='HTML', --指定消息的格式,一般文本直接去掉即可,发送html格式的内容需加上
@subject ='资质到期预警'; -- 消息的主题

下面最主要的部分就是@tableHTML了,在这里我们使用两种方式去拼接html。

1.通过sql CAST 函数,网上的示例大多数是这种,愚笨的我不太看得懂,只能依瓢。

declare @tableHTML varchar(max)
SET @tableHTML =
N'<H1 style="text-align:center">资质相关信息</H1>' +
N'<table border="1" cellpadding="3" cellspacing="0" align="center">' +
N'<tr><th width=100px" >公司名称</th>'+
N'<th width=250px>发证部门</th><th width=150px>证书名称</th>'+
N'<th width=50px>类别</th><th width=50px>等级</th>'+
N'<th width=60px>到期日期</th><th width=60px>预警级别</th></tr>'+
CAST ( (
select td = p.CompanyName, '',td = p.DeptName, '',td=p.Name,'', td = p.QualificationType, '',td = p.Level, '',td = p.ExpireDates, '',td=p.YJ,''
from(    
    select   
    CompanyName,DeptName,Name,QualificationType,Level,Convert(varchar(50),ExpireDate,111)ExpireDates,
    case when DATEDIFF(mm,getDate(),ExpireDate)<=3 then '一级预警' 
    when DATEDIFF(mm,getDate(),ExpireDate)<=6 then '二级预警' else '三级预警'end YJ
    from  T_Market***_JTZZ 
    where 12>=DATEDIFF(mm,getDate(),ExpireDate)
) p order by  p.ExpireDates  asc
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

Exec dbo.sp_send_dbmail 
    @profile_name='crm***', 
    @recipients = '156240***@qq.com', 
    @subject='资质到期预警', 
    @body=@tableHTML,
    @body_format = 'HTML' ;

2.通过游标动态绘制html,感觉这种更方便,虽然写起来有点啰嗦,但很灵活。

BEGIN
    declare @tableHTML varchar(max)
    declare @Companyname varchar(250) --公司名称
    declare @Deptname varchar(250)    --发证部门
    declare @Certname varchar(250)    --证书名称
    declare @Certtype varchar(50)     --证书类别
    declare @Certlevel varchar(50)    --证书等级
    declare @Expirdate varchar(20)    --到期时间
    declare @Warnlevel varchar(20)    --预警级别
    
    begin
        set @tableHTML = '<html><body><table><tr><td><p><font color="#000080" size="3" face="Verdana">您好!</font></p><p style="margin-left:30px;"><font size="3" face="Verdana">以下资质即将到期或已过期,请尽快办理资质延续:</font></p></td></tr>';
        --创建临时表#tbl_result
        create table #tbl_result(companyname varchar(250),deptname varchar(250),certname varchar(250),certtype varchar(50),certlevel varchar(50),expirdate varchar(20),warnlevel varchar(10));
        insert into #tbl_result 
        select CompanyName,DeptName,Name,QualificationType,Level,convert(varchar(20),ExpireDate,23) ExpireDate,case when ms<=3 then '一级' when ms>3 and ms<=6 then '二级' else '三级' end warnlevel
        from (
            select *,Datediff(MONTH,GETDATE(),ExpireDate) ms
            from T_Market***_JTZZ 
            where ExpireDate is not null and Datediff(MONTH,GETDATE(),ExpireDate)<=12
        ) res;
        
        declare @counts int;
        select @counts=count(*) from #tbl_result;
        --- 提醒列表
        if(@counts>0)
        begin
            set @tableHTML=@tableHTML+'<tr><td><table border="1" style="border:1px solid #d5d5d5;border-collapse:collapse;border-spacing:0;margin-left:30px;margin-top:20px;"><tr style="height:25px;background-color: rgb(219, 240, 251);"><th style="width:100px;">公司名称</th><th style="width:200px;">发证部门</th><th>证书名称</th><th style="width:60px;">类别</th><th style="width:80px;">等级</th><th style="width:100px;">到期日期</th><th style="width:80px;">预警级别</th></tr>';
            --申明游标
            Declare cur_cert Cursor for
            select companyname,deptname,certname,certtype,certlevel,expirdate,warnlevel from #tbl_result order by expirdate;
            --打开游标
            open cur_cert
            --循环并提取记录
            Fetch Next From cur_cert Into @Companyname,@Deptname,@Certname,@Certtype,@Certlevel,@Expirdate,@Warnlevel
            While (@@Fetch_Status=0)
            begin
                set @tableHTML = @tableHTML + '<tr><td align="center">'+@Companyname+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Deptname+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Certname+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Certtype+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Certlevel+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Expirdate+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Warnlevel+'</td></tr>';
                --继续遍历下一条记录
                Fetch Next From cur_cert Into @Companyname,@Deptname,@Certname,@Certtype,@Certlevel,@Expirdate,@Warnlevel
            end
            --关闭游标
            Close cur_cert
            --释放游标
            Deallocate cur_cert
            set @tableHTML = @tableHTML + '</table></td></tr>';
        end
        
        -- 发送邮件
            exec msdb.dbo.sp_send_dbmail 
            @profile_name='crm***',
            @recipients='156240***@qq.com',
            @body=@tableHTML,
            @body_format='HTML',
            @subject ='资质到期预警';
            
        -- 删除临时表(#tbl_result)
        if object_id('tempdb..#tbl_result') is not null 
        begin
            drop table #tbl_result;
        end
    end

END

看起来无疑第二种特别啰嗦,但个人感觉很好理解,游标拼接html部分思路很清楚,以上两个方式均经过实践,如需使用只需要将其中对应的字段、数据源替换掉即可,感谢诸位赏足,有什么不足之处还望大家见谅,

做项目的过程中,我们经常遇到需要把信息存储在本地的情况,比如权限验证的token、用户信息、埋点计数、客户配置的皮肤信息或语言种类等,我们可以暂存一下避免浏览器不必要的请求和客户多余操作,给客户使用带来方便。

上一篇讲了JavaScript浏览器端数据存储方案之Cookie篇,这篇文章主要介绍localStorage和sessionStorage。

HTML5中 Web Storage 的出现,主要是为了弥补使用 Cookie 作为本地存储的不足。Cookie 存储的数据量非常小,而且数据会自动携带到请求头里,但服务器端可能并不关心这些数据,所以会造成带宽的浪费。

Web Storage 提供了两个存储对象:localStorage 和 sessionStorage

sessionStorage 存储的数据仅在本次会话有用,会话结束后会自动失效,而且数据仅在当前窗口有效,同一源下新窗口也访问不到其他窗口基于sessionStorage 存储的数据。也是由于这些特性,导致 sessionStorage 的使用场景会比较少。

localStorage 可以永久存储,而且同源下数据多窗口也能共享。看起来很美好,但 localStorage 也有短板,绝大多数浏览器有 5M 的大小限制。但是这不足以成为大家使用 localStorage 的障碍,要知道 Cookie 只有 4K 的大小,多了一千多倍

localStorage 的基本使用

localStorage.setItem("b","isaac");//设置b为"isaac"
var b = localStorage.getItem("b");//获取b的值,为"isaac"
var a = localStorage.key(0); // 获取第0个数据项的键名,此处即为“b”
localStorage.removeItem("b");//清除c的值
localStorage.clear();//清除当前域名下的所有localstorage数据

这里和大家分享一段我们在VUE项目中封装使用的localStorage,大家可以借鉴实现的思路

有两点需要注意一下。在 setItem 时,可能会达到大小限制,最好加上错误捕捉

另外在存储容量快满时,会造成 getItem 性能急剧下降。我们下面看看 localStorage 有哪些脑洞大开的用法

缓存静态文件

你不禁要问,HTTP 协议不是本来就支持缓存文件吗,为什么还要使用 localStorage 来缓存?为了可编程化,通俗一点说就是把命运握在自己手中。

HTTP 协议的缓存,可以由用户浏览器清除或禁用缓存,也可以由 Web 服务器设置过期时间或不缓存。对于前端工程师,这更像是一个黑盒,想要决定文件是访问缓存还是访问远程显得有些力不从心了。

使用 localStorage 控制文件缓存的方式有两种:

  1. 使用 Loader 加载静态文件
  2. 借助服务器端将静态文件 inline 化

这两种方式一般都会提前做好缓存过期策略,通常是使用版本号来控制,下面还会细讲。否则文件新版上线,用户客户端还是旧版,这就麻烦大了,而且这类问题,还不好调试不好重现。

使用 Loader 加载静态文件

由于请求都是动态发出的,所以可以对请求拦截处理。大致流程如下:

  1. 查看请求的文件 url 是否有缓存到 localStorage
  2. 如果没有,到第 2 大步
  3. 如果有,判断文件是否过期或版本号是否匹配
  4. 过期或不匹配,到第 2 大步
  5. 文件内容有效,到第 4 大步
  6. 请求远程文件
  7. 缓存最新文件内容
  8. 执行文件内容

这个方式有个开源库:basket.js(可以在github上访问)

借助服务器端将静态文件 inline 化

这个方式比上面那种更进一步,在第一次响应时把需要放入 localStorage 的文件都内联进 html 中,后面每次响应只要文件版本没有变化,都是渲染一段从 localStorage 加载该文件的代码。这样做的好处是可以有效减少请求次数,即使是第一次。

版本号不匹配(版本号可记在 Cookie 中,第一次访问没有版本号),服务端响应内容:

<script> 
function script2ls(id) { 
 var script = document.getElementById(id);
 if (script) {
 localStorage[id] = script.innerHTML;
 }
}
</script>
<script id="jquery.js">...jquery source code...</script> 
<script>script2ls('jquery.js')</script> 

版本号匹配,服务端响应内容:

<script> 
function ls2script(id) { 
 var script = document.createElement('script');
 script.text = localStorage[id];
 document.head.appendChild(script);
}
</script>
<script>ls2script('jquery.js')</script> 

不过使用 localStorage 缓存文件会有 XSS 的风险,而且造成的伤害可能是永久的

同源窗口通信

你可能不禁又要问,不是有 postMessage 吗?没错 postMessage 确实可以用于窗口或 iframe 间通信,但是前提是你必须拿到打开新窗或 iframe 的句柄对象:

var popup = window.open(...popup details...); 
popup.postMessage("hello there!", "http://example.com"); 

这样在新窗中再打开新窗,似乎就不好传递消息了。

你可能还想问,为什么要在窗口间通信?好问题,没有应用场景的技术都是耍流氓。像多窗口共用的一些组件,而且对数据实时同步都有较高要求的都会是这个技术的应用场景。比如通知中心上面的未读数量,两个窗口,A 窗口更新为 8,切到 B 窗口还是 9,这就造成了体验不一致,这个例子可能还觉得无关痛痒;再比如购物车,两个产品窗口,A 窗口添加到购物车,切到 B 窗口添加到购物车,发现没有 A 添加的产品,这样就比较严重了。这当然也可以通过每个窗口都与后台建立连接来更新,但用户如果开十几个窗口就开销大了。

有了同源窗口通信,我们就可以只有一个窗口与后台建立连接,收到更新后,广播给其他窗口就可以。说了这么多,实现原理是怎样的呢?

其实原理也简单,每次 localStorage 中有任何变动都会触发一个 storage 事件,所有窗口都监听这个事件,一旦有窗口更新 localStorage,其他窗口都会收到通知,根据事件中的 key 把不关心的变动过滤掉。原理是很简单,但是要实现一套完整的广播机制还是有些复杂,你需要:

  • 管理好每个窗口的唯一 ID
  • 防止消息重复
  • 防止消息发给不关心的窗口
  • 窗口心跳 keep alive
  • 主窗口选举
  • ...

不用担心,已经有了不错的开源实现:diy/intercom.js、tejacques/crosstab

作为前端 DB 的存储介质

你可能不满足于用键值对保存数据,你还想保存更复杂的数据结构。

灵活存取 json 格式的数据:typicode/lowdb

通过 sql 对数据 CURD 操作:agershun/alasql

表单自动持久化

在填写表单时,遇到浏览器奔溃或者误操作导致填写内容丢失,此刻用户的内心也应该是奔溃的。误操作还可以加一个 beforeunload 事件,在关闭浏览器或跳出当前页前提醒一下用户。那浏览器崩溃呢,将数据变更实时保存到后台,这样似乎开销很大,实时保存到 localStorage 是个不错的解决方案,真巧,也有一个开源实现:simsalabim/sisyphus

TML5是最新的HTML标准,是专门为承载丰富的web内容而设计的,并且无需额外插件。接下来小编就为大家讲解HTML5的5种存储方式:

1.本地存储localstorage

存储方式:

以键值对(Key-Value)的方式存储,永久存储,永不失效,除非手动删除。

大小:

每个域名5M

支持情况:

注意:IE9 localStorage不支持本地文件,需要将项目署到服务器,才可以支持!

if(window.localStorage){ alert('This browser supports localStorage'); }else{ alert('This browser does NOT support localStorage'); }

常用的API:

getItem //取记录

setIten//设置记录

removeItem//移除记录

key//取key所对应的值

clear//清除记录

存储的内容:

数组,图片,json,样式,脚本。。。(只要是能序列化成字符串的内容都可以存储)

2.本地存储sessionstorage

HTML5 的本地存储 API 中的 localStorage 与 sessionStorage 在使用方法上是相同的,区别在于 sessionStorage 在关闭页面后即被清空,而 localStorage 则会一直保存。

3.离线缓存(application cache)

本地缓存应用所需的文件

使用方法:

①配置manifest文件

页面上:

...

Manifest 文件:

manifest 文件是简单的文本文件,它告知浏览器被缓存的内容(以及不缓存的内容)。

manifest 文件可分为三个部分:

①CACHE MANIFEST - 在此标题下列出的文件将在首次下载后进行缓存

②NETWORK - 在此标题下列出的文件需要与服务器的连接,且不会被缓存

③FALLBACK - 在此标题下列出的文件规定当页面无法访问时的回退页面(比如 404 页面)

完整demo:

CACHE MANIFEST # 2016-07-24 v1.0.0 /theme.css /main.js NETWORK: login.jsp FALLBACK: /html/ /offline.html

服务器上:manifest文件需要配置正确的MIME-type,即 "text/cache-manifest"。

如Tomcat:

manifest text/cache-manifest

常用API:

核心是applicationCache对象,有个status属性,表示应用缓存的当前状态:

0(UNCACHED) : 无缓存, 即没有与页面相关的应用缓存

1(IDLE) : 闲置,即应用缓存未得到更新

2 (CHECKING) : 检查中,即正在下载描述文件并检查更新

3 (DOWNLOADING) : 下载中,即应用缓存正在下载描述文件中指定的资源

4 (UPDATEREADY) : 更新完成,所有资源都已下载完毕

5 (IDLE) : 废弃,即应用缓存的描述文件已经不存在了,因此页面无法再访问应用缓存

相关的事件:

表示应用缓存状态的改变:

checking : 在浏览器为应用缓存查找更新时触发

error : 在检查更新或下载资源期间发送错误时触发

noupdate : 在检查描述文件发现文件无变化时触发

downloading : 在开始下载应用缓存资源时触发

progress:在文件下载应用缓存的过程中持续不断地下载地触发

updateready : 在页面新的应用缓存下载完毕触发

cached : 在应用缓存完整可用时触发

Application Cache的三个优势:

① 离线浏览

② 提升页面载入速度

③ 降低服务器压力

注意事项:

1. 浏览器对缓存数据的容量限制可能不太一样(某些浏览器设置的限制是每个站点 5MB)

2. 如果manifest文件,或者内部列举的某一个文件不能正常下载,整个更新过程将视为失败,浏览器继续全部使用老的缓存

3. 引用manifest的html必须与manifest文件同源,在同一个域下

4. 浏览器会自动缓存引用manifest文件的HTML文件,这就导致如果改了HTML内容,也需要更新版本才能做到更新。

5. manifest文件中CACHE则与NETWORK,FALLBACK的位置顺序没有关系,如果是隐式声明需要在最前面

6. FALLBACK中的资源必须和manifest文件同源

7. 更新完版本后,必须刷新一次才会启动新版本(会出现重刷一次页面的情况),需要添加监听版本事件。

8. 站点中的其他页面即使没有设置manifest属性,请求的资源如果在缓存中也从缓存中访问

9. 当manifest文件发生改变时,资源请求本身也会触发更新

离线缓存与传统浏览器缓存区别:

1. 离线缓存是针对整个应用,浏览器缓存是单个文件

2. 离线缓存断网了还是可以打开页面,浏览器缓存不行

3. 离线缓存可以主动通知浏览器更新资源

4.Web SQL

关系数据库,通过SQL语句访问

Web SQL 数据库 API 并不是 HTML5 规范的一部分,但是它是一个独立的规范,引入了一组使用 SQL 操作客户端数据库的 APIs。

支持情况:

Web SQL 数据库可以在最新版的 Safari, Chrome 和 Opera 浏览器中工作。

核心方法:

①openDatabase:这个方法使用现有的数据库或者新建的数据库创建一个数据库对象。

②transaction:这个方法让我们能够控制一个事务,以及基于这种情况执行提交或者回滚。

③executeSql:这个方法用于执行实际的 SQL 查询。

打开数据库:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024,fn); //openDatabase() 方法对应的五个参数分别为:数据库名称、版本号、描述文本、数据库大小、创建回调

执行查询操作:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024); db.transaction(function (tx) { tx.executeSql('CREATE TABLE IF NOT EXISTS WIN (id unique, name)'); });

插入数据: 

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024); db.transaction(function (tx) { tx.executeSql('CREATE TABLE IF NOT EXISTS WIN (id unique, name)'); tx.executeSql('INSERT INTO WIN (id, name) VALUES (1, "winty")'); tx.executeSql('INSERT INTO WIN (id, name) VALUES (2, "LuckyWinty")'); });

读取数据:

db.transaction(function (tx) { tx.executeSql('SELECT * FROM WIN', [], function (tx, results) { var len = results.rows.length, i; msg = "

查询记录条数: " + len + "

"; document.querySelector('#status').innerHTML += msg; for (i = 0; i <>

alert(results.rows.item(i).name ); } }, null); });

由这些操作可以看出,基本上都是用SQL语句进行数据库的相关操作,如果你会MySQL的话,这个应该比较容易用。

5.IndexedDB

索引数据库 (IndexedDB) API(作为 HTML5 的一部分)对创建具有丰富本地存储数据的数据密集型的离线 HTML5 Web 应用程序很有用。同时它还有助于本地缓存数据,使传统在线 Web 应用程序(比如移动 Web 应用程序)能够更快地运行和响应。