存储过程在性能测试中的应用

createorreplaceproceduredepartment_insert_test _users(testuserinvarchar2,iCountinnumber)is
.T+F0pV @ l?D0iint;51Testing软件测试网He;g,h?3g%a|
Y
--parent department stru_id BPM51Testing软件测试网8B(V"M-SK2W)sZ/Z K s
p_stru_idvarchar2(30);51Testing软件测试网_GGA t*}A
p_organ_idvarchar2(30);51Testing软件测试网?C3C{*w#vA7`#Q
p_stru_pathvarchar2(20);
6~.f,N:|UX%za1j0accountvarchar2(20);
(}5u],hM$hEO0var_organ_namevarchar2(20);51Testing软件测试网@jp4Gcm
var_organ_idvarchar2(20);51Testing软件测试网dIuv@9J,XS0F5Y
var_stru_id varchar2(20);51Testing软件测试网/M7yO'w!]1s
var_stru_pathvarchar2(1000);
S(w i-@~0begin51Testing软件测试网(M:wf1@2H)B/I
--insert bpm department
H4dG,W)?H0p_stru_id :='Sp_stru_id00113';
%A:O$IL$aC0p_organ_id :='Op_organ_id0113';51Testing软件测试网 y&S"Z umP(R3P_ _ g
p_stru_path :='1#'|| p_stru_id;51Testing软件测试网@\g ~Lq

]Zt'A)R$ST1W'[0deletefromtest_struwhereSTRU_ID=p_stru_id;51Testing软件测试网ay
P*G\Y?*x
deletefromtest_organwhereorgan_id=p_organ_id;
R4]-HavDM0commit;51Testing软件测试网s
G!Uii
insertintoTEST_ORGAN (ORGAN_ID, ORGAN_CODE, ORGAN_NAME, SHORT_NAME, ORGAN_TYPE, WORKPLACE_ID, BEGIN_DATE, END_DATE, SCN, IN_USE)
mI:JvJ KR0values(p_organ_id,'999902','BPM部门','BPM部门','2','1','20080904','99991231',50,'1');
j"{0eUV@"W0insertintoTEST_STRU (STRU_ID, ORGAN_ID, ORGAN_ALIAS, STRU_TYPE, PARENT_ID, PRINCIPAL_ID, CORPORATION_ID, STRU_LEVEL, STRU_PATH, STRU_ORDER, GLOBAL_ORDER, BEGIN_DATE, END_DATE, SCN, IS_LEAF, IN_USE, DEPARTMENT_ID, PERMISSION_STRU_ID)
2V#J5S].n'K+E N#C0values(p_stru_id, p_organ_id,'BPM部门','00','1',null,'1',2, p_stru_path,400,1,'20080904','99991231',54,'0','1','1','1');
a#{(V9PP p E-v|? `0commit;
S6m,@n I*i051Testing软件测试网2XWIFOOM8|
foriin1..iCountloop51Testing软件测试网0hv3jzx vY

