Oracle经典案例

------------Oracle PL/SQL 复习--------

select * from emp;

declare
e_no number:='&学生编号';
d_no emp.deptno%type;
money number;
begin
select deptno into d_no from emp where empno = e_no;
if(d_no=10) then
money := 2000;
elsif(d_no=20) then
money := 1700;
elsif(d_no=30) then
money := 1500;
end if;
update emp set sal = sal+money where empno = e_no;
end;


---使用游标给每个员工加薪水

declare
v_emp emp%rowtype;
money number;
cursor mycursor is
select * from emp;
begin
open mycursor;
loop
fetch mycursor into v_emp;
if(v_emp.deptno = 10) then
money :=2000;
elsif(v_emp.deptno = 20) then
money :=1700;
elsif(v_emp.deptno = 30) then
money :=1500;
end if;
update emp set sal = sal+money where empno = v_emp.empno;
exit when mycursor%notfound;
end loop;
close mycursor;
end;


----序列的创建及使用
create table course_details
(
sid number,
course varchar2(20)
)
select * from course_details;

create sequence sq_sid
start with 1
increment by 1
nomaxvalue ;

drop sequence sq_sid;

declare num number:=0;
begin
loop
insert into course_details values(sq_sid.nextval,'oracle');
num:=num+1;
exit when num=5;
end loop;
end;


-----for循环的使用--
--显示1至100之间的素数（质数）。
declare
i number(3):=2;
j number:=0;
begin
loop
j:=0;
for num in 2..i-1
loop
if(i mod num = 0)then
j:=1;
end if;
end loop;
if(j=0) then
dbms_output.put_line(i);
end if;
i:=i+1;
exit when i=100;
end loop;
end;


---统计70后，80后，90后用户的人数和占三者人数和的比

declare
num70 number;
num80 number;
num90 number;
numtatol number:=0;
begin
select count(*) into num70 from userinfo where substr(to_char(birthday,'yyyy'),3,1) = '7';
select count(*) into num80 from userinfo where substr(to_char(birthday,'yyyy'),3,1) = '8';
select count(*) into num90 from userinfo where substr(to_char(birthday,'yyyy'),3,1) = '9';
numtatol:=num70+num80+num90;

dbms_Output.put_line('70后占'||round(num70*100/numtatol,2)||'%');
dbms_Output.put_line('80后占'||round(num80*100/numtatol,2)||'%');
dbms_Output.put_line('90后占'||round(num90*100/numtatol,2)||'%');
end;



---使用预定义异常---????

declare
e_name varchar2(4);
Value_Error exception;
begin
select ename into

e_name from emp where empno = '&员工编号：';
raise Value_Error ;
exception
when others then
raise_application_error(-20000,'员工姓名过长！');
end;



--1、写一个pl/sql块求1..1000之间的素数

declare
flag boolean:=true;
begin
for i in 2..1000
loop
flag:=true;
for j in 2..i-1
loop
if(i mod j = 0) then
flag:=false;
end if ;
end loop;
if(flag) then
dbms_output.put_line(i);
end if;
end loop;
end;

--2、写一个pl/sql块将一个数字反转。（12345>>54321）

declare
i number:=0;
str varchar2(100):='&字符串';
s varchar2(100) ;
begin
loop
s:=s||substr(str,length(str)-i,1);
i:=i+1;
exit when i = length(str);
end loop;
dbms_output.put_line('反转前：'||str);
dbms_output.put_line('反转后：'||s);
end;


--3、写一个pl/sql块将一个数字中的数求和.（12345=15）

declare
num number;
i number:=0;
s number:=0;
begin
loop
num :='&num';
s:=s+substr(num,i+1,1);
i:=i+1;
exit when i=length(num);
end loop;
dbms_output.put_line('输入的数字是:'||num);
dbms_output.put_line('拆分后求和的结果是:'||s);
exception
when others then
dbms_output.put_line('请输入数字。。。');
end;


/*4、写一个pl/sql块实现九九乘法表
1*1=1
2*1=2 2*2=4
3*1=3 3*2=6 3*3=9
……*/

begin
for i in 1..9
loop
for j in 1..i
loop
dbms_output.put(i||'*'||j||'='||i*j||' ');
end loop;
dbms_output.put_line(' ');
end loop;
end;

5、将一个正整数转化为二进制（如10的结果1010）

declare
num number:='&num';
str varchar2(10000);
begin
loop
if(num mod 2=0) then
num :=num/2;
str :=str||0;
else
num :=num/2;
str :=str||1;
end if;
exit when (num/2=0);---??
end loop;
dbms_output.put_line(str);
end;



-----------case when 高级应用--------行转列

select * from demo;

select country 国家,
sum(case sex when 1 then pop else 0 end) 男,
sum(case sex when 2 then pop else 0 end) 女
from demo group by country;



