Karpova_bazy_dannyh

263

From books

WHERE YEARIZD=@YEARIZD AND PUBLICH =@PUBLICH

/* Select @TEK_Count */.

/* ,

, ,

, «BHW» 1999

.

, -

, .

. , -

.

, -

, . ,

,

.

, -

,

«» -

, . B

- , , :

/*

*/

if exists (select * from sysobjects where id = object_1d{'dbo NEW_BOOKS'} and sysstat & Oxf =4)

/* , */ 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)

/*

DECLARE @TEK int

/ * BOOKS */

INSERT INTO BOOKS VALUES (@ISBN.@TITL.@AUTOR.@COAUTUR. @YEARIZD.@PAGES)

/* */ SELECT @ = @NUM_EXEMPL

/* */

WHILE @>0 /* */ BEGIN

/* IDENTITY, . , ,

INSERT.

.

*/.

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) /*

@NUN_EXEMPL INT */ AS

DECLARE @TEK Int Declare @INV int

INSERT INTO BOOKS VALUES (@ISBN. @TITL @AUTOR. @COAUTOR. @YEARIZD. @PAGES)

/* */ SELECT @TEK = NUM_EXEMPL

/* */

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

.

.

/* */ 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.

/* , , -

/* , , - */

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

/* , , -

*/

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

4333-55-99'. '444-66-881. '01.06.83'. @NUM READER =@K OUTPUT. @Y_N=@N OUTPUT.@COUNT_BOOKS=@B OUTPUT

--, ,

--.

Select '', @. '-' @N. '-'. @B

:

, - , :

, . . , , 0() .

.

, -

SQL-, -

, , 5 SQL-, - , (. . 12.2).

, , ,

, SQL

.

Select

-

( )

-

. 12.2. SQL

-

«-».

. 12. 3 SQL ,

. 12.4 SQL,

.

.

.

.

.12.3. SQL-

271

.12.4.

.

SQL1 , .

SQL2, 1996 ,

.

: CREATE TRIGGER <_>

ON <_>

FOR {[INSERT]. [UPDATE] [DELETE] } [WITH ENCRIPTING]

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 /* */