/*This proc is invoked by .NET to test the output parameter with the type of associative array,
and the type of the collection element is number
Retrieve the employees whose id less than 105*/
PROCEDURE proc_asso_array_num_out(p_asso_array_num OUT T_Asso_Array_Num)
IS
BEGIN
SELECT employee_id BULK COLLECT INTO p_asso_array_num FROM employees_bk WHERE employee_id<105;
END;
---------------------------------------------------------------------------------------------------------------------------------
/*This proc is invoked by .NET to test the input parameter with the type of nested table,
and the type of the collection element is number
Loop through each last name in the given nested table, then update its salary*/
PROCEDURE proc_nested_tab_str_in(p_nested_tab_str IN T_Nested_Tab_Str)
IS
BEGIN
FORALL i IN p_nested_tab_str.FIRST..p_nested_tab_str.LAST
UPDATE employees_bk SET salary=salary+1 WHERE last_name=p_nested_tab_str(i);
COMMIT;
END;
---------------------------------------------------------------------------------------------------------------------------------
/*This proc is invoked by .NET to test the output parameter with the type of nested table,
and the type of the collection element is varchar2
Retrieve the employees whose id less than 105*/
PROCEDURE proc_nested_tab_str_out(p_nested_tab_str OUT T_Nested_Tab_Str)
IS
BEGIN
SELECT last_name BULK COLLECT INTO p_nested_tab_str FROM employees_bk WHERE employee_id<105;
END;
---------------------------------------------------------------------------------------------------------------------------------
/*This proc is invoked by .NET to test the output parameter with the type of VARRAY,
Loop through each last name in the given varray, then update its salary*/
PROCEDURE proc_varray_str_in(p_varray_str IN T_Varray_Str)
IS
BEGIN
FORALL i IN p_varray_str.FIRST..p_varray_str.LAST
UPDATE employees_bk SET salary=salary+1 WHERE last_name=p_varray_str(i);
COMMIT;
END;
---------------------------------------------------------------------------------------------------------------------------------
/*This proc is invoked by .NET to test the output parameter with the type of object
Update the last_name according to two fields last_name, employee_id in the given object*/
PROCEDURE proc_obj_in(p_obj IN T_Object)
IS
BEGIN
UPDATE employees_bk SET last_name=p_obj.last_name WHERE employee_id=p_obj.employee_id;
COMMIT;
END;
---------------------------------------------------------------------------------------------------------------------------------
/*This proc is invoked by .NET to test the output parameter with the type of nested table,
and the type of the collection element is object
Loop through each object in the given nested table, then update the last name of employee*/
PROCEDURE proc_nested_tab_obj_in(p_nested_tab_obj IN T_Nested_Tab_Obj)
IS
BEGIN
FORALL idx IN p_nested_tab_obj.FIRST..p_nested_tab_obj.LAST
UPDATE employees_bk SET last_name=p_nested_tab_obj(idx).last_name WHERE employee_id=p_nested_tab_obj(idx).employee_id;
COMMIT;
END;
END;
1.引用游标（REF Curs