月度归档:2011年12月

将DataTable导出为Excel (XML Spreadsheet)

结果导出成Excel文件. 用几个不同的办法做了出来. 最后部署时发现: 用COM+组件行不通, 服务器上没装OFFICE; 用OWC也不行, 因为服务器是64位的, OWC不支持; 导出成Excel2003支持的xml, 这招最灵活, 也是一个简单可行的办法.

首先用Excel 2003新建一个空白Wookbook, 然后保存为XML Spreadsheet. 然后打开看它生成的XML代码就能了解XML Spreadsheet的基本结构了.
我先把DataTable生成XML数据, 再使用一个XLS把XML数据转换成XML Spreadsheet的结构.

 

using System;using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Xml;
using System.Xml.Xsl;
namespace AProject
{
    public class ExcelHelper
    {
        public static string ExportAsTempFile(DataTable dataTable, bool appendColumnNames)
        {
            if(dataTable == null)
            {
                return null;
            }

            return ExportAsTempFile(new DataTable[] { dataTable }, appendColumnNames);
        }

        public static string ExportAsTempFile(DataSet dataSet, bool appendColumnNames)
        {
            string fileName = Path.GetTempFileName();

            if(ExportFile(dataSet, fileName, appendColumnNames))
            {
                return fileName;
            }

            return null;
        }

        public static string ExportAsTempFile(DataTable[] dataTables, bool appendColumnNames)
        {
            string fileName = Path.GetTempFileName();

            if(ExportFile(dataTables, fileName, appendColumnNames))
            {
                return fileName;
            }

            return null;
        }

        public static bool ExportFile(DataTable dataTable, string fileName, bool appendColumnNames)
        {
            if(dataTable == null)
            {
                return false;
            }

            return ExportFile(new DataTable[] { dataTable }, fileName, appendColumnNames);
        }

        public static bool ExportFile(DataSet dataSet, string fileName, bool appendColumnNames)
        {
            if(dataSet == null)
            {
                return false;
            }

            DataTable[] dataTables = new DataTable[dataSet.Tables.Count];
            dataSet.Tables.CopyTo(dataTables, 0);
            return ExportFile(dataTables, fileName, appendColumnNames);
        }

        public static bool ExportFile(DataTable[] dataTables, string fileName, bool appendColumnNames)
        {
            if(dataTables == null || dataTables.Length == 0 || string.IsNullOrEmpty(fileName))
            {
                return false;
            }

            XmlDocument xmlDoc = GetXmlDataTables(dataTables, appendColumnNames);
            XmlDocument xlsDoc = TransformXml(xmlDoc);

            try             {
                xlsDoc.Save(fileName);
                return true;
            }
            catch             {
                return false;
            }
        }

        private static XmlDocument GetXmlDataTables(DataTable[] dataTables, bool appendColumnNames)
        {
            if(dataTables == null)
            {
                return null;
            }

            XmlDocument xmlDoc = new XmlDocument();
            XmlElement rootNode = xmlDoc.CreateElement("DTS");
            XmlElement tableNode;
            XmlElement rowNode;
            XmlElement colNode;
            DataTable dt;

            for(int i = 0; i < dataTables.Length; i++)
            {
                dt = dataTables[i];

                if(dt == null)
                {
                    break;
                }

                if(dt.TableName.Trim() == string.Empty)
                {
                    dt.TableName = "DataTable" + i.ToString();
                }

                tableNode = xmlDoc.CreateElement("DT");
                tableNode.SetAttribute("N", dt.TableName);

                if(appendColumnNames)
                {
                    rowNode = xmlDoc.CreateElement("DR");

                    foreach(DataColumn dc in dt.Columns)
                    {
                        colNode = xmlDoc.CreateElement("DC");
                        colNode.SetAttribute("N", dc.ColumnName);
                        colNode.SetAttribute("T", "String");
                        colNode.AppendChild(xmlDoc.CreateTextNode(dc.ColumnName));
                        rowNode.AppendChild(colNode);
                    }

                    tableNode.AppendChild(rowNode);
                }


                foreach(DataRow dr in dt.Rows)
                {
                    rowNode = xmlDoc.CreateElement("DR");

                    foreach(DataColumn dc in dt.Columns)
                    {
                        colNode = xmlDoc.CreateElement("DC");
                        colNode.SetAttribute("N", dc.ColumnName);
                        colNode.SetAttribute("T", GetDataType(dc.DataType));
colNode.AppendChild(xmlDoc.CreateTextNode(GetTextValue(dc.DataType, dr[dc.ColumnName])));
                        rowNode.AppendChild(colNode);
                    }

                    tableNode.AppendChild(rowNode);
                }

                rootNode.AppendChild(tableNode);
            }

            xmlDoc.AppendChild(rootNode);
            return xmlDoc;
        }

