SQL> create or replace procedure sp_guocheng1 is--如果有这个名字就替换

  2  begin--执行部分

  3  insert into guocheng values('liyifeng','liyifeng');

  4  end;

  5  /  --执行的意思

 

Procedure created

 

 

显示错误

SQL> show error;

Errors for PROCEDURE LIYIFENG.SP_GUOCHENG1:

 

LINE/COL ERROR

-------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

--------------------------------------

3/22     PL/SQL: ORA-00928: 缺失 SELECT 关键字

3/1      PL/SQL: SQL Statement ignored

6/0      PLS-00103: 出现符号 "end-of-file"在需要下列之一时：   ( begin case     declare end exception exit for goto if loop mod null pragma     raise return select update while

 

 

 

with <an identifier>     <a double-quoted delimited-identifier> <a bind variable> <<     continue close current delete fetch lock insert open rollback     savepoint set sql

 

execute commit forall merge pipe purge 

 

 

执行过程

 

SQL> exec sp_guocheng1;

 

PL/SQL procedure successfully completed

 

SQL> commit;

 

 

 

 

一个新的快

SQL> set serveroutput on;--打开输出选项

SQL>

SQL> begin

  2    dbms_output.put_line('hello,world');--dbms_output是包.put_line是过程

  3  end;

  4  /

 

hello,world

 

PL/SQL procedure successfully completed

 

 

 

 

带有变量的查询

SQL> declare

  2  v_ename varchar2(25);

  3  v_id number;

  4  begin

  5    select name,id into v_ename,v_id from stu where name=&aa;

  6    dbms_output.put_line('用户名是：'||v_ename||v_id );

  7  end;

  8  /

 

用户名是：liyifeng            0

 

PL/SQL procedure successfully completed

 

带有类外的查询

SQL> declare

  2  v_ename varchar2(25);

  3  v_id number;

  4  begin

  5    select name,id into v_ename,v_id from stu where name=&aa;

  6    dbms_output.put_line('用户名是：'||v_ename||v_id );

  7  exception

  8    when no_data_found then

  9      dbms_output.put_line('您输入的数据不存在！');

 10  end;

 11  /

 

您输入的数据不存在！

 

PL/SQL procedure successfully completed

 

 

 

 

--传入参数的过程  注意，定义参数的时候不能带上长度。

create procedure sp_gc1(v_id number,v_name varchar2) is

begin

  update stu set name=v_name where id=v_id;

end;

 

 

 

 

过程在java中运行。。注意jdbc_oracle架包。。

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

 

 

 

public class testExec {

 

 

 

 /**

  * @param args

  */

 public static void main(String[] args) {

  // TODO Auto-generated method stub

 

 

 

  Connection ct=null;

  try{

   //加载驱动

//  Class.forName("com.hxtt.sql.access.AccessDriver");//连接access的

  Class.forName("oracle.jdbc.driver.OracleDriver");

  //创建连接

  ct=DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.9:1521:orcl","liyifeng","liyifeng");

  //创建 callablestatement

  CallableStatement cs=ct.prepareCall("{call sp_gc1(?,?)}");

  //给？复制

  cs.setInt(1, 0);

  cs.setString(2, "mountLee");

  //设置不自动提交事务

  ct.setAutoCommit(false);

  

  cs.execute();

  

  //提交事务

  ct.commit();

  //关闭资源

  cs.close();

  ct.close();

  }

  catch (Exception e){

   try {

   //如果失败，就回滚

   ct.rollback();

   }

   catch(Exception ex){

ex.printStackTrace();}

   

   e.printStackTrace();

  }

 }

 

 

 

}

 

 

 

 

 

--传入参数并有返回值的函数

create or replace function sp_guoc(v_id number) 

return varchar2 is v_name varchar2(20);

begin

  select name into v_name from stu where id=v_id;

return v_name;

end;

 

 

 

 

 

 

 

java中调用函数

 

Statement sm=ct.createStatement();

  ResultSet rs=sm.executeQuery("select sp_guoc(0) from stu");

  if(rs.next()){

  System.out.print(rs.getString(1));}

 

 

 

 

 

 

--创建包

create or replace package sp_pack is

procedure sp_pd (v_id number,v_name varchar2);

function  sp_ft (v_id number) return number;

end;

--创建包体

create or replace package body sp_pack is

procedure sp_pd(v_id number,v_name varchar2) is

begin

  update stu set name=v_name where id=v_id;

end;

function sp_ft(v_id number)

return number is v_name number;

begin

  select id into v_name from stu where id=v_id;

return v_name;

end;

end;

 

 

执行包中的过程

 

SQL> exec sp_pack.sp_pd(0,'lee');

 

PL/SQL procedure successfully completed

 

SQL> commit;

 

Commit complete

 

 

 

 

java中执行包中函数的方法。

 

Statement sm=ct.createStatement();

  ResultSet rs=sm.executeQuery("select sp_pack.sp_ft(0) from stu");

  if(rs.next()){

  System.out.print(rs.getString(1));}