1 2 3 下一页

在开始介绍之前，先给出文章里用到的所有PL/SQL代码：

（类型定义）

CREATE OR REPLACE TYPE T_Nested_Tab_Str IS TABLE OF VARCHAR2(25);  --  CREATE OR REPLACE TYPE T_Object IS OBJECT  (   employee_id number(6),   last_name varchar2(25)  );  --  CREATE OR REPLACE TYPE T_VARRAY_STR IS VARRAY(10) OF VARCHAR2(25);  --  CREATE OR REPLACE TYPE T_Nested_Tab_Obj IS TABLE OF T_Object;

（包的声明）

CREATE OR REPLACE PACKAGE pkg_odp_dotnet IS   TYPE T_Ref_Cursor IS REF CURSOR;   TYPE T_Asso_Array_Num IS TABLE OF employees_bk.employee_id%TYPE INDEX BY PLS_INTEGER;   TYPE T_Asso_Array_Str IS TABLE OF employees_bk.last_name%TYPE INDEX BY PLS_INTEGER;    --   PROCEDURE proc_ref_cursor(p_ref_cursor OUT T_Ref_Cursor);   PROCEDURE proc_sys_refcursor(p_sys_refcursor OUT SYS_REFCURSOR);   --   PROCEDURE proc_asso_array_num_in(p_asso_array_num    IN T_Asso_Array_Num);   PROCEDURE proc_asso_array_num_out(p_asso_array_num OUT T_Asso_Array_Num);   PROCEDURE proc_asso_array_str_out(p_asso_array_str OUT T_Asso_Array_Str);   --   PROCEDURE proc_nested_tab_str_in(p_nested_tab_str IN T_Nested_Tab_Str);   PROCEDURE proc_nested_tab_str_out(p_nested_tab_str OUT T_Nested_Tab_Str);   --   PROCEDURE proc_obj_in(p_obj IN T_Object);   --   PROCEDURE proc_nested_tab_obj_in(p_nested_tab_obj IN T_Nested_Tab_Obj);   --   PROCEDURE proc_varray_str_in(p_varray_str IN T_Varray_Str);  END;

（包体）

CREATE OR REPLACE PACKAGE BODY pkg_odp_dotnet IS   /*This proc is invoked by .net to test the ref cursor      Retrieve the employees whose id less than 105*/ PROCEDURE proc_ref_cursor(p_ref_cursor OUT T_Ref_Cursor)   IS   BEGIN      OPEN p_ref_cursor FOR      SELECT employee_id,first_name,last_name FROM employees_bk WHERE employee_id<105;   END;         ---------------------------------------------------------------------------------------------------------------------------------   /*This proc is invoked by .NET to test the pre-defined sys_refcursor      Retrieve the employees whose id less than 105*/ PROCEDURE proc_sys_refcursor(p_sys_refcursor OUT SYS_REFCURSOR)   IS   BEGIN      OPEN p_sys_refcursor FOR      SELECT employee_id,first_name,last_name FROM employees_bk WHERE employee_id<105;   END;         ---------------------------------------------------------------------------------------------------------------------------------   /*This proc is invoked by .NET to test the input parameter with the type of associative array,       and the type of the collection element is number      Loop through each employee id in the given associative array, then update its salary*/ PROCEDURE proc_asso_array_num_in(p_asso_array_num    IN T_Asso_Array_Num)   IS      idx PLS_INTEGER;   BEGIN      idx:=p_asso_array_num.FIRST;      WHILE(idx IS NOT NULL) LOOP        UPDATE employees_bk SET salary=salary+1 WHERE employee_id=p_asso_array_num(idx);        idx:=p_asso_array_num.NEXT(idx);      END LOOP;      COMMIT;   END;         ---------------------------------------------------------------------------------------------------------------------------------   /*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 varchar2      Retrieve the employees whose id less than 105*/ PROCEDURE proc_asso_array_str_out(p_asso_array_str OUT T_Asso_Array_Str)   IS   BEGIN      SELECT last_name BULK COLLECT INTO p_asso_array_str FROM employees_bk WHERE employee_id<105;   END;         ---------------------------------------------------------------------------------------------------------------------------------   /*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,       and the type of the collection element is varchar2      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;

上一篇： Oracle 11g系列—1—基础回顾
下一篇： Oracle 11g系列—7—SQL 基础学习

1 2 3 下一页