|
Zbek1ston respublikasi axborot
|
| bet | 55/71 | | Sana | 28.05.2024 | | Hajmi | 24,2 Mb. | | #255852 |
Bog'liq Ma\'lumotlar bazasi. ZaynidinovX.N.EXEC SQL UPDATE S SET TA’MINLOVCHI RAQAMI = SY WHERE TA’MINLOVCHI RAQAMI = SX;
EXEC SQL UPDATE SP SET TA’MINLOVCHI RAQAMI = SY WHERE TA’MINLOVCHI RAQAMI = SX;
EXEC SQL COMMIT; GO TO FINISH;
UNDO: EXEC SQL ROLLBACK; FINISH: RETURN; END TRANEX;
Misolimizdan ko‘rinib turibdiki, bu tranzaksiya jarayonida ikkita jadval ustunda o'zgarishlar amalga oshirilayapti. Demak, tranzaksiya deganimizda bitta amalni emas, balki amallar ketma-ketligini tushunish lozim.
SQL muhitida tranzaksiyalani boshqarish.
SQL tilida tranzaksivalami maxsus operatorlar yordamida boshqarish imkoniyati mavjud. Shulardan biri tranzaksiya
parametrlarini o'rnatish operatori bo'lib, uni yozilish format! quyidgicha:
::=
SET TRANSACTION transaction mode> [ { transaction mode> }... ]
transaction mode> :: -
' isolation level>
| transaction access mode> j «diagnostics size>
«isolation level> ::=
ISOLATION LEVEL «level of isolation>
::=
READ UNCOMMITTED | READ COMMITTED i REPEATABLE READ | SERIALIZAB1 1
transaction access mode> READ ONLY | READ WRITE
diagnostics size> ::=
DIAGNOSTICS SIZE
::= Bu yerda:
Agar himoya darajalari ko‘rsatilmasa, himoya darajasi SERIALIZABLE deb tushuniladi.
Agar ruxsat tartibi READWRITE kalit so‘zi bilan belgilansa, unda himoyalanish darajasi READUN COMMITTED boimasligi kerak.
Agar ruxsat tartibi va himoyalanish darajasi READUNCOMMITTED deb ko‘rsatilsa, unda beriladigan ruxsat tartibi READONLY deb tushuniladi, aks hollarda ruxsat tartibi READWRITE bo'ladi.
Ко" pchillк hollarda tranzaksiyalaming bajarilish jarayonida MB jadvallari strukturasi buzilishining oldini olish uchun tranzaksiyalarga faqat o‘qish tartibini o‘rnatish mumkin. Buning uchun quyidagi operator ishlatiladi:
SET TRANSACTION READ ONLY;
Bu operator tranzaksiya jarayoni boshlanishidan oldin koisatiladi. Masalan, EXEC SQL SET TRANSACTION READ ONLY:
93
Masalan: Buyurtmalami qabul qilgan sotuvchini nniqlarnoqchiniz. Bu ikki so'rovm bir-biridan farq qilishi uchun raatn kiritish yo‘li bilan tashkillashtirishimiz mumkin:
SELECT a.snum, sname, onum, ’Highest on’, odate FROM Salespeople a.
Orders b WHERE a.snum = b.snum A.ND b.amt ~ (SELECT MAX (amt) FROM Orders с WHERE c.odate = b,odate)
1/iNlON
SELECT a.snum, sname, onum, ’Lowest on’, odate FROM Salespeople a, Orders b WHERE a.snum b.snum AND b.amt = (SEi FCT MIN (amt)FROM Orders с WHERE c.odate ; b.odate);
10
|
Peel
|
30
|
High о
|
10/05/
|
SO
|
Peel
|
30
|
Low о
|
10/05/
|
10
|
Peel
|
30
|
Higho
|
10/06/
|
10
|
Serre
|
30
|
Higho
|
10/03/
|
10
|
Serre
|
30
|
Low о
|
10/04/
|
JO
|
Serre
|
30
|
Low о
|
10/06/
|
10
|
Axel
|
30
|
High о
|
10/04/
|
Birlashtirilgan natijalami ORDER BY kalit so‘zi yordamida iartiblashtirish mumkin. Yoqoridagi misolni tartib raqamlariga nisbatan tartiblashni ko‘rib o'tamiz.
SELECT a.snum, sname, onum, ’Highest on', odate FROM
Salespeople a. Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders с WHERE c.odate = b.odatej UNION
SELECT a.snum, sname, onum. ’Lowest on’, odate F ROM Salespeople a. Orders b WHERE a.snum = b.snu AND b.amt - (SELECT MIN (amt) FROM Orders с WHERE c.odate = b.odate)
1007 Rifkin
|
3001
|
Lowest on
|
10/03/199
|
1002 Serres
|
3005
|
Highest on
|
10/03/199
|
1002 Serres
|
3007
|
lowest on
|
10/04/199
|
1001 Peel
|
13008
|
Highest on
|
10/05/199
|
1001 Peel
|
3008
|
Lowest on
|
10/05/199
|
1003 Axelrod
|
3009
|
Highest on
|
10/04/199
|
94
1002 Series
|
3010
|
Lowest on
|
10/06/199
|
1001 Peel
|
3011
|
i Highest on
|
|
| |
