oracle知识点复习总结

--复习
--第一章
--1.建表时添加相关约束
create table onet
(
id number(5) primary key,--主键
name varchar2(8),
age number(3),
time timestamp,
sex char(2),
school number(4),--外键
constraint ch_age check(age>18 and age<80),
constraint fk_schol foreign key(school) references emp(empno)
);
--2.修改约束信息
alter table onet drop constraint ch_age;--删除
alter table onet add constraint ch_age check(age>18 and age<80);--添加
alter table onet modify constraint ch_age check(age>0 and age<100);
--3.序列
create sequence seq_one
start with 1--初始长度
increment by 1--步长(如果取负值则为递减序列)
nomaxvalue--无最大值
cache 20;--预分配序列个数
--使用序列
insert into onet(id) values(seq_one.nextval);
--4.子查询
--单行
select*from emp where sal=(select max(sal) from emp);
--多行(in,not in,exists,not exists,all,any)
select*from emp where sal in(select sal from emp where sal>900);
select*from emp where sal>=all(select sal from emp where deptno=20);
--相关
--查询管理者
select empno,ename,deptno,sal from emp a
where exists(select*from emp b where b.mgr=a.empno);
--5.将A表的数据录入B表
insert into B select*from A;
--6.删除
--delete   Truncate
truncate table emp;
--7.分页查询(Rownum伪列 )
--ROWNUM 是伪列，只能 <=，不能 >=，所以需要给ROWNUM起个别名，变成逻辑列后来比较
select*from emp where rownum>=5


select e.*from
(select rownum r, deptno,ename,job,mgr from emp)e
where r>(2-1)*5 and r<2*5+1;-- 2 指当前页 ,5 指每页大小


SELECT * from (select p.*,rownum r from emp p where rownum<=6 ) where r>=1--效率较高


select e.*from
    (select rownum r, empno empNo,ename as empName,job,hiredate from emp order by empno desc)e--内部整体降序
    where r >0 and r <6
    order by empno --局部升序
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
--第二章（内部结构）


--1.表空间(需要管理员权限)
create tablespace tbsp
datafile 'D:\aaa\my_aaa.dbf'--存放位置
size 3M--初始大小
--2.创建用户(需要管理员权限)
create user aaa --用户名
identified by aaa--密码
default tablespace tbsp--默认表空间
--临时表空间temporary tablespace tbsp
--3.用户授权
grant connect,resource to scott;
--4.导入/导出数据
--(1)创建目录对象
create directory my_im_exp_bak as 'D:\aaa';
--(2)用户授权
grant read,write on directory my_im_exp_bak to scott;
--(3)断开连接   在命令窗口 导出用户aaa的全部数据
--expdp scott/tiger schemas=scott dumpfile=scott_bak.dmp logfile=scott_bak_log.log directory=my_im_exp_bak
--结尾一定不能加;号
--(4)导入impdp scott/tiger directory=my_im_exp_bak logfile=scott_bak_log.log dumpfile=scott_bak.dmp schemas=scott


--------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
--第三章（常见函数）


