Oracle_视图_索引_plsql_定义变量_if/elsif/else_游标_存储过程_存储函数(7)

最新推荐文章于 2022-05-13 11:23:18 发布

原创最新推荐文章于 2022-05-13 11:23:18 发布 · 431 阅读

1 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了Oracle数据库中的视图、索引及其重要性，包括视图的概念、创建及作用，以及索引的类型和对查询效率的影响。进一步探讨了PL/SQL中的变量定义、条件判断、循环结构和游标的使用，最后讲解了存储过程和存储函数的创建与调用，强调它们在处理复杂业务逻辑时的作用。

一.视图

1.概念：提供一个查询的窗口，所有数据来自原表
2.创建试图必须要有dba权限
3.通过查询语句创建表格
-- 查询语句创建表格,创建来自scott用户的emp表格
create table emp as select * from scott.emp;
select * from emp;

-- 创建视图（数据只是创建在）
create view v_emp as select ename,job from emp;
select * from v_emp;

-- 修改视图（不推荐）
update v_emp set job='CLERK' where ename='ALLEN';
commit;
-- 创建只读视图
create view v_emp1 as select ename,job from emp with read only;
4.视图的作用：

视图可以屏蔽掉一些敏感字段。

视图可以保证总部和分部数据及时统一。

二.索引

1.概念：索引就是在表的列上构建一个二叉树，大幅度提高查询效率的目的，但是索引会影响增删改的效率。
2.单列索引
---创建单列索引
create index idx_ename on emp(ename);
注意：单行函数，模糊查询，都会影响索引的触发。
3.复合索引
---创建复合索引
create index idx_enamejob on emp(ename, job);
---复合索引中第一列为优先检索列
---如果要触发复合索引，必须包含有优先检索列中的原始值。
select * from emp where ename='SCOTT' and job='xx';---触发复合索引
select * from emp where ename='SCOTT' or job='xx';---不触发索引
select * from emp where ename='SCOTT';---触发单列索引。

三.plsql编程语言定义变量（数值、字符、引用、记录型），赋值与打印

declare 
-- 定义数值型变量，不能使用int，int只能定义整数
  i number(2) :=3;        
  -- 定义字符型字段
  s varchar2(10):='得到'; 
  --定义引用类型
  ena emp.ename%type; 
  -- 定义记录型变量
  emprow emp%rowtype;  
begin
  dbms_output.put_line(i);-- 输出
  dbms_output.put_line(s);
  -- 引用类型赋值
  select ename into ena from emp where empno=7788;
  dbms_output.put_line(ena);
  --记录类型赋值
  select * into emprow from emp where empno = 7788; 
  -- 记录类型的输出格式
  dbms_output.put_line(emprow.ename);  
end;

定义输入
- ```
declare
i number(3) := &11;
begin
end;
```

四.if/elsif/elsedec

-- if/elsif/else
declare
    -- 定义输入
   i number(3) := &11; 
begin
  if i<18 then
    dbms_output.put_line('未成年');
  elsif i<40 then
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

五.循环

1.while循环

-- while循环
declare
   i number(2):=1;
begin
  while i<11 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;

2.exit循环（用的比较多）

declare
  i number(2):=1;
begin
  loop
    exit when i>10;
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;

3.for循环

-- for循环不需要提前订好变量
declare
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

六.游标（类似java集合，可存放多个记录）

1.游标遍历记录

-- 游标
declare
   cursor c2 is select * from emp;
   
   emprow emp%rowtype;
begin
  -- 打开游标
  open c2; 
    loop
    -- 提取游标记录放到记录变量  
    fetch c2 into emprow;
    -- 判断游标是否为空
    exit when c2%notfound;
    dbms_output.put_line(emprow.ename); 
     end loop;
  -- 关闭游标
  close c2;
end;

2.游标遍历使用修改语句

-- 使用游标修改表格中的数据
declare
   -- 定义游标，游标中定义引用（类似于方法参数，后面会使用）
   cursor c2(eno emp.deptno%type) 
   -- eno：如同方法参数值可以引用
   is select empno from emp where deptno = eno;
   
   -- 接受游标值字段
   en emp.empno%type;
begin
  -- 打开游标，给其中参数赋值
  open c2(10);
  loop
    -- 提取游标值
    fetch c2 into en;
    -- 判断游标是否为空
    exit when c2%notfound;
    --修改工资
    update emp set sal = sal+100 where empno = en;
    -- 提交
    commit;
  end loop;
  -- 关闭游标
  close c2;
end;

七.存储过程

1.概念：
就是提前编译好的一段pl/sql语言，放置在数据库端
一般用来处理固定步骤的业务。
2.创建语法
参数默认为in。
3.创建过程与调用
-- or replace 表示在已有相同的过程上还可以在对其修改。即重新覆盖创建
create or replace procedure p1(eno emp.empno%type) 
is
begin
  update emp set sal=sal+100 where empno = eno;
  commit;
end;

-- 调用存储过程

declare

begin
    p1(7788);
end;
此为正常创建的结果。

此为过程中sql语句不正确创建的错误过程

八.存储函数

1.定义

2.创建存储函数和调用

----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度
create or replace function f_yearsal(eno in emp.empno%type) return number
is
  s number(10);     
begin
  select sal*12+nvl(comm, 0) into s from emp where empno = eno;
  return s;
end;

----调用
----存储函数在调用的时候，返回值需要接收。
declare
  s number(10); 
begin
  s := f_yearsal(7788);
  dbms_output.put_line(s);
end;