select 洲,sum(人数) from
(
select case country
when '中国' then '亚洲'
when '美国' then '美洲'
when '加拿大' then '美洲'
when '英国' then '欧洲'
end 洲,pop 人数 from demo
)group by 洲;



select * from studentexam;
--统计考试(不及格)的人，显示姓名，分数；

select estuid ,
sum(case examsubject when 'SQL' then examresult end) "SQL",
sum(case examsubject when 'Java' then examresult end) "Java"
from studentexam group by estuid;

select stuname ,
sum(case examsubject when

'SQL' then examresult end) "SQL",
sum(case examsubject when 'Java' then examresult end) "Java"
from studentexam,studentinfo where studentexam.estuid = studentInfo.stuid
--and examresult>=60
group by stuname --having "SQL"<60 or "Java"<60;

---统计双科都不及格的人数

select count(*) from
(
select stuname ,
sum(case examsubject when 'SQL' then examresult end) "SQL",
sum(case examsubject when 'Java' then examresult end) "Java"
from studentexam,studentinfo where studentexam.estuid = studentInfo.stuid
group by stuname
) where "SQL">60 and "Java">60;




--------------------游标练习题--------------、
--使用隐式游标的属性
-- 根据用户输入的部门编号，更新员工工资。

declare
dno number;
begin
dno:='&dno';
update emp set sal = sal +100 where deptno = dno;
if(SQL%found) then
dbms_output.put_line('更新了'||SQL%rowcount||'数据');
else
dbms_output.put_line('没有更新数据！');
end if;
rollback;
end ;



--显式游标
declare
cursor mycursor is select empno,ename,sal,dept.deptno,dname from emp,dept
where emp.deptno = dept.deptno;
begin
for v in mycursor
loop
dbms_output.put_line(v.empno||'-'||v.ename||'-'||v.sal||'-'||v.deptno||'-'||v.dname);
end loop;
end;




---循环游标
declare
cursor deptcursor is
select deptno,dname from dept ;
cursor empcursor(dno number) is
select ename from emp where deptno = dno;
begin
for dept in deptcursor
loop
dbms_output.put_line('部门编号：'||dept.deptno||', 名称：'||dept.dname);
for emp in empcursor(dept.deptno)
loop
dbms_output.put_line('员工'||empcursor%rowcount||':'||emp.ename);
end loop;
end loop;
end;


----使用游标更新数据
select * from userInfo;

declare
cursor usercursor is
select * from userInfo for update nowait;
begin
for u in usercursor
loop
update userinfo set PASSWORD = round(dbms_random.value(100000,999999),0) where current of usercursor;
end loop;
end;



/* REF游标
以Scott用户登录，编写一段代码，提示用户要查看哪个表的记录，
如果用户输入‘E’则显示emp表中的empno，ename列的值；
如果用户输入‘D’则显示dept表中的deptno，dname列的值。
*/

declare
t varchar2(10):='&表名';
tno number;
tname varchar2(10);
type cursortype is ref cursor;
mycur cursortype;

begin
if upper(t)='E' then
open mycur for select empno,ename from emp;
elsif upper(t) = 'D' then
open mycur for selec

t deptno,dname from dept;
end if;
loop
fetch mycur into tno,tname;
dbms_output.put_line(tno||' --- '||tname);
exit when mycur%notfound;
end loop;
exception
when others then
dbms_output.put_line('没有对应表');
end;


-----------------子程序与程序包的使用------------------
--过程
create or replace
procedure findemp(eno number,e_name out varchar2,e_sal out varchar2)
as
begin
select ename,sal into e_name,e_sal from emp where empno = eno;
end ;

--调用
declare
e_name varchar2(10);
e_sal number;
ename varchar2(10);
esal number;
begin
getnamesalout(7369,e_name,e_sal);
dbms_output.put_line(e_name||'----'||e_sal);

findemp(7499,ename,esal);
dbms_output.put_line(ename||'---'||esal);
end;


--函数
create or replace
function rdate(eno number)
return date
as
rd date;
begin
select hiredate into rd from emp where empno = eno;
return rd;
end;

--调用

declare
rd date;
begin
select rdate(7369) into rd from dual;
dbms_output.put_line(rd);
end ;



-------程序包--


--包规范：
create or replace package mypack
is
procedure addemp(eno number);
function isexists(eno number) return boolean;
end;

--包主体：

create or replace package body mypack
is
procedure addemp(eno number)
as
r emp%rowtype;
begin
if(isexists(eno)) then
select * into r from emp where empno = eno;
dbms_output.put_line(r.empno||'----'||r.ename||'----'||r.sal);
else
dbms_output.put_line('不存在此员工！');
end if;
end;
function isexists(eno number) return boolean
as
num number;
begin
select count(*) into num from emp where empno = eno;
return num>0;
end ;
end;

----测试程序包


