PostgreSQL与MSSQLServer在过程语言中的差异.docx

1、PostgreSQL与MSSQLServer在过程语言中的差异PostgreSQL 与 MS SQLServer在过程语言中的差异数字类型Sql serverNumeric/decimal精确数值型 从-10 的38 次方-1，到10 的38 次方-1bit整型 其值只能是0、1 或空值int整型 -2 的31 次方到 2 的31 次方smallint整型 -2 的15 次方到 2 的15 次方tinyint0 到255 之间的整数float浮点数real浮点数postgreSQLNumeric(p,s)/Decimal(p,s)任意精度数值，p必须为正数，s可以为零或者正数smallint2

2、字节小范围整数，-32768到+32767Integer/Int4字节常用的整数，-2147483648到+2147483647bigint8字节大范围的整数，-9223372036854775808到9223372036854775807real4字节变精度，不精确6位十进制数字精度double precision8字节变精度，不精确15位十进制数字精度serial4字节自增整数，1到+2147483647bigserial8字节大范围的自增整数，1到9223372036854775807字符类型Sql serverchar定长，最大8000varchar变长，SQL Server2005后

3、可以通过 varchar(MAX)来允许最大存储2G 的数据text最多存储有2G 字符nchar定长，最大4000 国际化字符nvarchar变长。ntext最多存储有1G 国际化字符postgreSQLcharacter varying(n) varchar(n)变长，有长度限制 character(n)char(n)定长，不足补空白 text变长，无长度限制 char1 字节，单字节内部类型 name64字节，用于对象名的内部类型 日期类型Sql servertime格式: hh:mm:ss.nnnnnnn,00:00:00.0000000 到 23:59:59.9999999,固定5个

4、字节date格式: YYYY-MM-DD,0001-01-01 到 9999-12-31, 固定3个字节datetime2格式: YYYY-MM-DD hh:mm:ss.fractional seconds,0001-01-01 到 9999-12-31,精度小于3时为6个字节；精度为3和4时为7个字节。所有其他精度则需要8个字节。postgreSQLtimestamp(p) without time zone包括日期和时间 8字节 精确到1毫秒/14位 timestamp(p) with time zone日期和时间 8字节 精确到1毫秒/14位 interval(p)时间间隔 12 字节

5、精确到1毫秒/14位 date 只用于日期 4字节 精确到1天 time(p) without time zone只用于一日内时间 8字节 精确到1毫秒/14位 伪类型postgreSQLvoid应用于过程语言(PL/pgSQL)编写的函数表示一个函数不返回数值 record应用于过程语言(PL/pgSQL)编写的函数标识一个函数返回一个未声明的行类型 变量定义与赋值Sql server3 DECLARE4 testvalue AS VARCHAR(20);5 BEGIN6 SET testvalue = First Test!;7 PRINT( testvalue );8 END;9 gop

6、ostgreSQLSQL DECLARE2 testvalue VARCHAR(20);3 BEGIN4 testvalue := First Test!;5 RAISE NOTICE Testvalue is %, testvalue;6 END;IF/ELSE控制结构Sql server1 DECLARE2 testvalue AS INT;3 BEGIN4 SET testvalue = 100;56 IF testvalue 1007 BEGIN8 PRINT( 100+ );9 END10 ELSE IF testvalue = 10011 BEGIN12 PRINT( 100 );

7、13 END14 ELSE15 BEGIN16 PRINT( 100- );17 END 1819 END;20 gopostgreSQLIF . THEN END IF;IF . THEN . ELSE END IF;IF . THEN . ELSE IF THEN END IF;IF . THEN . ELSIF . THEN . ELSE END IF;IF . THEN . ELSEIF . THEN . ELSE END IF;（注：ELSEIF 是 ELSIF 的别名）1 DECLARE2 testvalue INT;3 BEGIN4 testvalue := 100;56 IF

8、testvalue 100 THEN7 RAISE NOTICE 100+;8 ELSIF testvalue = 100 THEN9 RAISE NOTICE 100;10 ELSE11 RAISE NOTICE 100-;12 END IF;1314 END;case when 结构Sql server注：没有decode函数Case具有两种格式。简单Case函数和Case搜索函数。-简单Case函数CASE sex WHEN 1 THEN 男 WHEN 2 THEN 女ELSE 其他 END-Case搜索函数CASE WHEN sex = 1 THEN 男 WHEN sex = 2 TH

9、EN 女ELSE 其他 ENDpostgreSQL注：没有decode函数条件:case s.xxxwhen A then (case when S. is null then else end)when B then( case when S. is null then else end)when C then( case when S. is null then else end) else s.order_class end;循环结构Sql serverWHILE1 DECLARE2 testvalue AS INT;3 BEGIN4 SET testvalue = 0;56 WHILE

