Oracle数据库的知识点总结（PL/sql）

1， 登录数据库
      sqlplus  “/as sysdba”    最高权限
 conn user/pwd     切换用户
 创建用户： create  user userName identified by pwd;
2,   || 可以把两个字段合并显示 
      如：select xing||‘。’||名  from user
3，  nvl() 空值的代替算法
     select nvl(age, 0) from user; 如果年龄为空 设置年龄为0
4，  去重复distinct，在select关键字后如果有多列字段 表示联合去重复
5，  order  by 执行权限最低 
         如果 order by 后有多行 则先排第一行 如果第一行重复再排第二行
6，  模糊查询 like
       _ : 代表一个字符
  % ：代表任意一个字符
  escape： 代表 转意字符
  如： select id，last_name ,salary 
     from  s_emp
 where  last_name like ‘%/_%’  escape '/';
7，  and的逻辑比or 逻辑的级别要高
8，lower() 转小写
9，upper()转大写
10，initcap() 首字母转大写
11，concat() 把两个字符串连接起来
12， substr() 截取字符串
13，length() 字符串的长度
14，round() 四舍五入
15，trunct()截取到个位
16，mod()取余        floor（）向下取整    ceil（）向上取整
17，months_bewteen(sysdate+30, sysdate): 两个时间之间相差几个月
18,add_bewteen(sysdate,2):系统两个月后的时间
19，左外连接
     select last_name , name  from  s_emp left outer join s_dept
    on s_emp.dept_id = s_dept.id;    //把没有部门的员工也显示出来
20，右外连接
    select  last_name，name from 
s_emp right outer join  S_dept
on s_emp.dept_id=s_dept.id;         //把没有员工的部门也显示出来
20，全连接 r
  select last_name ， name from s_emp full outer join s_dept on
   s_emp.dept_id= s_dept.id;             //把没有部门的员工 和 没有员工的部门都显示出来
21，union ：select last_name from s_emp  
             union
select first_name from  s_emp    //两个结果集的并集（没有重复的）返回数据由第一条查询语句的名字
22, union all 
            select last_name from s_emp  
             union
select first_name from  s_emp    //两个结果集的 所有的东西 不去除重复的  返回数据由第一条查询语句的名字
23，minus  第一个结果集除去第二个结果集和它相同的部分
24，intersect  求俩个结果集的交集
25， rownum 伪列
       只能=1
  只能>0 
  可以小于任何数  （不能使用 bewteen and   能使用 in）
26，group by  分组：  select后面的字段一定要出现在group by后面 分组函数除外 group by 后面的函数 不一定出现在select后面
27，分组函数 avg       求平均值
count     计算有多少条数据
max       最大值
min       最小值
sum       求和


stddev    标准差
variance  方差
分组函数使用 ：.select后面 
2.having后面 
3.order by后面
4.where后面一定【不能】出现组函数
28，语句执行顺序
     where >group by  > 执行组函数 >having 筛选 》 order by
29，设及到分组排序 要先分组在排序
30，约束 列级约束  ：   primary key  主键  


                         reference 引用的主键的表名（主键名）  ：reference s_emp（id）    
                         not null ： 非空
                         unique  ：唯一
check（）：检查
                           


31，要删除一个在主键被其他表引用为外键 删除时要级联删除才能
     drop table 表名 cascade constraints;
32，表级约束：
           primary key （id）：写道表中字段的后面 
           unique（字段名）：
           非空约束不能使用在表级约束的情况下
           foreign key（字段名） reference  表名(id)
           联合主键  primary key（字段1，字段2，.....）
           联合外键 foreign key(字段1，字段2，....)reference 表名(字段1，字段2,......)   
33,constraint：约束，给约束起名字
34，事务控制语句
        commit，rollback ，savepoint
     定义语句  create  drop  alter truncate rename
DCL ： grant revoke
 
35，事务的原则
     a，原子性 b，一致性 c，隔离性 d，持久性
36，alter 的使用
      增加列：alter table   table_name 
            add columnname 数据类型  [约束]  
       例子：alter table t_s  add  age number  constraint  t_s_check_age check(age bewteen 10 and 100)
 删除列：alter table sr drop column  age；
 修改列：alter table sr modify age char(20) unique;
37，整加约束
    alter table  表名 add  constraint  约束名  primary key（id） //增加 主键
alter table  表名 add  constraints  约束名 foreign  key(ser_id) reference sr//表名 (id)//字段名
删除约束
alter table 表名 drop constraint  约束名
例如：alter table test drop primary key
alter table 表名 drop constraint test_pk_name
使一个约束生效
alter table 表名 enable constraint  约束名
alter table test enable  constraint  ag_sr_id_fk;
使一个约束失效
alter table 表名 distable constraint 约束名
38，删除表
   drop table table_name   ：删除表结构 表空间
   truncate table table_name ： 清空表 回到原始创表状态
   delete：可以指定删除某些咧，不释放表空间
