测试oracle的存储过程,测试技能：在oracle中自用存储过程进行测试数据构造

一、实践

–循环赋值

declare

i integer; --定义变量

begin

i := 101;

loop

/* 插入数据 /

insert into emp (EMP_ID, E_ID, UM_ID, NAME, BIRTHDAY, ADDRESS, POSTID, IDNO, GENDER, MARRY, MARRY_DESC, EM_NATIONAL, PHONE, XLNO, XL, COUNTRY, DEPT_ID, STATE, EMP_TYPE, EMP_TYPE_DESC, UPDATETIME)

values

(to_char(‘10000’+i), to_char(‘50100017847’ +i), CONCAT(‘kekexili’,to_char(i)), CONCAT(‘kekexili’,to_char(i)), ‘1900-01-01’, ‘深圳市XXXX’, ’ ', ‘123456190001012’, ‘m’, ‘M’, ‘已婚’, ‘不详’, ‘13800000000’, ‘10’, ‘大学专科’, ‘USA_PID’, ‘S000023309’, ‘A’, ‘1’, ‘正式员工’, ‘20171105’);

/ 参数递增 /

i := i + 1;

/ 停止条件 */

exit when i > 401;

end loop;

commit;

end;

/

二、其它

–存储过程,游标

declare c_num_acs_sequ number(10);

c_num_busi_scop NUMBER(10);

pk_num number(10);

CURSOR c_sal IS SELECT busi_code FROM OPP_BUSI_ACS_CFG group by busi_code order by busi_code;

begin

select count() INTO c_num_acs_sequ from user_tab_cols where table_name=‘OPP_BUSI_ACS_CFG’ and column_name=‘ACS_SEQU’;

SELECT COUNT() INTO c_num_busi_scop FROM USER_TAB_COLS WHERE TABLE_NAME=‘OPP_BUSI_ACS_CFG’ AND COLUMN_NAME=‘BUSI_SCOPE’;

SELECT COUNT() into pk_num FROM USER_CONSTRAINTS where CONSTRAINT_TYPE=‘P’ and TABLE_NAME=‘OPP_BUSI_ACS_CFG’;

IF c_num_acs_sequ=0 THEN

BEGIN

EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG rename column ACS_ID to ACS_SEQU’;

EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG modify(ACS_SEQU number(10))’;

END;

END IF;

if pk_num>0 then

BEGIN

EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG drop constraint PK_OPP_BUSI_ACS_CFG’;

EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG add constraint PK_OPP_BUSI_ACS_CFG primary key(busi_code,acs_sequ)’;

END;

end if;

SELECT COUNT() into pk_num FROM user_indexes where TABLE_NAME=‘OPP_BUSI_ACS_CFG’;

IF pk_num>0 THEN

BEGIN

EXECUTE IMMEDIATE ‘drop index PK_OPP_BUSI_ACS_CFG’;

END;

END IF;

FOR v_sal IN c_sal LOOP

EXECUTE IMMEDIATE (‘update OPP_BUSI_ACS_CFG set acs_sequ = rownum * 100 where busi_code =’’’ || v_sal.busi_code ||’’’’);

END LOOP;

IF c_num_busi_scop=0 THEN

EXECUTE IMMEDIATE ‘ALTER TABLE OPP_BUSI_ACS_CFG ADD BUSI_SCOPE VARCHAR2(32) DEFAULT NULL’;

END IF;

end;

–本想修改

set serveroutput on

declare

l_cur sys_refcursor;

l_empno varchar2(1000);

v_count varchar2(16) := ‘50100017858’;

begin

open l_cur for

select ODS_DICT_ITEM from dict_mapping where DICT_CODE = ‘NATIONALITY’;

loop

fetch l_cur into l_empno;

exit when l_cur%notfound;

dbms_output.put_line(l_empno);

update emp set EM_NATIONAL = l_empno where e_id = v_count;

v_count := to_char(to_number(v_count) + 1);

end loop;

commit;

end;

/

–修改

procedure loop_cursor_for

is

begin

declare cursor userinfo_cur is select * from userinfo_table;

begin

FOR userinfo_rec in userinfo_cur

loop

dbms_output.put_line(‘username is:’ || userinfo_rec.user_name);

end loop;

end;

end loop_cursor_for;