10、 testvalue BEGIN8 SET testvalue = testvalue + 1;9 PRINT ( testvalue );10 END1112 END;13 goFOR不支持Break 与 Continue1 DECLARE2 testvalue AS INT;3 BEGIN4 SET testvalue = 0;5 WHILE testvalue BEGIN7 SET testvalue = testvalue + 1;8 IF testvalue = 29 BEGIN10 CONTINUE;11 END;12 IF testvalue = 413 BEGIN14 BREA

11、K;15 END;16 PRINT ( testvalue );17 END18 END;19 gopostgreSQLWHILE WHILE expression LOOP statementsEND LOOP label ;例如:WHILE amount_owed 0 AND gift_certificate_balance 0 LOOP - 可以在这里做些计算END LOOP;WHILE NOT BOOLEAN_expression LOOP - 可以在这里做些计算END LOOP;LOOP LOOP statementsEND LOOP label ;EXITEXIT label WH

12、EN expression ;例如:Loop 循环If then 条件判断 Exit ; 条件成立，则退出循环。End if;End loop;CONTINUECONTINUE label WHEN expression ;例如: LOOP 一些计算 EXIT WHEN count 100; CONTINUE WHEN count 50; 一些在count 数值在 50 . 100 里面时候的计算END LOOP;FOR（整数变种） FOR name IN REVERSE expression . expression LOOP statementsEND LOOP labal ;例子 FOR

13、 i IN 1.10 LOOP -表示1循环到10 -这里可以放一些表达式 RAISE NOTICE i IS %, i;END LOOP;FOR i IN REVERSE 10.1 LOOP -这里可以放一些表达式END LOOP;存储过程（函数）创建时的基本定义与调用存储过程Sql server1 CREATE PROCEDURE HelloWorld AS2 BEGIN3 PRINT Hello World;4 END;5 go1 DECLARE RC int2 EXECUTE RC = HelloWorld3 PRINT RC4 goHello World0postgreSQL注：如果

14、没有返回值，则使用：returns void$body$可以简写为$ Create or replace function 过程名(参数名 参数类型,.) returns 返回值类型 as $body$ /声明变量 Declare 变量名 变量类型； 如： flag Boolean; 变量赋值方式（变量名 类型 ：=值；） 如： Str text :=值; / str text; str :=值； Begin 函数体； End; $body$ Language plpgsql;函数Sql server注：如果没有那个dbo.可能报错3 CREATE FUNCTION HelloWorld4()

15、4 RETURNS VARCHAR(20)5 AS6 BEGIN7 RETURN Hello World!;8 END9 go1 SELECT dbo.HelloWorld4()2 go-Hello World!postgreSQL注：函数可以重构；根据参数类型自动匹配调用对应的函数。（同存储过程，postgresql中没有procedure）修改存储过程（函数）Sql server5 ALTER PROCEDURE HelloWorld AS6 BEGIN7 PRINT Hello World V2.0;8 END;9 go1 DECLARE RC int2 EXECUTE RC = Hel

16、loWorld3 PRINT RC4 goHello World V2.00postgreSQL注: 修改后重新编译即可CREATE OR REPLACE FUNCTION isint(v_str varchar)RETURNS intAS $ DECLARE V_NUM numeric;BEGIN -直接修改函数体END;$ LANGUAGE plpgsql;参数定义Sql server注：如果参数的VARCHAR 不指定长度，那么 返回可能就是HelloE!1 CREATE PROCEDURE HelloWorld12 UserName VARCHAR(10)3 AS4 BEGIN5 PR

17、INT Hello + UserName + !;6 END;7 go1 DECLARE RC int;2 EXECUTE RC = HelloWorld1 Edward ;3 PRINT RC;4 goHelloEdward!0postgreSQLCREATE OR REPLACE FUNCTION isint(v_str varchar)RETURNS intAS $ DECLARE V_NUM numeric;BEGIN -函数体END;$ LANGUAGE plpgsql;IN、OUT、IN OUTSql server注：SQL Server 的OUTPUT 需要写在变量数据类型后面。

18、SQL Server 没有IN OUT 关键字OUTPUT 已经相当于 IN OUT 了。1 CREATE PROCEDURE HelloWorld22 UserName VARCHAR(10),3 OutVal VARCHAR(10) OUTPUT,4 InoutVal VARCHAR(10) OUTPUT5 AS6 BEGIN7 PRINT Hello + UserName + InoutVal + !;8 SET OutVal = A;9 SET InoutVal = B;10 END;11 go12 DECLARE RC int, OutVal VARCHAR(10), InoutVa

