少年阿賓

第一部分：關于delete的觸發(fā)器執(zhí)行
CREATE TABLE `abin5` (
  `id` int(11) DEFAULT NULL,
  `name5` varchar(39) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table abin6 like abin5; 

建立觸發(fā)器：

CREATE TRIGGER `tabin1` BEFORE DELETE ON `abin5` FOR EACH ROW begin
 declare id int default 0;
 declare name varchar(100) default null;
 set id=OLD.id;
 set name=OLD.name5;
 insert into abin6 values (id,name);
end;

測試語句：
delete from abin5 where id='1';
然后查看abin6表里面，必定是新增了一條和abin5表里面刪除掉的一模一樣的記錄。





第一部分：關于insert的觸發(fā)器執(zhí)行
建表：
create table abing(
id int not null auto_increment,
name varchar(100),
constraint pk primary key(id)
)

建立觸發(fā)器：（用于主鍵自增）
create trigger tabing before insert on abing for each row
begin
 set @id=new.id;
end

執(zhí)行insert語句：
insert into abing (name) values ('abing');

查看結(jié)果：
select * from abing

呵呵，結(jié)果是自然主鍵自增了。





第一部分：關于update的觸發(fā)器執(zhí)行
建表：
create table abing(
id int not null auto_increment,
name varchar(100),
sid int,
constraint pk primary key(id)
)

建立觸發(fā)器：
create trigger tabing before update on abing for each row
begin
 set @old=old.sid;
 set @new=new.sid;
end





參考資料：http://database.51cto.com/art/201010/229404.htm

1、建立存儲過程
create procedure pabin4()
BEGIN
declare status,id int;
declare az,ax varchar(100) default '';
declare mycur cursor for select * from abin5;
declare continue handler for not found set status=1;
open mycur;
 set status=0;
 loopLabel:LOOP

  fetch mycur into id,az;

  if status = 0 then
   if az is not null then
    set ax=CONCAT(az,'mycur');
   else
    set ax=CONCAT('mysql','mycur');
   end if;
   insert into abin5 values (id,ax);
  end if;

  if status = 1 then
   leave loopLabel;
  end if;

  end loop;
close mycur;
END

 

 

 2、測試語句

call pabin4()
 

在MySQL存儲過程的語句中有三個標準的循環(huán)方式：WHILE循環(huán)，LOOP循環(huán)以及REPEAT循環(huán)。還有一種非標準的循環(huán)方式：GOTO，不過這種循環(huán)方式最好別用，很容易引起程序的混亂，在這里就不錯具體介紹了。

這幾個循環(huán)語句的格式如下：
WHILE……DO……END WHILE
REPEAT……UNTIL END REPEAT
LOOP……END LOOP
GOTO。

1、建立存儲過程
create procedure pabin2()
BEGIN
declare i int ;
set i=0;
while i<5 do
 insert into abin5 values (i,CONCAT('while',i));
 set i=i+1;
end while;
END
測試語句：
call pabin2()


2、建立存儲過程：
create procedure pabin3()
BEGIN
declare i int ;
declare az varchar(100) default '';
set i=0;
repeat
 set az=CONCAT('repeat',i);
 insert into abin5 values (i,az);
 set i=i+1;
 until i>=5
end repeat;
end

測試語句：
call pabin3()


3、建立存儲過程：
create procedure pabin1()
begin
declare i int default 0;
declare av varchar(100) default '';
loop_label:LOOP
 if i=3 then
  set i=i+1;
  iterate loop_label;
 end if;
 set av=CONCAT('loop',i);
 insert into abin5 values (i,av);
 set i=i+1;
 if i>=5 then
  leave loop_label;
 end if;
end loop;
end;



測試語句：
call pabin1()

     摘要: oracle的函數(shù)很多﹐特整理出來﹕ abs(m)  m的絕對值mod(m,n) m被n除后的余數(shù)power(m,n) m的n次方round(m[,n]) m四舍五入至小數(shù)點后n位的值（n缺省為0）trunc(m[,n]) m截斷n位小數(shù)位的值（n缺省為0） 字符函數(shù)： initcap(st) 返回st將每個單詞的首字母大...  閱讀全文

-- Create table
create table ABIN1
(
  ID1         NUMBER,
  NAME1       NVARCHAR2(100),
  CREATETIME1 DATE default sysdate
)

create or replace function getFunction(myname varchar2)
return sys_refcursor
as

v_sql varchar2(4000);
mylist sys_refcursor;
begin
       v_sql:='select * from abin1 where name1=:1';
       open myList for v_sql using myname;
       return(myList);
end;

package com.abin.lee.db.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class OracleConnection {
 private static final String USER="abin";
 private static final String PWD="abin";
 private static final String URL="jdbc:oracle:thin:@localhost:1521:XE";
 private static final String DRIVER="oracle.jdbc.driver.OracleDriver";
 private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>(){
  protected Connection initialValue(){
   Connection conn=null;
   try {
    Class.forName(DRIVER);
    if(null==conn||conn.isClosed()){
     conn=DriverManager.getConnection(URL, USER, PWD);
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
   return conn;
  }
  
 };
 
 
 public static Connection getConnection() throws ClassNotFoundException, SQLException{
  Connection conn=null;
  if(threadLocal.get()==null){
   Class.forName(DRIVER);
   conn=DriverManager.getConnection(URL, USER, PWD);
   threadLocal.set(conn);
  }else {
   return threadLocal.get();
  }
  return conn;
 }

}

package com.abin.lee.db.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import junit.framework.TestCase;
import oracle.jdbc.OracleTypes;

public class GetFunctionResultSet extends TestCase{
 public void test(){
  Connection conn=null;
  CallableStatement cs=null;
  ResultSet rs=null;
  try {
   conn=OracleConnection.getConnection();
 /*此處是關鍵，不要寫錯了。第一個問號代表返回值，getFunction（）括號里面是function的輸入?yún)?shù)。*/  
   cs=conn.prepareCall("{?=call getFunction(?)}");
   cs.registerOutParameter(1, OracleTypes.CURSOR);
   cs.setString(2, "a1");
   cs.execute();
   rs=(ResultSet)cs.getObject(1);
   while(rs.next()&&rs!=null){
    System.out.println("ID="+rs.getInt("id1"));
    System.out.println("NAME="+rs.getString("name1"));
    System.out.println("NAME="+rs.getString("createtime1"));
   }
   
  } catch (Exception e) {
   e.printStackTrace();
  }
  /**
   * create or replace function getFunction(myname varchar2)
   return sys_refcursor
   as
   v_sql varchar2(4000);
   mylist sys_refcursor;
   begin
          v_sql:='select * from abin1 where name1=:1';
          open myList for v_sql using myname;
          return(myList);
   end;
   */
  
 }
}

參考資料：
http://lanselixiangxc.bokee.com/267623.html

-- Create table
create table ABIN1
(
  ID1         NUMBER,
  NAME1       NVARCHAR2(100),
  CREATETIME1 DATE default sysdate
)

create or replace procedure getList(myname varchar2,mylist out sys_refcursor)
is
v_sql varchar2(4000);
begin
      v_sql:='select * from abin1 where name1=:1';
       open myList for v_sql using myname;
end;

package com.abin.lee.db.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class OracleConnection {
 private static final String USER="abin";
 private static final String PWD="abin";
 private static final String URL="jdbc:oracle:thin:@localhost:1521:XE";
 private static final String DRIVER="oracle.jdbc.driver.OracleDriver";
 private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>(){
  protected Connection initialValue(){
   Connection conn=null;
   try {
    Class.forName(DRIVER);
    if(null==conn||conn.isClosed()){
     conn=DriverManager.getConnection(URL, USER, PWD);
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
   return conn;
  }
  
 };
 
 
 public static Connection getConnection() throws ClassNotFoundException, SQLException{
  Connection conn=null;
  if(threadLocal.get()==null){
   Class.forName(DRIVER);
   conn=DriverManager.getConnection(URL, USER, PWD);
   threadLocal.set(conn);
  }else {
   return threadLocal.get();
  }
  return conn;
 }

}

package com.abin.lee.db.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import junit.framework.TestCase;
import oracle.jdbc.OracleTypes;

public class GetProcedureResultSet extends TestCase{
 public void test(){
  Connection conn=null;
  CallableStatement cs=null;
  ResultSet rs=null;
  try {
   conn=OracleConnection.getConnection();
   cs=conn.prepareCall("{call getList(?,?)}");
   cs.setString(1, "a1");
   cs.registerOutParameter(2, OracleTypes.CURSOR);
   cs.execute();
   rs=(ResultSet)cs.getObject(2);
   while(rs.next()&&rs!=null){
    System.out.println("ID="+rs.getInt("id1"));
    System.out.println("NAME="+rs.getString("name1"));
    System.out.println("NAME="+rs.getString("createtime1"));
   }
   
  } catch (Exception e) {
   e.printStackTrace();
  }
  
  
 }
 /**
  * create or replace procedure getList(myname varchar2,mylist out sys_refcursor)
  is
  v_sql varchar2(4000);
  begin
        v_sql:='select * from abin1 where name1=:1';
         open myList for v_sql using myname;
  end;
  */
}

http://tianzt.blog.51cto.com/459544/171759/

/**
 * UserBean entity. @author MyEclipse Persistence Tools
 */

public class UserBean implements java.io.Serializable {

 // Fields

 private String id1;
 private String name1;
 private java.util.Date createtime1;
 private String address;

 // Constructors

 /** default constructor */
 public UserBean() {
 }

 /** minimal constructor */
 public UserBean(String id1) {
  this.id1 = id1;
 }

 /** full constructor */
 public UserBean(String id1, String name1, java.util.Date createtime1, String address) {
  this.id1 = id1;
  this.name1 = name1;
  this.createtime1 = createtime1;
  this.address = address;
 }

 // Property accessors

 public String getId1() {
  return this.id1;
 }

 public void setId1(String id1) {
  this.id1 = id1;
 }

 public String getName1() {
  return this.name1;
 }

 public void setName1(String name1) {
  this.name1 = name1;
 }

 public java.util.Date getCreatetime1() {
  return this.createtime1;
 }

 public void setCreatetime1(java.util.Date createtime1) {
  this.createtime1 = createtime1;
 }

 public String getAddress() {
  return this.address;
 }

 public void setAddress(String address) {
  this.address = address;
 }

}



<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"<!--
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="com.lcdbtsf.air.po.UserBean" table="ABIN5" schema="ABIN" dynamic-update="true">
        <id name="id1" type="string">
            <column name="ID1" precision="0" />
            <generator class="assigned" />
        </id>
        <property name="name1" type="string">
            <column name="NAME1" default="defaultname1"/>
        </property>
        <property name="createtime1" type="java.util.Date">
            <column name="CREATETIME1" length="19" />
        </property>
        <property name="address" type="string" update="true"  access="field" insert="true">
            <column name="ADDRESS" default="defaultaddress" />
        </property>
    </class>
   
    <query name="getUserBeanByAddress">
     <![CDATA[select o from UserBean o where o.address=:address order by o.createtime1 desc]]>
    </query>
   
     <!-- 命名查詢中使用sql，不推薦使用，影響跨數(shù)據(jù)庫
    <sql-query name="getUserBeanByAddress">
     <![CDATA[select o from UserBean o where o.address=:address order by o.createtime1 desc]]>
    </sql-query>
    -->
   
</hibernate-mapping>

package com.abin.lee.hibernate;

import java.util.List;

import junit.framework.TestCase;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Order;

import com.lcdbtsf.air.po.UserBean;

public class UserDao extends TestCase {

 public  List<UserBean> getAll(){
  Configuration cfg = new Configuration().configure(); // 獲取hibernate的配置信息
  SessionFactory sf = cfg.buildSessionFactory(); // 根據(jù)config建立sessionFactory
  Session session = sf.openSession(); // factory用于建立session，開啟Session，相當于開啟JDBC的Connection
  Transaction ts = session.beginTransaction(); // 創(chuàng)建事務的對象ts
  List<UserBean> list=null;
  try {
   Criteria criteria=session.createCriteria(UserBean.class);
   criteria.addOrder(Order.desc("createtime1"));
   list=criteria.list();
   ts.commit();
  } catch (HibernateException he) {
   he.printStackTrace();
   ts.rollback();
  }
  return list;
 }
 
 public  List<UserBean> getAllByAddress(String address){
  Configuration cfg = new Configuration().configure(); // 獲取hibernate的配置信息
  SessionFactory sf = cfg.buildSessionFactory(); // 根據(jù)config建立sessionFactory
  Session session = sf.openSession(); // factory用于建立session，開啟Session，相當于開啟JDBC的Connection
  Transaction ts = session.beginTransaction(); // 創(chuàng)建事務的對象ts
  List<UserBean> list=null;
  try {
   Query query=session.getNamedQuery("getUserBeanByAddress");
   query.setParameter("address", address);
   list=query.list();
   ts.commit();
  } catch (HibernateException he) {
   he.printStackTrace();
   ts.rollback();
  }
  return list;
 }

}

package com.abin.lee.hibernate;

import java.util.List;

import junit.framework.TestCase;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.lcdbtsf.air.po.UserBean;

public class UserDaoTest extends TestCase {

 public void test() {
   List<UserBean> list=new UserDao().getAllByAddress("shanghai");
  try {
   if(list.size()>0){
    UserBean user =list.get(0);
    System.out.println(list.get(0).getId1());
   }
  } catch (HibernateException he) {
   he.printStackTrace();  }
 }

}