调用存储过程【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;