PLSQL语言（二）

24 比普通员工最高薪水还要高的经理人

select ename from emp where empno in 
(select distinct mgr from emp where mgr is not null)
and sal >
(
	select max(sal) from emp where empno not in
 	(select distinct mgr from empwhere mgr is not null)
)

/

求部门经理人中平均薪水最低的部门名称


求薪水最高的前5名员工

select ename ,sal from (
select ename,sal from emp order by sal desc)
where rownum <=5


求薪水最高的第6到第10名员工（重点掌握）

select ename,sal from (
select rownum r,ename,sal from
(
select ename,sal from emp order by sal desc
)) 
where r >5 and r <= 10

1-- backup scott
    exp

2-- create user
	create user jetwu identified by jetwu default tablespace users quota 10M on users
	grant create session--登录权限, create table, create view to jetwu

3-- import the data
	imp


--备份一张表
create table emp2 as select * from emp;

insert into dept2 select * from dept;

rownum
--取15条记录中后5条记录值 
select ename from (select rownum r, ename from emp) where r > 10;

--取薪水最高的5个人
select ename, sal from
(select ename,sal from emp order by sal desc) where rownum <=5;

--取薪水最高的6到10
select ename, sal from
(
  select ename, sal, rownum r from
  (
  select ename, sal from emp order by sal desc
  )
)
where r >=6 and r <=10;


3张表S,C,SC
S(SNO,SNAME)
C(CNO,CNAME,CTEACHER)
SC(SNO,CNO,SCGRADE)
1.找出没选过黎明老师的所有学生姓名
2.列出2门以上(含2门)不及格学生姓名及平均成绩
3.即学过1号课又学过2号课所有学生的姓名
1.select ename from s join sc on(s.sno=sc.sno) join c on(c.cno = sc.cno) where c.cteacher <> '黎明';
2.select s.sname sc.avg(scgrade) from s s,sc sc where s.sno = sc.sno and
 sc.scgrade < 60 group by sc.sno having count(*) >=2 

3.
select sname from s join sc on (s.sno=(
select sno from sc where cno = 1 and sno in (select distinct sno from sc where cno = 2)));

28 字段约束，是将约束加在字段后面 

   非空约束  constraint xxx not null

   唯一约束  unique

   两个空值不认为是重复的

   表级约束,  constraint xxxx(名字随便起) unique(email[字段名],name[字段名])    {表示这两个字段的组合不能为空}

29 主键约束
	primary key (唯一的不可重复的不可为空的)
   外键约束
	references 表名（字段）
	写成表级约束
	constraint xxx foreign key (字段名) references 外表名（外表字段）

如：create table stu(
      	id int,
	name varchar(50) constraint aaa not null, 
	class1 number(6) ,
	email varchar(30) ,
	constraint stu_class_id foreign key (class1) references class(id),
	constraint xxx unique(name,email)         
    );

    create table class(
   	 id number(6) primary key,
   	 name varchar2(10)
    )


31 修改表结构 alter

   增加字段
	alter table 表名 add(字段名，参数)
	如：alter table stu add(school,varchar2(20));

   删除字段
	alter table 表名 drop(字段名)
	如：alter table stu drop(school);

   修改字段
	alter table 表名 modify(字段名，参数)
	如：alter table stu modify(email,varchar(50))


33  索引

一个表如果加了 “主键限制”，“唯一限制”，“组合限制”

oracle会自动生成索引

索引创建格式

create index xxx(索引名随便起，最好见名知义) on 表名（字段名）
如：create index index_stu_email on stu(email);

删除索引

drop index xxx;

显示数据库中有什么索引

select index_name from user_indexes;

第一范式-要有主键,列不可分,

第二范式-不能存在部分依赖，不是主键的字段不能部分依赖主键

第三范式-除了主键外的字段不能存在传递依赖


PL SQL显示
pl/sql 面向过程的sql 带有分支和循环的sql

匿名块 ：没有名字的程序

PL/SQL分为四块

declare 定义变量（可有可无）
begin 程序从这里开始（必须）
exception 有异常时执行
end;（必须）

如：
	set serveroutput on;(必须先执行这一命令，因为默认是OFF，如果是OFF的话，就不会输出)
	begin
		dbms_output.put_line('helloworld');
    	end;

set serveroutput on;

declare
	v_name varchar2(20);
begin
	v_name := 'myname';
	dbms_output.put_line(v_name);
end;


declare
	v_num number :=0;
begin
	v_num := 2/v_num;
	dbms_output.put_line(v_num);
exception
	when others then
	dbms_output.put_line('error');
end;


--变量声明的规则

