存储过程实现创建修改表及其数据

1、创建表

--创建表 emp6

方法一：

create or replace procedure ProCreateTable(var_tableName in varchar2) is
  var_count int:=0;
begin
  select count(*) into var_count from user_tables where upper(table_name)=var_tableName;--all_all_tables
  if var_count>0 then
 dbms_output.put_line('Table '||var_tableName||' is already exist.');
 execute immediate 'drop table '||var_tableName||'';
  end if; 
end ProCreateTable;
/

execute ProCreateTable('EMP5');
drop procedure ProCreateTable;
create table EMP5
(
  empno    NUMBER(6) not null,
  ename    VARCHAR2(20),
  job      VARCHAR2(20),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(8,2),
  comm     NUMBER(8,2),
  deptno   NUMBER(2)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

方法二;

create or replace procedure ProCreateBable(var_tableName in varchar2) is
  var_count int:=0;
begin
  select count(*) into var_count from  user_tables where upper(table_name)=var_tableName;
  if var_count>0 then
    --dbms_output.put_line('Table '||var_tableName||' is aready exit.');
 execute immediate 'drop table '||var_tableName||'';
  end if;
  --dbms_output.put_line('Begin to add table '||var_tableName||'.');
  execute immediate 'create table '||var_tableName||' as (select * from emp)';
end ProCreateBable;
/

execute ProCreateBable('EMP4');
drop procedure ProCreateBable;

2、新增主键

--表 emp6 新增主键 ename

--alter table emp5 drop constraint PK_EMP5;
--alter table emp5 add constraint PK_EMP5 primary key (EMPNO);
create or replace procedure ProAddPrimaryKey(
  var_tableName in varchar2,
  var_primaryKeyName in varchar2,
  var_column in varchar2
) is
var_count_table int:=0;
var_count_index int:=0;
begin
  select count(*) into var_count_table from user_tables where table_name=var_tableName;
  if var_count_table>0 then
 select count(*) into var_count_index from user_indexes where table_name=var_tableName and index_name=var_primaryKeyName;
 if var_count_index>0 then
   dbms_output.put_line('Begin to drop Primary Key.');
   execute immediate 'alter table '||var_tableName||' drop constraint '||var_primaryKeyName||'';
 end if;
 dbms_output.put_line('Begin to add Primary Key.');
 execute immediate 'alter table '||var_tableName||' add constraint '||var_primaryKeyName||' primary key ('||var_column||')';
  end if;
end;
/
 
execute ProAddPrimaryKey('EMP5','PK_EMP5','EMPNO');
drop procedure ProAddPrimaryKey;

3、新增外键

--表emp6新增外键 deptno

--select * from user_cons_columns where table_name='EMP5' and constraint_name='FK_DEPTNO5';
--alter table emp5 drop constraint PK_EMP5;
--alter table emp5 add constraint PK_EMP5 foreign key (EMPNO) references DEPT (DEPTNO);
create or replace procedure ProAddForeignKey(
  var_tableName in varchar2,
  var_ForeignKeyName in varchar2,
  var_column in varchar2
) is
var_count_table int:=0;
var_count_index int:=0;
begin
  select count(*) into var_count_table from user_tables where table_name=var_tableName;
  if var_count_table>0 then
 select count(*) into var_count_index from user_cons_columns where table_name=var_tableName and constraint_name=var_ForeignKeyName;
 --dbms_output.put_line(var_count_index);
 if var_count_index>0 then
   --dbms_output.put_line('Drop exist Forign Key.');
   execute immediate 'alter table '||var_tableName||' drop constraint '||var_ForeignKeyName||'';
 end if;
 --dbms_output.put_line('Add Forign Key.');
 execute immediate 'alter table '||var_tableName||' add constraint '||var_ForeignKeyName||' foreign key ('||var_column||') references DEPT (DEPTNO)';
  end if;
end;
/
 
execute ProAddForeignKey('EMP5','FK_DEPTNO5','DEPTNO');
drop procedure ProAddForeignKey;

4、新增索引

--表emp6新增索引（使用ename列）

--Add Index
--select * from user_indexes t where t.TABLE_NAME = upper('EMP5') and t.INDEX_NAME=UPPER('INDEX_ENAME5');
--select * from user_constraints t where upper(t.TABLE_NAME)='EMP5' and upper(t.INDEX_NAME)='INDEX_EAME5'
--select * from user_tables where table_name ='EMP5';
--drop index index_ename5;
--create index index_ename5 on emp5 (ename);
set serveroutput on
create or replace procedure ProAddIndex(var_tableName in varchar2,var_indexName in varchar2,var_column in varchar2) is
  var_tableCount int:=0;
  var_indexCount int:=0;
begin
  --dbms_output.put_line('Begin to add index');
  select count(*) into var_tableCount from user_tables where table_name = upper(var_tableName);
  if var_tableCount>0 then
    select count(*) into var_indexCount from user_indexes where table_name = upper(var_tableName) and INDEX_NAME=upper(var_indexName);
 if var_indexCount>0 then
   dbms_output.put_line('drop index '||var_indexName||' at first.');
   execute immediate 'drop index '||var_indexName||'';
 end if;
   execute immediate 'create index '||var_indexName||' on  '||var_tableName||' ('||var_column||')';
  else
    dbms_output.put_line('Table '||var_tableName||' is not exist.');
  end if;
end ProAddIndex;
/

execute ProAddIndex('emp66','index_ename5','ename');
drop procedure  ProAddIndex;

5、新增列

--表emp6新增列A1

set serveroutput on

create or replace procedure ProAddColumn(var_tableName varchar2,var_addColumnName varchar2) is
var_countTable int:=0;
var_countColumn int:=0;
begin
  select count(*) into var_countTable from user_tables where table_name=upper(var_tableName);
  if var_countTable>0 then
    select count(*) into var_countColumn from user_tab_columns where table_name=upper(var_tableName) and column_name=upper(var_addColumnName);
    if var_countColumn>0 then
   dbms_output.put_line('drop column '||var_addColumnName||' at first.');
      execute immediate 'alter table '||var_tableName||' drop column '||var_addColumnName||'';
    end if;
    execute immediate 'alter table '||var_tableName||' add ('||var_addColumnName||' varchar2(20))';
  else
    dbms_output.put_line('Table '||var_tableName||'is not exist.');
  end if;
end;
/
execute ProAddColumn('emp6','A1');

4、移动表数据，且ID递增

--move data from emp5 to Emp6

--truncate table emp6;

--将emp5表中的数据移动到emp6表中，且在表emp6原有基础上empno递增
create or replace procedure ProMoveData is
  var_maxEmpno int:=0;
begin
  select max(empno) into var_maxEmpno from emp6;
  if var_maxEmpno is null then
    var_maxEmpno:=5000;
  else
    var_maxEmpno := var_maxEmpno+100;
  end if;
 
  for var_empRecord in (select * from emp5)
  loop
    var_maxEmpno := var_maxEmpno+1;
    insert into emp6 (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(var_maxEmpno, var_empRecord.ename, var_empRecord.job, var_empRecord.mgr,
   var_empRecord.hiredate, var_empRecord.sal, var_empRecord.comm, var_empRecord.deptno);
  end loop;
  commit;
end ProMoveData;
/

execute ProMoveData();