oracle pl/sql 函数

--输入雇员的姓名，返回该雇员的年薪<br>CREATE FUNCTION annual_incomec(uname VARCHAR2)<br>RETURN NUMBER IS <br>annual_salazy NUMBER(7,2);<br>BEGIN <br>   SELECT a.sal*13 INTO annual_salazy FROM emp a WHERE a.ename=uname;<br>   RETURN annual_salazy;<br>END;<br>/

2）、在sqlplus中调用函数 

SQL> var income NUMBER;<br>SQL> call annual_incomec(‘SCOTT‘) into:income;<br>SQL> print income;

3）、在java程序中调用oracle函数：select annual_incomec(‘SCOTT‘) income from dual;

package junit.test;<br><br>import java.sql.Connection;<br>import java.sql.DriverManager;<br>import java.sql.PreparedStatement;<br>import java.sql.ResultSet;<br><br>/**<br> * 演示java程序调用oracle的函数案例<br> * <br> * @author jiqinlin<br> *<br> */<br>public class ProcedureTest {<br><br>    public static void main(String[] args) {<br><br>        try {<br>            // 1.加载驱动<br>            Class.forName("oracle.jdbc.driver.OracleDriver");<br>            // 2.得到连接<br>            Connection ct = DriverManager.getConnection(<br>                    "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");<br>            // 3.创建PreparedStatement<br>            PreparedStatement ps = ct.prepareStatement("select annual_incomec(‘SCOTT‘) annual from dual");<br>            // 4.执行<br>            ResultSet rs=ps.executeQuery();<br>            if(rs.next()){<br>                Float annual=rs.getFloat("annual");<br>                System.out.println(annual);<br>            }<br>            //5、关闭<br>            rs.close();<br>            ps.close();<br>            ct.close();<br>        } catch (Exception e) {<br>            e.printStackTrace();<br>        }<br>    }<br>}

参见：http://www.cnblogs.com/linjiqin/archive/2012/02/24/2367147.html

oracle pl/sql 函数

标签：src   red   out   varchar   包含   body   str   into   drive