Oracle全代码复习

--管理员身份登录(如果用户名和密码遗失，可以随便写用户名和密码)
--管理员登录可分为：密码认证/主机认证
sqlplus sys/password as sysdba;
--普通用户登录(不能遗失用户名和密码)：
sqlplus
--远程连接
sqlplus system/password@192.168.31.31:1521/orcl
--*******************************************************************
--DML（Data Manipulation Language 数据操作语言）
select,insert,update,delete
--DDL（Data Definition Language 数据定义语言）
create table,alter table,truncate table(清空表),drop table,
create/drop view,sequence,index,synonym(同义词)
--DCL（Data Control Language 数据控制语言）
grant(授权),revoke(撤销权限)
--*******************************************************************
select ename ||’的薪水是’||sal 信息 from emp;
--*******************************************************************
select * from v$nls_parameters;
alter session(只在当前会话中有效)/system(退出了也有效) set NLS_DATE_FORMAT=’yyyy-mm-dd’;
--*******************************************************************
select * from emp where ename like ‘%\_%’ escape ‘\’;
--*******************************************************************
select to_char(sal,'L9,999.99') from emp;
--*******************************************************************
select ename,job,sal 涨前,
	case job when 'PRESIDENT' then sal+1000
		     when 'MANAGER' then sal+800
		     else sal+400
	end 涨后
from emp;
--*******************************************************************
select ename,job,sal 涨前,
	decode(job,'PRESIDENT',sal+1000,
		       'MANAGER',sal+800,
			    sal+400) 涨后
from emp;
--*******************************************************************
select ename,job,sal 涨前,
	case when sal<3000 then sal+500
     	 when sal>=3000 and sal<6000 then sal+1000
     	 else sal+100
	end 涨后
from emp;
--*******************************************************************
select deptno,avg(sal)
from emp group by deptno;

select deptno,job,sum(sal)
from emp group by deptno,job order by 1;
--*******************************************************************
select deptno,avg(sal)
--where 后面不能使用组/多行函数（mysql中的聚合函数）
from emp group by deptno having avg(sal)>3000;
--*******************************************************************
select deptno,job,sum(sal) from emp group by deptno,job
union select deptno,to_char(null),sum(sal) from emp group by deptno
union select to_number(null),to_char(null),sum(sal) from emp
--group by 增强
==select deptno,job,sum(sal) from emp group by rollup(deptno,job);
breake on deptno skip 2 --不同的部门号跳过两行
break on null --恢复
--*******************************************************************
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
from emp e,dept d 
where e.deptno=d.deptno
group by d.deptno,d.dname
--*******************************************************************
/*
  希望把某些不成立的记录，任然包含在最后的结果中-->外连接
  左外连接:当where e.deptno=d.deptno不成立时，等号左边的表任然被包含在最后的结果中
 	写法：where e.deptno=d.deptno(+)
  右外连接:当where e.deptno=d.deptno不成立时，等号右边的表任然被包含在最后的结果中
 	写法：where e.deptno(+)=d.deptno
*/
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
from emp e,dept d 
where e.deptno(+)=d.deptno
group by d.deptno,d.dname
--*******************************************************************
--自连接(不适合操纵大表)
select e.ename 员工姓名,b.ename 老板姓名
from emp e,emp b
where e.mgr = b.ename
--层次查询
select level,empno,ename,mgr
from emp
connect by prior ename = mgr 
start with mgr is null
order by 1;
--*******************************************************************
--子查询(不可以在group by后使用子查询)
select * from emp where sal>
(select sal from emp where ename='TOM');

select empno,ename,sal,(select job from emp where empno=3) 第四列 from emp;

select * from (select empno,ename,sal from emp);

select * from emp
where deptno = (select deptno from dept where dname = '事业部');

select * from emp
where sal > all(select sal from emp where deptno=3);
--null值:not in与in
select * from emp
where ename in (select mgr from emp);

select * from emp
where ename not in (select mgr from emp where mgr is not null);
--*******************************************************************
--相关子查询
select id,name,money,(select money from test1 where id=t.id-1) money1 
from test1 t;
--*******************************************************************
select * from emp where deptno = 1 or deptno = 3;
=select * from emp where deptno in (1,3);
--集合运算
=select * from emp where deptno = 1
union select * from emp where deptno = 3;