--insert organ，stru
.t2jXi}9FcH"w0var_organ_name :='BPM员工'|| to_char(i);
"Cge2q,{5?0var_organ_id :='vorgan_id'|| to_char(i);51Testing软件测试网8~@aY3Qu
var_stru_id :='vstru_id'|| to_char(i);51Testing软件测试网.w/G1{Vi_l{
var_stru_path :=p_stru_path ||'#'|| var_stru_id;
9JJm4d,{9c051Testing软件测试网j)n'FU;pV}$]HNN
insertintoTEST_ORGAN (ORGAN_ID, ORGAN_CODE, ORGAN_NAME, SHORT_NAME, ORGAN_TYPE, WORKPLACE_ID, BEGIN_DATE, END_DATE, SCN, IN_USE)51Testing软件测试网 O)@$k)?(dS)vnn8\.E
values(var_organ_id, var_organ_id, var_organ_name, var_organ_name,'8','1','20080904','99991231',57,'1');
.l4P_8S3M[dvh0
xkIRr6_p0insertintoTEST_STRU (STRU_ID, ORGAN_ID, ORGAN_ALIAS, STRU_TYPE, PARENT_ID, PRINCIPAL_ID, CORPORATION_ID, STRU_LEVEL, STRU_PATH, STRU_ORDER, GLOBAL_ORDER, BEGIN_DATE, END_DATE, SCN, IS_LEAF, IN_USE, DEPARTMENT_ID, PERMISSION_STRU_ID)51Testing软件测试网)rRql%i~ xf
values(var_stru_id, var_organ_id, var_organ_name,'00', p_organ_id,null,'1',3, var_stru_path,200,1,'20080904','99991231',58,'1','1', p_organ_id,'1');51Testing软件测试网{S(T#LS,b at["B7u
--insert user51Testing软件测试网
_3I[%DF#XuhTP
account := testuser || to_char(i);

xE+R8v3k)OU;j0insertintoTEST_USERS (USER_ID, EXTERNAL_USER, USER_NAME, PASSWORD, ACCOUNT_STATUS, SECURITY_LEVEL, MAX_SESSIONS, IS_SYS, IS_CPUBLIC, CREATE_TIME, LOCK_TIME, EXPIRED_TIME, PSWD_UPT_TIME, PSWD_TIME, CORPORATION_ID, DEPARTMENT_ID, EMPLOYEE_ID, E_MAIL, MSN, QQ, MOBILE, CHAR_1, CHAR_2, NUM_1, NUM_2, PERMISSION_STRU_ID)51Testing软件测试网3qM:fq0D[)t
values(account,'01', var_organ_name,'99c6b157085564b43b85711360ec6166','11',0, -1,'0','0','20080904 16:12:57',null,'99991231 23:59:59','20080904 16:12:57','20081103 16:12:57','1', p_stru_id, var_stru_id,null,null,null,null,null,null,0,0,'1');51Testing软件测试网*rj:s+kS9IU
insertintoTEST_USER_DATAREF (REF_ID, USER_ID, RES_VALUE, DATA_RES_TYPE, IS_DEFAULT, IN_USE, STRU_ID, STRU_TYPE, DATE_BEGIN, DATE_END, IS_WRITABLE)51Testing软件测试网cI?{~AE/g-?7M
values(account, account,'1','permitOrgT','1','1',null,null,'20000101','99991231','1');
y[4]YGx;SNm0insertintoTEST_USER_PROXY (USER_ID, PROXY_USER_ID)51Testing软件测试网i y/dI7ga.E&B}
values('CPUBLIC', account);
tU.nzD-J2R0insertintoTEST_USER_PROXY (USER_ID, PROXY_USER_ID)
8xW O n!H?
DY0values('GPUBLIC', account);51Testing软件测试网qRX*Hv[:Q? I
insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)51Testing软件测试网;n/V8X9`;Eq@
values(account,'GPUBLIC','1','1');51Testing软件测试网0u;z'I)ho4jJ5[#Q
insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)
Ax-XR5Y0values(account,'SYSADMIN','1','1');51Testing软件测试网 B x
f'Ju"R(eKE
insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)51Testing软件测试网I/p.`&D9aIRC
values(account,'WORKFLOWADMIN','1','1');
:p[*k|,x
dX6[0insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)51Testing软件测试网#J#E M*T.i^ v:J1~o)E]
values(account,'WORKFLOWCOMUSER','1','1');51Testing软件测试网[ D2a P\e*i.pm0~
insertintoTEST_STRU_TYPE_REF (USER_ID, STRU_TYPE, STRU_ID, ORGAN_NAME)
RBt_q0values(account,'00','1','State Grid');
/y bfJ-_ XvhVp;z0 endloop;51Testing软件测试网Hw&t N9nxCd0Z
 commit;

enddepartment_insert_test_users;

SQL> call department_insert_test_users('BPM',100);51Testing软件测试网:o!~,_Cu+~?F{D

调用完成。51Testing软件测试网-j3p#nN
Q@$T"gF

SQL> call delete_test_users('BPM');

调用完成。

SQL> call delete_test_stru_organ();51Testing软件测试网!RmO h'b4~-c{:R \u3B

调用完成。51Testing软件测试网/rbr4Y.a9g:u

createorreplaceproceduredelete_test_users(likecount in varchar2)is
.HJEe6FA0likecount2varchar2(30);
-c7["K^rJ/?0begin51Testing软件测试网2S
m4w8Mm5B
GZk
 likecount2 :=likecount ||'%';51Testing软件测试网^_c2}rdghe

"s;s~$v ^0 deletefromTEST_USER_ROLE twheret.user_idlikelikecount2;51Testing软件测试网 b~BLD0X4h@OR
 deletefromTEST_STRU_TYPE_REF twheret.user_idlikelikecount2;51Testing软件测试网!{TT!O1_'p
e$u V
 deletefrom TEST_USER_PROXY twheret.user_idlikelikecount2ort.proxy_user_id likelikecount2;
G"Y7} i$|3r*W#U:c0 deletefromTEST_USER_DATAREF twheret.user_id likelikecount2;51Testing软件测试网X6F,YRY`Wu@
 commit;51Testing软件测试网_*b y;H7@l
 deletefromTEST_USERS twheret.user_id likelikecount2;51Testing软件测试网d u7e_rD(`(Za(v
 commit;
BV1LUScG0enddelete_test_users;51Testing软件测试网6w D&\0M@
uj

createorreplaceproceduredelete_test_stru_organis51Testing软件测试网2?:T5A3[+ML U
p_stru_idvarchar2(30);51Testing软件测试网7],T){tBN]
p_organ_idvarchar2(30);51Testing软件测试网dbiu!J*U;U(rq$`
begin
;X1g4R ko ?c0p_stru_id :='Sp_stru_id00113';51Testing软件测试网g-qRw3oD
p_organ_id :='Op_organ_id0113';
\G*Ih3p QcT0deletefromtest_struwhereSTRU_ID=p_stru_id;51Testing软件测试网$C#Apd'`
deletefromtest_organwhereorgan_id=p_organ_id;51Testing软件测试网3QU/EhY'|V-A
deletefromtest_stru twheret.organ_idlike'vorgan_id%';51Testing软件测试网Y[y#H?1|1l
deletefromtest_organ twheret.organ_idlike'vorgan_id%';
}Dc c#?c M:[0commit;
uv;rK+N#CQ_0enddelete_test_stru_organ;