oracle重点

一. 实例

1.使用plsql打印9*9乘法表
 declare
  x number:=1;
  y number:=1;
 begin
   
   for x in 1..9 loop
     for y in 1..9 loop
         if y<x+1 then
           dbms_output.put(x||'*'||y||'='||x*y||' ');
         end if;  
     end loop;
     dbms_output.put_line(' ');
   end loop;
   
 end;
 
 
 2.使用plsq输出 1-1000中所有的质数 （只能被1和自己整除的数）
declare 
 flag boolean;
begin
 for i in 2..1000 loop
    flag:=true;
    for j in 2..trunc(i/2)loop
      if(mod(i,j)=0)then
         flag:=false;
         exit;
      end if;
     end loop;
   if (flag)then
     dbms_output.put_line(i);
   end if;
 end loop;
end;
        
 3.使用数组添加元素后  排序输出
 
declare type myTable is TABLE OF number INDEX BY BINARY_INTEGER;
        arr myTable;
        x number;
begin
   arr(1):=5;
   arr(2):=3;
   arr(3):=7;
   arr(4):=4;
   for i in 1..4 loop
     for j in i+1..4 loop
       if(arr(i)>arr(j))then
          x:=arr(i);
          arr(i):=arr(j);
          arr(j):=x;
        end if;
     end loop;
   end loop;
   for i in 1..4 loop
     dbms_output.put_line(arr(i));
   end loop; 
end;

 题目一  在plsql中创建表cc 插入数据如下（列 c1,c2）： 
c1 c2 
1 西 
1 安 
1 的 
2 天 
2 气 
3 好 
转换为 
1 西安的
2 天气 输出
3 好  输出



create table cc(
       c1 number,
       c2 varchar2(20)
)
insert into cc values(1,'西');
insert into cc values(1,'安');
insert into cc values(1,'的');
insert into cc values(2,'天');
insert into cc values(2,'气');
insert into cc values(3,'好');
commit;
--wm_concat()
       select c1,replace(wm_concat(c2),',','') from cc group by c1;
       
--plsql语句
       declare mystr varchar2(20):='';
       begin
         for i in(select c1 from cc group by c1) loop
           mystr:='';
             for j in(select c2 from cc where c1=i.c1) loop
             mystr:=mystr||j.c2;
             end loop;
         dbms_output.put_line(i.c1||' '||mystr);
         end loop;
       end;
       
------------- -- ------

begin
  execute immediate 'create table cc(
          c1 number(3),
          c2 varchar2(20)
  )';
end;
  insert into cc (c1,c2) values(1,'西');
  insert into cc (c1,c2) values(1,'安');
  insert into cc (c1,c2) values(1,'的');
  insert into cc (c1,c2) values(2,'天');
  insert into cc (c1,c2) values(2,'气');
  insert into cc (c1,c2) values(3,'好');
  commit;


declare s1 varchar2(20);
        s2 varchar2(20);
        s3 varchar2(20);      
begin
  for i in(select * from cc) loop
    if(i.c1=1) then
      s1:=s1||i.c2;
    end if;
    if(i.c1=2) then
      s2:=s2||i.c2;
    end if;
    if(i.c1=3) then
      s3:=s3||i.c2;
    end if;
  end loop;
  dbms_output.put_line(s1);
  dbms_output.put_line(s2);
  dbms_output.put_line(s3);
end;



题目二 
  查找出输入的用户下，每张表的记录数，（提示：使用 tab表）
以scott用户为例，
结果应如下： 
  DEPT...................................4 
  EMP...................................14 
  BONUS.................................0 
  SALGRADE.............................5

  
  select * from tab;--有权限查询的表
  select * from user_tables;
  select * from all_tables where owner='SCOTT';
  --第一种
  declare vsql varchar2(200);
          rtrNumber number;
  begin 
    for i in(select * from user_tables ) loop
      --select count(rowid) from 表名
      vsql:='select count(rowid) from '||i.table_name;
      execute immediate vsql into rtrNumber;
      dbms_output.put_line(i.table_name||'...................'||rtrNumber);
    end loop;
  end;
  
  
--第二种 
declare
  type tab_names is table of varchar2(20) index by binary_integer;
  tab_name tab_names;
  coun number;
  str varchar2(200);
