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

一、MySQL

1、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 INT

BEGIN

RETURN a + b;

END

-- 调用自定义函数

SELECT mytest(4,2);

 

2、MySQL自定义存储过程及调用

-- 创建自定义存储过程

CREATE PROCEDURE myPro(a INT,b INT)

BEGIN

INSERT INTO `mg`.`mg_favorites` (

`regnumber`,

`type`,

`object_id`,

`ctime`

)

VALUES

(

a,

b,

'12784',

'1471871232'

);

END

-- 调用自定义存储过程

CALL myPro(1,2);

 

二、SqlServer

1、SqlServer自定义函数及调用

-- 自定义函数

CREATE FUNCTION dbo.mytest (@a INT ,@b INT) RETURNS INT AS

BEGIN

RETURN @a +@b

END

 

-- 调用自定义函数

SELECT dbo.mytest(1,2);

 

2、SqlServer自定义存储过程及调用

-- 自定义存储过程

CREATE PROCEDURE dbo.insertActiveData (@dataCount INT) AS

BEGIN

DECLARE

@i INT ;

SET @i = 0 ;

WHILE @i < @dataCount

BEGIN

INSERT 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 ;

END

END

 

-- 调用存储过程

EXECUTE dbo.insertActiveData 10000 ;

 

三、postgresql

1、postgresql自定义函数及调用

http://panyongzheng.iteye.com/blog/2194815

-- 创建自定义函数

CREATE

OR REPLACE FUNCTION test_function (INTEGER) RETURNS INTEGER AS

$$

DECLARE

b_count ALIAS FOR $1 ;

BEGIN

while 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);