创建一个表LOB_TEST
 
CREATE TABLE LOB_TEST
(
  A  NUMBER(10),
  B  BLOB,
  C  CLOB
)

下面是java代码
package test;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

/**
 * A general-purpose SQL interpreter program.
 **/
public class ExecuteSQL {
 final static String url="jdbc:oracle:thin:@127.0.0.1:1521:test"; //orcl为数据库的SID
 final static String user="test";
 final static String password="test";
 final static String driver="oracle.jdbc.driver.OracleDriver";
 final static String filePath="D:\\test\\test.txt";
 
    public static void main(String[  ] args) {
        Connection conn = null;  // Our JDBC connection to the database server
        PreparedStatement pstmt = null;
        Random random=new Random();
        String num=String.valueOf(random.nextLong());
        try {
         StringBuffer sql=new StringBuffer();
         StringBuffer updateSql=new StringBuffer();
         sql.append("INSERT INTO guof_test (A,C)  values ('"+num+"',EMPTY_CLOB())");
         updateSql.append("SELECT A,C from guof_test WHERE A= ? For update");
            conn = getConnection();
            conn.setAutoCommit(false);
            Statement state = conn.createStatement( );
            state.executeUpdate(sql.toString());
            state.close();
            pstmt = conn.prepareStatement(updateSql.toString());
            pstmt.setString(1, num);
            ResultSet rest = pstmt.executeQuery();
            File parsed = new File(filePath);
            BufferedReader in =new BufferedReader(new InputStreamReader(new FileInputStream(parsed)));
            String readStr=null;
        
            if(rest.next()){
              oracle.sql.CLOB clob = (oracle.sql.CLOB) rest.getClob("C");
                 Writer outStream = clob.getCharacterOutputStream();
                try{
                while((readStr=in.readLine())!=null){
                     outStream.write(readStr);
                     }
                    outStream.close();
                    }catch (IOException e)
                {
                    e.printStackTrace();
                }
                finally
                {
                    try
                    {
                       outStream.close();
                    } catch (IOException e)
                    {
                        e.printStackTrace();
                    }         
                } 
           
            }
            conn.commit();
            conn.setAutoCommit(true);
        }
        catch (Exception e) {
            e.printStackTrace();;
          
        }

          finally {
            try { conn.close( ); } catch (Exception e) {  }
        }
    }

 /**
  * @return
  * @throws ClassNotFoundException
  * @throws SQLException
  */
 public static Connection getConnection() throws ClassNotFoundException, SQLException {
  Connection conn;
  
  if (url == null)
      throw new IllegalArgumentException("No database specified");

   if (driver != null) Class.forName(driver);

  conn = DriverManager.getConnection(url, user, password);
 return conn;
 }
}

来到东软电信已经有3个月了，感到在学校学的知识很肤浅，自己的平时积累还很不够，但是广泛的涉猎也确实让我感觉到对于现在软件公司的一些硬伤应该如何进行改进。

今天突然客户让我们改产品变更的页面，这个页面使整个BSS最复杂的页面，稍作改动就会荡掉，我认为要想根治这个问题要作两步

1.编写详细的测试用例，用Junit进行详细的测试，在测试完备的基础上进行代码的重构

2.重构是个系统工程，将现在的代码改造成面向对象的设计。

3.从跑junit测试

 

 

The Singleton Patterns :ensures a class has only one instance ,and provides a global point of access of it.

(scott\tiger)
一.pl/sql
1.从DEPT，EMP表中选出部门的员工数大于3的部门号，部门名。
SELECT COUNT(*) AS SUMDEPNO,D.DEPTNO,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO =D.DEPTNO
GROUP BY E.DEPTNO,D.DEPTNO,D.DNAME
HAVING COUNT(*)>3

2.求出EMP表中HIREDATE在1981年并且并且JOB 是以‘M’开头的员工的薪金总和 。

SELECT SUM(SAL)
FROM EMP
WHERE  TO_CHAR(HIREDATE,'YYYY-MM-DD') LIKE '1981%'
       AND JOB LIKE 'M%'
