Mysql procedure 存储过程学习，小例子

有份工作，SSH web系统需要多数据库兼容，通过Powerdesigner转换，修改下建立好表结构和索引后，发现了难题了存储过程咋办

例如有以下存储过程，转换好像还蛮麻烦的，下边是Powerdesigner转换过来的mssql类似版本的，不适用于mysql语法，不行，那挨个学习转换过来呗，若哪位厉害的知情人

发现有更好的自动化转换工具，麻烦下方评论告知下，谢谢了！

CREATE PROCEDURE sys_InitLawlessSource @Plugin_DB VARCHAR(50) ASBEGINDECLARE @fdFormID INTDECLARE @LasoName VARCHAR(50)DECLARE @tableName VARCHAR(50) DECLARE @FIELDS VARCHAR(100) SET @FIELDS=''DECLARE @PriKey VARCHAR(50) SET @PriKey = 'fdID'DECLARE @ConnStr VARCHAR(50) SET @ConnStr = 'Plugin_DB'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FO_Form'))BEGINCREATE TABLE FO_Form (   fdFormID             INT                  NOT NULL,   fdFormName           NVARCHAR(200)        NOT NULL,   fdFormTable           NVARCHAR(50)         NOT NULL,   CONSTRAINT PK_FO_Form PRIMARY KEY (fdFormID))ENDIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FO_Field'))BEGINCREATE TABLE FO_Field (   fdFielID             INT                  NOT NULL,   fdFielFormID           INT       NOT NULL,   fdFielName           NVARCHAR(50)         NOT NULL,   fdFielCtrlType           INT       NOT NULL,   CONSTRAINT PK_FO_Field PRIMARY KEY (fdFielID))ENDDECLARE @SQL_Form VARCHAR(1024) SET @SQL_Form=('DELETE FO_Form INSERT FO_Form(fdFormID,fdFormName,fdFormTable)  SELECT fdFormID,fdFormName,fdFormTable FROM '+(@Plugin_DB+'.FO_Form'))exec( @SQL_Form)DECLARE @SQL_Field VARCHAR(1024) SET @SQL_Field=('DELETE FO_Field INSERT FO_Field(fdFielID,fdFielFormID,fdFielName,fdFielCtrlType)  SELECT fdFielID,fdFielFormID,fdFielName,fdFielCtrlType FROM '+(@Plugin_DB+'.FO_Field') +' WHERE fdFielCtrlType =1 OR fdFielCtrlType =2')exec( @SQL_Field)DECLARE CURSOR_FORMSOURSE CURSOR FORSELECT fdFormID,fdFormName,fdFormTable FROM FO_FormOPEN CURSOR_FORMSOURSEFETCH NEXT FROM CURSOR_FORMSOURSE INTO @fdFormID,@LasoName,@tableNameWHILE @@FETCH_STATUS = 0 BEGINSELECT @FIELDS= @FIELDS+','+fdFielName FROM FO_Field WHERE fdFielFormID = @fdFormID AND (fdFielCtrlType =1 OR fdFielCtrlType =2)IF(@FIELDS != '')BEGINIF NOT EXISTS(SELECT fdLasoID FROM  FA_Lawless_Source WHERE fdLasoTable=@tableName)BEGININSERT INTO FA_Lawless_Source(fdLasoID,fdLasoName,fdLasoConnStr,fdLasoTable,fdLasoPriKey,fdLasoFields)VALUES(90000+@fdFormID,@LasoName,@ConnStr,@tableName,@PriKey,SUBSTRING(@FIELDS,2,(len(@FIELDS)-1)))ENDENDFETCH NEXT FROM CURSOR_FORMSOURSE INTO @fdFormID,@LasoName,@tableNameENDCLOSE CURSOR_FORMSOURSEDEALLOCATE CURSOR_FORMSOURSEEND;

挨个学习下：

【demo功能：创建存储过程、声明变量、操作查询、调用、删除存储过程】

DELIMITER //CREATE PROCEDURE sys_InitLawlessSource(IN Plugin_DB VARCHAR(50))BEGINDECLARE fdFormID INT;SELECT * FROM USER;END    //DELIMITER ;CALL sys_InitLawlessSource('xxx')DROP PROCEDURE IF EXISTS  sys_InitLawlessSource

