--使用该数据库 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();
}
}
}