3.选出COMM为空并且部门号不是30、薪金小于2700 的员工号，名字。
SELECT * FROM EMP
WHERE COMM IS NULL
AND DEPTNO <> '30'
AND SAL>2700

4.选出部门中管理员手下超过2个人的管理员的信息.
SELECT E1.*
FROM EMP E1
WHERE EMPNO IN(
SELECT MGR FROM EMP E2
GROUP BY MGR
HAVING COUNT(*)>=2
 
)
5.选出部门20,30中最高工资和最低工资的差额.
SELECT MAX(SAL)-MIN(SAL)
FROM EMP
WHERE
DEPTNO ='20' OR DEPTNO='30'
GROUP BY DEPTNO

 

6.求EMP中名字中第二个字母是L的员工所在的部门信息。
SELECT * FROM EMP WHERE ENAME LIKE '_L%'
7.求出部门的平均工资。
SELECT AVG(SAL),DEPTNO FROM EMP
GROUP BY DEPTNO
8.求出在部门的平均工资大于3000的部门中工资大于2000的员工信息。
SELECT * FROM EMP
WHERE  DEPTNO IN (SELECT DEPTNO FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) >2000)
AND SAL>2000

二.
1.输入一个部门号,利用PROCEDURE,游标,将部门号中的员工号,姓名,工作,薪金打印到屏幕上.
（输入为空的时候打印全部信息）。
create or replace procedure getInfor(ID_Dept in  number) is
TYPE cur_ref_cursor_type IS REF CURSOR;
my_cursor cur_ref_cursor_type;

C_EMPNO EMP.EMPNO%TYPE;
C_ENAME EMP.ENAME%TYPE;
C_JOB   EMP.ENAME%TYPE;
C_MGR   EMP.MGR%TYPE;
C_HIRDATE EMP.HIREDATE%TYPE;
C_SAL   EMP.SAL%TYPE;
C_COMM EMP.COMM%TYPE;
C_DEPTNO EMP.DEPTNO%TYPE;
begin
  open my_cursor  for
  select * from emp where deptno=ID_Dept;
  LOOP
  FETCH my_cursor INTO C_EMPNO,C_ENAME,C_JOB,C_MGR,C_HIRDATE,C_SAL,C_COMM,C_DEPTNO;
    EXIT WHEN my_cursor%NOTFOUND;
    DBMS_OUTPUT.put_line (C_EMPNO||','||C_ENAME||','||C_JOB||','||C_MGR||','||C_HIRDATE||','||C_SAL||','||C_COMM||','||C_DEPTNO);
  END LOOP;

end getInfor;

 

--(scott\tiger)

--输入一个部门号,利用PROCEDURE,游标,将部门号中的员工号,姓名,工作,薪金打印到屏幕上.
--（输入为空的时候打印全部信息）。

create or replace procedure getInfor(ID_Dept in  number) is
TYPE cur_ref_cursor_type IS REF CURSOR;
my_cursor cur_ref_cursor_type;

C_EMPNO EMP.EMPNO%TYPE;
C_ENAME EMP.ENAME%TYPE;
C_JOB   EMP.ENAME%TYPE;
C_MGR   EMP.MGR%TYPE;
C_HIRDATE EMP.HIREDATE%TYPE;
C_SAL   EMP.SAL%TYPE;
C_COMM EMP.COMM%TYPE;
C_DEPTNO EMP.DEPTNO%TYPE;
begin
  open my_cursor  for
  select * from emp where deptno=ID_Dept;
  LOOP
  FETCH my_cursor INTO C_EMPNO,C_ENAME,C_JOB,C_MGR,C_HIRDATE,C_SAL,C_COMM,C_DEPTNO;
    EXIT WHEN my_cursor%NOTFOUND;
    DBMS_OUTPUT.put_line (C_EMPNO||','||C_ENAME||','||C_JOB||','||C_MGR||','||C_HIRDATE||','||C_SAL||','||C_COMM||','||C_DEPTNO);
  END LOOP;

end getInfor;

--前提
drop table CATEGORY;
create table CATEGORY
(CategoryName  VARCHAR2(12) primary key,
ParentCategory  VARCHAR2(8),
SubCategory VARCHAR2(20));

