11- Маъруза. SQL да жараёнлар ва стандарт функциялар

1.

11-МАЪРУЗА. SQL да жараёнлар ва
стандарт функциялар
(MYSQL мисолида).
Рахимбоев Х.Ж.
Курс-2

2.

5. MySQL: мураккаб
(таркибли)
операторлар
4. Процедура
параметрлари
3. CREATE PROCEDURE
процедура яратиш
синтаксиси
2. Сақланадиган
процедураларнинг
афзаллиги
1. MySQL да
сақланувчи
процедуралар
РЕЖА:

3.

1. MySQL да сақланувчи процедуралар
Сақланадиган процедура - бу маълумотлар базасида
сақланадиган қисм дастурдир. У ўз номидан, параметрлар рўйхати
ва SQL ko'rsatmalarдан иборат бўлади. Маълумотлар базасини
бошқаришнинг
барча
машҳур
тизимлари
сақланган
процедураларни қўллаб-қувватлайди. Улар MySQL 5-da ҳам
мавжуд .
Қисм дастурларни 2 та тури мавжуд: сақланувчи процедуралар
ва
бошқа
SQL
кўрсатмаларида
ишлатиладиган
функциялар ( масалан, pi () ).
Уларни асосий фарқ шундаки, функциялар
SQL
кўрсатмаларидаги бошқа иборалар каби ишлатилиши мумкин ва
сақланувчи процедуралар CALL буйруғи ёрдамида чақирилиши
керак.

4.

2. Сақланадиган процедураларнинг афзаллиги
Сақланган процедуралар тезда ишлайди. MySQL серверининг
афзаллиги шундаки, у кешдан ва олдиндан аниқланган
операторлардан фойдаланади. Тезликни оширишни биринчи омили
бу тармоқ трафигини камайтиришдир. Текширув, бир нечта
операторлардан
иборат
циклларга
ишлов
бериш
каби
такрорланувчи масалалар мавжуд бўлса ва бунда оператор билан
алоқа ўрнатилиши шарт бўлмаса бу масалани битта процедура
чақируви билан амалга ошириш мумкин;
MySQL
da
saqlanadigan
protseduralar
universal
хисобланади. SQL da ёзилган процедуралар MySQL ўрнатилган
ихтиёрий платформада ишлайди. Bu SQL процедураларнинг
Java , C yoki PHP каби бошқа тилларга нисбатан устунлиги;
Сақланувчи процедураларнинг дастлабки коди ҳар доим
маълумотлар базасида мавжуд. Маълумотни уни қайта ишлайдиган
жараёнлар билан боғлаш амалиётда қўл келади.

5.

3. CREATE PROCEDURE процедура яратиш синтаксиси
• Одатга кўра, процедура жорий маълумотлар базаси билан боғлиқ
бўлади. Процедурани маълум бир маълумотлар базаси билан боғлаш учун,
база
номи
процедурани
яратишда
кўрсатилади:
database_name,
сақланадиган_процедура_номи.
Тўлиқ
синтаксис:
CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE процедура_номи ([процедура_параметрлари [,...]])
[characteristic ...] дастур_танаси
процедура_параметрлари: [ IN | OUT | INOUT ] процедура_параметрлари type
Бу ерда type MySQL даги ихтиёрий валид тип
characteristic: COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA
| MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
Қисм_дастур_танаси: SQL тилининиг валид операторлари

6.

3. CREATE PROCEDURE процедура яратиш синтаксиси
• CREATE
PROCEDURE
ва
CREATE
FUNCTION операторларини бажариш учун
фойдаланувчида CREATE ROUTINE ваколати
бўлиши керак. Шунингдек DEFINER қийматига
боғлиқ равишда SUPER ваколати зарур бўлиши
мумкин. Фойдаланувчи ваколатлари
• mysql> SHOW PRIVILEGES; оператори ёрдамида
кўриш мумкин. (mysqld --verbose –help)

7.