        private static string GetTextValue(Type type, object value)
        {
            string text;

            if(type == typeof(DateTime))
            {
                text = ((DateTime)value).ToString("yyyy-MM-ddTHH:mm:ssZ");
            }
            else             {
                text = value.ToString();
            }

            return text;
        }

        private static string GetDataType(Type type)
        {
            string dataType;

            if(type == typeof(string))
            {
                dataType = "String";
            }
            else if(type == typeof(DateTime))
            {
                dataType = "DateTime";
            }
            else if(type == typeof(bool))
            {
                dataType = "Boolean";
            }
            else             {
                dataType = "Number";
            }

            return dataType;
        }

        private static XmlDocument TransformXml(XmlDocument xmlDoc)
        {
            XmlDocument xlsDoc = new XmlDocument();
            XslCompiledTransform xslt = new XslCompiledTransform();
            Assembly assembly = Assembly.GetExecutingAssembly();

            using(Stream s = assembly.GetManifestResourceStream("AProject.Resources.XmlSpreadsheet.xsl"))
            {
                if(s != null)
                {
                    xslt.Load(XmlReader.Create(s));
                    MemoryStream output = new MemoryStream();
                    XmlTextWriter xmlWriter = new XmlTextWriter(output, Encoding.UTF8);
                    xslt.Transform(xmlDoc, xmlWriter);
                    output.Position = 0;
                    xlsDoc.Load(output);
                    xlsDoc.PrependChild(xlsDoc.CreateXmlDeclaration("1.0", null, null));
                    output = null;
                }
            }

            return xlsDoc;
        }
    }
}

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <xsl:output method='xml' version='1.0'/>
    <xsl:template match="DTS">
        <ss:Workbook >
            <ss:Styles>
                <ss:Style ss:ID="Default">
                    <ss:NumberFormat ss:Format="General"/>
                </ss:Style>
                <ss:Style ss:ID="DateTime">
                    <ss:NumberFormat ss:Format="General Date"/>
                </ss:Style>
            </ss:Styles>
            <xsl:apply-templates select="DT" />
        </ss:Workbook>
    </xsl:template>

    <xsl:template match="DT">
        <ss:Worksheet>
            <xsl:attribute  name="ss:Name">
                <xsl:value-of select="@N"/>
            </xsl:attribute>
            <ss:Table>
                <xsl:apply-templates select="DR" />
            </ss:Table>
        </ss:Worksheet>
    </xsl:template>

    <xsl:template match="DR">
        <ss:Row>
            <xsl:apply-templates select="DC" />
        </ss:Row>
    </xsl:template>

    <xsl:template match="DC">
        <ss:Cell>
            <xsl:choose>
                <xsl:when test="@T = 'DateTime'">
                    <xsl:attribute name="ss:StyleID">
                        <xsl:text>DateTime</xsl:text>
                    </xsl:attribute>
                </xsl:when>
            </xsl:choose>
            <ss:Data>
                <xsl:attribute name="ss:Type">
                    <xsl:value-of select="@T"/>
                </xsl:attribute>
                <xsl:choose>
                    <xsl:when test="@T = 'String'">
                        <xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
                        <xsl:value-of  select="."/>
                        <xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:value-of select="."/>
                    </xsl:otherwise>
                </xsl:choose>
            </ss:Data>
        </ss:Cell>
    </xsl:template>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