Insert into CATEGORY values
('ADULTREF','ADULT','REFERENCE');
Insert into CATEGORY values
('ADULTFIC','ADULT','FICTION');
Insert into CATEGORY values
('ADULTNF','ADULT','NONFICTION');
Insert into CATEGORY values
('CHILDRENPIC','CHILDREN','PICTURE BOOK');
Insert into CATEGORY values
('CHILDRENFIC','CHILDREN','FICTION');
Insert into CATEGORY values
('CHILDRENNF','CHILDREN','NONFICTION');

commit;

drop table BOOKSHELF;
create table BOOKSHELF
(Title       VARCHAR2(100) primary key,
Publisher    VARCHAR2(20),
CategoryName VARCHAR2(20),
Rating       VARCHAR2(2),
constraint CATFK foreign key (CategoryName)
references CATEGORY(CategoryName));

Insert into BOOKSHELF values
('TO KILL A MOCKINGBIRD','HARPERCOLLINS','ADULTFIC','5');
Insert into BOOKSHELF values
 ('WONDERFUL LIFE','W.W.NORTON','ADULTNF','5');
Insert into BOOKSHELF values
 ('INNUMERACY','VINTAGE BOOKS','ADULTNF','4');
Insert into BOOKSHELF values
 ('KIERKEGAARD ANTHOLOGY','PRINCETON UNIV PR','ADULTREF','3');
Insert into BOOKSHELF values
 ('ANNE OF GREEN GABLES','GRAMMERCY','CHILDRENFIC','3');
Insert into BOOKSHELF values
 ('GOOD DOG, CARL','LITTLE SIMON','CHILDRENPIC','1');
Insert into BOOKSHELF values
 ('LETTERS AND PAPERS FROM PRISON','SCRIBNER','ADULTNF','4');
Insert into BOOKSHELF values
 ('THE DISCOVERERS','RANDOM HOUSE','ADULTNF','4');
Insert into BOOKSHELF values
 ('THE MISMEASURE OF MAN','W.W. NORTON','ADULTNF','5');
Insert into BOOKSHELF values
 ('EITHER/OR','PENGUIN','ADULTREF','3');
Insert into BOOKSHELF values
 ('POLAR EXPRESS','HOUGHTON MIFFLIN','CHILDRENPIC','1');
Insert into BOOKSHELF values
('RUNAWAY BUNNY','HARPERFESTIVAL','CHILDRENPIC','1');
Insert into BOOKSHELF values
('MY LEDGER','KOCH PRESS','ADULTNF','5');
Insert into BOOKSHELF values
('COMPLETE POEMS OF JOHN KEATS','VIKING','ADULTREF','2');
Insert into BOOKSHELF values
('UNDER THE EYE OF THE CLOCK','ARCADE PUB','CHILDRENNF','3');
Insert into BOOKSHELF values
('JOHN ADAMS','SIMON SCHUSTER','ADULTNF','4');
Insert into BOOKSHELF values
('TRUMAN','SIMON SCHUSTER','ADULTNF','4');
Insert into BOOKSHELF values
('JOURNALS OF LEWIS AND CLARK','MARINER','ADULTNF','4');
Insert into BOOKSHELF values
('GOSPEL','PICADOR','ADULTFIC','4');
Insert into BOOKSHELF values
('EMMA WHO SAVED MY LIFE','ST MARTIN''S PRESS','ADULTFIC','3');
Insert into BOOKSHELF values
('MIDNIGHT MAGIC','SCHOLASTIC','CHILDRENFIC','1');
Insert into BOOKSHELF values
('HARRY POTTER AND THE GOBLET OF FIRE','SCHOLASTIC','CHILDRENFIC','4');
Insert into BOOKSHELF values
('SHOELESS JOE','MARINER','ADULTFIC','3');
Insert into BOOKSHELF values
('BOX SOCIALS','BALLANTINE','ADULTFIC','3');
Insert into BOOKSHELF values
('TRUMPET OF THE SWAN','HARPERCOLLINS','CHILDRENFIC','3');
Insert into BOOKSHELF values
('CHARLOTTE''S WEB','HARPERTROPHY','CHILDRENFIC','3');
Insert into BOOKSHELF values
('WEST WITH THE NIGHT','NORTH POINT PRESS','ADULTNF','3');
Insert into BOOKSHELF values
('THE GOOD BOOK','BARD','ADULTREF','4');
Insert into BOOKSHELF values
('PREACHING TO HEAD AND HEART','ABINGDON PRESS','ADULTREF','4');
Insert into BOOKSHELF values
('THE COST OF DISCIPLESHIP','TOUCHSTONE','ADULTREF','3');
Insert into BOOKSHELF values
('THE SHIPPING NEWS','SIMON SCHUSTER','ADULTFIC','4');

