一.SqlServer 中
1. 创建表
create table testuser( id int, --primary key, names varchar(50), address varchar(50), paw varchar(50) )
2.创建存储过程
create proc testp12(@id int ,@names varchar(50),@address varchar(50),@paw varchar(50)) as begin declare @maxid int if @id is null or @id<5 begin return -1; end --set IDENTITY_INSERT testuser on insert into testuser(id,names,address,paw)values(@id,@names,@address,@paw) --set IDENTITY_INSERT testuser off select @maxid=(select max(id) from testuser) return @maxid end
3.c#对应的代码处理
#region"SqlServer中存储过程Return返回值处理" /* public void getresult(int id, string names, string address, string pwd) { string str = " server=192.168.xxx;user id=xx;password=xxxxxxxx;database=xxxxx_xx;min pool size=4;max pool size=4;packet size=3072"; SqlConnection conn = new SqlConnection(str); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "testp12"; cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)); cmd.Parameters["@id"].Value = id; cmd.Parameters.Add(new SqlParameter("@names", SqlDbType.VarChar, 50)); cmd.Parameters["@names"].Value = names; cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50)); cmd.Parameters["@address"].Value = address; cmd.Parameters.Add(new SqlParameter("@paw", SqlDbType.VarChar, 50)); cmd.Parameters["@paw"].Value = pwd; cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int)); cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue; cmd.Connection = conn; cmd.ExecuteNonQuery(); conn.Close(); Response.Write(cmd.Parameters["@return"].Value.ToString()); Response.Write("<br/>"); Response.Write("hello world"); }*/ #endregion
二.MySQL中
1.创建表
create table testuser( id int, names varchar(50), address varchar(50), paw varchar(50) )
2.创建函数
create FUNCTION testp12(_id int , _names varchar(50), _address varchar(50), _paw varchar(50) ) RETURNS INT label_pro: BEGIN declare _maxid int; declare returnid int; if _id is null or _id<5 then begin set returnid=-1; RETURN returnid; leave label_pro; end ; end if; -- set IDENTITY_INSERT testuser on insert into testuser(id,`names`,address,paw)values(_id,_names,_address,_paw); -- set IDENTITY_INSERT testuser off set _maxid=(select max(id) from testuser); set returnid=_maxid; RETURN returnid; end
3.C#代码实现----函数实现
#region"函数实现SqlServer中存储过程功能Return" /// <summary> /// 函数实现SqlServer中存储过程功能 /// </summary> /// <param name="id"></param> /// <param name="names"></param> /// <param name="address"></param> /// <param name="pwd"></param> public void getresult(int id, string names, string address, string pwd) { string str = "Server=192.168.xxxx;Database=xxxx_xxx; port=xxxx;user id=xxxxx;password= providerName=MySql.Data.MySqlClient"; MySqlConnection conn = new MySqlConnection(str); conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "fnetravel2_db.testp12"; cmd.Parameters.Add(new MySqlParameter("_id", MySqlDbType.Int32)); cmd.Parameters["_id"].Value = id; cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50)); cmd.Parameters["_names"].Value = names; cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50)); cmd.Parameters["_address"].Value = address; cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50)); cmd.Parameters["_paw"].Value = pwd; // cmd.Parameters.Add(new MySqlParameter("returnid", MySqlDbType.Int32)); //cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add("returnid", MySqlDbType.Int32, 11); //设置参数的类型为输出参数,默认情况下是输入, cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue; cmd.Connection = conn; cmd.ExecuteNonQuery(); string name = cmd.Parameters["returnid"].Value.ToString(); conn.Close(); Response.Write(name); Response.Write("<br/>"); Response.Write("hello world"); } #endregion
4.存储过程实现
create PROCEDURE testp122(in _id int ,in _names varchar(50),in _address varchar(50),in _paw varchar(50),out returnid int ) label_pro:BEGIN declare _maxid int; -- declare returnid int; if _id is null or _id<5 then begin set returnid=-1; leave label_pro; end ; end if; -- set IDENTITY_INSERT testuser on insert into testuser(id,`names`,address,paw)values(_id,_names,_address,_paw); -- set IDENTITY_INSERT testuser off set _maxid=(select max(id) from testuser); set returnid=_maxid; end
5.C#代码实现----存储过程
#region"mysql存储过程实现SqlServer中存储过程功能Return" /// <summary> /// /// </summary> /// <param name="id"></param> /// <param name="names"></param> /// <param name="address"></param> /// <param name="pwd"></param> public void getresult(int id, string names, string address, string pwd) { string str = "Server=192.168.xxxxxx;Database=xxxxx_dxxxxxxb; port=xxxxx;user id=xxxxx;password= providerName=MySql.Data.MySqlClient"; MySqlConnection conn = new MySqlConnection(str); conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "fnetravel2_db.testp122"; cmd.Parameters.Add(new MySqlParameter("_id", MySqlDbType.Int32)); cmd.Parameters["_id"].Value = id; cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50)); cmd.Parameters["_names"].Value = names; cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50)); cmd.Parameters["_address"].Value = address; cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50)); cmd.Parameters["_paw"].Value = pwd; // cmd.Parameters.Add(new MySqlParameter("returnid", MySqlDbType.Int32)); //cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add("returnid", MySqlDbType.Int32, 11); //设置参数的类型为输出参数,默认情况下是输入, cmd.Parameters["returnid"].Direction = ParameterDirection.Output; cmd.Connection = conn; cmd.ExecuteNonQuery(); string name = cmd.Parameters["returnid"].Value.ToString(); conn.Close(); Response.Write(name); Response.Write("<br/>"); Response.Write("hello world"); } } #endregion
看到上面想到是不是SqlServer中也可以实现这个功能,但是测试发现SqlServer中函数不允许insert语句。但是如果函数返回表则是可以的。如这个函数:
CREATE FUNCTION func(@selection int) RETURNS @table TABLE ( id char(4) primary key not null, names nvarchar(4) null ) AS BEGIN IF @selection = 0 INSERT INTO @table SELECT id,names FROM testuser ELSE INSERT INTO @table SELECT id,names FROM testuser Return END
这些例子都是我实践得到了。
记录一点点,收货一点点。