PostgreSQL与MSSQLServer在过程语言中的差异.docx
《PostgreSQL与MSSQLServer在过程语言中的差异.docx》由会员分享，可在线阅读，更多相关《PostgreSQL与MSSQLServer在过程语言中的差异.docx（62页珍藏版）》请在冰豆网上搜索。
PostgreSQL与MSSQLServer在过程语言中的差异
PostgreSQL与MSSQLServer在过程语言中的差异
数字类型
Sqlserver
Numeric/
decimal
精确数值型从-10的38次方-1，到10的38次方-1
bit
整型其值只能是0、1或空值
int
整型-2的31次方到2的31次方
smallint
整型-2的15次方到2的15次方
tinyint
0到255之间的整数
float
浮点数
real
浮点数
postgreSQL
Numeric（p,s）/
Decimal（p,s）
任意精度数值，p必须为正数，s可以为零或者正数
smallint
2字节小范围整数，-32768到+32767
Integer/
Int
4字节常用的整数，-2147483648到+2147483647
bigint
8字节大范围的整数，-9223372036854775808到9223372036854775807
real
4字节变精度，不精确6位十进制数字精度
doubleprecision
8字节变精度，不精确15位十进制数字精度
serial
4字节自增整数，1到+2147483647
bigserial
8字节大范围的自增整数，1到9223372036854775807
字符类型
Sqlserver
char
定长，最大8000
varchar
变长，SQLServer2005后可以通过varchar（MAX）来允许最大存储2G的数据
text
最多存储有2G字符
nchar
定长，最大4000国际化字符
nvarchar
变长。
ntext
最多存储有1G国际化字符
postgreSQL
charactervarying（n）varchar（n）
变长，有长度限制
character（n）
char（n）
定长，不足补空白
text
变长，无长度限制
"char"
1字节，单字节内部类型
name
64字节，用于对象名的内部类型
日期类型
Sqlserver
time
格式:
hh:
mm:
ss[.nnnnnnn],
00:
00:
00.0000000到23:
59:
59.9999999,
固定5个字节
date
格式:
YYYY-MM-DD,
0001-01-01到9999-12-31,
固定3个字节
datetime2
格式:
YYYY-MM-DDhh:
mm:
ss[.fractionalseconds],
0001-01-01到9999-12-31,
精度小于3时为6个字节；精度为3和4时为7个字节。
所有其他精度则需要8个字节。
postgreSQL
timestamp[（p）][withouttimezone]
包括日期和时间
8字节
精确到1毫秒/14位
timestamp[（p）]withtimezone
日期和时间
8字节
精确到1毫秒/14位
interval[（p）]
时间间隔
12字节
精确到1毫秒/14位
date
只用于日期
4字节
精确到1天
time[（p）][withouttimezone]
只用于一日内时间
8字节
精确到1毫秒/14位
伪类型
postgreSQL
void
应用于过程语言（PL/pgSQL）编写的函数
表示一个函数不返回数值
record
应用于过程语言（PL/pgSQL）编写的函数
标识一个函数返回一个未声明的行类型
变量定义与赋值
Sqlserver
3>DECLARE
4>@testvalueASVARCHAR（20）;
5>BEGIN
6>SET@testvalue='FirstTest!
';
7>PRINT（@testvalue）;
8>END;
9>go
postgreSQL
SQL>DECLARE
2testvalueVARCHAR（20）;
3BEGIN
4testvalue:
='FirstTest!
';
5RAISENOTICE'Testvalueis%',testvalue;
6END;
IF/ELSE控制结构
Sqlserver
1>DECLARE
2>@testvalueASINT;
3>BEGIN
4>SET@testvalue=100;
5>
6>IF@testvalue>100
7>BEGIN
8>PRINT（'100+'）;
9>END
10>ELSEIF@testvalue=100
11>BEGIN
12>PRINT（'100'）;
13>END
14>ELSE
15>BEGIN
16>PRINT（'100-'）;
17>END
18>
19>END;
20>go
postgreSQL
IF...THEN…ENDIF;
IF...THEN...ELSE…ENDIF;
IF...THEN...ELSEIF…THEN…ENDIF;
IF...THEN...ELSIF...THEN...ELSE…ENDIF;
IF...THEN...ELSEIF...THEN...ELSE…ENDIF;
（注：
ELSEIF是ELSIF的别名）
1DECLARE
2testvalueINT;
3BEGIN
4testvalue:
=100;
5
6IFtestvalue>100THEN
7RAISENOTICE'100+';
8ELSIFtestvalue=100THEN
9RAISENOTICE'100';
10ELSE
11RAISENOTICE'100-';
12ENDIF;
13
14END;
casewhen结构
Sqlserver
注：
没有decode函数
Case具有两种格式。
简单Case函数和Case搜索函数。
--简单Case函数
CASEsex
WHEN'1'THEN'男'
WHEN'2'THEN'女'
ELSE'其他'END
--Case搜索函数
CASE
WHENsex='1'THEN'男'
WHENsex='2'THEN'女'
ELSE'其他'END
postgreSQL
注：
没有decode函数
条件:
cases.xxx
when'A'
then（casewhenS.××isnullthen''else''end）
when'B'
then（casewhenS.××isnullthen''else''end）
when'C'
then（casewhenS.××isnullthen''else''end）
elses.order_classend;
循环结构
Sqlserver
WHILE
1>DECLARE
2>@testvalueASINT;
3>BEGIN
4>SET@testvalue=0;
5>
6>WHILE@testvalue<5
7>BEGIN
8>SET@testvalue=@testvalue+1;
9>PRINT（@testvalue）;
10>END
11>
12>END;
13>go
FOR
不支持
Break与Continue
1>DECLARE
2>@testvalueASINT;
3>BEGIN
4>SET@testvalue=0;
5>WHILE@testvalue<5
6>BEGIN
7>SET@testvalue=@testvalue+1;
8>IF@testvalue=2
9>BEGIN
10>CONTINUE;
11>END;
12>IF@testvalue=4
13>BEGIN
14>BREAK;
15>END;
16>PRINT（@testvalue）;
17>END
18>END;
19>go
postgreSQL
WHILE
[<
WHILEexpressionLOOP
statements
ENDLOOP[label];
例如:
WHILEamount_owed>0ANDgift_certificate_balance>0LOOP
--可以在这里做些计算
ENDLOOP;
WHILENOTBOOLEAN_expressionLOOP
--可以在这里做些计算
ENDLOOP;
LOOP
[<
LOOP
statements
ENDLOOP[label];
EXIT
EXIT[label][WHENexpression];
例如:
Loop循环
If…then条件判断
Exit;条件成立，则退出循环。
Endif;
Endloop;
CONTINUE
CONTINUE[label][WHENexpression];
例如:
LOOP
一些计算
EXITWHENcount>100;
CONTINUEWHENcount<50;
一些在count数值在[50..100]里面时候的计算
ENDLOOP;
FOR（整数变种）
[<
FORnameIN[REVERSE]expression..expressionLOOP
statements
ENDLOOP[labal];
例子∶
FORiIN1..10LOOP--表示1循环到10
--这里可以放一些表达式
RAISENOTICE'iIS%',i;
ENDLOOP;
FORiINREVERSE10..1LOOP
--这里可以放一些表达式
ENDLOOP;
存储过程（函数）
创建时的基本定义与调用
存储过程
Sqlserver
1>CREATEPROCEDUREHelloWorldAS
2>BEGIN
3>PRINT'HelloWorld';
4>END;
5>go
1>DECLARE@RCint
2>EXECUTE@RC=HelloWorld
3>PRINT@RC
4>go
HelloWorld
0
postgreSQL
注：
如果没有返回值，则使用：
returnsvoid
$body$可以简写为$$
Createorreplacefunction过程名（参数名参数类型,…..）returns返回值类型as
$body$
//声明变量
Declare
变量名变量类型；
如：
flagBoolean;
变量赋值方式（变量名类型：
=值；）
如：
Strtext:
=值;/strtext;str:
=值；
Begin
函数体；
End;
$body$
Languageplpgsql;
函数
Sqlserver
注：
如果没有那个dbo.
可能报错
3>CREATEFUNCTIONHelloWorld4（）
4>RETURNSVARCHAR（20）
5>AS
6>BEGIN
7>RETURN'HelloWorld!
';
8>END
9>go
1>SELECTdbo.HelloWorld4（）
2>go
--------------------
HelloWorld!
postgreSQL
注：
函数可以重构；根据参数类型自动匹配调用对应的函数。
（同存储过程，postgresql中没有procedure）
修改存储过程（函数）
Sqlserver
5>ALTERPROCEDUREHelloWorldAS
6>BEGIN
7>PRINT'HelloWorldV2.0';
8>END;
9>go
1>DECLARE@RCint
2>EXECUTE@RC=HelloWorld
3>PRINT@RC
4>go
HelloWorldV2.0
0
postgreSQL
注:
修改后重新编译即可
CREATEORREPLACEFUNCTIONisint（v_strvarchar）
RETURNSint
AS$$
DECLARE
V_NUMnumeric;
BEGIN
--直接修改函数体
END;
$$LANGUAGEplpgsql;
参数定义
Sqlserver
注：
如果参数的
VARCHAR不指定长
度，那么返回可能
就是
HelloE!
1>CREATEPROCEDUREHelloWorld1
2>@UserNameVARCHAR（10）
3>AS
4>BEGIN
5>PRINT'Hello'+@UserName+'!
';
6>END;
7>go
1>DECLARE@RCint;
2>EXECUTE@RC=HelloWorld1'Edward';
3>PRINT@RC;
4>go
HelloEdward!
0
postgreSQL
CREATEORREPLACEFUNCTIONisint（v_strvarchar）
RETURNSint
AS$$
DECLARE
V_NUMnumeric;
BEGIN
--函数体
END;
$$LANGUAGEplpgsql;
IN、OUT、INOUT
Sqlserver
注：
SQLServer的
OUTPUT需要写在
变量数据类型后
面。
SQLServer没有
INOUT关键字
OUTPUT已经相当
于INOUT了。
1>CREATEPROCEDUREHelloWorld2
2>@UserNameVARCHAR（10）,
3>@OutValVARCHAR（10）OUTPUT,
4>@InoutValVARCHAR（10）OUTPUT
5>AS
6>BEGIN
7>PRINT'Hello'+@UserName+@InoutVal+'!
';
8>SET@OutVal='A';
9>SET@InoutVal='B';
10>END;
11>go
1>
2>DECLARE@RCint,@OutValVARCHAR（10）,@InoutValVARCHAR（10）;
3>BEGIN
4>SET@InoutVal='~Hi~';
5>EXECUTE@RC=HelloWorld2'Edward',@OutValOUTPUT,@InoutVal
OUTPUT;
6>PRINT@RC;
7>PRINT'@OutVal='+@OutVal;
8>PRINT'@InoutVal='+@InoutVal;
9>END
10>go
HelloEdward~Hi~!
0
@OutVal=A
@InoutVal=B
postgreSQL
注：
Out需要写在变量数据类型前面；可以省去returns部分。
CREATEORREPLACEFUNCTIONp_gisrs2iodn_vendor（
v_vendorNameCnVARCHAR（255）,
OUTn_vendorIdBIGINT）
AS$$
declare
n_seqBIGINT;
BEGIN
--函数部分
END;
$$LANGUAGEplpgsql;
参数的默认值
Sqlserver
1>CREATEPROCEDUREHelloWorld3
2>@UserNameVARCHAR（10）,
3>@Val1VARCHAR（20）='GoodMoning,',
4>@Val2VARCHAR（20）='NicetoMeetyou'
5>AS
6>BEGIN
7>PRINT'Hello'+@UserName+@Val1+@Val2+'!
';
8>END;
9>go
1>
2>DECLARE@RCint;
3>BEGIN
4>EXECUTE@RC=HelloWorld3'Edward';
5>PRINT@RC;
6>EXECUTE@RC=HelloWorld3'Edward','GoodNight,';
7>PRINT@RC;
8>EXECUTE@RC=HelloWorld3'Edward','GoodNight,','Bye';
9>PRINT@RC;
10>END
11>go
HelloEdwardGoodMoning,NicetoMeetyou!
0
HelloEdwardGoodNight,NicetoMeetyou!
0
HelloEdwardGoodNight,Bye!
0
postgreSQL
注：
在8.4版本上测试可行
CREATEORREPLACEFUNCTIONdropprocedure（
v_prc_likevarchardefault'_return_ids'）
RETURNSvoid
AS$$
DECLARE
v_cntint;
v_sqlvarchar（255）;
currecord;
BEGIN
--函数部分
END;
$$LANGUAGEplpgsql;
返回结果集的函数
Sqlserver
SQLServer通过
RETURNTABLE来
实现。
1>CREATEFUNCTIONgetHelloWorld（）
2>RETURNSTABLE
3>AS
4>RETURN
5>SELECT'Hello'ASA,'World'ASB;
6>GO
1>SELECT*FROMgetHelloWorld（）;
2>go
AB
----------
HelloWorld
（1行受影响）
postgreSQL
注：
行变量类型使用record来定义。
CREATEORREPLACEFUNCTIONf_querydata（v_colvarchar（255））
ReturnsSETOFrecord
AS$$
DECLARE
v_sqltext;
v_recrecord;
Begin
v_sql:
=
'SELECT*FROMTABLE_NAMEWHEREcolname='||v_col;
FORv_recINexecutev_sqlLoop--隐性游标,使用FOR可以避免定义多个变量。
RETURNNEXTv_rec;
ENDLOOP;
END;
$$LANGUAGEplpgsq;
在存过中访问或调用函数的方法
postgreSQL
第一：
returnsvoid的函数：
PERFORMp_testfun（v1,v2,…）;
第二：
returnstype的函数：
例如：
--函数
CREATEORREPLACEFUNCTIONp_testfun（v1BIGINT,v2VARCHAR（255））
RETURNSvarchar
AS$$
Declare
v_rnvarchar2（255）;
BEGIN
--处理
returnv_rn;
END;
$$LANGUAGEplpgsql;
--调用
Declare
v_ttvarchar（255）;
Begin
v_tt:
=p_testfun（v1,v2）;
End;
第三：
使用单个out参数定义返回的函数：
例如：
--函数
CREATEORREPLACEFUNCTIONp_testfun（v1BIGINT,outv2VARCHAR（255））
AS$$
Declare
v_rnvarchar2（255）;
BEGIN
--处理
v2:
=v_rn;
END;
$$LANGUAGEplpgsql;
--调用
Declare
v_ttvarchar（255）;
Begin
v_tt:
=p_testfun（v1）;
End;
第四：
使用多个out参数定义返回的函数：
例如：
--函数
CREATEORREPLACEFUNCTIONp_testfun（v1BIGINT,outv2st_geometry,outv3numeric（28,20）,outv4numeric（28,20））
AS$$
Declare
v_rnvarchar2（255）;
BEGIN
--处理
END;
$$LANGUAGEplpgsql;
--调用
Declare
v_tt1st_geometry;
v_tt2numeric（28,20）;
v_tt2numeric（28,20）;
Begin
selectt1.v2,t1.v3,t1.v4
intov_tt1,v_tt2,v_tt3
fromp_testfun（v1）ast1;
End;
cursor游标定义、使用
Sqlserver
注：
CREATETABLEtest_main（
idINT,
valueVARCHAR（10）,
PRIMARYKEY（id）
）;
INSERTINTOtest_main（id,value）VALUES（1,
升级会员 