Sybase 部分命令
--使用该数据库 use testDB --设置该用户为当前用户 setuser 'testuser' --查看表结构 sp_help tableName --查看视图/触发器语句 sp_helptext viewname/triggerName --查看前十行数据 set rowcount 10 select * from tableName set rowcount 0 --创建表 create table Mytest ( testid int , testname varchar(12), testtime datetime ) --循环累加 declare @i int , @sum int,@csum char(10) set @i=1, @sum=0 while @i<=1000 begin set @sum = @sum+@i set @i=@i+1 if @i>1000 select @csum= convert(char,@sum) print @csum end --循环累加 declare @i int , @sum int , @csum char(10) set @i=1, @sum=0 lable:if @i<=1000 begin set @sum = @sum+@i set @i=@i+1 if @i>1000 begin set @csum= convert(char,@sum) print @csum end else goto lable end --定义常量 并赋值 打印 declare @i1 int , @i2 int set @i1=123,@i2=321 print "@i=%1!,@i2=%2!",@i1,@i2
--创建触发器,向Mytest Insert的时候同时向test2 Insert create trigger Inserttrigger on Mytest for insert as begin declare @tsid int , @tsname varchar(10), @tstime datetime begin select @tsid=testid,@tsname=testname,@tstime=testtime from inserted begin insert into test2 values(@tsid,@tsname,@tstime) end end end --创建触发器,从Mytest Delete的时候把delete的数据Insert到test2 create trigger Deletetrigger on Mytest for delete as begin declare @tsid int , @tsname varchar(10), @tstime datetime begin select @tsid=testid,@tsname=testname,@tstime=testtime from deleted begin insert into test2 values(@tsid,@tsname,@tstime) end end end --创建简单的视图 create view testview as select M.*,T.* from Mytest M,test2 T where M.testid = T.test2id --测试视图 select * from testview
--测试表 create table test ( testid numeric(8) Identity primary key, --主键 自增 testname varchar(12) ) go --系统自增1 insert into test values('testname2') go select * from test --自己手动输入 --Insert的时候列名一定要写上 否则出错 set identity_insert test on go insert into test(testid,testname) values(5,'testname3')
--不带参数的存储过程 create proc testproc as begin select * from testview end --带一个参数 create proc testproc2 @tid int as begin select * from Mytest where testid=@tid end --带一个参数 create proc testproc3 @tname varchar(12) as begin select * from Mytest where testname=@tname end --带两个参数 create proc testproc4 @tid int , @tname varchar(12) as begin select * from Mytest where testid = @tid and testname=@tname end --执行 exec testproc4 @tid=4,@tname='test'
--返回值为int的存储过程 create proc testReturn @tname varchar(12) , @tid int output as begin set @tid = (select testid from Mytest where testname=@tname) return end --返回值为varchar的存储过程 create proc testReturnT @tid int , @tname varchar(12) output as begin set @tname = (select testname from Mytest where testid=@tid) return end --可以正确执行 declare @tid int exec testReturn 'testname', @tid output select @tid --正确的执行方法 declare @tname varchar(12) declare @tid int exec @tid = testReturnT 3,@tname output select @tid select @tname --正确执行 declare @tname varchar(12) exec testReturnT 3,@tname output select @tname --注意:Sybase存储过程执行之后 返回值的存储过程成功与否的Int值 --查询 返回单个输出参数值 create proc selectproc @tid int out as begin select @tid=testid from Mytest where testname='test9' end --执行 declare @tid int exec selectproc @tid output select @tid --查询 返回一个结果集 create proc selectall as begin select * from Mytest end --执行 exec selectall --返回错误值 create proc testprocreturn @tname varchar(12) as begin declare @tid int,@error int if exists(select testid from Mytest where testname=@tname) begin set @error= (select testid from Mytest where testname=@tname) return @error end else begin set @error=-1 return @error end end return --执行 declare @error int exec @error= testprocreturn 'test9' select @error
测试连接(ODBC)
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Odbc; using System.Data.OleDb; namespace SybaseTest { class Program { static void Main(string[] args) { TestConn(); } public static void TestConn() { object obj = null; OdbcConnection SybaseConn = null; OdbcCommand odbccmd = null; try { //连接串 string strconn = "DSN=TEST;SRVR=TEST;DB=BFV752_T_JXC;UID=sa;PWD=;"; SybaseConn = new OdbcConnection(strconn); SybaseConn.Open(); string str = "update BFBHDD.Mytest set testname = 'testupdate' where testid=1"; odbccmd = new OdbcCommand(str, SybaseConn); obj = odbccmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { SybaseConn.Close(); } Console.WriteLine(obj); Console.ReadKey(); } } }
种一棵树最好的时间是十年前,其次是现在.