原表: SQL> select * from test; ID COUNT ---------- ---------- 1 2 1 3 2 5 2 8 1 9 2 10 6 rows selected创建动态过程: create or replace procedure Pro_Test(col_name varchar2,col_type varchar2) is str varchar2(1000); begin str:='alter table test add ' || col_name || ' ' || col_type; execute immediate str; end pro_test;调用过程: begin pro_test('age','number'); end; 添加列(age number) 查看: SQL> select * from test; ID COUNT AGE ---------- ---------- ---------- 1 2 1 3 2 5 2 8 1 9 2 10 6 rows selected 6 rows selected查看表结构: SQL> desc test; Name Type Nullable Default Comments ----- ------ -------- ------- -------- ID NUMBER Y COUNT NUMBER Y AGE NUMBER Y
假如增加"体重"字段. create procedure pro as str varchar2(100); begin str:='alter table tabname add (体重 varchar2(10))'; execute immediate str; exception --若存在就跳动例外事件 when others then null; end; /
SQL> select * from test; ID COUNT
---------- ----------
1 2
1 3
2 5
2 8
1 9
2 10 6 rows selected创建动态过程:
create or replace procedure Pro_Test(col_name varchar2,col_type varchar2) is
str varchar2(1000);
begin
str:='alter table test add ' || col_name || ' ' || col_type;
execute immediate str;
end pro_test;调用过程:
begin
pro_test('age','number');
end;
添加列(age number)
查看:
SQL> select * from test; ID COUNT AGE
---------- ---------- ----------
1 2
1 3
2 5
2 8
1 9
2 10 6 rows selected
6 rows selected查看表结构:
SQL> desc test;
Name Type Nullable Default Comments
----- ------ -------- ------- --------
ID NUMBER Y
COUNT NUMBER Y
AGE NUMBER Y
create procedure pro
as
str varchar2(100);
begin
str:='alter table tabname add (体重 varchar2(10))';
execute immediate str;
exception --若存在就跳动例外事件
when others then
null;
end;
/