1.	变量名不能够使用保留字，如from，select等
2.	第一个字符必须是字母
3.	变量名最多包含30个字符
4.	不要与数据库的表或者列同名
5.	每一行只能声明一个变量

--常用变量类型

1.	binary_interger:整数，主要用来计数而不是用来表示字段类型
2.	number：数字类型
3.	char：定长字符串
4.	varchar2:变长字符串
5.	date：日期
6.	long：长字符串，最长2GB
7.	boolean：布尔类型，可以取值为true，false和null值


--变量声明
declare
	v_temp number(1);
	v_count binary_integer := 0;
	v_sal number(7,2) := 4000.00;
	v_date date := sysdate;
	v_pi constant number(3,2) := 3.14;
	v_valid boolean := false;
	v_name varchar(20) not null := 'MyName';
begin
	dbms_output.put_line('v_temp value:' || v_count);
end;
当变量声明为boolean类型时，该变量能不直接打印
如上面变量不能写成
	dbms_output.put_line(v_valid);

--变量声明，使用%type属性
declare
	v_empno number(4);
	v_empno2 emp.empno%type;
	v_empno3 v_empno2%type;
begin
	dbms_output.put_line('Test');
end;
当使用“%type” 声明变量时，该变量变得灵活，会随着表的字段的修改而自动修改	


--Table变量类型    Table复合变量（相当于java中的数组）
declare
	type(关键字,说明是定义的新类型) type_table_emp_empno is table of emp.empno%type index by binary_integer;
	v_empnos type_table_emp_empno;
begin 
	v_empnos(0) := 7369;
	v_empnos(2) := 7839;
	v_empnos(-1) := 9999;
	dbms_output.put_line(v_empnos(-1));
end;

--Record变量类型 （record变量相当于java中的类）
declare
	type type_record_dept is record
	(
		deptno dept.deptno%type,
		dname dept.dname%type,
		loc dept.loc%type
	);
	v_temp type_record_dept;
begin
	v_temp.deptno := 50;
	v_temp.dname := 'aaaa';
	v_temp.loc := 'bj';
	dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;

--使用%rowtype声明record变量 （record变量相当于java中的类）可以随着表的改动自动更新变量
declare
	v_temp dept%rowtype;
begin
	v_temp.deptno := 50;
	v_temp.dname := 'aaaa';
	v_temp.loc := 'bj';
	dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;

--SQL语句的运用
在PL/SQL里面用select 必须加 “into”,且在select语句里面必须有且只能返回一条语句
declare
	v_ename emp.ename%type;
	v_sal emp.sal%type;
begin
	select ename,sal into v_ename,v_sal from emp where empno = 789;
	dbms_output.put_line(v_ename || ' ' || v_sal);
end;


declare
	v_ename emp.ename%type;
	v_sal emp.sal%type;
begin
	select ename,sal into v_ename,v_sal from emp where deptno = 10
	dbms_output.put_line(v_ename || ' ' || v_sal);
end;

--ddl在PL/SQL中的运用

创建表

在PL/SQL中创建表时，前面必须加“execute immediate” ，然后后面的建表语句用单引号引上
如果在单引号中，又出现单引号，则内部单引号用 （‘’）代表（‘）；
begin
	execute immediate 'create table t (nnn varchar2(20) default ''aaa'')';
end;


--游标 cursor

declare
	cursor c is
		select * from emp;
	v_emp c%rowtype;
begin
	open c;
	fetch c into v_emp;
	dbms_output.put_line(v_emp.ename);
	close c;
end;


declare
	cursor c is
		select * from emp;
	v_emp c%rowtype;
begin
	open c;
	loop

	   fetch c into v_emp;
	   exit when (c%notfound);
	   dbms_output.put_line(v_emp.ename);
	end loop;
	close c;
end;


--循环遍历
declare
	cursor c is
		select * from emp;
	v_emp c%rowtype;
begin
	open c;
	loop

	   fetch c into v_emp;
	   dbms_output.put_line(v_emp.ename);
	   exit when (c%notfound);

	end loop;
	close c;
end;

--IF语句

取出7369的薪水，如果 <1200 ，则输出‘low’，如果 <2000则输出‘middle’,否则'hign'

declare
	v_sal emp.sal%type;
begin
	select sal into v_sal from emp where empno = 7369;
	if(v_sal < 1200) then
		dbms_output.put_line('low');
	elsif(v_sal <2000) then                          //elsif这里中间的E是没有的
		dbms_output.put_line('middle');
	else
		dbms_output.put_line('hign');
	end if;
end;

循环 PL/SQL里面的循环一定是以LOOP 开始，以END LOOP结束

