Oracle 基础总结（下）

第六章 Oracle视图

第一节：视图简介
视图是虚表，没有具体物理数据，是通过实体表的一种计算映射逻辑。主要就是为了方便和数据安全；

第二节：创建视图
Create view 视图名称 as 查询语句
create view v_emp1 as select ename,job from emp;

==============system 用户给scott授权（然后scott要重新登录）否则创建不了视图===========

select * from user_role_privs; 查看用户的角色；
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='DBA';  查看角色对应的权限；
grant dba to scott; 授予用户dba权限

====================================================

第五节：使用视图
查询：select * from v_emp1; select * from v_emp1 where ename like '%M%';
修改：update v_emp1 set job='销售' where ename='sb'; 
添加：insert into v_emp2 values('2222','sb2','技术');
删除：delete from v_emp2 where empno=2222

第六节：只读视图

视图的增删改会改动实体表,实际开发都是设置成只读。
Create view 视图名称 as 查询语句 with read only ;

 

第七章 Oracle约束

第一节：主键约束
第二节：外键约束
第三节：唯一性约束
第四节：检查约束
第五节：默认值约束
第六节：非空约束

 

第八章 Oracle控制语句

第一节：IF条件语句
第二节：CASEWHEN流程控制语句
第三节：循环语句
1，无条件循环 loop
2，while 循环
3，for 循环

set serverout on;   //控制台输出
declare n number:=1;  //declare声明变量，number类型， := 赋值
        v varchar2(20):='world';
begin
   dbms_output.put_line('hello'||n||v);   // ||是连接符号
end;                    //  最后加一个 / + enter键运行


set serverout on;
declare emp_count number;
begin
  select count(*) into emp_count from emp where sal>=3000;  //into把值放到变量emp_count中
  if emp_count>0 then
    dbms_output.put_line('有'||emp_count||'个员工的基本薪资大于等于3000');
  else
    dbms_output.put_line('没有员工的基本薪资大于等于3000'); 
  end if;                // 结束if，否则匹配出错
end;


set serverout on;
declare emp_count number;
begin
  select count(*) into emp_count from emp where sal>=3000;
  if emp_count=1 then
    dbms_output.put_line('有1个员工的基本薪资大于等于3000');
  else if emp_count>1 then
    dbms_output.put_line('有超过1个员工的基本薪资大于等于3000');
  else
    dbms_output.put_line('没有员工的基本薪资大于等于3000'); 
  end if;
  end if;
end;

set serverout on;
declare emp_count number;
begin
  select count(*) into emp_count from emp where sal>=3000;
  case emp_count
    when 0 then dbms_output.put_line('没有员工的基本薪资大于等于3000');
    when 1 then dbms_output.put_line('有1个员工的基本薪资大于等于3000');
    when 2 then dbms_output.put_line('有2个员工的基本薪资大于等于3000');
    when 3 then dbms_output.put_line('有3个员工的基本薪资大于等于3000');
    else dbms_output.put_line('超过3个员工的基本薪资大于等于3000');
  end case;
end;

set serverout on;
declare g_id number:=2;
        g_losal number;
        g_hisal number;
begin
  loop
    if(g_id>4) then
      exit;
    end if;
    
    select losal,hisal into g_losal,g_hisal from salgrade where grade=g_id;
    dbms_output.put_line(g_id || '等级的最低薪资'|| g_losal || '，最高薪资：' || g_hisal);
    
    g_id:=g_id+1;
    
  end loop;
end;


set serverout on;
declare g_id number:=2;
        g_losal number;
        g_hisal number;
begin

  while g_id<5 loop
  
     select losal,hisal into g_losal,g_hisal from salgrade where grade=g_id;
     dbms_output.put_line(g_id || '等级的最低薪资'|| g_losal || '，最高薪资：' || g_hisal);   
     g_id:=g_id+1;
  end loop;

end;


set serverout on;
declare g_losal number;
        g_hisal number;
begin
  for g_id in 2..4 loop     //表示2--4
    select losal,hisal into g_losal,g_hisal from salgrade where grade=g_id;
    dbms_output.put_line(g_id || '等级的最低薪资'|| g_losal || '，最高薪资：' || g_hisal);   
  end loop;
end;

 

 

第九章 Oracle游标

第一节：游标简介
使用游标，我们可以对具体操作数据，比如查询的结果，对行，列数据进行更加细致的处理。以及对其他 DML
操作进行判断等操作；

