我正在嘗試執行以下代碼來更新表，但無法在變數中獲得count(*)結果。CNT
請問如何在更新之前獲取表中的記錄數？
我得到執行以下代碼的錯誤：
Error report - ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 12 06502. 00000 - "PL/SQL: numeric or value error%s"
DECLARE
tname varchar(255);
sql1 VARCHAR2(2000);
CNT INTEGER;
CURSOR myCursor IS select table_name from user_tables where table_name like '%VTS';
BEGIN
OPEN myCursor;
LOOP
FETCH myCursor INTO tname;
EXIT WHEN myCursor%NOTFOUND;
BEGIN
CNT:= 'SELECT COUNT(*) FROM ' || tname || ' where rownum=1';
EXECUTE IMMEDIATE 'CNT';
DBMS_OUTPUT.put_line( 'Number of rows = : ' || CNT);
IF ( CNT ) > 0 THEN
SELECT column_name INTO sql1 FROM user_tab_cols WHERE table_name = tname AND table_name not in (select view_name from user_views) and data_type ='VARCHAR2' ;
sql1 := 'UPDATE ' || tname || ' SET '|| sql1 || '=''hello''';
EXECUTE IMMEDIATE sql1;
END IF;
END;
END LOOP;
CLOSE myCursor;
END;
uj5u.com熱心網友回復：
您需要execute immediatewith into 子句。以下是調整后的程式：
DECLARE
tname varchar(255);
sql1 VARCHAR2(2000);
sql2 VARCHAR2(1000);
CNT NUMBER;
CURSOR myCursor IS select table_name from user_tables where table_name like '%VTS';
BEGIN
OPEN myCursor;
LOOP
FETCH myCursor INTO tname;
EXIT WHEN myCursor%NOTFOUND;
BEGIN
sql2 := 'SELECT COUNT(*) FROM ' || tname;
EXECUTE IMMEDIATE sql2 INTO CNT;
DBMS_OUTPUT.put_line( 'Number of rows = : ' || CNT);
IF ( CNT ) > 0 THEN
SELECT column_name
INTO sql1
FROM user_tab_cols
WHERE table_name = tname
AND table_name not in (select view_name from user_views)
AND data_type = 'VARCHAR2';
sql1 := 'UPDATE ' || tname || ' SET '|| sql1 || '=''hello''';
DBMS_OUTPUT.put_line( 'sql');
END IF;
END;
END LOOP;
CLOSE myCursor;
END;
補充說明：
- 當您只選擇 COUNT(*) 時，您不需要 rownum = 1。
- 您需要更好地命名變數。
uj5u.com熱心網友回復：
動態計數邏輯可以更簡潔地撰寫為
declare
tname varchar(255);
count_sql varchar2(500);
begin
for r in (
select table_name, num_rows from user_tables -- where table_name like '%VTS'
)
loop
count_sql := 'select count(*) from ' || r.table_name || ' where rownum = 1';
execute immediate count_sql into r.num_rows;
dbms_output.put_line('Number of rows in '||r.table_name||' = '||r.num_rows);
end loop;
end;
在 Oracle HR 示例模式中，輸出為
Number of rows in REGIONS = 1
Number of rows in COUNTRIES = 1
Number of rows in LOCATIONS = 1
Number of rows in DEPARTMENTS = 1
Number of rows in JOBS = 1
Number of rows in EMPLOYEES = 1
Number of rows in JOB_HISTORY = 1
更新每一varchar2列的部分更復雜，因為如果有多個 varchar2 列，則不能只為每個表獲取 varchar2 列。例如，假設表locations有 street_address、postal_code和。您可以遍歷 user_tab_columns 中的每一行并生成四個陳述句citystate_province
update locations set street_address = 'hello'
update locations set postal_code = 'hello'
update locations set city = 'hello'
update locations set state_province = 'hello'
但是當你真正想要的是
update locations
set street_address = 'hello'
, postal_code = 'hello'
, city = 'hello'
, state_province = 'hello'
你當然可以生成這個，但它更復雜。也許是這樣的（我已經替換execute immediate為，dbms_output.put_line所以我可以預覽生成的 SQL 而不執行它）：
declare
tname varchar(255);
count_sql varchar2(500);
update_sql varchar2(500);
l_separator varchar2(2);
begin
for t in (
select table_name, num_rows from user_tables -- where table_name like '%VTS'
)
loop
count_sql := 'select count(*) from ' || t.table_name || ' where rownum = 1';
execute immediate count_sql into t.num_rows;
dbms_output.put_line('Number of rows in '||t.table_name||' = '||t.num_rows);
if t.num_rows > 0 then
update_sql := 'update '||t.table_name||' set ';
l_separator := '';
for c in (
select column_name
from user_tab_columns
where table_name = t.table_name
and table_name not in (select view_name from user_views)
and data_type = 'VARCHAR2'
and data_length > 4
)
loop
update_sql := update_sql || l_separator || c.column_name||' = ''hello''';
l_separator := ', ';
end loop;
--execute immediate update_sql;
dbms_output.put_line(update_sql);
end if;
end loop;
end;
這給了我：
Number of rows in REGIONS = 1
update REGIONS set REGION_NAME = 'hello'
Number of rows in COUNTRIES = 1
update COUNTRIES set COUNTRY_NAME = 'hello'
Number of rows in LOCATIONS = 1
update LOCATIONS set STREET_ADDRESS = 'hello', POSTAL_CODE = 'hello', CITY = 'hello', STATE_PROVINCE = 'hello'
Number of rows in DEPARTMENTS = 1
update DEPARTMENTS set DEPARTMENT_NAME = 'hello'
Number of rows in JOBS = 1
update JOBS set JOB_ID = 'hello', JOB_TITLE = 'hello'
Number of rows in EMPLOYEES = 1
update EMPLOYEES set FIRST_NAME = 'hello', LAST_NAME = 'hello', EMAIL = 'hello', PHONE_NUMBER = 'hello', JOB_ID = 'hello'
Number of rows in JOB_HISTORY = 1
update JOB_HISTORY set JOB_ID = 'hello'
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/478827.html