Sybase_2

wanghuidhu

于 2010-01-20 16:20:00 发布

阅读量1k

点赞数

文章标签： sybase insert null 存储 sql server delete

本文链接：https://blog.csdn.net/wanghuidhu/article/details/5217647

版权

转自:http://blog.csdn.net/Didizyp/archive/2007/07/12/1687298.aspx

使用存储过程插入数据示例：
CREATE PROCEDURE dbo.p_insert
@bookname   nvarchar(50),
@bookauthor nvarchar(10),
@mesg       nvarchar(200) = 'ok' output
AS
BEGIN
     declare @bookcode nvarchar(10)

    --使用月、日、时、分、秒组成bookcode
    select @bookcode = convert(nvarchar(10), datepart(mm,getdate())*100000000 + datepart(dd,getdate())*1000000 + datepart(hh,getdate())*10000 + datepart(mi,getdate())*100 + datepart(ss,getdate()))

    --插入数据到book表
    insert into book (bookcode, bookname, bookauthor) values (@bookcode, @bookname, @bookauthor)
    if @@error <> 0 --异常处理
    begin
         select @mesg = '新增数据异常！' + convert(nvarchar(3), @@error)
         rollback    --事务回滚
        return -1
    end

    commit --事务提交
    return 0
END
执行：在Interactive SQL中尝试执行以下代码调用p_insert存储过程：
     declare @mesg nvarchar(200),
                    @flag int
     exec @flag = p_insert 'NUnit学习笔记', '0000000001', @mesg output
     if @flag <> 0
     begin
         print @mesg
     end
     else
     begin
         print '程序运行正常！'
     end
     go

     使用存储过程修改数据示例：
CREATE PROCEDURE dbo.p_update
@bookcode   nvarchar(10),
@bookname   nvarchar(50),
@bookauthor nvarchar(10)
AS
BEGIN
     update book set bookname = @bookname, bookauthor = @bookauthor where bookcode = @bookcode
END

使用存储过程删除数据示例：
CREATE PROCEDURE dbo.p_delete
@bookcode nvarchar(10)
AS
BEGIN
delete book where bookcode = @bookcode
END

     如果想删掉存储过程，只要选中simple_query，右键，点击Delete，或者在Interactive SQL 中执行命令：
     drop procedure dbo.simple_query
即可。
6．创建触发器
     触发器是因某一行为而自动做出反应并进行特定操作的一组SQL语句，可以理解为表的事件。例如在对表进行插入、修改或删除数据等操作时，都会导致触发器被触发。这里我会创建三个触发器，分别对应book表的插入、修改和删除操作。

     insert触发器：
     在Sybase Central的Folders中找到zhengTest->User Tables->book->Triggers并选中，然后在Details中双击“Add Trigger(Wizard)”，打开“Add Trigger”向导。在第一个界面中填写触发器名称，例如t_insert，点击Next；在第二个界面中选择Insert，点击Next；在第三个界面中输入代码：
CREATE TRIGGER dbo.t_insert ON dbo.book
     For INSERT AS
BEGIN
     declare @bookcode   nvarchar(10),
                    @bookname   nvarchar(50),
                    @bookauthor nvarchar(20)
    select @bookcode = bookcode, @bookname = bookname, @bookauthor = bookauthor from inserted

print '编号：%1!|名称：%2!|作者：%3!', @bookcode, @bookname, @bookauthor
END
点击Finish。至此，insert触发器创建完毕。
触发器其实也可以认为是一种存储过程，就像我们在VB、VC、Delphi、Swing等开发中写的事件处理方法一样，只不过这个存储过程即没有参数，也没有返回值，更不允许返回结果集，例如select * from book这种语句是绝对不能写到触发器中的。此外，触发器中有两个逻辑表，inserted和deleted，前者代表新插入或更新的值，后者代表数据表中已存在并将被更新掉或删除掉的值，这里用到了inserted。下面赶紧向book表中插入一条数据，看看效果吧！

     update触发器，代码：
CREATE TRIGGER dbo.t_update ON dbo.book
     For UPDATE AS
BEGIN
     IF UPDATE (bookname)
     BEGIN
         rollback transaction
     END

     declare @o_mesg nvarchar(100),
                    @ret int
     exec @ret = dbo.simple_demo 'N', @o_mesg output
     print '%1!--------%2!', @ret, @o_mesg