--1.字符函数
--(1)concat或||
select concat('d12',110)||'还可以拼接数字' from dual;---拼接两个字符串/两个数字    三个或以上用"||"
--(2)initcap首字母大写
select initcap('zhoU xiAng yUan') from dual;
--(3)instr搜索字符串返回位置
select instr('nihaomawohenhao','o',1,2) from dual;--原字符串,要搜索的字符,开始搜索的位置,第几次出现的位置
--(4)length长度
select length('周向') from dual;--参数也可以是数字
--(5)lower转为全小写
select lower('JojoiDuBjakHlk') from dual;
--(6)upper全大写
select upper('JojoiDuBjakHlk') from dual;
--(7)rpad 右黏贴 lpad 左黏贴
select lpad(rpad('向',4,'源'),6,'周') from dual;
--(8)rtrim 删除右边出现的字符 ltrim 删除左边出现的字符
select ltrim(rtrim('***%***%#%##7654#%$@**###**%**##','#'),'*') from dual;
--(9)substr截取
select substr('zaoshanghao',4,5) from dual;
--(10)replace替换
select replace('ni0000000000000000ha0o0m0a','0',null) from dual;--原字符,需要替换的字符,新字符
--(10)trim删除字符串两边的字符
select trim('   trim ') from dual;
select trim('%'from '%*   % trim  %  $%')from dual;
select trim(leading '0' from '0000123000')from dual;--删除头部的0
select trim(trailing '0' from '012300')from dual;--删除尾部的0
select trim(both'1'from'112345111')from dual;
--2.数学函数
--(1)abs绝对值
select abs(-45) from dual;
--(2)ceil返回大于等于给出数字的最小整数
select ceil(3) from dual;
select ceil(3.000215) from dual;
--(3)floor   舍去小数,取整数
select floor(5.9999999) from dual;
--(4)mod (n1,n2) 取余数
select mod(16,5) from dual;
--(5)power(n1,n2) 返回n1的n2次方
select power(3,3) from dual;
--(6) sign 判断数字的正负  >0 返回1,<0 返回 -1,==0 返回 0
select sign(-99) from dual;
select sign(16) from dual;
select sign(0) from dual;
--(7) sqrt 返回数字的根
select sqrt(4) from dual;
select sqrt(9) from dual;
select sqrt(16) from dual;
--(8) round(n1,n2) 按照指定的精度四舍五入  
select round(124.1698,2) from dual;  
select round(124.1698,-2) from dual;  
select round(124.1698,0) from dual;  
select round(124.1698) from dual;  
--(9) trunc 按照指定精度截取一个数
select trunc(124.1698,2) from dual;  
select trunc(124.1698,-2) from dual;  
select trunc(124.1698,0) from dual;  
select trunc(124.1698) from dual;  
--3. 日期函数
--(1) sysdate 获取系统当前日期 (date 类型 )
select sysdate from dual;
--(2) add_months 增加 / 减去月份
select add_months(sysdate,2) from dual;--增加 2 月
select add_months(sysdate,-2) from dual;--减少 2 月
--(3) last_day 返回日期 ( date) 的最后 一天
select last_day(to_date('2016/02/02','yyyy/mm/dd')) from dual;
select last_day(SYSDATE) from dual;
--(4) months_between( date2,date1)    date2 与date1相差的月份
select months_between('9-12月-2008','9-11月-2016') from dual;
--(5) next_day(date,'day') 给出日期date 和星期 之后 ,计算下一个星期的日期
select next_day(sysdate,'day') from dual;
--(6) 截断日期trunc
select trunc(sysdate,'ss') from dual;--保留秒 但必须有毫秒
select trunc(sysdate,'mi') from dual;--保留分钟
select trunc(sysdate,'hh') from dual;--保留小时
select trunc(sysdate,'mm') from dual;--保留天
--4.转换函数
--(1) to_char 转换为字符串
select to_char(sysdate,'yyyy,mm,dd hh24:mi:ss') from dual;
select to_char(23,'99') from dual;--将int 转为string 参数 2 表示: 与参数1相同位数的最大数
--(2) to_date  把String转换为 Date
select to_date('2016-07-09','yyyy-mm-dd') from dual;
--(3) to_timestamp 把String转换为 timestamp
select to_timestamp('2016,04,16','yyyy,mm,dd') from dual;
--(4) to_number  把String 转换为 number
select to_number('45678.12') from dual;
--5.其他函数
--(1)  user  返回当前用户的名字 ,   uid   返回用户的id
--(2) nvl(val1,val2) : 如果val1的值为null,则返回 val2;不为空返回 val1;    注意 ,val1和 val2 两者的类型要一致
--(3) nvl2 (val1,val2,val3) : val1不为null ,返回val2; 为null ,返回val3;    注意,如果val3 与val2 类型不同的话,3 的类型会转为 2
--(4) nullif (val1,val2) :  val1与val2 相等则返回 null ,不等则返回 val1;
--6.聚合函数
--count() ,max(),min(),sum(),avg()
---------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--第四章 ( 使用 PL/SQL语句 )
--1.PL/SQL概述
      --(1) 声明常量与变量
      declare 
      v_id constant number(3,2):=3.14;--定义常量 ,并赋值
      v_name varchar2(8) :='丨一';--定义普通变量,并赋值
      begin
        dbms_output.put_line(v_id);
        dbms_output.put_line(v_name);
      end;
      --(2) 数据类型
            --%type 与%rowtype
            declare 
            v_name emp.ename%type;--定义变量v_name 数据类型与emp 表的 ename 列的数据类型一致
            v_emp_row emp%rowtype;--定义变量,存储一行员工信息
            begin
              select * into v_emp_row from emp where empno=7369;
              dbms_output.put_line(v_emp_row.empno||v_emp_row.ename);
            end;
        --(3)record 记录类型( 表类型数据类型 )
        declare 
        type emp_record_type is record(--定义一个记录类型，包含员工信息
          ename emp.ename%type,
          sal emp.sal%type,
          comm emp.comm%type,
          total_sal sal%type
        );
          v_emp_record emp_record_type;--声明记录类型变量
        begin
          select ename,sal,nvl(comm,0),sal+nvl(comm,0) into v_emp_record from emp where empno=7369;
          dbms_output.put_line('员工姓名：'|| v_emp_record.ename);
          dbms_output.put_line('基本工资：'|| v_emp_record.sal);
          dbms_output.put_line('奖金：'|| v_emp_record.comm);
          dbms_output.put_line('实发工资：'|| v_emp_record.total_sal);
        end;


