函数用于返回特定数据,如果在应用程序中经常需要通过执行SQL语句来返回特定数据,可以基于这些操作创建特定的函数。使用函数不仅可以简化客户端应用程序的开发和维护,还可以提高应用程序的执行性能(特别适用于统计SQL对字典码的频繁转换)。
函数的创建
创建函数语法
create [or replace] function function_name [(parameter_list)]
return datatype
{is/as}
[local_declarations]
begin
executable_statements;
[exception
exception_handlers;]
end;
说明
or repalce:是否覆盖,可选(如果函数存在则覆盖)
function_name:函数名称
parameter_list:参数列表,可选。
return datatype:指定函数的返回类型,不能指定大小。
is/as:选择任一即可
local_declarations:局部变量声明,可选。
executable_statements:要执行的PL-SQL语句。
exception_handlers:异常处理,可选。
建立数据表(字典表)
注: 表空间和索引部分可以不创建
-- Create table
create table SYS_CODE
(
id VARCHAR2(32) not null,
mark VARCHAR2(100) not null,
name VARCHAR2(2048) not null,
value VARCHAR2(255),
type VARCHAR2(255) not null,
parent_id VARCHAR2(32) not null,
sort VARCHAR2(4)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table SYS_CODE
is '数据字典';
-- Add comments to the columns
comment on column SYS_CODE.id
is '主键ID';
comment on column SYS_CODE.mark
is '字典唯一标识';
comment on column SYS_CODE.name
is '字典值';
comment on column SYS_CODE.value
is '字典码值';
comment on column SYS_CODE.type
is '类型 1:字典名,2:字典值';
comment on column SYS_CODE.parent_id
is '父级ID';
comment on column SYS_CODE.sort
is '排序号';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_CODE
add constraint PK_ID_SYS_CODE primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table SYS_CODE
add constraint UK_MARK_SYS_CODE unique (MARK)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
导入测试数据
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('a54ccc62aaa94fd089cac50b2bcc78ba', 'qj_type', '请假类型', null, '1', 'affffc4716424fe3b0dc4ccfdd301946', '1');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('6b1483bb655643c388a4e03ce02843d8', 'qj_type_BJ', '病假', 'BJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '5');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('496de7e06ce34043968c66634ba92230', 'qj_type_BXJ', '补休假', 'BXJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '2');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('60dc633087944ff1a97f6d6c681a6ca2', 'qj_type_CJ', '产假', 'CJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '6');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('20f65b307b7c4d0bb1e54667dd0e1d4b', 'qj_type_HJ', '婚假', 'HJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '4');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('6dafe9de8cfb4505bee2753eda4d733a', 'qj_type_KHJ', '看护假', 'KHJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '7');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('ba549f819c8c4924a190fa2548d52b18', 'qj_type_NJ', '年休假', 'NJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '1');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('069a0204540145819ee1175f0e2bec99', 'qj_type_SJ', '事假', 'SJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '9');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('80072bcaf33c4255b209458942de79a7', 'qj_type_SangJ', '丧假', 'SangJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '8');
insert into sys_code (ID, MARK, NAME, VALUE, TYPE, PARENT_ID, SORT)
values ('964c97a006e147d78eb275b2ecb13059', 'qj_type_TQJ', '探亲假', 'TQJ', '2', 'a54ccc62aaa94fd089cac50b2bcc78ba', '3');
创建函数
CREATE OR REPLACE FUNCTION get_Dict_Name_By_Code(dict VARCHAR2,dict_code VARCHAR2)
RETURN VARCHAR2 IS
dict_name SYS_CODE.NAME%TYPE;
BEGIN
SELECT name into dict_name
FROM sys_code
WHERE mark = dict||'_'||dict_code;
RETURN dict_name;
END ;
函数的使用
传入参数字典key值以及字典码值查询字典值
select get_Dict_Name_By_Code('qj_type','BXJ') 请假类型 FROM DUAL;