END
这里需要说明的是IF UPDATE (bookname)，该语句判断bookname字段是否被更新，在这里，bookname被更新便会导致回滚，即数据永远无法通过update bookname字段的方式更改。当有多个字段需要作这种判断时，只要写作“IF UPDATE(bookcode) and|or UPDATE(bookname)”即可。此外，逻辑表inserted、deleted在update触发器中同样有效，相关说明请见insert触发器。

     delete触发器，代码：
CREATE TRIGGER dbo.t_delete ON dbo.book
     For DELETE AS
BEGIN
     declare @bc nvarchar(10)
                  ,@bn nvarchar(50)
                  ,@ba nvarchar(10)
     --定义游标
     declare del_curs cursor for
         select bookcode, bookname, bookauthor
         from deleted
         order by bookcode

open del_curs --打开游标

     --循环游标读取数据
     fetch del_curs into @bc, @bn, @ba
     while (@@sqlstatus = 0)
     begin
         print '编号：%1!|名称：%2!|作者：%3!', @bc, @bn, @ba
         fetch del_curs into @bc, @bn, @ba
     end

close del_curs --关闭游标
deallocate cursor del_curs --释放游标
END
这里使用了逻辑表deleted和游标来查询被删除的数据。

     注意，insert、update、delete触发器在每张表中都只允许创建一个，如果再次创建，例如insert触发器，无论使用什么名字，如test_trigger，之前创建的insert触发器都会被覆盖掉。此外，Sybase也允许在一个触发器中指定多个操作，例如：
CREATE TRIGGER dbo.t_author ON dbo.author
For INSERT, DELETE, UPDATE AS
BEGIN
    print 'author表的触发器被执行！'
END
这个触发器就同时被指定了insert、update和delete操作。
     此外，我们还可以指定触发器失效或者有效，只要选中触发器，右键，点击Disable/Enable，或者在Interactive SQL 中执行命令：
     alter table dbo.book disable| enable trigger dbo.t_insert
即可。
     如果想删掉触发器，只要选中触发器，右键，点击Delete，或者在Interactive SQL 中执行命令：
     drop trigger dbo.t_insert
即可。
三、使用Java访问Sybase
     我写了两个Java访问Sybase的例子，IDE任意，驱动位于${Sybase安装目录}/jConnect-6_0/devclasses下，将文件jconn3d.jar引入即可。

第一个例子的代码如下：
package net.test.db.sybase;

import java.sql.*;

public class TestQuery {

public static void main(String[] args) {
      Connection cn = null;
      CallableStatement cs = null;
      ResultSet rs = null;

      try {
         // 初始化连接对象
         Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
         cn = DriverManager.getConnection("jdbc:sybase:Tds:image:5000/zhengTest?charset=cp936&jconnect_version=3", "sa", "");

cs = cn.prepareCall("{call simple_query()}"); // 获得声明对象

rs = cs.executeQuery(); // 执行，当有结果集时使用executeQuery()方法执行

         while (rs.next()) {
             System.out.print("编号：" + rs.getString(1));
             System.out.print("|名称：" + rs.getString(2));
             System.out.println("|作者：" + rs.getString(3));
         }

         if (rs != null)
             rs.close(); //关闭结果集
         if (cs != null)
             cs.close(); // 关闭声明
         if (cn != null)
             cn.close(); // 关闭连接
     } catch (Exception ex) {
         ex.printStackTrace();
     } finally {
         rs = null;
         cs = null;
         cn = null;
     }
}
}
这个例子演示的是如何访问存储过程simple_query，该存储过程会返回一个结果集。这里需要注意两点：由于simple_query会返回一个结果集，因此需要使用cs.executeQuery()方法执行；在设置连接字符串的时候，设置了charset=cp936，即将客户端的字符集设置成cp936，这样查看结果时才不会乱码。

第二个例子的代码如下：
package net.test.db.sybase;

import java.sql.*;

