oracle 方法函数,Oracle中调用函数的5种方法

--表主体：sql

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代码块code

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.executeserver

SQL> variable g_salary number

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

--方法3.callget

SQL> var get_salary1 number

SQL> call get_sal(7902) into :get_salary1; --须要用分号

--variable同var

--方法4.selectio

SQL> select get_sal(7902) from dual;

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

function

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