oracle 自定义函数

CREATE TABLE STUDENT( --创建学生表
  ID NUMBER(10) PRIMARY KEY,   --主键ID
  SNAME VARCHAR2(20),
  CLASSNAME VARCHAR2(20) --班级
);

INSERT INTO STUDENT VALUES(1,'Tom',‘SVSE’);
INSERT INTO STUDENT VALUES(2,'Jack','GIS');
INSERT INTO STUDENT VALUES(3,'Bay','3G');
INSERT INTO STUDENT VALUES(4,'John','SVSE');
INSERT INTO STUDENT VALUES(5,'Dld','3G');

 

--创建函数  返回3G班的总人数

CREATE OR REPLACE FUNCTION FUN_NUM
  (IN_CNAME IN CLASSTAB.CNAME%TYPE)  --函数接收的参数
RETURN NUMBER   每个函数必须返回一个值
  AS   
    OUT_NUM NUMBER;  --定义返回的变量  out_num接收查询出来的值
BEGIN
  IF IN_CNAME ='3G' THEN
      SELECT COUNT(CNAME) INTO OUT_NUM FROM STUDENT WHERE CNAME='3G';
    ELSIF IN_CNAME='SVSE' THEN
      SELECT COUNT(CNAME) INTO OUT_NUM FROM STUDENT WHERE CNAME='SVSE';
    ELSE IN_CNAME = 'GIS' THEN
      SELECT COUNT(CNAME) INTO OUT_NUM FROM STUDENT WHERE CNAME='GIS';
  END IF;  --结束条件
RETURN OUT_NUM; 将值返回出去
END FUN_NUM;--结束函数
--调用函数
SELECT DISTINCT FUN_NUM('3G') AS 人数 from STUDENT;