39，重命名
     重命名表：rename old_tableName to new_tableName
重命名列：alter table table_name rename  column  old_col_name  to new_col_name
40，创建队列
       create sequence 队列名称
  create sequence s_name
[increment by n]:步长
[start with n]：开始值
[maxvalue n]：最大值 10
[minvalue n]：最小值 1
[cycle]：是否循环，建议不使用
[cache n]：缓存
create sequence mySeq
increment by 1
start with 0
maxvalue 10
minValue  -2
cycle
cache 3;
注意:通常写create sequence  name;
默认步长为1 ，从0开始，下一个值是1
41，只读事务
      set transation read only  在设置了只读事务后，尽管其他事务会提交新的事务 
 但只读事务将不会取得最新的数据变化，从而保证取得特定时间点的数据信息
 
42，导出表
  exp userid=用户名/密码@数据库名 tables=（表名1，表名2） flie=d：\文件名.emp   导入导出表需要进入 orcale的bin目录下
            在末尾 加 rows = n //表示导出表结构
在末尾 加 direct=y // 表示直接导出 比默认的方式要快
43，导出方案
   exp userid=system/密码@数据库名  owner=（用户名） flie=d：\文件名.emp  
44，增量备份
     exp userId=用户名/密码@数据库名 full=y inctype=complete file=d：\文件名。emp
  
 45，导入表
   imp userid=用户名/密码@数据库名  tables(表名1，表名2) file=d: \文件名.emp
              在末尾加 touser=scott 导入该用户需要的权限
 在末尾 加 rows = n //表示导入表结构
 在末尾加 ignore=y 只导入数据
   46，查询
       select table_name from  user_tables;  //查询用户拥有的所有表
  select  table_name from  all_tables ; //查询用户能访问的所有表
47，dba_tables 查询表  //所有表
   必须拥有  dba角色 或者拥有查询所有表的权限
    48，查询角色
  select *  from   dba_roles;
  
   49，建立索引                                           缺点：数据冗余  数据更新必须更新索引
       create index  indexName  on 表名(字段名，字段名2)
  索引的类型  存储方式分类：B*树，反向索引，位图索引
                  个数分类：单列索引 复合索引
索引列值的唯一性：唯一索引 、非唯一索引
还有：分区索引 全局索引 函数索引
50，权限 系统权限 对象权限
51，授予权限的角色必须有 grant any privilege   带有 with admin option (这是系统用户 如果是普通 用户为 with grant option) 
的话以为着授权的用户可以把自己的权限
  授予其他用户
      列： grant create table ，create session to 用户
    53，收回权限
  revoke create session   from   用户名
    54,授予用户对象权限
    grant select  on  表名  to 用户名       =grant all on  表名 to  用户名 {把修改 查询 删除得权限都给了他}
       update
delect
在表名后加（）里面添加字段名 则对应只能受用该字段上的权限
55 建立表空间
            建立表空间(一般建N个存数据的表空间和一个索引空间)：        eg:create tablespace SHISPACE   
                create tablespace 表空间名  
            datafile ' 路径(要先建好路径)\***.dbf  ' size *M  
            tempfile ' 路径\***.dbf ' size *M  
            autoextend on  --自动增长  
          --还有一些定义大小的命令，看需要  
         default storage(  
        initial 100K,  
         next 100k,  
          );                                                             datafile 'D:\orcale\myself\SHISPACE_TBSPACE.dbf'   
                                                                           size 150M   
                                                                          autoextend on next 5M maxsize 300M; 
           
56，删除表空间 
   drop tablespace DEMOSPACE including contents and datafiles  ；
 
    57,给用户授予表空间权限
alter user username  default tablespace userspace;
58：plsql的结构
     三个区  ： declare--定义区
                 begin -------执行区 开始
                 end-------- 执行区结束
execption ----异常信息
    59,      写一段plsql
           create or replace procedure 名字 is
     begin ---执行部分
    insert into  user values（'shi','s23234'）;
end;
/

60 执行一段存储过程
     exec 存储过程名（参数1，参数2）；
 或者 call 存储过程名（参数1，参数2）
  
     61，oracle 的基本 单位是快
            |-----过程
        |
        |
        |-----函数
块----- |
        |------触发器
|
|
|------包


62，标识符号的命名规范
       1，定义的是变量时 建议用V_ 作为前缀
2，当定义一个常量时 建议用c_作为前缀
3， 定义游标 用_cursor 作为后缀
4，定义异常时 用 e_作为前缀
63，PL/sql 的包
    dbms_output 包 输出包
 
