Oracle中调用函数的5种方法

--表主体：

SQL> SELECT * FROM emp_pl;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1000.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

--函数主体：

create or replace function get_sal
(v_id IN emp_pl.empno%TYPE)
return number
is
    v_salary        emp_pl.sal%TYPE   :=0;
begin
    SELECT sal INTO v_salary
    FROM emp_pl
    WHERE empno=v_id;
    RETURN v_salary;
end get_sal;

--方法1.PLSQL代码块

SQL> set serveroutput on
SQL> declare
  2    v_sal emp_pl.sal%type;
  3  begin
  4    v_sal :=get_sal(7902);
  5    dbms_output.put_line('7902的工资是'||v_sal);
  6  END;
  7  /

--方法2.execute

SQL> variable g_salary number
SQL> execute  :g_salary     :=get_sal(7902)    --不用分号

--方法3.call

SQL> var get_salary1 number
SQL> call get_sal(7902) into :get_salary1;    --需要用分号
--variable同var

--方法4.select

SQL> select get_sal(7902) from dual;

--方法5.将函数作为另一个子程序的参数
 

SQL> execute dbms_output.put_line(get_sal(7902));