4. Процедура параметрлари
Амалда
параметрлар
даструлаш
тилидаги
функцияларни
параметрларига
ўхшаш.
Параметрларни қуйидаги турлари мавжуд:
• – IN кирувчи параметр. SQL сўрови билан бирга
жўнатилади. Ушбу параметрлар процедурага
берилади, процедура ишлашида улардан
фойдаланади
• –OUT чиқувчи параметр. Натижани қайтаради.
Процедура ўз ишини тугатгандан кейин натижани
ушбу параметрларга беради.
• –INOUT параметрлар. Бир вапқтнинг ўзида ҳам
кирувчи ҳам чиқувчи параметр. Уларни. Масалан,
счетчик сифатида фойдаланиш мумкин.

8.

Процедурани ишга тушириш. Қайта яратиш.
CALL оператори МБ да сақланаётган процедурани ишга тушириш
учун ишлатилади. Синтаксиси:
• CALL процедура_номи ([параметр[,...]])
• CALL процедура_номи [()]
MySQL да аргумент қабул қилмайдиган сақланувчи процедуралар
қавсларсиз чақирилиши мумкин. Шунинг учун CALL job_data()
чақируви CALL job_data чақирувига тенг кучли.
• SHOW CREATE PROCEDURE
• Бу оператор MySQL ни кенгайтмаси бўлиб, кўрсатилган
процедурани яратувчи сатрларни қайтаради. Синтаксиси
қуйидагича:
• SHOW CREATE PROCEDURE процедура_номи
• Мисол: mysql> SHOW CREATE PROCEDURE job_data$$

9.

CHARACTERISTIC характеристкалар блоки
CREATE PROCEDURE оператори синтаксисида процедурани
характерловчи блоклардан фойдаланиш мумкин. Блоклар дастур
танасидан олдин қавсда кўрсатилади. Бу блоклар кўсатилиши шарт
эмас.
Масалан :
• CHARACTERISTIC:
• COMMENT 'string'
• | LANGUAGE SQL
• | [NOT] DETERMINISTIC
• | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
• | SQL SECURITY { DEFINER | INVOKER }

10.

CHARACTERISTIC характеристкалар блоки
• COMMENT – бу MySQL кенгайтмаси . Бу процедурани тавсифлаш учун
ишлатилади ва уни SHOW CREATE PROCEDURE оператори ёрдамида кўриш
мумкин.
• LANGUAGE - дастур танаси SQL ёрдамида ёзилганлигини билдиради.
• NOT DETERMINISTIC – ахборот берувчи характеристика бўлиб, агар бир хил
кирувчи параметрларда бир хил натижа берса DETERMINISTIC акс холда NOT
DETERMINISTIC хусусиятга эга бўлади.
• CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
• CONTAINS SQL процедурада маълумотларни ўқиш ва ёзиш амаллари
йўқлигини билдиради. масалан, SET @x = 1 ёки DO RELEASE_LOCK(‘abc’)
кўрсатмалари бажарилади ва бу маълумотларни ўқиш ва ёзиш билан боғлиқ
эмас.
• NO SQL – SQL операторлар йўқлигин билдиради.
• READS SQL DATA — маълумотларни ўқиш операторлари бор (масалан,
SELECT).
• MODIFIES SQL DATA – дастурда маълумотларни ёзиш операторлари
борлигини англатади (масалан, INSERT ёки DELETE).

11.

CHARACTERISTIC характеристкалар блоки:
• SQL SECURITY {DEFINER | INVOKER}
• SQL SECURITY қиймати ёки SQL SECURITY DEFINER ёки SQL
SECURITY INVOKER каби кўрсатилиши мумкин. Бу қиймат қисм
дастур DEFINER шартида кўрсатилган аккаунт ёки сақланаётган
процедурани чақираётган жорий MySQL аккаунт ваколатидан
фойдаланиб бажарилишлигини кўрсатади. Бу аккаунт қисм
дастур боғланган маълумотлар базасига мурожаат ваколатига
эга бўлиши керак. Агар процедура DEFINER хавфсизлик
контекстида бажарилаётган бўлса, чақирувчи аккаунт EXECUTE
ваколатига эга бўлиши керак.

