1 - 不可能2 - 增加列alter table table_name add(column_name column_type...)
增加约束alter table table_name add constraints constraint_name..
修改列alter table table_name modify(column_name column_type...)
修改数据块空间使用参数alter table table_name pctfree n%,pctused n%3 - The DBMS_SQL package can be used to execute DDL statements directly from PL/SQL.
例 1: 这是一个创建一个表的过程的例子。该过程有两个参数:表名和字段及其类型的列表。CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS
cursor1 INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || ' ( ' || cols || ' )', dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)'); PL/SQL procedure successfully completed. SQL> desc mytable;
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER
COL2 VARCHAR2(10)
注意:DDL语句是由Parese命令执行的。因此,不能对DDL语句使用bind变量,否则你就会受到一个错误信息。下面的在DDL语句中使用bind变量的例子是错误的。 **** Incorrect Example ****CREATE OR REPLACE PROCEDURE ddlproc (tablename VARCHAR2,
colname VARCHAR2,
coltype VARCHAR2) AS
cursor1 INTEGER;
ignore INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE :x1 (:y1 :z1)', dbms_sql.v7);
dbms_sql.bind_variable(cursor1, ':x1', tablename);
dbms_sql.bind_variable(cursor1, ':y1', colname);
dbms_sql.bind_variable(cursor1, ':z1', coltype);
ignore := dbms_sql.execute(cursor1);
dbms_sql.close_cursor(cursor1);
end;
/ 虽然在过程创建时,没有错误信息。但在运行时,你将得到错误信息"ORA-00903: invalid table name" 。 SQL> execute ddlproc ('MYTABLE', 'COL1', 'NUMBER');
begin ddlproc ('MYTABLE', 'COL1', 'NUMBER'); end; *
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "SCOTT.DDLPROC", line 8
ORA-06512: at line 1
例2: 删除表的过程。使用表明作为参数。create or replace procedure droptable (table_name varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'DROP TABLE ' || table_name, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ SQL> begin
2 droptable('MYTABLE');
3 end;
4 / PL/SQL procedure successfully completed.
例 3: 执行任何DDL语句的过程。以DDL语句为参数。create procedure anyddl (s1 varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, s1, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)'); PL/SQL procedure successfully completed. SQL> desc mytable;
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER SQL> execute anyddl('drop table mytable'); PL/SQL procedure successfully completed. 4 - 违反命名规定5 - 函数是能返回一个值的子程序。函数和过程结构相似,只是函数有一个“RETURN”子语句。具体请参考相关规范
增加约束alter table table_name add constraints constraint_name..
修改列alter table table_name modify(column_name column_type...)
修改数据块空间使用参数alter table table_name pctfree n%,pctused n%3 - The DBMS_SQL package can be used to execute DDL statements directly from PL/SQL.
例 1: 这是一个创建一个表的过程的例子。该过程有两个参数:表名和字段及其类型的列表。CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS
cursor1 INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || ' ( ' || cols || ' )', dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)'); PL/SQL procedure successfully completed. SQL> desc mytable;
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER
COL2 VARCHAR2(10)
注意:DDL语句是由Parese命令执行的。因此,不能对DDL语句使用bind变量,否则你就会受到一个错误信息。下面的在DDL语句中使用bind变量的例子是错误的。 **** Incorrect Example ****CREATE OR REPLACE PROCEDURE ddlproc (tablename VARCHAR2,
colname VARCHAR2,
coltype VARCHAR2) AS
cursor1 INTEGER;
ignore INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE :x1 (:y1 :z1)', dbms_sql.v7);
dbms_sql.bind_variable(cursor1, ':x1', tablename);
dbms_sql.bind_variable(cursor1, ':y1', colname);
dbms_sql.bind_variable(cursor1, ':z1', coltype);
ignore := dbms_sql.execute(cursor1);
dbms_sql.close_cursor(cursor1);
end;
/ 虽然在过程创建时,没有错误信息。但在运行时,你将得到错误信息"ORA-00903: invalid table name" 。 SQL> execute ddlproc ('MYTABLE', 'COL1', 'NUMBER');
begin ddlproc ('MYTABLE', 'COL1', 'NUMBER'); end; *
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "SCOTT.DDLPROC", line 8
ORA-06512: at line 1
例2: 删除表的过程。使用表明作为参数。create or replace procedure droptable (table_name varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'DROP TABLE ' || table_name, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ SQL> begin
2 droptable('MYTABLE');
3 end;
4 / PL/SQL procedure successfully completed.
例 3: 执行任何DDL语句的过程。以DDL语句为参数。create procedure anyddl (s1 varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, s1, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)'); PL/SQL procedure successfully completed. SQL> desc mytable;
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER SQL> execute anyddl('drop table mytable'); PL/SQL procedure successfully completed. 4 - 违反命名规定5 - 函数是能返回一个值的子程序。函数和过程结构相似,只是函数有一个“RETURN”子语句。具体请参考相关规范
SQL> desc test_table
Name Null? Type
------------------------------- -------- ----
ID NUMBER(3)
B NUMBER(2)SQL> update test_table set id = null
2 /1 row updated.
SQL> select * from test_table
2 / ID B
--------- ---------
1SQL> alter table test_table modify ( id number(2))
2 /Table altered.
execute immediate 'trucate table test_table';5
is和as一样
和其他的C R E AT E语句一样,创建过程是一种D D L操作,因此,在过程创建前和创建后,都要执行一条隐式的C O M M I T命令。这种操作可以通过使用关键字IS 或A S来实现,这两个关键字是等价的。
答:前提条件是数据必须满足字段长度的定义
alter table 表名 modify(column_name column_type...)
2 - 怎样用modify命令来修改一个表的主键;
答:alter table 表名 drop constraint 主键约束名;
alter table 表名 add constraint 约束名 primary key(字段);