create or replace function Fun_FindNameAndDept
(
/*
Date: Liu JueJue @ June 1, 2007 14:50
Summary: 根据职员ID查找其姓名和所在科室,中间用标志 ‘*’ 隔开
*/
V_EmployeeID number -- Ö°Ô±ID
)
return varchar2
as
DepartmentName Varchar2(100); -- 职员ID
EmployeeName varchar2(100); -- 职员姓名
Begin
DepartmentName := '未知';
EmployeeName := '未知';
select name into EmployeeName from base_employee_property t where t.employee_id = V_EmployeeID;
if EmployeeName <> '未知' then
select ( select l.name from base_dept_property l where l.dept_id = k.dept_id ) into DepartmentName from base_emp_dept_role k where k.employee_id = V_EmployeeID;
end if;
EmployeeName := EmployeeName || '*' || DepartmentName;
return EmployeeName;
End;
create or replace procedure test_20070601_1
(
V_ID In varchar2,
c Out pa_test_20070601.c_sr
/*
create or replace package pa_Test_20070601
is
type c_sr is ref cursor;
end;
*/
)
as
/*
here for define local variables
*/
Begin
open c for
select * from sr_itemtype s where s.id = V_ID;
exception
when others then
rollback;
RAISE_APPLICATION_ERROR(-20001, '查询出错' );
End;
How to create Function/Procedure in Oracle?
最新推荐文章于 2023-07-26 10:37:17 发布