函数的创建和使用
1不带参数的函数:返回员工人数
CREATE
OR
REPLACE
FUNCTION
SYSTEM.getempcount
RETURN NUMBER
IS
countvar NUMBER ;
BEGIN
SELECT COUNT ( * )
INTO countvar
FROM employee1;
RETURN countvar;
END getempcount
RETURN NUMBER
IS
countvar NUMBER ;
BEGIN
SELECT COUNT ( * )
INTO countvar
FROM employee1;
RETURN countvar;
END getempcount
执行函数
DECLARE
RetVal NUMBER ;
BEGIN
RetVal : = SYSTEM.GETEMPCOUNT;
DBMS_OUTPUT.Put_Line( ' RetVal = ' || TO_CHAR(RetVal));
COMMIT ;
END ;
RetVal NUMBER ;
BEGIN
RetVal : = SYSTEM.GETEMPCOUNT;
DBMS_OUTPUT.Put_Line( ' RetVal = ' || TO_CHAR(RetVal));
COMMIT ;
END ;
2.带参数的函数:通过员工编号获取员工姓名
CREATE
OR
REPLACE
FUNCTION
SYSTEM.GetEmpNameByID (ID employee1.EmpID
%
TYPE)
RETURN varchar2
IS
NameVar varchar2 ( 10 );
BEGIN
SELECT EmpName
INTO NameVar
FROM employee1
WHERE EmpID = ID;
RETURN NameVar;
END GetEmpNameByID;
RETURN varchar2
IS
NameVar varchar2 ( 10 );
BEGIN
SELECT EmpName
INTO NameVar
FROM employee1
WHERE EmpID = ID;
RETURN NameVar;
END GetEmpNameByID;
/
执行函数:
DECLARE
RetVal VARCHAR2 ( 10 );
BEGIN
RetVal : = SYSTEM.GetEmpNameByID( ' 01 ' );
DBMS_OUTPUT.Put_Line( ' RetVal = ' || RetVal);
COMMIT ;
RetVal VARCHAR2 ( 10 );
BEGIN
RetVal : = SYSTEM.GetEmpNameByID( ' 01 ' );
DBMS_OUTPUT.Put_Line( ' RetVal = ' || RetVal);
COMMIT ;