调用存储过程【Delphi版】

A)返回结果为参数

var
Cn:TAdoConnection;
Sp:TAdoStoredProc;
recordCount: string;
begin
Cn := TAdoConnection.Create( nil);
try
Cn.ConnectionString := [数据库连接字符串];
Cn.LoginPrompt := False;
Cn.KeepConnection := True;
Cn.ConnectionTimeout:= 2;
try
Cn.Open;
Sp := TAdoStoredProc.Create( nil);
try
Sp.Connection := Cn;
Sp.Close;
Sp.ProcedureName := ' getStudentAmount ';
Sp.Parameters.Refresh;//参数的初始化要放在refresh之后
Sp.Parameters.ParamByName( ' @recordCount ').Value:= 0;//存储过程中每一个参数都要
Sp.ExecProc; //初始化，包括“返回参数”
recordCount:= inttostr(Sp.Parameters.ParamByName( ' @recordCount ').Value);
finally
Sp.Free;
end;
except
on E:EoleException do
begin
ShowMessage( ' 数据库连接失败,请检查连接 ');
end;
end;
finally
Cn.Free;
end;
end;

B)返回结果为数据集

1)单个数据集

var
  Cn:TAdoConnection;
  Sp:TAdoStoredProc;
  nameFieldString:string;
begin
  Cn := TAdoConnection.Create(nil);
  Sp := TAdoStoredProc.Create(nil);
  try
    Cn.ConnectionString := [数据库连接字符串];
    Cn.LoginPrompt := False;
    Cn.KeepConnection := True;
    Cn.ConnectionTimeout:=2;
    try
      Cn.Open;
      Sp.Connection := Cn;
      Sp.Close;
      Sp.ProcedureName :='queryAllStudentInfo';
      Sp.Parameters.Refresh;
      Sp.Active:=True;//当返回结果是数据集时，一定要激活,但是“有参数时”必须放在参数初始化之后
      Sp.open;//返回的是参数时只能用ExecProc,返回的是数据集时用Open,返回的既有参数又有数据集合也用Open
      whilenot Sp.Eof do
      begin
        nameFieldString:=Sp.FieldByName('SName').AsString;
        nameFieldString:=Trim(nameFieldString);
        Sp.Next;
      end;
    except
      on E:EoleException do
      begin
        ShowMessage('数据库连接失败,请检查连接');
      end;
    end;
  finally
    Sp.Free;
    Cn.Free;
  end;
end

2)多个数据集

var
      Cn:TAdoConnection;
      Sp:TAdoStoredProc;
      AdoQuery:TAdoQuery;
      nameFieldString:string;

      aintf:_Recordset;
      RecordsAffected:OleVariant;
begin
    Cn := TAdoConnection.Create(nil);
    try
       Cn.ConnectionString :=MainForm.DatabaseConnStr;
       Cn.LoginPrompt := False;
       Cn.KeepConnection := True;
       Cn.ConnectionTimeout:=2;
       try
          Cn.Open;
          Sp := TAdoStoredProc.Create(nil);
          AdoQuery:=TAdoQuery.Create(nil);
          try
            Sp.Connection := Cn;
            Sp.Close;
            Sp.ProcedureName :='CIB_GetCheckAccountFileInfo';
            Sp.Active:=True;//当返回结果是数据集时，一定要激活
            Sp.Parameters.Refresh;
            Sp.Active:=True;//当返回结果是数据集时，一定要激活,但是“有参数时”必须放在参数初始化之后

            Sp.open;//返回的是参数时只能用ExecProc,返回的是数据集时用Open,返回的既有参数又有数据集合也用Open

            aintf:=Sp.Recordset;
            AdoQuery.Recordset:=aintf;
            whilenot AdoQuery.Eof do
            begin
               nameFieldString:=AdoQuery.FieldByName('市场应用代码').AsString;
               nameFieldString:=Trim(nameFieldString);
               showmessage('市场应用代码为：'+nameFieldString);
               AdoQuery.Next;
            end;

            aintf:=aintf.NextRecordset(RecordsAffected);
            AdoQuery.Recordset:=aintf;
            whilenot AdoQuery.EOF do
            begin
              nameFieldString:=AdoQuery.FieldByName('市场帐号').AsString;
              nameFieldString:=Trim(nameFieldString);
              showmessage('市场帐号为：'+nameFieldString);
              AdoQuery.Next;
            end;
          finally
            AdoQuery.Free;
            Sp.Free;
          end;
       except
          on   E:EoleException   do
          begin
            ShowMessage('数据库连接失败,请检查连接');
          end;
       end;
    finally
      Cn.Free;
    end;
end;