e , Postavki.
1. .
Transact-SQL @. DECLARE. :
) SET.
DECLARE @name CHAR(20), @a BIGINT
SET @name =''
SET @a =528670
PRINT @a;
) SELECT.
DECLARE @a BIGINT, @name CHAR(20), @c CHAR(20)
SELECT @name=''
SELECT @a=COUNT(*)
FROM PostDet.tblS
SELECT @a As N;
) SET .
DECLARE @z INT
SET @z=(SELECT SUM(QTY) FROM PostDet.tblSP)
PRINT @z;
) - .
DECLARE @t TABLE (S CHAR (5), STATUS INT)
INSERT INTO @t (S, STATUS) VALUES ('S1', 10)
SELECT * FROM @t;
2. IFELSE. .
) tblS .
DECLARE @MiCHAR CHAR(5)
SET @MiCHAR= (SELECT S FROM PostDet.tblS WHERE S='S1')
IF @MiCHAR ='S1'
PRINT 'Record find'
ELSE
PRINT 'Record notfind';
) 30 , 350 .
IF (SELECT AVG (QTY) FROM PostDet.tblSP)< 350
UPDATE PostDet.tblSP
SET QTY = QTY + 30
SELECT* FROM PostDet.tblSP;
) , S1, , S2, S1 50 .
IF (SELECT AVG (QTY)
FROM PostDet.tblSP
WHERE S = 'S1') <
(SELECT AVG (QTY)
FROM PostDet.tblSP
WHERE S ='S2')
UPDATE PostDet.tblSP
SET QTY=QTY + 30
WHERE S ='S1'
SELECT * FROM PostDet.tblSP;
3. WHILE.
) . , .
DECLARE @MiCounter INT
SET @MiCounter=1
WHILE @MiCounter < 10
BEGIN
PRINT CONVERT(CHAR(2), @MiCounter)
SET @MiCounter= @MiCounter+1
END
) 10 , S2 400 .
WHILE (SELECT AVG(QTY) FROM PostDet.tblSP WHERE S = 'S2') < 400
BEGIN
PRINT ' '
UPDATE PostDet.tblSP
SET QTY = QTY +10
END
SELECT* FROM PostDet.tblSP;
4. CREATE PROCEDURE.
) , - , .
CREATE PROCEDURE PostDet. SUP_AVG @AvgPost INT output,
@NumPost CHAR (10)
AS
SELECT @AvgPost= AVG(QTY)
FROM PostDet.tblSP
WHERE S = @NumPost;
.
DECLARE @AvgSup INT
EXEC PostDet. SUP_AVG @AvgSup OUTPUT, S1
SELECT @AvgSup As AVGQTY;
|
|
|
) tblS S5.
CREATE PROCEDURE PostDet. ADD_SUP
@RS CHAR(6),
@RSNAME CHAR(9),
@RSTATUS INTEGER,
@RCITY CHAR(8)
AS
IF EXISTS (SELECT*
FROM PostDet.tblS
WHERE S= @RS
AND SNAME = @RSNAME)
PRINT ' '
ELSE
BEGIN
INSERT INTO PostDet.tblS VALUES (@RS, @RSNAME, @RSTATUS, @RCITY)
PRINT ' '
END;
:
PostDet. ADD_SUP S5, Smith, 40, London ;
) , c.
CREATE PROCEDURE PostDet.PrLike @XName varchar (50)
AS
BEGIN
SELECT *
FROM PostDet.tblS
WHERE SName LIKE @XName;
END
: PostDet.PrLike '%c%';
P4.
XML .
MS SQLServer XML,
Transact SQL XML .
