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;