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

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


  • 70 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

10 SQL Server

  • SQL


10 sql server

10.1

SQL sreverT-SQL

/web

T-SQL

SQL srever


10 sql server

T_SQLGOSQLTransact-SQLSQL ServerSQL Server


10 sql server

1SQLGO

2

3

SQL Server


10 sql server

SQL .sql


10 sql server

SQL Server

- -

/* */


10 sql server

use student

go

print :

select * from

print

SELECT COUNT(*) FROM

go

--PRINTcharvarchar

/**/


10 2 sql server

10.2 SQL Server

SQL Server,Transact-SQL@@@


10 sql server

SQL ServerSQL ServerSQL ServerTransact-SQL


10 sql server

SQL Server33()

  • @@


10 sql server

sql server

sql server

print sql server

print @@version

print sql server:+

@@servername

print :+

@@servicename

go


10 sql server


10 sql server

:

DECLARE @ [n]

:

SET @ =

SELECT @= [ ,...n ]


10 sql server

@;DECLARE

DECLARE @var1 int,@var2 money

SELECT @var1=50,@var2=$29.95

SELECT @var1,@var2

@@

@@version

@@rowcount


10 sql server

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

set @nowdate=getdate()

set @disp=

print @[email protected]


10 sql server

10.3

SQL Server SQL


Begin end

BEGINEND

BEGIN

1

2

END


10 sql server

BEGINENDTransact-SQLBEGINEND,T-sql


If else

IFELSE

IF

1

ELSE

2


10 sql server

IFELSEELSE

IFELSE

IFELSE

SQL Server

IFELSE


10 sql server

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

*/


10 sql server

CASE

CASECASECASECASE


10 sql server

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


10 sql server

CASE

USE MARKET

Select =

CASE

when 0 then

when 1 then

END

FROM goods


10 sql server

CASE

USE MARKET

Select =

CASE

when>=5000then

when>=1500then

when>=900then

when>=100then

ElSE

END

FROM goods


Waitfor

WAITFOR

WAITFORSQL

WAITFOR

WAITFOR DELAY 'time' | TIME 'time'

DELAYTIME

datetimedatetime

hh:mm:ss


Waitfor1

WAITFOR

SELECTwaitforDATEPARTSECONDGETDATEwaitfor= GETDATE

go

Waitfor delay00:00:055

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


10 sql server

PRINT

PRINT

PRINT ||


While

WHILE

WHILEWHILESQLWhile


10 sql server

WHILE

Begin

1

[ BREAK ]

2

[ CONTINUE ]

3

End


10 sql server

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

RETURN


10 4 2

10.4.2

SQL Server

1024

  • returnselect


10 4 21

10.4.2

1CREATE FUNCTION

CREATE FUNCTION

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

RETURNS

[ AS ]

BEGIN

RETURN

END


10 sql server

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


10 sql server

xuefen0004

USE studenet

GO

SELECT ,,dbo.xuefen() AS

FROM

WHERE ='0004'

GO


10 sql server

2.

  • RETURNStableRETURN SELECT

  • BEGINEND

  • RETURNSELECTSELECT


10 sql server

STUDENTXSH

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

AS

RETURN

( SELECT , , FROM WHERE [email protected])

GO


10 sql server

SELECT * FROM DBO.XSH (')

GO


10 4 3

10.4.3

1.

sp_helptext

sp_help

sp_help


10 4 31

10.4.3

STUDENTXUEFEN

USE STUDENT

GO

SP_HELPTEXT XUEFEN

GO


10 4 32

10.4.3

T-SQL

DROP

DROP FUNCTION [,n]


10 sql server

10.5

1.

1

2

Atomic

ConDemoltent

Isolated

Durable


10 sql server

10.5

3

BEGIN TRAN

COMMIT TRAN

ROLL BACK TRAN


10 sql server

10.6

SELECT

1.5


10 sql server

1

DECLARE CURSOR

[ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select

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


10 sql server

2

OPEN

OPEN

@@ERROR

0

@@CURSOR_ROWS


10 sql server

3

FETCH

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


10 sql server

4

CLOSE

OPEN


10 sql server

5

DEALLOCATE


10 sql server

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 ''


10 sql server

--

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


10 sql server

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

--


10 sql server

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