--创建包

create or replace PACKAGE MYPACKAGE AS 

  type empcursor is ref cursor;

  procedure queryEmpList(dno in number,empList out empcursor);

END MYPACKAGE;

--创建包体

create or replace PACKAGE BODY MYPACKAGE AS

  procedure queryEmpList(dno in number,empList out empcursor) AS

     str_l_selectsql varchar2(4000);

  BEGIN

    str_l_selectsql :='select * from emp where deptno = :dno';

    --EXECUTE IMMEDIATE str_l_selectsql USING  v_2;--不能实现动态绑定参数

    --str_l_selectsql :=str_l_selectsql || dno;--可以通过拼接sql的方式，但不专业

    --open empList for str_l_selectsql;

    --使用光标，首先要打开光标

    open empList for str_l_selectsql USING  dno;--动态绑定参数

    NULL;

  END queryEmpList;

END MYPACKAGE;