begin
  select table_name bulk collect into tab_name from user_tables;
  for i in tab_name.first..tab_name.last loop
    str:='select count(*) from '||tab_name(i);
    execute immediate str into coun;
    dbms_output.put_line(tab_name(i)||'......'||coun);
  end loop;
end;
 
题目 三
加载在c盘存在一个文件 a.txt
内容如下
  100|金融部|深圳
  110|财务部|东莞
  
使用plsql读取该文件 并将 每行的 数据插入到dept表中 （读取一行 按|切割）
读取文件的代码
declare vInHandle utl_file.file_type ; --定义文件类型
vNewLine VARCHAR2(250);  --定义变量获取当前行数据
BEGIN
vInHandle := utl_file.fopen ('目录', '文件名 ', 'R');
utl_file.get_line (vInHandle, vNewLine); --调用一次 读取一行



declare vInHandle utl_file.file_type ; --定义文件类型
 vNewLine VARCHAR2(250);  --定义变量获取当前行数据
begin
  vInHandle := utl_file.fopen ('MYDIR', 'a.txt ', 'R');
  loop
    utl_file.get_line (vInHandle, vNewLine); --调用一次 读取一行
    --dbms_output.put_line(vNewLine);
    --100|金融部|深圳
    declare a1 varchar2(20);
      a2 varchar2(20);
      a3 varchar2(20);
      beginIdx number:=1;
      begin
        beginIdx:=instr(vNewLine,'|');
        --1号位置开始到找到一个| 的位置结束的字符转
        a1:=substr(vNewLine,1,instr(vNewLine,'|')-1);
         --从第一个|的下一个位置开始
        a2:=substr(vNewLine,beginIdx+1,instr(vnewLine,'|',beginIdx+1)-beginIdx-1);
        beginIdx:=instr(vNewLIne,'|',beginIdx+1);
        a3:=substr(vNewLine,beginIdx+1);
        dbms_output.put_line(a1||' '||a2||' '||a3); 
        insert into dept values(a1,a2,a3);
        
      end;
  end loop;
  exception when others then
    utl_file.fclose(vInHandle);
    commit;
end;

二。--步骤定义 经过5步骤的 显示游标 没有5步骤的 隐示游标

       1.定义获取数据的变量
       2.声明游标，并制定查询
       3.打开游标
       4.抓取数据
       5.关闭游标
begin
  --存放select 语句返回的查询 隐示游标 oravle负责操作5步骤
  for i in(select ename,empno from emp) loop
    dbms_output.put_line(i.ename||' '||i.empno);
  end loop;
end;




--显示游标  分为 :自然游标，普通游标

--1定义获取数据变量
declare rowobj emp%rowtype;
   --2声明游标,并指出查询     
  cursor mycursor is select * from emp;
begin
   --3打开游标
  open mycursor;
   --4抓取数据
  loop
    fetch  mycursor into rowobj;
    exit when mycursor%NOTFOUND;
    dbms_output.put_line(rowobj.ename || rowobj.sal);
   end loop;
   --5关闭游标
   close mycursor;
end;




--(一)动态游标
 enamevar =null;
 enamebar='SMITH';
 if(enamevar is null)
   sql='select * from emp';
  else 
    sql='select * from emp where ename=enamevar';
 
 
--1定义获取数据变量
declare rowobj emp%rowtype;
        enameVar varchar2(20):='SMITH';
        sqlVar varchar2(200):='select * from emp';
    --2声明游标,并指出查询
   mycursor sys_refcursor;
