转：Oracle调用函数的方法

参考：https://blog.csdn.net/qq_30934019/article/details/80557576

--表主体：

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

 v_sal emp_pl.sal%type;

begin

 v_sal :=get_sal(7902);

 dbms_output.put_line('7902的工资是'||v_sal);

END;

 /

 

--方法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));