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

createorreplaceproceduredepartment_insert_test _users(testuserinvarchar2,iCountinnumber)is
4n!xg)d!v5|0iint;51Testing软件测试网 nljV
t[a)hZ
--parent department stru_id BPM
V$C;j\zw0p_stru_idvarchar2(30);
2K9\-bO4^)S?0p_organ_idvarchar2(30);51Testing软件测试网-~3|'M J(lM0] d!A
p_stru_pathvarchar2(20);
YAq@0f4_0accountvarchar2(20);51Testing软件测试网/V_DK
Tu(e rj
var_organ_namevarchar2(20);51Testing软件测试网;m4C @@ S%_(FY$g
var_organ_idvarchar2(20);51Testing软件测试网,B F:T&LP8v2Z[!N8K3[e
@#R;W
var_stru_id varchar2(20);
:~?-xd~6N9y.b0var_stru_pathvarchar2(1000);
}Q/@-@+\7vz0begin51Testing软件测试网g#M~9tVtg_
--insert bpm department51Testing软件测试网(iTSj[]
p_stru_id :='Sp_stru_id00113';
bQAyg.R-T0p_organ_id :='Op_organ_id0113';
BBn.\ q7J0p_stru_path :='1#'|| p_stru_id;
Z2w JcY#C0
I;h7}S!m1z9tXu0deletefromtest_struwhereSTRU_ID=p_stru_id;
K XMVn0eM"W0deletefromtest_organwhereorgan_id=p_organ_id;
`,S
Q MvMxH0commit;
{!AFm^3g;q'b0insertintoTEST_ORGAN (ORGAN_ID, ORGAN_CODE, ORGAN_NAME, SHORT_NAME, ORGAN_TYPE, WORKPLACE_ID, BEGIN_DATE, END_DATE, SCN, IN_USE)51Testing软件测试网:u/RGL0R.@\-\H
values(p_organ_id,'999902','BPM部门','BPM部门','2','1','20080904','99991231',50,'1');
"N ["G5r#K/D0Za8l0insertintoTEST_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软件测试网-k8E%AI6?w?Ca
values(p_stru_id, p_organ_id,'BPM部门','00','1',null,'1',2, p_stru_path,400,1,'20080904','99991231',54,'0','1','1','1');51Testing软件测试网]:|$tQ8p$l,K0}&J
commit;
9q ~,vvK051Testing软件测试网8[)eV|AFw?9yLR
foriin1..iCountloop51Testing软件测试网x`!g+G{

--insert organ，stru51Testing软件测试网,A/h]K_D
var_organ_name :='BPM员工'|| to_char(i);
Q(CfDP ?4q0var_organ_id :='vorgan_id'|| to_char(i);51Testing软件测试网 Gm
S3HfTjt
var_stru_id :='vstru_id'|| to_char(i);

t)X4V9Cg c$s2`*|j8ug0var_stru_path :=p_stru_path ||'#'|| var_stru_id;51Testing软件测试网:m'WDxEn t%R T4Tus!e
51Testing软件测试网,_y}4Ni#G-o
insertintoTEST_ORGAN (ORGAN_ID, ORGAN_CODE, ORGAN_NAME, SHORT_NAME, ORGAN_TYPE, WORKPLACE_ID, BEGIN_DATE, END_DATE, SCN, IN_USE)
4{-@-c;K*a4f0values(var_organ_id, var_organ_id, var_organ_name, var_organ_name,'8','1','20080904','99991231',57,'1');
#b/mG$c)~cR-J0
fay o,[5Qn{0insertintoTEST_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软件测试网:M5_#\4Sj"r
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');
1lmb@%TG\0--insert user
h-T1~.h,N+i5VK~tv0account := testuser || to_char(i);51Testing软件测试网ho8T*u8mB9v
insertintoTEST_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软件测试网(oV6b;yg
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');
1wKxo%F+t0insertintoTEST_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)
X^/qX1[C;A+l#L^0values(account, account,'1','permitOrgT','1','1',null,null,'20000101','99991231','1');51Testing软件测试网8?.|nyxGU;|}&R
insertintoTEST_USER_PROXY (USER_ID, PROXY_USER_ID)
)u_;vx+N0values('CPUBLIC', account);51Testing软件测试网v[ ]F%s"N
insertintoTEST_USER_PROXY (USER_ID, PROXY_USER_ID)
[g+SkRz$z+b%A0values('GPUBLIC', account);51Testing软件测试网d"ODC;W:xO
insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)
WAj0V
B5V.P rST[0values(account,'GPUBLIC','1','1');
1[9w-t"IG h0insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)
@^w-j&oQ0values(account,'SYSADMIN','1','1');
R}4}c&|9W0insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)51Testing软件测试网 i"RI _Fs
values(account,'WORKFLOWADMIN','1','1');51Testing软件测试网(rOu.]@-]+D.k
insertintoTEST_USER_ROLE (USER_ID, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)51Testing软件测试网;X'wK5ek4ak
values(account,'WORKFLOWCOMUSER','1','1');51Testing软件测试网ghogt!nY3Mi1za
insertintoTEST_STRU_TYPE_REF (USER_ID, STRU_TYPE, STRU_ID, ORGAN_NAME)
n @ ntk/hz~3R0values(account,'00','1','State Grid');51Testing软件测试网l%n*Auy/y0j
 endloop;
!S }O5lY r.Z0 commit;

enddepartment_insert_test_users;51Testing软件测试网,{2bB~"T8x^

SQL> call department_insert_test_users('BPM',100);

调用完成。

SQL> call delete_test_users('BPM');

调用完成。51Testing软件测试网qx'EE
a6Rk

SQL> call delete_test_stru_organ();

调用完成。

createorreplaceproceduredelete_test_users(likecount in varchar2)is
J:K5v:]7R
v0likecount2varchar2(30);51Testing软件测试网"Wn.p E1wgp"hc{
begin51Testing软件测试网2N_;@"Q P0K'[[w[u
 likecount2 :=likecount ||'%';
X,S#PTk4P4]\o051Testing软件测试网(EH$CVJ g9t}
 deletefromTEST_USER_ROLE twheret.user_idlikelikecount2;
*O1_jv*T-g0 deletefromTEST_STRU_TYPE_REF twheret.user_idlikelikecount2;51Testing软件测试网/vK
ng:@XAj
 deletefrom TEST_USER_PROXY twheret.user_idlikelikecount2ort.proxy_user_id likelikecount2;51Testing软件测试网-?Y2a6b0x$Wa;T#a0x
 deletefromTEST_USER_DATAREF twheret.user_id likelikecount2;51Testing软件测试网7~h{"gb
B
 commit;
-W2qVBl"g0 deletefromTEST_USERS twheret.user_id likelikecount2;
9|ht)a7BA9Gj0 commit;
&mY*G\9IF2PWa
J:@N0enddelete_test_users;

createorreplaceproceduredelete_test_stru_organis
Y*~!E2u2b5M8NlJ/`0p_stru_idvarchar2(30);51Testing软件测试网sKf-h%sL-S1H
p_organ_idvarchar2(30);51Testing软件测试网GvK.p-zd9@h
begin51Testing软件测试网&L*e:U%{O9@r H
p_stru_id :='Sp_stru_id00113';
0p B6t d}#~[0p_organ_id :='Op_organ_id0113';
] Cb
tj+K4vj;C0deletefromtest_struwhereSTRU_ID=p_stru_id;
@?9nF{%`"EYB:y"|0deletefromtest_organwhereorgan_id=p_organ_id;51Testing软件测试网i8dzOJ&~jQ:U#y
deletefromtest_stru twheret.organ_idlike'vorgan_id%';51Testing软件测试网}9SiHQd
deletefromtest_organ twheret.organ_idlike'vorgan_id%';
x gp3@/x9dzAvA0commit;
8l a:q(b~W)?;Z0enddelete_test_stru_organ;