begin
    if(enameVar is not null) then
       sqlVar:=sqlVar ||' where ename=''' ||enameVar|| '''';
    end if;
    
     dbms_output.put_line(sqlVar);
     if(not mycursor%isopen) then
       dbms_output.put_line('未打开游标');
      end if;
      
     --3打开游标
    open mycursor for sqlVar;
     --4抓取数据
    loop
       fetch mycursor into rowobj;
       exit when mycursor%NOTFOUND; --抓取之后，才能判断是否有数据
       dbms_output.put_line(rowobj.ename || rowobj.sal);
     end loop;
      --5关闭游标
     close mycursor;
end;



--异常处理
declare myVar number;
begin
  select sal into myVar from emp where ename='a';
  --myVar:=5/0;
  exception
    when NO_DATA_FOUND then
      dbms_output.put_line('没有找到');
    when ZERO_DIVIDE then
      dbms_output.put_line('除数为0');
    when others then
      dbms_output.put_line('未知异常');
end;


--异常捕获
declare myVar number;
begin
  begin
    select sal into myVar from emp where ename='aa';
    exception when NO_DATA_FOUND then
      dbms_output.put_line('没有数据');
  end;
  myVar:=5/0;
  exception
    when ZERO_DIVIDE then
       dbms_output.put_line('除数为0');
    when others then
      dbms_output.put_line('未知异常');
end;


--自定义异常
declare myexe exception;
        sex varchar2(3):='&请输入性别';
begin
  if(sex not in ('男','女')) then
     raise myexe;
   end if;
   exception
     when myexe then
       dbms_output.put_line('未知性别');
     when others then
       dbms_output.put_line('未知异常');
end; 




--存储过程 
定义 不能直接返回值,间接返回
create or replace procedure syso(mystr varchar2)
as 
begin
  dbms_output.put_line(mystr);
end;


--调用begin end中  exec(sal命令) call(sal语句) 三种方式
begin
  syso('hello world');
  mul(123,456);
end;
--默认传入的参数在存储过程中只读 默认in;
create or replace procedure mul(p number,p2 number)
as
begin
  syso(p*p2);
end;



-- out表示参数值，可以修改的 可以用户简介返回值
create or replace procedure mula(a1 in number,a2 in number,a3 out number)
as 
begin
  a3:=(a1*a2);
end;


declare a3 number;
begin
 mula(345,789,a3);
 syso(a3);
end;


--函数

create or replace function fun(p1 number,p2 number)
 return number
as 
begin
  return p1*p2;
end;


declare result number;
begin
  result:=fun(123,456);
  syso(result);
end;


3、要求定义两个function  分别实现NVL和NVL2的功能

create or replace function myNvl(str varchar2,value1 varchar2)
return varchar2
as 
begin
  if str is null then
    return value1;
  else
    return str;
  end if;
end;


create or replace function myNvl2(str varchar2,value1 varchar2,value2 varchar2)
return varchar2
as 
begin
  if str is null then
    return value2;
  else
    return value1;
  end if;
end;


--存储过程和函数
 1.存储过程使用关键字 procedure 函数function
 2.存储过程 只能使用out间接返回值 ， 函数必须要有一个返回值
 3.函数是一个特殊的存储过程 存储过程能使用的技术 函数都能使用 存储过程是预编译
 (一次编译多次执行)的，速度快于sal与plsal
 4.存储过程只能在plsal中调用 函数可以在任何地方调用
 ---
 函数：有返回值。执行的时候 是作为表达式用的。如select 函数 from dual;
存储过程：无返回值。执行的时候是exec 过程名。
包：将自定义的功能，如变量，函数，过程，触发器等划分到一个自包含的单元中,
  调用功能是，用包名.功能名
  
--oracle常用优化方式有哪些


  有物理优化和逻辑优化：
  物理优化——升级硬件，提高网速，使用合适的优化器，修改合适的数据库连接数量，禁止回收站，
  合理使用索引，索引和数据分开，不同数据放到不同磁盘上管理等等
  逻辑优化：编写sql语句不要使用*号，列不要经过计算会不使用索引，
  不要使用is null和not is null，用exits和not exits替换in和not in，
  使用占位符，设置合理的隔离级别，编写数据做好分类。
  
--触发器
  触发器(trigger)：当特定的DML语句（INSERT,UPDATE或DELETE）运行时，由数据库自动运行的过程。
  触发器可以在DML语句运行之前和之后激活。
  触发器可以在DML语句作用的每一行上都运行一次，也可能只在所有的行上运行一次。
  
  
--第一种
create or replace trigger trg_emp
 before insert or delete or update on emp --去掉 行级触发器 for each row
 for each row --行级触发器 一次sal中如果影响多行，执行多次
begin
  --新旧值 :new  :old
  --insert 插入 new 值
  --delete 删除old值
  --更新 将old 改为 new 值
  if(inserting) then
   syso('正在插入'||:new.ename);
   if(:new.comm is null) then
     :new.comm:=0;
   end if;
  end if;
  if(deleting) then
    syso('正在删除'||:old.ename||:old.sal);
   end if;
   if(updating) then
    syso('正在更新');
   end if;
end;


delete from emp;
delete from emp where ename='za';
update emp set sal=100 where ename='aa';
insert into emp(empno,ename) values(145,'za');
select * from emp;
 
--第二种
create or replace trigger trg_emp
 before insert or delete or update on emp --去掉 行级触发器 for each row
begin
  --新旧值 :new  :old
  --insert 插入 new 值
  --delete 删除old值
  --更新 将old 改为 new 值
  if(inserting) then
   syso('正在插入');
  end if;
  if(deleting) then
    syso('正在删除');
   end if;
   if(updating) then
    syso('正在更新');
   end if;
end;


  Delete  from  aa where  rowid  != all(select max(rowid) from aa group by name,age); 

三.1 使用游标输出 scott中所有的雇员名称，部门名称，年薪

   第一种
   declare v_emp_ename emp.ename%type;
           v_dept_dname dept.dname%type;
           v_emp_sal emp.sal%type;
    cursor v_curosr is select e.ename,d.dname,(nvl(sal,0)+nvl(comm,0))*12 from emp e inner join dept d on e.deptno=d.deptno;
    begin
      open v_curosr;
      loop
        fetch v_curosr into v_emp_ename,v_dept_dname,v_emp_sal;
        exit when v_curosr%notfound;
        dbms_output.put_line('ename='||v_emp_ename||' '||'dname='||v_dept_dname||' '||'sal='||v_emp_sal);
      end loop;
      close v_curosr;
    end;
       


第二种
    declare v_emp_ename emp.ename%type;
            v_dept_dname dept.dname%type;
            v_emp_sal emp.sal%type;
            sqlvar varchar2(200):='select e.ename,d.dname,(nvl(sal,0)+nvl(comm,0))*12 from emp e inner join dept d on e.deptno=d.deptno';
            
      mycursor sys_refcursor;
    begin
    
      dbms_output.put_line(sqlvar);
      sqlvar:='select e.ename,d.dname,(nvl(sal,0)+nvl(comm,0))*12 from emp e inner join dept d on e.deptno=d.deptno';
      open mycursor for sqlvar;
      loop
        fetch mycursor into v_emp_ename,v_dept_dname,v_emp_sal;
        exit when mycursor%NOTFOUND;
        dbms_output.put_line('ename:'||v_emp_ename||' '||'dname:'||v_dept_dname||' '||'sal:'||v_emp_sal);
      end loop;
      close mycursor;
    end;    
    


   2 定义存储过程 可以传入以下参数
          query(ename,job,sal)
        如果传入了某几个参数 以参数组合的形式查询结果
      比如调用过程如下
       query('Cleck',null,null);
       查询的sql为
       select * from emp where ename like '%Cleck%';
        query('Cleck','Manager',null);
       查询的sql为
       select * from emp where ename like '%Cleck%' and job like '%Manager%'
       要求输出查询的结果
       
       create or replace procedure queryEmp(ename varchar2,job varchar2,sal number)
       as    
          vsql varchar2(200) :='select * from emp where 1=1';
       begin
         if(ename is not null) then
           vsql:=vsql||'and ename'' '||ename||'''';
         end if;
         if(job is not null) then
           vsql:=vsql||'and job'' '||job||'''';
         end if;
         if(sal is not null) then
           vsql:=vsql||'and sal'' '||sal||'''';
         end if;
       end;
       
