|
FUNCTIONS
Minus
mysql> create function minus(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> delimiter ;
mysql> select minus(100, 25);
+----------------+
| minus(100, 25) |
+----------------+
| 75 |
+----------------+
1 row in set (0.00 sec)
mysql>
Bo`lish
mysql> create function bolish(a float, b float)
-> returns float
-> deterministic
-> begin
-> declare result float;
-> set result = a / b;
-> return (result);
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> select bolish(10, 3)//
+------------------+
| bolish(10, 3) |
+------------------+
| 3.33333325386047 |
+------------------+
1 row in set (0.06 sec)
mysql>
Ildiz
mysql> create function ildiz(a float)
-> returns float
-> deterministic
-> begin
-> declare result float;
-> set result = sqrt(a);
-> return (result);
-> end//
Query OK, 0 rows affected (0.07 sec)
mysql> select ildiz(16)//
+-----------+
| ildiz(16) |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)
mysql>
Modul
mysql> create function modul(a int)
-> returns int
-> deterministic
-> begin
-> declare result int;
-> set result = abs(a);
-> return (result);
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> select modul(-1)//
+-----------+
| modul(-1) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql>
Ustunlarni birlashtirish
mysql> create function birlashtirish(
-> ism text charset utf8,
-> fam text charset utf8,
-> ota text charset utf8)
-> returns text charset utf8
-> reads sql data
-> begin
-> return concat(
-> ism, ' ', fam, ' ', ota);
-> end//
Query OK, 0 rows affected (0.09 sec)
mysql> select birlashtirish(ismi, familyasi, otasining_ismi) from bemorlar_2;
-> //
+------------------------------------------------+
| birlashtirish(ismi, familyasi, otasining_ismi) |
+------------------------------------------------+
| Botir Ahrorov Ziyatovich |
| Dilobar Saidova Davron qizi |
| Qayyum Ahad Jobirovich |
| Davron Qodirov Otaxonovich |
| Farzona Mo`minova Ahmadjon qizi |
| Vladimir Belyakov Gennadevich |
| Andrey ╨Рndreev Ivanovich |
| Liliya Muxamadullina Nailevna |
| Elvira ╨Рbdullina Rashidovna |
| Viktoriya Gayfullina ╨Рleksandrovna |
| Bekzod Asadov Valiyevich |
| Temur Ahrorov Aliyevich |
+------------------------------------------------+
12 rows in set (0.00 sec)
Mavjud standart funksiyalar(10-lab)dan foydalanib yangi funksiya yaratish.
mysql> create function uzunligi(s varchar(30))
-> returns int
-> deterministic
-> begin
-> declare result int;
-> set result = length(s);
-> return (result);
-> end//
Query OK, 0 rows affected (0.11 sec)
mysql> select uzunligi('Abdurauf')//
+----------------------+
| uzunligi('Abdurauf') |
+----------------------+
| 8 |
+----------------------+
1 row in set (0.00 sec)
mysql>
TRIGGERS
After delete
mysql> create trigger tuzalgan_bemorlar
-> after delete
-> on bemorlar_2
-> for each row
-> set @del = @del + 1;
-> //
Query OK, 0 rows affected (0.25 sec)
Before delete
mysql> create trigger tuzalganlar
-> before delete
-> on bemorlar_2 for each row
-> begin
-> insert into tuzalganlar
-> values(old.familyasi, old.ismi, old.manzili);
-> end//
Query OK, 0 rows affected (0.13 sec)
Before update
mysql> create trigger check_yoshi before update on bemorlar_2
-> for each row
-> begin
-> if new.yoshi < 0 then
-> set new.yoshi = 0;
-> elseif new.yoshi > 100 then
-> set new.yoshi = 100;
-> end if;
-> end //
Query OK, 0 rows affected (0.16 sec)
After update
mysql> create table update_bemorlar like bemorlar_2;
Query OK, 0 rows affected (0.13 sec)
mysql> select * from update_bemorlar;
Empty set (0.00 sec)
mysql> create trigger after_update
-> after update
-> on bemorlar_2 for each row
-> begin
-> insert into update_bemorlar values
-> (old.bemor_id, old.familyasi, old.ismi, old.otasining_ismi, old.manzili, old.yoshi, old.jinsi);
-> end//
Query OK, 0 rows affected (0.10 sec)
Before Insert
mysql> create trigger before_insert before insert
-> on shifokorlar_2 for each row
-> begin
-> if new.lavozimi = 'Shifokor' then
-> set new.lavozimi = 'Yordamchi';
-> end if;
-> end//
Query OK, 0 rows affected (0.09 sec)
mysql> insert into shifokorlar_2 values
-> (13, 'Abrorov', 'Ali', 'Asadovich', 'Shifokor', 9, 'Magistr', 'Namangan'),
-> (14, 'Asrorov', 'Vali', 'Ahadovich', 'Shifokor', 6, 'Bakalavr', 'Andijon')//
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from shifokorlar_2//
After Insert
mysql> create table ogohlantirish
-> (id int auto_increment,
-> bemor_id int(4),
-> message varchar(255) not null,
-> primary key(id, bemor_id))//
Query OK, 0 rows affected (0.11 sec)
mysql> create trigger after_insert after insert
-> on bemorlar_2 for each row
-> begin
-> if new.ismi is null then
-> insert into ogohlantirish (bemor_id, message)
-> values(new.bemor_id, concat(new.familyasi, ' ismingizni yangilang.'));
-> end if;
-> end//
Query OK, 0 rows affected (0.13 sec)
mysql> insert into bemorlar_2(bemor_id, familyasi) values
-> (9, 'Ahmadjonov')//
Query OK, 1 row affected (0.11 sec)
mysql> select * from ogohlantirish//
Xulosa:
Men ushbu laboratoriya ishida MySQL da triggerlarr va funksiyalar yaratish bo`yicha ko`nikmaga ega bo`ldim.
Do'stlaringiz bilan baham: | 
