7.7 ???SQL - PowerPoint PPT Presentation

1 / 120

About This Presentation

Title:

7.7 ???SQL

Description:

Title: Author: RUC IDKE Last modified by: ljf Created Date: 8/9/2000 8:19:19 AM Document presentation format – PowerPoint PPT presentation

Number of Views:44

Avg rating:3.0/5.0

Slides: 121

Provided by: RUCI4

Category:

more less

Transcript and Presenter's Notes

Title: 7.7 ???SQL

1
??? ????????

7.1 ???????
7.2 ????
7.3 ??????
7.4 ??????
7.5 ????????
7.6 ????????
7.7 ???SQL
7.8 ????
7.9 ODBC??

2
?????

???????SQL???????????????????????SQL(ESQL)?????(?
???SQL)?ODBC(???????)?JDBC(Java?????)?OLEDB(??????
????)????

3
7.7 ? ? ? SQL

SQL??????????????
???
???
?????????SQL
SQL?????????
????????????
???????????,?????????,SQL???????????

4
7.7 ? ? ? SQL

3.7.1 ???SQL?????
3.7.2 ???SQL???????????
3.7.3 ?????SQL??
3.7.4 ?????SQL??
3.7.5 ??SQL??

5
7.7.1???SQL?????

????SQL????????,??
??EXEC SQL
??????????????
?C????????SQL???????
EXEC SQL ltSQL??gt
?EXEC SQL DROP TABLE Student
?COBOL?????????SQL???????
EXEC SQL ltSQL??gt END-EXEC
? EXEC SQL DROP TABLE Student END-EXEC

6
DBMS??????????SQL ???

???
?????????????SQL??

7
???

1.?DBMS??????????????,???SQL??
2.?????????????,?????????????
3.????????????????????????

8
??SQL??

?????
??SQL?? ????
????? ????
????
?????????????????,???????SQL??
???????????,???????SQL??

9
3.7.2 ???SQL???????????

?SQL????????????,?????
????????????
SQL??
???????????
???????
??????
???????????
????????

10
???????????

1. SQL???
??????SQL?????????
?????????????
2. ???
1)????SQL??????
2)?SQL???????????????????
3. ??
?????????????????????

11
1. SQL???

SQLCA SQL Communication Area
SQLCA???????
SQLCA???
SQL?????,DBMS?????????
??????????
??????
???????SQL???SQLCA?
?????SQLCA?????????,????????????

12
SQLCA???

?????SQL????
??SQL?????????
????????DELETE?,???????,SQLCA???????
????????,????
????????,???????
????,???????
?????????
??????,????

13
SQLCA?????

??SQLCA
?EXEC SQL INCLUDE SQLCA????
??SQLCA
SQLCA??????????SQL??????????SQLCODE
??SQLCODE????????SUCCESS,???SQL????,??????
??????????SQL ???????????SQLCODE??,????SQL????????
????

14
2. ???

??????
???SQL???????????????????????
?SQL???????????????????(Host Variable)

15
???(?)

??????
?????
?????????,SQL????
?????
?SQL???????????,???????
??????????????????????

16
???(?)

??????
?????
????????????
??????????????
???????
??WHERE???HAVING??????
?????
??SQL???????
??SQL???????

17
???(?)

????
???????????????(Indicator Variable)
???????
????
??????????????
???????
????????????????
????????????????????,??????

18
???(?)

?SQL????????????????
1) ??????????
BEGIN DECLARE SECTION
.........
......... (??????????)
.........
END DECLARE SECTION

19
???(?)

2) ?????
???????????SQL???????????????????
?????????(??????????)??,SQL?????????????()????

20
???(?)

3) ??????
?????????????
????????????

21
???(?)

?SQL????(??????)?????????????
??????,?????

22
3. ??(cursor)

????????
SQL????????????????
SQL????????,??SQL????????????????

23
??(?)

?????????,???????????????
?????????????SQL??????????????
???SQL????????,??????????????