12.

CHARACTERISTIC характеристкалар блоки :
Барча блоклар жимликка кўра қийматларга эга. Қуйидаги 2 та процедура бир хил
натижага эга:
1)
• mysql> CREATE PROCEDURE job_data()
• -> SELECT * FROM JOBS; $$
Query OK, 0 rows affected (0.00 sec)
• бир хил натижага эга:
mysql> CREATE PROCEDURE new_job_data()
-> COMMENT ''
-> LANGUAGE SQL
-> NOT DETERMINISTIC
-> CONTAINS SQL
-> SQL SECURITY DEFINER
-> SELECT * FROM JOBS;
-> $$
Query OK, 0 rows affected (0.26 sec)

13.

5. MySQL: мураккаб (таркибли) операторлар
Мураккаб операторлар - бу бошқа блокларни ўз ичига олган блок:
ўзгарувчиларни ан декларациялари, ҳолат ва курсорни қайта
ишлаш воситалари ҳамда маълумотлар оқимини, циклларни ва
шартларни бошқариш конструкциялари. Навбатдаги таркибий
блоклар мавжуд:
• BEGIN … END;
• Операторлар белгилари;
• DECLARE;
• Сақланувчи дастурдаги ўзгарувчилар;
• Маълумотлар оқимини бошқариш операторлари;
• Курсорлар;
• Шартларни қайта ишловчилар.
CREATE PROCEDURE оператори параметрлари билан боғлиқ 4 та
мураккаб операторни кўриб чиқамиз

14.

5.1. BEGIN … END оператори синтаксиси
Қисм дастурда бирдан кўп операторни жойлаштириш учун фойдаланилади.
Синтаксиси:
• [бошланиш_белгиси:]
• BEGIN [операторлар_рўйхати]
• END [тугаш_белгиси])
операторлар_рўйхати: - нуқта вергул (;) билан тугаган бир ёки бир нечта
операторлар. Опретаорла рўйхати бўлиши шарт эмас, шунинг учун бўш BEGIN
END оператори хатолик эмас.

15.

5.2. Оператор белгилари
Операторлар белгиси – бу BEGIN … END ҳамда REPEAT ва WHILE такрорлаш
операторлари бажариш кўрсатмасини билдиради. Синтаксиси:
• [бошланиш_белгиси:]
• BEGIN [операторлар_рўйхати]
• END [тугаш_белгиси]
• [бошланиш_белгиси:]
• LOOP операторлар_рўйхати
• END LOOP [тугаш_белгиси]
• [бошланиш_белгиси:]
• REPEAT
операторлар_рўйхати
• UNTIL search_condition
• END REPEAT [тугаш_белгиси]
• [бошланиш_белгиси:]
• WHILE қидириш_шарти
• DO
операторлар_рўйхати
• END WHILE
• [тугаш_белгиси]

16.

5.3. DECLARE оператори. Процедуралар параметрлари
Процедурада локал элементларни аниқлаш учун
ишлатилади. масалан, локал ўзгарувчи, шарт, қайта
ишловчи, курсор. DECLARE фақат BEGIN … END блоки
ичида фойдаланилади ва барча операторлардан олдин
жойлашиши керак.
Ўзгарувчини эълон қилиш:
• DECLARE ўзгарувчи_номи [,ўзгарувчи_номи] ... type
[DEFAULT қиймат]
Ўзгарувчига жимликка кўра қиймат бериш учун DEFAULT
блоки ишлатилади. Қиймати ифода шаклида
кўрсатилиши мумкин, яъни константа бўлиши шарт эмас.
Агар DEFAULT блоки бўлмаса қиймати NULL га тенг.

17.

