How to create Function/Procedure in Oracle?

 

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;