Mavzu: sql da funksiyalar yaratish. Ishdan maqsad

21-22-23.Laboratoriya ishi

Mavzu: SQL da funksiyalar yaratish.

Ishdan maqsad: SQL da funksiyalar yaratish bo`yicha ko`nikmaga ega bo`lish. Masalani qo`yilishi: Berilgan predmet soha ma`lumotlar bazasidagi barcha ob`yektlarni ustida VIEWlar yaratish asosida amallarini bajarish.

Uslubiy ko`rsatmalar: Funksiyalar (FUNCTION) - protseduraga o'xshash, bu biz qayta-qayta ishlatmoqchi bo'lgan ba'zi SQL kod. Funktsiya va protsedura o'rtasidagi farq shundan iboratki, funktsiya faqat bitta parametrni qaytaradi va funktsiya "Call" qilmasdan nomi bilan chaqiriladi. Shuning uchun funktsiya SQL so'rovlarida ishlatilishi mumkin. Masalan, test () funktsiyasini yarating;

mysql> delimiter //

mysql> create function plus (a int, b int) returns int

-> deterministic

-> begin

-> declare result int;

-> set result = a+b;

-> return(result);

-> end//

Query OK, 0 rows affected (0.40 sec)

mysql> SELECT PLUS(12,25);

-> END//

+-------------+

| PLUS(12,25) |

+-------------+

| 37 |

+-------------+

1 row in set (0.13 sec)

mysql> delimiter //

mysql> create function AYRISH (a int, b int) returns int

-> deterministic

-> begin

-> declare result int;

-> set result = a-b;

-> return(result);

-> end//

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT AYRISH(18,9);

-> end//

+--------------+

| AYRISH(18,9) |

+--------------+

| 9 |

+--------------+

1 row in set (0.10 sec)

mysql> create function KOPAYTIRISH (a int, b int) returns int

-> deterministic

-> begin

-> declare result int;

-> set result = a*b;

-> return(result);

-> END//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT KOPAYTIRISH(5,3);

-> END//

+------------------+

| KOPAYTIRISH(5,3) |

+------------------+

| 15 |

+------------------+

1 row in set (0.01 sec)

mysql> create function BOLISH (a int, b int) returns int

-> deterministic

-> begin

-> declare result int;

-> set result = a/b;

-> return(result);

-> end//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT BOLISH(4,2);

-> end//

+-------------+

| BOLISH(4,2) |

+-------------+

| 2 |

+-------------+

1 row in set (0.00 sec)

mysql> DELIMITER $$

mysql> CREATE FUNCTION AYIRMADAN_ILDIZ_CH (x1 decimal, y1 decimal)

-> RETURNS decimal

-> DETERMINISTIC

-> BEGIN

-> DECLARE dist decimal;

-> SET dist = SQRT(x1 - y1);

-> RETURN dist;

-> END$$

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT AYIRMADAN_ILDIZ_CH(64,48)

-> $$