oracle課堂筆記包含ddl冬骚，dml世囊，tcl别瞭，游標(biāo)，過程株憾，函數(shù)蝙寨，觸發(fā)器等詳細(xì)demo。志在幫助小白變大神
--創(chuàng)建
/*
create tablespace student1
datafile 'E:\oracle\product\10.2.0\oradata\dafiles\student.dbf'
size 10M
autoextend on
*/
drop table new_dept;
create table newdept
as select * from scott.dept;
create table salgrade
as select * from scott.salgrade;
--1. 創(chuàng)建一個(gè)學(xué)生表包含：學(xué)號嗤瞎、姓名墙歪、性別、年齡贝奇、出生日期等 字段虹菲。
create table student(
cno number(5) not null,
cname varchar(10),
sex char(2),
age number(2),
birthday date default sysdate
)
/
--2、修改學(xué)生表額外添加成績字段弃秆。
alter table Student
add (grade number(3,1));
--2-1届惋、給學(xué)號加上主鍵約束。
alter table Student
add constraint PK_Student_cno primary key (cno);
--3菠赚、給性別加檢查約束：提示 check(sex='男' or sex='女') 脑豹。
alter table Student
add constraint CHK_Student_sex check (sex in('男','女'));
--4、年齡加檢查約束衡查，年齡在18~25之間瘩欺；提示：check(age>=18 and age<=25)。
alter table Student
add constraint CHK_Student_age check (age between 18 and 25);
--5拌牲、向表里插入記錄來驗(yàn)證約束是否生效俱饿。
insert into Student
values (95009,'\張三','男',25,date'2011-02-15',96);
insert into Student
values (95002,'李四','女',23,date'2011-02-15',97);
insert into Student
values (95004,'王五','男',18,date'2019-8-15',96);
insert into Student
values (95003,'james','女',22,date'2019-8-15',96);
insert into Student
values (95005,'andy','男',18,'9-12月-14',96);
--6、查詢出表中的所有記錄塌忽。
select * from Student;
--7. 查詢出表中的所有記錄拍埠。
--8. 選擇部門為30中的所有員工
select * from new_scott;
select * from new_scott where deptno = 30 ;
--9. 列出所有辦事員（CLERK）的姓名，編號和部門編號
select distinct ename from new_scott where job='CLERK';
--10. 找出傭金高于薪金的員工土居。
select * from new_scott where comm>sal;
--11. 找出傭金高于薪金60%的員工
select * from new_scott where comm>(sal*0.6);
--12. 找出部門10中所有的（MANAGER）和部門20中所有辦事員（CLERK）枣购。
select * from new_scott
where (deptno=10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');
--插入信息
INSERT into new_scott values(7347,'Bob','CLERK',7876,to_date('1999-02-15','yyyy-mm-dd'),1000,0,10);
--
select * from new_scott where empno = '7347';
COMMIT;--提交
ROLLBACK;--回滾
--修改當(dāng)前回話的日期格式
--alter session set sysdate_formate = 'YYYY-MM-DD HH24:MI:SS';
--更新
update new_scott
set ename = 'james' where empno = 7347;
--刪除
delete new_scott
where empno = 7347;
--DCL ||||| commit:提交 rollback：回滾 savepoint ：表即可回滾的點(diǎn)
delete new_scott where ename = 'JONES';
savepoint mark1;
update new_scott set ename = 'chengui'
where empno = 7369;
savepoint mark2;
rollback to savepoint mark1;
--鏈接符||
select empno||'姓名'||ename||'工作'||job||'上司'||mgr||'入職時(shí)間'||hiredate||'工資'||sal||'傭金'||comm||'部門編號'||deptno from new_scott;
--like 匹配
select * from new_scott
where job like '_A%';
select * from new_scott
where ename like '\%' ;
select ename , sal,nvl(comm,0),sal+nvl(comm,0) salary from new_scott;
select * from new_scott
where mgr is not null;
--13. 找出收取傭金的員工的不同工作
select distinct job from new_scott
where comm is not null;
--14. 找出不收取傭金或收取的傭金不低于100的員工
select * from new_scott
where comm is null or comm<100
order by empno
--15. 找出部門10中所有的（MANAGER）和部門20中所有辦事員（CLERK）,
--和既不是經(jīng)理又不是辦事員但其薪金大于或等于2000的所有員工的詳細(xì)資料
select * from new_scott
where (deptno = 10 and job = 'MANAGER' or deptno = 20 AND JOB = 'CLERK' )
OR (JOB NOT IN ('MANAGER','CLERK') AND SAL>=2000);
--16. 找出各月倒數(shù)第三天受雇的所有員工
select * from new_scott
where extract(DAY from last_day(hiredate))- extract(DAY from (hiredate))=2;
select * from new_scott
--17. 找出早于25年前受雇傭的員工
select * from new_scott
where extract(year from systimestamp)- extract(year from (hiredate))>25;
--18. 以首字母大寫其它字母小寫的方式顯示所有的員工的姓名嬉探。
select empno,INITCAP(LOWER(ename)),job,mgr,hiredate,sal,comm,deptno from new_scott;
--以首字母小寫其它字母大寫的方式顯示所有的員工的姓名。
--CONCAT(UPPER(substr(LOWER(ename),1,1)) , UPPER(substr(UPPER(ename),1)))
select empno,CONCAT((substr(LOWER(ename),1,1)) , UPPER(substr(UPPER(ename),1))),
job,mgr,hiredate,sal,comm,deptno from new_scott;
--19. 顯示正好為5字符的員工的姓名
select ename from new_scott
where LENGTH(ename)=5;
--20. 顯示不帶有‘R’的與員工的姓名
select ename from new_scott
where ename not like '%R%';
--21. 顯示所有員工姓名的前三個(gè)字符棉圈。
select SUBSTR(ename,1,3) from new_scott;
--22. 顯示所有員工的姓名涩堤，用 A 替換 a
select TRANSLATE(ename,'A','a')from new_scott;
--23. 顯示 滿25年 服務(wù)年限的員工的姓名 和受雇日期
select ename,hiredate from new_scott
where extract(year from systimestamp)- extract(year from (hiredate))>25;
--MONTHS_BETWEEN(sysdate,hiredate)/12>25;
--24. 顯示員工的詳細(xì)資料，按姓名排序,姓名相同按工資降序排序分瘾。
select * from new_scott
order by ename ,sal desc;
--25. 顯示與員工的姓名和受雇日期胎围，根據(jù)其服務(wù)年限，將最老的員工排在最前面德召。
select ename,hiredate , (extract(year from systimestamp)- extract(year from (hiredate))) time from new_scott
order by time desc;
--26. 顯示所有員工的姓名白魂，工作和薪金，按工作的降序排序氏捞，若工作相同按薪金排序
select ename,job,sal from new_scott
order by job desc,sal desc;
--select * from new_scott;
--decode用法（條件碧聪，屬性1，值1液茎，屬性2逞姿，值2，……捆等，默認(rèn)值）
select ename,decode(job,'CLERK','辦事員','MANAGER','經(jīng)理',
'SALESMAN','銷售員','ANALYST','分析師','老板') from new_scott;
--27. 顯示所有員工的姓名滞造，加入公司的年份和月份，按受雇日期所在的月排序栋烤，若月份相同谒养，則將最早的年份的員工排在最前面
select ename,to_char(hiredate,'yyyy"年"mm"月"') from new_scott
order by extract(MONTH from hiredate) ,extract(YEAR from hiredate)
;
--經(jīng)典方法
select ename,job,TO_CHAR(hiredate,'yyyy')year,to_char(hiredate,'mm')month,form employee order by month,year;
/*-------------------------------------------------------- 分組查詢 -------------------------------------*/
--28. 顯示非銷售人員工作名稱以及從事同一工作雇員的月工資的總和，并且要滿足從事同一工作的雇員的月工資合計(jì)大于$5000明郭，
--輸出結(jié)果按月工資排序买窟。
select job,sum(sal) from new_scott
group by job
having sum(sal)>5000
order by sum(sal);
--29. 查詢出各部門的部門編號以及各部門的總工資和平均工資。
select deptno ,to_char(sum(sal),'L999,999,999.00'),to_char(avg(sal),'L999,999,999.00')from new_scott
group by deptno;
select * from new_scott;
--30. 按男生和女生統(tǒng)計(jì)JAVA和ORACLE成績的總分和平均分薯定？
select sum(JAVASCORE) AS java總成績 ,AVG(JAVASCORE) java平均成績,SUM(ORACLESCORE)oracle總成績 ,AVG(ORACLESCORE) Oracle平均成績 FROM STUDENT2
group by GENDER ;
-- 1） 建表
create table STUDENT2
(
STUNO CHAR(4) not null primary key,
STUNAME VARCHAR2(20),
GENDER CHAR(2),
JAVASCORE INTEGER,
ORACLESCORE INTEGER
);
-- 2） 插入記錄
INSERT INTO STUDENT2 VALUES('1000','JAMES','男',88,78);
INSERT INTO STUDENT2 VALUES('1001','JACK','男',86,79);
INSERT INTO STUDENT2 VALUES('1002','ANDY','女',76,78);
INSERT INTO STUDENT2 VALUES('1003','SAMMY','女',77,76);
INSERT INTO STUDENT2 VALUES('1004','frank','男',88,78);
INSERT INTO STUDENT2 VALUES('1005','bob','男',86,79);
INSERT INTO STUDENT2 VALUES('1006','july','女',76,78);
INSERT INTO STUDENT2 VALUES('1007','mark','女',77,76);
-- 統(tǒng)計(jì)成績
select stuno,stuname,javascore+oraclescore 總成績,(javascore+oraclescore)/2 平均成績 from STUDENT2
order by 總成績 desc;
select gender,sum(javascore),avg(javascore),sum(oraclesore),avg(oraclescore) form student2
group by gender;
create table newdept
as select * from scott.dept;
select * from newdept;
select * from new_scott;
--全鏈接
select * from newdept full join new_scott on new_scott.deptno = newdept.deptno;
select * from newdept left join new_scott on new_scott.deptno = newdept.deptno;
select * from newdept right join new_scott on new_scott.deptno = newdept.deptno;
--coalesce(expr1,expr2,expr3,....exprn) NVL 同始绍，前為空，則一直向后搜尋话侄，最后默認(rèn)為exprn
create table salgrade
as
select * from scott.salgrade;
select e.*
from new_scott n, salgrade s
where e.sal between s.lowsal and s.hisal;
select '工號為：'|| e.empno||'的員工的名字是'||e.ename||'的上司是'||temp.empno
||temp.ename from new_scott e join new_scott temp on e.mgr = temp.empno;
--工資分類
select e.* ,f.* from new_scott e join salgrade f
on e.sal >f.losal and e.sal< f.hisal;
create trigger tri_salgrade_insert
after insert
on salgrade
begin
RAISE_APPLICATION_ERROR ('YOU MAY ERRROR');
end;
INSERT into salgrade
values(6,4000,9999)
--查詢在‘NEW YORK’工作亏推，工資高于2000的員工以及員工的工資等級。
--方法一：
select n.*,s.grade ,dept.*
from new_scott n , salgrade s,newdept dept
where n.deptno = dept.deptno and n.sal>s.losal and n.sal<s.hisal and n.sal>2000 and dept.loc = 'NEW YORK'
--方法二：
select n.*,s.grade,dept.*
from new_scott n join salgrade s
on n.sal>s.losal and n.sal<s.hisal
join newdept dept
on dept.deptno = n.deptno and n.sal>2000 and dept.loc like 'NEW YORK';
select n.empno,n.ename,sal,loc
from new_scott n, (select * from newdept where newdept.loc = 'NEW YORK' and deptno = 10 ) S
where n.deptno = s.deptno;
select * from newdept;
select * from NEW_SCOTT;
select * from newdept;
/*-----------------------------------------------第三章 高級查詢 --------------------------------
--A. 查詢部門在‘NEW YORK’工資低于4000年堆，不是‘CLERK’的員工吞杭？
--B. 查詢部門在‘CHICAGO’，在1981年入職变丧，工資在2000~4000的員工芽狗？
/*----------------------------------------------- 子查詢 -----------------------------------------
子查詢注意的問題：
1、要將子查詢發(fā)在圓括號內(nèi)痒蓬。
2童擎、子查詢可出現(xiàn)在WHERE子句曼月、FROM子句、SELECT列表(此處只能是一個(gè)單行子查詢)柔昼、HAVING子句中。
3炎辨、子查詢不能出現(xiàn)在主查詢的GROUP BY語句中捕透。
4、子查詢和主查詢使用表可以不同碴萧，是要子查詢返回的結(jié)果能夠被主查詢使用即可乙嘀。
5、一般不會(huì)在子查詢中使用ORDER BY語句破喻，但在TOP-N(只需前幾條記錄)分析中必須使用ORDER BY語句虎谢。
6、單行子查詢只能使用單行操作符曹质，多行子查詢只能使用多行操作符婴噩。
7、采用合理的縮進(jìn)和換行來提過SQL語句的可讀性羽德。
8几莽、子查詢中的空值問題。
*/
--31. 查詢部門名稱為SALES和ACCOUNTING的員工信息
/*
1 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 30 SALES CHICAGO
2 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 30 SALES CHICAGO
3 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 30 SALES CHICAGO
4 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 30 SALES CHICAGO
5 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK
6 7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK
7 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 30 SALES CHICAGO
8 7900 JAMES CLERK 7698 1981/12/3 950.00 30 30 SALES CHICAGO
9 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK
*/
select * from new_scott e,newdept n
where e.deptno = n.deptno and n.dname in ('SALES','ACCOUNTING');
--32. 查詢不是經(jīng)理的員工的信息(使用in 或 not in來做)
/*
1 7369 SMITH CLERK 7902 1980/12/17 800.00 20
2 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
3 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
4 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
5 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
6 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7 7900 JAMES CLERK 7698 1981/12/3 950.00 30
8 7934 MILLER CLERK 7782 1982/1/23 1300.00 10
*/
select * from new_scott
where empno not in (select distinct mgr from new_scott where mgr is not null);
--33. 查詢工資比10號部門員工中任意一個(gè)低的員工信息(13)
select * from new_scott e
where e.sal < any (select distinct sal from new_scott where deptno = 10)
--34. 查詢工資比10號部門都要低的員工信息
select * from new_scott e
where e.sal <all (select distinct sal from new_scott where deptno = 10)
--35. 查詢出部門名稱宅静，部門員工數(shù)章蚣，部門平均工資，部門最低工資雇員的姓名姨夹，及工資等級
select n.dname,t.deptno,t.total,t.ag,t.mi,s.grade,e.ename
from new_scott e ,salgrade s,newdept n,(select deptno, count(*) total, avg(sal) ag, min(sal) mi from new_scott group by deptno) t
where e.deptno = t.deptno
and e.sal>=s.losal
and e.sal<=s.hisal
and n.deptno = e.deptno
and e.sal in (select mi from (select deptno, count(*) total, avg(sal) ag, min(sal) mi from new_scott group by deptno) t)
--36. 列出最低薪金大于1500的各種工作及此從事此工作的全部雇員人數(shù)
/*
1 ANALYST 2
2 MANAGER 3
3 PRESIDENT 1
*/
--方法一
select distinct(e.job),t.total
from new_scott e,(select job ,min(sal) mi,count(*) total from new_scott group by job)t
where e.job = t.job
and t.mi > 1500;
--方法二
select e.job, count(*)人數(shù)
from
emp e
group by e.job having (select min(sal) from emp where e.em)
--37. 求出在'salesman'部門工作的員工姓名纤垂，假設(shè)不知道銷售部的部門編號
select distinct e.ename from new_scott e,newdept n
where e.deptno = (select distinct deptno from new_scott where job = UPPER('salesman'));
--38. 列出薪金高于公司平均薪金的所有員工，所在部門磷账，上級領(lǐng)導(dǎo)峭沦，公司的工資等級
select e.ename,e.sal,n.dname,e.mgr,s.grade
from new_scott e,newdept n,salgrade s
where e.sal >(select avg(sal) from new_scott )
and e.deptno = n.deptno
and e.sal >s.losal and e.sal <=s.hisal;
--39. 列出于“SCOTT”從事相同工作的所有員工及部門名稱
select e.ename ,n.dname
from new_scott e,newdept n
where e.job = (select job from new_scott where ename = 'SCOTT')
and e.deptno = n.deptno;
--40. 查詢和SMITH部門相同 崗位相同的人
select * from new_scott e, newdept n
where e.deptno = (select deptno from new_scott where ename = 'SMITH' )
and e.job = (select job from new_scott where ename = 'SMITH')
and e.deptno = n.deptno;
--41. 和ALLEN同部門，工資高于MARTIN的雇員有哪些
select * from new_scott e
where e.deptno = (select deptno from new_scott where ename = 'ALLEN')
and e.sal > (select sal from new_scott where ename = 'ALLEN')
--42. 比blake工資高的雇員有哪些?
select * from new_scott e
where e.sal > (select sal from new_scott where ename = upper('blake'))
--43. 高于30部門最高工資的雇員有哪些?
select * from new_scott e
where e.sal > (select max(sal) from new_scott where deptno = 30)
--44. 查詢scott用戶下的emp表中所有的經(jīng)理的信息(此操作子查詢會(huì)返回多行記錄)
select * from new_scott
where empno in (select mgr from new_scott);
--45. 工資高于本部門平均工資的人（拿上游工資的人）有哪些够颠？****
select e.*
from new_scott e
where e.sal >(select avg(sal) from new_scott where deptno = e.deptno)
select e.deptno ,e.empno,e.sal,e.ename from new_scott e,(select deptno,avg(sal) ag from new_scott group by deptno ) t
where t.deptno = e.deptno and e.sal > t.ag
group by e.deptno ,e.empno,e.ename,e.sal;
--46. 工作和部門與SMITH相同熙侍，工資高于JAMES的雇員有哪些?
select * from new_scott e,newdept n
where e.job = (select job from new_scott where ename = 'SMITH')
and e.deptno = n.deptno
and n.deptno = (select new_scott.deptno from new_scott,newdept where ename = 'SMITH' and new_scott.deptno = newdept.deptno )
and e.sal >(select sal from new_scott where ename = 'JAMES');
/*---------------------------------------------- 多行子查詢 --------------------------------------*/
--47. 列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金
select e.ename,e.sal from new_scott e
where e.sal in (select sal from new_scott where deptno = 30);
--48.列出薪金大于部門30中員工的薪金的所有員工的姓名和薪金
select e.ename,e.sal from new_scott e
where e.sal>any (select sal from new_scott where deptno = 30);
--49.列出每個(gè)部門工作的員工數(shù)量馅袁，平均工資和平均服務(wù)年限
select deptno ,count(*) 員工數(shù)量 ,to_char(avg (sal),'L999,999.00'),to_char(avg(months_between(sysdate,hiredate)/12),'999.00') from new_scott
group by deptno;
select *
from new_scott e,
--刪除沒有員工的部門
--方法一
delete newdept
where deptno in (select deptno from newdept MINUS select distinct deptno from new_scott)and deptno <> 40;
--方法二
delete newdept
where deptno not in (select deptno from new_scott)
--創(chuàng)建同義詞：
-- 1)創(chuàng)建私有同義詞 注意：需要sys授權(quán)：grant create SYNONYM to stu;
create or replace public synonym emp for new_scott ;
-- 2）創(chuàng)建共有同義詞 注意：需要sys授權(quán)：grant create public SYNONYM to stu;
create public synonym dept1 for newdept;
--drop public synonym dept;
--創(chuàng)建序列：
create sequence emp_seq
start with 8002
increment by 1
minvalue 8002
maxvalue 999999999999
cycle
cache 20
--select emp_seq.nextval from emp_seq;--執(zhí)行emp_seq.currval 之前一定要執(zhí)行一次 emp_seq.nextval
select emp_seq.currval from dual;
commit;
drop sequence emp_seq;
insert into emp(empno,ename,job,mgr,deptno)
values(emp_seq.nextval ,'james','CLERK',7698,20)
--創(chuàng)建視圖
create view emp_view
as
select * from emp;
select * from emp_view;
--刪除視圖
drop view emp_view;
select empno,ename,dname from emp e,newdept d where e.deptno = d.deptno AND e.ename<>'SCOTT' AND e.job=(select job from emp where ename='SCOTT');
--創(chuàng)建索引
create index emp_job on emp(job);
--驗(yàn)證索引
select * from emp where job = upper('salesman');
--第一個(gè)小例子
BEGIN
dbms_output.put_line('最簡單的PL/SQL');
END;
declare
v_money number(4,1) := 2.3;
begin
dbms_output.put_line(v_money);
end;
--第二個(gè)小例子
DECLARE
v_ename VARCHAR2(20); --運(yùn)行時(shí)在輸入
v_ename2 v_ename%type; --引用另一個(gè)變量的類型
v_empno NUMBER(4) not null := 7999; --初始化
v_empno2 NUMBER(4); --運(yùn)行時(shí)在輸入
v_job emp.job%type; --變量與job列的數(shù)據(jù)類型和寬度一致
v_hiredate emp.hiredate%type default sysdate; --賦默認(rèn)值
v_sal CONSTANT emp.sal%type := 3000; --常量
BEGIN
--賦值方式 1：
v_ename := '&請輸入員工姓名：'; --&號表示一個(gè)替代變量崎脉，可在運(yùn)行時(shí)輸入值烘豹，字符串需用單引號括起來
v_empno2 := &請輸入員工編號;
--賦值方式 2： select 列名列表 into 變量列表（對號入座）
--但是要注意現(xiàn)在給變量賦值不能返回多行劲室，要處理多行的查詢結(jié)果需要用到游標(biāo)
SELECT job INTO v_job FROM emp WHERE empno=v_empno2; --8001
dbms_output.put_line('員工編號:'||v_empno2||', 員工姓名：'||v_ename||', 職務(wù)：'||v_job||', 入職時(shí)間：'||v_hiredate);
END;
--更新方式(emp 為 new_scott 的同義詞 synonym)
declare
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_sal emp.sal %TYPE;
begin
v_empno:=&員工工號;
select ename,sal into v_ename,v_sal from emp where emp.empno = v_empno;
dbms_output.put_line(v_empno||' 的名字是：'||v_ename||' 薪水是：'||v_sal);
if v_sal < 3000 then
update emp
set sal = sal +500 where empno = v_empno;
dbms_output.put_line(v_ename||'加薪完成家制！');
else
dbms_output.put_line('薪水已經(jīng)很高了朝墩！');
end if;
end;
--IF-ELSE案例
--create or replace procedure emp_add_sal
--is
-- begin
declare
v_empno emp.empno%
rec_emp emp%rowtype;
begin
v_empno := &員工編號;
--更新前
select * into rec_emp from emp where empno = v_empno;
dbms_output.put_line('更新前：員工編號：'||rec_emp.empno||', 員工名字：'||rec_emp.ename||'衩匣， 員工工資：'||rec_emp.sal);
if(rec_emp.sal<3000) then
update emp set sal = sal-500 where empno = v_empno;
else
update emp set sal = sal-200 where empno = v_empno;
end if;
-- commit;
--更新后
select * into rec_emp from emp where empno = v_empno;
dbms_output.put_line('更新后：員工編號：'||rec_emp.empno||', 員工名字：'||rec_emp.ename||'擂仍， 員工工資：'||rec_emp.sal);
--處理沒有找到數(shù)據(jù)的異常
exception
when NO_DATA_FOUND THEN
dbms_output.put_line('查無此人矛辕！');
when others then
dbms_output.put_line('錯(cuò)誤代碼：'||SQLCODE ||',錯(cuò)誤信息：'||SQLERRM);
end;
--end emp_add_sal;
/
--drop procedure emp_add_sal;
--CASE_WHEN
/* ---------------------------------------第五章 PL/SQL 作業(yè)-----------------------------------*/
--A. 使用LOOP循環(huán)求1-100之間的素?cái)?shù)
/*
50. 打印99乘法表
1*1=1
2*1=2 2*2=4
3*1=3 3*2=6 3*3=9
*/
--第一種方式
declare
v_score integer default 0;
begin
v_score:= &成績;
case
when v_score >=80 then
dbms_output.put_line('優(yōu)秀');
when v_score >=70and v_score<80 then
dbms_output.put_line('良好');
when v_score >=60 and v_score <70 then
dbms_output.put_line('一般');
else
dbms_output.put_line('不及格');
end case;
end;
--第二種方式
declare
v_grade char(2);
begin
case '&grade' -- 此處一定注意：如果是字符型一定要加上 '' 否者報(bào)錯(cuò)笑跛，付魔，，如果是數(shù)值型則不需要加 ’‘
when 'A' then
dbms_output.put_line('優(yōu)秀');
when 'B' then
dbms_output.put_line('良好');
when 'C' then
dbms_output.put_line('一般');
else
dbms_output.put_line('不及格');
end case;
end;
--循環(huán)
declare
v_row int:= &行數(shù);
v_counter1 int :=1;
v_counter2 int :=1;
begin
loop
v_counter1 := 1;
loop
dbms_output.put('*');
exit when v_counter1=v_counter2;
v_counter1:=v_counter1+1;
end loop;
dbms_output.new_line;
v_counter2:=v_counter2+1;
exit when v_counter2 = v_row;
end loop;
end;
--使用LOOP循環(huán)求1-100之間的素?cái)?shù)
declare
v_prime int :=2;
v_counter int default 1;
counter int default 0;
num int default 0;--統(tǒng)計(jì)個(gè)數(shù)
begin
loop
counter:=0;--計(jì)數(shù)器（每次重新開始）
v_counter:=2;--除數(shù) 從 2~v_prime 除（每次重新開始）
loop
if v_prime mod v_counter = 0 then
counter:=counter+1;
end if;
exit when v_counter = v_prime or counter > 2; --內(nèi)循環(huán)結(jié)束條件 （2個(gè)條件滿足一個(gè)即可跳出循環(huán)）
v_counter:=v_counter+1;--除數(shù)自加
end loop;
if counter = 1 then--輸出語句
num:= num+1;
if num<>1 then
dbms_output.put(',');
end if;
dbms_output.put(v_prime);
end if;
exit when v_prime=100;--外循環(huán)結(jié)束條件
v_prime:= v_prime+1;--外循環(huán)自加
end loop;
dbms_output.new_line;
end;
--九九乘法表
declare
counter1 int default 1;
counter2 int default 1;
begin
counter1 :=&乘法表范圍;
for i in 1..9 loop
for j in 1..i loop
dbms_output.put(i||'*'||j||'='||i*j);
if(j<>i)then dbms_output.put(',');
end if;
end loop;
dbms_output.new_line;
end loop;
end;
--52.使用FOR循環(huán)求1-100之間的素?cái)?shù)
declare
counter int :=0;
begin
for i in 2..100 loop
counter:=0;
for j in 2..i loop
if mod(i,j)=0 then
counter:=counter+1;
end if;
exit when counter >1;
end loop;
if counter = 1 then
dbms_output.put(i||',');
end if;
end loop;
dbms_output.new_line;
end;
/*
練習(xí)：
根據(jù)部門名稱飞蹂，按以下格式打印"RESEARCH"部門所有人員姓名：
部門名稱：RESEARCH
部門人員：SMITH,JONES,FORD
*/
declare
counter int ;
v_ename emp.ename%type;
begin
dbms_output.put_line('部門名稱：'||'RESEARCH');
dbms_output.put('部門人員：');
select count(*) into counter from emp e,newdept n --統(tǒng)計(jì)有多少個(gè)符合條件的
where e.deptno = n.deptno
and n.dname = 'RESEARCH';
for i in 1..counter loop --由于每次只能打印一個(gè)几苍，所以用for循環(huán)
select ename into v_ename from
(
select rownum r,ename --用偽列rownum 為每一個(gè)符合的條件做個(gè)編號，以便輸出
from emp e join newdept n
on e.deptno = n.deptno
and n.dname = 'RESEARCH'
)a
where a.r = i;
dbms_output.put(v_ename);
if i<>counter then
dbms_output.put(',');
end if;
end loop;
dbms_output.new_line();
end;
--51. 根據(jù)工資查詢員工姓名陈哑。如果此員工不存在（發(fā)出NO_DATA_FOUND異常）妻坝，則打印相應(yīng)的提示信息。
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
v_sal := &請輸入工資;
select ename into v_ename from emp
where sal = v_sal;
dbms_output.put_line('工資為：'||v_sal||'的員工姓名是：'||v_ename);
exception
when NO_DATA_FOUND then
dbms_output.put_line('查無此人惊窖！');
end;
---自定義異常
declare
v_empno emp.empno%type:=&加薪員工的工號;
v_count int default 0;
v_sal emp.sal%type;
invlite_sal exception;--自定義異常
no_found exception; --自定義異常
begin
v_sal:=&工資;
if v_sal<0 then
raise invlite_sal; -- 手動(dòng)拋出異常
end if;
select count(*) into v_count from emp where empno = v_empno;
if(v_count = 0) then
raise no_found ; --手動(dòng)拋出異常
end if;
exception --捕獲異常
when no_found then
dbms_output.put_line('沒有符合的信息刽宪！');
when invlite_sal then
dbms_output.put_line('工資不可為負(fù)！');
end;
--記錄類型
declare
type rec_emp_type is record(
empno emp.empno%type,
ename emp.ename%type,
sal emp.sal%type
);
no_found exception;
rec rec_emp_type ;
v_count int:=0;
v_empno emp.empno%type:=&請輸入員工工號;
begin
select count(*)into v_count from emp where empno = v_empno;
if v_count = 0 then
raise no_found;
end if;
select empno,ename,sal into rec from emp where empno = v_empno;
dbms_output.put_line('員工工號：'||rec.empno ||'員工姓名：'||rec.ename ||'員工薪水：'||rec.sal);
exception
when no_found then
-- raise_application_error(-20010,'查無此人');
dbms_output.put_line('查無此人');
end;
-------------------------------------------第六章 游標(biāo)---------------------------------------------------
----游標(biāo)(loop訪問)
declare
cursor cv_emp is --1.定義游標(biāo) cursor 游標(biāo)名 is （select 語句）
select e.* ,rownum from emp e;
rec_emp cv_emp%rowtype; --定義一個(gè)游標(biāo)類型的變量界酒，保存每次結(jié)果
begin
open cv_emp; --2.打開游標(biāo) open 游標(biāo)名圣拄；
loop
fetch cv_emp into rec_emp; -- 3.從游標(biāo)中獲取數(shù)據(jù) ： fetch 有標(biāo)明 into 變量
dbms_output.put_line(rec_emp.empno||' '||rec_emp.rownum);
exit when cv_emp%NOTFOUND;
end loop;
close cv_emp; --4.關(guān)閉游標(biāo) close 游標(biāo)名
end;
--while訪問
declare
cursor cv_emp is
select e.*,rownum from emp e ;--order by empno
rec_emp cv_emp%rowtype;
begin
open cv_emp;
fetch cv_emp into rec_emp; --因?yàn)橛螛?biāo)是從0開始，第零個(gè)沒有數(shù)據(jù)毁欣，所以要跳過庇谆，
while cv_emp%FOUND LOOP
dbms_output.put_line(rec_emp.empno||' '||REC_EMP.ROWNUM);
fetch cv_emp into rec_emp;
END LOOP;
close cv_emp;
end;
--for
declare
cursor cv_emp is
select e.*,rownum from emp e ;--order by empno
begin
for rec_emp in cv_emp loop --for 不需要特意定義 游標(biāo)變量，因?yàn)? ORACLE 自動(dòng)分配計(jì)數(shù)器變量
dbms_output.put_line(rec_emp.empno||' '||rec_emp.rownum);
end loop ;
end;
--for 升級版
begin
for rec_emp in (select e.*,rownum from emp e ) loop --直接用select 語句作為 匿名的 cursor
dbms_output.put_line(rec_emp.empno||' '||rec_emp.rownum);
end loop ;
end;
/* ---------------------------------------------第六章 游標(biāo)管理 作業(yè)----------------------------------*/
/*
（一）什么是游標(biāo)：
（二）隱式游標(biāo)：在 PL/SQL 程序中執(zhí)行DML SQL 語句時(shí)自動(dòng)創(chuàng)建隱式游標(biāo)署辉。 并且只能訪問最近執(zhí)行的一條DML語句族铆，查詢只能返回一行。
（三）顯式游標(biāo)用于處理返回多行的查詢哭尝。
1）無參數(shù)的顯式游標(biāo)
2）帶參數(shù)的顯式游標(biāo)
語法：
cursor 游標(biāo)名(參數(shù)名 類型) is select_statement;
（四）隱式游標(biāo)的特性：
1）在PL/SQL中使用DML語句時(shí)自動(dòng)創(chuàng)建隱式游標(biāo)
2）隱式游標(biāo)自動(dòng)聲明哥攘、自動(dòng)打開和自動(dòng)關(guān)閉，其名為: SQL
3）通過檢查隱式游標(biāo)的屬性可以獲得最近一次執(zhí)行的DML 語句的信息
4）游標(biāo)的屬性有：
（1）%FOUND – SQL語句查詢或影響了一行或多行時(shí)為 TRUE
（2）%NOTFOUND – SQL語句沒有影響任何行時(shí)為 TRUE
（3）%ROWCOUNT – SQL語句影響的行數(shù)
（4）%ISOPEN - 檢查游標(biāo)是否打開材鹦，隱式游標(biāo)始終為FALSE
（五）使用游標(biāo)更新行
1) 查詢時(shí)使用 select .. from table where 條件 for update [of column [nowait]]子句鎖定需要更新的行或列逝淹。
2) update employee set sal=sal-2 where current of 游標(biāo);
*/
--54. 顯示EMP中的第四條記錄。 如：游標(biāo)%rowcount=4
declare
cursor cv_emp is select * from emp;
rec_emp cv_emp%rowtype;
begin
open cv_emp;
loop
fetch cv_emp into rec_emp; --先移動(dòng)桶唐，再讀取
if cv_emp%rowcount = 4 then
dbms_output.put_line(rec_emp.empno||' '||rec_emp.ename);
end if;
exit when cv_emp%rowcount = 4;
end loop;
close cv_emp;
end;
/*
55：針對所有部門栅葡，按以下格式打印各部門人員姓名：
部門名稱：RESEARCH
部門人員：SMITH,JONES,FORD
部門名稱：ACCOUNTING
部門人員：CLARK,KING,MILLER
兩種實(shí)現(xiàn)提示：
1）循環(huán)每個(gè)部門，用其部門號作條件去查員工表
2）用顯示cursor完成
3）要求用FOR尤泽，會(huì)用到嵌套循環(huán)欣簇。
*/
--方法一：不帶參數(shù)游標(biāo)
declare
begin
for rec_emp in (select dname,deptno from newdept d) loop
dbms_output.put_line('部門名稱：'||rec_emp.dname);
dbms_output.put('部門人員：');
for rec_tep in (select e.ename ename ,e.deptno deptno from emp e join newdept n on e.deptno = n.deptno ) loop
if rec_tep.deptno = rec_emp.deptno then
dbms_output.put(rec_tep.ename||',');
end if;
end loop;
dbms_output.new_line;
end loop;
end;
--方式二：帶參數(shù)游標(biāo)
declare
cursor cv_dept is
select * from newdept ;
cursor cv_employee (cp_deptno emp.deptno%type)--傳遞一個(gè)員工部門參數(shù)，根據(jù)參數(shù)鎖定該部門信息
is select * from emp where deptno = cp_deptno;
begin
for cp_dept in cv_dept loop
dbms_output.put_line('部門名稱：'||cp_dept.dname);
dbms_output.put('部門員工：');
for cp_emp in cv_employee(cp_dept.deptno) loop --內(nèi)循環(huán)中的參數(shù)是外循環(huán)給的坯约，通過每一個(gè)部門參數(shù)熊咽，打印結(jié)果
dbms_output.put(cp_emp.ename||',');
end loop;
dbms_output.new_line;
dbms_output.new_line; --空一行
end loop;
end;
/*
56. 對所有員工,如果該員工職位是MANAGER，并且在DALLAS工作那么就給他薪金加15％闹丐；如果該員工職位是CLERK横殴，并且在NEW
YORK工作那么就給他薪金扣除5％;其他情況不作處理
*/
--方式二： 游標(biāo)
declare
cursor cv_emp(p_job emp.job%type,p_loc newdept.loc%type)is
select e.sal,e.deptno,n.loc from emp e,newdept n where e.deptno = n.deptno and p_job = e.job and p_loc = n.loc for update;
v_job emp.job%type;
v_loc newdept.loc%type;
begin
v_job:='&工作：';
v_loc:='&地點(diǎn)：';
for rec_emp in cv_emp(v_job,v_loc) loop
if v_job='MANAGER' and v_loc = 'DALLAS' then
update emp set sal= sal*(1+0.15) where current of cv_emp;
end if;
if v_job='CLERK' and v_loc = 'NEW YORK' then
update emp set sal= sal*(1-0.05) where current of cv_emp;
end if;
end loop;
end;
--方式一:
declare
cursor cv_emp is select empno ,e.job job, n.loc loc from emp e join newdept n on e.deptno = n.deptno for update;
begin
FOR rec_emp in cv_emp loop
if rec_emp.job ='MANAGER' and rec_emp.loc ='DALLAS' then
update emp set sal= sal*(1+0.15) where empno = rec_emp.empno; --where current of cv_emp;
DBMS_OUTPUT.PUT_LINE('加薪10%完成！');
else if rec_emp.job ='CLERK' and rec_emp.loc ='NEW YORK' then
update emp set sal= sal*(1-0.05) where empno = rec_emp.empno; -- where current of cv_emp;
DBMS_OUTPUT.PUT_LINE('減薪5%完成卿拴！');
end if;
end if;
end loop;
end;
--驗(yàn)證過程
select empno ,e.job job, n.loc loc ,e.sal from emp e join newdept n on e.deptno = n.deptno
and e.job = 'MANAGER' and n.loc = 'DALLAS';
--1 7566 MANAGER DALLAS 4275.00
--1 7566 MANAGER DALLAS 5653.69
select empno ,e.job job, n.loc loc,e.sal from emp e join newdept n on e.deptno = n.deptno
and e.job = 'CLERK' and n.loc = 'NEW YORK';
--1 7934 CLERK NEW YORK 1300.00
--1 7934 CLERK NEW YORK 1433.25
--57.(使用游標(biāo)更新行) 編寫一PL/SQL衫仑，對所有的"銷售員"(SALESMAN)增加傭金500.
-- 1) 查詢時(shí)使用 select .. from table where 條件 for update [of column [nowait]]子句鎖定需要更新的行或列梨与。
-- 2) update employee set sal=sal-2 where current of 游標(biāo);
declare
cursor cv_emp(p_job emp.job%type) is select * from emp for update OF sal nowait;
v_job emp.job%type;
begin
v_job:= '&請輸入工作';
for rec_emp in cv_emp(v_job) loop
update emp set comm= comm+500 where current of cv_emp; --update employee set sal=sal-2 where current of 游標(biāo);
end loop;
end;
--58. 編寫一PL/SQL，以提升兩個(gè)資格最老的"CLERK(職員)"為"HIGHTCLERK(高級職員)"文狱。（工作時(shí)間越長粥鞋，優(yōu)先級越高）
--alter table new_scott modify ( job varchar2(10));
declare
cursor cv_emp is select * from emp order by hiredate ;
begin
for rec_emp in cv_emp loop
if cv_emp%rowcount <=2 then
update emp set job = 'HIGHTCLERK' where empno = rec_emp.empno;
end if;
end loop;
end;
--方式二：游標(biāo)方式
declare
--游標(biāo)選定job為CLERK 的員工，并按受雇時(shí)間
cursor cv_emp is
select hiredate ,job from emp where job = 'CLERK' order by hiredate for update ;
begin
for rec_emp in cv_emp loop
if cv_emp%rowcount <3 then --限制只修改兩個(gè)
update new_scott set job = 'HIGHTCLERK' where current of cv_emp;
end if;
end loop;
end;
select * from new_scott;
/*
59. 對直接上級是'BLAKE'的所有員工瞄崇，按照參加工作的時(shí)間加薪：
81年6月以前的加薪10％
81年6月以后的加薪5％
*/
declare
cursor cv_emp is select * from emp where mgr = (select empno from emp where ename = 'BLAKE');
begin
for rec_emp in cv_emp loop
if MONTHS_BETWEEN(rec_emp.hiredate,to_date('1981/6/1','yyyy/mm/dd'))>0 then
update emp set sal=sal*(1+0.05) where empno = rec_emp.empno;
else
update emp set sal=sal*(1+0.1) where empno = rec_emp.empno ;
end if;
end loop;
end;
--方式二：游標(biāo)方法
declare
cursor cv_emp(p_ename emp.ename%type) is
select * from emp where mgr = (select empno from emp where ename = p_ename) for update of sal;
v_ename emp.ename%type;
begin
v_ename:= '&上級姓名';
for rec_emp in cv_emp(v_ename) loop
if MONTHS_BETWEEN(rec_emp.hiredate,to_date('1981/6/1','yyyy/mm/dd'))>0 then
update emp set sal=sal*(1+0.05) where current of cv_emp;
else
update emp set sal=sal*(1+0.1) where current of cv_emp ;
end if;
end loop;
end;
/*----------------------------------------------------第7章 過程和函數(shù)----------------------------------------------*/
/*
60. 編寫一個(gè)給指定雇員加薪10%的過程：Raise_Sal(p_ename)陷虎，這之后，檢查如果已經(jīng)雇傭該雇員超過60個(gè)月杠袱，則給他額外加薪3000.
*/
create or replace procedure Raise_Sal(p_ename emp.ename%type)
is
cursor cv_rise(p_ename emp.ename%type) is select * from emp where ename = p_ename for update of sal;
cursor cv_emp is select * from emp where months_between(sysdate,hiredate)>60 for update of sal;--后一部分
begin
for emp in cv_rise(p_ename) loop
update emp set sal=sal*(1+0.1) where current of cv_rise ;
end loop;
for emp in cv_emp loop
update emp set sal = sal+3000 WHERE current of cv_emp ;
end loop;
--commit;
exception
when others then
rollback;
dbms_output.put_line('操作有誤，已經(jīng)撤銷窝稿！');
end;
--調(diào)用
declare
v_ename emp.ename%type;
begin
v_ename:='&需要加薪的員工的名字：';
Raise_Sal(v_ename);
end;
--驗(yàn)證
select * from emp;
/*
61. 編寫一個(gè)過程打印出99的乘法表楣富。
*/
create or replace procedure pro_9_9
is
begin
for i in 1..9 loop
for j in 1..i loop
dbms_output.put(i||'*'||j||'='||i*j||' ');
end loop;
dbms_output.new_line;
end loop;
end;
--調(diào)用過程
begin
pro_9_9;
end;
/*
62.編寫一個(gè)過程完成兩個(gè)數(shù)字的交換
*/
create or replace procedure date_change(no1 in out int,no2 in out integer )
is
v_temp int ;
begin
v_temp := no1;
no1 := no2;
no2 := v_temp;
end;
--調(diào)用
declare
v_on1 int :=&請輸入第一個(gè)數(shù)字;
v_on2 int :=&請輸入第二個(gè)數(shù)字;
begin
date_change(v_on1,v_on2);
dbms_output.put_line('交換后：第一個(gè)數(shù)字：'||v_on1||' 第二個(gè)參數(shù)：'||v_on2);
end;
/*
--drop table log_msg cascade constraint;//刪除表同時(shí)刪除約束
63. 編寫三個(gè)過程完成
(1)開戶功能。open_account(.....);
(2)兩個(gè)賬戶的轉(zhuǎn)賬功能伴榔。transaction(from帳戶纹蝴，to帳戶,money)
a)檢查帳戶是否存大？不存在的情況出異常踪少。
b)轉(zhuǎn)入轉(zhuǎn)出帳戶必須要在一個(gè)事務(wù)里進(jìn)行塘安。
(3)根據(jù)轉(zhuǎn)出賬號和交易時(shí)間段 可查詢轉(zhuǎn)出賬號的所有交易記錄。最后還需要返回該時(shí)間段轉(zhuǎn)出的總金額援奢。
a)向“交易日志表”插入數(shù)據(jù)兼犯。
b)交易號的生成規(guī)則：'T10000-'||trans_seq.nextval --> T10000-1000
表結(jié)構(gòu)如下：
1. 賬戶表：賬號（6722 6738 xxxx yyyy）,戶名，身份證號集漾，余額切黔，開戶時(shí)間。
說明：賬號后8位數(shù)隨機(jī)生成具篇。dbms_random.value函數(shù)可以得一個(gè)八位小數(shù)的隨機(jī)小數(shù)纬霞。
select dbms_random.value from dual;
select trunc(dbms_random.value*100000000,0) from dual;
2. 交易日志表：交易號(T10000-999)，轉(zhuǎn)出賬戶驱显，轉(zhuǎn)入賬戶诗芜，交易金額，交易時(shí)間systimestamp埃疫。
說明：交易號使用序列生成伏恐。
*/
--1.custome_account表
create table custome_account (
accountno varchar2(16) not null primary key ,
aname varchar2(6) not null ,
idno varchar2(18)unique,
money number(20,3) check (money>0),
opentime date default systimestamp
)
--1.1建立賬號表視圖
create or replace view acc
as
select * from custome_account;
--2.logtable 表
create table log_msg (
tradeno varchar2(11)not null primary key,
fromaccount varchar2(16) ,
toaccourt varchar2(16),
money number(20,3),
tradetime date default systimestamp,
constraint FK_ACCOUT_LOG_FROMACCOUT foreign key(fromaccount) referenceS custome_account(accountno),
constraint FK_ACCOUT_LOG_TOACCOUT foreign key(toaccourt) referenceS custome_account(accountno)
)
--1.1建立交易表視圖
create or replace view tra
as
select * from log_msg;
--1.2 建立開戶過程
create or replace procedure open_account(p_aname acc.aname%type,aid acc.idno%type,money acc.money%type, p_accountno out acc.ACCOUNTNO%type )
is
v_rand number(20);
v_num int default 0;
v_account acc.ACCOUNTNO%type;
id_has_exist exception; --該身份證已經(jīng)開過戶
name_has_exist exception; --該姓名已經(jīng)注冊
begin
select count(*) into v_num from acc where aid= idno;
if v_num > 0 then
raise id_has_exist;
end if;
select count(*) into v_num from acc where ANAME= p_aname ;
if v_num > 0 then
raise name_has_exist;
end if;
select trunc(dbms_random.value*100000000,0) into v_rand from dual;--產(chǎn)生一個(gè)8位的隨機(jī)數(shù)
v_account:=CONCAT('67226738',to_char(v_rand)); --根據(jù)前綴產(chǎn)生一個(gè)賬號
p_accountno:=v_account; --作為返回值返回
insert into acc values(v_account,p_aname,aid,money,sysdate);
dbms_output.put_line(v_account||' '||p_aname||' '||aid||' '||money||' '||sysdate);--檢驗(yàn)輸出
commit;
exception
when id_has_exist then
raise_application_error('-20012','該身份證已經(jīng)開戶,請換其他身份證繼續(xù)！熔恢！');
when name_has_exist then
raise_application_error('-20002','該客戶已經(jīng)存在脐湾，不能重名！叙淌！');
when others then
rollback;
raise_application_error('-20011','操作失敗,已撤銷３诱啤愁铺！');
end open_account;
--1.3 測試(------------------------------------------開戶過程----------------------------------------)
declare
aname acc.aname%type;
aid acc.idno%type;
money acc.money%type;
v_account acc.ACCOUNTNO%type;--記錄返回賬號
begin
aname:='&開戶姓名';
aid:='&身份證號';
money:=&余額;
open_account(aname,aid,money,v_account);
dbms_output.put_line('恭喜您開戶成功！您的賬戶為：'||v_account);
end ;
select * from acc;
--2.1 轉(zhuǎn)賬功能工程transaction
create or replace procedure pro_tract(p_fromaccount tra.FROMACCOUNT%type,
p_toaccount tra.TOACCOURT%type,p_money tra.MONEY%type)
is
v_counter int default 0;
v_money tra.MONEY%type;
no_found_from exception;
no_found_to exception;
no_enough exception;
data_error exception;
no_equals exception;
begin
if p_money<0 then
raise data_error;
end if;
select count(*) into v_counter from acc where ACCOUNTNO = p_fromaccount;
if v_counter = 0 then
raise no_found_from;
end if ;
select money into v_money from acc where ACCOUNTNO = p_fromaccount;
if (v_money- p_money)<0 then
raise no_enough;
end if ;
select count(*) into v_counter from acc where ACCOUNTNO = p_toaccount;
if v_counter = 0 then
raise no_found_to;
end if ;
if p_fromaccount = p_toaccount then
raise no_equals;
end if;
update acc set money = money-p_money where acc.ACCOUNTNO = p_fromaccount;
update acc set money = money+p_money where acc.ACCOUNTNO = p_toaccount;
insert into tra(tradeno,fromaccount,toaccourt,money) values ( to_char('T10000-'||to_char(sq_tra_log.nextval)) ,p_fromaccount, p_toaccount,p_money);-----------------------------------------
dbms_output.put_line('轉(zhuǎn)賬成功闻鉴！');
commit;
exception
when no_found_from then
raise_application_error('-20013','轉(zhuǎn)出賬戶不存在茵乱！');
when no_found_to then
raise_application_error('-20014','轉(zhuǎn)入賬戶不存在！');
when no_enough then
raise_application_error('-20015','轉(zhuǎn)出賬戶余額不足孟岛！');
when data_error then
raise_application_error('-20016','轉(zhuǎn)賬數(shù)額不能為負(fù)值瓶竭！');
when no_equals then
raise_application_error('-20030','轉(zhuǎn)出賬戶與轉(zhuǎn)入賬戶相同！');
when others then
raise_application_error('-20017','操作失敗,動(dòng)作已撤銷渠羞！');
rollback;
end pro_tract;
select * from acc;
select * from tra;
--2.2 測試(-------------------------------------------------轉(zhuǎn)賬過程測試----------------------------------)
declare
v_fromaccount tra.FROMACCOUNT%type;
v_toaccount tra.TOACCOURT%type;
v_money tra.MONEY%type;
begin
v_fromaccount :='&轉(zhuǎn)賬賬號';
v_toaccount :='&收賬賬號';
v_money:=&轉(zhuǎn)賬金額;
pro_tract(v_fromaccount,v_toaccount,v_money);
commit;
/* exception -- 加了這個(gè)異常后斤贰，raise_application_error 不在顯示
when others then
dbms_output.put_line('轉(zhuǎn)賬失敗次询！');*/
end;
--2.3創(chuàng)建序列
create sequence sq_tra_log
start with 999;
--3.1
/* (3)根據(jù)轉(zhuǎn)出賬號和交易時(shí)間段 可查詢轉(zhuǎn)出賬號的所有交易記錄荧恍。最后還需要返回該時(shí)間段轉(zhuǎn)出的總金額。
a)向“交易日志表”插入數(shù)據(jù)屯吊。
b)交易號的生成規(guī)則：'T10000-'||trans_seq.nextval --> T10000-1000
*/
create or replace procedure pro_search_trace(p_fromaccount tra.FROMACCOUNT%type,
begintime date,endtime date,
totalmoney out tra.MONEY%type)
is
cursor cv_accot is
select * from tra where FROMACCOUNT = p_fromaccount
and TRADETIME between begintime and endtime ;
v_counter int default 0;
time_error exception;
no_found_from exception;
begin
if months_between(begintime ,endtime)>0 then
raise time_error;
end if;
select count(*) into v_counter from tra where FROMACCOUNT = p_fromaccount;
if v_counter = 0 then
raise no_found_from;
end if ;
totalmoney:=0;
dbms_output.put_line(p_fromaccount||' 在 '||to_char(begintime,
'yyyy"年"mm"月"dd"日"')||' ~ '||to_char(endtime,'yyyy"年"mm"月"dd"日"')||' 時(shí)間段內(nèi)的交易清單：');
dbms_output.new_line;
for tra_accot in cv_accot loop
totalmoney:=totalmoney+tra_accot.MONEY;
dbms_output.put_line(tra_accot.tradeno||' '||tra_accot.fromaccount||
' '||tra_accot.toaccourt||' '||to_char(tra_accot.money,'L999,999,999.00')||' '||to_char(tra_accot.tradetime,'yyyy"年"mm"月"dd"日"'));
end loop;
dbms_output.new_line;
exception
when time_error then
raise_application_error('-20040','起始時(shí)間不能晚于結(jié)束時(shí)間送巡！');
when no_found_from then
raise_application_error('-20018','沒有發(fā)現(xiàn)該賬戶的交易記錄！');
when others then
raise_application_error('-20019','查詢異常盒卸！');
end pro_search_trace ;
--3.2 測試(----------------------------------------------------交易記錄查詢測試------------------------------------------------------)
declare
p_fromaccount tra.FROMACCOUNT%type;
begintime date;
endtime date;
v_totalmony tra.MONEY%type ;
begin
p_fromaccount:='&請輸入查詢賬戶';
begintime:=to_date('&請輸入開始時(shí)間','yyyy/mm/dd');
endtime:=to_date('&請輸入結(jié)束時(shí)間','yyyy/mm/dd');
pro_search_trace(p_fromaccount,begintime ,endtime,v_totalmony );
dbms_output.put_line('合計(jì)：');
dbms_output.put_line(p_fromaccount||' 在 '||to_char(begintime,
'yyyy"年"mm"月"dd"日"')||' ~ '||to_char(endtime,'yyyy"年"mm"月"dd"日"')||' 時(shí)間段內(nèi)的交易額為: '||to_char(v_totalmony,'L999,999.00'));
end ;
select * from tra;
select * from acc;
/*-----------------------------------------------------------------函數(shù) -----------------------------------------------*/
--65. 基于賬戶表骗爆，編寫一個(gè)函數(shù)通過賬號獲得賬戶余額；如果輸入的賬號不存在則顯示相應(yīng)的異常信息蔽介。
create or replace function get_balance (accno acc.ACCOUNTNO%type)
return number
is
v_balance acc.MONEY%type;
begin
select money into v_balance from acc where ACCOUNTNO = accno;
return v_balance ;
end;
---
select get_balance('6722673861353700') from dual;
--66. 檢測一個(gè)值是否落在了正常的員工工資范圍內(nèi)
create or replace function check_sal (sal salgrade.losal%type)
return varchar2
is
v_losal salgrade.losal%type;
v_hisal salgrade.losal%type;
begin
select min(losal)into v_losal from salgrade;
select max(hisal) into v_hisal from salgrade;
if sal>v_losal and sal < v_hisal then
return '在正常工資范圍摘投！';
else
return '不在正常工資范圍！';
end if ;
end;
select check_sal(690) from dual;
select * from salgrade;
--67. 編寫一個(gè)函數(shù)獲得指定部門的平均工資虹蓄。
create or replace function get_avg_sal(p_dname newdept.dname%type)
return number
is
v_avg new_scott.sal%type;
v_counter int default 0;
no_dept exception;
begin
select count(dname) into v_counter from newdept where p_dname=dname;
if(v_counter=0) then
raise no_dept;
end if;
select avg(sal) into v_avg from new_scott where deptno =
(select deptno from newdept where dname = p_dname);
return v_avg;
exception
when no_dept then
raise_application_error('-20048','不存在該部門');
end;
--SQL/PL語句
declare
v_dname newdept.dname%type;
v_avg_sal new_scott.sal%type;
begin
v_dname:='&請輸入部門名稱';
v_avg_sal:= get_avg_sal(v_dname) ;
dbms_output.put_line(v_avg_sal);
end;
select * from newdept;
--68. 綜合應(yīng)用谷朝，利用第67題的函數(shù)，編寫一個(gè)過程將所有部門的平均顯示出來武花。
create or replace procedure search_avg
is
cursor cv_dept is select dname from newdept;
v_avg new_scott.sal%type default 0;
begin
for rec_dname in cv_dept loop
v_avg := get_avg_sal(rec_dname.dname);
dbms_output.put_line(rec_dname.dname||'部門的平均工資是：'||v_avg);
end loop;
end;
--
begin
search_avg;
end;
-------------------------------------------------觸發(fā)器----------------------------------trigger
/*--------------------------------------------------(了解) 觸發(fā)器 --------------------------------------------------------
1. 什么是觸發(fā)器：觸發(fā)器在數(shù)據(jù)庫里以獨(dú)立的對象存儲(chǔ)圆凰，它與存儲(chǔ)過程和函數(shù)不同的是，存儲(chǔ)過程與函數(shù)需要用戶顯示調(diào)用才執(zhí)行体箕，
而觸發(fā)器是由一個(gè)事件來啟動(dòng)運(yùn)行专钉。即觸發(fā)器是當(dāng)某個(gè)事件發(fā)生時(shí)自動(dòng)地隱式運(yùn)行。并且累铅，觸發(fā)器不能接收參數(shù)跃须。
所以運(yùn)行觸發(fā)器就叫觸發(fā)或點(diǎn)火。
2. ORACLE事件指的是對數(shù)據(jù)庫的表進(jìn)行的INSERT娃兽、UPDATE及DELETE操作或?qū)σ晥D進(jìn)行類似的操作菇民。
3. 觸發(fā)器不接受參數(shù)。
觸發(fā)器組成:
1)觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件。 例如：DML語句(INSERT, UPDATE, DELETE語句對表或視圖執(zhí)行數(shù)據(jù)處理操作)第练、
DDL語句（如CREATE阔馋、ALTER、DROP語句在數(shù)據(jù)庫中創(chuàng)建娇掏、修改呕寝、刪除模式對象）、數(shù)據(jù)庫系統(tǒng)事件（如系統(tǒng)啟動(dòng)或退出婴梧、異常錯(cuò)誤）下梢、
用戶事件（如登錄或退出數(shù)據(jù)庫）。
2)觸發(fā)時(shí)間：即該TRIGGER 是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸發(fā)塞蹭，也就是觸發(fā)事件和該TRIGGER 的操作順序孽江。
3)觸發(fā)操作：即該TRIGGER 被觸發(fā)之后的目的和意圖，正是觸發(fā)器本身要做的事情番电。 例如：PL/SQL 塊竟坛。
4)觸發(fā)對象：包括表、視圖钧舌、模式、數(shù)據(jù)庫涎跨。只有在這些對象上發(fā)生了符合觸發(fā)條件的觸發(fā)事件洼冻，才會(huì)執(zhí)行觸發(fā)操作。
5)觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式隅很。只有當(dāng)該表達(dá)式的值為TRUE時(shí)撞牢，遇到觸發(fā)事件才會(huì)自動(dòng)執(zhí)行觸發(fā)器，
使其執(zhí)行觸發(fā)操作叔营。
6)觸發(fā)頻率：說明觸發(fā)器內(nèi)定義的動(dòng)作被執(zhí)行的次數(shù)屋彪。即語句級(STATEMENT)觸發(fā)器和行級(ROW)觸發(fā)器。
a)語句級(STATEMENT)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)绒尊，該觸發(fā)器只執(zhí)行一次畜挥；
b)行級(ROW)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)，對受到該操作影響的每一行數(shù)據(jù)婴谱，觸發(fā)器都單獨(dú)執(zhí)行一次蟹但。
REFERENCING 參照名稱：OLD,NEW都是代表當(dāng)前操作的記錄行
1) 默認(rèn)的相關(guān)名稱分別為OLD和NEW，參照當(dāng)前DML操作的新谭羔、舊列值
2) 觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱時(shí)华糖，必須在它們之前加冒號(:)，但在WHEN子句(觸發(fā)條件)中則不能加冒號瘟裸。
語法：
create or replace trigger 觸發(fā)器名
觸發(fā)時(shí)間(BEFORE|AFTER) 觸發(fā)事件(INSERT OR UPDATE OR DELETE) [of 列名] on 觸發(fā)對象(表等)
觸發(fā)頻率
when (觸發(fā)條件)
declare
--聲明變量等
begin
--執(zhí)行部分
end 觸發(fā)器名;
*/
--當(dāng)更新或插入員工的獎(jiǎng)金時(shí)客叉，其獎(jiǎng)金comm大于1000時(shí)，sal工資自動(dòng)多加10元
create or replace trigger auto_raise_sal_trg
before insert or update of comm on employee
for each row --表示行級觸發(fā)器
when (NEW.comm>=1000)
declare
begin
--觸發(fā)器里預(yù)定義了三個(gè)操作條件
-- inserting,updating,deleting觸發(fā)動(dòng)作條件，其值為boolean兼搏，只能在觸發(fā)器主體中使用
if inserting then
--觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱時(shí)卵慰，必須在它們之前加冒號(:)，但在WHEN子句(觸發(fā)條件)中則不能加冒號向族。
:NEW.sal := :NEW.sal + 10;
end if;
if updating then
:NEW.sal := :OLD.sal + 10;
end if;
end auto_raise_sal_trg;
--建表 test_trg
drop table test_trg;
create table test_trg(
tid varchar2(4)
)
create sequence test_trg_seq
start with 1;
--使用觸發(fā)器為表在插入數(shù)據(jù)時(shí)生成一個(gè)id值：A序號(序號用序列生成)
create table test_id(
tid varchar2(4)
)
create sequence test_id_sq
start with 1;
create or replace trigger auto_id
before insert on test_id
for each row
when (new.tid is null)
declare
v_id test_id.tid%type;
begin
select 'A'||test_id_sq.nextval into v_id from dual;
:new.tid := v_id;
end;
--
insert into test_id values('159');
select * from test_id;
--刪除觸發(fā)器
drop trigger auto_raise_sal_trg;
--禁用或啟用觸發(fā)器
alter trigger auto_raise_sal_trg disable; --enable
insert into employee(empno,ename,job,sal,comm,deptno)
values(employee_seq.nextval,'小美','CLERK',4000.0,1000,10);
update employee set comm=1500 where ENAME='小美';
------------------------------------------------
select * from employee WHERE ENAME='小美' order by empno DESC;
select * from department;
select * from salgrade;