19、l VARCHAR(10);3 BEGIN4 SET InoutVal = Hi;5 EXECUTE RC = HelloWorld2 Edward, OutVal OUTPUT, InoutValOUTPUT;6 PRINT RC;7 PRINT OutVal= + OutVal;8 PRINT InoutVal= + InoutVal;9 END10 goHello EdwardHi!0OutVal=AInoutVal=BpostgreSQL注： Out 需要写在变量数据类型前面；可以省去returns部分。CREATE OR REPLACE FUNCTION p_gisrs2iodn_v

20、endor(v_vendorNameCn VARCHAR(255), OUT n_vendorId BIGINT)AS $ declare n_seq BIGINT;BEGIN -函数部分END;$ LANGUAGE plpgsql;参数的默认值Sql server1 CREATE PROCEDURE HelloWorld32 UserName VARCHAR(10),3 Val1 VARCHAR(20) = Good Moning,4 Val2 VARCHAR(20) = Nice to Meet you5 AS6 BEGIN7 PRINT Hello + UserName + Val1 +

21、 Val2 + !;8 END;9 go12 DECLARE RC int;3 BEGIN4 EXECUTE RC = HelloWorld3 Edward;5 PRINT RC;6 EXECUTE RC = HelloWorld3 Edward, Good Night,;7 PRINT RC;8 EXECUTE RC = HelloWorld3 Edward, Good Night, Bye;9 PRINT RC;10 END11 goHello Edward Good Moning, Nice to Meet you!0Hello Edward Good Night, Nice to Me

22、et you!0Hello Edward Good Night, Bye!0postgreSQL注： 在8.4版本上测试可行CREATE OR REPLACE FUNCTION dropprocedure(v_prc_like varchar default _return_ids) RETURNS void AS $DECLARE v_cnt int; v_sql varchar(255); cur record;BEGIN -函数部分 END;$ LANGUAGE plpgsql;返回结果集的函数Sql serverSQL Server 通过RETURN TABLE 来实现。1 CREAT

23、E FUNCTION getHelloWorld()2 RETURNS TABLE3 AS4 RETURN5 SELECT Hello AS A, World AS B;6 GO1 SELECT * FROM getHelloWorld();2 goA B- -Hello World(1 行受影响)postgreSQL注：行变量类型使用record来定义。CREATE OR REPLACE FUNCTION f_querydata(v_col varchar(255)Returns SETOF record AS $DECLARE v_sql text; v_rec record;Begin

24、v_sql := SELECT * FROM TABLE_NAME WHERE colname = | v_col; FOR v_rec IN execute v_sql Loop -隐性游标, 使用FOR可以避免定义多个变量。 RETURN NEXT v_rec; END LOOP; END;$ LANGUAGE plpgsq;在存过中访问或调用函数的方法postgreSQL第一：returns void 的函数：PERFORM p_testfun(v1,v2,);第二：returns type 的函数：例如：-函数CREATE OR REPLACE FUNCTION p_testfun(v

25、1 BIGINT, v2 VARCHAR(255)RETURNS varchar AS $Declare v_rn varchar2(255);BEGIN -处理 return v_rn;END;$ LANGUAGE plpgsql;-调用Declare v_tt varchar(255);Begin v_tt := p_testfun(v1,v2);End;第三：使用单个out参数定义返回的函数：例如：-函数CREATE OR REPLACE FUNCTION p_testfun(v1 BIGINT, out v2 VARCHAR(255) AS $Declare v_rn varchar2

26、(255);BEGIN -处理 v2 := v_rn;END;$ LANGUAGE plpgsql;-调用Declare v_tt varchar(255);Begin v_tt := p_testfun(v1);End;第四：使用多个out参数定义返回的函数：例如：-函数CREATE OR REPLACE FUNCTION p_testfun(v1 BIGINT, out v2 st_geometry, out v3 numeric(28, 20), out v4 numeric(28, 20) AS $Declare v_rn varchar2(255);BEGIN -处理END;$ LA

27、NGUAGE plpgsql;-调用Declare v_tt1 st_geometry;v_tt2 numeric(28, 20);v_tt2 numeric(28, 20);Begin select t1.v2, t1.v3, t1.v4 into v_tt1, v_tt2, v_tt3 from p_testfun(v1) as t1;End;cursor游标定义、使用Sql server注：CREATE TABLE test_main (id INT,value VARCHAR(10),PRIMARY KEY(id);INSERT INTO test_main(id, value) VALUES (1,