一步一步mysql(四)之触发器与...-让普通人用excel读取oracle数据...-DECODE函数的用法和实际运用

当前位置: 数据库>其它

本页文章导读:

▪一步一步mysql(四)之触发器与存储过程触发器 Mysql的触发器和存储过程一样，都是嵌入到mysql的一段程序。触发器是mysql5新增的功能，目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本，很多程序比如fc-st.........

▪让普通人用excel读取oracle数据库表的通用办法作者：iamlaosong Excel通过ADO方式连接到Oracle并操作Oracle给我们这些编程能力不强的人带来很大的福音，结合着Excel的数据处理与图表制作，就能很轻松地处理一些常规工作。日常工作中需要查.........

▪DECODE函数的用法和实际运用在公司呆了一段时间，发现真正的利器是sql，要想在企业里得到大家的认可，有很好的sql编程功底是很重要很重要的，为了不断提高自己的sql语句编写能力，我将不定期坚持写sql的博客，其中.........

[1]一步一步mysql(四)之触发器与存储过程

来源: 互联网发布时间: 2013-11-07

触发器

Mysql的触发器和存储过程一样，都是嵌入到mysql的一段程序。触发器是mysql5新增的功能，目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本，很多程序比如fc-star管理端，sfrd(das)，dorado都会用到触发器程序，实现对于数据库增、删、改引起事件的关联操作。

1.语法

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
· INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
· UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
· DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。例如，关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。 2.说明

　　现在，重新注意到trigger_time和trigger_event，上文说过， trigger_time可以用before和after替换，表示触发器程序的执行在sql执行的前还是后；trigger_event可以用 insert，update，delete替换，表示触发器程序在什么类型的sql下会被触发。

　　在一个表上最多建立6个触发器，即1）before insert型，2）before update型，3）before delete型，4）after insert型，5）after update型，6）after delete型。

　　这里需要说明一下，上述实验所使用的mysql引擎是innodb，innodb引擎也是目前线上凤巢系统、北斗系统以及哥伦布系统所使用的引擎，在 innodb上所建立的表是事务性表，也就是事务安全的。“对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚”（摘自mysql使用手册）。因而，即使语句失败，失败之前所作的任何更改依然有效，也就是说，对于 innodb引擎上的数据表，如果触发器中的sql或引发触发器的sql执行失效，则事务回滚，所有操作会失效。

3.实例

参考：http://hi.baidu.com/avauntage/item/023ac310391b1a011894ecd2

4.出现的问题

存储过程 1.介绍

　　我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
　　一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

2.优势