【demo功能：IF EXISTS用法，IF 用法  存储过程创建表的用法】

DROP PROCEDURE IF EXISTS gg;DELIMITER $$CREATE PROCEDURE gg()BEGINIF NOT EXISTS(SELECT  * FROM 1go.user WHERE id<1 ) THEN CREATE TABLE FO_Form (`fdFormID`             INT                  NOT NULL);END IF;END$$DELIMITER ;CALL gg;

【demo功能：IF EXISTS用法，IF 用法  存储过程创建表（外键关联）的用法,最后一个Delimiter如果没有加分号,且分号前留有空格，执行会报错，通不过】

DROP PROCEDURE IF EXISTS create_table;DELIMITER $$CREATE PROCEDURE create_table()BEGINIF NOT EXISTS (SELECT * FROM USER WHERE id < 1) THENCREATE TABLE test_1_tb(`id` INT NOT NULL,`string1` VARCHAR(255),`fdFormID` INT NOT NULL,CONSTRAINT PK_FO_Form PRIMARY KEY (fdFormID));END IF; END$$DELIMITER ;####这里分号前的空格要保留，否则报以下错误

错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

【demo功能：execute执行动态sql语句、参数传值和调用】

DROP PROCEDURE IF EXISTS data_trans;DELIMITER $$CREATE PROCEDURE data_trans(IN a VARCHAR(8000),IN b VARCHAR(8000),OUT c INT)BEGINDECLARE sql_ VARCHAR(8000);SET @MyQuery='INSERT INTO fo_form SELECT * FROM fo_form1';PREPARE sql_ FROM @MyQuery; EXECUTE sql_;END$$DELIMITER ;CALL  data_trans('','',@c);

【demo功能：游标使用，execute语句】

DELIMITER $$DROP PROCEDURE IF EXISTS CursorProc $$DROP TABLE IF EXISTS infologs $$CREATE PROCEDURE CursorProc ()      BEGIN     DECLARE  no_more_products , quantity_in_stock INT DEFAULT 0;     DECLARE  prd_code VARCHAR(255);     DECLARE  mysql_var VARCHAR(255);     DECLARE  more_ VARCHAR(255);  /*多参数抓取游标，深刻理解游标是什么，其实有点像一行数据集 */            DECLARE  cur_product CURSOR FOR   SELECT id,email FROM USER;         /*First: Delcare a cursor,首先这里对游标进行定义*/            DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_products = 1;  /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/                  /*for  loggging information 创建个临时表格来保持*/       /* CREATE TEMPORARY TABLE infologs (  Id int(11) NOT NULL AUTO_INCREMENT,  emial varchar(255) NOT NULL,  PRIMARY KEY (Id)  );     */  /*等同于下边的execute语句*/                 SET @mysql_ = 'CREATE TEMPORARY TABLE infologs ( Id int(11)  NOT NULL AUTO_INCREMENT,  emial varchar(255) NOT NULL,  PRIMARY KEY (Id) )';     PREPARE mysql_var FROM @mysql_;     EXECUTE mysql_var;               IF 1 = 2 THEN     SELECT id FROM USER;                                                 /*学习下if else语句*/       ELSE      SELECT email FROM USER;                                                    END IF;                  OPEN  cur_product;                                                   /*Second: Open the cursor 接着使用OPEN打开游标*/       FETCH  cur_product INTO prd_code,more_;                              /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/             REPEAT             SELECT  id INTO quantity_in_stock       FROM  USER       WHERE  id LIKE CONCAT('%',prd_code);              IF  quantity_in_stock < 100 THEN       INSERT  INTO infologs(emial)       VALUES  (prd_code);       END  IF;       FETCH  cur_product INTO prd_code,more_;             UNTIL  no_more_products = 1       END REPEAT;       CLOSE  cur_product;  /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/       /*SELECT *  FROM infologs;*/      /*SELECT *  FROM user;  */     ##SELECT *  FROM user right join infologs on 1=1;      DROP TABLE  infologs;      END $$DELIMITER ;CALL CursorProc;

未完待续！！！！持续学习中，学会了，就把上边的语法转换成mysql的存储过程.