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

最新推荐文章于 2022-12-13 00:58:46 发布

当富网络

最新推荐文章于 2022-12-13 00:58:46 发布

阅读量437

点赞数

分类专栏： MYSQL/ORACLE/...

本文为博主原创文章，未经博主允许不得转载。安卓五千源码下载--https://blog.csdn.net/wuqilianga/article/details/89185887

本文链接：https://blog.csdn.net/wuqilianga/article/details/78411407

版权

MYSQL/ORACLE/... 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

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

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

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

CREATE PROCEDURE sys_InitLawlessSource @Plugin_DB VARCHAR(50) AS
BEGIN
	DECLARE @fdFormID INT
	DECLARE @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'))
	BEGIN
		CREATE TABLE FO_Form (
		   fdFormID             INT                  NOT NULL,
		   fdFormName           NVARCHAR(200)        NOT NULL,
		   fdFormTable           NVARCHAR(50)         NOT NULL,
		   CONSTRAINT PK_FO_Form PRIMARY KEY (fdFormID)
		)
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FO_Field'))
	BEGIN
		CREATE 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)
		)
	END
	
	DECLARE @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 FOR
	SELECT fdFormID,fdFormName,fdFormTable FROM FO_Form
	OPEN CURSOR_FORMSOURSE
	FETCH NEXT FROM CURSOR_FORMSOURSE INTO @fdFormID,@LasoName,@tableName
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		SELECT @FIELDS= @FIELDS+','+fdFielName FROM FO_Field WHERE fdFielFormID = @fdFormID AND (fdFielCtrlType =1 OR fdFielCtrlType =2)
		
		IF(@FIELDS != '')
		BEGIN
			IF NOT EXISTS(SELECT fdLasoID FROM  FA_Lawless_Source WHERE fdLasoTable=@tableName)
			BEGIN
				INSERT INTO FA_Lawless_Source(fdLasoID,fdLasoName,fdLasoConnStr,fdLasoTable,fdLasoPriKey,fdLasoFields)
				VALUES(90000+@fdFormID,@LasoName,@ConnStr,@tableName,@PriKey,SUBSTRING(@FIELDS,2,(len(@FIELDS)-1)))
			END	
		END
	
	FETCH NEXT FROM CURSOR_FORMSOURSE INTO @fdFormID,@LasoName,@tableName
	END
	CLOSE CURSOR_FORMSOURSE
	DEALLOCATE CURSOR_FORMSOURSE
END;

挨个学习下：

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

DELIMITER //
CREATE PROCEDURE sys_InitLawlessSource(
IN Plugin_DB VARCHAR(50)
)
BEGIN
DECLARE 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()
BEGIN
IF 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()
BEGIN
IF NOT EXISTS (SELECT * FROM USER WHERE id < 1) THEN
CREATE 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)
BEGIN
DECLARE 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的存储过程.