--子程序可单独使用---》》测试函数isexists(eno)
declare
num boolean;
begin
num:=mypack.isexists(7369);
if(num) then
dbms_output.put_line('返回值为true，存在此员工');
else
dbms_output.put_line('返回值为false, 不存在此员工');
end if;
end ;

---->>测试过程addemp(eno)

begin
mypack.addemp('&eno');
end;


------程序包，分页存储过程（游标参数）----

create or replace package pack
is
type mytype is ref cursor;
procedure getpagerow(curpage number,pagerows number,mycursor out mytype);
end ;

----包主体

create or replace package body pack
is
procedure getpagerow(curpage number,pagerows number,mycursor out mytype)
as
begin
open mycursor for
'select * from (select rownum rn,emp.* from emp where rownum<=:0)
where rn>:1'----

动态SQL
using curpage*pagerows,(curpage-1)*pagerows;
end;
end ;

----测试
create view view_emp
as
select rownum rn,emp.* from emp;


declare
mycursor pack.mytype;
myemp view_emp%rowtype;
begin
pack.getpagerow(2,5,mycursor);
loop
fetch mycursor into myemp;
exit when mycursor%notfound;
dbms_output.put_line(myemp.ename);
end loop;
close mycursor;
end;



-------------生成账单号的存储过程--------------

--表结构
create table billInfo
(
billID varchar2(14), ---账单编号
billDate date ---开单时间
);

select * from billInfo;

----开单过程
create or replace
procedure OpenBill
as
counter number; --当天的账单编号个数（有没有）
maxid varchar2(14); ---当天最后的账单编号
bid varchar2(14); ---判断后最终得到的账单编号
begin
bid:='ZD'||to_char(sysdate,'yyyymmdd');
select count(*) into counter from billInfo where billID like bid||'%';
--select count(*) into counter from billInfo where billdate = sysdate;
if(counter>0) then --当天有新单--》》求出最后一笔单
select max(billID) into maxid from billInfo;
bid:=bid||Lpad(substr(maxid,11)+1,4,0);
else --当天没有新单
bid:=bid||'0001';
end if;
insert into billInfo values(bid,sysdate);
commit;
end;


--测试开单过程
begin
OpenBill;
end;

select * from billInfo;



/*create or replace procedure OpenBill
as
bid varchar2(14);
maxid varchar2(14);
counter number;
begin
bid:='ZD'||to_char(sysdate,'yyyymmdd');
select count(*) into counter from billInfo where billID like bid||'%';
if(counter>0) then
select max(billID) into maxid from billInfo ;
bid:=bid||Lpad(substr(maxid,11)+1,4,0);
else
bid:=bid||'0001';
end if;
insert into billInfo values(bid,sysdate);
commit;
end;*/







--------分页查询模板------------

select * from
(
select rownum rn, T.* from
(select * from emp where sal>2000 order by sal) T
where rownum<=6
)
where rn>3

--程序包-规范
create or replace package pagePack
as
type pageCursorType is ref cursor;--游标类型
procedure procPage(
tableName varchar2,--查询的表名
showField varchar2,--查询的字段，T.*表示所有
whereText varchar2,--查询的条件，不要where
orderText varchar2,--排序的字段，不要order by
pageIndex integer,--显示的页码
pageSize integer,--每页显示的记录数
pageCursor out pageCursorType,--返回的游标
counter out integer--返回的记录总数
);
end;
--程序

包-主体
create or replace package body pagePack
as
procedure procPage(
tableName varchar2,--查询的表名
showField varchar2,--查询的字段，T.*表示所有
whereText varchar2,--查询的条件，不要where
orderText varchar2,--排序的字段，不要order by
pageIndex integer,--显示的页码
pageSize integer,--每页显示的记录数
pageCursor out pageCursorType,--返回的游标
counter out integer--返回的记录总数
)
as
sqlstr varchar2(400);
wText varchar2(100);
oText varchar2(100);
begin
if whereText is not null then
wText:=' where '||whereText;
end if;
if orderText is not null then
oText:=' order by '||orderText;
end if;
sqlstr:='select * from
(
select rownum rn, '||showField||' from
(select * from '||tableName||wText||oText ||' ) T
where rownum<=:1
)
where rn>:2';
dbms_output.put_line(sqlstr);
open pageCursor for sqlstr using pageSize*pageIndex,pageSize*(pageIndex-1);

sqlstr:='select count(*) from '||tableName||wText||oText;
dbms_output.put_line(sqlstr);
execute immediate sqlstr into counter;
end;
end;

--视图
create view viewEmp
as
select * from
(
select rownum rn,emp.* from emp
)
--测试
declare
pageCursor pagePack.pageCursorType;
users viewEmp%rowtype;
counter integer;
begin
pagePack.procPage('emp','T.*','sal>2000','sal',2,5,pageCursor,counter);
loop
fetch pageCursor into users;
exit when pageCursor%notfound;
dbms_output.put_line(users.ename);
end loop;
close pageCursor;
dbms_output.put_line(counter);
end;