10 sql server
This presentation is the property of its rightful owner.
Sponsored Links
1 / 54

第 10 章 SQL Server 程序设计 PowerPoint PPT Presentation


  • 72 Views
  • Uploaded on
  • Presentation posted in: General

第 10 章 SQL Server 程序设计. 本章要点 领会批处理、脚本、事务 学会变量的使用方法 掌握程序控制语句的特点及使用方法 学会编写简单的 SQL 程序. 10.1 程序中的批处理、脚本、注释. SQL srever 的编程语言就是 T-SQL 语言 ,是一种非过程化的语言。不论是普通 的客户 / 服务器应用程序,还是 web 应用程 序,都必须通过服务器发送 T-SQL 语言才 能实现与 SQL srever 的通信。. 一、批处理.

Download Presentation

第 10 章 SQL Server 程序设计

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


10 SQL Server

  • SQL


10.1

SQL sreverT-SQL

/web

T-SQL

SQL srever


T_SQLGOSQLTransact-SQLSQL ServerSQL Server


1SQLGO

2

3

SQL Server


SQL .sql


SQL Server

- -

/* */


use student

go

print :

select * from

print

SELECT COUNT(*) FROM

go

--PRINTcharvarchar

/**/


10.2 SQL Server

SQL Server,Transact-SQL@@@


SQL ServerSQL ServerSQL ServerTransact-SQL


SQL Server33()

  • @@


sql server

sql server

print sql server

print @@version

print sql server:+

@@servername

print :+

@@servicename

go



:

DECLARE @ [n]

:

SET @ =

SELECT @= [ ,...n ]


@;DECLARE

DECLARE @var1 int,@var2 money

SELECT @var1=50,@var2=$29.95

SELECT @var1,@var2

@@

@@version

@@rowcount


declare @nowdate char(6),@disp varchar(30)

set @nowdate=getdate()

set @disp=

print @[email protected]


10.3

SQL Server SQL


BEGINEND

BEGIN

1

2

END


BEGINENDTransact-SQLBEGINEND,T-sql


IFELSE

IF

1

ELSE

2


IFELSEELSE

IFELSE

IFELSE

SQL Server

IFELSE


Use market

if (select from goods

where =)<30

Begin

Declare @number1 int

Set @number1=(select from goods

where =) --

Print :+cast(@number1 as char(3))

Print !

end /*cast

*/


CASE

CASECASECASECASE


1. CASE

CASE

WHEN 1 THEN 1

WHEN 2 THEN 2

[ ELSE n+1]

END

2.CASE

CASE

WHEN 1 THEN 1

WHEN 2 THEN 2

[ ELSE n+1]

END


CASE

USE MARKET

Select =

CASE

when 0 then

when 1 then

END

FROM goods


CASE

USE MARKET

Select =

CASE

when>=5000then

when>=1500then

when>=900then

when>=100then

ElSE

END

FROM goods


WAITFOR

WAITFORSQL

WAITFOR

WAITFOR DELAY 'time' | TIME 'time'

DELAYTIME

datetimedatetime

hh:mm:ss


WAITFOR

SELECTwaitforDATEPARTSECONDGETDATEwaitfor= GETDATE

go

Waitfor delay00:00:055

Select waifor=DATEPART(SEXOND,GETDATE()),waitfor=GETDATE


PRINT

PRINT

PRINT ||


WHILE

WHILEWHILESQLWhile


WHILE

Begin

1

[ BREAK ]

2

[ CONTINUE ]

3

End


DECLARE @i INT,@e INT

SET @i=1

SET @e=0

WHILE @i<=100 --

BEGIN

IF @i%7=0 [email protected]*/

begin

SET @e= @e+1

SET @i= @i+1

CONTINUE --WHILE

END

IF @ i>=100 - -i>=100

BREAK

ELSE

SET @ i=@ i+1

END

PRINT1~1007+

CONVERTVARCHAR, @ e)+


RETURN

RETURN

RETURN

RETURN

RETURN


10.4.2

SQL Server

1024

  • returnselect


10.4.2

1CREATE FUNCTION

CREATE FUNCTION

( [ { @ } [ ,...n ] ] )

RETURNS

[ AS ]

BEGIN

RETURN

END


studenetxuefen60

CREATE FUNCTION xuefen(@cj int)

RETURNS nvarchar(10)

BEGIN

declare @str nvarchar(10)

if @cj >=60

set @str=''

else

set @str=''

return @str

END

GO


xuefen0004

USE studenet

GO

SELECT ,,dbo.xuefen() AS

FROM

WHERE ='0004'

GO


2.

  • RETURNStableRETURN SELECT

  • BEGINEND

  • RETURNSELECTSELECT


STUDENTXSH

CREATE FUNCTION XSH (@zymc char(4)) RETURNS table

AS

RETURN

( SELECT , , FROM WHERE [email protected])

GO


SELECT * FROM DBO.XSH (')

GO


10.4.3

1.

sp_helptext

sp_help

sp_help


10.4.3

STUDENTXUEFEN

USE STUDENT

GO

SP_HELPTEXT XUEFEN

GO


10.4.3

T-SQL

DROP

DROP FUNCTION [,n]


10.5

1.

1

2

Atomic

ConDemoltent

Isolated

Durable


10.5

3

BEGIN TRAN

COMMIT TRAN

ROLL BACK TRAN


10.6

SELECT

1.5


1

DECLARE CURSOR

[ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select

[ FOR UPDATE [ OF [ ,...n ] ] ]


2

OPEN

OPEN

@@ERROR

0

@@CURSOR_ROWS


3

FETCH

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n}|RELATIVE{n}]FROM] [ INTO @Variable_name [ ,...n ] ]


4

CLOSE

OPEN


5

DEALLOCATE


student

--

DECLARE c_jsxx CURSOR KEYSET FOR SELECT ,, FROM

OPEN c_jsxx --

DECLARE @xm nvarchar(8),@xb char(2),@xl nvarchar(10)

IF @@ERROR=0 --

BEGIN

IF @@CURSOR_ROWS>0

BEGIN

PRINT ''+RTRIM(CAST(@@CURSOR_ROWS AS CHAR(3)))+''

PRINT ''


--

FETCH NEXT FROM c_jsxx INTO @xm,@xb,@xl

--@@FETCH_STATUS

WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT @[email protected][email protected]

--

FETCH NEXT FROM c_jsxx INTO @xm,@xb,@xl

END

END

END

ELSE

PRINT ''

CLOSE c_jsxx --

DEALLOCATE c_jsxx --

GO


student

USE student

GO

SELECT * INTO from

SELECT * FROM

GO

--

DECLARE c_xiugai CURSOR SCROLL DYNAMIC FOR SELECT * FROM

OPEN c_xiugai --

--

FETCH FIRST FROM c_xiugai

--


DELETE FROM

WHERE CURRENT OF c_xiugai

--

FETCH LAST FROM c_xiugai

--

UPDATE SET =''

WHERE CURRENT OF c_xiugai

CLOSE c_xiugai --

DEALLOCATE c_xiugai --

GO

SELECT * FROM

GO


  • Login