oracle 建表、最大ID、判断表是否已存在、字段的查询以及增删改、插入与修改语句

文件管理功能可以对文件进行分类管理，每一个分类对应一张分类表，并且随着应用的增加可以通过新增扩展表添加分类表。
添加一个分类就是添加一张表，注册到表OA_FILE_CLASS中，
并且需要把新增的分类表的字段信息添加到OA_ATTRIBUTE_REGEDIT表中（ID和FILE_ID除外），
最后根据用户输入的表名和字段信息生成数据库表。
列类型主要有：0-字符、1-数值、2-日期
--------------------------------------------------------------------------------------------------------
--分类表
select CODE     ,  
        NAME      ,
        TABLE_NAME
  from OA_FILE_CLASS --for update
 where 1 = 1
   and CODE       = @CODE       
   and NAME       = @NAME       
   and TABLE_NAME = @TABLE_NAME
order by CODE

--扩展属性注册表                0-字符、1-数值、2-日期
select A.CODE_ID       ,
       A.COLUMN_NAME   ,
       A.COLUMN_CN_NAME,
       A.COLUMN_TYPE
  from OA_ATTRIBUTE_REGEDIT A  --for update
 where A.CODE_ID = @CODE_ID
order by A.CODE_ID

select A.CODE_ID       ,
       A.COLUMN_NAME   ,
       A.COLUMN_CN_NAME,
       A.COLUMN_TYPE   ,
       decode(A.COLUMN_TYPE,0,'字符',1,'数值',2,'日期') COLUMN_TYPE_CN,
       (select b.WIDTH
          from SYS.COL B
         where b.tname = (select upper(c.TABLE_NAME)
                           from OA_FILE_CLASS C
                          where c.code = a.code_id
                        )
           and b.cname = upper(a.column_name)
       ) WIDTH
  from OA_ATTRIBUTE_REGEDIT A
 where A.CODE_ID = @CODE_ID
order by A.CODE_ID

--表名示例
select * from table_ceshi1

select * from sys.col where TNAME= upper('table_ceshi1')
--------------------------------------------------------------------------------------------------------
--查询当前最大code
SELECT nvl(max(CODE),0)+1 ID
  FROM OA_FILE_CLASS

SELECT to_char(nvl(max(to_number(ID)),0)+1,
                             'fm000000000'
                            ) Max_Seq
  FROM FR_DEPARTMENT

SELECT to_char(nvl(max(to_number(ID)),0)+1
                            ) Max_Seq
  FROM FR_DEPARTMENT

select 'YGCP' || 
       to_char(
              (nvl(to_number(substr(max(DRUG_CODE), 5)),0)+1),
              'fm00000000'
       )
  from PHA_GCP_DRUG

--------------------------------------------------------------------------------------------------------
--查找表是否已存在
select 1 from tabs
 where table_name = upper('TABLE_ff')

create table TABLE_ff
(
  ID      VARCHAR2(38) not null,
  FILE_ID VARCHAR2(38) not null
)

select * from table_ff

drop table table_ff

--查找字段信息
select * from sys.col where TNAME= upper('TABLE_FF')

--添加字段
ALTER TABLE table_ff
ADD num1 number(6) null

ALTER TABLE table_ff
ADD num3 date null

--修改字段数据类型
alter table table_ff modify (NUM2 VARCHAR2(1))

--修改字段长度
alter table table_ff modify column(num2 varchar(10))

--修改字段名称
ALTER TABLE table_ff RENAME COLUMN num1 TO num2;

--删除字段
ALTER TABLE table_ff
DROP COLUMN file_id

--删除数据
delete from table_ff
where id = ''

delete table_ff
where id = ''
------------------------------------
delete OA_ATTRIBUTE_REGEDIT
 where CODE_ID = @CODE_ID

insert into OA_ATTRIBUTE_REGEDIT
(
ID,
TPC_CODE,            
NORM_NAME,           
TPC_NAME,            
TPC_TOTAL
)           
values
(
@ID,          
@TPC_CODE,           
@NORM_NAME,          
@TPC_NAME,           
@TPC_TOTAL
)

" insert into OA_ATTRIBUTE_REGEDIT" +
                                " (                        " +
                                " CODE_ID       ,          " +
                                " COLUMN_NAME   ,          " +
                                " COLUMN_CN_NAME,          " +
                                " COLUMN_TYPE              " +
                                " )                        " +
                                " values                   " +
                                " (                        " +
                                " @CODE_ID       ,         " +
                                " @COLUMN_NAME   ,         " +
                                " @COLUMN_CN_NAME,         " +
                                " @COLUMN_TYPE             " +
                                " )                        ";
                                
                                
"update OA_ATTRIBUTE_REGEDIT set  " +
"COLUMN_NAME  =@COLUMN_NAME  ,   " +
"COLUMN_CN_NAME =@COLUMN_CN_NAME ,   " +
"where                     " +
"CODE_ID        =@CODE_ID            ";

---------------------------------------------------------------------------------------------------------------

select CODE
  from OA_FILE_CLASS
 where TABLE_NAME = @TABLE_NAME