第二节：显示游标
第三节：隐式游标
隐式游标的属性 返回值类型 意 义
SQL%ROWCOUNT 整型 代表 DML 语句成功执行的数据行数
SQL%FOUND 布尔型 值为 TRUE 代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与 SQL%FOUND 属性返回值相反
SQL%ISOPEN 布尔型 DML 执行过程中为真，结束后为假  （游标默认都是关闭，false）

第四节：动态游标
强类型动态游标
弱类型动态游标

set serverout on;
declare cursor cu_emp is select empno,ename,sal from emp;   //定义游标，有点像集合
e_no number;
e_name varchar2(10);
e_sal number;
begin
  open cu_emp;                            //打开游标，默认是关闭的
  fetch cu_emp into e_no,e_name,e_sal;              //游标是一次取一行数据，自动向后移动，像JDBC resultSet
  while cu_emp%found loop                    //判断是否取到数据
    dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);
    fetch cu_emp into e_no,e_name,e_sal;                 //再取一次
  end loop;
  close cu_emp;
end;


set serverout on;
declare cursor cu_emp is select empno,ename,sal from emp;
e_no emp.empno%type;                        //动态指定类型，后期如果改变字段类型会出错    
e_name emp.ename%type;
e_sal emp.sal%type;
begin
  open cu_emp;
  fetch cu_emp into e_no,e_name,e_sal;
  while cu_emp%found loop
    dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);
    fetch cu_emp into e_no,e_name,e_sal;
  end loop;
  close cu_emp;
end;

set serverout on;
declare cursor cu_emp is select * from emp;            //select语句要改成*，不能用字段
e emp%rowtype;                            //这个比上面的更方便，直接定义一行的参数和类型
begin
  open cu_emp;
  fetch cu_emp into e;
  while cu_emp%found loop
    dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
    fetch cu_emp into e;
  end loop;
  close cu_emp;
end;


set serverout on;
declare cursor cu_emp is select * from emp where sal>2000 and sal<3000;
e emp%rowtype;
begin
  open cu_emp;
  fetch cu_emp into e;
  while cu_emp%found loop
    dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
    fetch cu_emp into e;
  end loop;
  close cu_emp;
end;

//==========================隐式游标--开始=======================================
begin
  if sql%isopen then
     dbms_output.put_line('sql游标已打开');
  else
     dbms_output.put_line('sql游标未打开'); 
  end if;
end;

declare e_count number;
begin
  select count(*) into e_count from emp;
  dbms_output.put_line('游标捕获的记录数：'||sql%rowcount); 
end;



begin
  update emp set ename='sb3' where empno=111;
  if sql%rowcount=1 then
    dbms_output.put_line('已更新');
  else
    dbms_output.put_line('未更新');
  end if;
end;

begin
  update emp set ename='sb3' where empno=111;
  if sql%found then
    dbms_output.put_line('已更新');
  else
    dbms_output.put_line('未更新');
  end if;
end;

//==========================隐式游标--结束=======================================


//==========================动态游标--开始=======================================

//emp表中有job='PRESIDENT' 就打印那一条信息，否则打印emp表中所有的信息

declare type emptype is ref cursor return emp%rowtype;   //强类型动态游标，type emptype 相当于一个游标类
cu_emp emptype;                        //cu_emp 相当于一个游标对象
e_count number;
e emp%rowtype;
begin
  select count(*) into e_count from emp where job='PRESIDENT1';
  if e_count=0 then
    open cu_emp for select * from emp;
  else
    open cu_emp for select * from emp where job='PRESIDENT';
  end if;
  fetch cu_emp into e;
  while cu_emp%found loop
    dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
    fetch cu_emp into e;
  end loop;
  close cu_emp;
end;

//emp表中有job='PRESIDENT' 就打印emp表中的信息，否则打印salgrade表中的信息

declare type customType is ref cursor;            //弱类型动态游标, 用于获取不同表的信息，后面不需要return  *%rowtype;
e_count number;
e emp%rowtype;
s salgrade%rowType;
cType customType;                                       //这是个对象
begin
  select count(*) into e_count from emp where job='PRESIDENT1';
  if e_count=0 then
    open cType for select * from salgrade;
    fetch cType into s;
    while cType%found loop
      dbms_output.put_line('等级：'||s.grade||'，最低薪资：'||s.losal||'，最高薪资：'||s.hisal);
      fetch cType into s;
    end loop;
    close cType;
  else
    open cType for select * from emp where job='PRESIDENT';
    fetch cType into e;
    while cType%found loop
      dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
      fetch cType into e;
    end loop;
    close cType;
  end if;
