mysql精華

# 動態sql
CREATE DEFINER = CURRENT_USER PROCEDURE `test`(IN USER_ID varchar(10), IN USER_NAME varchar(10))
BEGIN                                                        　　　　　　　　　　　　　　　　　　　　　　    
       set @SQL_FOR_SELECT = CONCAT("select * from  user  where userId = '",USER_ID,"' and userName = '",USER_NAME,"'");   -- 拼接查詢sql陳述句
       PREPARE stmt FROM @SQL_FOR_SELECT;       -- 預處理動態sql陳述句
       EXECUTE stmt ;                           -- 執行sql陳述句
       deallocate prepare stmt;                 -- 釋放prepare

END;


#創建臨時表

DROP TEMPORARY TABLE IF EXISTS tmp_order; -- 先刪一下再說，比較保險
create temporary table tmp_order(
　　id int primary key,
　　name varchar(20) 
)Engine=InnoDB default charset utf8;

CREATE TEMPORARY TABLE dw.tmp_order AS ( SELECT * FROM dw.f_gdmx_all WHERE orderno IS NULL LIMIT 1 );-- 懶人方法創建臨時表不建議使用


#匯出資料字典

SELECT
TABLE_SCHEMA,
TABLE_NAME AS '表名',
COLUMN_NAME AS '欄位名',
COLUMN_TYPE AS '資料型別',
IS_NULLABLE AS '允許為空',
EXTRA AS '額外資訊',
COLUMN_COMMENT AS '欄位說明'
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA = 'api'


#關聯洗掉

DELETE  sap_ybtx from sap_ybtx  INNER JOIN sap_ybtx_notin notin  on notin.mandt=sap_ybtx.mandt and 
notin.gjahr=sap_ybtx.gjahr and notin.buzei=sap_ybtx.buzei and notin.bukrs=sap_ybtx.bukrs and notin.belnr=sap_ybtx.belnr


#批量更新
UPDATE f_order_finish f ,orderno_cf c  set f.cf=1 where f.orderno=c.orderno


#存盤程序回圈處理資料
CREATE DEFINER=`dw`@`%` PROCEDURE `test`()
BEGIN
   DECLARE done INT DEFAULT FALSE;
	 DECLARE xx VARCHAR(64);
	 
    DECLARE cur_list CURSOR FOR   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    OPEN cur_list;
    read_loop : LOOP
        FETCH cur_list INTO xx;
        IF done THEN
            LEAVE read_loop;
        END IF;
			 
        COMMIT;
    END LOOP read_loop;
CLOSE cur_list;

#字串分割成多行
SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM 
    mysql.help_topic 
WHERE 
    help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1



 自定義函式字串分割函式
CREATE DEFINER=`root`@`%` PROCEDURE `str_to_list`(IN str varchar(4000), IN split varchar(255))
BEGIN
    SELECT substring_index(substring_index(a.chain, split, b.help_topic_id + 1), split, - 1) AS list
    FROM (select str as chain) a
             JOIN mysql.help_topic b ON b.help_topic_id < (length(a.chain) - length(replace(a.chain, split, '')) + 1);

END


#創建記憶體表

CREATE TABLE `abc` ENGINE= MEMORY select * from `sap_zbkpf_bseg` limit 1;


#存盤程序函式賦值

	SELECT
				ooi.orderId,
				ooi.state,
				ooi.insuranceOrderId,
				MIN(ooi.mainStartTime) AS mainStartTime, -- 生效日期 需取最早開始生效日期(主部件和非主部件的)
				ooi.mainEndTime,
				ooi.notMainStartTime,
				ooi.notMainEndTime,
				ooi.brandName
		into 	@orderId,@state,@insuranceOrderId,@mainStartTime,@mainEndTime,	@notMainStartTime,@notMainEndTime,@brandName


#實作排名

SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t;


SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, testsort AS t
ORDER BY t.score DESC;

#強制轉型別
cast( `oyss`.`salegroupcode` AS signed ) signed 為數字


#補全前導零
CREATE DEFINER=`root`@`%` FUNCTION `firstAddZro`(str VARCHAR(10)) RETURNS varchar(10) CHARSET utf8mb4
BEGIN
	 declare result varchar(10);
	 set result=str;
		WHILE LENGTH(result)<10 DO
			set result=CONCAT('0',result);
		END WHILE;

	RETURN result;
END

 #指定索引
 select * from user force index(username_index) where  username='zhanqi'

 #存盤程序開啟事物(看這個注釋<-- 這里>)
CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
    BEGIN
        DECLARE error TINYINT DEFAULT 1;
        DECLARE DONE INT DEFAULT FALSE;
        DECLARE field_loginName VARCHAR(50);

        DECLARE cur_list CURSOR FOR (SELECT a.loginName from asd_oa_empinfo  where a.operType <> 'delete');

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = - 1; -- 這里

        START TRANSACTION; -- 這里

        OPEN cur_list;
        read_loop :
        LOOP
            FETCH cur_list INTO field_loginName;

            IF DONE THEN
                LEAVE read_loop;
            END IF;

          -- 這里
            IF error = - 1 THEN
                ROLLBACK;
            ELSE
                COMMIT;
            END IF;

        END LOOP read_loop;
        CLOSE cur_list;
        SELECT error;

    END;
END