Oracle中调用函数的方法

创建表

create table EMP
(
  empno VARCHAR2(16) not null,
  ename VARCHAR2(16) not null,
  sal   NUMBER,
  comm  NUMBER,
  job   VARCHAR2(16),
  deptno NUMBER
);

添加数据

insert into EMP (empno, ename, sal, comm, job, deptno) values ('7839', 'xiaoming', 1200, 500,  'Actor', 12);
insert into EMP (empno, ename, sal, job, deptno) values ('7566', 'xiaowang', 800, 'Lawyer', 12);
commit;

创建函数

create or replace function nian_xin(pempno in number)
return number
as
psal emp.sal%type;
pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno = pempno;
return psal*12+nvl(pcomm,0);
end;
/

 

1、PLSQL代码块

SQL>  set serveroutput on 
SQL> declare
  2  v_a emp.sal%type;
  3  v_b emp.sal%type;
  4  begin
  5    v_a:=nian_xin(7839);
  6    v_b:=nian_xin(7566);
  7     dbms_output.put_line('年薪：'||v_a);
  8     dbms_output.put_line('年薪：'||v_b);
  9  end;
 10  /
年薪：14900
年薪：9600
PL/SQL procedure successfully completed

2、execute

SQL> variable v_a number
SQL> execute  :v_a     :=nian_xin(7839)
PL/SQL procedure successfully completed
v_a
---------
14900

3、call

SQL> var v_a1 number
SQL>  call nian_xin(7839) into :v_a1;
Method called
v_a1
---------

4、select （这里的表可以随便选，表中有多少条数据会有打印多少条数据）

SQL> select nian_xin(7566) from emp;
NIAN_XIN(7566)
--------------
          9600
          9600

5、函数的嵌套使用

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