MySQL stored procedures and function stored proceduresDownloadDemo
Mysql> delimiter// --here//for modifying the default delimiter;mysql> CREATE PROCEDURE simpleproc (out param1 INT) begin< c5/>-> 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 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 (a); 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 do 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), (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