http://www.ll19.com/bb/ ~19~记录点有用的好吗？少说废话少装b~ en Tue, 07 Feb 2012 08:57:46 +0000 http://www.ll19.com/bb/topic/91#post-91 Sun, 03 Jan 2010 15:46:05 +0000 admin 91@http://www.ll19.com/bb/ <p>过程</p> <p>过程是为了执行一定任务而组合在一起的SQL语句和PLSQL语句。（个人理解过程就是ORACLE的程序（或者说是方法、函数））</p> <p>只有输入参数时候的例子</p> <p>创建存储过程</p> <p>创建或替换一个存储过程myproc </p> <p>参数为id ORACLE中参数默认为输入参数 in （之后用is 或者 as 都可以）</p> <p>CREATE or replace procedure myproc(id in varchar2)<br /> Is<br /> 定义一个变量 注意：定义变量一定要给出长度，参数可以不给出长度<br /> Name varchar2(10);<br /> Begin<br /> 将books_name放置到变量里面<br /> Select books_name into Name form books where books_id = id;<br /> Dbms_Output.put_line(Name);<br /> End myproc;</p> <p>执行存储过程</p> <p>Declare<br /> 定义变量<br /> Tid vachar2(10);<br /> Begin<br /> 赋值<br /> Tid := ‘001’;<br /> Myproc(Tid);<br /> End;</p> <p>完整内容：</p> <p>CREATE or replace procedure myproc(idcs in varchar2)<br /> Is<br /> Namebl varchar2(10);<br /> Begin<br /> select name into Namebl from test where id = idcs;<br /> Dbms_Output.put_line(Namebl);<br /> End myproc;</p> <p>Declare<br /> Tid integer(10);<br /> Begin<br /> Tid := 1;<br /> Myproc(Tid);<br /> End;</p> <p>或者 </p> <p>Begin<br /> Myproc(1);<br /> End;</p> <p>JAVA程序</p> <p>import java.sql.*;</p> <p>import java.io.OutputStream;</p> <p>import java.io.Writer;</p> <p>import java.sql.PreparedStatement;</p> <p>import java.sql.ResultSet;</p> <p>import oracle.jdbc.driver.*;</p> <p>public class TestProcedureOne {</p> <p>public TestProcedureOne() {</p> <p>}</p> <p>public static void main(String args ){</p> <p>String driver = "oracle.jdbc.driver.OracleDriver";</p> <p>String strUrl = "jdbc:oracle:thin:@192.168.10.216:1521:ctbu";</p> <p>Statement stmt = null;</p> <p>ResultSet rs = null;</p> <p>Connection conn = null;</p> <p>CallableStatement cstmt = null;</p> <p>try {</p> <p>Class.forName(driver);</p> <p>conn = DriverManager.getConnection(strUrl, "dbname", "password");</p> <p>CallableStatement proc = null;</p> <p>proc = conn.prepareCall("{ call TESTA(?,?) }");</p> <p>proc.setString(1, 1);</p> <p>proc.setString(2, "232323");</p> <p>proc.execute();</p> <p>}</p> <p>catch (SQLException ex2) {</p> <p>ex2.printStackTrace();</p> <p>}</p> <p>catch (Exception ex2) {</p> <p>ex2.printStackTrace();</p> <p>}</p> <p>finally{</p> <p>try {</p> <p>if(rs != null){</p> <p>rs.close();</p> <p>if(stmt!=null){</p> <p>stmt.close();</p> <p>}</p> <p>if(conn!=null){</p> <p>conn.close();</p> <p>}</p> <p>}</p> <p>}</p> <p>catch (SQLException ex1) {</p> <p>}</p> <p>}</p> <p>}</p> <p>} </p>