【Trafodion使用技巧篇】Trafodion 存储过程SPJ

有人提供一个问题：Trafodion的存储过程是用Java实现的，既然如此，为何要建立存储过程，为什么不直接写业务代码实现，而是建立复杂的Trafodion存储过程，Trafodion存储过程的优势在哪里？不可否认，由于Trafodion目前还不支持PL/SQL（Trafodion企业版EsgynDB支持PL/SQL），建立存储过程确实相对有点麻烦，需要编写Java类，编码打包上并上传到每个Trafodion安装的节点。但说到Trafodion存储过程的优势（准确地说，是存储过程的优势，存储过程不是Trafodion特有的），这里我就要来说道说道了。

何为存储过程？下面是摘自百度百科的一段话:

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

存储过程的优势？

1.模块化设计：将多个复杂SQL语句封装在一起，一次创建，调用多次，可减少开发人员工作量

2.执行更快：存储过程只在创建时编译，以后每次执行都无需重新编译，而一般SQL语句是执行一次编译一次

3.安全性高：可以基于角色授权，指定某些用户才可以执行存储过程

4.减少网络传输：存储过程是运行于服务器上，数据访问都是在服务器上执行，减少了与客户端进行大数据量的传输

下面通过一个例子简单介绍如何创建并使用Trafodion SPJ。主要步骤如下：

1.编写Java文件

import java.sql.*;
import java.math.*;
 
public class test
{
  public static void adjCredit(String name, String[] newCredit) throws SQLException {
 
     Connection conn = DriverManager.getConnection("jdbc:default:connection");
 
     PreparedStatement updateCredit = conn.prepareStatement("update trafodion.sales.customer " +
                                    "set credit = 'A2' " +
                                 "where custname = ?");
 
     PreparedStatement getCredit = conn.prepareStatement("select credit " +
                                "from trafodion.sales.customer " +
                                "where custname = ?");
 
     updateCredit.setString(1, name);
 
     updateCredit.executeUpdate();
 
     getCredit.setString(1, name);
 
     ResultSet rs = getCredit.executeQuery();
     rs.next();
 
     newCredit[0] = rs.getString(1);
 
     rs.close();
     conn.close();
  }
}

2.编译1中的java文件生成class文件

[trafodion@cent-1 ~]$ javac test.java
[trafodion@cent-1 ~]$ ls -la test.*
-rw-rw-r--. 1 trafodion trafodion 1113 Oct  9 09:21 test.class
-rw-rw-r--. 1 trafodion trafodion  823 Oct  9 09:21 test.java 

3.打包2中的class文件

[trafodion@cent-1 ~]$ jar cvf test.jar test.class
added manifest
adding: test.class(in = 1113) (out= 642)(deflated 42%)
[trafodion@cent-1 ~]$ ls -la test.*
-rw-rw-r--. 1 trafodion trafodion 1113 Oct  9 09:21 test.class
-rw-rw-r--. 1 trafodion trafodion 1095 Oct  9 09:21 test.jar
-rw-rw-r--. 1 trafodion trafodion  823 Oct  9 09:21 test.java

4.发布3中的jar文件

• 使用pdsh命令在每个节点上创建相同的目录

pdsh $MY_NODES -x $HOSTNAME mkdir $HOME/trafodion-spjs

• 使用pdcp把前面生成的jar复制到每个节点的相同目录下

pdcp $MY_NODES test.jar  trafodion-spjs/

• 创建library指向上述jar路径

SQL>create library trafodion.sales.sales file '/home/trafodion/trafodion-spjs/test.jar';
 
--- SQL operation complete.
 
SQL>get libraries;
 
--- SQL operation complete.
 
SQL>get libraries in schema trafodion.sales;
 
SALES
 
--- SQL operation complete.

5.创建SPJ

SQL>create procedure trafodion.sales.adjCredit(in name varchar(10),out credit varchar(10))
+>external name 'test.adjCredit'
+>library trafodion.sales.sales
+>language java
+>parameter style java;
 
--- SQL operation complete.
 
SQL>get procedures;
 
Procedures in Schema TRAFODION.SALES
====================================
 
ADJCREDIT

6.执行SPJ

SQL>call adjcredit('A',?);
....
 
CREDIT
----------
A2
 
--- SQL operation complete.