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 Code
import 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 & SPJ
create 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)
AS
BEGIN
    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)执行后