一. 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 ;