public class TestDemo {

public static void main(String[] args) {
Connection cn = null;
CallableStatement cs = null;

   try {
    // 初始化连接对象
    Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
    cn = DriverManager.getConnection("jdbc:sybase:Tds:image:5000/zhengTest?charset=cp936&jconnect_version=3","sa", "");

cs = cn.prepareCall("{?=call simple_demo(?,?)}"); // 获得声明对象

    // 设置参数
    cs.registerOutParameter(1, Types.INTEGER);
    cs.setString(2, "N");
    cs.registerOutParameter(3, Types.VARCHAR);

cs.execute(); // 执行，当没有返回结果集时，为防止出错，使用execute()方法作执行

    // 输出处理结果
    System.out.println(cs.getInt(1));
    System.out.println(cs.getString(3));

    if (cs != null)
     cs.close(); // 关闭声明
    if (cn != null)
     cn.close(); // 关闭连接
   } catch (Exception ex) {
    ex.printStackTrace();
   } finally {
    cs = null;
    cn = null;
   }
}
}
这个例子演示的是动态访问存储过程simple_demo，该存储过程带有一个输出参数，并有一个返回值，在JDBC中，返回值和输出参数可以认为是一回事。这里需要注意的是，由于这个存储过程没有结果集，因此执行时应该使用cs.execute()方法。
四、使用VB.net/C#访问Sybase
     我写了两个VB.net/C#访问Sybase的例子，IDE使用SharpDevelop 2.1，环境为.net Framework 2.0，驱动位于${Sybase安装目录}/DataAccess/ADONET/dll下，引用其中的文件Sybase.Data.AseClient.dll到项目中即可。

第一个例子的VB.net代码如下：
Imports Sybase.Data.AseClient

Public Class TestQuery
Sub New()
   Dim cn As AseConnection = Nothing
   Dim cmd As AseCommand = Nothing
   Dim rd As AseDataReader = Nothing

   Try
    '初始化连接对象
    cn = New AseConnection("Data Source=image;Port=5000;UID=sa;PWD=;Database=zhengTest;CharSet=cp936;")

    cn.Open() '打开连接

    cmd = New AseCommand("exec simple_query", cn) '获得命令对象

    rd = cmd.ExecuteReader() '执行，当有只读记录集时使用ExecuteReader()方法执行

    While rd.Read()
     Console.Write("编号：" & rd.GetString(0))
     Console.Write("|名称：" & rd.GetString(1))
     Console.WriteLine("|作者：" & rd.GetString(2))
    End While

    If rd IsNot Nothing Then
     rd.Close() '关闭只读记录集
    End If
    If cmd IsNot Nothing Then
     cmd.Dispose() '销毁命令对象
    End If
    If cn IsNot Nothing Then
     cn.Close() '关闭连接对象
    End If
   Catch ex As AseException
    Console.WriteLine(ex.Message)
   Finally
    rd = Nothing
    cmd = Nothing
    cn = Nothing
   End Try
End Sub
End Class
     C#代码如下：
using System;
using Sybase.Data.AseClient;

namespace zhengTest_Sybase
{
public class TestQuery
{
   public TestQuery()
   {
    AseConnection cn = null;
    AseCommand cmd = null;
    AseDataReader rd = null;

    try
    {
     //初始化连接对象
     cn = new AseConnection("Data Source=image;Port=5000;UID=sa;PWD=;Database=zhengTest;CharSet=cp936;");

     cn.Open(); //打开连接

     cmd = new AseCommand("exec simple_query", cn); //获得命令对象

     rd = cmd.ExecuteReader(); //执行，当有只读记录集时使用ExecuteReader()方法执行

     while(rd.Read())
     {
      Console.Write("编号：" + rd.GetString(0));
      Console.Write("|名称：" + rd.GetString(1));
      Console.WriteLine("|作者：" + rd.GetString(2));
     }

     if (rd != null)
      rd.Close(); //关闭只读记录集
     if (cmd != null)
      cmd.Dispose(); //销毁命令对象
     if (cn != null)
      cn.Close(); //关闭连接对象
    }
    catch( AseException ex )
    {
     Console.WriteLine(ex.Message);
    }
    finally
    {
     rd = null;
     cmd = null;
     cn = null;
    }
   }
}
}
跟第一个Java例子一样，也是演示如何访问simple_query存储过程并对记录集进行操作的。这里请注意几点：第一，连接字符串中Data Source=image指的不是数据源，而是服务器，与设置Server=image效果一样；第二，连接字符串中的CharSet=cp936用来设置字符集，但从当前环境看，这个设置不是必须的，至于我是如何知道该参数有效的，我将字符集设置成cp850后出现乱码了；第三，由于simple_query会返回一个结果集，因此需要使用ExecuteReader方法来执行，这点与Java很类似。