commit;

--按书的类型分类并为每种类型记数
SELECT CATEGORYNAME ,COUNT(*)
FROM BOOKSHELF
GROUP BY CATEGORYNAME；

 

--使用avg()
select categoryName ,count(*),AVG(Rating)
from bookshelf
group by categoryName

--找出大约总平均值的组
select categoryName ,count(*),AVG(Rating)
from bookshelf
group by categoryName
having avg(rating)>(select avg(rating) from bookshelf)

--增加一个order by
SELECT CATEGORYNAME ,COUNT(*)
FROM BOOKSHELF
GROUP BY CATEGORYNAME
order by categoryname desc

SELECT CATEGORYNAME ,COUNT(*)
FROM BOOKSHELF
GROUP BY CATEGORYNAME
order by count(*) desc

--分组视图
create or replace view CATGORY_COUNT as
select categoryname ,count(*) as counter
from bookshelf
group by categoryname;
--内联视图的使用
select categoryname ,counter,(counter/bookcount)*100 as percent
from category_count,(select count(*) as bookcount from bookshelf)
order by categoryname;

 

中华帝国领导人最强大阵容这样搭配，绝对是中华盛世
国家主席：李世民        
人大委员长：孙中山
国务院总理：诸葛亮     
政协主席：魏孝文帝 
纪委书记：魏征    
军委主席：毛泽东
外交部长：周恩来        
国防部长：成吉思汗
教育部长：孔子          
卫生部长：华佗
环保总局局长：老子      
农业部长：贾思勰
商务部长：胡雪岩        
水利部长：大禹
建设部长：秦始皇        
地质部长：张衡
公安部长：展昭          
国土资源部长：徐霞客
发展与改革委员会主任：商鞅
最高人民法院院长：包拯
国家禁毒署署长：林则徐  
妇联主任：武则天
新闻出版署署长：纪晓岚  
足协主席：高俅
国家药品监督管理局局长：李时珍  

看了小马哥，感觉做人做快乐的时候就是有兄弟和你一起为一个目标奋斗，真正的朋友用不着用语言交流，也不用计较得失。我的兄弟们，我想你们了！

--建立用户 uts 密码 uts
create user uts identified by uts

--赋予用户uts连接的权限
grant create session to uts

--更改用户密码
alter user uts identified by ut

--在预置文件中设置 可以登录失败的次数
create profile LIMITED_PREFILE limit
FAILED_LOGIN_ATTEMPTS 5;--登陆失败5次锁定用户
CREATE USER JANE IDENTIFIED BY EYRE
profile  LIMITED_PREFILE ;

--给账户解锁
alter user jane account unlock

--在预置文件中设置 设置密码的生命期
ALTER PROFILE LIMITED_PREFILE LIMIT
PASSWORD_LIFE_TIME 30;

--查找用户的密码到期时间
select username ,expiry_date
from dba_users
where username='JANE'

--创建同义词
--建立一个包含其它用户的完整表或视图的一种方法是创建一个同义词
create synonym LITTLECOMFORT for Dora.SOMECOMFORT--Dora 用户名，SOMECOMFORT表名

--创建角色
create role clerk
--为角色授权
grant select on COMFORT　to clerk--COMFORT　表名

--将一个角色授予另外一个角色
grant clerk to manager with admin option--with admin option表示被授予者有权将这个角色授予其它用户或角色

--撤销角色的权限
revoke select on comfort from clerk
--删除角色
drop role clerk

大梅沙一行，我们四人丢的一塌糊涂，可感觉挺不错，在这个冰冷的城市我并不孤单，朋友－－一生最大的财富。