end;

 

第十章 Oracle触发器

第一节：触发器简介
具备某些条件，由数据库自动执行的一些 DML 操作行为。

第二节：语句触发器
语句触发器针对整个表，作用整个表操作；
语法结构：
Create trigger 触发器名称
Before/after 触发动作, 多个动作用or连接
On 作用对象   ， （一般是表名，视图比较少）
触发器谓词：INSERTING、UPDATING、DELETING

第三节：行触发器
行触发器针对行记录。
语法结构：
Create trigger 触发器名称
Before/after 触发动作
For each row
On 作用对象
触发器内置变量 :old :new

select t.*, t.rowid from T_BOOK t

insert into t_book values(6,'xx7',2);

delete from t_book where id=6;

update t_book set bookname='xx4' where id=4;

--用在权限操作

create trigger tr_book
before insert
on t_book
begin
  if user!='cc' then
    raise_application_error(-20001,'权限不足');
  end if;
end;


create trigger tr_book2
before update or delete
on t_book
begin
  if user!='CC' then
    raise_application_error(-20001,'权限不足');
  end if;
end;

--记录操作日志
create trigger tr_book_log
after insert or update or delete
on t_book
begin
  if updating then
    insert into t_book_log values(user,'update',sysdate);
  else if inserting then
    insert into t_book_log values(user,'insert',sysdate);
  else if deleting then
    insert into t_book_log values(user,'delete',sysdate);
  end if;
  end if;
  end if;
end;

--行触发器，当在t_book中插入一条记录，t_booktype 中的相应记录数量+1
create trigger tr_book_add
after insert
on t_book
for each row
begin
  update t_booktype set num=num+1 where id=:new.typeId;
end;

create trigger tr_book_delete
after delete
on t_book
for each row
begin
  update t_booktype set num=num-1 where id=:old.typeId;
end;

第四节：触发器禁用和开启
禁用触发器： alter trigger 触发器名称 disable
启用触发器： alter trigger 触发器名称 enable

 

第十一章 Oracle函数与存储过程

第一节：Oracle自定义函数
Create function 函数名称 return 返回值类型 as
Begin
...
End 函数名称;

第二节：Oracle存储过程
Create procedure 存储过程名称 as
Begin
...
End 存储过程名称；
In 只进不出
Out 只出不进
In out 可进可出

（右击存储过程，选择debug,可进入调试模式）

create function getBookCount return number as
begin
  declare book_count number;
  begin                    --有新操作语句，用begin,end包住，外面的begin,end是定义函数的
    select count(*) into book_count from t_book;
    return book_count;
  end;
end getBookCount;


set serveroutput on;
begin
  dbms_output.put_line('表t_book有'|| getBookCount() ||'条数据');
end;


create function getTableCount(table_name varchar2) return number as
begin
  declare recore_count number;
  query_sql varchar2(300);
  begin
    query_sql:='select count(*) from ' || table_name;   --不能像上面那样直接写sql语句，不会执行的
    execute immediate query_sql into recore_count;
    return recore_count;
  end;
end getTableCount;


begin
  dbms_output.put_line('表有'|| getTableCount('t_bookType') ||'条数据');
end;

--存储过程用于DML操作

create procedure addBook(bookName in varchar2,typeId in number) as
begin
  declare maxId number;
  begin
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bookName,typeId);
    commit;
  end;
end addBook;

execute addBook('java好东西',1);


create procedure addBook2(bN in varchar2,typeId in number) as
begin
  declare maxId number;
  n number;
  begin
    select count(*) into n from t_book where bookName=bN;
    if(n>0) then
     return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bN,typeId);
    commit;
  end;
end addBook2;

execute addBook2('java好东西33',1);


create procedure addBook3(bN in varchar2,typeId in number,n1 out number,n2 out number) as
begin
  declare maxId number;
  n number;
  begin
    select count(*) into n1 from t_book;
    select count(*) into n from t_book where bookName=bN;
    if(n>0) then
     return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bN,typeId);
    select count(*) into n2 from t_book;
    commit;
  end;
end addBook3;

--有输出参数的就不能用execute 了

declare n1 number;
        n2 number;
begin
  addBook3('喝喝33223',2,n1,n2);
  dbms_output.put_line('n1='||n1);
  dbms_output.put_line('n2='||n2);
end;

