Oracle Custom Functions

CREATE TABLE STUDENT (--Creating student tables
ID Number (TEN) PRIMARY key,--primary Key ID
SNAME VARCHAR2 (20),
CLASSNAME VARCHAR2 (20)--Class
);

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 ');

--Create function return total number of 3G classes

CREATE OR REPLACE FUNCTION fun_num
(In_cname in Classtab. Cname%type)--parameters received by the function
Return number each function must return a value
As
Out_num number; --Define the returned variable Out_num receive the value of the query
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; --End condition
RETURN Out_num; Return the value out
End fun_num;--function
--Call function
SELECT DISTINCT fun_num (' 3G ') as number from STUDENT;

Oracle Custom Functions