public class DataSetToExcel
    {
        /// <summary>
        /// 生成EXCEL
        /// </summary>
        public DataSetToExcel()
        { }

        /// <summary>创建Excel的xml文件
        ///
        /// </summary>
        /// <param name="DS">数据</param>
        /// <param name="fileName">文件名</param>
        public DataSetToExcel(DataSet DS, string fileName)
        {
            CreateXmlExcelFile(DS, fileName);
        }
        /// <summary> 创建Excel的xml文件
        ///
        /// </summary>
        /// <param name="DS">数据</param>
        /// <param name="fileName">文件名</param>
        public void CreateXmlExcelFile(DataSet DS, string fileName)
        {
            string foldName = fileName;

            #region 同名文件删除
            FileInfo FI = new FileInfo(foldName);
            if (FI.Exists)
            {
                FI.Delete();
            }
            #endregion

            #region 创建XML对象,二维数组写入并保存
            //创建一个XML文档
            
            XmlDocument xmldoc = new XmlDocument();
            XmlNode xmldc = xmldoc.CreateNode(XmlNodeType.XmlDeclaration, "", "");

            xmldoc.AppendChild(xmldc);

            XmlProcessingInstruction xmlpi = xmldoc.CreateProcessingInstruction("mso-application", "progid=/"Excel.Sheet/"");
            xmldoc.AppendChild(xmlpi);

            //创建一个工作簿
            XmlElement xmlwb = xmldoc.CreateElement("Workbook");
            xmlwb.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
            xmlwb.SetAttribute("xmlns:o", "urn:schemas-microsoft-com:office:office");
            xmlwb.SetAttribute("xmlns:x", "urn:schemas-microsoft-com:office:excel");
            xmlwb.SetAttribute("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
            xmlwb.SetAttribute("xmlns:html", "http://www.w3.org/TR/REC-html40");

            xmldoc.AppendChild(xmlwb);

            XmlElement xmlsts = xmldoc.CreateElement("Styles");
            xmlwb.AppendChild(xmlsts);

            XmlElement xmlst = xmldoc.CreateElement("Style");
            XmlAttribute xmlarr = xmldoc.CreateAttribute("ss:ID", "urn:schemas-microsoft-com:office:spreadsheet");
            xmlarr.Value = "s21";
            xmlst.Attributes.Append(xmlarr);
            xmlsts.AppendChild(xmlst);

            XmlElement xmlnf = xmldoc.CreateElement("NumberFormat");
            xmlarr = xmldoc.CreateAttribute("ss:Format", "urn:schemas-microsoft-com:office:spreadsheet");
            xmlarr.Value = "Short Date";
            xmlnf.Attributes.Append(xmlarr);
            xmlst.AppendChild(xmlnf);

            foreach (DataTable DT in DS.Tables)
            {
                XmlElement xmlws = xmldoc.CreateElement("Worksheet");
                xmlarr = xmldoc.CreateAttribute("ss:Name", "urn:schemas-microsoft-com:office:spreadsheet");
                xmlarr.Value = DT.TableName.Replace("/", "");
                xmlws.Attributes.Append(xmlarr);
                xmlwb.AppendChild(xmlws);

                XmlElement xmltb = xmldoc.CreateElement("Table");
                xmlws.AppendChild(xmltb);

                XmlElement xmlrw = xmldoc.CreateElement("Row");
                xmltb.AppendChild(xmlrw);
                foreach (DataColumn DC in DT.Columns)
                {
                    XmlElement xmlcl = xmldoc.CreateElement("Cell");
                    xmlrw.AppendChild(xmlcl);

                    XmlElement xmldt = xmldoc.CreateElement("Data");
                    xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                    xmlarr.Value = "String";
                    xmldt.Attributes.Append(xmlarr);

                    XmlText xmltxt = xmldoc.CreateTextNode(DC.ColumnName);
                    xmldt.AppendChild(xmltxt);

                    xmlcl.AppendChild(xmldt);
                }

                foreach (DataRow DR in DT.Rows)
                {
                    xmlrw = xmldoc.CreateElement("Row");
                    xmltb.AppendChild(xmlrw);
                    foreach (DataColumn DC in DT.Columns)
                    {
                        XmlElement xmlcl = xmldoc.CreateElement("Cell");
                        XmlElement xmldt = xmldoc.CreateElement("Data");
                        XmlText xmltxt;
                        switch (DC.DataType.Name.ToLower())
                        {
                            case "int32":
                            case "decimal":
                            case "double":

                                xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                                xmlarr.Value = "Number";
                                xmldt.Attributes.Append(xmlarr);

                                xmltxt = xmldoc.CreateTextNode(DR[DC.ColumnName].ToString().Trim());
                                break;
                            case "datetime":
                            case "smalldatetime":
                                xmlarr = xmldoc.CreateAttribute("ss:StyleID", "urn:schemas-microsoft-com:office:spreadsheet");
                                xmlarr.Value = "s21";
                                xmlcl.Attributes.Append(xmlarr);

                                xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                                xmlarr.Value = "DateTime";
                                xmldt.Attributes.Append(xmlarr);

                                xmltxt = xmldoc.CreateTextNode(Convert.ToDateTime(DR[DC.ColumnName]).ToString("yyyy-MM-ddTHH:mm:ss").Trim());

                                break;
                            default:

                                xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                                xmlarr.Value = "String";
                                xmldt.Attributes.Append(xmlarr);

                                xmltxt = xmldoc.CreateTextNode(DR[DC.ColumnName].ToString().Trim());
                                break;
                        }

                        xmldt.AppendChild(xmltxt);

                        xmlcl.AppendChild(xmldt);
                        xmlrw.AppendChild(xmlcl);
                    }
                }

            }
            xmldoc.Save(foldName);
            System.Diagnostics.Process.Start(foldName);

            #endregion
        }

    }

C#实现md5加密 sha1加密 sha256加密 sha384加密 sha512加密 des加密解密

后台一直用md5加密,一天群里人问,除了MD5还有其它的加密方法吗?当时只知道还有个SHA,但怎么实现什么的都不清楚,于是当网上找了下,把几种常见的加密方法都整理了下,用winform写了个程序,如图:
点击查看原图点击查看原图

关键代码

using System.Security;
using System.Security.Cryptography;
using System.Web;
using System.IO;

        //MD5       不区分大小写的
        //type 类型,16位还是32位,16位就是取32位的第8到16位
        public string DoMd5Encode(string pwd,string type)
        {
            byte[] result = Encoding.Default.GetBytes(pwd);
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            byte[] output = md5.ComputeHash(result);
            if (type == "16")
                return BitConverter.ToString(output).Replace("-", "").ToLower().Substring(8,16);   
            else
                return BitConverter.ToString(output).Replace("-", "").ToLower();   
           
        }

        /// <summary>
        /// 对字符串进行SHA1加密
        /// </summary>
        /// <param name="strIN">需要加密的字符串</param>
        /// <returns>密文</returns>
        public string SHA1_Encrypt(string Source_String)
        {
            byte[] StrRes = Encoding.Default.GetBytes(Source_String);
            HashAlgorithm iSHA = new SHA1CryptoServiceProvider();
            StrRes = iSHA.ComputeHash(StrRes);
            StringBuilder EnText = new StringBuilder();
            foreach (byte iByte in StrRes)
            {
                EnText.AppendFormat("{0:x2}", iByte);
            }
            return EnText.ToString();
        }

        /// <summary>
        /// SHA256加密,不可逆转
        /// </summary>
        /// <param name="str">string str:被加密的字符串</param>
        /// <returns>返回加密后的字符串</returns>
        private string SHA256Encrypt(string str)
        {
            System.Security.Cryptography.SHA256 s256 = new System.Security.Cryptography.SHA256Managed();
            byte[] byte1;
            byte1 = s256.ComputeHash(Encoding.Default.GetBytes(str));
            s256.Clear();
            return Convert.ToBase64String(byte1);
        }

       /// <summary>
        /// SHA384加密,不可逆转
        /// </summary>
        /// <param name="str">string str:被加密的字符串</param>
        /// <returns>返回加密后的字符串</returns>
        private string SHA384Encrypt(string str)
        {
            System.Security.Cryptography.SHA384 s384 = new System.Security.Cryptography.SHA384Managed();
            byte[] byte1;
            byte1 = s384.ComputeHash(Encoding.Default.GetBytes(str));
            s384.Clear();
            return Convert.ToBase64String(byte1);
        }

        /// <summary>
        /// SHA512加密,不可逆转
        /// </summary>
        /// <param name="str">string str:被加密的字符串</param>
        /// <returns>返回加密后的字符串</returns>
        private string SHA512Encrypt(string str)
        {
            System.Security.Cryptography.SHA512 s512 = new System.Security.Cryptography.SHA512Managed();
            byte[] byte1;
            byte1 = s512.ComputeHash(Encoding.Default.GetBytes(str));
            s512.Clear();
            return Convert.ToBase64String(byte1);
        }

        //默认密钥向量
        private byte[] Keys = { 0xEF, 0xAB, 0x56, 0x78, 0x90, 0x34, 0xCD, 0x12 };
        /// <summary>
        /// DES加密字符串
        /// </summary>
        /// <param name="encryptString">待加密的字符串</param>
        /// <param name="encryptKey">加密密钥,要求为8位</param>
        /// <returns>加密成功返回加密后的字符串,失败返回源串</returns>
        public string EncryptDES(string encryptString, string encryptKey)
        {
            try
            {
                byte[] rgbKey = Encoding.UTF8.GetBytes(encryptKey.Substring(0, 8));
                byte[] rgbIV = Keys;
                byte[] inputByteArray = Encoding.UTF8.GetBytes(encryptString);
                DESCryptoServiceProvider dCSP = new DESCryptoServiceProvider();
                MemoryStream mStream = new MemoryStream();
                CryptoStream cStream = new CryptoStream(mStream, dCSP.CreateEncryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
                cStream.Write(inputByteArray, 0, inputByteArray.Length);
                cStream.FlushFinalBlock();
                return Convert.ToBase64String(mStream.ToArray());
            }
            catch
            {
                return encryptString;
            }
        }

        /// <summary>
        /// DES解密字符串
        /// </summary>
        /// <param name="decryptString">待解密的字符串</param>
        /// <param name="decryptKey">解密密钥,要求为8位,和加密密钥相同</param>
        /// <returns>解密成功返回解密后的字符串,失败返源串</returns>
        public string DecryptDES(string decryptString, string decryptKey)
        {
            try
            {
                byte[] rgbKey = Encoding.UTF8.GetBytes(decryptKey.Substring(0, 8));
                byte[] rgbIV = Keys;
                byte[] inputByteArray = Convert.FromBase64String(decryptString);
                DESCryptoServiceProvider DCSP = new DESCryptoServiceProvider();
                MemoryStream mStream = new MemoryStream();
                CryptoStream cStream = new CryptoStream(mStream, DCSP.CreateDecryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
                cStream.Write(inputByteArray, 0, inputByteArray.Length);
                cStream.FlushFinalBlock();
                return Encoding.UTF8.GetString(mStream.ToArray());
            }
            catch
            {
                return decryptString;
            }
        }

hMailSer 反垃圾邮件及病毒防护

服务器正常运行后,可以配置垃圾邮件过滤和病毒邮件防护了。

1、配置垃圾邮件过滤

hmailserver的设置=>反垃圾邮件中,设置垃圾邮件门槛,默认为5,可以设置添加[SPAM]字段至邮件主题。设置垃圾邮件删除门槛,根据自己需求,若垃圾邮件很多,建议设10-15. 也可以将需要的邮箱或域添加到白名单中。

点击查看原图垃圾邮件测试选项中,可以选择检测SPF、校验命令主机名、校验发送者是否有DNS-MX记录

点击查看原图如要更严谨的过滤垃圾邮件,需要用到一个外部工具Spam Assassin for Win32,官方网站http://spamassassin.apache.org/,该程序是一个免费的功能强大的垃圾邮件过滤工具。支持网上垃圾邮件数据库DCC(http://www.rhyolite.com/anti-spam/dcc/)和Cloudmark(http://razor.sf.net/)的垃圾邮件列表。

使用很简单,运行spamd.exe即可,该程序无运行界面工作在后台,通过任务管理器可以看到,退出也需要用任务管理器结束。点击Spam Assassin,选择使用Spam Assassin,评分也选择使用Spam Assassin。点击测试,检查Spam Assassin运行是否正常。

Spam Assassin for Win32下载地址:http://disk.epcboy.com/viewfile.php?file_id=23

点击查看原图

下面是测试反馈界面,说明程序运行正常:

点击查看原图        在反垃圾邮件中有RBLSURBL列表,RBL是英文Real time Black hole List的缩写,即实时黑名单列表。该列表实时更新,因此能够保证过滤最新垃圾邮件。默认有二个RBL服务器zen.spamhaus.orgbl.spamcop.net,再手动添加一个国内反垃圾邮件组织cbl.anti-spam.org.cn服务器,全部启用。给邮件评分,评分越高越接近垃圾邮件,若垃圾邮件比较多,将评分设置的高些,如3-5.

点击查看原图点击查看原图

保存后你的邮件服务器就拥有很强劲的实时更新的垃圾邮件过滤功能了。

 


2、配置防病毒邮件功能。

在常规中根据需要选择发现病毒时的操作。配置病毒扫描软件,我用的是hMailServer推荐的Clam Win免费杀毒软件。

点击查看原图Clam Win免费杀毒软件,号称最低功耗的“静音杀毒软件”。它占用资源非常小,拥有排程扫描、在线更新病毒码、及时侦测等功能,而且是一款基于 GPL 许可证的自由软件。 hMailServer对Clam Win非常友好,安装好Clam Win后点击Auto-Detect按钮即可自动加入参数,无需手工填写。

Clam Win 0.97.2杀毒软件,下载地址:http://disk.epcboy.com/viewfile.php?file_id=14

点击查看原图这样就可以使hMailServer拥有病毒邮件防护功能了。

hMailServer收到病毒邮件会自动删除并发邮件通知收件人,内容如下:

点击查看原图至此,hMailServer的垃圾邮件过滤及病毒邮件防护功能配置完成,一台完整的功能强劲的邮件服务器搭建完毕。

标准数字格式字符串用法说明及举例

    // {index[,alignment][:format]}
   
// index     - 从零开始的整数,指示对象列表中要格式化的元素。
   
// alignment - 可选整数,指示最小宽度,不足部分用空格填充。负数指示左对齐,正数指示右对齐。
   
// format    - 格式字符串。标准数字格式字符串采取“Axx”形式,A为单个字母, 说明如下:
   
//    c - 货币,     xx指示小数位数
   
//    d - 十进制,   xx指示有效位数, 不足用零填充, 只能用于整数
   
//    x - 十六进制, 用法同d
   
//    e - 指数,     xx指示小数位数
   
//    f - 固定点,   xx指示小数位数
   
//    n - 数字,     输出千位分隔符, 其余同f
   
//    g - 常规,     xx指示有效位数, 使用e或f中较短者
   
//    p - 百分比,   xx指示小数位数
  
// 例如以下语句的输出为: "|¥-5.00|-05| -05|-5.0 |-500%|12345.7|12,345.7|"

mysql 5.0存储过程学习总结

一.创建存储过程

1.基本语法:

create procedure sp_name()
begin
.........
end

2.参数传递

二.调用存储过程

1.基本语法:call sp_name()
注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递

三.删除存储过程

1.基本语法:
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

四.区块,条件,循环

1.区块定义,常用
begin
......
end;
也可以给区块起别名,如:
lable:begin
...........
end lable;
可以用leave lable;跳出区块,执行区块以后的代码
2.条件语句

if 条件 then
statement
else
statement
end if;

3.循环语句
(1).while循环

[label:] WHILE expression DO

statements

END WHILE [label] ;

(2).loop循环

[label:] LOOP

statements

END LOOP [label];

(3).repeat until循环

[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;

五.其他常用命令

1.show procedure status
显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等
2.show create procedure sp_name
显示某一个存储过程的详细信息

继续阅读