--5.第五章   开发子程序和包
--子程序: 被命名的PL/SQL块(  过程和函数 )


--(1)过程 ( 执行特定操作 )in ; out; in out 
--仅有输入参数
create or replace procedure pro1
    (--参数
    v_no in emp.empno%type     --in 输入参数 ;out 输出参数 ;in out 输入输出参数
    )
as
     v_sal emp.sal%type;  --变量
begin
      select sal into v_sal from emp where emp.empno=v_no;
      dbms_output.put_line('查询的工资是:'||v_sal);--输出
      exception
        when no_data_found then
          dbms_output.put_line('该员工不存在....');
end;


call pro1(7369);


begin
  pro1(7369);
end;


--带输入,输出参数的存储过程( 根据员工编号查询 工资 )
create or replace procedure proinout
(--参数
v_eno in emp.empno%type,--入参7369
v_sal out emp.sal%type--出参
)
as 
begin
  select sal into v_sal from emp where emp.empno=v_eno;
  exception--异常处理
    when no_data_found then
      dbms_output.put_line('员工不存在');
end;
--调用
declare
v_salk emp.sal%type;--接收出参
begin
  proinout(7369,v_salk);
  dbms_output.put_line('工资是:'||v_salk);
end;
--带输入输出参数的存储过程 in out 
create or replace procedure matha
(--参数
v_num in out number
)
as 
begin
  select sal into v_num from emp where emp.empno=v_num;
  exception--异常处理
    when no_data_found then
      dbms_output.put_line('员工不存在');
end;
--调用
declare 
v_one number(10,4):=7499;
begin
  matha(v_one);
  dbms_output.put_line('工资是:'||v_one);
end;


--(2) 函数 ( 返回特定数据 )function  


--2.创建带in和out 参数的函数       输入参数雇员名称,输出参数 部门名称  返回值 岗位信息  
create or replace function fun_getEmpInfor
(
param_ename in varchar2,--雇员名称
param_dname out varchar2--部门名称
)
return varchar2--返回值类型
as
v_ejob emp.job%type;--定义变量,保存雇员岗位信息
begin
  select d.dname,e.job into param_dname,v_ejob from emp e,dept d
  where upper(e.ename)=upper(param_ename) and e.deptno=d.deptno;
  return v_ejob;--返回岗位信息
  exception
    when no_data_found then
      raise_application_error(-20001,'该雇员不存在');
end;
--调用fun_getEmpInfor
declare
v_ename varchar2(20):='smith';
v_bname varchar2(20);
begin
  v_bname:=fun_getEmpInfor(v_ename,v_bname);
  dbms_output.put_line(v_bname);
end;


--(3)包 ( package/package body)
create or replace package emp_package as
       g_deptno number(3):=30;--公有属性
       procedure pro_add_emp(param_empno number,param_ename varchar2,param_sal number,param_deptno number:=g_deptno);--添加员工
       function fun_get_sal(param_empno number) return number;--查询雇员工资
end emp_package;
--包体
create or replace package body emp_package as
       --验证部门是否存在 ( 私有函数 )
       function fun_validate_deptno
         (param_deptno number)
         return boolean as
         v_temp number;
         begin
           select 0 into v_temp from dept where dept.deptno=param_deptno;
           return true;
           exception
             when no_data_found then
               return false;
         end;
         --添加员工的过程
         procedure pro_add_emp(
         param_empno number,
         param_ename varchar2,
         param_sal number,
         param_deptno number:=g_deptno
         )as
         begin
           --如果部门编号存在则插入数据
           if fun_validate_deptno(param_deptno) then
             insert into emp(empno,ename,sal,deptno) values(param_empno,param_ename,param_sal,param_deptno);
           else--否则 ,抛出异常
             raise_application_error(-20001,'不存在你要录入的部门');
             end if;
             exception
               when dup_val_on_index then
                  raise_application_error(-20002,'该雇员编号已经存在');
               end;
          --查询员工工资(公有函数)
          function fun_get_sal(param_empno number) return number as
            v_sal emp.sal%type;
          begin
            select sal into v_sal from emp where empno=param_empno;
            return v_sal;
            exception
              when no_data_found then
                raise_application_error(-20003,'不存在该雇员');
          end;
 end emp_package;
 
 --调用包
 begin
   emp_package.g_deptno:=20;
   emp_package.pro_add_emp(8888,'周',30000);
   
  -- fun_get_sal(&numb);
   dbms_output.put_line(emp_package.fun_get_sal(&eno));--'&' 输入
   end;
 --删除包
 drop package emp_package
 
--6.第六章 （游标和触发器）
        --（1）隐含游标    用于处理select....into和DML ；  显式游标   用于处理select语句返回的多行数据