Karpova_bazy_dannyh

, ,

, ,

.

4. CLOSE -

. -

. CLOSE

. ,

MS SQL Server 7.0.

:

DECLARE <_> CURSOR FOR <_ > <> = <_ SELECT>

, .

, ,

.

DECLARE Debtor_reader_cursor CURSOR F0R

SELECT READERS FIRST_NAME. READERS LAST_NAME. READERS ADRES.

READERS HOME_PHON, READERS WORK_PHON. BOOKS TITLE

FROM READERS, BOOKS EXEMPLAR

WHERE READERS READER_1D = EXEMPLAR READERJD AND

BOOKS ISBN = EXEMPLARS ISBN AND

EXEMPLAR DATA_OUT > Get date ()

ORDER BY READERS FIRST_NAME

.

DECLARE <_> [INSENSITIVE] [SCR0LL] CURSOR

FOR <SELECT>

INSENSITIVE ()

, , -

,

. ,

,

.

,

, «».

SCROLL ,

(FIRST. LAST. PRIOR. NEXT, RELATIVE, ABSOLUTE) FETCH.

, ,

-

.

UPDATE [0F <_ 1> [...< n>]]

,

. .

, ,

.

. ,

, , -

.

:

DECLARE Debtor_reader_cursor INSENSITIVE CURSOR

FOR

SELECTREADERS.FIRST_NAME,READERS.LAST_NAME.READERS.ADRES.

READERS.H0ME_PHON; READERS WQRK_PHON. BOOKS,TITLE

FR READERS.BOOKS.EXEMPLAR

WHERE READERS. READER_1D EXEMPLAR. READER _1O AND

BOOKS ISBN -EXEMPLARE ISBN AND

EXEMPLAR,DATA_OUT >Get date()

ORDER BY READERS FIRST_NAME

FOR READ ONLY

SELECT

.

SQL CODE.

, SQL.

- SQL CODE.

-

, , -

.

, - (COMMIT) (ROLLBACK).

FETCH

.

.

, -

FETCH :

FETCH <_> INTO < >

-

:

FETCH Debtor_reader_cursor into @FIRST_NAME, @LAST_NAME, @ADRES, @HOME_PHON @WORK_PHON, @TITLE

FETCH :

FETCH

{NEXT | PRIOR | FIRST | LAST

| ABSOLUTE {n | <_>} | RELATI VE {n | <_>}}

FROM

<_> INTO <>

NEXT

, . PRIOR -

. FIRST

, LAST

.

, , -

, , -

ABSOLUTE, , RELATIVE.

, .

FETCH - SQL2

SCROLL. -

. , -

. , -

,

.

, ,

, -

.

.

,

:

CLQSE <_>

,

, .

.

.

, (SCR0LL),

,

.

, .

, ,

.

, , ,

. -

.

, -

, . , , -

.

, , ,

.

, . -

, ,

, -

.

, , (Stored Procedure) , . , . . -

,

. -

,

.

, .

-

, SQL, -

, ,

, -

. -

, Oracle -

PL /SQL/, MS SQL Server System 11 Sybase

Transact SQL. Oracle Java

.

. -

;

. -

.

SQL GREATE PROCEDURE.

,

, .

.

,

, , -

.

MS SQL Server :

CREATE PROCEDURE] <_> [<>] [{@1 _}

[VARYING] [= <__>] [OUTPUT]] [. . N..]

[WITH

| RECOMPILE

| ENCRYPTION

| RECOMPILE, ENCRYPTION}] [FOR REPLICATION]

AS

VARYING

.

RECOMPILE , -

. REC0MPILE,

, . -

. , , , -

, , -

, ,

, , .

ENCRYPTION ,

. ,

, -

. ,

, , -

261

. ,

-

. -

,

.

, .

, OUTPUT, , .

.

/*

@ISBN

,

, ,

*/

CREATE PROCEDURE COUNT_EX (@ISBN varchar (12)) AS

/* */ DECLARE @_OUNT int

/* SELECT

,

, */

select @TEK_COUNT = select count(*) FROM EXEMPLAR WHERE ISBN = @1$BN AND READER_ID Is NULL AND EXIST = True

/* 0 - , */

RETURN @TEK_COUNT

.

:

EXEC <> <___> <___>

, .

, » «Oracle 8.

», ISBN 966-7393-08-09,

:

/*

@Ntek - ©ISBN - */

declare @Ntek int

DECLARE ©ISBN VARCHAR(14)

/ * © ISBN */

Select USBH * '966-7393-08-09

/* @Ntek COUNT_EX */

EXEC ©Ntek = COUNT_EX @ISBN

,

. , , 2 COUNT_EX :

EXEC @Ntek = COUNT_EX.2 ©ISBN

, ,

.

:

, , , -

. , .

CREATE PROCEDURE COUNT_BOOKS (@YEARIZD int = YEAR Get date ()). (PUBLICH varchar(20))

/* ,

@YEARIZD int © PUBLICH

*/ AS

DECLARE (STEK Count int

Select @TEK_count = Select COUNT (ISBN)