24
??(?)

?????
??????????????????,??SQL???????
???????????
?????SQL????????????,??????,??????????

25
???SQL???????????(?)

??
????SQL?,SQL??????????????
SQL???????????
?????
1. ??????
2. ?SQL???????????
????

26
???SQL???????????(?)

SQL?????????????????,?????
SQL????????DBMS??SQLCA?
??????SQLCA???????,?????????
??SQL???????????????,?????????????????
SQL??????????????????????

27
???SQL???????????(?)

??????SQL????C??
............
EXEC SQL INCLUDE SQLCA
/ (1) ??SQL??? /
EXEC SQL BEGIN DECLARE SECTION
/ (2) ????? /
CHAR title_id(7)
CHAR title(81)
INT royalty
EXEC SQL END DECLARE SECTION

28
???SQL???????????(?)

main()
EXEC SQL DECLARE C1 CURSOR FOR
SELECT tit_id, tit, roy FROM
titles
/ (3) ????(????)/
/ ?titles???? tit_id, tit, roy
/
EXEC SQL OPEN C1
/ (4) ????(????)/

29
???SQL???????????(?)

for()
EXEC SQL FETCH C1 INTO title_id,
title, royalty
/ (5) ????(?????????????????)/
if (sqlca.sqlcode ltgt SUCCESS)
/ (6) ??SQLCA?????????????? /
break
printf("Title ID s, Royalty d",
title_id, royalty)
printf("Title s", title)
/ ?????? /
EXEC SQL CLOSE C1
/ (7) ????(????)/

30
7.7.3 ?????SQL??

?????SQL?????
?????
??????
??????
?????????SELECT??
?CURRENT???UPDATE??
?CURRENT???DELETE??
INSERT??

31
???????

????????????SQL???????SQLCA?????
?????
1. EXEC SQL BEGIN DECLARE SECTION
2. EXEC SQL END DECLARE SECTION
???????????,???????,?????????????
??SQLCA
3. EXEC SQL INCLUDE SQLCA

32
????????

?1 ???????Student
EXEC SQL CREATE TABLE Student
(Sno CHAR(5) NOT
NULL UNIQUE,
Sname CHAR(20),
Ssex CHAR(1),
Sage INT,
Sdept CHAR(15))

33
??????(?)

???????????????
?????????
EXEC SQL DROP
TABLE table_name

34
????????

?2 ???Student???????U1
EXEC SQL GRANT SELECT ON
TABLE Student TO U1

35
???????????SELECT??

????
EXEC SQL SELECT ALLDISTINCT
lt??????gt,lt??????gt..
.
INTO lt???gtlt????gt
,lt???gtlt????gt...
FROM lt??????gt,lt??????gt ...
WHERE lt?????gt
GROUP BY lt??1gt HAVING lt?????gt
ORDER BY lt??2gt ASCDESC

36
?????????SELECT??(?)

????SELECT???????????INTO??
????????????????,??INTO???????????

37
?????????SELECT??(?)

??????
1. ?????
INTO??
WHERE????????
HAVING????????

38
?????????SELECT??(?)

2. ??????
????????INTO???
??INTO??????????????,????????????????,????????????
??????????,?????????????,??????????SQL??????
????????????,????????,?????????NULL

39
?????????SELECT??(?)

3. ???????
???????????????,???????,?DBMS?SQLCODE????100
4. ?????????
????,DBMS??SQLCA???????

40
?????????SELECT??(?)

?3 ?????????????
???????????????????givensno
EXEC SQL SELECT Sno, Sname, Ssex, Sage, Sdept
INTO Hsno, Hname, Hsex,
Hage, Hdept
FROM Student
WHERE Snogivensno
Hsno, Hname, Hsex, Hage, Hdept?givensno?????,?????
??????????

41
?????????SELECT??(?)

