设置动态修改，删除，插入的odac

来源：互联网发布：python 微信上传图片编辑：程序博客网时间：2024/04/29 16:12

//获得表的主键

function TComDBConn.GetTableKey(TableName: string; owner: string = ''): string;
var
    Sqlstr                              : WideString;
begin
    if (owner = '') then
    begin
        Sqlstr := 'select   nvl(max(Column_name),''ylok999'')   from   user_cons_columns ' +
            'where constraint_name = (select constraint_name from user_constraints ' +
            'where table_name = :1 and constraint_type = ''P'')';
        Result := self.ExeSqlResult(Sqlstr, [TableName]);
    end
    else

    begin
        Sqlstr := 'select   nvl(max(Column_name),''ylok999'')   from   all_cons_columns ' +
            'where constraint_name = (select constraint_name from user_constraints ' +
            'where table_name = :1 and owner=:2 and constraint_type = ''P'')';
        Result := self.ExeSqlResult(Sqlstr, [TableName, owner])
    end;
    if Result='ylok999' then
    begin
       Raise exception.CreateFmt('表%s没有定义主键',[TableName]);
    end;
end;

procedure GetOwnertable(var TableName: string; var owner: string);
var
    P                                   : Integer;
begin
    P := pos('.', TableName);
    if (P < 1) then
    begin
        owner := uppercase(LandUserName);
        Exit;
    end;
    owner := copy(TableName, 1, P - 1);
    TableName := copy(TableName, P + 1, 100);

end;
//获得表的字段

procedure TComDBConn.GetFieldList(TableName: string; List: TstringList; owner:
    string = '');
var
    Sqlstr                              : string;
    ADOQ                                : TOraQuery;
begin
    ADOQ := TOraQuery.Create(nil);

    try
        if (owner = '') then
        begin
            Sqlstr :=
                'select COLUMN_NAME from user_tab_columns c where c.TABLE_NAME =:1';

            OpenRecordBySql(Sqlstr, ADOQ, [TableName]);
        end
        else
        begin
            Sqlstr :=
                'select COLUMN_NAME from all_tab_columns c where c.TABLE_NAME =:1 and owner=:2';
            OpenRecordBySql(Sqlstr, ADOQ, [TableName, owner]);

end;

GetList(ADOQ, List);

    finally
        ADOQ.Free;
    end;

end;

//设置update

procedure TComDBConn.SetUpdate(ADOQ: TOraQuery; TableName: string;
    IsDel: boolean = True; Isinsert: boolean = True; isupdate: boolean = True);
var
    PIDFieldName                        : string;
    owner                               : string;
    List                                : TstringList;

    function DelSql(): string;
    var
        Sqlstr                          : string;
    begin
        Sqlstr := 'DELETE FROM ' + TableName + ' WHERE ACTIVITYID = :' +
            PIDFieldName;
        Result := Sqlstr;

    end;
    function InsertSql(): string;
    var
        Sqlstr                          : string;
        Field, Value                    : string;
        i                               : Integer;
    begin
        Field := '';
        for i := 0 to List.Count - 1 do
        begin
            Field := Field + List[i];
            Value := Value + ':' + List[i];
            if (i < List.Count - 1) then
            begin
                Field := Field + ',';
                Value := Value + ',';
            end;

        end;
        Sqlstr := 'INSERT INTO ' + TableName + '(' + Field + ') VALUES (' + Value
            + ')';
        Result := Sqlstr;

    end;
    function updateSql(): string;
    var
        Sqlstr                          : string;
        Field                           : string;
        i                               : Integer;
    begin
        Field := '';
        for i := 0 to List.Count - 1 do
        begin
            Field := Field + List[i] + '=:' + List[i];

            if (i < List.Count - 1) then
            begin
                Field := Field + ',';

end;

        end;
        Sqlstr := 'UPDATE ' + TableName + ' set ' + Field + ' where ' +
            PIDFieldName + '=:OLD_' + PIDFieldName;

Result := Sqlstr;

end;
begin

    TableName := uppercase(TableName);
    GetOwnertable(TableName, owner);
    PIDFieldName := GetTableKey(TableName, owner);
    List := TstringList.Create();
    try
        GetFieldList(TableName, List, owner);
        ADOQ.UpdateObject := self.FOraUpdateSQL;
        if IsDel then
            FOraUpdateSQL.DeleteSQL.Text := DelSql();

        if Isinsert then
            FOraUpdateSQL.InsertSql.Text := InsertSql();
        if isupdate then
            FOraUpdateSQL.ModifySQL.Text := updateSql();

    finally
        List.Free;
    end;
end;

注意：不能多个TOraQuery;，公用一个OraUpdateSQL;