oracle中Function函数的使用

1.函数调用限制函数示例

1. SQL只能调用带有输入参数,不能带有输出,输入输出函数。
2. SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)。
3. SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句。

2.函数示例

--定义行类型
create or replace type t_operation as object(operation_no NUMBER (2));

--以行类型定义一个表类型
create or replace type t_operation_table is table of t_operation;

--事务性临时表
create global temporary table operation_table
(patient_id VARCHAR2(16),visit_id NUMBER(4),operating_date DATE,operating_end_date DATE,operation_no NUMBER(2))
on commit preserve rows; 

-----示例一
create or replace function ret_emp_sal(v_ename varchar2)
return t_operation_table pipelined
as
v_recode t_operation;
begin
for item in (select OPERATION_NO from operation)
loop
v_recode:=t_operation(item.operation_no);
pipe row(v_recode);
end loop;
return;
end ret_emp_sal;

-----示例二

create or replace function ret_emp_sal(starttime date, endtime date)
return t_operation_table pipelined
as
v_recode t_operation;
begin
insert into operation_table
select distinct o.patient_id,
o.visit_id,
o.operating_date,
o.operating_end_date,
o.operation_no
from pat_visit t, operation o
where t.patient_id = o.patient_id
and t.visit_id = o.visit_id
and o.operation_type = '0'
and t.discharge_date_time >= starttime
and t.discharge_date_time <= endtime;
commit;
for item in (select OPERATION_NO from operation)
loop
v_recode:=t_operation(item.operation_no);
pipe row(v_recode);
end loop;
return;
end ret_emp_sal;

-----示例三

create or replace function f_get_operation(starttime date, endtime date)
return t_operation_table --返回表
pipelined as  --管道函数
pragma autonomous_transaction;  --自治事务
v_recode t_operation;  --行类型
begin

--事务性临时表插入数据
insert into operation_table
select distinct o.patient_id,
o.visit_id,
o.operating_date,
o.operating_end_date,
o.operation_no
from pat_visit t, operation o
where t.patient_id = o.patient_id
and t.visit_id = o.visit_id
and o.operation_type = '0'
and t.discharge_date_time >= starttime
and t.discharge_date_time <= endtime;
for item in (select patient_id, visit_id
from operation_table
group by patient_id, visit_id) loop
declare  --定义参数
i number :=0;
starttime date;
endtime date;
begin
for sub in (select * from operation_table where patient_id = item.patient_id and visit_id = item.visit_id order by operating_date)
loop
if i < 1 then starttime := sub.operating_date; endtime := sub.operating_end_date; v_recode := t_operation(sub.operation_no);
else
if endtime < sub.operating_date then starttime := sub.operating_date; endtime := sub.operating_end_date;
v_recode := t_operation(sub.operation_no);
else continue;
end if;
end if;
end loop;
pipe row(v_recode);
end;
end loop;
commit;
return;
end f_get_operation;

-----示例四

create or replace function sta_deadpatient(starttime date, endtime date)
return varchar2 is
patcount varchar2(20);
begin
declare
sw number := 12;
zz number := 53;
begin
for item in (select a.file_unique_id
FROM jhoutpat_visit t
left join jhmr_file_index a
on t.patient_id = a.patient_id
and t.visit_id = a.visit_id
where a.delete_flag = '0'
and a.mr_code in ('EMR02.00.01_170')
and t.visit_date >= starttime
and t.visit_date <= endtime) loop
declare
z number := 0;
x number := 0;
y number := 0;
s number := 0;
hz VARCHAR2(20);
jr VARCHAR2(20);
cf VARCHAR2(20);
begin
if (item.file_unique_id is not null) then
select count(*)
into z
from jhcdr_emr_data_element m
where m.de_code = 'HZSWSJ'
and m.file_unique_id = item.file_unique_id
and m.s_value <> ' 年 月 日 时 分 ';
if (z > 0) then
select to_char(to_date(s_value, 'yyyy-MM-dd hh24:mi:ss'),
'yyyy-MM-dd hh24:mi:ss')
into hz
from jhcdr_emr_data_element m
where m.file_unique_id = item.file_unique_id
and m.de_code = 'HZSWSJ'
and m.s_value <> ' 年 月 日 时 分 ';
select (case
when m.s_value is null then
'0'
else
to_char(to_date(s_value, 'yyyy-MM-dd hh24:mi:ss'),
'yyyy-MM-dd hh24:mi:ss')
end)
into jr
from jhcdr_emr_data_element m
where m.de_code = 'JRQJSSJ'
and m.file_unique_id = item.file_unique_id
and m.s_value <> ' 年 月 日 时 分 ';
select count(*)
into s
from jhcdr_emr_data_element m
where m.de_code = 'CFJZQJSSJ'
and m.file_unique_id = item.file_unique_id
and m.s_value <> ' 年 月 日 时 分 ';
if (s > 0) then
select (case
when m.s_value is null then
'0'
else
to_char(to_date(s_value, 'yyyy-MM-dd hh24:mi:ss'),
'yyyy-MM-dd hh24:mi:ss')
end)
into cf
from jhcdr_emr_data_element m
where m.de_code = 'CFJZQJSSJ'
and m.file_unique_id = item.file_unique_id
and m.s_value <> ' 年 月 日 时 分 ';
else
cf := '0';
end if;
if (jr != '0' and hz != '0' and
(72 -
round((to_date(hz, 'yyyy-MM-dd hh24:mi:ss') -
to_date(jr, 'yyyy-MM-dd hh24:mi:ss')) * 24)) > 0) then
x := 1;
end if;
if (cf != '0' and jr != '0' and
(72 -
round((to_date(jr, 'yyyy-MM-dd hh24:mi:ss') -
to_date(cf, 'yyyy-MM-dd hh24:mi:ss')) * 24)) > 0) then
y := 1;
end if;
sw := x + sw;
zz := x + zz;
end if;
end if;
end;
end loop;
patcount := concat(concat(to_char(sw), ','), to_char(zz));
end;
return patcount;
end sta_deadpatient;

3.调用函数

1.用select调用函数：

select sta_deadpatient(to_date('2022-01-01','yyyy-mm-dd'),to_date('2022-01-23','yyyy-mm-dd')) as pat from dual；

2.对函数返回参数进行分割

select substr(pat, 0, instr(pat, ',') - 1) as sw, 
       substr(pat, instr(pat, ',') + 1, length(pat)) as qj from
 (select sta_deadpatient(to_date('2022-01-01','yyyy-mm-dd'),to_date('2022-01-23','yyyy-mm-dd')) as pat from dual)；