转：Oracle调用函数的方法

Myth丶恋晨 2022-05-11 01:52 181阅读 0赞

参考：[https://blog.csdn.net/qq\_30934019/article/details/80557576][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));

[https_blog.csdn.net_qq_30934019_article_details_80557576]: https://blog.csdn.net/qq_30934019/article/details/80557576