PL/SQL学习笔记(二)

1.先来一道经典题目的三种做法

用一个查询语句，实现查询各个岗位的总工资和各个部门的总工资和所有雇员的总工资

方法一：
①使用union all，先分步查询，查询出每一个最后再整合。

--各个岗位的总工资
select e.job,sum(e.sal) from emp e group by e.job
--各个部门的总工资
select e.deptno,sum(e.sal) from emp e group by e.deptno
--所有雇员的总工资
select sum(e.sal) from emp e ;

②再两两结合，先结合前两句，

select e.job,sum(e.sal) from scott.emp e group by e.job
union all
select e.deptno,sum(e.sal) from scott.emp e group by e.deptno

提示出错如下

说明类型不匹配，观察发现e.deptno为number，e.job为varchar2，只要将这两个类型都转换为varchar2型即可。

select e.job,sum(e.sal) from scott.emp e group by e.job
union all
select e.deptno||'',sum(e.sal) from scott.emp e group by e.deptno

结果为

③再结合后两句

select e.deptno,sum(e.sal) from scott.emp e group by e.deptno
union all
select sum(e.sal) from emp e ;

提示

说明查询列不一致，往第三句加一个空字段即可

select e.deptno||'',sum(e.sal) from scott.emp e group by e.deptno
union all
select '',sum(e.sal) from scott.emp e ;

结果如下

然后再查就可得出最终结果。这是思路最简单的。

方法二：
该方法最简洁，但没那么好理解，利用grouping sets

select e.job,e.deptno,sum(e.sal) from emp e group by grouping sets((e.job),(e.deptno),(null));

方法三：
使用over和partition by，这相当于内部分组

select distinct
	sum(e.sal) over(partition by e.job) 岗位总工资,
	sum(e.sal) over(partition by e.deptno) 部门总工资,
	sum(e.sal) over() 雇员总工资
from emp e;

当然这也可以分开使用。

2.视图命名以$开头，如

create or replace view v$_salesinfo as

3.trunc函数在处理数字时，有
trunc（number,num_digits）
number 需要截尾取整的数字。
num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
trunc()函数截取时不进行四舍五入

select TRUNC(15.79) from dual;

TRUNC(15.79);--15

select TRUNC(15.79,1) from dual;

TRUNC(15.79,1);--15.7

select trunc(15.79,-1) from dual;

TRUNC(15.79,-1);--10

4.触发器(trigger)是数据库发生某项操作时自动运行的程序，它不能直接被调用，只能被系统自动调用。它和存储过程和函数类似，有声明、执行和异常处理。但二者不同之处在于存储过程和函数需要用户显示调用才执行，而触发器则是由事件来触发执行。即触发器是某个事件发生时自动地隐式运行，并且触发器不能接受参数。如记录系统日志的程序就是一个触发器。

5.oracle中的sequence的作用。
在oracle中没有自增字段，用sequence可以实现序列的自增长。在某个表中，若想实现某列字段的自增长，那么在插入id值得时候，可以这么写sequence_name.nextval或者建立一个触发器来关联。举一个和触发器关联的例子

--创建表
create table t_user(
	id number(11),
	name varchar2(100)
)

--创建序列,可以用可视化界面操作
create sequence user_seq
increment by 1 -- 每次增长几个
start with 1 --从几开始
nomaxvalue
nominvalue
nocache

--创建触发器
create or replace trigger tr_user
before insert on t_user
for each row
begin
	select user_seq.nextval into :new.id from dual;
end;

6.触发器有before和after关键词，
before表示在操作数据库之前执行触发器，先完成数据的增删改，再触发；
after是在操作数据库之后执行触发器，先触发，再执行数据的增删改。
before可以修改:new的值，after不行。:old的值谁也不能修改。

7.触发器分为语句级触发器和行级触发器
若为语句级触发器，则在执行类似于delete from t时，触发器只运行一次。
若为行级触发器，则t中有几条记录就执行几次触发器。

8.oracle中的包类似于java的接口，只可以装变量和类型、声明和方法、过程的头部，不能加方法和过程体。如：

create or replace package my_pac
is
       --公有数据类型
       v_num number :=100;
       type tab_type is table of varchar2(100) index by binary_integer;
       type c_cursor is ref cursor;
       --包有点类似java的接口 只可以装变量和 类型 声明 和 方法和过程的头部；
       --不能加方法和过程体
      
       --方法的重载
       function f_twonum(l_num1 number,l_num2 number) return number;
       function f_twonum(l_num1 varchar2,l_num2 varchar2) return varchar2;
end;

引用时，直接用my_pac.c_cursor等即可。

9.oracle的安装包中就自带有连接java的驱动，我的路径为oracle\product\10.2.0\db_1\jdbc\lib。当然也可以去官网的driver页面去下载。

10.java调用存储过程，分三步
①oracle里的存储过程如下

create or replace procedure p_emp_copy(
       l_no in number,
       l_result out varchar2
)
is
begin
  select ec.ename into l_result from emp_copy ec where ec.empno = l_no;
end;

②连接数据库的工具类,DBUtil

public class DBUtil {
	public static Connection getConn(){
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
}
//关闭连接的方法省略

③开始调用

public class Test1 {
	public static void main(String[] args) throws Exception {
		Connection conn = DBUtil.getConn();
		//PreparedStatement继承自Statement
		CallableStatement cs = conn.prepareCall("{call p_emp_copy (?,?)}");
		cs.setInt(1, 7369);//in类型的直接传入
		cs.registerOutParameter(2, Types.VARCHAR);//out类型需先注册
		cs.execute();//执行
		String result = cs.getString(2);//获取传出的参数的值
		System.out.println(result);
	}
}

结果为
SMITH

11..java调用存储过程的cursor
①oracle里的存储过程如下
先是生成存储过程

create or replace procedure p_cursor(
  l_result out my_pac.c_cursor
)
is
begin
  open l_result for select * from emp_copy;
end;

而my_pac为包，里边包含了cursor

create or replace package my_pac
is
       type c_cursor is ref cursor;
end ;

②连接数据库的工具类不变,还是DBUtil

③java开始调用

public class Test2 {
	public static void main(String[] args) throws Exception {
		Connection conn = DBUtil.getConn();
		CallableStatement cs = conn.prepareCall("{call p_cursor(?)}");
		cs.registerOutParameter(1, OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
		while(rs.next()){
			System.out.println(rs.getInt(1)+"====="+rs.getString(2));
		}
	}
}

结果为

7369=====SMITH
7499=====ALLEN
7521=====WARD
7566=====JONES
7654=====MARTIN
7698=====BLAKE
7782=====CLARK
7788=====SCOTT
7839=====KING
7844=====TURNER
7876=====ADAMS
7900=====JAMES
7902=====FORD
7934=====MILLER

12.sql的建表语句为

create table t_order (
       --order_id number(8) primary key;--订单id，错误写法，不能为分号，得用逗号
       order_id number(8) primary key,--订单id
       order_no number unique,--订单号
       customer varchar2(200),--客户
       sales_id number(8),--销售员ID
       order_status varchar(200) check(order_status = '通过' or order_status = '驳回')--审批状态
) ;

13.mysql的数据表查询是可以一次在多张表中查询，就是说from后可以有多张表。

如学生表stu和课程表course

--表stu

--表course

--查找需要补考的学生姓名，还有课程名称和分数

select s.sname,s.score,c.cname 
from stu s,course c 
where s.score<60 and s.sc_id = c.cid;

原先还以为from后只能有一张表，都不知道怎么写了，看来失误了。结果如下