毕业论文（大容量数据库智能备份系统）

数据库管理专业毕业设计论文

from host_tab

where ip_address = p_host_ip;

if v_host_exist > 0 then raise v_error_host_exist; end if;

--insert host information into host_tab select host_sequence.nextval into v_host_id from dual;

insert into host_tab(host_id,host_name,host_desc,ip_address,ip_port, host_default_dir,status)

values(v_host_id,p_host_name,p_host_desc,p_host_ip,p_host_port,p_host_dir,0);

--insert rows into oper_tab

insert into oper_tab(oper_id,user_id,user_name,oper_type,oper_object_type, oper_object_id,oper_result,start_time,end_time)

values(oper_sequence.nextval,v_user_id,p_user_name,0,1,v_host_id,0,sysdate,sysdate);

--check if database is exist

select count(database_id) into v_database_exist from database_tab where sid=p_db_sid;

if v_database_exist>0 then raise v_error_database_exist; end if;

--insert database information into database_tab select database_sequence.nextval into v_database_id from dual;

insert into database_tab(database_id,database_name,sid,host_id,oracle_home,database_type, status,login_user,login_pswd,tns_name)

values(database_sequence.nextval,p_db_name,p_db_sid,v_host_id,p_db_home,p_db_type,0, p_db_user,p_db_pswd,p_db_tnsname);

--insert rows into oper_tab

insert into oper_tab(oper_id,user_id,user_name,oper_type,oper_object_type, oper_object_id,oper_result,start_time,end_time)

values(oper_sequence.nextval,v_user_id,p_user_name,0,2,v_database_id,0,sysdate,sysdate);

--commit

第 页 25 2006-6-8

数据库管理专业毕业设计论文

commit;

p_rtn_num := 0; --exception exception

when v_error_user_exist then p_rtn_num := 1;

when v_error_host_exist then p_rtn_num := 2;

when v_error_database_exist then p_rtn_num := 3; end;

CREATE OR REPLACE PROCEDURE \ p_user_id in number,

P_STOREPOOL_NAME in varchar2, P_HOST_ID in number, P_STORE_PATH in varchar2, P_POOL_SIZE in number,

P_USED_SIZE in number default 0, P_ALERT_PERCENT in number, P_AUTO_EXTEND in number, P_STATUS in number,

P_oper_type in number,--0 add,1 modify,2 delete P_output out number ) as

--declare

v_role_id number(1); v_user_name varchar2(30); v_storepool_id number(5); v_error_no_privs exception; v_pool_exist number(1); v_error_pool_exist exception; v_same_pool number(1); v_error_same_pool exception;

begin

select role_id into v_role_id from user_tab

where user_id = p_user_id;

if v_role_id <> 0 then

raise v_error_no_privs;

第 页 26 2006-6-8

数据库管理专业毕业设计论文

end if;

select user_name into v_user_name from user_tab

where user_id = p_user_id;

--check if storepool exist select count(storepool_id) into v_pool_exist from storepool_tab

where storepool_name = p_storepool_name;

--check oper_type case

when p_oper_type = 0 then if v_pool_exist > 0 then raise v_error_pool_exist; end if;

select count(storepool_id) into v_same_pool from storepool_tab

where store_path = p_store_path;

if v_same_pool > 0 then raise v_error_same_pool; end if;

--insert rows to storepool_tab select storepool_sequence.nextval into v_storepool_id from dual;

insert into storepool_tab(storepool_id,storepool_name,host_id,store_path, pool_size,used_size,alert_percent,auto_extend,status)

values(v_storepool_id,p_storepool_name,p_host_id,p_store_path, p_pool_size,0,p_alert_percent,p_auto_extend,0);

--insert rows to oper_tab

insert into oper_tab(oper_id,user_id,user_name,oper_type,oper_object_type, oper_object_id,oper_result,start_time,end_time)

values(oper_sequence.nextval,p_user_id,v_user_name,0,1,v_storepool_id,0,sysdate,sysdate);

第 页 27 2006-6-8

数据库管理专业毕业设计论文

--commit; commit;

p_output :=0;

when p_oper_type = 1 then if v_pool_exist = 0 then raise v_error_pool_exist; end if;

select count(storepool_id) into v_same_pool from storepool_tab

where store_path = p_store_path;

if v_same_pool > 1 then raise v_error_same_pool; end if;

--insert rows to storepool_tab update storepool_tab set host_id = p_host_id,

store_path = p_store_path, pool_size = p_pool_size, used_size = p_used_size, auto_extend = p_auto_extend, status = p_status

where storepool_name = p_storepool_name;

--insert rows to oper_tab

insert into oper_tab(oper_id,user_id,user_name,oper_type,oper_object_type, oper_object_id,oper_result,start_time,end_time)

values(oper_sequence.nextval,p_user_id,v_user_name,0,1,v_storepool_id,0,sysdate,sysdate);

--commit; commit;

p_output := 0;

when p_oper_type = 2 then if v_pool_exist = 0 then raise v_error_pool_exist; end if;

--insert rows to storepool_tab delete from storepool_tab

第 页 28 2006-6-8