select deptno,job,sum(sal) from emp group by deptno,job
union select deptno,to_char(null),sum(sal) from emp group by deptno
union select to_number(null),to_char(null),sum(sal) from emp
--set timing on 打开执行时间
--set timing off 关闭执行时间
--*******************************************************************
--插入数据（与mysql有所不同的是:问号占位符换成地址符）
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值:  10
输入 ename 的值:  'lucy'
输入 sal 的值:  4000
输入 deptno 的值:  5
--再次插入可以使用：/
--*******************************************************************
--复制表结构（不包括表数据）
create table emp2 as select * from emp where 1=2;
--复制表（包括表数据）
create table emp2 as select * from emp;
--一次性插入多条数据
insert into emp2 select * from emp where deptno = 3;
--*******************************************************************
/*
delete和truncate的区别：
	1.delete逐条删除；truncate先摧毁表再重建表
   *2.delete是DML(可以回滚)；truncate是DDL(不可以回滚)
	3.delete不会释放空间；truncate会释放空间
	4.delete会产生碎片；truncate不会产生碎片
	5.delete可以闪回(flashback)；truncate不可以闪回
	delete 删除数据更快(在mysql中，truncate删除数据更快)
*/
--*******************************************************************
/*
rownum(行号永远从1开始)：
	rownum永远按照默认的顺序生成
	rownum只能使用<、<=，不能使用>、>=
*/
--找到员工表中工资最高的前三名：
select rownum,empno,ename,sal
from (select * from emp order by sal desc)
where rownum <= 3;
--分页(5~8)
select *
from (select rownum r,e.*
	from (select * from emp order by sal) e
	where rownum <= 8
	)
where r >= 5;
--找到员工表中薪水大于本部门平均薪水的员工
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno = d.deptno and e.sal > d.avgsal;

select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno) avgsal
from emp e
where sal > 
(select avg(sal) from emp where deptno = e.deptno);
--统计每年入职的员工个数
select count(*) Total,
	sum(decode(to_char(hiredate,'yyyy'),'2000',1,0)) "2000",
	sum(decode(to_char(hiredate,'yyyy'),'2012',1,0)) "2012",
	sum(decode(to_char(hiredate,'yyyy'),'2008',1,0)) "2008",
	sum(decode(to_char(hiredate,'yyyy'),'2005',1,0)) "2005"
from emp;
--*******************************************************************
--临时表创建：
create global temporary table 表名;
--*******************************************************************
--rowid 行地址
select rowid,ename,sal from emp;
--*******************************************************************
--新增列:添加photo列
alter table test add photo blob;
--修改列:将name的长度设为40
alter table test modify name varchar2(40);
--删除列:删除photo列
alter table test drop column photo;
--重命名列:将name修改为username
alter table test rename column name to username;
--重命名表:将test表名改为test2
rename test to test2;
--*******************************************************************
--删除表(包括清空回收站)
drop table test purge;
--查看回收站
show recyclebin;
--清空回收站
purge recyclebin;
--闪回删除
flashback table 表名 to before drop;
--管理员没有回收站
--*******************************************************************
create table test2
(
tid number,
tname varchar2(20),
gender varchar2(2) check(gender in ('男','女'))，
sal number check(sal > 0)
);
--*******************************************************************
create table student
(
sid number constraint student_pk primary key,
sname varchar2(20) constraint student_name_notnull not null,
gender varchar2(2) constraint student_gender_check check(gender in ('男','女')),
email varchar2(40) constraint student_email_unique unique
				   constraint student_email_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null
);
insert into student values(1,'Tom','男','110@qq.com',3);
--*******************************************************************
--视图
create or replace view empinfoview 
as select empno,ename,sal,deptno from emp
with read only/with check option;
--*******************************************************************
/*
Mysql中使用increment，Oracle中使用序列
	nextval:取得序列的下一个内容
	currval:取得序列的当前内容
*/
--创建序列
create sequence myseq
--建表
create table testseq(tid number,tname varchar(20));
 --select myseq.nextval from dual;
 insert into 表名 values(myseq.nextval,'aaa');
 /*
 序列可能产生裂缝的原因：
	1.回滚
	2.系统异常
	3.多个表公用一个序列
 */
--*******************************************************************
--同义词（为其他用户的数据库对象起别名，方便访问）
create [public] synonym s_emp for 用户名.表名;
select * from s_emp;
drop public synonym s_emp;
--*******************************************************************

--*******************************************************************
--PL/SQL语法
declare
	--说明部分
begin
	--程序
	dbms_output.put_line('Hello World');
end;
/
set serveroutput on
/
--*******************************************************************
--引用型变量
declare
	--定义变量
	--pename varchar2(20);
	--psal number;
	pename emp.ename%type;--与emp.ename的类型一样
	psal emp.sal%type;
begin
	--赋值分两种：into / :=
	select ename,sal into pename,psal from emp where empno = 7839;
	dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
--*******************************************************************
--记录型变量
declare
	--定义记录型变量：代表一行(相当于一个数组)
	emp_rec emp%rowtype;
