SQL-NOTE

CI_ID    STU_IDS

1         1 2 3 4

2         1 4 

 

---子查询

inser into emp values(9996, 'peter', 'MANAGER',7782, to_date('1988/12/12','yyyy/mm/dd')

 

 

create table kkk (myId number(4), myname varchar2(50), myDept number(5)); 

现要从emp表中把10号部门的员工转到kkk表中

 

insert into kkk (myId, myname, mydept) select eno, ename, edeptno from emp where deptno='10';

 

--更新员工Scott的岗位，工资，补助，变为与SMITH员工的一样

 

update emp set (job, sal, comm) = (select job, sal, comm from emp where ename = 'SMITH') where ename = 'SCOTT';

 

dml: 数据操作语言（ 增，删，改）

锁

保存点(savepoint) -----> 可以有无限个保存点

SQL> savepoint a1;

SQL> delete from emp where empno=9996; 

SQL> savepoint a2;

SQL> delete from emp where empno=9999;

SQL> rollback to a2;

SQL> rollback to a1;

 

注意： rollback 只能在commit之前做！因为只要一commit，所有的保存点都不见了。

 

如果没有手动提交（ commit）而是退出了sql plus（exit），那么oracle会自动提交你做的操作。

 

提交了事务后： 事务变化，删除保存点，释放锁

 

 

---------------------------ORACLE 事务处理

Connection conn = DriverManager.getConnection(url, username, pwd);

 

try{

conn.setAutoCommit(false);

 

statement.execut();

statement.execut();

 

conn.commit();

}

catch(Exeption e){

       try{

conn.rollback();

}

catch(Exeption sqlExeption){ 

 }

}finally

{

}

 

--------------------------只读事务----------------------------

只读事务：只允许查询，不允许DML操作。set transaction read only; 表示类似于在这个点取到系统的一个镜像，即使别的用户在操作这个数据库，我现在也是的。常用于某时刻的数据统计之类...

user1(管理员):

SQL>set transaction read only;

 

user2:

SQL>insert into emp values(8888,'ok',to_date('1988-12-12','yyyy-mm-dd'));

SQL>commit;

 

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

 

 

 

 

select c.ci_id, s.stu_id

from pm_ci c, pm_stu s

where instr(c.stu_ids, s.stu_id)>0

------

select ci_id, wm_concat(stu_name) names

from

(select c.ci_id, s.stu_id

from pm_ci c, pm_stu s

where instr(c.stu_ids, s.stu_id)>0)

group by ci_id

 

------

col names for a40

 

--求每年入职的员工人数

 

 

--分析

--SQL语句： select to_char(hiredate,'RR') from emp;

 

declare

cursor c is select to_char(hiredate,'RR') from emp;

phiredate vachar2(4);

count81 number:=0;

count82 number :=0;

count87 number :=0;

begin

open c;

loop

 

fetch c into phiredate;

exit when c%notfound;

if phiredate = '1981' then count81:=count81+1

elsif phiredate = '1982' then count82 :=count82+1

else then count87:=count87+1;

end loop;

end;

 

 

-----------实例二，给员工涨工资

--从最低工资涨起，但是要保证工资总额不超过50000；返回总工资数，以及涨工资的人数

--分析：

--循环，order，

 

 

declare 

totalSal number ;

count number;

cursor c is select eno, esal from emp order by esal;

pno emp.eno%type

psal emp.esal%type

 

begin

select sum(esal) into totalSal from emp;

exit when  totalSal > 50000;

open c

loop

 

fetch c into pno,psal;

exit when c%notfound;

 

if(totalSal+psal*0.1>50000) then exit

else then

update emp set esal = esal*1.1 where eno = pno;

totalSal := totalSal+psal*0.1;

count:=count+1;

end if;

 

 

end loop

close c;

 

--用PL语言实现部门分段（6000以上，（6000,3000）,3000一下，

-- 统计各工资段的职工人数，以及各部门 工资总额（不包括奖金）

 

--select depno into pdepno from emp

 

 

declare 

cursor c1 is select depno from emp;

pdepno emp.depno%type;

 

cursor c2(pdepno emp.depno%typ) is select esal from emp where depno = pdepno;

psal emp.esal%type;

--计数器

pcount1 number;

pcount2 number;

pcount3 number;

 

--工资总额

totalSal number;

 

begin

open c1;

loop

fetch c1 into pdepno;

exit when c1%notfound;

totalSal :=0;

pcount1 number:=0;

pcount2 number:=0;

pcount3 number:=0;

,,,,,,,,,, 

open c2(pdepno);

loop

fetch c2 into psal;

exit when c2%notfound;

totalSal:= totalSal+psal;

if psal > 6000 then pcount1:=pcount1+1;

elsif psal> 3000 then pcount2 :=pcount2+1;

else then pcount3:=pcount3+1;

endloop

close c2;

insert into msg(depno, count1,count2,count3, total) values(pdepno,pcount1,pcount2,pcount3,nvl(totalSal,0));

 

 

--数据字典

 

oracle中的表分两种： 基本表（数据字典）和 用户表

oracle中的数据字典很多，我们只需要记住一个即可：dictionary 

select * from dictionary; 

 

 select * from user_cons_columns;--用户创建的所有约束

 

--给表创建注释：

comment on table emp is '这是一个员工信息表'

 

 

 

select * from user_tables;

select * from user_objects;

select * from user_tab_columns;

 

end loop;

close c1;

 

end;

 

 

----------------------jdbc创建连接

--把一个jar包放在project下的lib文件里后，还要右击选择添加到buildpath将其变成奶瓶才行哦

 

String driver = "oracle.jdbc.OracleDriver";

String url = "jdbc:oracle:thin:@lacalhost:1521:dbname";

String user="scott";

String password ="tiger";

 

static{

Class.forName(driver);

}

 

public static Connection getConnection(){

 return DriverManager.getConnection(url, user, password);

}

 

jdbc 调用procedure的过程：

1. CallableStatement callablestatement = null；

2.  建立连接

3. String sql=".....(?,?,?);"

4. callablestatement= conn.prepareCall(sql);

5. callablestatement.setInt(1, 1234);//入参

   callablestatement.registerOutParameter(2, OracleTypes.VARCHAR);

      callablestatement.register(3,OracleTypes.CURSOR);

6.callablestatement.execute();

 

7. String name = callablestatement.getString(2);

  resultset rs = (OracleCallableStatement)callablestatement.getCursor(3);

 

/*

java -Xms100M -Xmx200M Helloword

 

 此程序的最小内存，最大内存

 

ThreadDump 里含有死锁的信息。

windows: Ctrl+Break

linux: kill -3 pid 

 

desc EmpPackage;

*/

 

CallableStatement --是个接口，其由 ojdbc14.jar 实现 

 

 

--本地登陆：

sqlplus username/password

或者：

sqlplus username/password@localhost:1521/dbname

 

--远程登录

 sqlplus username/password@192.168.1.217:1521/dbname

 

 

  private static String driver = "oracle.jdbc.OracleDriver";

private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";

private static String user = "scott";

private static String password = "tiger";

 

static{

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

throw new ExceptionInInitializerError(e);

}

}

 

