存储过程增删改查

存储过程查询
建过程
1.建个游标
CREATE OR REPLACE PACKAGE MYPACKAGE   AS   


TYPE MY_CURSOR IS REF CURSOR;   


end MYPACKAGE;
2.建过程
create or replace PROCEDURE pro_3(p_CURSOR OUT MYPACKAGE.mycursor)
IS
BEGIN
  OPEN P_CURSOR FOR SELECT * FROM REGIST;
  
END PRO_3;
3在xml里写个配置
  <select id="sss" statementType="CALLABLE" >
   {call SCOTT.pro_3(#{pro_31,mode=OUT,jdbcType=CURSOR,resultMap=BaseRegistMap,javaType=java.sql.ResultSet})}
  </select>
4.在MapperInterger里建个方法
 List<Regist>sss(Map<String, Object>map);
5.返回个list的集合
sqlSession.getMapper(RegistMapper.class).sss(map);
List<Regist> dlist=(List<Regist>)map.get("pro_31");
6.查询
RegistMapper mapper=new RegistMapperImpl();
Map<String,Object> map=new HashMap<String, Object>();
System.out.println(mapper.sss(map));


添加
1.过程
create or replace PROCEDURE REGISTHAHA(
Apeopleid IN VARCHAR,
Aname IN VARCHAR
)
AS 
BEGIN
INSERT INTO REGIST(PEOPLEID,NAME) VALUES(Apeopleid,Aname);
END REGISTHAHA;
2.配置
<insert id="inserRegist2" statementType="CALLABLE" parameterType="com.oracle.vo.Regist">
  {call registhaha(#{peopleid,jdbcType=VARCHAR},#{name,jdbcType=VARCHAR})}
  </insert>
3.执行语句
Regist a=new Regist();
a.setPeopleid("111113");
a.setName("lixiao");
int b = session2.insert("inserRegist2",a);
删除
1.加过程
create or replace PROCEDURE REGISTDELETE(ID in VARCHAR)
AS 
BEGIN
delete from regist where peopleid=ID;


END REGISTDELETE;
2.配置
  <delete id="aaaa" statementType="CALLABLE" parameterType="java.lang.String">
  {call REGISTDELETE(#{id,jdbcType=VARCHAR})}
  </delete>
 3.执行语句
session2.delete("aaaa","FK213"); 
* session2.commit();


更新
1.过程
create or replace PROCEDURE REGISTDELETE(Apeopleid IN VARCHAR,
Aname IN VARCHAR)
AS
BEGIN 
UPDATE SET REGIST (PEOPLEID IN VARCHAR,ANAME IN VARCHAR) VALUES(Apeopleid,Aname)
END REGISTDELETE;
2.配置
  <update id="updateRegist111"  statementType="CALLABLE" parameterType="com.oracle.vo.Regist">
  {call REGISTUPDATE(#{peopleid,jdbcType=VARCHAR},
  #{name,jdbcType=VARCHAR},
  #{idCardno,jdbcType=VARCHAR})}
  </update>
3.
执行语句
Regist r=new Regist();
r.setPeopleid("111113");
r.setName("hehe");
r.setIdCardno("hehehe");
session2.update("updateRegist111",r);
session2.commit();