Postgresql没有像sqlServer一样的存储过程。他们的标准也不一样，sqlserver是T-sql标准，而Postgresql有很多过程语言，其中以 PL/pgsql最热。由于我也是初学，就不在这里跟大家讨论语法了。进入主题，模拟实现sqlserver中的存储过程

首先，在建好Postgresql中的函数（Postgresql中过程用函数来实现），代码如下：

CREATE OR REPLACE FUNCTION "public"."userreg" (p_passport varchar,p_nickname varchar,out p_password varchar,out p_userid integer) RETURNS record AS@H_502_5@$body$@H_502_5@declare@H_502_5@tmpstr varchar(32);@H_502_5@begin@H_502_5@$3 := NULL;@H_502_5@$4 := NULL;@H_502_5@if exists(select 1 from public.t_user where passport = $1) then@H_502_5@ begin @H_502_5@ $4:=-1;@H_502_5@ end;@H_502_5@ else@H_502_5@ begin@H_502_5@ $3 := cast ((round((random()+1) * 9999999)) as varchar(20)); @H_502_5@ tmpstr := md5($3);@H_502_5@ insert into public.t_user (passport,password,nickname) values($1,tmpstr,$2);@H_502_5@ $4:=(select userid from public.t_user where passport = $1);@H_502_5@ end;@H_502_5@ end if;@H_502_5@ EXCEPTION@H_502_5@ WHEN others THEN@H_502_5@ $4:=-2;@H_502_5@ $3:=-2;@H_502_5@en d;@H_502_5@$body$@H_502_5@LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

以上示例代码，模拟sqlServer，所以故意加入了两个In参数和两OUT参数。

下面是在.net下的调用代码，就跟sql中的存储过程调用一样。ODBC方式貌似不行。

view plaincopy to clipboardprint?public void UserRegiste(T_User user) { NpgsqlConnection conn = new NpgsqlConnection(); conn.ConnectionString = "Server=127.0.0.1;Port=5432;Userid=postgres;database=jtnet;password=123456;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=60;SslMode=Disable"; NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "userreg"; NpgsqlParameter p1 = new NpgsqlParameter("p_passport",DbType.String,50); NpgsqlParameter p2 = new NpgsqlParameter("p_nickname",50); NpgsqlParameter p3 = new NpgsqlParameter("p_nickname",DbType.Int32); NpgsqlParameter p4 = new NpgsqlParameter("p_userid",DbType.Int32); p1.Value = user.Passport; p2.Value = user.UserNickName; p3.Direction = ParameterDirection.Output; p4.Direction = ParameterDirection.Output; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); conn.Open(); NpgsqlDataReader dr = cmd.ExecuteReader(); dr.Read(); //此处可以写上显示代码 ｝ public void UserRegiste(T_User user) { NpgsqlConnection conn = new NpgsqlConnection(); conn.ConnectionString = "Server=127.0.0.1;Port=5432;Userid=postgres;database=jtnet;password=123456;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=60;SslMode=Disable"; NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "userreg"; NpgsqlParameter p1 = new NpgsqlParameter("p_passport",50); NpgsqlParameter p2 = new NpgsqlParameter("p_nickname",50); NpgsqlParameter p3 = new NpgsqlParameter("p_nickname",DbType.Int32); NpgsqlParameter p4 = new NpgsqlParameter("p_userid",DbType.Int32); p1.Value = user.Passport; p2.Value = user.UserNickName; p3.Direction = ParameterDirection.Output; p4.Direction = ParameterDirection.Output; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); conn.Open(); NpgsqlDataReader dr = cmd.ExecuteReader(); dr.Read(); //此处可以写上显示代码｝