--使用该数据库 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 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
--创建触发器,向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 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
--测试表 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' ) |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
--不带参数的存储过程 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' |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
--返回值为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):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
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(); } } } |