oracle課堂筆記包含ddl，dml，tcl，遊标，過程，函數，觸發器等詳細demo。志在幫助小白變大神
--建立
/*
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. 建立一個學生表包含：學号、姓名、性别、年齡、出生日期等 字段。
create table student(
cno number(5) not null,
cname varchar(10),
sex char(2),
age number(2),
birthday date default sysdate
)
/
--2、修改學生表額外添加成績字段。
alter table Student
add (grade number(3,1));
--2-1、給學号加上主鍵限制。
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、向表裡插入記錄來驗證限制是否生效。
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;--復原
--修改目前回話的日期格式
--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 ：表即可復原的點
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||'入職時間'||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）,
--和既不是經理又不是辦事員但其薪金大于或等于2000的所有員工的詳細資料
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. 找出各月倒數第三天受雇的所有員工
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. 顯示所有員工姓名的前三個字元。
select SUBSTR(ename,1,3) from new_scott;
--22. 顯示所有員工的姓名，用 A 替換 a
select TRANSLATE(ename,'A','a')from new_scott;
--23. 顯示 滿25年 服務年限的員工的姓名 和受雇日期
select ename,hiredate from new_scott
where extract(year from systimestamp)- extract(year from (hiredate))>25;
--MONTHS_BETWEEN(sysdate,hiredate)/12>25;
--24. 顯示員工的詳細資料，按姓名排序,姓名相同按工資降序排序。
select * from new_scott
order by ename ,sal desc;
--25. 顯示與員工的姓名和受雇日期，根據其服務年限，将最老的員工排在最前面。
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，……，預設值）
select ename,decode(job,'CLERK','辦事員','MANAGER','經理',
'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)
;
--經典方法
select ename,job,TO_CHAR(hiredate,'yyyy')year,to_char(hiredate,'mm')month,form employee order by month,year;
/*-------------------------------------------------------- 分組查詢 -------------------------------------*/
--28. 顯示非銷售人員工作名稱以及從事同一工作雇員的月工資的總和，并且要滿足從事同一工作的雇員的月工資合計大于$5000，
--輸出結果按月工資排序。
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. 按男生和女生統計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);
-- 統計成績
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 同，前為空，則一直向後搜尋，最後預設為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、要将子查詢發在圓括号内。
2、子查詢可出現在WHERE子句、FROM子句、SELECT清單(此處隻能是一個單行子查詢)、HAVING子句中。
3、子查詢不能出現在主查詢的GROUP BY語句中。
4、子查詢和主查詢使用表可以不同，是要子查詢傳回的結果能夠被主查詢使用即可。
5、一般不會在子查詢中使用ORDER BY語句，但在TOP-N(隻需前幾條記錄)分析中必須使用ORDER BY語句。
6、單行子查詢隻能使用單行操作符，多行子查詢隻能使用多行操作符。
7、采用合理的縮進和換行來提過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. 查詢不是經理的員工的資訊(使用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号部門員工中任意一個低的員工資訊(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. 查詢出部門名稱，部門員工數，部門平均工資，部門最低工資雇員的姓名，及工資等級
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的各種工作及此從事此工作的全部雇員人數
/*
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(*)人數
from
emp e
group by e.job having (select min(sal) from emp where e.em)
--37. 求出在'salesman'部門工作的員工姓名，假設不知道銷售部的部門編号
select distinct e.ename from new_scott e,newdept n
where e.deptno = (select distinct deptno from new_scott where job = UPPER('salesman'));
--38. 列出薪金高于公司平均薪金的所有員工，所在部門，上級上司，公司的工資等級
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表中所有的經理的資訊(此操作子查詢會傳回多行記錄)
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.列出每個部門工作的員工數量，平均工資和平均服務年限
select deptno ,count(*) 員工數量 ,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)
--建立同義詞：
-- 1)建立私有同義詞 注意：需要sys授權：grant create SYNONYM to stu;
create or replace public synonym emp for new_scott ;
-- 2）建立共有同義詞 注意：需要sys授權：grant create public SYNONYM to stu;
create public synonym dept1 for newdept;
--drop public synonym dept;
--建立序列：
create sequence emp_seq
start with 8002
increment by 1
minvalue 8002
maxvalue 999999999999
cycle
cache 20
--select emp_seq.nextval from emp_seq;--執行emp_seq.currval 之前一定要執行一次 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)
--建立視圖
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');
--建立索引
create index emp_job on emp(job);
--驗證索引
select * from emp where job = upper('salesman');
--第一個小例子
BEGIN
dbms_output.put_line('最簡單的PL/SQL');
END;
declare
v_money number(4,1) := 2.3;
begin
dbms_output.put_line(v_money);
end;
--第二個小例子
DECLARE
v_ename VARCHAR2(20); --運作時在輸入
v_ename2 v_ename%type; --引用另一個變量的類型
v_empno NUMBER(4) not null := 7999; --初始化
v_empno2 NUMBER(4); --運作時在輸入
v_job emp.job%type; --變量與job列的資料類型和寬度一緻
v_hiredate emp.hiredate%type default sysdate; --賦預設值
v_sal CONSTANT emp.sal%type := 3000; --常量
BEGIN
--指派方式 1：
v_ename := '&請輸入員工姓名：'; --&号表示一個替代變量，可在運作時輸入值，字元串需用單引号括起來
v_empno2 := &請輸入員工編号;
--指派方式 2： select 列名清單 into 變量清單（對号入座）
--但是要注意現在給變量指派不能傳回多行，要處理多行的查詢結果需要用到遊标
SELECT job INTO v_job FROM emp WHERE empno=v_empno2; --8001
dbms_output.put_line('員工編号:'||v_empno2||', 員工姓名：'||v_ename||', 職務：'||v_job||', 入職時間：'||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('薪水已經很高了！');
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);
--處理沒有找到資料的異常
exception
when NO_DATA_FOUND THEN
dbms_output.put_line('查無此人！');
when others then
dbms_output.put_line('錯誤代碼：'||SQLCODE ||',錯誤資訊：'||SQLERRM);
end;
--end emp_add_sal;
/
--drop procedure emp_add_sal;
--CASE_WHEN
/* ---------------------------------------第五章 PL/SQL 作業-----------------------------------*/
--A. 使用LOOP循環求1-100之間的素數
/*
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('優秀');
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' -- 此處一定注意：如果是字元型一定要加上 '' 否者報錯，，，如果是數值型則不需要加 ’‘
when 'A' then
dbms_output.put_line('優秀');
when 'B' then
dbms_output.put_line('良好');
when 'C' then
dbms_output.put_line('一般');
else
dbms_output.put_line('不及格');
end case;
end;
--循環
declare
v_row int:= &行數;
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循環求1-100之間的素數
declare
v_prime int :=2;
v_counter int default 1;
counter int default 0;
num int default 0;--統計個數
begin
loop
counter:=0;--計數器（每次重新開始）
v_counter:=2;--除數 從 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; --内循環結束條件 （2個條件滿足一個即可跳出循環）
v_counter:=v_counter+1;--除數自加
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;--外循環結束條件
v_prime:= v_prime+1;--外循環自加
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循環求1-100之間的素數
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;
/*
練習：
根據部門名稱，按以下格式列印"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 --統計有多少個符合條件的
where e.deptno = n.deptno
and n.dname = 'RESEARCH';
for i in 1..counter loop --由于每次隻能列印一個，是以用for循環
select ename into v_ename from
(
select rownum r,ename --用僞列rownum 為每一個符合的條件做個編号，以便輸出
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. 根據工資查詢員工姓名。如果此員工不存在（發出NO_DATA_FOUND異常），則列印相應的提示資訊。
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; -- 手動抛出異常
end if;
select count(*) into v_count from emp where empno = v_empno;
if(v_count = 0) then
raise no_found ; --手動抛出異常
end if;
exception --捕獲異常
when no_found then
dbms_output.put_line('沒有符合的資訊！');
when invlite_sal then
dbms_output.put_line('工資不可為負！');
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;
-------------------------------------------第六章 遊标---------------------------------------------------
----遊标(loop通路)
declare
cursor cv_emp is --1.定義遊标 cursor 遊标名 is （select 語句）
select e.* ,rownum from emp e;
rec_emp cv_emp%rowtype; --定義一個遊标類型的變量，儲存每次結果
begin
open cv_emp; --2.打開遊标 open 遊标名；
loop
fetch cv_emp into rec_emp; -- 3.從遊标中擷取資料 ： fetch 有标明 into 變量
dbms_output.put_line(rec_emp.empno||' '||rec_emp.rownum);
exit when cv_emp%NOTFOUND;
end loop;
close cv_emp; --4.關閉遊标 close 遊标名
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; --因為遊标是從0開始，第零個沒有資料，是以要跳過，
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 不需要特意定義 遊标變量，因為 ORACLE 自動配置設定計數器變量
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;
/* ---------------------------------------------第六章 遊标管理 作業----------------------------------*/
/*
（一）什麼是遊标：
（二）隐式遊标：在 PL/SQL 程式中執行DML SQL 語句時自動建立隐式遊标。 并且隻能通路最近執行的一條DML語句，查詢隻能傳回一行。
（三）顯式遊标用于處理傳回多行的查詢。
1）無參數的顯式遊标
2）帶參數的顯式遊标
文法：
cursor 遊标名(參數名 類型) is select_statement;
（四）隐式遊标的特性：
1）在PL/SQL中使用DML語句時自動建立隐式遊标
2）隐式遊标自動聲明、自動打開和自動關閉，其名為: SQL
3）通過檢查隐式遊标的屬性可以獲得最近一次執行的DML 語句的資訊
4）遊标的屬性有：
（1）%FOUND – SQL語句查詢或影響了一行或多行時為 TRUE
（2）%NOTFOUND – SQL語句沒有影響任何行時為 TRUE
（3）%ROWCOUNT – SQL語句影響的行數
（4）%ISOPEN - 檢查遊标是否打開，隐式遊标始終為FALSE
（五）使用遊标更新行
1) 查詢時使用 select .. from table where 條件 for update [of column [nowait]]子句鎖定需要更新的行或列。
2) update employee set sal=sal-2 where current of 遊标;
*/
--54. 顯示EMP中的第四條記錄。 如：遊标%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; --先移動，再讀取
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
兩種實作提示：
1）循環每個部門，用其部門号作條件去查員工表
2）用顯示cursor完成
3）要求用FOR，會用到嵌套循環。
*/
--方法一：不帶參數遊标
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;
--方式二：帶參數遊标
declare
cursor cv_dept is
select * from newdept ;
cursor cv_employee (cp_deptno emp.deptno%type)--傳遞一個員工部門參數，根據參數鎖定該部門資訊
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 --内循環中的參數是外循環給的，通過每一個部門參數，列印結果
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％;其他情況不作處理
*/
--方式二： 遊标
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:='&地點：';
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;
--驗證過程
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.(使用遊标更新行) 編寫一PL/SQL，對所有的"銷售員"(SALESMAN)增加傭金500.
-- 1) 查詢時使用 select .. from table where 條件 for update [of column [nowait]]子句鎖定需要更新的行或列。
-- 2) update employee set sal=sal-2 where current of 遊标;
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 遊标;
end loop;
end;
--58. 編寫一PL/SQL，以提升兩個資格最老的"CLERK(職員)"為"HIGHTCLERK(進階職員)"。（工作時間越長，優先級越高）
--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;
--方式二：遊标方式
declare
--遊标標明job為CLERK 的員工，并按受雇時間
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 --限制隻修改兩個
update new_scott set job = 'HIGHTCLERK' where current of cv_emp;
end if;
end loop;
end;
select * from new_scott;
/*
59. 對直接上級是'BLAKE'的所有員工，按照參加工作的時間加薪：
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;
--方式二：遊标方法
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章 過程和函數----------------------------------------------*/
/*
60. 編寫一個給指定雇員加薪10%的過程：Raise_Sal(p_ename)，這之後，檢查如果已經雇傭該雇員超過60個月，則給他額外加薪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('操作有誤，已經撤銷！');
end;
--調用
declare
v_ename emp.ename%type;
begin
v_ename:='&需要加薪的員工的名字：';
Raise_Sal(v_ename);
end;
--驗證
select * from emp;
/*
61. 編寫一個過程列印出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;
--調用過程
begin
pro_9_9;
end;
/*
62.編寫一個過程完成兩個數字的交換
*/
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;
--調用
declare
v_on1 int :=&請輸入第一個數字;
v_on2 int :=&請輸入第二個數字;
begin
date_change(v_on1,v_on2);
dbms_output.put_line('交換後：第一個數字：'||v_on1||' 第二個參數：'||v_on2);
end;
/*
--drop table log_msg cascade constraint;//删除表同時删除限制
63. 編寫三個過程完成
(1)開戶功能。open_account(.....);
(2)兩個賬戶的轉賬功能。transaction(from帳戶，to帳戶,money)
a)檢查帳戶是否存大？不存在的情況出異常。
b)轉入轉出帳戶必須要在一個事務裡進行。
(3)根據轉出賬号和交易時間段 可查詢轉出賬号的所有交易記錄。最後還需要傳回該時間段轉出的總金額。
a)向“交易日志表”插入資料。
b)交易号的生成規則：'T10000-'||trans_seq.nextval --> T10000-1000
表結構如下：
1. 賬戶表：賬号（6722 6738 xxxx yyyy）,戶名，身份證号，餘額，開戶時間。
說明：賬号後8位數随機生成。dbms_random.value函數可以得一個八位小數的随機小數。
select dbms_random.value from dual;
select trunc(dbms_random.value*100000000,0) from dual;
2. 交易日志表：交易号(T10000-999)，轉出賬戶，轉入賬戶，交易金額，交易時間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; --該身份證已經開過戶
name_has_exist exception; --該姓名已經注冊
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;--産生一個8位的随機數
v_account:=CONCAT('67226738',to_char(v_rand)); --根據字首産生一個賬号
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);--檢驗輸出
commit;
exception
when id_has_exist then
raise_application_error('-20012','該身份證已經開戶,請換其他身份證繼續！！');
when name_has_exist then
raise_application_error('-20002','該客戶已經存在，不能重名！！');
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 轉賬功能工程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('轉賬成功！');
commit;
exception
when no_found_from then
raise_application_error('-20013','轉出賬戶不存在！');
when no_found_to then
raise_application_error('-20014','轉入賬戶不存在！');
when no_enough then
raise_application_error('-20015','轉出賬戶餘額不足！');
when data_error then
raise_application_error('-20016','轉賬數額不能為負值！');
when no_equals then
raise_application_error('-20030','轉出賬戶與轉入賬戶相同！');
when others then
raise_application_error('-20017','操作失敗,動作已撤銷！');
rollback;
end pro_tract;
select * from acc;
select * from tra;
--2.2 測試(-------------------------------------------------轉賬過程測試----------------------------------)
declare
v_fromaccount tra.FROMACCOUNT%type;
v_toaccount tra.TOACCOURT%type;
v_money tra.MONEY%type;
begin
v_fromaccount :='&轉賬賬号';
v_toaccount :='&收賬賬号';
v_money:=&轉賬金額;
pro_tract(v_fromaccount,v_toaccount,v_money);
commit;
/* exception -- 加了這個異常後，raise_application_error 不在顯示
when others then
dbms_output.put_line('轉賬失敗！');*/
end;
--2.3建立序列
create sequence sq_tra_log
start with 999;
--3.1
/* (3)根據轉出賬号和交易時間段 可查詢轉出賬号的所有交易記錄。最後還需要傳回該時間段轉出的總金額。
a)向“交易日志表”插入資料。
b)交易号的生成規則：'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"日"')||' 時間段内的交易清單：');
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','起始時間不能晚于結束時間！');
when no_found_from then
raise_application_error('-20018','沒有發現該賬戶的交易記錄！');
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('&請輸入開始時間','yyyy/mm/dd');
endtime:=to_date('&請輸入結束時間','yyyy/mm/dd');
pro_search_trace(p_fromaccount,begintime ,endtime,v_totalmony );
dbms_output.put_line('合計：');
dbms_output.put_line(p_fromaccount||' 在 '||to_char(begintime,
'yyyy"年"mm"月"dd"日"')||' ~ '||to_char(endtime,'yyyy"年"mm"月"dd"日"')||' 時間段内的交易額為: '||to_char(v_totalmony,'L999,999.00'));
end ;
select * from tra;
select * from acc;
/*-----------------------------------------------------------------函數 -----------------------------------------------*/
--65. 基于賬戶表，編寫一個函數通過賬号獲得賬戶餘額；如果輸入的賬号不存在則顯示相應的異常資訊。
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. 檢測一個值是否落在了正常的員工工資範圍内
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. 編寫一個函數獲得指定部門的平均工資。
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. 綜合應用，利用第67題的函數，編寫一個過程将所有部門的平均顯示出來。
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;
-------------------------------------------------觸發器----------------------------------trigger
/*--------------------------------------------------(了解) 觸發器 --------------------------------------------------------
1. 什麼是觸發器：觸發器在資料庫裡以獨立的對象存儲，它與存儲過程和函數不同的是，存儲過程與函數需要使用者顯示調用才執行，
而觸發器是由一個事件來啟動運作。即觸發器是當某個事件發生時自動地隐式運作。并且，觸發器不能接收參數。
是以運作觸發器就叫觸發或點火。
2. ORACLE事件指的是對資料庫的表進行的INSERT、UPDATE及DELETE操作或對視圖進行類似的操作。
3. 觸發器不接受參數。
觸發器組成:
1)觸發事件：引起觸發器被觸發的事件。 例如：DML語句(INSERT, UPDATE, DELETE語句對表或視圖執行資料處理操作)、
DDL語句（如CREATE、ALTER、DROP語句在資料庫中建立、修改、删除模式對象）、資料庫系統事件（如系統啟動或退出、異常錯誤）、
使用者事件（如登入或退出資料庫）。
2)觸發時間：即該TRIGGER 是在觸發事件發生之前（BEFORE）還是之後(AFTER)觸發，也就是觸發事件和該TRIGGER 的操作順序。
3)觸發操作：即該TRIGGER 被觸發之後的目的和意圖，正是觸發器本身要做的事情。 例如：PL/SQL 塊。
4)觸發對象：包括表、視圖、模式、資料庫。隻有在這些對象上發生了符合觸發條件的觸發事件，才會執行觸發操作。
5)觸發條件：由WHEN子句指定一個邏輯表達式。隻有當該表達式的值為TRUE時，遇到觸發事件才會自動執行觸發器，
使其執行觸發操作。
6)觸發頻率：說明觸發器内定義的動作被執行的次數。即語句級(STATEMENT)觸發器和行級(ROW)觸發器。
a)語句級(STATEMENT)觸發器：是指當某觸發事件發生時，該觸發器隻執行一次；
b)行級(ROW)觸發器：是指當某觸發事件發生時，對受到該操作影響的每一行資料，觸發器都單獨執行一次。
REFERENCING 參照名稱：OLD,NEW都是代表目前操作的記錄行
1) 預設的相關名稱分别為OLD和NEW，參照目前DML操作的新、舊列值
2) 觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒号(:)，但在WHEN子句(觸發條件)中則不能加冒号。
文法：
create or replace trigger 觸發器名
觸發時間(BEFORE|AFTER) 觸發事件(INSERT OR UPDATE OR DELETE) [of 列名] on 觸發對象(表等)
觸發頻率
when (觸發條件)
declare
--聲明變量等
begin
--執行部分
end 觸發器名;
*/
--當更新或插入員工的獎金時，其獎金comm大于1000時，sal工資自動多加10元
create or replace trigger auto_raise_sal_trg
before insert or update of comm on employee
for each row --表示行級觸發器
when (NEW.comm>=1000)
declare
begin
--觸發器裡預定義了三個操作條件
-- inserting,updating,deleting觸發動作條件，其值為boolean，隻能在觸發器主體中使用
if inserting then
--觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒号(:)，但在WHEN子句(觸發條件)中則不能加冒号。
: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;
--使用觸發器為表在插入資料時生成一個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;
--删除觸發器
drop trigger auto_raise_sal_trg;
--禁用或啟用觸發器
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;
![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)