5.3. DECLARE оператори. Процедуралар параметрлари.
Мисол: Локал ўзгарувчилар
DELIMITER $$
CREATE PROCEDURE my_procedure_Local_Variables()
BEGIN /* локал ўзгарувчини эълон қилиш */
DECLARE a INT DEFAULT 10; DECLARE b, c INT; /* локал
ўзгарувчидан фойдаланиш */
SET a = a + 100; SET b = 2; SET c = a + b; BEGIN
/*ичма-ич блокдаги локал ўзгарувчи */
DECLARE c INT;
SET c = 5;
/* локал ўзгарувчи
бошқа блокдаги бир хил номли ўзгарувчига нисбатан
приоритетга эга бўлади. */
SELECT a, b, c; END; SELECT a, b, c;
END$$

18.

5.3. DECLARE оператори. Процедуралар параметрлари.
Мисол: Локал ўзгарувчилар
Процедурани бажариб кўрамиз:
mysql> CALL my_procedure_Local_Variables();
+------+------+------+
|a |b |c |
+------+------+------+
| 110 | 2 | 5 |
+------+------+------+
1 row in set (0.00 sec)
+------+------+------+
|a |b |c |
+------+------+------+
| 110 | 2 | 112 |
+------+------+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.03 sec)

19.

5.3. DECLARE оператори. Процедуралар параметрлари.
Мисол: фойдаланувчи ўзгарувчилари
Сақланувчи процедураларда фойдаланувчи ўзгарувчиларига мурожаат
ўзгарувчи олдида амперсанд ((@) белгисини кўрсатиш орқали амалга
ошади (масалан, @x ва @y). Навбатдаги мисолда фойдаланувчи
ўзгарувчиларига мурожаат этилган:
• DELIMITER $$
• CREATE PROCEDURE my_procedure_User_Variables()
• BEGIN SET @x = 15;
SET @y = 10;
• SELECT @x, @y, @x-@y;
• END$$
• Процедурани чақириш:
mysql> CALL my_procedure_User_Variables() ;
+------+------+-------+
| @x | @y | @x-@y |
+------+------+-------+
| 15 | 10 | 5 |
+------+------+-------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.05 sec)

20.

5.3.. Процедура параметрлари.
Навбатда параметрлар билан ишловчи CREATE PROCEDURE
процедура яратилган:
• CREATE
[DEFINER = { фойдаланувчи | CURRENT_USER }]
PROCEDURE процедура_номи ([процедура_параметри[,...]])
[характеристикалр ...] дастур_танаси процедура_параметри: [
IN | OUT | INOUT ] параметр_номи type
Синтаксис вариантлари:
• CREATE PROCEDURE процедура_номи () …
• CREATE PROCEDURE процедура_номи ([IN] параметр_номи
type)…
• CREATE PROCEDURE процедура_номи ([OUT] параметр_номи
type)…
• CREATE PROCEDURE процедура_номи ([INOUT] параметр_номи
type)…

21.

5.3. Процедуралар параметрлари. Мисол: процедура
параметрлари
• 1-мисолда параметр бўш.
• 2-мисолда IN процедурага қиймат беради. Процедура қийматни
ўзгартириши мумкин. Лекин, процедура қиймат қайтарганида
унинг қиймати чақирувчи агентга кўринмайди.
• 3- мисолда OUT процедурани чақирувчи агентга процедурадан
қиймат қайтади. Процедурада унинг бошланғич қиймати NULL га
тенг.
• 4-мисолда INOUT чақирувчи агент томонидан шакллантирилади
ва процедура томонидан ўзгартилиши мумкин. Процедура
қийматни қайтарганида ихтирёрий қиймат ўзгаришлари агентга
кўринади.
Процедурада ҳар бир параметр жимликка кўра IN параметр
хисобланади. Буни ўзгартириш учун OUT ёкм INOUT калит сўзи
ишлатилади.

22.

5.3. Процедуралар параметрлари. : Мисол: IN
параметри
Навбатдаги процедурада Var1 (бутун сонли типдаги) IN параметри
мавжуд бўлиб уни қийматини фойдаланувчи кўрсатади. Процедура
танасида SELECT оператори “Jobs” жадвалидан сатрларни ўқиб
олади. Сатрлар сони фойдаланувчи томонидан кўрсатилади:
• mysql> CREATE PROCEDURE my_proc_IN (IN var1 INT)
• -> BEGIN
• -> SELECT * FROM jobs LIMIT var1;
• -> END$$
• Query OK, 0 rows affected (0.00 sec)
«Persons» жадвалидаги дастлабки 100 та сатрни ўқиш учун қуйидаги буйруқ
берилади:
mysql> CALL my_proc_in(100)$$