begin
	--赋值分两种：into / :=
	select * into emp_rec from emp where empno = 7839;
	dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
--*******************************************************************
--判断用户从键盘输入的数字
set serveroutput on
--接受键盘输入
--变量num：是一个地址值，在该地址上保存了输入的值
accept num prompt '请输入一个数字：';
declare
	--定义变量保存输入的数字
	pnum number := &num;
begin
	if pnum = 0 then dbms_output.put_line('你输入的数字为0');
		elsif pnum = 1 then dbms_output.put_line('你输入的数字为1');
		elsif pnum = 2 then dbms_output.put_line('你输入的数字为2');
		else dbms_output.put_line('其它数字');
	end if;
end;
/
--*******************************************************************
--循环(打印1~10)
set serveroutput on
declare
	--定义变量
	pnum number := 1;
begin
	loop
		--退出条件
		exit when pnum > 10;
		--打印
		dbms_output.put_line(pnum);
		--加一（不能用++）
		pnum := pnum + 1;
	end loop;
end;
/
--*******************************************************************
/*
光标的属性： %isopen		%rowcount(影响的行数)
			  %found		%notfound
*/
set serveroutput on
declare
	--定义光标(游标)
	cursor cemp is select ename,sal from emp;
	--定义引用型变量
	pename emp.ename%type;
	psal emp.sal%type;
begin
	--打开
	open cemp;
	loop
		--取当前记录
		fetch cemp into pename,psal;
		--exit when 没有取到记录;
		exit when cemp%notfound;
		dbms_output.put_line(pename||'的薪水是'||psal);
	end loop;
	--关闭
	close cemp;
end;
/
--*******************************************************************
--给员工涨工资，总裁1000，经理800，其他400
declare
	cursor cemp is select empno,job from emp;
	pempno emp.empno%type;
	pjob emp.job%type;
begin
	open cemp;
	loop
		--取一个员工
		fetch cemp into pempno,pjob;
		exit when cemp%notfound;
		--判断职位
		if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
			elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
			else update emp set sal=sal+400 where empno=pempno;
		end if;
	end loop;
	close cemp;
	commit;
	dbms_output.put_line('完成');
end;
/
--*******************************************************************
--查询某个部门的员工姓名
set serveroutput on
declare
	cursor cemp(dno number) is select ename from emp where deptno=dno;
	pename emp.ename%type;
begin
	open cemp(10);
	loop
		fetch cemp into pename;
		exit when cemp%notfound;
		dbms_output.put_line(pename);
	end loop;
	close cemp;
end;
/
--*******************************************************************
--被0除
set serveroutput on
declare
	pnum number;
begin
	pnum := 1/0;
	exception
		when zero_divide then dbms_output.put_line('1:0不能做分母');
							  dbms_output.put_line('1:0不能做分母');
		when value_error then dbms_output.put_line('算数或者转换错误');
		when others then dbms_output.put_line('其它例外');
end;
/
--*******************************************************************
--自定义例外
set serveroutput on
declare
	cursor cemp is select ename from emp where deptno = 50;
	pename emp.ename%type;
	--自定义例外
	no_emp_found exception;
begin
	open cemp;
	fetch cemp into pename;
	if cemp%notfound then
		--抛出例外
		raise no_emp_found;
	end if;
	close cemp;
	exception
		when no_emp_found then dbms_output.put_line('没有找到员工');
		when others then dbms_output.put_line('其它例外');
end;
/
--*******************************************************************
--统计每年入职的员工个数
set serveroutput on
declare
	cursor cemp is select to_char(hiredate,'yyyy') from emp;
	phiredate varchar2(4);
	--每年入职的人数
	count80 number := 0;
	count81 number := 0;
	count82 number := 0;
	count87 number := 0;
begin
	open cemp;
	loop
		--取一个员工的入职年份
		fetch cemp into phiredate;
		exit when cemp%notfound;
		--判断年份是哪一年
		if phiredate = '1980' then count80:=count80+1;
			elsif phiredate = '1981' then count81:=count81+1;
			elsif phiredate = '1982' then count82:=count82+1;
			else count87:=count87+1;
			end if;
	end loop;
	close cemp;
	dbms_output.put_line('Total:'||(count80+count81+count82+count87));
	dbms_output.put_line('1980:'|| count80);
	dbms_output.put_line('1981:'|| count81);
	dbms_output.put_line('1982:'|| count82);
	dbms_output.put_line('1987:'|| count87);
end;
/
--*******************************************************************
set serveroutput on
declare
	cursor cemp is select empno,sal from emp order by sal;
	pempno emp.empno%type;
	psal emp.sal%type;
	--涨工资的人数
	countEmp number := 0;
	--涨后的工资总额
	salTotal number;
