Trafodion SPJ 对比 Oracle 存储过程

Trafodion提供存储过程SPJ，相当于Oracle的存储过程，只不过Oracle的存储过程基于PLSQL，而由于Trafodion暂不支持PLSQL，因此Trafodion的存储过程从实现上面与Oracle存储过程有所不同，它是利用Java实现的，关于如何创建并使用Trafodion SPJ，可以参考我之前的一篇文章 http://blog.csdn.net/post_yuan/article/details/52765656，关于SPJ的详细内容，也可以参考官网文档 http://trafodion.incubator.apache.org/docs/spj_guide/index.html

本篇幅通过一个使用存储过程更新数据的实例来描述分别使用Trafodion SPJ与Oracle的存储过程实现相同的功能，通过实例来了解Trafodion SPJ与Oracle存储过程实现方式的不同。

1 创建样例表并插入测试数据

DROP TABLE customer;CREATE TABLE customer( custnum NUMERIC (4) NOT NULL, custname CHAR (18), street CHAR (22), city CHAR (14), state CHAR (12), postcode CHAR (10), credit CHAR (2), PRIMARY KEY ( custnum ));INSERT INTO customer VALUES( 21, 'CENTRAL UNIVERSITY', 'UNIVERSITY WAY', 'PHILADELPHIA', 'PENNSYLVANIA', '19104', 'A1' );INSERT INTO customer VALUES( 123, 'BROWN MEDICAL CO', '100 CALIFORNIA STREET', 'SAN FRANCISCO', 'CALIFORNIA','94944', 'C2' );INSERT INTO customer VALUES( 143, 'STEVENS SUPPLY', '2020 HARRIS STREET', 'DENVER', 'COLORADO','80734', 'A2' );INSERT INTO customer VALUES( 324, 'PREMIER INSURANCE', '3300 WARBASH', 'LUBBOCK', 'TEXAS','76308', 'A1' );INSERT INTO customer VALUES( 543, 'FRESNO STATE BANK', '2300 BROWN BLVD', 'FRESNO', 'CALIFORNIA','93921', 'B3' );INSERT INTO customer VALUES( 926, 'METALL-AG.', '12 WAGNERRING', 'FRANKFURT', 'WESTGERMANY', '34', 'D4' );INSERT INTO customer VALUES( 1234, 'DATASPEED', '300 SAN GABRIEL WAY', 'NEW YORK', 'NEW YORK','10014', 'C1' );INSERT INTO customer VALUES( 3210, 'BESTFOOD MARKETS', '3333 PHELPS STREET', 'LINCOLN', 'NEBRASKA','68134', 'A4' );INSERT INTO customer VALUES( 3333, 'NATIONAL UTILITIES', '6500 TRANS-CANADIENNE', 'QUEBEC', 'CANADA','H4T 1X4', 'A1' );INSERT INTO customer VALUES( 5635, 'ROYAL CHEMICALS', '45 NEW BROAD STREET', 'LONDON', 'ENGLAND','EC2M 1NH', 'B2' );INSERT INTO customer VALUES( 7654, 'MOTOR DISTRIBUTING', '2345 FIRST STREET', 'CHICAGO', 'ILLINOIS','60610', 'E4' );INSERT INTO customer VALUES( 7777, 'SLEEPWELL HOTELS', '9000 PETERS AVENUE', 'DALLAS', 'TEXAS','75244', 'B1' );INSERT INTO customer VALUES( 9000, 'BUNKNOUGHT INN', '4738 RALPH STREET', 'BAYONNE', 'NEW JERSEY','09520', 'C1' );INSERT INTO customer VALUES( 9010, 'HOTEL OREGON', '333 PORTLAND AVE.', 'MEDFORD', 'OREGON','97444', 'C2' );INSERT INTO customer VALUES( 9033, 'ART SUPPLIES, INC.', '22 SWEET ST.', 'PITTSBURGH', 'PENNA.','08333', 'C3' );

2 编写Trafodion SPJ Java文件并创建对应的Library、SPJ

//Java Source Codeimport java.sql.*;  import java.math.*;  public class testSPJ    {        public static void updateCustomer(String customer_name) throws SQLException {           Connection conn = DriverManager.getConnection("jdbc:default:connection");           PreparedStatement updateStatement = conn.prepareStatement("update trafodion.seabase.customer " +                                          "set state = 'China' " +                                       "where custname = ?");           updateStatement.setString(1, customer_name);           updateStatement.executeUpdate();           conn.close();        }      }  

//Create Library & SPJcreate library trafodion.seabase.testlibrary file '/home/trafodion/testSPJ.jar';  create procedure trafodion.seabase.updatecustomer(customer_name varchar(50))  external name 'testSPJ.updateCustomer'  library trafodion.seabase.testlibrary  language java  parameter style java;

3 创建Oracle存储过程

CREATE OR REPLACE PROCEDURE updatecustomer1(p_name CHAR)ASBEGIN    dbms_output.put_line(p_name);    UPDATE customer SET state = 'China' WHERE custname = p_name;END;/

4 测试Trafodion执行存储过程
(1)执行前

(2)执行存储过程

(3)执行后

5 测试Oracle执行存储过程
(1)执行前

(2)执行存储过程

(3)执行后