oracle的存储过程和存储函数

1.存储过程和存储函数的区别

（1）返回值：存储函数必须有一个通过return得到的返回值，而多个通过输出参数得到的返回值；存储过程只有通过输出参数得到的返回值。

存储函数：create or replace function fun1(参数1，参数2，参数...)  

　　　　　return 返回类型

　　　　　is

　　　　　--定义变量

　　　　　begin

　　　　    ....执行语句

　　　　　end

存储过程：create or replace procedure pro1(参数1，参数2，参数...)

　　　　　is

　　　　　--定义变量

　　　　   begin

　　　　　　--执行语句

　　　　　end

（2）调用的方式不同：函数可以直接在sql语句中调用，但是过程必须单独使用。

　　如：select fun1 from dual;    -----在sql语句中调用函数。

（3）用途不一样：函数一般是用来返回一个计算结果，过程是用来完成特定的数据操作。

2.存储过程及其调用

（1）创建一个无参数的存储过程

　　create or replace procedure proce1 is
　　begin 
　　　　dbms_output.put_line('HelloWorld!');
　　end;

　　调用方式1：exec proce1;

　　调用方式2：

　　　　　　begin 
　　　　　　　　proce1;
　　　　　　end;

（2）创建一个有输入参数的存储过程

　　create or replace procedure proce2(v_empno in emp.empno%type,
　　　　　　　　　　　　　　　　　　v_ename in emp.ename%type,
　　　　　　　　　　　　　　　　　　v_sal in emp.sal%type)  

　　 is
　　begin
　　　　insert into emp(empno,ename,sal) values(v_empno,v_ename,v_sal);
　　end;

　　调用方式1：

　　　　begin
　　　　　　proce2('111','qaz',2000);
　　　　　　proce2('222','wsx',1020);
　　　　　　proce2('333','edc',3300);
　　　　end;

　　调用方式2：

　　exec  proce2('111','qaz',2000);

　　exec  proce2('222','wsx',1020);

　　exec  proce2('333','edc',3300);

（3）创建一个有输入和输出参数的存储过程

　　create or replace procedure pro_query_enameAndSal_by_empno(v_empno in emp.empno%type
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　,v_ename out emp.ename%type
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　,v_sal out emp.sal%type)

　　is 
　　begin
　　　　select ename,sal into v_ename,v_sal from emp where empno = v_empno;
　　end;

　　调用存储过程：
　　declare
　　　　p_ename emp.ename%type;
　　　　p_sal emp.sal%type;
　　begin
　　　　pro_query_enameAndSal_by_empno(7369,v_ename=>p_ename,v_sal=>p_sal);
　　　　dbms_output.put_line('员工号为7369的员工是'||p_ename||'薪水是'||p_sal);
　　end;

　　java调用存储过程：

　　String sql = "{call pro_query_enameAndSal_by_empno(?,?,?)}";
　　cs = ct.prepareCall(sql);
　　cs.setInt(1, 7369);
　　cs.registerOutParameter(2, OracleTypes.VARCHAR);
　　cs.registerOutParameter(3, OracleTypes.INTEGER);
　　cs.execute();
　　String ename = cs.getString(2);
　　String sal = cs.getString(3);
　　System.out.println("员工姓名是"+ename+"工资是"+sal);

3.存储函数及其调用

（1）创建一个无参数存储函数：

　　create or replace function fun_hello
　　return varchar2 is
　　begin
　　　　return 'hello world';
　　end;

　　调用方式1（sql语句中进行调用）：select fun_hello from dual;

　　调用方式2：

　　　　declare
　　　　str varchar2(20);
　　　　begin
　　　　　　str :=fun_hello;
　　　　　　dbms_output.put_line(str);
　　　　end;

（2）创建一个有输入参数的存储函数

　　create or replace function fun_get_annualSal_by_empno(f_empno in emp.empno%type)
　　return number
　　is
　　v_sal emp.sal%type;
　　v_comm emp.comm%type;
　　begin
　　　　select sal,comm into v_sal,v_comm from emp where empno = f_empno;
　　　　return v_sal*12+nvl(v_comm,0);
　　end;

　　调用：select fun_get_annualSal_by_empno(7369) from dual; 

（3）创建一个有输入和输出的存储函数

　　create or replace function fun_get_annualSal_by_empno1(v_empno in emp.empno%type,
　　v_ename out emp.ename%type,v_comm out emp.comm%type) 
　　return number
　　is
　　v_sal emp.sal%type;
　　begin
　　　　select ename,sal,nvl(comm,0) into v_ename,v_sal,v_comm from emp where empno = v_empno;
　　　　return v_sal*12+v_comm;
　　end;

　　调用：

　　declare
　　　　v_annualSal number(10,2);
　　　　v_ename emp.ename%type;
　　　　v_comm emp.comm%type;
　　begin
　　　　v_annualSal := fun_get_annualSal_by_empno1(7499,v_ename,v_comm);
　　　　dbms_output.put_line('员工姓名是'||v_ename||'奖金是'||v_comm||'年薪是'||v_annualSal);
　　end;

　　用java操作该存储函数：　

　　cs = ct.prepareCall("{? = call fun_get_annualSal_by_empno1(?,?,?)}");
　　//首先要注册函数返回值
　　cs.registerOutParameter(1, OracleTypes.DOUBLE);
　　cs.setInt(2, 7499);
　　cs.registerOutParameter(3, OracleTypes.VARCHAR);
　　cs.registerOutParameter(4, OracleTypes.DOUBLE);
　　//【注意】注册完成必须要执行！！！
　　cs.execute();
　　System.out.println("员工姓名是"+cs.getString(3)+"薪水是"+cs.getDouble(1)+"奖金是"+cs.getDouble(4));