如： dbms_output.put_line('hello,world');
64,想输出的东西在控制台看见 
    要设置  set serveroutput on;
不想看见 设置 set serveroutput off；
 65 declare--定义区和执行部分的快
     v_ename varchar2(5);
 begin 
    select ename from emp where ename=‘哈哈’
dbms_output.put_line('','')
end；
 66，通过控制台 输入调价
 d declare 
  v_pwd varchar2(20);
  begin
    select pwd into v_pwd from use where name=&a;
dbms_output.put_line('密码是'||v_pwd）
end
通过控制台输入：“haha or 1=1”   //会出现sql 注入错误
 67，块异常的编写
      declare
v_pwd varchar2(30);
begin
select pwd  into v_pwd from use where name=&a;
dbms_output.put_line('用户密码'||v_pwd);
exception
when no_data_found then dbms_output.put_line('没有查到数据');
   when others then dbms_output.put_line('其他');
end;
/
 
 68，函数用与返回特定的数据当建立函数时，在函数头部必须包含return语句
          而在函数体内部必须包含return语句返回的数据，我们可以使用create function
         来建立函数实际案例
//写一个函数 通过输入的姓名 返回密码
 create function sg_fun(ename varchar2)
           return number is
     v_fun_pwd varchar2(30);
            begin 
               select  pwd  into v_fun_pwd from use where name=ename;
                return v_fun_pwd;
             end;
 
             /
       69,在cmd 调用 定义变量
           var ename  varchar2(30)
             call sg_fun('ssh') into : ename;
                 print ename
        70,在Java程序中 调用该函数
           select sg_fun('ssh') from dual;
71,创建包 
create package sg_package is 
  procedure shipro;
  function sg_fun( ename varchar2) return varchar2;
  end;
72,创建包体
    create or replace package body sg_package is
   procedure shipro is 
begin
 dbms_output.put_line('hello shi');
 end;
 function sg_fun(ename varchar2) return varchar2
 is  v_fun_pwd varchar2(30);
 begin  
   select pwd into v_fun_pwd  from  use  where name=ename;
return v_fun_pwd;
end;
end;
73,通过 exec 包名.函数、方法 调用函数或方法
74，plsql的变量
  标量类型 Scalar
  复合类型 composite
  参照类型 reference
  lob 
 75，标量
    如 ：v_ename varchar2(10)
定义一个小数并给他一个初始值5.4 := PL/sql的赋值语句
v_sal number(3,2):=5.4
     定义一个日期类型
               v_hiredate date;
              定义一个布尔值
               v_valid boolean not null default false
 76，定义变量时 可以让变量的长度和数据库与里存放字段的长度相匹配
               v_ename  表名.字段名%type
          77，复合类型的定义---记录
declare
--定义一个pl/sql记录类型 emp_record_type,类型包含三个数据 name、salary、title
type emp_record_type is record(
name emp.ename%type,
salary emp.salary%type,
title emp.job%type
)
---定义一个sg_record 变量，这个变量的类型是emp_record_type
sg_recored emp_record_type;
begin 
select ename,salary,job into sg_recored
from emp where empno=7788;
dbms_output.put_line('员工姓名:'|| sg.record.name)
end;
78，复合类型的表  //想当于高级语言的数组
declare
--定义一个pl/sql表类型sg_emp_table ，该类型用于存放emp.name%type
--inde by binary_integer 表示下标是整数
type sg_emp_table is table of emp.name%type index by binary_integer;
sg_table sg_emp_table;
begin 
select ename into sg_table(0) from emp where empno=7788;
dbms_output.put_line('员工姓名:'|| sg_table(0));
end;
79，参照变量之游标变量 （ref cursor）
    使用游标时当定义游标时不需要指定相应的select语句
但当使用游标时（open时）需要指定select语句，这样一个游标变量就和一个
select语句结合了
eg：
 declare 
 --定义一个游表类型
   type sg_emp_cursor is ref cursor;
     --定义一个游标变量
 sg_cursor sg_emp_cursor;
 --定义一个变量
  v_name emp.empname%type;
  v_sal emp.sal%type;
  begin
   --执行 把游标和select结合在一块
open sg_cursor for 
select ename,sal from emp where dept_id=&aa;
loop --循环取出游标变量里得值
 fetch sg_cursor into v_name,v_sal;
 exit where sg_cursor%notfound；
 dbms_output.put_line('名字：'||v_name)；
 end loop；
 end；
 80，分支语句
 create or replace procedure sp_pro6(sName varchar2)
is v_sal emp.sal%type;
              begin
                select sal into v_sal from emp  where ename =sName；
               --判断
                if v_sal<2000 then
                  update emp set sal=sal*1.1where ename=sName;
  --可以加 else if 条件 then  操作  else操作
                 end if;
                  end;  
81，循环语句
  -- 先自行一遍 在判断
create replace procedure  sg_pro2(Sname varchar2) is

v_num number:=1;
begin 
 loop 
  insert into users values(v_num, sname);
  exit when v_num=10;
   v_num=v_num+1;
end loop;
end;
/
--另一种循环 判断后执行
           create replace procedure  sg_pro2(Sname varchar2) is

v_num number:=11;
begin 
 while v_num<20 loop
  insert into users values(v_num, sname);

   v_num=v_num+1;
end loop;
end;
/
   -- 循环语句 for
                     begin
  for i in reverse 1..10 loop
                        insert into users  value(i,'顺平');
end loop；
end；
/
  82,goto 语句执行顺 
      delclare
   i int :=1;
begin 
 loop 
   dbms_output.put_line('输出i='|| i);
if i=10 then 
goto end_loop;
end if;
i:=i+1;
end loop;
《end loop》
dbms_output.put_line("循环结束");
end；
83.null 空语句 表示什么都不会干 补充结构
                84，存储过程
  in ： 表示一个输入参数
  out： 表示一个输出参数
  ----无返回值
  create or replace procedure SG_PRO7
  (
  sgBookId in number,
   sgBookName in varchar2,
sgPublishHouse in varchar2
  )
    is 
     begin
   insert into books values(sgBookId,sgBookName,sgPublishHouse);
end;
  -----有返回值
  create or replace procedure sg_pro8
  (sgNo in number, sgName out varchar2) is
  begin 
                       select bookName into sgName  from  books where bookId=sgNo;
  end;
  /
-----返回结果集  //创建一个包 定义一个 游标变量 test_cursor
1,创建包
  create  or replace package testspackage as
  type test_cursor is ref cursor;
  end  testspackage;
 //2 创建过程
                       create or replace procedure sg_proArr(sgNo in number,p_cursor out testspackage.test_cursor)
                        is begin
                            open p_cursor for select * from books where bookId= sgNo;
end;
       85，通过存储过程 分页
   //输入 要显示的页  每页显示的个数   查询的表名
// 输出 记录的总页数 记录的总条数   结果集
create  or replace procedure fenye(
tableName  in  varchar2,
sizeNum    in   number,
nowPage    in  number ,
pageNum    out   number,
mycount    out  number,
t_cursor   out  testspackage.test_cursor
)is
v_sql  varchar2(1000)
begin
v_sql:=   select t1.*,rownum  rn  from  (select * from emp ) t1
 





86，oracle 预定义例外有二十个


                        no_data_found ：数据查询不带例外
                        case_no_found : 条件不匹配例外


                  eg： 
                      create or replace procedure sg_pro88(sno number) is
                       s_sal emp.sal%type;
                      begin
select sal into s_sal from emp where empId= sno;
case
whern v_sal <1000 then 
  update emp set sal=sal+1000where empId =sno；
where v_sal <2000 then 
  update  emp set sal = sal+500 when empId = sno;
  end case;
  exception 
    when case_no_found then 
  dbms_output.put_line("没有匹配的数据");
  
 end;
 
cursor_already_open:游标打开后 有去打开就会有这个错误
dul_val_on_index:在索引处添加重复的值
invaild_cursor:在游标上进行不合法的操作 ：关闭未打开的游标  读取为开开的游标值
   invaild_number：输入的数字不合法
too_mary_rows: 一个变量被赋予结果集
zero_divide:除于0得异常
error_value: 变量的长度不能存储下结果
 ----自定义例外
create or replace ex_test(sno number) is
is 
myexceotion;
begin 
 update emp set sal=sal+1000 where empno= sno;
   ---sql%notfound 这是表示 没有update
---raise myexceotion :表示触发这个意外
 if sql%notfound then
 raise myexceotion;
 end if;
 exception 
 when myexceotion then
   dbms_output.put_line('没有找到这个用户');
 end;
 87，创建视图
   表 占用磁盘资源 视图不占用磁盘资源

                视图不能加索引
                 视图可以简化复杂查询
                 视图有利于提高系统安全性  
create view myview as select name ，salary  from emp；
               ===create or  replace view 视图名 as select 查询语句  【with read only 只读】
                  drop view 视图名  删除视图
 视图更新
              如果视图是从单个基本表只选用选择、投影作导出的，并且包含了基本表的主键，那么这样的视图成为“行列子集视图”，并且可以被执行更新操作。但由于视图里面没有存放数据，作更新的时候要回到基本表中。所以说是会影响的。
表更新 视图会更新是