`      begin
         queryEmp('SMITH',null,null);
       end;
       select * from emp where ename like '%SMITH%';
       begin
         queryEmp('BLAKE','MANAGER',null);
       end;
       select * from emp where ename like '%BLAKE%' and job like '%MANAGER%'


        
 select * from emp;


      
  3 写出一个分页的存储过程
        定义如下
           tablePager(tableName,curPage,pageSize)
        调用 
            tablePager('Emp',2,10) 
            查询emp表中 第二页的数据（每页显示10条 第二页就是 10-20条）
         curPage*pageSize-(pageSize-1)  curPage*pageSize
         curPage*pageSize-pageSize*1+1=(curPage-1)*pageSize+      

  
    create or replace procedure tablepager(tableName varchar2,curPage number,pageSize number)
    as  
      starIndex number:=(curPage-1)*pageSize+1;
      endIndex number:=curPage*pageSize;
      vSql varchar2(200):='';
      colSql varchar2(200):='';
      mycursor sys_refcursor;
      myResult varchar2(200);
    begin
      dbms_output.put_line(starIndex);
      select replace(wm_concat(column_name),',','||'' ''||') into colSql from user_tab_cols where table_name=tableName;
       vSql:='select '||colSql||' from (select t.*,rownum rn from '||tableName||' t) where rn>='||starIndex||' and rn<='||endIndex;
       
       open mycursor for vSql;
       loop
          fetch mycursor into myResult;
          exit when mycursor%notfound;
          dbms_output.put_line(myResult);
       end loop;
       close mycursor;
    end; 
     
   
    begin
      tablepager('DEPT',2,3);
    end;       
                  
  4 定义一个存储过程 传入表名
        删除该表中的重复记录
          比如 deleteMul(tableName)
              调用 deleteMul('emp'); 必须删除表emp的重复数据  （execute immediate    using ）
 
  create or replace procedure deleteMul(tableName varchar2)
  as 
  colSql varchar2(2000):='';
  begin
    select wm_concat(column_name) into colSql from user_tab_cols where table_name=upper(tableName);
    execute immediate 'delete from '||tableName||' where rowid not in(select max(rowid) from 
    '||tableName||' group by '||colSql||')';
  end;
  
  begin
   deleteMul('sc');
  end;
                                                                                                                                                        
select * from sc;

四.实例

select * from dept;
 -- 1.新增用户 orderUser 给予超级管理员（DBA）
  create user orderUser identified by orderUser;
  grant dba to orderUser;
 
 -- 2.使用orderuser登陆 并且查询scott账户下 所有雇员的雇员名称和部门名称
   select ename from emp;
   select deptno from dept;
   select e.ename,d.dname from scott.emp e inner join scott.dept d on e.deptno=d.deptno ;
  
--   3.回收orderUser的超级管理员权限(DBA) 使用orderuser登陆 查询第二题
   revoke dba from orderUser;
   
   select * from scott.emp;
   grant select on scott.emp to orderUser;
--  4.使用orderuser 导出scott账户下的所有表
   grant EXP_FULL_DATABASE to orderUser;
   exp orderUser/orderUser@ORCL file=f:\scott.dmp owner=scott
   
--  5.使用plsqldeveloper导出orderuser账户下的所有表的sql  使用sqlplus命令删除用户下所有的表 并且导入
   
--  6.锁定orderuser账号后 删除orderuser账号
   alter user orderUser account lock;
   drop user orderUser;
--   7.使用客户端备份（exp） 只备份scott用户下的emp表 其他表不备份
   
   exp scott/scott@ORCL file=e:\app.dmp tables(emp) 
   
--  8.新增一个用户不给于dba角色权限（考虑赋予其他权限） 尝试备份scott用户下的所有表
   create user manger identifide by manger;
   grant create session to manger;
   grant EXP_FULL_DATABASE TO manger
   exp scott/scott@ORCL file=c:\scott.dmp owner=scott;
   
--   9.在scott用户下 复制emp表 emp1  使用exp备份 所有emp开头的所有表


   
   create table emp1 as select * from emp
   
   exp scott/scott@ORCL file=e:\sco.dmp tables(emp%)
   
   10.尝试在plsqldeveloper中进行客户端备份和还原
   
   11.假设在c:/test目录下 有两个dmp文件 a.dmp b.dmp 通过一个还原命令将两个文件中所有 emp开头的表导入到scott用户下
   FOR /R C:\test %%i IN (*.dmp) DO(
       echo %%i
       imp scott/scott@ORCL file=%%i full=y ignore=y
    )
    pause


--  12.给orderUser只能访问emp表ename和sal列更改的权限 其他的列不允许orderUser修改权限
            比如orderUser 发起sql  update scott.emp set ename='a',sal=123 where empno='1234' 有权限
                             update scott.emp set comm=123 where empno='1234' 无权限
                            
       grant update(ename,sal) on scott.emp to orderUser;
       update scott.emp set ename='a',sal=123 where empno='1234'                      
       update scott.emp set comm=123 where empno='1234'   
          select * from emp;                   
 --  13.请自己通过工具 比较 sys和system用户的不同
   sys:超级管理员,在系统中权限最多的用户 sysem普通管理员 相对比较少
   
--   14.查看当前用户的所有系统权限和对象权限和角色权限
   select * from user_role_privs;--角色权限
   select * from user_sys_privs;--系统权限
   select * from user_tab_privs_made;--对象权限