sql方面的***********************************************
create table aa
(
a_id number(10) primary key,
a_name varchar2(50),
a_sex varchar2(4),
a_date date,
a_money number(8,2)
)
--查看下是否成功
select * from aa;
--创建触发器
create sequence aa_id_seq increment by 1 start with 1;
--插入一条数据测试一下
insert into aa(a_id,a_name,a_sex,a_date,a_money) values(1,'陈飞龙','男',sysdate,55.55);--成功
insert into aa(a_name,a_sex,a_date,a_money) values('陈飞龙','男',sysdate,55.55);--不成功，有序列但是没自增
--创建触发器
create or replace trigger aa_id_tigger
before insert on aa for each row
begin
if:new.a_id is null
then
select aa_id_seq.nextval into:new.a_id from dual;
end if;
end;
/
--插入一数据进行测试
delete from aa;
insert into aa(a_name,a_sex,a_date,a_money) values('陈小龙','男',sysdate,66.55);--成功
--插入信息的存储过程
create or replace procedure aa_insert_pro
(
b_name in aa.a_name%type,
b_sex in aa.a_sex%type,
b_money in aa.a_money%type
)
as
begin
insert into aa(a_name,a_sex,a_date,a_money) values(b_name,b_sex,sysdate,b_money);
end aa_insert_pro;
/
--删除的存储过程
create or replace procedure aa_delete_pro
(
b_id in number
)
as
begin
delete from aa where a_id = b_id;
end aa_delete_pro;
/
--修改表aa的存储过程
create or replace procedure aa_update_pro
(
b_id in number,
b_name in varchar2,
b_sex in aa.a_sex%type,
b_money in aa.a_money%type
)
as
begin
update aa set a_name = b_name,a_sex = b_sex,a_date = sysdate,a_money = b_money where a_id = b_id;
end aa_update_pro;
/
--查询一条数据的某些个数据字段
create or replace procedure aa_seleceById_pro
(
b_id in number,
b_name out varchar2,
b_sex out varchar2,
b_date out date,
b_money out number
)
as
begin
select a_name,a_sex,a_date,a_money into b_name,b_sex,b_date,b_money from aa where a_id = b_id;
end aa_seleceById_pro;
/
-------------------------------------------------------------------------------
--查询多个数据,步骤1，2
--步骤1：创建程序包
create or replace package aa_package as
type aa_all is ref cursor;
end aa_package;
/
--步骤2：利用程序包创建多查询
create or replace procedure aa_selectMore_pro
(
b_all out aa_package.aa_all
)
as
begin
open b_all for select * from aa;
end aa_selectMore_pro;
/
select * from aa where a_id = 2;
java类文件方面*****************************************************************************************
类DB,连接数据库:
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DB
{
private static Connection con = null;
private static ResultSet rs = null;
private static PreparedStatement pstm = null;
public static Connection getCon()
{
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String uid = "scott";
String pid = "tiger";
try
{
Class.forName(driver);
con = DriverManager.getConnection(url, uid, pid);
} catch (Exception e)
{
e.printStackTrace();
con = null;
}
System.out.println("打开");
return con;
}
public static void closeCon()
{
try
{
if (rs != null)
{
rs=null;
}
if(con != null)
{
con=null;
}
if(pstm != null)
{
pstm=null;
}
} catch (Exception e)
{
e.printStackTrace();
}finally
{
rs =null;
pstm = null;
con = null;
System.out.println("关闭");
}
}
/**
* @param args
*/
public static void main(String[] args)
{
DB db = new DB();
System.out.println(DB.getCon());
DB.closeCon();
}
}
类TESTAA,操作存储过程
package com.chen;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.Date;
import com.db.DB;
/**调用存储过程操作数据库*/
public class TestAA
{
private Connection con = null;
ResultSet rs = null;
CallableStatement cstm = null;
/**增加数据*/
public void insertAA(String name,String sex,double money)
{
try
{
String sql = "call scott.aa_insert_pro(?,?,?)";
if(con == null) con = DB.getCon();
cstm = con.prepareCall(sql);
cstm.setString(1, name);
cstm.setString(2, sex);
cstm.setDouble(3, money);
boolean flag = cstm.execute();
System.out.println("写入完成:"+ flag);
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**删除数据*/
public void deleteAA(int id)
{
try
{
String sql = "call aa_delete_pro(?)";
if(con == null) con = DB.getCon();
cstm = con.prepareCall(sql);
cstm.setInt(1, id);
boolean flag = cstm.execute();
System.out.println("删除结果:"+flag);
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**修改数据*/
public void updateAA(int id,String name,String sex,double money)
{
try
{
String sql = "call aa_update_pro(?,?,?,?)";
if(con == null) con = DB.getCon();
cstm = con.prepareCall(sql);
cstm.setInt(1, id);
cstm.setString(2, name);
cstm.setString(3, sex);
cstm.setDouble(4, money);
int k = cstm.executeUpdate();
boolean flag ;
if(k>0)
{
flag = true;
}else
{flag = false;}
System.out.println("修改结果为:"+flag);
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**查某记录某几个字段*/
public void selectSomeById(int id)
{
try
{
String sql = "call aa_seleceById_pro(?,?,?,?,?)";
if(con == null) con = DB.getCon();
cstm = con.prepareCall(sql);
cstm.setInt(1, id);
cstm.registerOutParameter(2, Types.VARCHAR);
cstm.registerOutParameter(3, Types.VARCHAR);
cstm.registerOutParameter(4, Types.DATE);
cstm.registerOutParameter(5, Types.INTEGER);
cstm.execute();
String name = cstm.getString(2);
String sex = cstm.getString(3);
Date date = cstm.getDate(4);
int money = cstm.getInt(5);
System.out.println("name = "+name+"\tsex = "+sex+"\tdate = "+date+"\tmoney = "+money);
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**查询全部数据*/
public void selectAllAA()
{
try
{
String sql = "call aa_selectMore_pro(?)";
if(con == null) con = DB.getCon();
cstm = con.prepareCall(sql);
cstm.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
// rs = cstm.executeQuery();
cstm.execute();
rs = (ResultSet) cstm.getObject(1);
System.out.println("查询结果:");
// System.out.println(rs);
while(rs.next())
{
// System.out.println("a_id = "+rs.getInt(1)+"\ta_name = "+rs.getString(2)+"\ta_sex = "+rs.getString(3)+"\ta_date = "+rs.getDate(4)+"\ta_money"+rs.getDouble(5));
System.out.println("-*****************************************************************--------------***********");
System.out.println("a_id = "+rs.getInt("a_id")+"\ta_name = "+rs.getString("a_name")+"\ta_sex = "+rs.getString("a_sex")+"\ta_date = "+rs.getDate("a_date")+"\ta_money"+rs.getDouble("a_money"));
}
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**
* @param args
*/
public static void main(String[] args)
{
TestAA ta = new TestAA();
// ta.insertAA("朱老三", "男", 99.63);
// ta.insertAA("朱传文", "男", 869.63);
// ta.insertAA("王小丫", "女", 299.63);
// ta.insertAA("王大拿", "男", 199.63);
// ta.deleteAA(9);
ta.updateAA(5, "覃媚媚", "女", 594.21);//--还存在问题
ta.selectSomeById(5);
// ta.selectAllAA();
}
}