第二个例子的VB.net代码如下：
Imports System.Data
Imports Sybase.Data.AseClient

Public Class TestDemo
Sub New()
Dim cn As AseConnection = Nothing
Dim cmd As AseCommand = Nothing

   Try
    '初始化连接对象
    cn = New AseConnection("Data Source=image;Port=5000;UID=sa;PWD=;Database=zhengTest;")

    cn.Open()'打开连接

    cmd = New AseCommand("simple_demo", cn) '获得命令对象
    cmd.CommandType = CommandType.StoredProcedure '设置命令类型为存储过程

    '设置参数
    cmd.Parameters.Add("RETURN_VALUE", AseDbType.[Integer]).Direction = ParameterDirection.ReturnValue
    cmd.Parameters.Add("@i_mesg", "N")
    cmd.Parameters.Add("@o_mesg", AseDbType.NVarChar, 50).Direction = ParameterDirection.Output

cmd.ExecuteNonQuery() '执行，当没有返回结果集时，使用ExecuteNonQuery()方法作执行

    '输出处理结果
    Console.WriteLine(cmd.Parameters(0).Value)
    Console.WriteLine(cmd.Parameters(2).Value)

    If cmd IsNot Nothing Then
     cmd.Dispose() '销毁命令对象
    End If

    If cn IsNot Nothing Then
     cn.Close() '关闭连接对象
    End If
   Catch ex As AseException
    Console.WriteLine(ex.Message)
   Finally
    cmd = Nothing
    cn = Nothing
   End Try
End Sub
End Class
     C#代码如下：
using System;
using System.Data;
using Sybase.Data.AseClient;

namespace zhengTest_Sybase
{
public class TestDemo
{
   public TestDemo()
   {
    AseConnection cn = null;
    AseCommand cmd = null;

    try
    {
     //初始化连接对象
     cn = new AseConnection("Data Source=image;Port=5000;UID=sa;PWD=;Database=zhengTest;");

     cn.Open(); //打开连接

     cmd = new AseCommand("simple_demo", cn); //获得命令对象
     cmd.CommandType = CommandType.StoredProcedure; //设置命令类型为存储过程

     //设置参数
     cmd.Parameters.Add("RETURN_VALUE", AseDbType.Integer).Direction = ParameterDirection.ReturnValue;
     cmd.Parameters.Add("@i_mesg", "N");
     cmd.Parameters.Add("@o_mesg", AseDbType.NVarChar, 50).Direction = ParameterDirection.Output;

     cmd.ExecuteNonQuery(); //执行，当没有返回结果集时，使用ExecuteNonQuery()方法作执行

     //输出处理结果
     Console.WriteLine(cmd.Parameters[0].Value);
     Console.WriteLine(cmd.Parameters[2].Value);

     if (cmd != null)
      cmd.Dispose(); //销毁命令对象
     if (cn != null)
      cn.Close(); //关闭连接对象
    }
    catch( AseException ex )
    {
     Console.WriteLine(ex.Message);
    }
    finally
    {
     cmd = null;
     cn = null;
    }
   }
}
}
跟第二个Java例子类似，演示如何访问simple_demo存储过程。不过ADO.net调用存储过程的方式与JDBC不同，只允许在命令对象中输入过程名作为语句，而且不支持“?”占位符。关于参数设置中用到的RETURN_VALUE、@i_mesg、@o_mesg，分别代表simple_demo的返回值和两个参数。由于simple_demo不会返回记录集，因此需要使用ExecuteNonQuery方法来执行。这里推荐一下文章《将存储过程用于命令》，该文对ADO.net访问存储过程进行了比较详细的介绍，从.net Framework自带文档或网上都能找到。

     另外，再提供两个连接字符串：
     Sybase for OLED：PROVIDER=ASEOLEDB;Server=image;Port=5000;User Id=sa;Password=;Initial Catalog=zhengTest;CharSet=cp936;
