day12_PLSQL编程--存储过程---统一发布动态属性管理
1，创建表：
create or replace procedure p_create_table(table_name in varchar2,result out varchar2) authid current_user as
/**
功能:按照表名到数据库中查询，如果表存在，则不创建新表！如果表不存在，则按照传入的新表名创建新表!
*/
v_tablename varchar2(100); --表名
v_flag number(10, 0); --表是否存在 0:不存在 1:存在
create_sql varchar2(2000);--创建表语句
v_sqlfalg varchar2(2000);--查询目标表是否存在
begin
result :='0';
v_flag := 0;
v_tablename :=upper(table_name);
v_sqlfalg := 'select count(*) from user_tables where table_name='''||v_tablename || '''';
execute immediate v_sqlfalg into v_flag;
if v_flag = 0 then ----表不存在,就创建它
begin
execute immediate 'create table '|| v_tablename || '
(
ID VARCHAR2(64) primary key,
CREATOR VARCHAR2(32),
CREATE_TIME VARCHAR2(32),
PROVINCE_CODE VARCHAR2(32),
STATUS VARCHAR2(32),
ORDER_NUMBER NUMBER,
DATA_PROVINCE VARCHAR2(32)
) nologging';
result :='1';
end;
end if;
end;
/
DECLARE
aaaa varchar2(1);
BEGIN
p_create_table('lipengfei',aaaa);
DBMS_OUTPUT.PUT_LINE(aaaa);
END;
/
2,修改表字段的名称
create or replace procedure p_rename_colum(table_name in varchar2,old_colum_name in varchar2,new_colum_name in varchar2,result out varchar2)
authid current_user as
/**
功能:
1、按照传入的表名,判断此表是否存在
2、如果目标表存在,判断目标列是否存在
2.1、如果目标列存在，则修改
2.2、如果目标列不存在，则不做任何操作
3、返回操作结果【0：列不存在 1：成功修改 2：表不存在】
*/
v_tablename varchar2(100); --目标表名
v_flag number(10, 0); --目标表是否存在 0:不存在 1:存在
v_sqlfalg varchar2(2000);--查询目标表是否存在
c_clum varchar2(100); --目标列名
c_flag number(10, 0); --目标列是否存在 0:不存在 1:存在
c_sqlfalg varchar2(2000);--查询目标列是否存在
begin
v_flag := 0;
c_flag := 0;
result :='0';
v_tablename :=upper(table_name);
c_clum :=upper(old_colum_name);
v_sqlfalg := 'select count(*) from user_tables where table_name=''' ||v_tablename || '''';
dbms_output.put_line(v_sqlfalg);
execute immediate v_sqlfalg into v_flag;
if v_flag = 1 then ---表存在,判断列是否存在
c_sqlfalg := 'select count(*) from user_tab_columns where table_name='''||v_tablename || ''''||' and column_name='''||c_clum||'''';
dbms_output.put_line(c_sqlfalg);
execute immediate c_sqlfalg into c_flag;
if c_flag = 1 then ----列存在,修改成新名字
begin
execute immediate 'alter table '||v_tablename||' rename column '||old_colum_name||' to '||new_colum_name;
result :='1';
end;
end if;
else
result :='2';
end if;
end;
/
调试：
set serveroutput on size 10000000
DECLARE
aaaa varchar2(1);
BEGIN
p_rename_colum('LI','lifei','id',aaaa);
DBMS_OUTPUT.PUT_LINE(aaaa);
END;
/
3修改表字段的长度
create or replace procedure p_alter_length(table_name in varchar2,colum_name in varchar2,new_length in number,result out varchar2)
authid current_user as
/**
功能:
1、按照传入的表名,判断此表是否存在
2、如果目标表存在,判断目标列是否存在
2.1、如果目标列存在，则修改
2.1.1、如果给定的新长度>旧长度，修改
2.1.2、如果给定的新长度<旧长度，不修改
2.2、如果目标列不存在，则不做任何操作
3、返回操作结果【0：列不存在 1：成功修改 2：表不存在 3：新长度<旧长度 或类型非 varchar2】
*/
v_tablename varchar2(100); --目标表名
v_flag number(10, 0); --目标表是否存在 0:不存在 1:存在
v_sqlfalg varchar2(2000);--查询目标表是否存在
c_clum varchar2(100); --目标列名
c_flag number(10, 0); --目标列是否存在 0:不存在 1:存在
c_sqlfalg varchar2(2000);--查询目标列是否存在
old_length number(10, 0);--旧列的长度
l_sqlfalg varchar2(2000);--判断：新长度<旧长度 或类型非 varchar2
begin
v_flag := 0;
c_flag := 0;
result :='0';
v_tablename :=upper(table_name);
c_clum :=upper(colum_name);
v_sqlfalg := 'select count(*) from user_tables where table_name=''' ||v_tablename || '''';
dbms_output.put_line(v_sqlfalg);
execute immediate v_sqlfalg into v_flag;
if v_flag = 1 then ----表存在,判断列存在否
c_sqlfalg := 'select count(*) from user_tab_columns where table_name='''||v_tablename || ''''||' and column_name='''||c_clum||'''';
dbms_output.put_line(c_sqlfalg);
execute immediate c_sqlfalg into c_flag;
if c_flag = 1 then ----列存在,判断给定的新长度、列的类型是否合理
begin
l_sqlfalg := 'select data_length from user_tab_columns where data_type=''VARCHAR2'' and table_name='''||v_tablename || ''''||' and column_name='''||c_clum||'''';
dbms_output.put_line(l_sqlfalg);
execute immediate l_sqlfalg into old_length;
EXCEPTION ----如果类型非 varchar2,抛异常
WHEN NO_DATA_FOUND THEN
old_length :=0;
end;
dbms_output.put_line('old： '||old_length||' new: '||new_length);
if old_length >0 and new_length > old_length then ----判断新长度>旧长度
begin
execute immediate 'alter table '||v_tablename||' modify '||colum_name||' varchar2('||new_length||')';
result :='1';
end;
else
result :='3';
end if;
else
result :='0';
end if;
else
result :='2';
end if;
end;
/
调试：
set serveroutput on size 10000000
DECLARE
aaaa varchar2(1);
BEGIN
p_alter_length('sunyanping','ORDER_NUMBER',30,aaaa);
DBMS_OUTPUT.PUT_LINE(aaaa);
END;
/
4添加一个新的属性
create or replace procedure p_add_colum(table_name in varchar2,colum_name in varchar2,colum_type in varchar2,colum_length in number,result out varchar2)
authid current_user as
/**
功能:
常用类型：number char varchar2 date clob blob 【不建议使用long类型,与你们程序中的Long不一样,可以"百度"一下！】
1、按照传入的表名,判断此表是否存在
2、如果目标表存在,判断目标列是否存在
2.1、如果目标列存在，则什么也不做
2.2、如果目标列不存在，则新增
3、返回操作结果【0：列存在 1：成功修改 2：表不存在】
*/
v_tablename varchar2(100); --目标表名
v_flag number(10, 0); --目标表是否存在 0:不存在 1:存在
v_sqlfalg varchar2(2000);--查询目标表是否存在
c_clum varchar2(100); --目标列名
c_flag number(10, 0); --目标列是否存在 0:不存在 1:存在
c_sqlfalg varchar2(2000);--查询目标列是否存在
begin
v_flag := 0;
c_flag := 0;
result :='0';
v_tablename :=upper(table_name);
c_clum :=upper(colum_name);
v_sqlfalg := 'select count(*) from user_tables where table_name=''' ||v_tablename || '''';
dbms_output.put_line(v_sqlfalg);
execute immediate v_sqlfalg into v_flag;
if v_flag = 1 then ---表存在,判断列是否存在
c_sqlfalg := 'select count(*) from user_tab_columns where table_name='''||v_tablename || ''''||' and column_name='''||c_clum||'''';
dbms_output.put_line(c_sqlfalg);
execute immediate c_sqlfalg into c_flag;
if c_flag = 0 then ----列不存在,增加新列
if upper(colum_type)='DATE' or upper(colum_type)='CLOB' or upper(colum_type)='BLOB' then
begin
execute immediate 'alter table '||v_tablename||' add '||colum_name||' '||colum_type;
result :='1';
end;
else
begin
execute immediate 'alter table '||v_tablename||' add '||colum_name||' '||colum_type||'('||colum_length||')';
result :='1';
end;
end if;
end if;
else
result :='2';
end if;
end;
/
调试：
set serveroutput on size 10000000
DECLARE
aaaa varchar2(1);
BEGIN
p_add_colum('sunyanping','lili','clob',30,aaaa);
DBMS_OUTPUT.PUT_LINE(aaaa);
END;
/
--blob字段插入实例
create table blob_table(
id number primary key,
blob_cl blob not null
);
insert into blob_table values(1,to_blob('11111000011111'));
commit;
select * from blob_table;
update blob_table
set blob_cl=to_blob('110010000110011')
where id=1;
delete from blob_table where id=1;
commit;
5删除一个属性
create or replace procedure p_del_colum(table_name in varchar2,colum_name in varchar2,result out varchar2)
authid current_user as
/**
功能:
1、按照传入的表名,判断此表是否存在
2、如果目标表存在,判断目标列是否存在
2.1、如果目标列存在，则执行删除操作
2.2、如果目标列不存在，则什么也不操作
3、返回操作结果【0：列不存在 1：删除成功 2：表不存在】
*/
v_tablename varchar2(100); --目标表名
v_flag number(10, 0); --目标表是否存在 0:不存在 1:存在
v_sqlfalg varchar2(2000);--查询目标表是否存在
c_clum varchar2(100); --目标列名
c_flag number(10, 0); --目标列是否存在 0:不存在 1:存在
c_sqlfalg varchar2(2000);--查询目标列是否存在
begin
v_flag := 0;
c_flag := 0;
result :='0';
v_tablename :=upper(table_name);
c_clum :=upper(colum_name);
v_sqlfalg := 'select count(*) from user_tables where table_name=''' ||v_tablename || '''';
dbms_output.put_line(v_sqlfalg);
execute immediate v_sqlfalg into v_flag;
if v_flag = 1 then ---表存在,判断列是否存在
c_sqlfalg := 'select count(*) from user_tab_columns where table_name='''||v_tablename || ''''||' and column_name='''||c_clum||'''';
dbms_output.put_line(c_sqlfalg);
execute immediate c_sqlfalg into c_flag;
if c_flag = 1 then ----列存在,执行删除
begin
execute immediate 'alter table '||v_tablename||' drop column '||colum_name;
result :='1';
end;
end if;
else
result :='2';
end if;
end;
/
调试：
set serveroutput on size 10000000
DECLARE
aaaa varchar2(1);
BEGIN
p_del_colum('sunyanping','lili',aaaa);
DBMS_OUTPUT.PUT_LINE(aaaa);
END;
/
6,整合所有功能
create or replace procedure p_operation_table(p_operation in varchar2,table_name in varchar2,old_colum in varchar2,new_colum in varchar2 default null,new_length in number default null,colum_type in varchar2 default null,result out varchar2)
authid current_user as
v_operation varchar2(100); ----操作类型
select_table varchar2(2000);----查询表存在否的SQL
select_colum varchar2(2000);----查询列存在否的SQL
select_length varchar2(2000);----查询列的长度SQL
v_table_flag number(10, 0); ----标识表存在否
v_colum_flag number(10, 0); ----标识列存在否
v_tablename varchar2(100); ----转换表名大写
v_clum varchar2(100); ----转换列名大写
v_length number(10, 0); ----列的当前长度
begin
v_operation := lower(p_operation); ---控制操作符小写，方便后面选择
v_tablename := upper(table_name); ---控制表名为大写，方便后面查询
v_clum := upper(old_colum); ---控制列名为大写，方便后面查询
result := '0';
select_table := 'select count(*) from user_tables where table_name=''' ||v_tablename || ''''; ---判断表存在否
execute immediate select_table into v_table_flag;
select_colum := 'select count(*) from user_tab_columns where table_name='''||v_tablename || ''''||' and column_name='''||v_clum||''''; ---列存在否
execute immediate select_colum into v_colum_flag;
if v_operation = 'pname' then ---为列重新命名操作
if v_table_flag=1 and v_colum_flag=1 then
P_DYNAMIC_LOG('p_operation_table','rname-column',sysdate,'table-name:'||v_tablename||' old-col-name:'||old_colum||' new-col-name:'||new_colum);
begin
execute immediate 'alter table '||v_tablename||' rename column '||old_colum||' to '||new_colum;
result :='1';
end;
end if;
end if;
if v_operation = 'plength' then ---修改列长度操作
if v_table_flag=1 and v_colum_flag=1 then
begin
select_length := 'select data_length from user_tab_columns where data_type=''VARCHAR2'' and table_name='''||v_tablename || ''''||' and column_name='''||v_clum||'''';
execute immediate select_length into v_length;
EXCEPTION ---只修改varchar2类型列，其它类型会抛异常
WHEN NO_DATA_FOUND THEN
v_length :=0;
P_DYNAMIC_LOG('p_operation_table','rlength-column[type-error]',sysdate,'table-name:'||v_tablename||' col-name:'||old_colum||' new-length:'||new_length);
end;
if v_length >0 and new_length > v_length then
begin
P_DYNAMIC_LOG('p_operation_table','rlength-column',sysdate,'table-name:'||v_tablename||' col-name:'||old_colum||' new-length:'||new_length);
execute immediate 'alter table '||v_tablename||' modify '||old_colum||' varchar2('||new_length||')';
result :='1';
end;
end if;
end if;
end if;
if v_operation = 'padd' then ---为表增加列操作
if v_table_flag=1 and v_colum_flag=0 then
if upper(colum_type)='DATE' or upper(colum_type)='CLOB' or upper(colum_type)='BLOB' then
P_DYNAMIC_LOG('p_operation_table','add-column',sysdate,'table-name:'||v_tablename||' col-name:'||old_colum||' col-type:'||colum_type);
begin
execute immediate 'alter table '||v_tablename||' add '||old_colum||' '||colum_type;
result :='1';
end;
else
begin
P_DYNAMIC_LOG('p_operation_table','add-column',sysdate,'table-name:'||v_tablename||' col-name:'||old_colum||' col-type:'||colum_type||' col-length:'||new_length);
execute immediate 'alter table '||v_tablename||' add '||old_colum||' '||colum_type||'('||new_length||')';
result :='1';
end;
end if;
end if;
end if;
if v_operation = 'pdel' then ---删除表中列操作
if v_table_flag=1 and v_colum_flag=1 then
begin
P_DYNAMIC_LOG('p_operation_table','del-column',sysdate,'table-name:'||v_tablename||' col-name:'||old_colum);
execute immediate 'alter table '||v_tablename||' drop column '||old_colum;
result :='1';
end;
end if;
end if;
end;
/
7默认值处理
create or replace procedure p_operation_default(p_operation in varchar2,table_name in varchar2,tar_colum in varchar2,colum_type in varchar2,new_default in varchar2,result out varchar2)
authid current_user as
select_table varchar2(2000);----查询表存在否的SQL
select_colum varchar2(2000);----查询列存在否的SQL
v_table_flag number(10, 0); ----标识表存在否
v_colum_flag number(10, 0); ----标识列存在否
v_tablename varchar2(100); ----转换表名大写
v_clum varchar2(100); ----转换列名大写
v_type varchar2(100); ----转换列类型大写
v_operation varchar2(100); ----操作类型
select_num varchar2(2000);----将传入的参数转换成数值类型
v_num number; ----转换成字符为数值
begin
v_operation := lower(p_operation); ---控制操作符小写，方便后面选择
v_type := lower(colum_type); ---控制列类型小写，方便后面选择
v_tablename := upper(table_name); ---控制表名为大写，方便后面查询
v_clum := upper(tar_colum); ---控制列名为大写，方便后面查询
result := '0';
select_table := 'select count(*) from user_tables where table_name=''' ||v_tablename || ''''; ---判断表存在否
execute immediate select_table into v_table_flag;
select_colum := 'select count(*) from user_tab_columns where table_name='''||v_tablename || ''''||' and column_name='''||v_clum||''''; ---列存在否
execute immediate select_colum into v_colum_flag;
if v_operation = 'add' or v_operation = 'edit' then ---为字段增加、编辑默认值
if v_table_flag=1 and v_colum_flag=1 then
result :='1';
if v_type = 'date' then
P_DYNAMIC_LOG('p_operation_default','add or edit',sysdate,'table-name:'||v_tablename||' col-name:'||v_clum||' col-type:'||v_type||' default-value:'||sysdate);
execute immediate 'alter table '||v_tablename||' modify '||v_clum||' default sysdate';
elsif v_type = 'number' then
select_num := 'select to_number('||new_default||') from dual';
execute immediate select_num into v_num;
P_DYNAMIC_LOG('p_operation_default','add or edit',sysdate,'table-name:'||v_tablename||' col-name:'||v_clum||' col-type:'||v_type||' default-value:'||v_num);
execute immediate 'alter table '||v_tablename||' modify '||v_clum||' default '||v_num;
else
execute immediate 'alter table '||v_tablename||' modify '||v_clum||' default '''||new_default||'''';
P_DYNAMIC_LOG('p_operation_default','add or edit',sysdate,'table-name:'||v_tablename||' col-name:'||v_clum||' col-type:'||v_type||' default-value:'||new_default);
end if;
end if;
end if;
if v_operation = 'delete' then ---删除字段默认值
if v_table_flag=1 and v_colum_flag=1 then
begin
P_DYNAMIC_LOG('p_operation_default','delete',sysdate,'table-name:'||v_tablename||' col-name:'||v_clum);
execute immediate 'alter table '||v_tablename||' modify '||v_clum||' default null';
result :='1';
end;
end if;
end if;
end;
/
set serveroutput on size 10000000
DECLARE
aaaa varchar2(1);
BEGIN
p_operation_default('delete','lipengfei','hihi','varchar2','abc',aaaa);
DBMS_OUTPUT.PUT_LINE(aaaa);
END;
/
8动态填充某一列【递增】
create or replace PROCEDURE update_num(table_name in varchar2) IS
data_table varchar2(100); ---目标表
select_sql varchar2(2000);---循环查询全部数据语句
update_sql varchar2(2000);---循环更新全部数据语句
TYPE cur_pro IS REF CURSOR; ---定义游标
fetchCurPro cur_pro; ---游标变量
V_COUNTER NUMBER; ---自加变量
v_id varchar2(64); ---循环取id变量
begin
data_table :=table_name;
V_COUNTER :=1;
select_sql :='SELECT id FROM '||data_table;
OPEN fetchCurPro for select_sql;
LOOP
FETCH fetchCurPro INTO v_id;
EXIT WHEN fetchCurPro%NOTFOUND;
update_sql := 'UPDATE '||table_name||' SET order_number =to_char( '||V_COUNTER||') where id='''||v_id||'''';
dbms_output.put_line(update_sql);
execute immediate update_sql;
V_COUNTER := V_COUNTER + 1;
END LOOP;
COMMIT;
CLOSE fetchCurPro;---关闭游标
dbms_output.put_line('全部更新完了！');
end;
/
create table t_z_faq_lipengfei as select * from t_z_faq where rownum<6;
update t_z_faq_lipengfei set ORDER_NUMBER=null ;
commit;
调试：
set serveroutput on size 10000000
exec update_num('t_z_faq_lipengfei');
9,功能说明文档
功能说明文档
功能描述：此存储过程包括以下4项功能
1、修改表字段名
2、修改表字段长度
3、增加一个新属性
4、删除一个旧属性
输入参数说明
(1)
p_operation
'pname' 此值代表：为指定表指定列重新命名操作
'plength' 此值代表：修改指定表指定列的长度操作
'padd' 此值代表：为指定表增加新列操作
'pdel' 此值代表：为指定表删除指定列操作
(2)
table_name
被操作的目标表
(3)
old_colum
1、pname 操作时，这个参数做为被重命名的目标列
2、plength操作时，这个参数做为被修改长度的目标列
3、padd 操作时，这个参数做为新增加的列
4、pdel 操作时，这个参数做为被删除的目标列
(4)
new_colum
1、pname 操作时，这个参数是列的新名称
(5)
new_length
1、plength操作时，这个参数做为列的新长度
(6)
colum_type
1、padd 操作时，这个参数做为新增加的列的指定类型
输出参数
(1)
result
1、pname 操作时，返回值： [0:表或列不存在 1:操作成功]
2、plength操作时，返回值： [0:表或列不存在、新长度<旧长度、非varchar2类型 1:操作成功]
3、padd 操作时，返回值： [0:表不存在、列存在 1:操作成功]
4、pdel 操作时，返回值： [0:表或列不存在 1:操作成功]
10,总需求描述
参数说明：
输入：
输出：
功能：
1，判断
参数是否正确
表是否存，列 表名，列名
2，实现
3，日志
1、按照表名到数据库中查询，如果表存在，则不创建新表，如果表不存在，则按照改表名创建新表，
表中字段为(ID VARCHAR2(64) primary key,CREATOR VARCHAR2(32),CREATE_TIME VARCHAR2(32),PROVINCE_CODE VARCHAR2(32),
STATUS VARCHAR2(32),ORDER_NUMBER NUMBER,DATA_PROVINCE VARCHAR2(32))
需要的参数：表名
2、修改表字段的名称。
需要的参数：表名、原字段名、现字段名
3、修改表字段的类型，涉及到原来是Varchar2类型转换成Clob类型和原来是Clob类型现在转换成Varchar2类型的。
需要的参数：表名、字段名、原字段类型、现字段类型、长度
4、修改表字段的长度，即原来是Varchar2（m）改成Varchar2（n）
需要的参数：表名、字段名、字段类型、长度n
5、添加一个新的属性
需要的参数：表名、字段名、字段类型、长度
number char varchar2 long clob date
6、删除一个属性
需要的参数：表名、字段名
测试环境：10.143.131.63 tyfb用户
