如何在使用列时修改列数据类型

Question

使用SELECT查询时出现问题。不幸的是，ID被存储为VARCHAR，这是一个很大的错误，现在我在以下函数中遇到了问题

FUNCTION GET_SUB_PROJECTS(p_currentUserId IN VARCHAR,p_projectId IN INT)
RETURN SYS_REFCURSOR IS 
rc  SYS_REFCURSOR;
-- getSubProject
BEGIN
OPEN rc FOR
SELECT
p.*,
a.number_ AS activityNumber,
a.description AS activityDescription
FROM
projects p
LEFT JOIN
project_users_schedule_dates pusd
ON
pusd.ProjectID = p.ProjectID AND pusd.UserID = p_currentUserId
LEFT JOIN
activities a
ON
a.id = p.activity
LEFT JOIN 
responsible_persons rp
ON 
rp.ProjectID = p.ProjectID AND rp.UserID = p_currentUserId
LEFT JOIN 
users u
ON 
u.UserID = p_currentUserId
WHERE
(u.User_roleID = 1 AND
p.CustomName LIKE CONCAT((SELECT CustomName FROM projects pr WHERE pr.ProjectID = p_projectId), '%') AND p.ProjectID <> p_projectId)
OR 
((
(p.Responsible_person_id = p_currentUserId OR p.Delivery_contact = p_currentUserId OR rp.UserID = p_currentUserId OR (pusd.UserID = p_currentUserId AND SYSTIMESTAMP BETWEEN TO_DATE(pusd.StartDate,'YYYY-MM-DD') AND TO_DATE(pusd.EndDate,'YYYY-MM-DD') + INTERVAL '1' DAY AND
SYSTIMESTAMP BETWEEN TO_DATE(p.StartDate,'YYYY-MM-DD') AND TO_DATE(p.EndDate,'YYYY-MM-DD') + INTERVAL '1' DAY))
)
AND
p.CustomName LIKE CONCAT((SELECT CustomName FROM projects pr WHERE pr.ProjectID = p_projectId), '%') AND p.ProjectID <> p_projectId)
ORDER BY p.CustomName;      
RETURN rc;
END GET_SUB_PROJECTS;

当我调用函数时，它需要返回数据，但它没有。不知怎的，这里p.Responsible_person_id和p.Delivery_contact需要是NUMBER，但不知怎的是VARCHAR当我调用函数时，我使用

SELECT PROJECT_PACKAGE.GET_SUB_PROJECTS('199',141) FROM DUAL

我找到了一个解决方案来修改，但它像一样给我带来了错误

Error report -
ORA-01439: column to be modified must be empty to change datatype
01439. 00000 -  "column to be modified must be empty to change datatype"

在这种情况下该怎么办？解决这个问题的最佳方法是什么？

Answer 1

您可以使用dbms_redefinition在线更改列的数据类型

create table t (
c1 varchar2(10)
primary key
);
create table t_new (
c1 number(10, 0)
primary key
);
insert into t values ( '1.0000' );
commit;
begin 
dbms_redefinition.start_redef_table ( 
user, 't', 't_new', 
col_mapping => 'to_number ( c1 ) c1', 
options_flag => dbms_redefinition.cons_use_rowid 
);
end;
/
exec dbms_redefinition.sync_interim_table ( user, 't', 't_new' );
exec dbms_redefinition.finish_redef_table ( user, 't', 't_new' );
desc t
Name   Null?      Type         
C1     NOT NULL   NUMBER(10)
select * from t;
C1   
1 

还有一些选项可以在此过程中自动复制约束、触发器、索引等。