Oracle经典案例

Oracle经典案例
------------OraclePL/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) thenmoney :=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_sidstart
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=12*1=2 2*2=43*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 countrywhen '中国' 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>=60group 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.stuidgroup 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;beginfor v in mycursorloopdbms_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 select 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 
       dateasrd 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 packistype mytype 
is
        ref cursor;
        procedure getpagerow(curpage number,pagerows number,mycursor out mytype);
end ;
----包主体
create or replace package body packisprocedure 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_empas 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 OpenBillas 
is
       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 pagePackastype pageCursorType 
is
       ref cursor;--游标类型
       procedure procPage(tableName varchar2,--查询的表名
                 showField varchar2,--查询的字段，T.*表示所有
                 whereText varchar2,--查询的条件，不要where
                 orderText varchar2,--排序的字段，不要order 
                 bypageIndex 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 
                          bypageIndex 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 thenoText:=' 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 viewEmpasselect * 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;