sybase数据库查看表结构 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();
        }
    }
}