begin
	--得到工资总额的初始值
	select sum(sal) into salTotal from emp;
	open cemp;
	loop
		--总额>5w
		exit when salTotal > 50000;
		--取一个员工
		fetch cemp into pempno,psal;
		--notfound
		exit when cemp%notfound;
		--涨工资
		update emp set sal = sal*1.1 where empno = pempno;
		--人数+1
		countEmp := countEmp + 1;
		--涨后=涨前+sal*0.1
		salTotal := salTotal + psal*0.1;
	end loop;
	close cemp;
	commit;
	dbms_output.put_line('人数:'||countEmp||'   总额:'||salTotal);
end;
/
--*******************************************************************
create table msg
(
pdeptno number,
count1 number,
count2 number,
count3 number,
saltotal number
);
set serveroutput on
declare
	cursor cdept is select deptno from dept;
	pdeptno dept.deptno%type;
	cursor cemp(dno number) is select sal from emp where deptno = dno;
	psal emp.sal%type;
	count1 number;
	count2 number;
	count3 number;
	salTotal number := 0;
begin
	--部门
	open cdept;
	loop
		--取一个部门
		fetch cdept into pdeptno;
		exit when cdept%notfound;
		--初始化
		count1 := 0;
		count2 := 0;
		count3 := 0;
		--得到部门的工资总额
		select sum(sal) into salTotal from emp where deptno = pdeptno;
		--取部门的中员工薪水
		open cemp(pdeptno);
		loop	
			--取一个员工的薪水
			fetch cemp into psal;
			exit when cemp%notfound;
			--判断
			if psal < 3000 then count1 := count1 + 1;
				elsif psal >= 3000 and psal < 6000 then count2 := count2 + 1;
				else count3 := count3 + 1;
			end if;						
		end loop;
		close cemp;
		--保存结果
		insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));		
	end loop;
	close cdept;
	commit;
	dbms_output.put_line('完成');
end;
/
--*******************************************************************
/*
	存储过程：没有返回值(用out可以指定输出参数)
	存储函数：有返回值
*/
--存储过程:
create or replace procedure sayhelloworld
as
	--说明部分
begin
	dbms_output.put_line('Hello World');
end;
/
--调用存储过程
exec sayhelloworld();

--给指定的员工涨100，并且打印涨前和涨后的薪水
create or replace procedure raiseSalary(eno in number)
is
	--定义变量保存涨前的薪水
	psal emp.sal%type;
begin
	--得到涨前的薪水
	select sal into psal from emp where empno = eno;
	--涨100
	update emp set sal = sal + 100 where empno = eno;
	dbms_output.put_line('涨前:'||psal||'   涨后:'||(psal+100));
end raiseSalary;
/
--调用存储过程
begin
	raiseSalary(7839);
	raiseSalary(7566);
	commit;
end;
/
--*******************************************************************
--存储函数:
create or replace function queryEmpIncome(eno in number)
return number
as
	--定义变量保存月薪和奖金
	psal emp.sal%type;
	pcomm emp.comm%type;
begin
	select sal,comm into psal,pcomm from emp where empno=eno;
	return psal*12+nvl(pcomm,0);
end queryEmpIncome;
/
--调用存储函数
declare
	result number;
begin
	result := queryEmpIncome(7839);
	dbms_output.put_line(result);
end;
/
--*******************************************************************
create or replace procedure queryEmpInformation(eno in number,pename out varchar2,psal out number,pjob out varchar2)
is
begin
	select ename,sal,job into pename,psal,pjob from emp where empno = eno;	
end queryEmpInformation;
/
--调用存储过程
begin
	queryEmpInformation(7839, :pename, :psal, :pjob);
end;
/
--*******************************************************************
--触发器
--每当成功插入新员工后，自动打印"成功插入新员工"
create trigger firsttrigger
after insert
on emp
declare
begin
	dbms_output.put_line('成功插入新员工');
end;
/
--*******************************************************************
--禁止在非工作时间插入新员工(表级触发器)
create or replace trigger securityemp
before insert
on emp
declare
begin
	if to_char(sysdate,'day') in ('星期六','星期日','星期二') or
	   to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
	   --禁止insert
	   raise_application_error(-20001,'不能在非工作时间插入新员工');
	end if;	
end securityemp;
/
--*******************************************************************
--涨后的薪水不能少于涨前的薪水(行级触发器)
create or replace trigger checksalary
before update
on emp for each row
declare
begin
	if :new.sal<:old.sal then 
		raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水');
	end if;
end checksalary;
/