场景：在FM建模的时候，我们往往会创建查询主题-New Query Subject，下面我们就从如何构建查询主题，构建查询主题有哪些不同的方法，以及不同方法之间有哪些区别
展开论述。
首先我们可以看一下在FM中New Query Subject的时候有三种不同的方法供我们选择：
方法1：利用显存模型构建查询主题
此方法比较简单，选择新建查询主题向导之后，选择模型-确定，直接从左侧现存对象中拖拉过来就可以了
方法2：利用SQL-数据源构建查询主题
此方法较为简单，但是需要注意一些地方，选择新建查询主题向导之后，选择数据源（表格和列）-确定
会出现让我们选择确定
默认情况下上面的√是勾上的，我们选择去掉，避免一系列的选择，直接进入写SQL界面，接下来尽情发挥你的SQL功底吧
方法3：利用存储过程构建查询主题
此方法较为复杂，需要注意几个地方，选择新建查询主题向导之后，选择存储过程-确定
会出现让我们选择确定存储过程
选择GETORDER完成，测试报错如下，无法返回结果集
基于上面的错误去官网查看解决办法
URL： https://www-304.ibm.com/support/docview.wss?uid=swg21445524
Resolving the problem
In order to allow an Oracle Stored Procedure to pass a result set back to Cognos 8 for further reporting, the Stored Procedure must be part of an Oracle package and use ref cursors to return the result set.
Below is an example of an Oracle Package including a Stored Procedure that would be imported successfully as a Stored Procedure Query Subject into Framework Manager. There are comments in italics included to explain the structure.
create or replace package project_pk as /* Creates Package Header*/
type project_type is record( /* A record declaration is used to */
c1 projects.projectid %TYPE, /* provide a definition of a record */
c2 projects.projecttype %TYPE); /* that can be used by other variables*/
type project_type1 is ref cursor return project_type; /* Variable declaration */
procedure project_sp (tproj IN numeric, result1 in out project_type1); /* SP declaration */
end;
/
create or replace package body project_pk as /* Name of package body must be same as header */
procedure project_sp (tproj IN numeric, result1 in out project_type1) is /* SP Definition */
begin
open result1 for
select projects.projectid, projects.projecttype
from projects
where projects.projecttype=tproj;
end;
end;
/
注意这句话：the Stored Procedure must be part of an Oracle package and use ref cursors to return the result set.
大概意思是：存储过程必须是一个Oracle包和使用动态CURSOR返回的结果集。
完了，原来一般的存储过程（有输出值）FM还识别不了
3.1构建基于package和ref cursor 的procedure
A:创建package标题
create or replace package king_pk as /* Creates Package Header*/
type king_type is record
( /* A record declaration is used to */
o1 testorder.datekey %TYPE, /* provide a definition of a record */
o2 testorder.ordercount %TYPE
); /* that can be used by other variables*/
type king_type1 is ref cursor return king_type; /* Variable declaration */
procedure king_sp (result1 out king_type1); /* SP declaration */
end;
A:创建package主体
create or replace package body king_pk as /* Name of package body must be same as header */
procedure king_sp(result1 out king_type1) is /* SP Definition */
begin
open result1 for
select testorder.datekey,testorder.ordercount from testorder
where to_date(datekey,'yyyy-mm-dd')>=to_date(to_char(sysdate-3,'yyyy-mm-dd'),'yyyy-mm-dd')
and to_date(datekey,'yyyy-mm-dd')<=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd');
end;
end;
3.2：查看创建是否成功，失败的话会带红色的×
3.3：OK返回，选择新创建的存储过程
3.4：验证OK，测试OK，数据返回样式和预想一样
PS：需要注意的地方就是，创建包含存储过程的包标题和包体的时候
create or replace package king_pk as /* Creates Package Header*/包标题和包体的名字必须一致
type king_type is record
( /* A record declaration is used to */
o1 testorder.datekey %TYPE, /* provide a definition of a record */ 输出对象加上表别名，不然包标题找不到对象就报错
o2 testorder.ordercount %TYPE
); /* that can be used by other variables*/
type king_type1 is ref cursor return king_type; /* Variable declaration */
procedure king_sp (result1 out king_type1); /* SP declaration */
end;
create or replace package body king_pk as /* Name of package body must be same as header */
procedure king_sp(result1 out king_type1) is /* SP Definition */ 包体中存储过程中的参数信息和包标题中的参数信息保持一致
begin
open result1 for
select testorder.datekey,testorder.ordercount from testorder
where to_date(datekey,'yyyy-mm-dd')>=to_date(to_char(sysdate-3,'yyyy-mm-dd'),'yyyy-mm-dd')
and to_date(datekey,'yyyy-mm-dd')<=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd');
end;
end;
先创建包标题，再创建包体，
文终于此，2014-4-23