文章目录
- 函数
- 1.1 简单自定义函数
- 1.2 作用于表的自定义函数
- 1.3 自定义的Java函数
- 1.4 应用RETURN语句
- 1.5 异常处理
函数
参考《Oracle函数》
使用函数可以大大提高SELECT语句操作数据库的能力;它给数据的转换和处理提供了方便。
函数只是将取出的数据进行处理,不会改变数据库中的值。
1.1 简单自定义函数
语法格式:
CREATE [OR REPLACE] FUNCTION function_name
( [argument [{IN|OUT}] datatype ], [,...] )#可以没有参数
RETURN datatype {IS|AS}#函数必须有返回值,且需明确指出返回数据类型
function_body
示例:
定义一个计算给定半径的圆的面积,用户调用该函数时输入半径参数即可得到计算结果。
SQL> create or replace function area(f float)
2 return float
3 is
4 begin
5 return 3.14*(f*f);
6 end area;
7 /
函数已创建。
查看当前用户多拥有的函数
SQL> col object_name for a20
SQL> select object_name,object_type,created,status
2 from user_objects
3 where object_type = 'FUNCTION';
OBJECT_NAME OBJECT_TYPE CREATED STATUS
-------------------- ------------------- -------------- ---------------
CURSOREMP FUNCTION 10-3月 -20 VALID
AREA FUNCTION 12-3月 -20 VALID
SQL> select area(4) from dual;
AREA(4)
----------
50.24
1.2 作用于表的自定义函数
创建一个函数,通过输入员工号读取员工的工资和姓名,并对工资做一个判断。
SQL> CREATE
2 OR REPLACE FUNCTION findwealthier(empnumber number) RETURN varchar2 IS
3 salary NUMBER;
4 name varchar2(40);
5
6 BEGIN
7 SELECT
8 ename,
9 sal
10 INTO name,salary
11 FROM
12 emp
13 WHERE
14 empno = empnumber;
15
16 if salary > 4000 then
17 dbms_output.put_line(name || ' salary is ' || salary);
18
19 RETURN 'good salary!';
20 # 这里做了测试,
21 #发现函数直接return后是不会再执行之后的语句的
22 else RETURN 'not so good salary!';
23 #后面这句测试输出时显然没有执行
24 dbms_output.put_line(name || ' salary is ' || salary);
25
26 END IF;
27
28 END findwealthier;
29 /
函数已创建。
SQL> select findwealthier(7654) from dual;
FINDWEALTHIER(7654)
------------------------------------------------------------------------------------------------------------------------
not so good salary!#未执行输出语句
SQL> select findwealthier(7839) from dual;
FINDWEALTHIER(7839)
------------------------------------------------------------------------------------------------------------------------
good salary!
KING salary is 5000#执行了输出语句
1.3 自定义的Java函数
- 创建一个类,类中包含要发布的Java函数,且该方法需公共(public)和静态(static)
public class AreaJava {
public static float areaj(float f) {
//3.14数值属于double类型,不能直接转换为float类型数据
//可参考
float area = 3.14f*(f*f);
return area;
}
}
- 编译该类生成
.class
文件
E:\workspace\oracle\src\main\java>javac AreaJava.java
- 授予用户Scott的JAVAUSERPRIV特权,使得用户Scott可加载Java类文件
:\workspace\oracle\src\main\java>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 3月 16 15:27:43 2020
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn sys/password as sysdba
已连接。
SQL> grant javauserpriv to scott;
授权成功。
- 加载Java类文件到Oracle的Scott模式
E:\workspace\oracle\src\main\java>loadjava
loadjava: Usage: loadjava [-definer] [-encoding encoding] [-force] [-genmissing] [-genmissingjar jar] [-grant grants] [-help] [-nousage] [-nosing] [-genmissingjar jar] [-grant grants] [-help] [-nousage] [-noverify] [-schema schema] [-synonym] [-thin] [-tableschema schema] [-user us-oci8] [-order] [-resolve] [-nativecompile] [-resolver resolver] [-schema ss..properties...chema] [-synonym] [-thin] [-tableschema schema] [-user user/password@database] [-verbose] [-edition edition] classes..jars..resources..properties...
E:\workspace\oracle\src\main\java> loadjava -verbose -schema scott -thin -user scott/password@localhost:1521:oracle AreaJava.class
arguments: '-user' 'scott/***@localhost:1521:oracle' '-verbose' '-schema' 'scott' '-thin' 'AreaJava.class'
creating : class SCOTT.AreaJava
loading : class SCOTT.AreaJava
created : "SCOTT".CREATE$JAVA$LOB$TABLE
Classes Loaded: 1 #显示加载了一个类
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
- 创建一个PL/SQL封装调用该Java函数(这里出现报错,需重新编译)
SQL> conn scott/password
已连接。
SQL> create or replace function areajava(f float) return float
2 as language java name 'AreaJava.areaj(float) return float';
3 /
函数已创建。
SQL> set serveroutput on;
SQL> col object_name for a20;
SQL> select object_name ,object_type,created,status
2 from user_objects
3 where object_type='FUNCTION';
OBJECT_NAME OBJECT_TYPE CREATED STATUS
-------------------- ------------------- -------------- -------
FINDWEALTHIER FUNCTION 12-3月 -20 VALID
CURSOREMP FUNCTION 10-3月 -20 VALID
AREAJAVA FUNCTION 16-3月 -20 VALID
AREA FUNCTION 12-3月 -20 VALID
SQL> select areajava(8) from dual;
select areajava(8) from dual
*
第 1 行出现错误:
ORA-29516: Aurora 断言失败: Assertion failure at eox.c:359
Uncaught exception System error: java/lang/UnsupportedClassVersionError
报错情况:
Uncaught exception System error: java/lang/UnsupportedClassVersionError
,
由于本机装了多个java版本,oracle的java版本低于环境变量设置的版本
参考《oracle调用JAVA类的方法 》重新编译Java文件并重载Java类到Oracle数据库
D:\app\Administrator\product\11.2.0\dbhome_1\jdk\bin\javac E:\workspace\oracle\src\main\java\AreaJava.java
- 再次调用成功
SQL> create or replace function areajava(f float) return float
2 as language java name 'AreaJava.areaj(float) return float';
3 /
函数已创建。
SQL> select areajava(8) from dual;
AREAJAVA(8)
-----------
200.96
1.4 应用RETURN语句
- RETURN 语句用于立即结束函数的执行,并返回数据
- 一个函数可包含多个RETURN语句
语法示例1:
简单的输入一个数值,输出平方值
#创建函数
create or replace FUNCTION square (original NUMBER ) --参数列表
RETURN NUMBER --函数返回数据类型
AS
-- 声明开始部分
original_squared NUMBER;
BEGIN -- 执行开始部分
original_squared := original * original;
RETURN original_squared; --函数返回数据
#执行函数
BEGIN
DBMS_OUTPUT.PUT_LINE(square(170)); --invocation
END;
/
语法示例2:
多个RETURN语句
SQL> list
1 create or replace function fun2 (n integer)
2 return integer
3 is
4 begin
5 if n =0 then
6 return n+1;
7 elsif n =1 then
8 return n*n;
9 else
10 return n*n*n;
11 end if;
12* end;
SQL> list
1 begin
2 for i in 0..5 loop
3 dbms_output.put_line(fun2(i));
4 end loop;
5* end;
SQL> /
1
1
8
27
64
125
PL/SQL 过程已成功完成。
语法示例3:
创建一个函数,判断输入的id值在Scott模式下的emp表中是否存在,返回boolean值。
SQL> list
1 create or replace function if_id_exist(emp_id in number)
2 return boolean
3 as
4 var_emp_count number;
5 begin
6 select count(*)
7 into var_emp_count
8 from emp
9 where empno = emp_id;
10 return 1 = var_emp_count;
11 exception
12 when others then
13 return false;
14* end if_id_exist;
SQL> /
函数已创建。
SQL> desc if_id_exist;
FUNCTION if_id_exist RETURNS BOOLEAN
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
EMP_ID NUMBER IN
#简单测试下函数能否成功
SQL> l
1 begin
2 if if_id_exist(7654) =false
3 then dbms_output.put_line('not exist');
4 elsif if_id_exist(7654) =true
5 then dbms_output.put_line('it exists');
6* end if; end;
SQL> /
it exists
tips:
复制表格
create table scott.employees
as select employee_id,first_name,last_name
from hr.employees;
使用该函数来判断在向表格插入数据时,是否存在id相同的情况,只有在不存在时才插入数据。
SQL> l
1 CREATE
2 OR REPLACE
3 PROCEDURE insert_emp(
4 emp_id IN NUMBER,
5 emp_sal IN NUMBER,
6 emp_name IN VARCHAR2 ) IS if_id BOOLEAN;
#varchar2这里声明字段类型一直报错,后来发现原来在plsql这个界面不能指定字段长度,否则报错:
# PLS-00103: 出现符号 "("在需要下列之一时:
# := . ) , @ % default
# character
# 符号 ":=" 被替换为 "(" 后继续。
#但是dbeaver可以
7 BEGIN
8 iF_id := if_id_exist(emp_id);
9 IF iF_id THEN dbms_output.put_line('emp_id exist'); #if true then
10 ELSE INSERT
11 INTO
12 employees (empno,sal,ename)
13 VALUES (emp_id,
14 emp_sal,
15 emp_name);
16 END IF;
17* END;
SQL> /
过程已创建。
两种方式查看创建的过程的具体内容
1. SELECT dbms_metadata.get_dDl('PROCEDURE','INSERT_EMP') as pro FROM DUAL;
2. select text from user_source where name='INSERT_EMP';
插入数据测试
SQL> l
1 begin
2 insert_emp(7456,5000,'dal');
3 insert_emp(7453,5000,'dal2');
4* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> select * from employees;
EMPNO SAL ENAME
---------- ---------- ----------
7876 1300 ADAMS
7900 1150 JAMES
7902 3000 FORD
7934 1500 MILLER
7456 5000 dal #新插入数据
7453 5000 dal2 #新插入数据
已选择17行。
#插入empno已存在数据
SQL> begin insert_emp(7566,5000,'dal3'); end ;
2 /
emp_id exist
PL/SQL 过程已成功完成。
1.5 异常处理
在函数语句执行后添加
SQL> l
1 create or replace function show_ename
2 (v_empno emp.empno%type)
3 return varchar2
4 is vename varchar2(10);
5 begin
6 select ename into vename from emp
7 where empno = v_empno;
8 return vename;
# 当where条件语句不满足时,
#vename是没有值可以返回的,
#这里就需要加入异常处理语句
9 exception
10 when no_data_found
11 then
12 return ('the name is not in the emp');
13 when others
14 then
15 return ('error in running shoe_name');
16* end;
SQL> /
函数已创建。
#测试当empno不在emp表中时
SQL> select show_ename(454) from dual;
SHOW_ENAME(454)
---------------
the name is not in the emp
#测试当empno不在emp表中时
SQL> select show_ename(7456) from dual;
SHOW_ENAME(7456)
------------------
the name is not in the emp
#测试当empno在emp表中时
SQL> select show_ename(7879) from dual;
SHOW_ENAME(7879)
-------------
tom