oracle与jsql2008,Oracle sqlj 开发入门

1.开发工具使用Oracle JDeveloper

2.建立sqlj文件：右键电击项目->新增，选择 Database Tier->Database File->SQLJ class

3.编写sqlj的java方法，实例代码：

package com.mycompany.db.sqlj;

import com.mycompany.common.formula.service.FormulaParser;

import com.mycompany.common.formula.service.FormulaParserResult;

import sqlj.runtime.*;

import sqlj.runtime.ref.*;

import java.sql.*;

import oracle.sqlj.runtime.*;

import java.util.ArrayList;

import java.util.Iterator;

import oracle.jdbc.driver.OracleConnection;

#sql iterator Hr_kpi_formula_items

(Long f_id,String f_formula);

public class FormulaCalculate

{

public FormulaCalculate()

{

}

public static void calculate(long itemid) throws Exception

{

//以下内容为调试时使用

// Connection c = null;

// String URL = "jdbc:oracle:thin:@102.106.66.25:1521:orcl";

// String userid = "dev";

// String passwd = "dev";

//

//

// DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// c = DriverManager.getConnection(URL, userid, passwd);

// System.out.println("Successfully conencted to Oracle...");

//

// // You need to set a Context for SQLJ, otherwise -

// // SQLException: found null connection context

// DefaultContext.setDefaultContext( new DefaultContext(c));

ExecutionContext ec = DefaultContext.getDefaultContext().getExecutionContext();

Hr_kpi_formula_items items;

#sql[ec] items = {

select f_id,f_formula from hr_kpi_formula_item where f_id=:itemid

};

ArrayList vtIdList = new ArrayList();

while (items.next()) {

vtIdList.add((String) items.f_formula());

}

items.close();

Iterator itr = vtIdList.iterator();

while (itr.hasNext()) {

String formula = (String)itr.next();

FormulaParserResult result = FormulaParser.validate(formula);

System.out.println(formula);

System.out.println(result.getMessages());

}

System.out.println("----end");

}

public static void main(String[] args)

{

try{

FormulaCalculate.calculate(186644);

}catch(Exception e)

{

e.printStackTrace();

}

}

}

4.添加发布文档：右键点击项目－>新增－>General -> Deployment Profiles -> Loadjava and java store procedures

5.发布配置：

右键点击发布文档，选择 Properties...

在弹出窗口，选择：

Loadjava Options -> Force loading all file(-force)

Resolver -> Compile and Resolve all classes after loading

FileGroup - Project Output - Contributors ,点击 add 按钮，将依赖的包加入。

6.发布

右键点击发布文档，选择 Deploy *

7.编写包调用规则

在sqlplus，执行包调用规则，实例：

create or replace package FormulaCalculate as

procedure calculate

(f_id in number);

end FormulaCalculate;

/

create or replace package body FormulaCalculate as

procedure calculate

(f_id in number)

as

language java

name 'com.mycompany.db.sqlj.FormulaCalculate.calculate(long)';

end FormulaCalculate;

/

8.调用java存储过程

定义了包调用后，可以直接在sqlplus中调用java存储过程

SET SERVEROUTPUT ON

CALL dbms_java.set_output(200000);

/

begin

FormulaCalculate.calculate(186644);

End;

/

9.一些提醒：

过程名称中使用java类的全名如com.mycompany.db.sqlj.FormulaCalculate。

参数使用java对象类的全名如 calculate(java.lang.String)。基础java数据类型直接使用类型名称如 calculate(long)。

调试的时候必须取得Connection c,然后根据Connection获得上下文，如:DefaultContext.setDefaultContext( new DefaultContext(c));在发布运行时，系统自动获得连接，可以直接获得上下文：ExecutionContext ec = DefaultContext.getDefaultContext().getExecutionContext();

查看java存储过程长对象名的命令：SELECT dbms_java.longname(object_name) FROM user_objects WHERE object_type='JAVA CLASS';