Karpova_bazy_dannyh

,

253

, ,

, ,

.

4. CLOSE -

. -

. CLOSE

. ,

MS SQL Server 7.0.

:

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

		.
		.
,	OPEN	-
,	,	-
,		.

, .

, ,

.

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

	Transact SQL Get date (),	-
.	,	-
,	,
,	.
SQL2 Transact SQL

.

DECLARE <_> [INSENSITIVE] [SCR0LL] CURSOR

FOR <SELECT>

[FOR {REA0 ONLY | UPDATE [OF <

_

1> [ n]]}]

254

12.

SQL

INSENSITIVE ()

, , -

,

. ,

,

.

,

, «».

SCROLL ,

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

SCROLL,		-
NEXT	F	.
READ ONLY (	),	-
		-
.		, -
	READ ONLY,

, ,

-

.

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

,

255

SELECT

.

		:
OPEN < _	> [USING <	>]
		,	-
	,	DECLARE_CURSOR.	-
	OPEN		,
	2 5	( . 12.1),	-
		,	-
		.	-
		,	.

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

256

12.

SQL

FETCH :

FETCH

{NEXT | PRIOR | FIRST | LAST

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

FROM

<_> INTO <>

NEXT

, . PRIOR -

. FIRST

, LAST

.

, , -

, , -

ABSOLUTE, , RELATIVE.

, .

FETCH - SQL2

SCROLL. -

. , -

. , -

	SQL.
	FETCH:	, -
,	,	-

,

.

, ,

, -

.

.

,

:

CLQSE <_>

,

, .

.

,

257

-

.

(). , MS SQL Server 7.0

:

DEALLOCATE <_>

, -

, ,

.

DEALLOCATE SQL Server

, DECLARE. OPEN .

-

-

. , -

				,
				.
		,		.
	,		,	-
:
DELETE FROM < _	> WHERE CURRENT OF <			>
				,
	,			,
	.	,		FROM
DELETE,	,
FROM	.
		,		-
FETCH NEXT.
			.
		:

UPDATE <_ [{<__N>={< WHERE CURRENT OF <

>	SET <	_	1>= {<	> | NULL}
	> | NULL}}		]
_	>

-

, .

.

(DELETE) (UPDATE),

. SQL1, :

258			12.	SQL
,	,				-
,	FROM	SELECT
(DECLARE CURSOR),			.
			ORDER BY.
	,
		DISTINCT.
		,		-
	GR0UP Y	HAVING.
	,			-
	,
13).	(
		,	,
	,	,	,	-
		.	READ ONLY,	-
			.	, -
.	,			-
		-	,	-
	,	,	,	,
			.
			,	-
			:
		.
	,	COMMIT	.
		,		-

.

, (SCR0LL),

,

.

	-
,	FETCH,
	,
	.
,	READ ONLY.

				259
«	-	»,	,	-
,				-
		,		.
	,

, .

, ,

.

, , ,

. -

.

, -

, . , , -

.

, , ,

.

, . -

, ,

, -

.

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

,

. -

,

.

, .

-

, SQL, -

, ,

, -

. -

, Oracle -

PL /SQL/, MS SQL Server System 11 Sybase

Transact SQL. Oracle Java

.

260

12.

SQL

. -

;

. -

.

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

, .

262

12.

SQL

, » «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

, ,

.

:

EXEC <		> < _	1>=<	1>
< _	N>=<		N>

, , , -

. , .

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)