23.

5.3. Процедуралар параметрлари. : Мисол: OUT
параметри
• Навбатдаги мисолда OUT параметри ишлатилади. Унда
MAX() оператори ёрдамида энг максимал иш ҳақи
олинади ва натижа сифатида қайтарилади:
• mysql> CREATE PROCEDURE my_proc_OUT (OUT
highest_salary INT)
• -> BEGIN
• -> SELECT MAX(MAX_SALARY) INTO highest_salary FROM
JOBS;
• -> END$$

24.

5.3. Процедуралар параметрлари. : Мисол: OUT
параметри
Дастур танасида параметри MAX_SALARY устунидаги юнг юқори
иш ҳақини олади. Процедура ишга тушганда OUT сўзи МББТга
қийматни маълум қилади. highest_salary — бу чиқувчи параметр
номи ва CALL операторида унинг қийматини @M сеанс
ўзгарувчисига берилди.:
• mysql> CALL my_proc_OUT(@M)$$
• Query OK, 1 row affected (0.03 sec)
mysql> SELECT @M$$
+-------+
| @M |
+-------+
| 40000 |
+-------+
1 row in set (0.00 sec)

25.

5.3. Процедуралар параметрлари. : INOUT параметрига
мисол
• Навбатдаги мисолда INOUT ва IN параметрлари қўлланилади. Фойдаланувчи
M‘ ёки ‘F‘ қийматни IN (emp_gender) параметри орқали user_details
жадвалидаги эркак ёки аёл жинсли ходимлар сонини аниқлаш учун беради.
INOUT (mfgender) параметри фойдаланувчигна натижани қайтаради:
• mysql> CREATE PROCEDURE my_proc_INOUT (INOUT mfgender INT, IN
emp_gender CHAR(1))
• -> BEGIN
• -> SELECT COUNT(gender) INTO mfgender FROM user_details WHERE gender =
emp_gender;
• -> END$$
• Query OK, 0 rows affected (0.00 sec)
• MariaDB [test]> CREATE PROCEDURE my_proc_INOUT (INOUT mfgender INT, IN
emp_gender varCHAR(255))
• -> begin
• -> SELECT COUNT(id) INTO mfgender FROM persons WHERE lastname =
emp_gender;
• -> end;$$
• Query OK, 0 rows affected (0.03 sec)

26.

5.3. Процедуралар параметрлари. : INOUT параметрига
мисол
Энди жадвалдаги аёл ва эркак ходимлар сонини аниқлаш мумкин:
• mysql> CALL my_proc_INOUT(@C,'M')$$
• Query OK, 1 row affected (0.02 sec)
• mysql> SELECT @C$$
• +------+
• | @C |
• +------+
• | 3|
• +------+
• 1 row in set (0.00 sec)
• mysql> CALL my_proc_INOUT(@C,'F')$$
• Query OK, 1 row affected (0.00 sec)
• mysql> SELECT @C$$
• +------+
• | @C |
• +------+
• | 1|
• +------+
• 1 row in set (0.00 sec)

27.

Назорат саволлари
1. SQL да сақланувчи процедуралар. SQL
процедураларнинг турлари.
2. SQL да сақланувчи процедураларнинг афзалликлари?
3. SQL процедура яратиш операториCREATE PROCEDURE.
Синтаксиси.
4. Параметры хранимых SQL процедуранинг параметрлари.
5. SQL процедуранинг характеристикалар блоки.
6. SQL процедуранинг таркибий (мураккаб) операторолари.
7. SQL процедуранинг ўзгарувчилари. Оператор DECLARE.
8. SQL процедуранинг ўзгарувчилари. IN, OUT, INOUT
параметрлари.