Dear Friend, Please Copy Link Below, Add Me and Then Buzz on this post Comment to do Link Exchange.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
A Cursor can be imagine as A Table in MySQL PLSQL. We can use a cursor as temp table that we process in the PLSQL.
Below i have a simple MySQLTrigger to show how cursor can be use. I use MySQL version 5.1.33.
Please Just See A Red Row of the Program, That i will Explain.
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `db_pengabdian`.`update_jum_pengabdi`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_jum_pengabdi` AFTER UPDATE ON `tb_mketua`
FOR EACH ROW BEGIN
DECLARE done INT DEFAULT 0;
DECLARE nip CHAR(30);
DECLARE yr YEAR;
DECLARE jum INT;
DECLARE cur1 CURSOR FOR select distinct(nip_anggota), tahun from tb_manggota union all select distinct(nip_ketua), tahun from tb_mketua;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
set jum = 0;
REPEAT
FETCH cur1 INTO nip, yr;
IF yr = old.tahun THEN
set jum=jum+1;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
set jum=jum-1;
update tb_dwh_pengabdian set pengabdian_jum_dosen_mengabdi = jum where pengabdian_tahun=old.tahun;
END;
$$
DELIMITER ;
The Red Row of the syntax is a row definiion, then a fetch (something we said to cut per row piece). And at the Green Line, you can find that if the table change on field year has a similar value with a cursor fetch, do something.
That is a simple example of using cursor in In MySQL PLSQL, Using Cursor in a Trigger.
Below i have a simple MySQLTrigger to show how cursor can be use. I use MySQL version 5.1.33.
Please Just See A Red Row of the Program, That i will Explain.
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `db_pengabdian`.`update_jum_pengabdi`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_jum_pengabdi` AFTER UPDATE ON `tb_mketua`
FOR EACH ROW BEGIN
DECLARE done INT DEFAULT 0;
DECLARE nip CHAR(30);
DECLARE yr YEAR;
DECLARE jum INT;
DECLARE cur1 CURSOR FOR select distinct(nip_anggota), tahun from tb_manggota union all select distinct(nip_ketua), tahun from tb_mketua;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
set jum = 0;
REPEAT
FETCH cur1 INTO nip, yr;
IF yr = old.tahun THEN
set jum=jum+1;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
set jum=jum-1;
update tb_dwh_pengabdian set pengabdian_jum_dosen_mengabdi = jum where pengabdian_tahun=old.tahun;
END;
$$
DELIMITER ;
The Red Row of the syntax is a row definiion, then a fetch (something we said to cut per row piece). And at the Green Line, you can find that if the table change on field year has a similar value with a cursor fetch, do something.
That is a simple example of using cursor in In MySQL PLSQL, Using Cursor in a Trigger.
After Read a Post about Safely Backup MySQL Database With SQL Yog,
Below is the step by step we doing a MySQL Database backup with SQL Yog :
1. Right Click A Database
3. Choose Backup Options
Figure 2.
4. Export and done.
The Explanation of Figure 2
Bulk SQL Statement and Non Bulk Insert Statement
andBackup MySQL Database Safely as SQL File with SQL Yog
Below is the step by step we doing a MySQL Database backup with SQL Yog :
1. Right Click A Database
Figure 1.
2. Choose Back Up Database3. Choose Backup Options
Figure 2.
The Explanation of Figure 2
- All Check on table, stored procedure and trigger mean The backup doing all backup include table, views, stored procedure, trigger, event and function
- Structure and Data Check mean The Backup doing a Data and Structure Backup.
- File Name is a Must.
- Do not Check Use Bulk Insert Statement if you have a database that has great size.
Subscribe to:
Posts (Atom)
