Oracle ~ 创建函数

create or replace function sf_score_pm(
		p_in_stuid in varchar2,--学号
		p_in_courseid in  varchar2 --课程ID
)
return number
is
		ls_pm number:=0;
		ls_score number:=0;
begin
  --获取该学生的成绩
	  select t.score into ls_score from score t
	   where t.stuid = p_in_stuid
	     and t.courseid = p_in_courseid;
  --获取成绩比该学生高的人数
	  select count(1) into ls_pm from score t
	   where t.courseid = p_in_courseid
	   and  t.score>ls_score;
   --得到该学生的成绩排名
 	  ls_pm:=ls_pm+1;
	   return ls_pm;
exception
  when no_data_found  then
     dbms_output.put_line('该学生的课程：'||p_in_courseid|| '的成绩在成绩表中找不到');
end;

CREATE OR REPLACE FUNCTION DayString_to_DayNumber (
   		 DayString NCHAR
) RETURN NUMBER IS
   		 DayNumber   NUMBER(38,0);
  		  n      NUMBER(38,0);
BEGIN
    IF
        instr(DayString,'W') > 0
    THEN
        n := to_number(replace(DayString,'W','') );
        DayNumber := n * 5;END IF;

    RETURN DayNumber;
END;

CREATE OR REPLACE FUNCTION F_GET_DIFF_TIME
(
			START_TIME IN DATE,
            END_TIME   IN DATE
)
  			RETURN VARCHAR2 IS
  			DIFF_TIME VARCHAR2(50);
BEGIN
  SELECT TDAY || '天' || THOUR || '时' || TMINUTE || '分' || ROUND((TT - TMINUTE) * 60) || '秒' INTO DIFF_TIME
  FROM (
  			SELECT TDAY,
                 THOUR,
                 TRUNC((TT - THOUR) * 60) TMINUTE,
                 (TT - THOUR) * 60 TT
            FROM (
            		SELECT TDAY,
                         TRUNC((TT - TDAY) * 24) THOUR,
                         (TT - TDAY) * 24 TT
                    FROM (
                    		SELECT TO_NUMBER(END_TIME - START_TIME) AS TT,
                                 TRUNC(TO_NUMBER(END_TIME - START_TIME)) AS TDAY
                            FROM (SELECT START_TIME, END_TIME FROM dual))));

  RETURN DIFF_TIME;
END;