plsql中函数调用java

一. plsql中函数调用java

1. 在plsql中先写一个函数

create or replace function test_create_id  return  number is
  language java name 'GeneratorUtil.getId() return long' ;

2. 在plsql中选择java resource定义一个java类

create or replace and compile java source named generator_util as
public class GeneratorUtil
{
  public static long getId()
  {
      long currentTimeMillis = System.currentTimeMillis();
      return currentTimeMillis;
  }
}

3. 测试

select test_create_id from dual ;

二. 存储过程

create or replace procedure test_1(productId in varchar , productName out varchar) is  -- in表示输入参数, out 表示输出参数
  product_num number(5) ;
  cur_product_name varchar(100);
  cnt number(5) ;
  
  cursor products is select '111' productId , '华为' productName from dual 
    union all select '222' productId , '苹果' productName from dual 
    union all select '333' productId , 'oppo' productName from dual;
    

begin
  product_num := 0;
  
  dbms_output.put_line('hello 开始了');
  -- 循环三次
  cnt := 1 ;
  for i in 1..3 loop
    dbms_output.put_line('hello');
    select 1 into cnt from dual ;
    dbms_output.put_line(cnt);
  end loop;
  
  -- 循环对象
  for product in products loop
    product_num := product_num + 1;
    dbms_output.put_line('产品id' || product.productid || '产品名称' || product.productname);
    
    if (productId = product.productid) 
      then productName := product.productname ;
    end if;
    
    exit when product.productid = '333';
  end loop;
  
end test_1;

测试

-- 定义变量
declare
cnt number;
msg varchar2(50);

begin 
  dbms_output.put_line('sql窗口执行');
  cnt := 1 ;
  for i in 1..3 loop
    
    select 111 into cnt from dual ;
    dbms_output.put_line(cnt);
  end loop;
  ---------------------------
  test_1(cnt ,msg);
  dbms_output.put_line('调用结果-> ' || msg);
    
end ;