MySQL、SqlServer、postgresql自定义函数和存储过程

2018-02-12 10:47:12来源:http://kukiwi.iteye.com作者:kukiwi人点击

分享
一、MySQL1、MySQL自定义函数及调用-- 报错his function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logg,解决办法set global log_bin_trust_function_creators=TRUE;-- 自定义函数CREATE FUNCTION mytest (a INT, b INT) RETURNS INTBEGINRETURN a + b;END-- 调用自定义函数SELECT mytest(4,2); 2、MySQL自定义存储过程及调用-- 创建自定义存储过程CREATE PROCEDURE myPro(a INT,b INT)BEGININSERT INTO `mg`.`mg_favorites` (`regnumber`,`type`,`object_id`,`ctime`)VALUES(a,b,'12784','1471871232');END-- 调用自定义存储过程CALL myPro(1,2); 二、SqlServer1、SqlServer自定义函数及调用-- 自定义函数CREATE FUNCTION dbo.mytest (@a INT ,@b INT) RETURNS INT ASBEGINRETURN @a +@bEND -- 调用自定义函数SELECT dbo.mytest(1,2); 2、SqlServer自定义存储过程及调用-- 自定义存储过程CREATE PROCEDURE dbo.insertActiveData (@dataCount INT) ASBEGINDECLARE@i INT ;SET @i = 0 ;WHILE @i < @dataCountBEGININSERT INTO log_DeviceInfo ([Idfa],[Idfa_MD5],[IsDel],[CreateDate],[EditDate],[ClientIP],[OsType],[PhoneType],[SystemVersion],[StartDate],[Idfa_UP_MD5],[Imei],[Channel])VALUES('868029027522895','9FD47F63EADB66590A531884215F6212','0','2018-02-05 19:10:57.920','2018-02-05 19:10:57.920','112.227.78.1','android','Redmi+Note+3','5.0.2','2018-02-05 19:07:54.000','9FD47F63EADB66590A531884215F6212','868029027511111','WHAN_XM') ;SET @i = @i + 1 ;ENDEND -- 调用存储过程EXECUTE dbo.insertActiveData 10000 ; 三、postgresql1、postgresql自定义函数及调用http://panyongzheng.iteye.com/blog/2194815-- 创建自定义函数CREATEOR REPLACE FUNCTION test_function (INTEGER) RETURNS INTEGER AS$$DECLAREb_count ALIAS FOR $1 ;BEGINwhile b_count > 0 loop INSERT INTO "public"."call_log" ("app_id","platform_id","call_id","type","extension_number","use_long","use_time","recording_file","calling_number","called_number","province","city","editor","edit_time","creator","create_time","app_account_id")VALUES('19','1','1505722054.5600903','1','2009',NULL,'2017-09-18 16:11:25.466',NULL,'28306099','15088052151','广东','广州','19','2017-09-18 16:44:14.801','19','2017-09-18 16:07:48.097615',NULL) ;b_count := b_count-1 ;END loop ;RETURN b_count ;END;$$LANGUAGE plpgsql;-- 调用自定义函数SELECT test_function (100000); 

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台