Oracle之对象

2023-01-25 14:44:23

视图

视图封装了复杂查询语句，可以看做是表的部分内容的映射。

视图的存在可以直观感受数据的关系，并且对一些敏感源数据进行隐藏。

虽然视图是原始表的内容映射，但是不建议直接对视图进行DML操作

创建一张视图：

create or replace view Name(自定义字段名) //or replace 保证创建同名视图会自动删除原视图
as
select ...
[with check opinion] //允许在不影响视图根本规则的情况下适当修改数据
[with read only]  //只读，不允许修改

查询视图与查询一般表没什么区别

select ... from viewName;

索引

可以快速定位并减少磁盘IO，与物理表独立存在，需要定期维护

唯一性索引非唯一索引

定义约束或 create index

原则：维护索引列的唯一性+快速访问；查询量大的表；where查询频繁的字段；非重复数据占比大的字段(如pk字段)；复合索引的主列应是使用频繁的字段；数据量多的表(大于5M)

查询结果行占比5%以下，用索引最好

多列查询的话，复合索引优于单列索引，因为只需查询索引块

调优：索引与表不要放在同一个表空间，否则容易产生IO冲突；而且不放在同一块硬盘，还可以增加并行度

tip

一个查询可以同时用到多个索引，先访问一个索引得到结果1，此基础*问第二个索引，且效率是高于全表扫描的，但是低于复合索引

sql优化的实质就是在结果正确的前提下。用优化器认识的语句充分使用索引。尽量访问最少的数据块，减少磁盘IO，节省资源

in 不能使用索引

!= 不能使用索引

like 'xxx' 匹配模式不能通配符开头，这样就不能使用索引

索引列不要参与计算和函数，包括隐式类型转换

>=优于>，因为等于可以先定位

sga共享池

表连接条件放在最前，过滤大的条件放在最后

packages，首次调用将整个包导入内存

cached sequences 生成序列

varchar 替代 char

序列

创建序列

create sequence Name
[
start with num
increment by step
order
cache num			//预先存取一些序列值，保证存取速度快，但是如果数据库挂掉，序列就会紊乱，所以一般用nocache
nocycle  			//nocycle保证序列用于不同表时id唯一
]

//不缓存，序列值按3循环
create sequence a
start with 1
increment by 1
cycle 3
nocache;

alter sequence Name increment by num;//修改step

取值

select name.nextval,name.currval from dual;//先有nextval，才能用currval

同义词

其实就是dual，是sys用户下的一张表，可以看做是杂货堆

PLSQL块

类似编程的代码块，有特定的格式，可以批量执行一些固定操作

declare
	var type;
begin
	//content
	...
	exception
		when e then
		...
end

set serveroutput on //设置输出显示

声明变量

var type;

类型可以直接写：eno number;

也可以直接映射某表的字段类型：eno emp.empno%type;

变量也可以是整条记录(类似Javabean)，但是类型就要映射了：dept dept%rowtype;

语法

en:=&no // 获取输入

Loop循环

// do while
loop
	...
	exit when xxx;
	...
end loop;

// while
while(...) loop
	...
end loop;

//for1
for xx in xxxx loop
	...
end loop;
//for2
for xx..xxxx loop
	...
end loop;

if选择

if 条件 then
	...
end if

if 条件 then
	...
else
	...
end if

if 条件 then
	...
elif 条件 then
	...
else
	...
end if

goto跳转

游标

通过PLSQL创建，主要对查询语句结果进行遍历，类似于java集合的迭代器。

存在于内存中，并非数据库对象

声明——>打开——>遍历——>关闭

declare
	cursor Name is select ...;
	//一般游标一定伴随一个记录对象
	xxx table%rowtype;
	length number;
begin
	length:=Name%rowcount;//取游标长度
	for xxx in Name loop
		...
	end loop;
end;

//使用fetch抓取游标
declare
	...
begin
	open Name;//先要开启游标
	fetch Name into xxx;//从游标取值
	while(Nmme%found) loop//查看游标是否取完不存在?
		....
		fetch Name into xxx;//循环取值
	end loop
end

//loop方式取值
declare
	...
begin
	open Name;
	loop
		fetch Name into xxx;
		exit when Name%notfound;
		...
	end loop
end