public static Connection getConnection(){

try {

return DriverManager.getConnection(url,user,password);

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

 

 

public static void release(Connection conn, Statement st, ResultSet rs){

if(rs!=null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}finally{

rs =null;//既然无法关掉，总要告诉GC这个本来指向的资源可以回收啦！

}

}

if(st!=null){

try {

st.close();

} catch (SQLException e) {

e.printStackTrace();

}finally{

st =null;既然无法关掉，总要告诉GC这个本来指向的资源可以回收啦！

}

}

if(conn!=null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}finally{

conn =null;既然无法关掉，总要告诉GC这个本来指向的资源可以回收啦！

}

}

}

}

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

dataSource 的配置：

<bean id ="dataSource" destroy-method="close" class = "org.apache.commons.dbcp.BasicDataSource">

<property name="driverClassName" value="${jdbc.driverClassName}"/>

<property name="url" value="${jdbc.url}"/>

<property name="username" value="${jdbc.username}"/>

<property name="password" value="${jdbc.password}"/>

</bean>

 

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

<property name="locations">

   <value>classpath:env-jdbc.properties</value>

</property>

</bean>

 

jdbc--datasource（driver， url， username，pwd)

JdbcAccessor(setDataSource(datasource);_

  |

  |

JdbcTemplate( 封装一个datasource

 

JdbcTemplate 是JdbcDaoSupport的一个属性。

方案一：

personDao extends JdbcDaoSupport 

public void update（）

{

this.getJdbcTemplate().execute("update Emp set ename= 'a' where eno = '3'");

}

 

方案二：

personDao extends JdbcTemplate

public PersonDao(DataSource dbsource)

{

  super(dbsource);//TO CHECK

}

<bean id="personDao" class = ".....dao.PersonDao">

<constructor-arg index="0" ref="dataSource"></constructor-arg>

</bean>

 

-----------jdbc+Spring 查询

interface RowMapper{

  Object mapRow(ResultSet rs);

}

 

     |

     |

PersonRowMapper implements RowMapper{

   Object mapRow(ResultSet rs)

   {

         Person p = new Person();

         p.setId(rs.getLong("pid"));

         ....

         return p;

   }

   

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

String sql = "select * from t_actor where id = ?";

String sql_all = "select * from t_actor";

Object[] params = new Object[]{new Long(1222)};

RowMapper  actorRowMapper = new ActorRowMapper();

Actor actor =(Actor) this.jdbcTemplate.queryForObject(sql, params, actorRowMapper);// 返回一个object

Collection actors = this.jdbcTemplate.query(sql_all, actorRowMapper);//返回一个object的集合

 

 

 

--------------------------procedure------------------------------------------------------

查看错误信息：

show error；

 

如何调用过程：

exec 过程名（para1，para2.....）；

call 过程名（para1, para2.......）;

 

标识符号的命名规范：

定义变量时， 用v_作为前缀 v_id

定义常量时，用c_作为前缀 c_pi

定义游标时，用_cursor作为后缀： emp_cursor

定义例外时，用e_作为前缀

 

dbms_output.put_line('hello, word');

 

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

SQL>set serveroutput on

begin

dbms_output.put_line('hello, word');

end;

/

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

引入java驱动：Library-----add external jars----

 

 

-------FUNCTION

SQL>create function my_function(p_name varchar2) return numer is

yearSal number(7,2);

begin

select sal*12 + nvl(comm, 0) into yearSal from emp where ename = p_name;

return yearSal;

end;

/

==注意，刚才这部分只是生命而已！！！！！执行要这样：

 

SQL>var v_sal number;

SQL>call myFunction ('小红') into : v_sal;

 

 

----------关于TYPE-------------

SQL>declare 

type emp_record_type is record(name emp.ename%type, salary emp.sal%type, title emp.job%type);

p_record emp_record_type;

 

begin 

select ename, sal, job into p_record from emp where eno = '7788';

dbms_output.put_line('员工||p_record.name');

 

end;

/

 

-------TABLE--------

declare

type empNameList is table of emp.ename%type index by binary_integer;

pempnamelist empNameList ;

begin 

select ename into pempnamelist(0) from emp where empno=7788;

dbms_output.put_line(''||pempnamelist(0));

 

 

 

----------ref cursor--------

declare 

 

type  sp_emp_cursor is ref cursor;

v_ename emp.ename%type;

v_sal emp.sal%type;

p_emp_cursor sp_emp_cursor;

 

begin

open p_emp_cursor for 

   select ename, sal from emp where deptno = &no;

 

loop

   fetch p_emp_cursor into v_ename, v_sal ;

  exit when p_emp_cursor%notfound;

  dbms_output.put_line(v_ename || '  '|| v_sal);

end loop

end;

end;

/

 

 

 

 

 

------接收键盘输入--------------------

begin

select ename, sal into v_ename, v_sal from emp where ename =&no;

end;

 

-------------------分页---------------------

select * from (select t1.* , rownum rn from (select * from emp) t1 where rn <=10) where rn>=6;

 

sql varchar:='select * from (select t1.* , rownum rn from (select * from '|| myTable || ') t1 where rn <=' ||endRow || ') where rn>= ' || startRow;

 

 

create or replace procedure FY (myTable IN varchar，pageSize in number, pageNow in number, totalRows out number, totalPage out number, cur out sys_refcursor) AS

 

 

v_endrow number := pageSize*pageNow;

v_startrow number := pageSize*(pageNow-1) +1;

sql varchar:='select * from (select t1.* , rownum rn from (select * from '|| myTable || ') t1 where rn <=' ||endRow || ') where rn>= ' || startRow;

 

open cur for sql;

close cur;

select count(1) into totalRows from emp;

if mod(totalRows, pageSize)=0 then 

totalPage := totalRows/pageSize;

else totalPage := totalRows/pageSize+1;

end IF;

end FY;