oracle序列和索引的使用以及plsql数据库编程

本是古典何须时尚 2023-06-20 14:56 25阅读 0赞

### 1.创建视图 ###

create or replace view view\_emp  
as  
select e.ename,d.dname,e.sal,s.grade  
from emp e,dept d,salgrade s  
where e.deptno=d.deptno and e.sal between s.losal and s.hisal

### 2.创建序列 ###

create sequence seq\_dept  
increment by 1 – 步长  
start with 1 – 开始位置  
nomaxvalue --最大值  
nocycle – 是否循环 到达最大之后是否重新开始标号  
cache 10 – 缓存 定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。（大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列）

#### 序列的使用 ####

insert into emp(empno,ename) values(seq\_dept.nextval,‘小明’)

#### 查询当前序列 ####

select seq\_dept.currval from dual

#### 查询下一序列 序列此时也会向后移动一步长 ####

select seq\_dept.nextval from dual

#### 修改序列 ####

– 不允许修改序列的开始值  
alter sequence seq\_dept  
increment by 2

### 3.创建索引 ###

create index index\_ename on emp(ename)  
经常作为查询条件的列或者表连接需要用到的列可以创建索引，提高效率

### 4.plsql编程 ###

#### （1）语句块 ####

声明部分（用declare开头）  
执行部分（以 begin开头）其中执行部分是必须的，其他两个部分可选  
异常处理部分（以exception开头）  
结束：end  
– 声明变量  
==declare ==  
smithsal number(8,2); 变量名 数据类型:=值 （可以不赋值）  
–执行部分  
begin  
smithsal:=3000;  
select sal into smithsal from emp where ename=‘SMITH’;  
dbms\_output.put\_line(smithsal);  
– 结束  
end;

#### （2）存储过程 ####

**创建存储过程**  
–计算某个部门的总薪资  
create or replace procedure pro\_sal(dno number)  
is  
begin  
declare totalsal emp.sal%type;  
begin  
select sum(sal) into totalsal from emp where deptno=dno;  
dbms\_output.put\_line(‘总月薪为：’||totalsal);  
end;  
end;  
**调用存储过程**  
declare  
deptno emp.deptno%type;  
begin  
deptno:=30;  
pro\_sal(deptno);  
end;

#### （3）函数 ####

**创建函数**

create or replace function fun\_sal(dno number)  
return number  
is  
begin  
declare totalsal emp.sal%type;  
begin  
select sum(sal) into totalsal from emp where deptno=dno;  
return totalsal;  
end;  
end;  
**调用函数**  
declare  
dno emp.deptno%type:=30;  
totalsal emp.sal%type;  
begin  
totalsal:=fun\_sal(dno);  
dbms\_output.put\_line(totalsal);  
end;

#### （4）游标的使用 ####

create or replace function fun\_addsal(dno number)  
return number  
is  
begin  
declare  
cursor cur\_emp is select \* from emp where deptno=dno;  
– 声明 cur\_emp游标类型的一个变量  
c cur\_emp%rowtype;  
begin  
– 遍历游标来进行加薪操作 使用for循环会自动打开和关闭游标  
for **c** in **cur\_emp** loop  
– c存储当前行的各列数据  
if c.sal<=1500 then update emp set sal=sal+500 where empno=c.empno;  
end if;  
if c.sal>1500 and c.sal<3000 then update emp set sal=sal+300 where empno=c.empno;  
end if;  
if c.sal>=3000 then update emp set sal=sal+100 where empno=c.empno;  
end if;  
end loop;  
return 1;  
end;  
end;  
**调用函数**  
declare  
dno emp.deptno%type:=30;  
rs number(2,0); – 用来接收函数返回值  
begin  
rs:=fun\_addsal(dno);  
end;