Karpova_bazy_dannyh
.pdf263
From books
WHERE YEARIZD=@YEARIZD AND PUBLICH =@PUBLICH
/* Select @TEK_Count */.
/* ,
, ,
|
. |
|
SELECT |
|
|
|
, |
|
- |
|
- |
. |
|
|
|
|
|
|
|
|
Transact SQL |
COALESCE |
(n1, |
n2, ,nm), |
|
|
, |
NULL? |
- |
|
n1, n2, |
,nm. |
|
|
|
Return COALESCE (@TEK Count ()) |
|
|
|
||
|
, |
|
|
, |
- |
|
|
. |
|
|
|
declare @N int |
|
|
|
|
|
E @N= COUNT_BOOKS @PUBLICH = ' |
' |
|
|
||
@N |
|
|
|
, |
- |
«« |
» |
. |
|
|
|
|
: |
|
|
|
|
Exec @N = COUNT_BOOKS @PUBLICH = 'BHW', @YEARIZD= 1999 |
|
, «BHW» 1999
.
, -
, .
. , -
.
, -
, . ,
,
.
, -
,
«» -
, . B
- , , :
/*
*/
if exists (select * from sysobjects where id = object_1d{'dbo NEW_BOOKS'} and sysstat & Oxf =4)
264 |
12. |
SQL |
/* , */ drop procedure dbo NEW_BOOKS
GO
CREATE PROCEDURE NEW_B0OKS (@ISBN varchar (12), @TITL varchar (255). @AUTOR varchar (30). @COAUTOR varchar (30),@YEARIZD int. @PAGES INT. @NUM_EXEMPL INT)
/*
@ISBN |
varchar (12) |
|
|
@TITL |
varchar (255) |
|
|
© AUTOR |
varchar (30) |
|
|
@COAUTOR |
varchar (30) |
|
|
© YEARIZD |
int |
|
|
©PAGES |
INT |
|
|
@NUM_EXEMPL INT |
|
|
|
*/ AS |
|
|
|
/* |
, |
|
- |
|
, . . |
, |
- |
*/ |
|
|
|
DECLARE @TEK int
/ * BOOKS */
INSERT INTO BOOKS VALUES (@ISBN.@TITL.@AUTOR.@COAUTUR. @YEARIZD.@PAGES)
/* */ SELECT @ = @NUM_EXEMPL
/* */
WHILE @>0 /* */ BEGIN
/* IDENTITY, . , ,
INSERT.
, |
|
(EXIST) |
, |
TRUE, |
- |
.
|
|
, |
|
|
- |
, |
1 |
1900 |
. |
|
|
|
, |
|
: /* |
, |
- |
*/.
265
insert into EXEMPLAR (ISBN Data_IN, Data_OUT, EXSIST) VALUES (@ISBN. OUT_DATE (), GET_DATE ()), TRUE)
/* */ SELECT @ = @ = 1
End /* */ GO
-
, , -
.
-
, ,
, ,
.
:
/* , */
if exists (select * from sysobejects where Id = object_td<'dbo NEW_BOOKS) and sysstat & Oxf = 4)
/* , */ drop Procedure dboNEW_BOOKS
CREATE PROCEDURE NEW_BOOKS (@ISBN varchar (12), @TITL varchar (255).0AUTQR varchar (30), @COAUTOR varchar (30).@YEARIZO int. @PAGES INT.
@NUM_EXEMPL INT) /*
@ISBN |
varchar (l2) |
@TITL |
varchar (255), |
@AUTOR |
varchar (30) |
@COAUTOR |
varchar (30) |
@YEARIZD |
int |
©PAGES |
INT |
@NUN_EXEMPL INT */ AS
DECLARE @TEK Int Declare @INV int
INSERT INTO BOOKS VALUES (@ISBN. @TITL @AUTOR. @COAUTOR. @YEARIZD. @PAGES)
/* */ SELECT @TEK = NUM_EXEMPL
/* */
266 |
12. |
SQL |
Select @ inv = select max (1D_EXEMPLAR) from EXEMPLAR.
/* */
WHILE @TEK>0 /* */ BEGIN
insert into EXEMPLAR (1D_EXEMPLAR. ISBN, DATA_IN, DATA_OUT.EXIST) VALUES (@INV.@ISBN.GETDATE (), Get date (), TRUE)
/* */ SELECT @E = @ - 1
select inv = inv +1
End /* */ GO
. - , .
if exists (select * from sysobjects were 1d = object_1d('dbo. CK_READER) and sysstat & Oxf = 4)
/* , */ drop procedure dbo. CK_READER
/* , , 0 -
. */
CREATE PROCEDURE CK_READER (@FIRST_NAME varchar (3O), @BIRTH_DAY varchar (12)),
AS
/*, */
DECLARE @NUM_READER INT , /* */
Select <i>NUM_READER = select NUM_READER from READERS WHERE FIRSTJAME = @ FIRST_NAMF AND
AND convert (varchar (8), BIRTH_DAY,4)=@BIRTH_DAY RETURN COALESCE (@NUM_READER, 0)
datetime - varchar(S).
. |
, |
@BIRTH_DAY (varchar), BIRTH_DAY
SmallDateTime.
.
OUTPUT. -
.
|
|
267 |
|
|
, |
|
|
|
, |
- |
|
|
. |
- |
|
, |
, |
- |
|
|
, |
, |
|
.
/* */ if exists (select * from sysobjects where 1d=
object_1d (N dbo] [NEW_READER]) and OBJECTPR0PERTY(1d. N Is Procedure') = 1) drop procedure [dbo].[NEW_READER]
GO
/* .
,
, , - */
CREATE PROCEDURE NEW_READER (@NAME_READER varchar (30) @ADRES varchar (40).@H0OM_PHONE char (9). @WORC_PH0N char (9),BIRTH_DAY varchar
(8). @NUM_READER int OUTPUT
/* , */ @Y_N int OUTPUT.
/* , , -
*/ |
|
|
|
|
@COUNT_BOOKS |
int OUTPUT |
|
|
|
/* |
, |
, |
, |
- |
|
*/) |
|
|
|
AS |
|
|
|
|
/* , , - */
DECLARE @N_R int
/* */
EXEC @N_R= CK_READER @NAME_READER. @BIRTH_DAY IF @N_R=0 Or |N_R Is Noll
/* , . @N_R , */
BEGIN
/* , , -
*/
268 |
12. |
SQL |
|
insert into |
READER (NAME_READER. ADRES. HOOM_PHONE. |
||
WORK_PHONE BIRT_DAY) |
|
|
|
values (@NAME_READER.@ ADRES.@ HOOM_PHONE.@ WORK_PHONE. |
|
|
|
@BIRTH_DAY 4) |
|
|
|
/* |
INSERT |
|
|
@BIRTH_DAY |
smalldatetime, |
|
|
BIRTH_DAY. Transact SQL Convert */
/* */
select NAME_READER = NUM_READER FROM READER WHERE NAME_READER = <NAME_READER AND convert varchar(B) BIRTH_DAY,4)=@BIRTH_DAY
/* , BIRTH_DAY smalldatatime varchar (8), @BIRTHDAY */
Select @Y_N =0
/* @Y_N 0 (), , */
Select COUNT_BOOKS = 0
/* , , , */
Return 1 END else
/* @N_R , - */
BEGIN
-
*/
select PCOUNTJOOKS = COUNT(INV_NUMBER) FROM EXEMPLAR WHERE NUM_READER= @N_R
select @Count_books = COALESCE( @C0UNT_BOOKS, 0)
@COUNT_BOOKS ,
, ,
@C0UNT_B0OKS ,
, COALESCE (@COUNT_BOOKS, 0),
*/ Select @Y_N = 1
/* @Y_N 1, , */
269
Select @NUM_READER=@N_R
/* @NUM_READER */
return 0
end
, , -
( QueryAnalyzer MS SQL Server 7.0) -
:
--
--new reader
--1
Declare @ int, @N int. @ |
int |
|
exec NEW_READER " |
. .',' |
22-90'. |
4333-55-99'. '444-66-881. '01.06.83'. @NUM READER =@K OUTPUT. @Y_N=@N OUTPUT.@COUNT_BOOKS=@B OUTPUT
--, ,
--.
Select '', @. '-' @N. '-'. @B
:
|
- |
- |
18 |
0 |
0 |
, - , :
|
- |
- |
18 |
1 |
0 |
, . . , , 0() .
.
, -
SQL-, -
, , 5 SQL-, - , (. . 12.2).
, , ,
, SQL
.
270 |
12. |
SQL |
|
|
SQL |
Select
-
( )
-
. 12.2. SQL
-
«-».
. 12. 3 SQL ,
. 12.4 SQL,
.
.
.
.
|
Update |
|
|
|
|
Update |
|
Update |
|
Select From |
|
Declare x cursor |
|
Select |
|
Fetch |
|
Open cursor x |
|
Fetch |
Fetch x |
Fetch |
Fetch |
Fetch x |
Fetch |
|
|
|
|
Fetch x |
|
Fetch |
|
Commit |
|
Commit |
|
Commit |
|
|
|
.12.3. SQL-
271
Execute Pr1 |
|
Update |
|
|
|
Exec Pr1 |
|
|
|
|
Select |
|
|
Fetch |
(12 |
) |
Fetch |
|
|
Fetch |
|
|
Fetch |
|
|
Fetch |
|
|
Commit |
.12.4.
|
|
|
, |
SQL |
|
Server |
|
|
|
|
- |
. |
|
|
|
, |
|
. |
|
|
|
|
- |
|
|
. |
|
|
|
|
|
|
. |
, MS SQL |
|
Server |
|
|
, |
, |
- |
|
|
. |
|
|
|
Oracle |
|
: |
, |
|
- |
|
|
|
, |
BEFORE- |
|
, |
, |
|
|
|
- |
|
, |
MS SQL Server, |
|
|
|
AFTER- |
. |
|
|
|
|
, |
|
, |
|
- |
|
(constraint), |
|
|
|
|
|
. |
|
|
|
, |
- |
|
|
(DRI declarative Referential Integrity) |
- |
.
SQL1 , .
SQL2, 1996 ,
.
: CREATE TRIGGER <_>
ON <_>
FOR {[INSERT]. [UPDATE] [DELETE] } [WITH ENCRIPTING]
272 |
12. |
SQL |
AS
SQL- ()
.
FOR , -
.
WITH EMCRIPTING , ,
.
, ,
, .
, :
(
, , , , ,
, ).
D&OP
.
ALTER TABLE, ALTER DATABASE
, GRAND REVOKE.
(VIEW) .
, -
, -
.
, -
, , .
? : ,
, -
, -
, . Transact SQL .
/* */
if exists (select * from sysobjects where 1d = object_1d('dbo DEL_EXEMP') and sysstat &,0xf * 8)
drop trigger dbo DEL_EXEMP GO
CREATE TRIGGER DEL_EXEMP ON dbo EXEMPLAR /*
EXEMPLAR */
FOR DELETE /* */