Oracle存储过程---修改字段

– 修改表字段 参数：表名、字段名、字段类型
create or replace procedure TRYMODIFYTABCOLUMN(tabName in varchar2,
colName in varchar2,
colType in varchar2) is
n_col int;
stmt VARCHAR(2000);
begin
select count(*)
into n_col
from cols
where table_name = upper(tabName)
and column_name = upper(colName);
if n_col = 1 then
stmt := 'alter table ’ || tabName || ’ modify ’ || colName || ’ ’ ||
colType;
execute immediate stmt;
end if;
end;

–修改表字段不能为空 参数：表名 字段名
CREATE OR REPLACE PROCEDURE TRYMODIFYTABCOLUMNNOTNULL(pTab IN VARCHAR2, pCol IN VARCHAR2) is
begin
FOR ITEM IN (SELECT COUNT(*) NUM FROM user_tab_columns WHERE TABLE_NAME = UPPER(pTab) AND COLUMN_NAME = UPPER(pCol) AND NULLABLE = ‘Y’) LOOP
IF ITEM.NUM = 1 THEN
EXECUTE IMMEDIATE('ALTER TABLE ‘|| UPPER(pTab) || ’ MODIFY ’ || UPPER(pCol) || ’ NOT NULL’);
END IF;
END LOOP;
end TRYMODIFYTABCOLUMNNOTNULL;

– 修改字段可以为空 参数：表名 字段名
CREATE OR REPLACE PROCEDURE TRYMODIFYTABCOLUMNNULL(pTab IN VARCHAR2, pCol IN VARCHAR2) is
begin
FOR ITEM IN (SELECT COUNT(*) NUM FROM user_tab_columns WHERE TABLE_NAME = UPPER(pTab) AND COLUMN_NAME = UPPER(pCol) AND NULLABLE = ‘N’) LOOP
IF ITEM.NUM = 1 THEN
EXECUTE IMMEDIATE('ALTER TABLE ‘|| UPPER(pTab) || ’ MODIFY ’ || UPPER(pCol) || ’ NULL’);
END IF;
END LOOP;
end TRYMODIFYTABCOLUMNNULL;

– 修改字段可以名称 参数：表名 原字段名 修改后字段名称
create or replace procedure TRYRENAMETABCOLUMN(tabName in varchar2,
oldColName in varchar2,
newColName in varchar2) is
n_col int;
stmt VARCHAR(2000);
begin
select count(*)
into n_col
from cols
where table_name = upper(tabName)
and column_name = upper(oldColName);
if n_col = 1 then
stmt := 'alter table ’ || tabName || ’ rename column ’ || oldColName || ’ to ’ ||
newColName;
execute immediate stmt;
end if;
end;