Sybase 部分SQL语句介绍

最新推荐文章于 2021-07-14 15:59:10 发布

转载最新推荐文章于 2021-07-14 15:59:10 发布 · 598 阅读

数据库专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一系列Sybase数据库的基本操作案例，包括数据库选择、用户设置、表结构查看、数据查询、表创建、循环累加操作、触发器及视图创建、不同类型的存储过程创建与调用等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--使用该数据库
use testDB
 
--设置该用户为当前用户
setuser 'testuser'
 
--查看表结构
sp_help tableName
 
--查看视图/触发器语句
sp_helptext viewname/triggerName
 
--查看前十行数据
set 
rowcount 10
select 
* from tableName 
set 
rowcount 0
 
--创建表
 
create 
table Mytest
(
  testidint
,
  testname varchar(12),
  testtime  datetime
)
 
 
--循环累加
declare 
@i int , @sumint,@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 , @sumint
, @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
             gotolable
        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  onMytest
for insert
as
begin
declare
 @tsidint
,
 @tsnamevarchar(10),
 @tstime datetime
begin
  select@tsid=testid,@tsname=testname,@tstime=testtime
frominserted
 begin
  insertinto
test2 values(@tsid,@tsname,@tstime)
 end
end
end
 
 
 
--创建触发器,从Mytest Delete的时候把delete的数据Insert到test2
create 
trigger Deletetrigger  onMytest
for delete
as
begin
declare
 @tsidint
,
 @tsnamevarchar(10),
 @tstime datetime
begin
  select@tsid=testid,@tsname=testname,@tstime=testtime
fromdeleted
 begin
  insertinto
test2 values(@tsid,@tsname,@tstime)
 end
end
end
 
 
--创建简单的视图
 
create 
view testview
as
select 
M.*,T.* from Mytest M,test2 Twhere
M.testid = T.test2id
 
--测试视图
select 
* from testview

--测试表
create 
table test
(
  testidnumeric(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 intoutput
as
begin
set 
@tid = (select testidfrom
Mytest where 
testname=@tname)
return
end
 
 
--返回值为varchar的存储过程
create 
proc testReturnT
@tid int,
@tname varchar(12)output
as
begin
set 
@tname = (select 
testname from Mytestwhere
testid=@tid)
return
end
 
 
--可以正确执行
declare 
@tid int
exec 
testReturn 'testname', @tidoutput
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 intout
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(selecttestid
from Mytest 
where testname=@tname)
   begin
   set@error= (select
testid from 
Mytestwhere 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;
 
namespaceSybaseTest
{
    classProgram
    {
        staticvoid
Main(string[] args)
        {
            TestConn();
        }
 
        publicstatic
voidTestConn()
        {
            objectobj =
null;
            OdbcConnection SybaseConn =null;
            OdbcCommand odbccmd =null;
            try
            {
                //连接串
                stringstrconn =
"DSN=TEST;SRVR=TEST;DB=BFV752_T_JXC;UID=sa;PWD=;";
 
                SybaseConn =new
OdbcConnection(strconn);
 
                SybaseConn.Open();
 
                stringstr =
"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();
        }
    }
}