CASE- ERWin
ERWin - . - , , , , , " ", "" " ". , , ( ). , . .
, , , . . (, ), . , . , , - , , , . . .
13) -?
- . , - . , , , . - -- . , ERD .
14. , ?
, , - . , .
15. ? ?
, . , . ERWin , (Subject Area). , , , .
|
|
|
- : , .
16) ER-. ER-. ER-?
. (Relationship Verb Phrases). - .
--, -- --.
. ( ) ( ) . .. . . .
, . .
, .
ERWin - , . , . . . ( ) , - , - .
, . . , . , , . , , "" , . . .
- ,
17) ER-? ?
. (Relationship Verb Phrases). - . --, -- --. . ( ) ( ) . .. . . . , . . , . ERWin - , . , . . . ( ) , - , - . , . . , . , , . , , "" , . . . - ,
|
|
|
18) ? ? ?
(primary key) - , . - , .
, . , . (candidate key).
, . . . - :"
(Foreign Key) , : ( ). (FK) .
.
19) ?
. - . , , . , . - . :
|
|
|
- (1NF);
- (2NF);
- (3NF);
- - ( 3NF);
- (4NF);
- (5NF).
. , , . .
. , , .
20) .
, . , . , 1NF , . 1NF .
, . , . , - , .
. , 4286
24. ORACLE
Oracle , . , , Oracle , .
(shared memory)? Oracle : , . ( ). Oracle (System Global Area SGA) (Program Global Area PGA). SGA PGA .
(SGA)
SGA , Oracle Oracle. SGA Oracle . Oracle SGA. SGA ( ):
. , , ( ); , ( ). , ( , / ).
25 ? , , . , , . , , , . Oracle. , Windows NT Oracle. . :
|
|
|
1. SMON - PMON
2. DBWR
3. LGWR
4. Dnnn
5. ARCH
6. CKPT
7. RECO
8. SPNn
9. LCKn
10. Pnnn
11. Snnn
User Server , . , . SMON, PMON . PMON - (Process Monitor) , . - , PMON "" ( ). "" , , , . SGA. PMON . SMON - , . , , . "" ! ! ( ).:) , , . , , , pctincrease 0. , . SMON, PMON , . SMON PMON.
27) - ?
1 30 , 8 , 128
, , , $ # _
VARCHAR2
VARCHAR2 , . {}
31. (DDL)?
(DDL Data Definition Language) , Microsoft Access. , SQL , - .
CREATE TABLE, CREATE INDEX, CREATE PROCEDURE, CREATE USER, CREATE GROUP, CREATE VIEW, ADD USER, DROP USER, DROP GROUP, ALTER TABLE, ALTER USER, ALTER DATABASE, DROP, GRANT, REVOKE CONSTRAINT.
. (DDL) - , IV, . IV , , , - . .
32. SQL?
< > (<close statement>)
.
<close statement>::=
CLOSE <cursor name>
, .
.
< > (<cursor soecificaton>).
< > (<commit statement>)
.
<commit statement>::=
COMMIT WORK
.
, .
|
|
|
, , .
8.3. < > (<declare cursor>)
.
< > (<cursor name>) < > (<cursor name>), - < > (<declare cursor>) .
< > (<parameter name>), < > (<cursor specification>), < > (<paremeter declaration>) <> (<procedure>) <> (<module>), < > (<open statement>), < > (<cursor name>).
33. ? , ?
SELECT SQL, () .
, , . SELECT DML. SELECT , , . , .
SELECT :
SELECT
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC],...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC],...]
:
SELECT FROM WHERE
, SELECT:
WHERE , GROUP BY.
GROUP BY .
HAVING , GROUP BY .
ORDER BY , .
ISO SQL:2003 :
,
SELECT
34,35 .?
- , WHERE (HAVING) SELECT , WHERE . WHERE (HAVING) .. , , , , (, ).
, . , ( ), .
36. PL/sql. ?
PL/SQL (Procedural Language / Structured Query Language) - ANSI SQL, Oracle.
PL/SQL - , SQL. (3GL), C++, Pascal . . PL/SQL .
PL/SQL , ADA , (package).
PL/SQL , . .
PL/SQL - , , .
PL/SQL , , , .
SQL . , , , . PL/SQL , , Oracle, ( ) . SQL PL/SQL-, ( ).
PL/SQL :
- , ;
--;
--;
--;
36,37. :
1. IF - THEN - END IF;
2. IF - THEN - ELSE - END IF;
3. IF - THEN - ELSIF - END IF;
4. CASE - WHEN - THEN - END CASE;
IFTHEN :
IF THEN
... ...
END IF;
, , TRUE, FALSE NULL. , THEN END IF, , TRUE, FALSE NULL.
IFTHENELSE , . IF :
IF THEN
... , ,
TRUE...
ELSE
... , ,
FALSE NULL...
END IF;
, . TRUE, , THEN ELSE, FALSE
IFTHENELSIF
IFTHENELSIF CASE.
ELSIF THEN, ELSE . , , IFTHENELSIF . TRUE, , ELSE. ELSE , IFTHENELSIF .
CASE - WHEN - THEN - END CASE;
CASE PL/SQL . :
38,36 :
1. LOOP - END LOOP;
2. WHILE - LOOP - END LOOP;
3. FOR - LOOP - END LOOP;
4. EXIT;
5. EXIT WHEN;
PL/SQL FOR: . FOR, . , FOR LOOP. ( ), , ( ).
1) . loop, ( ) end loop:
LOOP
_
END LOOP;
2) WHILE . , TRUE. , , . WHILE:
WHILE
_
END LOOP;
, :
FALSE NULL
:
-, , ; -, , ; -, ,
3) FOR:
FOR _ IN [REVERSE]
_.. _ LOOP
_ END LOOP;
LOOP END LOOP . FOR .
:
, . ( EXIT, )
39. .
:
-------------- CREATE [OR REPLACE] PROCEDURE - _ ----------------------------------
-------------- ( [IN] [OUT] [IN OUT] ,.....) AS [IS] -----------------------------
-------------- -----------------------------------------------------------------
:
(_) - ,
() - ,
() - ,
( ) - PL/SQL .
, , , REPLACE. , DROP PROCEDURE .
.
, , .
:
-------------- CREATE [OR REPLACE] FUNCTION - _ -------------------------------------
-------------- ( [IN] [OUT] [IN OUT] ,.....) AS [IS] -----------------------------
-------------- -----------------------------------------------------------------
-------------- RETURN (_) -------------------------------------------------
- , , RETURN. RETURN . , , - , .. .
BOOLEAN VARCHAR2:
(package). . PL/SQL Ada. .
:
. , , :
. , , , . , .
40, 36 ?
. SQL (Oracle, Microsoft SQL Server) .
PL/SQL
PL/SQL : . , .
, . , , . , .
PL/SQL, .
CURSOR .
OPEN , .
FETCH .
CLOSE
%ISOPEN TRUE, .
%FOUND , , .
%NOTFOUND TRUE, .
%ROWCOUNT .
41,36 SQL-? SQL- , SQL- , SQL-, , ; , , SQL- [ - . 1. . ], . , SQL-, SQL-, . SQL-. 1 . 1 DBMS_SQL, PL/SQL , SQL- . SQL- , SQL- "Programmer`s Guide to the Oracle Precompilers" ( Oracle). , , DBMS_SQL, SQL- PL/SQL.
42, 36 ?
, . , .
CASE:EXCEPTION
WHEN _1 THEN
_1
WHEN __N THEN
_n
WHEN OTHERS THEN
_ others
END;
__1, __1; __N, __N . , WHEN OTHERS , WHEN ( ELSE IF).
43,36 !
, PL/SQL , . . , , . - . , PL/SQL - , DML - INSERT, UPDATE, DELETE. , .
:
1. , , .
2. . , - .
3. , .
4. " ".
5. .
, :
--- CREATE [OR REPLACE] TRIGGER _ ---------------------
--- BEFORE | AFTER _ ON __ ---
--- FOR EACH ROW [WHEN _] ---------------------
--- _ ------------------------------------------------
:
_ - .
_ - BEFORE DML, AFTER DML.
__ - , .
FOR EACH ROW - , DML.
_ - TRUE , FALSE .
_ - .
44 ?
SQL-, . (commiting) . (rollbacking) , .. .
:
SQL*Net.
.
SQL- ( ). , .
SQL- SQL-. SQL-, . , , , SQL-. , SQL- , , SQL-.
SGA ( ) SGA.
SGA. , . DBWR ( , ).
COMMIT () ROLLBACK (). COMMIT , ROLLBACK . , LGWR .
, . - , .
: , (redo log file). , .
ORACLE , , .
-COMMIT. COMMIT . .
-ROLLBACK. . , . .
45. ?
,
,
,
SYSTEM:
ORACLE7 - SYSTEM. ORACLE7 SYSTEM . - , . : .