(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

3.语法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

或具体一些：

CREATE PROCEDURE存储过程名 (参数列表)
   BEGIN
         SQL语句代码块
END

eg:

delimiter |
create procedure  proce_name(INOUT testpi int )
begin
set testpi=90 ;
end ;
| 
delimiter ;

delimiter ｜是语句定界符，当使用delimiter命令时，你应该避免使用反斜杠(‘"’)字符，因为那是MySQL的转义字符。

A. ALTER PROCEDURE （修改） ALTER PROCEDURE 存储过程名SQL语句代码块
这个语句可以被用来改变一个存储程序的特征。
B. DROP PROCEDURE （删除） DROP PROCEDURE IF EXISTS存储过程名
eg:DROP PROCEDURE IF EXISTS proc_employee (proc_employee 存储过程名)
这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
C. SHOW CREATE PROCEDURE

（类似于SHOW CREATE TABLE，查看一个已存在的存储过程）

SHOW CREATE PROCEDURE 存储过程名
D. SHOW PROCEDURE STATUS

（列出所有的存储过程）

SHOW PROCEDURE STATUS
E. CALL语句（存储过程的调用） CALL 存储过程名(参数列表)

CALL语句调用一个先前用CREATE PROCEDURE创建的程序。
CALL语句可以用声明为OUT或的INOUT参数的参数给它的调用者传回值。
存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
F BEGIN ... END（复合语句） [begin_label:]
BEGIN
[statement_list]
END
[end_label]
存储子程序可以使用BEGIN ... END复合语句来包含多个语句。

statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。
复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。
G DECLARE语句（用来声明局部变量） DECLARE语句被用来把不同项目局域到一个子程序：局部变量
DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。
更多参考：http://database.51cto.com/art/201011/235405.htm
H 存储程序中的变量 a. DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。
要给变量提供一个默认值，请包含一个DEFAULT子句。
值可以被指定为一个表达式，不需要为一个常数。
如果没有DEFAULT子句，初始值为NULL。
局部变量的作用范围在它被声明的BEGIN ... END块内。
它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。
b. 变量SET语句
SET var_name = expr [, var_name = expr]
在存储程序中的SET语句是一般SET语句的扩展版本。
被参考变量可能是子程序内声明的变量，或者是全局服务器变量。
在存储程序中的SET语句作为预先存在的SET语法的一部分

[2]让普通人用excel读取oracle数据库表的通用办法

来源: 互联网发布时间: 2013-11-07

作者：iamlaosong

Excel通过ADO方式连接到Oracle并操作Oracle给我们这些编程能力不强的人带来很大的福音，结合着Excel的数据处理与图表制作，就能很轻松地处理一些常规工作。

日常工作中需要查询各种数据，而且不断变化，处理这些数据的人不是技术人员，不会连接数据库自己查询，通过下面的办法就可以让技术人员编辑好包含查询语句的excel文件，让管理人员自己输入条件取数了。

我的方法是编辑需要的SQL语句保存在单元格中，并在查询条件需要参数值的地方用问号“？”代替，再在其他单元格中保存查询条件所需的参数值，在“宏”中用参数值替换掉SQL语句中的问号，最后执行查询语句并将结果保存到excel表中。

以下是通过Excel 的VBA连接Oracle并读取Oracle相关数据的步骤：

Excel 通过VBA连接数据库需要安装相应的Oracle客户端工具并引用ADO的相关组件，引用ADO相关组件可按如下步骤操作：

　　1、打开VBA编辑器，在菜单中点选“工具”--》“引用”。确保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾选上。

　　2、建立读取数据的过程，代码如下：

Public Sub get_data()
    '根据工作表中的查询语句读取数据
    On Error GoTo ErrMsg:

    Dim cnn As Object, rst As Object
    Dim name, stat, sqls, field As String
    Dim pm(4) As String
    Dim i, j, lineno As Integer
    Dim OraOpen As Boolean

    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    sqls = "connect database"
    cnn.Open "Provider=msdaora;Data Source=dl580;User Id=sxjk;Password=sxjk;"
    OraOpen = True '成功执行后，数据库即被打开

    If OraOpen Then lineno = [D65536].End(xlUp).Row Else lineno = 0       '行数

    For i = 3 To lineno
        name = Cells(i, 2) '保存数据工作表名称
        stat = Cells(i, 3)    '是否需要读取数据
        field = Cells(i, 4)   '清除原表数据定位
        pm(1) = Cells(i, 6) '参数值
        pm(2) = Cells(i, 8)
        pm(3) = Cells(i, 10)
        pm(4) = Cells(i, 12)
        sqls = Cells(i, 15)
        For j = 1 To 4
            If pm(j) <> "" Then
                sqls = Replace(sqls, "?", pm(j), 1, 1)     '用参数值替换SQL语句中的问号
                'MsgBox sqls
            End If
        Next j
        If stat = "Y" Or stat = "y" Then
            Set rst = cnn.Execute(sqls)
            sqls = "CopyFromRecordset"
            Sheets(name).Range("a3:" & field & "10000").ClearContents    '清除原表数据
            Sheets(name).Range("a2").CopyFromRecordset rst   '执行SQL

            Cells(i, 3) = "成功"
        End If
    Next i
    Worksheets("系统参数").Select
    msg = MsgBox("数据读取完毕!", vbOKOnly, "iamlaosong")
    Exit Sub
ErrMsg:
    OraOpen = False
    MsgBox sqls, vbCritical, "操作失败，请检查！"

End Sub

　　 3、SQL语句实例

这是一个简单的语句：

SELECT * FROM zdgc_sn_sj_gfl t WHERE t.CLCT_DATE = to_date('?','yyyy-mm-dd') AND t.JSBZ = '1' ORDER BY t.CITY, t.SSXS

这是一个复杂的语句：

select aa.zj_code,aa.zj_mc,aa.clct_date,aa.sjzl,aa.jyqsjzl,nvl(bb.wgfsl,0),nvl(bb.jyqwgfsl,0) from
(select b.ssxs,b.zj_code,b.zj_mc,a.clct_date,count(*) sjzl,
       sum(case when to_char(a.clct_time, 'hh24mi') <= '?' then 1 else 0 end ) jyqsjzl
from tb_evt_mail_clct a, sncn_zd_jg b
where a.clct_bureau_org_code = b.zj_code
   and a.time_limit_code <> '6'
   and a.mail_kind_code <> '10401'
   and a.addi_service_code <> '1'
   and (a.rcv_area like '23%' or a.rcv_area like '24%')
group by b.ssxs,b.zj_code, b.zj_mc,a.clct_date) aa
left join
(select b.ssxs,b.zj_code, b.zj_mc, a.clct_date,count(*) wgfsl, sum(decode(jybz, 'b', 1, 0)) jyqwgfsl
from sncn_zd_jg b,zdgc_sn_sj_errfc a
where a.zj_code = b.zj_code
   and a.jsbz = '1'
   and a.jybz = 'b'
group by b.ssxs,b.zj_code, b.zj_mc, a.clct_date) bb
on aa.ssxs=bb.ssxs and aa.zj_code=bb.zj_code and aa.clct_date=bb.clct_da

[3]DECODE函数的用法和实际运用

来源: 互联网发布时间: 2013-11-07

在公司呆了一段时间，发现真正的利器是sql，要想在企业里得到大家的认可，有很好的sql编程功底是很重要很重要的，为了不断提高自己的sql语句编写能力，我将不定期坚持写sql的博客，其中大部分是遇到的问题和解决方案。如果你有幸看到我的文章，也欢迎加入学习讨论吧！

公司要要我在原来的报表基础上加一个“是否坏机返厂”的字段，公司用的数据库是oracle9i，但是要实现这个字段内容的正确显示，必须要经过一系列的判断才能得到是还是否。

下面是我查找的解决sql做判断的方法：

判断是否坏机返厂
select sms.series_code,decode(ibs.wh_status,'','否','是') as isbad from sm_seristatus sms
left join imei_billseries ibs on ibs.series_code = sms.series_code and ibs.wh_status = '坏机返厂'

其中涉及到的表都是和具体业务相关，我只是把语句列出。

DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。

DECODE有什么用途呢？

先构造一个例子，假设我们想给智星职员加工资，其标准是：

工资在8000元以下的将加20％；

工资在8000元以上的加15％，

通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，

然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

decode()函数使用技巧

·软件环境:

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为:C:\ORANT

·含义解释:

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

该函数的含义如下:

IF 条件=值1 THEN

RETURN(翻译值1)

ELSIF 条件=值2 THEN

RETURN(翻译值2)

......

ELSIF 条件=值n THEN

RETURN(翻译值n)

ELSE

RETURN(缺省值)

END IF

· 使用方法:

1、比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如:

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

2、表、视图结构转化

现有一个商品销售表sale，表结构为:

month char(6) --月份

sell number(10,2) --月销售金额

现有数据为:

200001 1000

200002 1100

200003 1200

200004 1300

200005 1400

200006 1500

200007 1600

200101 1100

200202 1200

200301 1300

想要转化为以下结构的数据:

year char(4) --年份

month1 number(10,2) --1月销售金额

month2 number(10,2) --2月销售金额

month3 number(10,2) --3月销售金额

month4 number(10,2) --4月销售金额

month5 number(10,2) --5月销售金额

month6 number(10,2) --6月销售金额

month7 number(10,2) --7月销售金额

month8 number(10,2) --8月销售金额

month9 number(10,2) --9月销售金额

month10 number(10,2) --10月销售金额

month11 number(10,2) --11月销售金额

month12 number(10,2) --12月销售金额

结构转化的SQL语句为:

create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as

select

substrb(month,1,4),

sum(decode(substrb(month,5,2),'01',sell,0)),

sum(decode(substrb(month,5,2),'02',sell,0)),

sum(decode(substrb(month,5,2),'03',sell,0)),

sum(decode(substrb(month,5,2),'04',sell,0)),

作者：tpstu 发表于2013-1-4 21:05:19 原文链接

阅读：0 评论：0 查看评论