#存储过程包含过程及函数
##MYSQL 函数定义:
DROP FUNCTION IF EXISTS SHOWRTEN;
DELIMITER $$
CREATE FUNCTION SHORTEN(S VARCHAR(255) , N INT)
RETURNS VARCHAR(255)
BEGIN
IF ISNULL(S) THEN
RETURN '';
ELSEIF N < 15 THEN
RETURN LEFT(S ,N);
ELSE
IF CHAR_LENGTH(S) <= N THEN
RETURN S;
ELSE
RETURN CONCAT(LEFT(S ,N-10),'...',RIGHT(S ,5));
END IF;
END IF;
END$$
#比较一下Oracle函数定义
CREATE OR REPLACE FUNCTION SHORTEN (S IN VARCHAR2 , N IN INTEGER)
RETURN VARCHAR2
AS
RETURN_DATA VARCHAR2(255) :='';
BEGIN
IF S IS NULL THEN
RETURN_DATA :='';
ELSIF N<15 THEN
RETURN_DATA := SUBSTR(S,0,N);
ELSE
IF LENGTH(S) <= N THEN
RETURN_DATA :=S;
ELSE
RETURN_DATA := SUBSTR(S,0,N-10) || '...' || SUBSTR(S,-5,5);
END IF;
END IF;
RETURN RETURN_DATA;
END;
#新建函数出错
错误代码: 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
#解决方法:
#1、cmd 登陆 mysql
mysql -h localhost -u root -p
2、设置数据库函数
set global log_bin_trust_function_creators=1;
set global log_bin_trust_function_creators=TRUE;
#3、再新建就没问题了。
#4、测试
SELECT SHORTEN('This function has none of DETERMINISTIC, NO SQL',15) FROM DUAL;
#删除函数
DROP FUNCTION IF EXISTS SHORTEN;
#删除存储
DROP PROCEDURE IF EXISTS ST;
#查询存储及函数
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE dm.ST ;
SHOW FUNCTION STATUS;
SHOW CREATE FUNCTION dm.SHORTEN;
#创建MYSQL存储过程
DROP PROCEDURE IF EXISTS TS;
DELIMITER $$
CREATE PROCEDURE TS (IN ID INT,OUT TEST VARCHAR2(255))
BEGIN
SELECT TITLE INTO TEST FROM TITLES WHERE TITLEID = 110;
select TEST;
END$$
#执行MYQL存储过程 Mysql不需要匿名块就可以执行存储,但需要先定义变量
SET @TEST='';
CALL TS(101,@TEST);
SELECT @TEST;
#与Oracle存储过程相比较
CREATE OR REPLACE PROCEDURE TS (ID IN INTEGER , TEST OUT VARCHAR2)
IS
BEGIN
SELECT TITLE INTO TEST FROM TITLES WHERE TITLEID = ID;
--DBMS_OUTPUT.PUT_LINE(TEST);
END
#执行oracle存储过程 要在存储及匿名块中执行
DECLARE
TEST VARCHAR2(255) :='';
BEGIN
TS(101,TEST);
DBMS_OUTPUT.PUT_LINE(TEST);
END
#定义变量与赋值
DROP PROCEDURE IF EXISTS TEST;
DELIMITER $$
CREATE PROCEDURE TEST (IN ID INT,OUT TIT VARCHAR(255))
BEGIN
DECLARE SUB_TITLE VARCHAR(255) ; #若想声明变量并赋值 DECLARE SUB_TITLE VARCHAR(255) DEFAULT '';
SELECT TITLE INTO TIT FROM TITLES WHERE TITLEID = ID;
SET SUB_TITLE = LEFT(TIT,3); #赋值只能用set 和select XX into 变量
SELECT SUB_TITLE FROM DUAL;
END$$
SET @TITLE ='';
CALL TEST(101,@TITLE);
SELECT @TITLE FROM DUAL;
#声明的临时变量的作用域
DROP PROCEDURE IF EXISTS VER;
DELIMITER $$
CREATE PROCEDURE VER()
BEGIN
DECLARE X INT DEFAULT 1;
BEGIN
DECLARE X INT DEFAULT 2;
IF TRUE THEN
BEGIN
DECLARE X INT DEFAULT 3;
SELECT X; #作用域为 本层begin end
END;
END IF;
SELECT X;#作用域为 本层begin end
END;
SELECT X;#作用域为 本层begin end
END$$
CALL VER();
//---- IN & OUT 模式----
DROP PROCEDURE IF EXISTS SIMPLE_PROC;
DELIMITER $$
CREATE PROCEDURE simple_PROC(IN P INT,OUT X INT)
BEGIN
SELECT 1 INTO P FROM DUAL;
SET X=P+1;
SELECT X ;
END$$
CALL simple_PROC(@A,@B);
//------------- IN模式---------
DROP PROCEDURE IF EXISTS SIMPLE_PROC ;
DELIMITER $$
CREATE PROCEDURE SIMPLE_PROC (IN P_IN INT)
BEGIN
SELECT P_IN;
SET P_IN =1;
SELECT P_IN;
END$$
SET @P_IN =2;
CALL SIMPLE_PROC(@P_IN);
SELECT @P_IN;//并没有out 存储里的作用域仅在存储内 并没有改变数据库的P_IN变量
//-------------- OUT 模式------
DROP PROCEDURE IF EXISTS SIMPLE_PROC;
DELIMITER $$
CREATE PROCEDURE SIMPLE_PROC(OUT P_IN INT )
BEGIN
SELECT P_IN;
SET P_IN=1;
SELECT P_IN;
END$$
SET @P_IN =12;
CALL SIMPLE_PROC(@P_IN);
SELECT @P_IN;
#IF分支
DROP FUNCTION IF EXISTS TEST_IF;
DELIMITER $$
CREATE FUNCTION TEST_IF(II INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE I VARCHAR(255);
IF II >0 THEN
SET I= '大于0' ;
ELSEIF II = 0 THEN
SET I= '等于0' ;
ELSE
SET I = '小于0' ;
END IF;
RETURN I;
END$$
SELECT TEST_IF(2) FROM DUAL;
#错误 函数只能返回一个值 不能返回一个结果集
DELIMITER $$
CREATE FUNCTION TEST_IF01(I INT)
RETURNS VARCHAR(255)
BEGIN
IF I >0 THEN
SELECT '大于0' FROM DUAL;
ELSEIF I = 0 THEN
SELECT '等于0' FROM DUAL;
ELSE
SELECT '小于0' FROM DUAL;
END IF;
RETURN '';
END$$
#错误 函数只能返回一个值 不能返回一个结果集
#CASE语句 01
DROP FUNCTION IF EXISTS TEST_CASE;
DELIMITER $$
CREATE FUNCTION TEST_CASE(VARCHAR_IN VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE DATA VARCHAR(255) DEFAULT '';
CASE VARCHAR_IN
WHEN 'ME' THEN
SET DATA = 'ME';
WHEN 'HE' THEN
SET DATA = 'HE';
ELSE
SET DATA = 'OHTER';
END CASE;
RETURN DATA;
END$$
SELECT TEST_CASE('ME1') FROM DUAL;
#CASE语句 02
DROP FUNCTION IF EXISTS TEST_CASE01;
DELIMITER $$
CREATE FUNCTION TEST_CASE01(VARCHAR_IN VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE DATA VARCHAR(255) DEFAULT '';
CASE
WHEN VARCHAR_IN = 'ME' THEN
SET DATA = 'ME';
WHEN VARCHAR_IN = 'HE' THEN
SET DATA = 'HE';
ELSE
SET DATA = 'OHTER';
END CASE;
RETURN DATA;
END$$
SELECT TEST_CASE01('ME1') FROM DUAL;
#CASE语句 03
DROP FUNCTION IF EXISTS TEST_CASE03;
DELIMITER $$
CREATE FUNCTION TEST_CASE03(VARCHAR_IN VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
RETURN
CASE
WHEN VARCHAR_IN = 'ME' THEN 'ME'
WHEN VARCHAR_IN = 'HE' THEN 'HE'
ELSE 'OHTER'
END ;
END$$
SELECT TEST_CASE03('ME1') FROM DUAL;
#循环
#1、REPEAT-UTIL循环
DROP FUNCTION IF EXISTS RULOOP;
DELIMITER $$
CREATE FUNCTION RULOOP (N INT)
RETURNS TEXT
BEGIN
DECLARE I INT DEFAULT 0;
DECLARE S TEXT DEFAULT '';
REPEAT #也可以为这个循环命名 MY_REPEAT : REPEAT
SET I = I+1;
SET S = CONCAT(S,'*');
UNTIL I >= N
END REPEAT;
RETURN S;
END$$
SELECT RULOOP(10) FROM DUAL;
#2、WHILE循环
DROP FUNCTION IF EXISTS WHILE_LOOP;
DELIMITER $$
CREATE FUNCTION WHILE_LOOP(N INT)
RETURNS TEXT
BEGIN
DECLARE I INT DEFAULT 0;
DECLARE S TEXT DEFAULT '';
WHILE I <= N DO #也可以为这个循环命名 MY_WHILE : WHILE I <= N DO
SET I = I+1;
SET S = CONCAT(S,'*');
END WHILE;
RETURN S;
END$$
SELECT WHILE_LOOP(5) FROM DUAL;
#3、LOOP循环
DROP FUNCTION IF EXISTS L_LOOP;
DELIMITER $$
CREATE FUNCTION L_LOOP(N INT)
RETURNS TEXT
BEGIN
DECLARE I INT DEFAULT 0;
DECLARE S TEXT DEFAULT '';
MY_LOOP : LOOP #此处循环名不可省略
SET I = I+1;
SET S = CONCAT(S,'*');
IF I >= N THEN
LEAVE MY_LOOP;
END IF;
END LOOP;
RETURN S;
END$$
SELECT L_LOOP(3) FROM DUAL;
# 出错处理、异常处理 待学习
DROP PROCEDURE IF EXISTS TEST_EXCEPTION;
DELIMITER $$
CREATE PROCEDURE TEST_EXCEPTION()
BEGIN
DECLARE A VARCHAR(102); #多个变量时必须要定义多个 不能一次定义多个
DECLARE CDE VARCHAR(102) ;
DECLARE MSG VARCHAR(102) ;
DECLARE B VARCHAR(100) ;
DECLARE N INT;
#CATCH
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION #相当于oracle exception other错误 不能有分号
BEGIN
GET DIAGNOSTICS CONDITION 1 #获取错误缓冲区内容 这些内容输出到不同范围域的变量里 没有分号 最后有个1
CDE =RETURNED_SQLSTATE,MSG = MESSAGE_TEXT; #输出到不同范围域的变量里 中间要用,号
SELECT CDE ,MSG FROM DUAL;
END;
#END CATCH
SET N =1;
MY_LOOP : LOOP
SELECT * FROM TITLE; #本表是不存在的
SET N = N+1;
SELECT 'IN LOOP' FROM DUAL;
IF N > 3 THEN
SELECT 'N>3' FROM DUAL;
LEAVE MY_LOOP;
END IF ;
END LOOP;
END$$
CALL TEST_EXCEPTION();
#获取返回值
DROP PROCEDURE IF EXISTS TEST_EXCEPTION;
DELIMITER $$
CREATE PROCEDURE TEST_EXCEPTION( OUT R_DATA TEXT)
BEGIN
DECLARE A VARCHAR(102);
DECLARE CDE VARCHAR(102) ;
DECLARE MSG VARCHAR(102) ;
DECLARE B VARCHAR(100) ;
DECLARE ERRNO VARCHAR(100);
DECLARE N INT;
#CATCH
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
CDE = RETURNED_SQLSTATE
,MSG = MESSAGE_TEXT
,ERRNO = MYSQL_ERRNO;
SET R_DATA = CONCAT(CDE,' ',MSG,' ',ERRNO) ;
END;
#END CATCH
SET N =1;
MY_LOOP : LOOP
SELECT * FROM TITLE;
SET N = N+1;
SELECT 'IN LOOP' FROM DUAL;
IF N > 3 THEN
SELECT 'N>3' FROM DUAL;
LEAVE MY_LOOP;
END IF ;
END LOOP;
END$$
CALL TEST_EXCEPTION(@R_DATA);
SELECT @R_DATA FROM DUAL
#游标遍历
#LOOP
DROP PROCEDURE IF EXISTS CURSOR_LOOP;
DELIMITER $$
CREATE PROCEDURE CURSOR_LOOP()
BEGIN
DECLARE A VARCHAR(102);
DECLARE B VARCHAR(100);
DECLARE N INT ;
DECLARE DONE INT DEFAULT 0; #游标停止标识
DECLARE MY_CURSOR CURSOR
FOR
SELECT TITLE
,AUTHORS
FROM TITLES
ORDER BY TITLEID DESC LIMIT 5;
declare continue HANDLER for not found #在MySql中,造成游标溢出时会引发mysql预定义的NOT FOUND错误,
set done = 1;#当引发not found错误时定义一个continue 的事件,指定这个事件发生时修改done变量的值。
OPEN MY_CURSOR;
MY_LOOP : LOOP
FETCH MY_CURSOR INTO A,B; #当游标的下条没有数据时会触发not found
IF DONE =1 THEN #完成游标时跳出循环 这个条件必须在执行游标后
LEAVE MY_LOOP;
END IF;
SELECT A,B FROM DUAL;
END LOOP;
CLOSE MY_CURSOR;
END$$
CALL CURSOR_LOOP();
#使用WHILE 循环遍历
#写法1
DROP PROCEDURE IF EXISTS CURSOR_WHILE;
DELIMITER $$
CREATE PROCEDURE CURSOR_WHILE (IN NUM INT)
BEGIN
DECLARE C INT;
DECLARE TI VARCHAR(200);#命名不能与字段名一样,否则获取不到游标内容
DECLARE AUTHOR VARCHAR(200);
DECLARE CURSOR_FINISHED INT DEFAULT FALSE;
DECLARE MY_CURSOR CURSOR
FOR
SELECT TITLE
,AUTHORS
FROM DM.TITLES
ORDER BY TITLEID DESC LIMIT NUM;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET CURSOR_FINISHED = TRUE;
OPEN MY_CURSOR;
FETCH MY_CURSOR INTO TI,AUTHOR; #1.循环外先步进
WHILE NOT CURSOR_FINISHED DO
SELECT TI,AUTHOR FROM DUAL; #2.循环内执行命令
FETCH MY_CURSOR INTO TI,AUTHOR;#3.循环内再步进 当NOT FOUND 时就会触发NOT FOUND 以后就不会进循环了
END WHILE;
CLOSE MY_CURSOR;
END$$
#写法2
DELIMITER $$
CREATE PROCEDURE CURSOR_WHILE (IN NUM INT)
BEGIN
DECLARE C INT;
DECLARE TI VARCHAR(200);
DECLARE AUTHOR VARCHAR(200);
DECLARE CURSOR_FINISHED INT DEFAULT FALSE;
DECLARE MY_CURSOR CURSOR
FOR
SELECT TITLE
,AUTHORS
FROM DM.TITLES
ORDER BY TITLEID DESC LIMIT NUM;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET CURSOR_FINISHED = TRUE;
OPEN MY_CURSOR;
WHILE NOT CURSOR_FINISHED DO
FETCH MY_CURSOR INTO TI,AUTHOR;
IF NOT CURSOR_FINISHED THEN #如果没触发not found 时
SELECT TI,AUTHOR FROM DUAL; #执行命令
END IF;
END WHILE;
CLOSE MY_CURSOR;
END$$
CALL CURSOR_WHILE(2);
# 使用REPEAT遍历游标
DROP PROCEDURE IF EXISTS CURSOR_REPEAT;
DILIMITER $$
CREATE PROCEDURE CURSOR_REPEAT(IN START_INDEX INT,IN NUM INT)
BEGIN
DECLARE TI VARCHAR(200);
DECLARE AUTHOR VARCHAR(200);
DECLARE CURSOR_FINISHED INT DEFAULT FALSE ;
DECLARE MY_CURSOR CURSOR
FOR
SELECT TITLE
, AUTHORS
FROM DM.TITLES
ORDER BY TITLEID DESC LIMIT START_INDEX,NUM;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET CURSOR_FINISHED = TRUE;
OPEN MY_CURSOR ;
REPEAT
FETCH MY_CURSOR INTO TI,AUTHOR;
IF NOT CURSOR_FINISHED THEN #如果没有触发 NOT FOUND时执行命令
SELECT TI,AUTHOR FROM DUAL;
END IF;
UNTIL CURSOR_FINISHED
END REPEAT;
CLOSE MY_CURSOR;
END$$
CALL CUSROR_REPEAT(2,3);