Karpova_bazy_dannyh
.pdfSQL |
|
|
273 |
|
AS |
|
|
|
|
/* |
*/ |
|
|
|
DECLARE @Ntek int /* |
|
|
*/ |
|
SECLARE © DEL_EX VARCHAR(12) /* |
*/ |
|
|
|
Begin /* |
, |
, |
- |
|
|
|
*/ |
|
|
SELECT @DEL_EX = ISBN from deleted
/* , */
EXEC @Ntek = COUNT_EX @DEL_EX
/* , - BOOKS */
IF @Ntek = DELETE from BOOKS WHERE BOQKS ISBN =@DEL_EXEN0GQ
SQL
SQL, , -
SQL. SQL SQL
. SQL. , SQL
|
. |
|
|
SQL- |
|
|
, |
« |
», |
|
. |
|
SQL- |
, |
|
, |
, |
, |
- |
|
, |
|
- |
. |
|
|
|
.
SQL .
SQL -
. ,
|
|
|
. |
|
12.5 |
|
|
|
SQL- |
SQL |
|
SQL. |
, |
SQL |
|
|
|
, |
- |
SQL. |
, |
, |
, |
- |
SQL. , SQL
. ,
-
, , . ,
SQL
.
274 |
12. |
SQL |
SQL -
EXECUTE IMMEDIATE. -
:
EXECUTE IMMEDIATE <_> SQL .
- , . SQL, -
|
|
, |
|
|
|
, |
SQL |
|
|
. |
|
|
|
|
, |
|
SQL- |
|
- |
SQL |
. |
|
|
, |
|
4 |
SQL- |
, |
- |
|
: |
, |
|
- |
.
PREPARE, :
PREPARE <_> FROM <_> <_> .
- EXECUTE, :
EXECUTE <_> USING {<> | DESCRIPTOR <_>}
DESCRIPTOR , ,
. |
- |
, .
. SQL2 SQL.
SQL2 :
ALLOCATE DESCRIPTOR <_> [WITH <_>]
-
.
DEALLOCATE DESCRIPTOR <_> - , . -
.
SET DESCRIPTOR {COUNT - <_> | VALUE <-
> {<_ > <_>[...]}} -
.
, , , .
SQL |
275 |
GET DESCRIPTOR {<_ COUNT | VALUE <-
>, {<_>=<_}}-
.
DESCRIBE [INPUT | OUTPUT] <_> USING SQL DESCRIPTOR <_pa> , - (DESCRIBE OUTPUT) (DESCRIBE
INPUT). |
|
|
|
|
|
|
OPEN < |
_ |
|
> [USING < |
|
> | |
USING SQL |
DESCRIPTOR < |
_ |
>] |
|
|
|
|
FETCH < |
_ |
|
> [USING « |
|
> | USINGSQL |
|
DESCRIPTOR< |
_ |
>] |
|
|
|
|
. |
|
|
|
|
|
|
DEALLOCATE PREPARE < _ |
> |
|
- |
|||
|
|
|
|
SQL |
|
|
, |
|
|
|
. |
|
- |
, |
|
|
|
|
|
- |
|
|
|
SQL. |
|
|
|
|
, |
|
|
|
|
- |
SQL |
|
|
, |
|
- |
|
|
|
|
|
|
, |
- |
|
|
|
|
|
|
- |
.
13
:
. «»,
,
, .
:
-
()
.
.
.
, |
, |
- |
|
, |
|
|
. |
- |
. |
|
|
|
|
. |
|
. |
- |
|
|
. |
|
, |
- |
|
, |
|
|
|
.
|
. |
|
. |
PUBLIC, |
- |
277
. |
, |
, |
, |
PUBLIC.
(), .
|
|
« |
». |
|
|
. |
|
, |
|
. |
|
, |
|
|
. |
, |
. |
, |
|
|
, |
|
|
, .
|
|
|
|
. |
|
, |
|
, |
, |
: |
, |
, |
|
. |
|
|
, |
|
- |
.
|
. |
|
|
|
|
: |
( |
). |
|
|
, |
|
, |
- |
|
|
. |
|
|
|
, |
, |
- |
|
|
|
|||
|
, |
|
, |
|
|
. |
|
|
|
|
|
|
|
, |
|
. |
|
|
. |
|
|
|
|
|
|
|
|
. |
, |
|
|
|
|
|
|
|
, |
|
. |
|
|
|
|
|
|
. |
- |
|
|
|
, |
|
|
- |
|
. |
|
|
|
|
|
|
|
|
|
. |
|
|
- |
|
|
|
|
|
( |
, MS SQL Server, Sybase). |
|
|
|
278 |
13. |
Oracle ,
,
.
SQL ,
-
,
.
.
SQL : GRANT REVOKE -
.
:
GRANT {<_ | AIL PRIVILEGES}
ON <_> {<_> | PUBLIC} [WITH GRANT OPTION ]
.
ALL PRIVILEGES ,
.
<_> : , , , .
<_> PUBLIC , .
|
WITH GRANT OPTION |
|
, |
|
, |
|
|
|
. |
|
|
|
|
|
|
|
. |
|
|
|
|
|
|
|
|
|
|
|
|
, |
|
|
|
- |
|
. |
user1. user2 |
user3. |
|
|
- |
|
|
|
|
|
|
|
User1 |
|
Tab1, |
|
|
|
- |
|
|
|
|
. |
, |
- |
|
user2 |
|
, |
|
|
Tab1 |
( |
, |
|
), |
user3 |
|
|
|
( |
, |
|
), |
|
- |
|
|
. |
|
|
|
|
|
|
|
|
|
|
- |
|
|
: SELECT, INSERT, DELETE, UPDATE. |
|
- |
||
|
|
|
|
. |
|
|
:
GRANT {[SELECT] [INSERT] [DELETE] [UPDATE (<_>)]} ON <_>
279
{<_> | PUBLIC} [WITH GRANT OPTION]
:
GRANT INSERT
ON Tab1
TO user2
GRANT SELECT
ON Tab1
TO user3
, |
user2 |
|
|
Tab1, |
user3 |
|
|
Tab1. |
|
|
|
|
. |
, |
, |
|
|
||
|
|
. |
, |
COST |
Tab1. |
|
- |
user3 |
|
|
: |
GRANT SELECT. UPDATE (COST)
ON Tab1
TO user3
user1 , user4 -
,
Tab1.
GRANT ALL PRIVILEGES
ON b1
TO user4
WITH GRANT OPTION
user4
Tab1 user1 . user5,
:
GRANT INSERT
ON b1
user5
, |
, |
, |
|
||
, |
, |
. |
user4 |
|
: |
280 |
13. |
GRANT SELECT, UPDATE. DELETE
ON Tab1
TO user4
WITH GRANT OPTION.
user4 user5, .
-
, -
-
.
,
, , ,
SELECT,
, -
4 : SELECT, INSERT, UPDATE DELETE.
SQL - REVOKE. :
REVOKE {< | ALL PRIVILEGES} ON <_>
FROM {<> | PUBLIC} {CASCADE | RESTRICT}
CASCADE |
RESTRICT |
, |
|
. |
CASCADE |
|
|
, |
|
GRANT |
- |
|
, |
, |
|
, |
|
WITH GRANT OPTION. |
|
, :
REVOKE ALL PRIVILEGES
ON Tab1
TO user4 CASCADE
user5, |
|
user4 |
. |
|
|
RESTRICKT |
, |
- |
REVOKE. |
|
- |
. |
, |
, |
: |
REVOKE ALL PRIVILEGES |
|
|
|
ON Tab1 |
|
|
|
TO user4 RESTRICT |
|
|
|
281
, user4 - users.
REVOKE
.
,
PUBLIC.
REVOKE: REVOKE INSERT
ON Tab1
TO user2.user4 CASCADE
, -
GRW REVOKE,
, () -
, PUBLIC.
, REVOKE.
REVOKEEXECUTE
ON COUNT_EX
TO PUBLIC CASCADE
user4.
GRANT EXECUTE
ON COUNTJX
TO user4
.
:
GRANT CREATE TABLE. ALTER TABLE, DROP TABLE ON DBJ.IB
TO user1
user1 , DB_LIB,
|
, |
|
- |
|
. |
|
|
, |
MS SQL Server |
. |
- |
|
|
||
main |
user |
. |
|
|
: |
|
|
GRANT CREATE DATABASE
ON SERVER_0
TO main user
282 |
|
|
13. |
|
|
|
, |
main user, |
, |
|
|
|
. |
|
|
|
|
, |
|
|
, |
|
|
. |
, |
Oracle |
( |
, |
|
|
|
|
|
|
|
, 80 |
). |
. |
. |
|
|
|
|
|
|
||
|
|
|
|
, |
|
|
|
, |
|
- |
|
|
|
. |
|
- |
|
|
. |
, |
, |
- |
|
|
|
(CREATE TABLE), |
|
|
|
|
|
, |
|
|
|
|
CREATE TRIGGER. |
Oracle |
- |
||
|
, |
|
|
. |
|
|
|
Oracle |
|
- |
|
|
|
, |
|
|
|
.
MS SQL Server
SQL server 6.5 |
3 |
: |
1. |
(standard). |
|
2. |
(integrated security). |
|
3. |
(mixed). |
|
, |
|
NT Server. |
- |
. |
- |
. |
- |
. |
|
SQL Server. |
SQL Server |
MS |
SQL Server. |
. |
|
. |
- |
SQL Server |
. |
, |
|
, |
, a SQL |
Server |
. |
|
. |