bz_tr_mapping.sql

来自「oracle9i+j2ee开发aq oracle9i+j2ee开发aq or」· SQL 代码 · 共 42 行

REM This function verifies if the employee_id is correct and fills in the
REM missing information


REM =======================================================
REM cleanup section
REM =======================================================

execute dbms_transform.drop_transformation -
	(schema =>'BZ_ADM',name =>'BZCARDVERIFY');

drop function FnVerifyBzCardOrder;

create or replace function FnVerifyBzCardOrder 
	(ord BZ_ADM.BZCARDORDER_TYP)
return BZCARDORDER_TYP is
	newOrder BZ_ADM.BZCARDORDER_TYP;
BEGIN
	newOrder := BzCardOrder_typ(null, null, null, 'NORMAL');
	select employee_id, first_name, last_name into newOrder.employee_id, 
		newOrder.first_name, newOrder.last_name from hr.employees e
	where (ord.employee_id is null or ord.employee_id=e.employee_id)
 	and (ord.first_name is null or ord.first_name = e.first_name)
	and (ord.last_name is null or ord.last_name = e.last_name) and rownum<2;
	exception
		when NO_DATA_FOUND then
			newOrder := ord;
	return newOrder;
END;
/

show errors;

execute	dbms_transform.create_transformation ( -
	schema => 'BZ_ADM', name  => 'BZCARDVERIFY', -
	from_schema => 'BZ_ADM', to_schema => 'BZ_ADM', -
	from_type => 'BZCARDORDER_TYP', to_type => 'BZCARDORDER_TYP',-
	transformation => 'BZ_ADM.FNVERIFYBZCARDORDER(SOURCE.USER_DATA)');

show errors;
grant execute on FnVerifyBzCardOrder to bz;