第三节：程序包
引入的目的，是为了有效的管理函数和存储过程，当项目模块很多的时候，用程序包管理就很有效了。
语法：
Create or replace package 包名 as
变量名称 1 数据类型 1；
变量名称 2 数据类型 2；
...
...
Function 函数名称 1(参数列表) return 数据类型 1；
Function 函数名称 2(参数列表) return 数据类型 2；
...
...
Procedure 存储过程名称 1(参数列表)；
Procedure 存储过程名称 2(参数列表)；
...
...
End 包名；

--创建包接口
create package pkg_book as
   function getbookcount return number;
   function getTableCount(table_name varchar2) return number;
   procedure addBook(bookName in varchar2,typeId in number);
end pkg_book;

--实现包的接口
create package body pkg_book as

       function getBookCount return number as
          begin
            declare book_count number;
            begin
              select count(*) into book_count from t_book;
              return book_count;
            end;
        end getBookCount;
        
        
        function getTableCount(table_name varchar2) return number as
           begin
              declare recore_count number;
              query_sql varchar2(300);
              begin
                query_sql:='select count(*) from ' || table_name;
                execute immediate query_sql into recore_count;
                return recore_count;
              end;
          end getTableCount;
          
          
          procedure addBook(bookName in varchar2,typeId in number) as
            begin
              declare maxId number;
              begin
                select max(id) into maxId from t_book;
                insert into t_book values(maxId+1,bookName,typeId);
                commit;
              end;
            end addBook;


end pkg_book;



set serveroutput on;
begin
  dbms_output.put_line('表t_book有'|| pkg_book.getBookCount() ||'条数据');
end;

 

第十二章 Oracle用户，角色和权限

第一节：Oracle 用户
Oracle 用户分两种，一种是系统用户 sys（最高权限） system（次高） ；另外一种是普通用户；
视图 dba_users 存储着所有用户信息；
创建用户：
Create user 用户名 identified by 密码 default tablespace 表空间
授予 session 权限：grant create session to TEST;      （刚创建的用户需要授权才能登陆）
锁定和开启帐号：alter user TEST account lock / unlock ;
修改用户密码：alter user TEST identified by 123 ;
删除用户： drop user TEST cascade ; 删除用户，并且把用户下的对象删除，比如表，视图，触发器等。

create user TEST identified by 123456 default tablespace users;

grant create session to TEST;

alter user TEST account lock;

alter user TEST account unlock;

alter user TEST identified by 123;

drop user TEST cascade;

 第二节：Oracle 权限
Oracle 权限分为系统权限和对象权限；
1.系统权限是 Oracle 内置的，与具体对象无关的权限，比如创建表的权限，连接数据库权限；
对象权限就是对具体对象，比如表，视图，触发器等的操作权限；
系统权限视图：system_privilege_map
权限分配视图：dba_sys_privs  （查询分配给用户的权限和传播特性）
回收系统权限 revoke 权限 from 用户

2.对象权限分配
用户表权限视图：dba_tab_privs
给对象授权 grant 权限 on 对象 to 用户 with grant option;
回收权限：revoke 对象权限 on 对象 from 用户；

第三节：Oracle 角色
角色是权限的集合；可以给用户直接分配角色，不需要一个一个分配权限；
语法：
Create role 角色名称；
使用视图 dba_roles 可以查找角色信息；

create user TEST identified by 123456 default tablespace users;

grant create session to TEST;  --系统权限，授权登陆

grant create table to TEST;    --创建表的权限

select * from dba_sys_privs;    --查询分配给用户的权限和传播特性

create user TEST2 identified by 123456 default tablespace users;

grant create session,create table to TEST with admin option;  --赋予test用户权限并具有传播特性，
                                --这样test用户可以把这2个权限赋予其他用户

revoke create session,create table from TEST;           --回收权限

create user TEST2 identified by 123456 default tablespace users;

grant create session to TEST2;

grant create table to TEST2;



grant create session to TEST;

grant create table to TEST;


select * from sys.aa ;

--对象权限分配， 授权（把sys用户的表aa的查询权限给TEST用户）

grant select on AA to TEST;

update sys.AA set name='喝喝';

delete from sys.AA ;

grant all on AA to TEST;    --对表的所有操作都赋给test用户

--传播性

grant select on sys.AA to TEST2;


grant select on AA to TEST with grant option;  --对象权限分配的传播， 和系统权限分配的传播语法有点不同

select * from dba_tab_privs where grantee='TEST'  --查询用户对表有哪些权限


revoke update on AA from TEST;


--角色：
select * from dba_roles;


grant select, update,insert ,delete on AA to role_AA;  --把对AA表的操作权限赋给角色


revoke all on AA from TEST,TEST2;

grant role_AA to TEST;