?4 ????????????????
???????????????????givensno,??????????givencno
?
EXEC SQL SELECT Sno, Cno, Grade
INTO Hsno, Hcno,
HgradeGradeid
FROM SC
WHERE Snogivensno AND
Cnogivencno

42
?????????SELECT??(?)

?????????????????,SELECT???????????????
????????????SELECT???????????
???????????,?SELECT???????????,?????????

43
???CURRENT???UPDATE??

?CURRENT???UPDATE??
?????
SET??
WHERE??
??????
SET??
?CURRENT???UPDATE??????????

44
?CURRENT???UPDATE??(?)

?5 ?????1??????????????
?????????????Raise
EXEC SQL UPDATE SC
SET GradeGradeRaise
WHERE Cno'1'

45
?CURRENT???UPDATE??(?)

?6 ??????1???????
??????????????givensno,
????????????newgrade?
EXEC SQL UPDATE SC
SET Gradenewgrade
WHERE Snogivensno

46
?CURRENT???UPDATE??(?)

?7 ????????????NULL?
Sageid-1
EXEC SQL UPDATE Student
SET SageRaiseSageid
WHERE Sdept'CS'

47
?CURRENT???UPDATE??(?)

?????Sageid??????,?????Raise???,DBMS???CS?????????
?????????
EXEC SQL UPDATE Student
SET SageNULL
WHERE Sdept'CS'

48
???CURRENT???DELETE??

?CURRENT???DELETE??
?????
WHERE??
?CURRENT???DELETE??????????

49
?CURRENT???DELETE??(?)

?8 ???????,?????????????????
??????????????stdname
EXEC SQL DELETE
FROM SC
WHERE Sno
(SELECT Sno
FROM Student
WHERE Snamestdname)

50
??INSERT??

?CURRENT???INSERT??
?????
VALUES??
??????
VALUES??
?CURRENT???INSERT????????????

51
INSERT??(?)

?9 ????????????,???????SC?
?????????????stdno,?????????couno?
gradeid-1
EXEC SQL INSERT
INTO SC(Sno, Cno, Grade)
VALUES(stdno, couno,
grgradeid)
??????????,????,??????????????????????????????

52
7.7.4 ?????SQL??

???????SQL??
??????????SELECT??
CURRENT???UPDATE??
CURRENT???DELETE??

53
?? ??????????SELECT??

???????
1. ????
2. ????
3. ??????,???????
4. ????

54
1. ????

??DECLARE??
????
EXEC SQL DECLARE lt???gt CURSOR
FOR ltSELECT??gt
??
????????,??DBMS????SELECT????????

55
2. ????

??OPEN??
????
EXEC SQL OPEN lt???gt
??
?????????????SELECT??,??????????????????????
??????????,?????????????????

56
3. ??????,???????

??FETCH??
????
EXEC SQL FETCH NEXTPRIOR
FIRSTLAST FROM lt???gt
INTO lt???gtlt????gt
,lt???gtlt????gt...

57
??????,???????(?)

??
??????????,??????????????????????????????
NEXTPRIORFIRSTLAST????????????
NEXT????????
PRIOR???????
FIRST???????
LAST????????
????NEXT

58
??????,???????(?)

??
(1) ??????SELECT??????????????????
(2) FETCH?????????????,??????FETCH????????????????
(3) ????????????,??????????????FETCH??????,???????
?????????????

59
4. ????

??CLOSE??
????
EXEC SQL CLOSE lt???gt
??
????,????????????????
??
??????,???????????????
?????????????,??????????

60
??

?1 ????????????(???????????)????????????????????,?
????deptname?
......
......
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION

61
??(?)

......
/ ????? deptname,HSno,HSname,HSsex,HSag
e?/
......
......
EXEC SQL END DECLARE SECTION
......
......
gets(deptname) / ????deptname??
/
......

62
??(?)

EXEC SQL DECLARE SX CURSOR FOR
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE SDeptdeptname / ????
/
EXEC SQL OPEN SX / ???? /

63
??(?)

