oracle中有一亿多行数据的表，要全表更新某字段值，有什么号的方法？

你可以写一个存储过程来执行，由于数据量大，如果直接更新，数据库UNDO表空间肯定会满，会产生异常。

过程如下，给你参考下。

CREATE OR REPLACE PROCEDURE UPDATE_TABLES

AS

TYPE T_MW IS TABLE OF ROWID

T_T_MW T_MW

CURSOR V_CUR IS

SELECT ROWID FROM T

BEGIN

OPEN V_CUR

LOOP

FETCH V_CUR BULK COLLECT

INTO T_T_MW LIMIT 80000

FOR C1 IN 1 .. T_T_MW.COUNT LOOP

UPDATE T NOLOGGING

SET 要改的列

WHERE ROWID=T_T_MW(C1)

END LOOP

COMMIT

EXIT WHEN V_CUR%NOTFOUND

END LOOP

CLOSE V_CUR

COMMIT

END

该过程可一次提交80000行。你也可以根据需求改。

你的创建一个数据库连接对象，如adodb库中command对象、recordset对象

使用command对象，必须要adodb.connection对象,详细语法查adodb帮助

可以使用

command.execute "update score set scores=scores+" &text2.text &" where number=" &text1.text

完成更新

附示例：

Public Sub ExecuteX()

Dim strSQLChange As String

Dim strSQLRestore As String

Dim strCnn As String

Dim cnn1 As ADODB.Connection

Dim cmdChange As ADODB.Command

Dim rstTitles As ADODB.Recordset

Dim errLoop As ADODB.Error

' Define two SQL statements to execute as command text.

strSQLChange = "UPDATE Titles SET Type = " &_

"'self_help' WHERE Type = 'psychology'"

strSQLRestore = "UPDATE Titles SET Type = " &_

"'psychology' WHERE Type = 'self_help'"

' Open connection.

strCnn = "Provider=sqloledb" &_

"Data Source=srvInitial Catalog=PubsUser Id=saPassword="

Set cnn1 = New ADODB.Connection

cnn1.Open strCnn

' Create command object.

Set cmdChange = New ADODB.Command

Set cmdChange.ActiveConnection = cnn1

cmdChange.CommandText = strSQLChange

' Open titles table.

Set rstTitles = New ADODB.Recordset

rstTitles.Open "titles", cnn1, , , adCmdTable

' Print report of original data.

Debug.Print _

"Data in Titles table before executing the query"

PrintOutput rstTitles

' Clear extraneous errors from the Errors collection.

cnn1.Errors.Clear

' Call the ExecuteCommand subroutine to execute cmdChange command.

ExecuteCommand cmdChange, rstTitles

' Print report of new data.

Debug.Print _

"Data in Titles table after executing the query"

PrintOutput rstTitles

' Use the Connection object's execute method to

' execute SQL statement to restore data. Trap for

' errors, checking the Errors collection if necessary.

On Error GoTo Err_Execute

cnn1.Execute strSQLRestore, , adExecuteNoRecords

On Error GoTo 0

' Retrieve the current data by requerying the recordset.

rstTitles.Requery

' Print report of restored data.

Debug.Print "Data after executing the query " &_

"to restore the original information"

PrintOutput rstTitles

rstTitles.Close

cnn1.Close

Exit Sub

Err_Execute:

' Notify user of any errors that result from

' executing the query.

If rstTitles.ActiveConnection.Errors.Count >= 0 Then

For Each errLoop In rstTitles.ActiveConnection.Errors

MsgBox "Error number: " &errLoop.Number &vbCr &_

errLoop.Description

Next errLoop

End If

Resume Next

End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _

rstTemp As ADODB.Recordset)

Dim errLoop As Error

' Run the specified Command object. Trap for

' errors, checking the Errors collection if necessary.

On Error GoTo Err_Execute

cmdTemp.Execute

On Error GoTo 0

' Retrieve the current data by requerying the recordset.

rstTemp.Requery

Exit Sub

Err_Execute:

' Notify user of any errors that result from

' executing the query.

If rstTemp.ActiveConnection.Errors.Count >0 Then

For Each errLoop In Errors

MsgBox "Error number: " &errLoop.Number &vbCr &_

errLoop.Description

Next errLoop

End If

Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

' Enumerate Recordset.

Do While Not rstTemp.EOF

Debug.Print " " &rstTemp!Title &_

", " &rstTemp!Type

rstTemp.MoveNext

Loop

End Sub

不能直接用，因为数据库环境不同，稍微修改下就可以了，不过之前你最好看下ADODB手册，网上有。

是工程->引用中 Microsoft ActiveX Data Objects x.x Library

也可以使用工具栏中->部件中 Microsoft ADO DataControl x.x(OLEDB)

他们区别在于一个是控件，一个是函数库，若是熟悉点的人都用Library，添加DataControl时VB会自动添加Library，DataControl实际还是通过Library处理的。你要是不熟悉就用DataControl吧，有图形界面你可能容易上手些。QQ群2832109里有ADO帮助。

你的代码中conn.Open "Provider=SQLOLEDB.1Persist Security Info=FalseUser ID= sapassword=Initial Catalog=publicData Source=."

“Data Source” 要指定数据源

cnn.Execute 也可以执行命令 可你的命令只是个查询是不返回结果的。

可通过以下方法：

以100条数据为例，如果海量数据可参考。

如test表中有如下数据：

现要将begintime改成当前时间，每10条提交一次。

可用如下存储过程：

declare 

i int--定义变量

v_count int--定义变量

v_loop int--定义变量

begin

 select count(*) into v_count from test--计算表内数据总数

 select ceil(v_count/10) into v_loop from dual--计算需要循环次数

 i:=1--为i赋值

 while i<=v_loop loop--循环退出条件

   update test set begintime=sysdate where begintime is null and rownum<=10--执行更新

   commit--提交

   i:=i+1--i依次加1

 end loop--结束循环

end

---