MySQL 存储过程中执行动态 SQL 语句

mysqlmysql delimiter $$mysqlmysql CREATE PROCEDURE set_col_value - (in_table VARCHAR(128), - in_column VARCHAR(128), - in_new_value VARCHAR(1000), - in_where VARCHAR(4000))- - BEGIN - DECLARE l_sql VARCHAR(4000); - SET l_sql=CONCAT_ws( , -...

利用 mysql.proc 表来获取基础数据信息

mysqlmysqlmysql DELIMITER //mysqlmysql CREATE FUNCTION myFunction (item_sum DECIMAL(10,2)) - RETURNS DECIMAL(10,1) - BEGIN-- - RETURN ROUND(item_sum,1);- - END - //Query OK, 0 rows affected (0.00 sec)mysql DELIMITER ;mysqlmysql select myFu...

MySQL 使用 LOOP 的 ITERATE 语句示例

介绍 MySQL 的 Iterate 迭代语句 mysqlmysqlmysql DELIMITER //mysql CREATE PROCEDURE myProc (IN in_count INT) - BEGIN - DECLARE count INT default 0;- - increment: LOOP - SET count = count + 1; - IF count 20 THEN ITERATE increment; END IF; - I...

MySQL 的 LEAVE 语句退出循环

mysqlmysql delimiter $$mysqlmysql CREATE PROCEDURE myProc() - BEGIN - DECLARE i,j INT DEFAULT 1; - outer_loop: LOOP - SET j=1; - inner_loop: LOOP - SELECT CONCAT(i,times, j,is,i*j); - SET j=j+1; - IF j12 THEN - LEAVE inner_loop; - END IF;...

MySQL 的存储过程中请求一个锁对象

mysqlmysql delimiter $$mysqlmysql CREATE PROCEDURE myProc() - BEGIN - DECLARE lock_result INT; - IF get_lock(sp_critical_section_lock,60) THEN - - SELECT got lock; - - SET lock_result=release_lock(sp_critical_section_lock); - ELSE - SELECT...

MySQL 存储过程的异常处理

mysqlmysql delimiter $$mysqlmysql CREATE PROCEDURE myProc - (p_first_name VARCHAR(30), - p_last_name VARCHAR(30), - p_city VARCHAR(30), - p_description VARCHAR(30), - OUT p_sqlcode INT, - OUT p_status_message VARCHAR(100)) - BEGIN- - - - D...

MySQL 存储过程使用 REPEAT 来打开游标读取数据

mysqlmysqlmysql delimiter $$mysql CREATE PROCEDURE myProc (in_customer_id INT) - BEGIN- - DECLARE l_first_name VARCHAR(30); - DECLARE l_id INT; - DECLARE l_city VARCHAR(30); - DECLARE l_department_count INT; - DECLARE no_more_departments I...

MySQL 事务 ROLLBACK 示例代码

mysqlmysql delimiter $$mysqlmysql CREATE PROCEDURE myProc( - in_from INTEGER, - in_to INTEGER, - in_amount DECIMAL(8,2)) - BEGIN- - DECLARE txn_error INTEGER DEFAULT 0 ;- - DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN - SET txn_error=1;...

MySQL 存储过程中使用 WHILE 循环语句

mysql mysql delimiter $$ mysql mysql CREATE PROCEDURE myProc() - BEGIN - - DECLARE i int; - SET i=1; - loop1: WHILE i=10 DO - IF MOD(i,2)0 THEN - SELECT CONCAT(i,is an odd number); - END IF; - SET i=i+1; - END WHILE loop1; - END$$ Query OK,...

使用 LOOP 和 FETCH 来读取 MySQL 游标

mysqlmysql delimiter $$mysql CREATE PROCEDURE myProc() - BEGIN - DECLARE l_sale_id INT; - DECLARE l_last_sale INT DEFAULT 0;- - DECLARE sale_csr CURSOR FOR - SELECT id - FROM employee;- - DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_s...