请注意字符串中的Initial Catalog=zhengTest，表示要访问我在Sybase中创建的zhengTest数据库，而CharSet=cp936是用来设置字符集的，不设置会乱码。
     Sybase for ODBC：DSN=zhengTest_Sybase;UID=sa;PWD=;CharSet=cp936;
字符串中的DSN=zhengTest_Sybase表示访问我在ODBC中设置的数据源zhengTest_Sybase（如何在ODBC中设置数据源就不演示了），而CharSet=cp936也是用来设置字符集的，但在当前环境中不是必须的。
关于OLEDB和ODBC的使用，与示例类似，请参考其他ADO.net书籍或文档。
五、使用Perl访问Sybase
      我使用的Perl是5.8.8 build 820，虽然默认不带数据库访问接口，但可以使用工具ppm下载到DBI。
      打开命令行工具，输入ppm search dbi，查找dbi，有183个结果，我们需要的是第56个——DBI v1.57。下面执行命令ppm install dbi，如果提示错误，请尝试ppm install 56，即安装查询结果中的第56个。不过安装结束后，暂时还是无法使用DBI来连接数据库，因为DBI提供的只是一套数据库接口，而不是数据库驱动，因此还需要安装dbd-ado和dbd-odbc，安装方法同dbi。
     不过您或许已经注意到了，我使用的dbi版本不是最新的1.58，而且驱动也不是dbd-sybase，的确，因为这些都没有安装包，只能从http://www.perl.com/CPAN/上下载源码自己编译。我自己倒是也做了不少尝试，包括安装FreeTDS，在cygwin和WinGW中make，结果都失败了，自己推测原因大约是Perl和DBI、DBD-SYBASE之间版本不对应，毕竟这些东东每个版本接口都会有所变化。最后在经过一周的折磨之后，终于连编译Ruby的dbd_sybase模块的想法统统放弃了。

这里也是两个例子。第一个例子的代码如下：
use DBI;
#创建连接对象，类型为ADO
$dbh=DBI->connect('DBI:ADO:PROVIDER=ASEOLEDB;Server=image;Port=5000;User Id=sa;Password=;Initial Catalog=zhengTest;CharSet=cp936;');

$sth=$dbh->prepare('exec simple_query' ); #获得声明对象
$sth->execute; #当有结果集时，使用execute方法

while (@row=$sth->fetchrow_array) {
print '编号：', $row[0];
print '|名称：', $row[1];
print '|作者：', $row[2], "/n";
}

$sth->finish if $sth; #关闭声明

$dbh->disconnect if $dbh; #断开连接
也是演示如何访问simple_query存储过程并对结果集进行操作的。这里请注意两点：第一，本例使用的驱动类型为ADO，其连接字符串中，Initial Catalog=zhengTest表示要访问我在Sybase中创建的zhengTest数据库，而CharSet=cp936则表示设置字符集为cp936，目的在于避免乱码；第二，由于simple_query会返回一个结果集，因此需要使用execute方法来执行，这点与前面很类似。

第二个例子的代码如下：
use DBI;
#创建连接对象，类型为ODBC
$dbh=DBI->connect('DBI:ODBC:DSN=zhengTest_Sybase;UID=sa;PWD=;CharSet=cp936;');

$sql='{?=call simple_demo(?,?)}';
$ret='';
$msg='';

$sth=$dbh->prepare($sql); #获得声明对象

#设置参数
$sth->bind_param_inout(1, /$ret, 1);
$sth->bind_param(2, 'N');
$sth->bind_param_inout(3, /$msg, 100);

$sth->execute; #执行

#输出处理结果
print $ret, "/n";
print $msg, "/n";

$sth->finish if $sth; #关闭声明

$dbh->disconnect if $dbh; #断开连接
也是演示动态访问simple_demo存储过程的。这里需要注意的是，本例使用的驱动类型为ODBC，其连接字符串中，DSN=zhengTest_Sybase表示访问我在ODBC中设置的数据源zhengTest_Sybase，而CharSet=cp936则表示设置字符集为cp936，这里是必须的。
六、使用Ruby访问Sybase
     我使用的Ruby是1.8.6-25，已安装Rails 1.2.3。由于Ruby默认不带数据库访问接口，因此请先从http://rubyforge.org/projects/ruby-dbi下载ruby-dbi，当前版本0.1.1。由于Ruby版本的DBI是从Perl版本演变过来的，因此两者有很多相似之出。
     下载完dbi-0.1.1.tar.gz文件之后，解压缩，在命令行中定位到解压后的目录下，例如F:/ruby-dbi，执行命令：
     ruby setup.rb config --with=dbi,dbd_ado,dbd_odbc
     ruby setup.rb setup
     ruby setup.rb install