//注：在打开游标前最好先判断游标是否已经打开
if Name%isopen then
	null;
else
	open Name;
end if;

//以上游标属于静态游标，声明即确定内容；也可以设定动态游标
declare
	TYPE typename is ref cursor [return xxx%xxx];//自定义一个游标变量类型，允许指定游标内容类型
    var typename;//声明游标变量
    xxx xxx%xxx;//声明记录变量
    str varchar(200);//建立一个语句变量
begin
	...
end

//example
declare
	TYPE stus_cursor is ref cursor return student%rowtype;
	stus stu_cursor;
	stu student%rowtype;
	str varcaar(200);
begin
	str:='select * from student where sid between :x and :y';
	open stus for str using 20,23;
	或者
	open stus for select * from student where sid between 20 and 23;
	
	fetch stus into stu;
	while(stus%found) loop
		fetch stus into stu;
		...
	end loop
end

//动态游标如果不想自定义也可以使用系统游标
declare
	stus sys_refcursor;//自定义类型建立变量一步到位
	...
begin
	...
end

(自定义)函数

数据库的函数是一个有返回值的过程

其格式也是类似PLSQL

create or replace function funName([param xxx.xxx%xxx])  //参数定义一如前面PLSQL声明变量一致
return type as var type
begin
	...
end

//example:根据雇员的编号查询出雇员的年薪
CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) 
RETURN NUMBER AS rsal NUMBER ;    //这不是特指定义返回值变量，普通变量也可以的，只要最后返回的变量在这里定义即可
BEGIN
SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;
RETURN rsal ;                     //返回值一定要在前面定义过
END ;

?返回多个变量

自定义函数的调用与系统函数一致，直接调用即可

前面提到游标对象可以迭代获取一组数据的记录

这里可以将游标与函数结合，由函数接收参数，由动态游标取值，甚至直接返回一个游标(return sys_refcursor as Name sys_refcursor)

存储过程

存储过程相当于是函数的反面，功能相似，只是存储过程没有返回值，而函数要有返回值

语法也及其相似

create or replace procedure Name([param xxx%xxx])
as
	//声明变量
begin
	...
end

param参数有三种类型：in，out，in out
in 就是简简单单的输入参数

关键就是这个out，前面说过存储过程和函数的区别就在于存储过程没有返回值；但是参数加上out ，其实就是隐式的返回值

out 参数就是外界传入一个“返回值地址”变量，由存储过程操作并将结果传入该地址，外界调用存储过程后该变量结果事实上已经产生变化；

in out 参数就是参数既当传入值，有作为返回值。只是结果存储过程一顿操作，原始值被修改。

//example1
Create or replace procedure proc_is_exist(na emp.ename%type,is_exist out number)
as
   name varchar(22);
begin
   select count(*) into is_exist from emp where ename = na;
end;
declare
   i number;
begin
   proc_is_exist('sdsdsd',i);
   dbms_output.put_line(i);     //外界将参数i传入过程，等待过程操作得到结果赋值。最后i结果为count(*)
end;

//example2
CREATE OR REPLACE PROCEDURE myproc(dno IN OUT dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)
AS
cou NUMBER ;
BEGIN
    SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;
    IF cou=0 THEN
        INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;
        DBMS_OUTPUT.put_line('部门插入成功！') ;
        dno := 1 ;
    ELSE
        DBMS_OUTPUT.put_line('部门已存在，无法插入！') ;
        dno := -1 ;
    END IF ;
END ;

DECLARE
	deptno dept.deptno%TYPE ;
BEGIN 
    deptno := 12 ;
    myproc(deptno,'开发','南京') ;
    DBMS_OUTPUT.put_line(deptno) ;  //首先以传入的deptno查看结果，根据查看结果将1、-1覆盖原变量作为返回值，外界接收的deptno值只能是1，-1
END ;

//删除存储过程
drop procedure Name;

触发器

create or replace triggle Name
before/after
insert/delete/update on tableName //注：触发器的执行代码中不允许提交关于查询基表的语句?
[for each row]  //加for each row 是行级触发器，针对每行记录动作触发响应
[when(条件)]  //限制触发器，当满足条件触发器启用
declare
	...
begin
	...
end

码农公寓

视图

索引

tip

序列

同义词