WHILE(1) / ???????????????? /
EXEC SQL FETCH SX INTO HSno, HSname,
HSsex, HSage
/ ???????????,????
???????,??????/

64
??(?)

if (sqlca.sqlcode ltgt SUCCESS)
break
/ ???????????????
SQL????,????? /
/ ????????????? /
......
......

65
??(?)

EXEC SQL CLOSE SX / ???? /
......
......

66
??(?)

?2 ?????????????
......
......
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
......
/ ????? deptname,HSno,HSname,HSsex,HSag
e?/
......
......

67
??(?)

EXEC SQL END DECLARE SECTION
......
......
......
EXEC SQL DECLARE SX CURSOR FOR
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE SDeptdeptname / ????
/

68
??(?)

WHILE (gets(deptname)!NULL) /
?????deptname?? /
/ ??????deptname????????,
?????deptname??????? /
EXEC SQL OPEN SX / ????
/

69
??(?)

WHILE (1) / ???????????????? /
EXEC SQL FETCH SX
INTO HSno, HSname,
HSsex, HSage
/ ???????????,???????????,?????
?/

70
??(?)

if (sqlca.sqlcode ltgt SUCCESS)
break
/ ???????????????
SQL????,????? /
/ ????????????? /
......
......
/ ????? /

71
??(?)

EXEC SQL CLOSE SX / ???? /
/ ????? /
......
......

72
??CURRENT???UPDATE???DELETE??

CURRENT???UPDATE???DELETE?????
?CURRENT???UPDATE???DELETE??
???????
????????????????

73
??CURRENT???UPDATE???DELETE??

CURRENT???UPDATE???DELETE?????(?)
???????????????
?????SELECT?????????????
????????????????
?CURRENT???UPDATE???DELETE????????

74
CURRENT???UPDATE???DELETE??(?)

??
(1) DECLARE
????
(2) OPEN
????,?????????????????????
(3) FETCH
??????,???????????????????

75
CURRENT???UPDATE???DELETE??(?)

(4) ?????????????????,?????
(5) ???(3)?(4)?,??????????????????
(6) CLOSE
????,????????????????

76
CURRENT???UPDATE???DELETE??(?)

????
?UPDATE??????
???FOR UPDATE OF lt??gt???DECLARE??
????
EXEC SQL DECLARE lt???gt CURSOR
FOR ltSELECT??gt FOR UPDATE
OF lt??gt
FOR UPDATE OF lt??gt??????????????????????,??DBMS???
???

77
CURRENT???UPDATE???DELETE??(?)

?DELETE??????
???FOR UPDATE???DECLARE??
????
EXEC SQL DECLARE lt???gt CURSOR
FOR ltSELECT??gt FOR UPDATE
FOR UPDATE????DBMS??????

78
CURRENT???UPDATE???DELETE??(?)

?????????
???????????????????,??UPDATE???DELETE??????????
????
ltUPDATE??gt WHERE CURRENT OF lt???gt
ltDELETE??gt WHERE CURRENT OF lt???gt
WHERE CURRENT OF lt???gt????????????????????????

79
CURRENT???UPDATE???DELETE??(?)

???????SELECT????UNION?ORDER BY???,???SELECT??????
????????????,????CURRENT???UPDATE???DELETE??

80
??

?3 ?????????,????????????????????
??
????????????(??????????deptname??)
??????????????????????

81
??(?)

......
......
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
......
/ ????? deptname,HSno,HSname,HSsex,HSage,NE
WAge?/
......

82
??(?)

......
EXEC SQL END DECLARE SECTION
......
......
gets(deptname) / ????deptname??
/
......

83
??(?)

EXEC SQL DECLARE SX CURSOR FOR
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE SDeptdeptname
FOR UPDATE OF Sage / ???? /
EXEC SQL OPEN SX / ???? /

84
??(?)

WHILE(1) / ???????????????? /
EXEC SQL FETCH SX INTO HSno, HSname,
HSsex, HSage
/ ???????????,??????
?????,??????/