下面这个相当于 DO WHILE
declare 
	i binary_integer := 1;
begin
	loop
		dbms_output.put_line(i);
		i := i + 1;
		exit when( i >= 11);
	end loop;
end;


下面相当于while循环
declare
	j binary_integer := 1;
begin
	while j < 11 loop
		dbms_output.put_line(i);
		j := j + 1;
	end loop;
end

--for循环
declare
	cursor c is
		select * from emp;
begin
	for v_emp in c loop
	dbms_output.put_line(v_emp.ename);
	end loop;
end;


--带参数的游标

declare
	cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
	is
	select ename,sal from emp where deptno = v_deptno and job = v_job;
begin
	for v_temp in c(30,'CLERK') loop
		dbms_output.put_line(v_temp.ename);
	end loop;
end;


--可更新的游标
declare
	cursor c
	is
	select * from emp2 for update;
begin

	for v_temp in c loop
	   if(v_temp.sal < 2000) then
		update emp2 set sal = sal*2 where current of c;
		elsif (v_temp.sal = 5000) then
			delete form emp2 where current of c;
		end if;
	end loop;
	commit;
end;


--存储过程
create or replace procedure p
is
--上面的替代declare，其他都一样
	cursor c is
		select * from emp2 for update;
begin
	for v_emp in c loop
		if (v_emp.deptno = 10) then
			update emp2 set sal = sal + 10 where current of c;
		elsif (v_emp.deptno = 20)
			update emp2 set sal = sal + 20 where current of c;
		else 
			update emp2 set sal = sal + 50 where current of c;
		end if;
	end loop;
	commit;
end;

--执行存储过程
exec p;
另外一种执行方式
begin
p;
end;


--带参数的存储过程
create or replace procedure p
	(v_a in number, v_b number, v_ret out number,v_temp in out number )
is

begin
	if(v_a > v_b) then
		v_ret := v_a;
	else
	   	v_ret := v_b;
	end if;
	v_temp := v_temp + 1;
end;


declare
	v_a number := 3;
	v_b number := 4;
	v_ret number;
	v_temp number := 5;
begin
	p(v_a,v_b,v_ret,v_temp);
	dbms_output.put_line(v_ret);
	dbms_output.put_line(v_temp);
end;

--函数
create or replace function sax_tax
	(v_sal number)
	return number
is
begin
	if(v_sal < 2000) then
		return 0.10;
	elsif(v_sal < 2750) then
		return 0.15;
	else
		return 0.20;
	end if;
end;

--执行函数
select lower(ename), sal_tax(sal) from emp;


--触发器 当做一个件事的时候，一触动就会触发另一事件
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);

create or replace trigger trig
	after insert or delete or update on emp2 for each row
begin
	if inserting then
		insert into emp2_log values (USER, 'insert',sysdate);
	elsif updateing then
		insert into emp2_log values (USER, 'update',sysdate);
	elsif deleting then
		insert into emp2_log values (USER, 'delete',sysdate);
	end if;
end;

--触发器用于更新关联

create or replace trigger trig
	after update on dept
	for each row
begin
	update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;

应用
update dept set deptno = 99 where deptno = 10;--NEW.deptno:99  OLD.deptno:10


--树状结构的存储与展示
drop talbe article;

create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1),    --0 代表非叶子节点,1代表叶子节点
alevel number(2)
);

insert into article values (1,'蚂蚁大战大象',0,0,0);
insert into article values (2,'大象被打趴下了',1,0,1);
insert into article values (3,'蚂蚁也不好过',2,1,2);
insert into article values (4,'瞎说',2,0,2);
insert into article values (5,'没有瞎说',4,1,3);
insert into article values (6,'怎么可能',1,0,1);
insert into article values (7,'怎么没有可能',6,1,2);
insert into article values (8,'可能性是很大的',6,1,2);
insert into article values (9,'大象进医院了',2,0,2);
insert into article values (10,'护士是蚂蚁',9,1,3);

蚂蚁大战大象
    大象被打趴下了
	蚂蚁也不好过
	瞎说
	    没有瞎说
	大象进医院了
	    护士是蚂蚁
    怎么可能
	怎么没有可能
	可能性是很大的


create or replace procedure p(v_pid article.pid%type, v_level binary_integer) 
is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) := '';
begin
	for i in 1..v_level loop
		v_preStr := v_preStr || '   |';
	end loop;

	for v_article in c loop
		dbms_output.put_line(v_preStr || v_article.cont);
		if(v_article.isleaf=0) then 
			p(v_article.id,v_level + 1);
		end if;
	end loop;
end;