MySQL stored procedures and function stored proceduresDownloadDemo
Mysql> delimiter//--here//for modifying the default delimiter;mysql> CREATE PROCEDURE simpleproc (out param1 INT), BEGIN SELECT COUNT (*) into param1 from T; End//query OK, 0 rows Affected (0.00 sec) mysql> delimiter; --Change back here the default delimiter is;
This calls the trial call
Mysql> call Simpleproc (@a); Mysql> Select @a;
Function Demo
Parameter does not have input/output parameters
mysql> CREATE FUNCTION Hello (S CHAR ()) mysql> RETURNS CHAR (deterministic), RETURN CONCAT (' Hello, ', s ,‘!‘);
The trial select is called here, just like the normal function.
mysql> SELECT Hello (' world '); +----------------+| Hello (' world ') |+----------------+| Hello, world!. |+----------------+
Use of variables
DECLARE last_month_start date;declare my_sql INT DEFAULT ten; set var_name = 2;
Process Control
create definer= ' root ' @ ' localhost ' PROCEDURE ' student_insert ' () begin set @x=0; ins :loop set @x = @x +1; if @x=100 then leave ins; end if; insert into student (Stuname) values (CONCAT ("name", @x); end loop ins; endcreate definer= ' root ' @ ' localhost ' PROCEDURE ' student_insert2 ' () begin Set @x=100; ins :loop set @x = @x +1; if @x=120 then &nBsp; leave ins; elseif mod (@x, 2) = 0 then iterate ins; end if; insert into student (Stuname) values (CONCAT ("name", @x); END LOOP ins; endcreate definer= ' root ' @ ' localhost ' PROCEDURE ' Loop_demo ' () begin set @x=1 , @x1 =1; repeat set @x = @x +1; until @x > 0 end repeat; while @x1 < 2 do set @[email protected]+1; end while; END
Use of Cursors
create definer= ' root ' @ ' localhost ' PROCEDURE ' Cursor_demo ' () begin declare i_stuid int; declare i_stuname varchar (; ) declare cur_stu cursor for select stuid,stuname from student; declare exit handler for not found close cur_stu; set @x1 = 0; set @x2 = 0; open cur_stu; repeat fetch cur_stu into i_stuid,i_stuname; select i_stuid,i_stuname; until 0 end repeat; close cur_stu; Endcreate definer= ' root ' @ ' localhost ' PROCEDURE ' Cursor_demo3 ' () begin declare done int default 0; declare a char ( declare b,c int; declare); cur1 CURSOR FOR SELECT stuname,stuid FROM ' student '; declare cur2 CURSOR FOR SELECT subid FROM ' Subject '; declare continue handler for sqlstate ' 02000 ' SET done = 1; Open cur1; open cur2; repeat fetch cur1 into a, b; fetch cur2 into c; if NOT done THEN SELECT a,b,c; END IF; UNTIL done END REPEAT; CLOSE cur1; CLOSE cur2; END
Trigger
Must be built on top of the real table, suitable for some initialization data
Create table test1 (A1 int); Create table test2 (A2 int); Create table test3 (A3 int not null auto_increment primary key); Create table test4 ( a4 INT NOT NULL AUTO_INCREMENT PRIMARY key, b4 int default 0); delimiter | create trigger testref before insert on test1 for each row begin insert into test2 set a2 = new.a1; delete FROM test3 WHERE a3 = NEW.a1; UPDATE test4 set b4 = b4 + 1 where a4 = new.a1; end| DELIMITER ; INSERT INTO test3 (A3) VALUES (NULL), ( NULL), (NULL), (NULL), (NULL), &NBSP;&NBSP;&NBSP, (NULL), (NULL), (NULL), (NULL), (NULL); insert into test4 (A4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
View Index
Show index from table
MySQL stored procedure function trigger