85
??(?)

if (sqlca.sqlcode ltgt SUCCESS)
break
/ ?????????????
??SQL????,????? /
printf("s, s, s, d", Sno, Sname,
Ssex, Sage)
/ ????? /
printf("UPDATE AGE ? ") / ???????? /
scanf("c",yn)

86
??(?)

if (yn'y' or yn'Y') / ???? /
printf("INPUT NEW AGE ")
scanf("d",NEWAge) / ???????
/
EXEC SQL UPDATE Student
SET SageNEWAge
WHERE CURRENT OF SX
/ ??????????? /

87
??(?)

......
......
EXEC SQL CLOSE SX / ???? /
......
......

88
??(?)

?4 ?????????,??????????????????
......
......
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
......
/ ????? deptname,HSno,HSname,HSsex,HSag
e?/

89
??(?)

......
......
EXEC SQL END DECLARE SECTION
......
......
gets(deptname) / ????deptname??
/
......

90
??(?)

EXEC SQL DECLARE SX CURSOR FOR
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE SDeptdeptname
FOR UPDATE / ???? /
EXEC SQL OPEN SX / ???? /

91
??(?)

WHILE(1) / ???????????????? /
EXEC SQL FETCH SX INTO HSno, HSname,
HSsex, HSage
/ ???????????,????
???????,??????/

92
??(?)

if (sqlca.sqlcode ltgt SUCCESS)
break
/ ?????????????
??SQL????,????? /
printf("s, s, s, d", Sno, Sname,
Ssex, Sage)
/ ????? /

93
??(?)

printf("DELETE ? ") / ???????? /
scanf("c",yn)
if (yn'y' or yn'Y') / ???? /
EXEC SQL DELETE
FROM Student
WHERE CURRENT OF SX / ?????? /

94
??(?)

......
......
EXEC SQL CLOSE SX / ???? /
......
......

95
7.7.5 ??SQL??

?????SQL
?????SQL

96
????SQL???

????????????????????WHERE???HAVING??????????
????????????????????????,??????????????????????

97
??SQL???

??SQL????????????????
?????,????????????

98
??

?,?SC
????????????????????????
?????????????????????????
?????????????????
??????????,?????????????

99
????SQL

1. ????????SQL
??SQL????????????????SQL???
2. ????
??????????????
SQL????
?????
????????
SQL???????????(????????????)

100
??SQL(?)

3. ??SQL???
????
???????SQL??
????
WHERE??????
HAVING??????
?????????????
SELECT??????
FROM??????????
WHERE??????
HAVING??????

101
??SQL(?)

4. ????SQL??
EXECUTE IMMEDIATE
PREPARE
EXECUTE
DESCRIBE
????SQL?????????????????,???SQL????

102
??(?)

???SQL
?????????
1. SQL???
??????SQL?????????
2. ???
1)????SQL??????
2)?SQL???????????????????
3. ??
?????????????????????

103
??(?)

???SQL(?)
??SQL
????
??????????SQL??
????????SQL??
????
????
????
??????????
????
??SQL

104
??? ????????

7.1 ???????
7.2 ????
7.3 ??????
7.4 ??????
7.5 ????????
7.6 ????????
7.7 ???SQL
7.8 ????
7.9 ODBC??

105
7.8 ????

?????????????????????
?????????????????????,???????,???????
?????????????????????
?????????

106
????(?)

????RDBMS?????PL/SQL????????????,????????????????

107
??? ????????

7.1 ???????
7.2 ????
7.3 ??????
7.4 ??????
7.5 ????????
7.6 ????????
7.7 ???SQL
7.8 ????
7.9 ODBC??

108
7.9 ODBC??

???????????????????,?????RDBMS???,??????
???????RDBMS?????????,????????,????????
ODBC????????????????????????,????????,?
????????????API?
????????????????????????,??????RDBMS?
????

109
ODBC????