此时dbi接口连带dbd_ado、dbd_odbc驱动就都安装到系统中了。
     据说dbi所提供的驱动仅是表层的，如果要使用还需要安装底层驱动，例如PostgreSQL，就还需要安装ruby-postgres。不过似乎是因为我安装了Rails的缘故（Rails带有activerecord，据说里边包含了很多驱动），我在使用的过程中倒也没被要求安装底层驱动。
     此外，我也没有使用dbd_sybase驱动。不知道为什么，dbd_sybase居然需要编译，而我在cygwin和Windows命令行下都进行过尝试，还安装了MinGW（GCC的Windows版本），甚至查找了perl-dbi和freetds的资料，结果还是失败。最可恨的是dbd_sybase调用的接口居然跟freetds和ruby中定义的接口不一致，后来看了dbd_sybase代码才发现，居然是2001年的。最后，dbd_sybase到底没装上，可是make倒学会了。汗！

这里也是还个例子。第一个例子的代码如下：
require 'dbi'
#创建连接对象，类型为ADO
dbh=DBI.connect('DBI:ADO:PROVIDER=ASEOLEDB;Server=image;Port=5000;User Id=sa;Password=;Initial Catalog=zhengTest;CharSet=cp936;')

sth=dbh.prepare('exec simple_query') #获得声明对象
sth.execute #当有结果集时，使用execute方法

while row=sth.fetch do
print '编号：', row[0]
print '|名称：', row[1]
print '|作者：', row[2], "/n"
end

sth.finish if sth #关闭声明

dbh.disconnect if dbh #断开连接

第二个例子的代码如下：
require 'dbi'
#创建连接对象，类型为ODBC
dbh=DBI.connect('DBI:ODBC:DSN=zhengTest_Sybase;UID=sa;PWD=;CharSet=cp936;')

sql='{?=call simple_demo(?,?)}' #支持该代码
ret=''
msg=''

sth=dbh.prepare(sql) #获得声明对象

#设置参数
sth.bind_param(1, ret)
sth.bind_param(2, 'N')
sth.bind_param(3, msg)

sth.execute #执行

#由于dbi不支持输出参数，因此这里无法获取simple_demo的返回值与输出参数

sth.finish if sth #关闭声明

dbh.disconnect if dbh #断开连接
演示动态访问simple_demo存储过程。不过由于dbi不支持存储过程的输出参数，注意是存储过程，所以在这个示例中没有获取返回值的代码。

     也不知道为什么，dbi只支持函数的输出参数，而不支持存储过程的输出参数，似乎SQL Server和DB2的存储过程也同样无法返回输出参数。
     由于本文只是使用Ruby访问Sybase，因此没打算对dbi作深入介绍。如果对dbi有兴趣，建议在网上找一篇名为《firstworks Programming with SQL Relay using the Ruby DBI API》的文章，虽然是英文的，但内容很全面，对多种数据库都有介绍，值得一看。
     别告诉我你英文不好，我英文也不好，不也是用着金山词霸就那么看了吗^_^
七、小结
     本文只是对Sybase操作的简单介绍，像索引、序列这些都没有作说明，而且给出的Java、.net、Perl和Ruby示例也都比较简单，如果希望了解更多内容，还请参考其他书籍或文档。
     此外，SQL Server与Sybase系出同门，大约是在Sybase 6的时候微软不愿再同Sybase公司合作而分成两支的，因此如果手头缺少Sybase资料，也不妨参考一下SQL Server的文档。不过作为一款大型数据库，Sybase至今已发布了15个大的版本，却不知道为什么，在程序汉化和帮助文档方面做得始终很差，而且提供的功能也很弱，像用户自定义函数、try-catch语句和row_number函数这些，至今没有，因此在使用SQL Server文档的时候，如果发现某些功能在Sybase中实现不了，请不要奇怪。