存储过程查询是指基于数据库中已编写好的存储过程获取数据结果。
适用情况:已存在的老数据系统。
存储过程要求
在产品中使用的存储过程在数据库创建时有一定的要求,具体存储过程要求如下:
1、调用方法要求
在Smartbi中存储过程检测参数的调用方法如下:
数据库类型 | 存储过程获取参数 |
---|---|
Teradata | Teradata JDBC接口没有直接提供获取宏参数和字段的方法,所以是通过执行以下SQL语句数据集获得参数的: SELECT C.* FROM DBC.TABLES T, DBC.COLUMNS C T.DATABASENAME=? AND T.TABLENAME=? AND T.TABLEKIND='M' AND T.DATABASENAME=C.DATABASENAME AND T.TABLENAME=C.TABLENAME |
Oracel、DB2等其他数据库 | 调用JDBC中的标准方法: connection.getMetaData().getProcedureColumns(...) |
在Smartbi中存储过程检测结果集的调用方法如下:(检测结果集:在前端弹出界面让用户输入参数后,真正的执行一次存储过程而获得输出字段。)
数据库类型 | 存储过程执行过程 |
---|---|
Teradata | 调用下面语句执行宏: |
Oracel、DB2等其他数据库 | 调用JDBC中的标准方法: |
2、数据库权限要求
数据库类型 | 数据库权限要求 |
---|---|
Teradata | 要求连接用户必须具有访问DBC.TABLES、DBC.COLUMNS的权限,及调用Exec方法的权限。 |
Oracel、DB2等其他数据库 | 要求连接用户必须具有调用getProcedureColumns和Call方法的权限。 |
3、前提条件
在创建存储过程数据集前,必须检测出相关的参数和获取存储过程的结果集。
(1)数据连接中对所需数据库进行数据库管理,选择所需的存储过程资源;
(2)对所选存储过程进行管理;
(3)“存储过程管理”窗口中通过 自动检测 和 检测结果集 按钮分别检测出相关的参数和获取存储过程的结果集;
- Oracle是通过输出参数作为结果集返回。
- 存储过程的OUT参数只支持游标类型,而且只支持单个输出参数。
可按需通过“绑定”按钮将存储过程中的参数与事实表中相应的参数进行绑定,绑定方法如下:
- 参数设置中点击“绑定”按钮
- 弹出选择参数对话框后,根据需求选择参数后,点击确定则实现存储过程参数和产品中的参数绑定。
操作入口
在“数据模型”界面点击“+”新建私有查询,选择“存储过程查询”;、
界面介绍
“存储过程查询”界面主要分为以下几区域:
- 资源选择区:该区列出可供选择的结果集;
- 工具栏:列出了可操作存储过程查询的工具按钮;
- 结果集选择区:支持拖动左侧资源树存储过程下的结果集;
- 参数设置/预览数据区:对存储过程查询的参数进行设置以及查看输出的数据;
1、资源选择区
资源选择区列出了所有可被存储过程查询使用的结果集。用户只需将所需结果集拖到结果筛选器区域即可。
当切换数据源后,会出现提示,确认切换后,结果集选择区、参数设置区以及数据预览区内容会被清空。
必须确保在“存储过程管理”窗口中通过 自动检测 和 检测结果集 按钮分别检测出相关的参数和结果集,资源区才会出现可选资源。
2、工具栏
存储过程查询工具栏按钮具体说明如下表:
按钮名称 | 图标 | 说明 |
---|---|---|
执行 | 执行当前存储过程查询 | |
保存 | 将当前存储过程查询保存到数据模型页面 | |
回到数据集 | 返回到数据模型页面 |
3、结果集选择区
结果集筛选器区域支持拖入左侧资源树中存储过程查询下的结果集;
该区域只支持一个结果集
若拖入多个结果集会出现提示,确认是否替换当前存在的结果集;
4、参数设置/预览数据区
通过tab页的形式将参数设置区和预览数据区进行展示。
(1)参数设置区
点击“参数设置”按钮可切换至参数设置界面;
参数设置界面点击“默认值”输入框可设置或修改参数的默认值;
(2)数据预览区
点击“数据预览”按钮可切换至查看输出数据界面;
数据预览前需在“参数设置”页面设置默认值后,前往工具栏中点击“执行”按钮,数据预览页面才会输出相关数据。