Oracle数据库简单认识（三）

最新推荐文章于 2025-07-07 13:32:03 发布

原创最新推荐文章于 2025-07-07 13:32:03 发布 · 152 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle #sql

小白入门专栏收录该内容

39 篇文章

订阅专栏

本文介绍了数据库的基础概念，包括视图、索引、PL/SQL语法、存储过程、存储方法及触发器等内容。通过具体示例详细解释了如何使用这些功能来优化查询效率、实现复杂的数据处理逻辑。

1视图

视图就是封装了一条复杂查询的语句

语法

CREATE VIEW 视图名称 AS 子查询

create view dep20 as select * from EMP where DEPTNO=20;

select * from dep20;

2.索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

单列索引

create index sex_index on student(sex);
select * from student where sex = 'F'; #根据索引查找
select * from student where id=2; #全表扫描

复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，

create index sex_age_index on student(sex,age);

删除索引

drop index sex_index;

索引的使用原则：

在大表上建立索引才有意义
在 where 子句后面或者是连接条件上的字段建立索引
表中数据修改频率高时不建议建立索引

3.pl/sql语法

什么是 PL/SQL?

PL/SQL（Procedure Language/SQL）

PLSQL 是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循

环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

pl/sql 程序语法

declare
说明部分（变量说明，游标申明，例外说明〕
begin
语句序列（DML 语句〕…
exception
例外处理语句
End;

3.1变量的声明

声明类型

    a number;
   b char;
   c constant boolean:=true;

引用类型

name emp.ename%type;

记录类型

p emp%rowtype;

注意：

声明的类型只有被into 赋值之后才能使用，
constant不能直接输出

示例

declare
   a number;
   b varchar2(30);
   c constant boolean:=true;
   name emp.ename%type;
begin
select e.ENAME into name from EMP e where e.EMPNO = 7900;
DBMS_OUTPUT.PUT_LINE(name);

select e.ENAME into b from EMP e where e.EMPNO = 7900;
DBMS_OUTPUT.PUT_LINE('b'||b);

if c=true then DBMS_OUTPUT.PUT_LINE('c');
end if ;
end;

declare
   p emp%rowtype;
begin
   select * into p from EMP a where a.EMPNO = 7900;
   DBMS_OUTPUT.PUT_LINE(p.ENAME||p.SAL);
end;

3.2 if else分支语句

declare
   sal_v emp.SAL%type;
begin
   select e.SAL into sal_v from EMP e where e.EMPNO = 7566;
   DBMS_OUTPUT.PUT_LINE(sal_v);
   if sal_v< 2000 then
   DBMS_OUTPUT.PUT_LINE('低收入');
   elsif sal_v>=2000 and sal_v<=3000 then
   DBMS_OUTPUT.PUT_LINE('中等收入');
   else
   DBMS_OUTPUT.PUT_LINE('高收入');
   end if;

end;

3.3游标 Cursor

语法

CURSOR 游标名 [ (参数名数据类型,参数名数据类型,...)] IS SELECT 语句;
cursor pc is select * from emp;

游标的使用步骤：

打开游标： open c1; (打开游标执行查询)
取一行游标的值：fetch c1 into pjob; (取一行到变量中)
关闭游标： close c1;(关闭游标释放资源)
游标的结束方式 exit when c1%notfound

遍历

declare
   cursor pc is select * from emp; -- 创建游标
   pemp emp%rowtype;    -- 声明pemp
begin
   open pc; --打开游标
   loop -- 开始循环
   fetch pc into pemp; -- 获取游标中的标量
   exit when pc%notfound ; --- 设置退出条件
   DBMS_OUTPUT.PUT_LINE(pemp.ENAME||pemp.SAL); --- 输出
   end loop;
   close pc; ---关闭游标
end;

为不同收入的员工加薪

declare
   cursor pc is select * from emp;
   pemp emp%rowtype;
   addsal emp.sal%type ;
begin
   open pc;
   loop
   fetch pc into pemp;
   exit when pc%notfound ;
-- DBMS_OUTPUT.PUT_LINE(pemp.ENAME||pemp.SAL);
   if pemp.SAL< 2000 then
   DBMS_OUTPUT.PUT_LINE('低收入');
   addsal := 200;
   elsif pemp.SAL>=2000 and pemp.SAL<=3000 then
   DBMS_OUTPUT.PUT_LINE('中等收入');
   addsal := 400;
   else
   DBMS_OUTPUT.PUT_LINE('高收入');
   addsal := 6000;
   end if;
   -- 更新工资
   update emp set sal = sal + addsal where emp.empno = pemp.empno;
   end loop;
end;

4.存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经

编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来

执行它。

简单理解：存储过程就是数据库中写好的脚本，我们可以通过sql语句调用存储过程，完成调用

语法

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
IS
beginI
PLSQL 子程序体；
End;

无参存储过程调用

--- 声明
create or replace procedure hell is
begin
DBMS_OUTPUT.PUT_LINE('hells');
end;

---调用
begin
hell();
end;

有参存储过程调用

create or replace procedure say( str in varchar2) is

begin
DBMS_OUTPUT.PUT_LINE(str);
end;

begin
say('12312312');
end;

根据EMPNO为员工加薪

create or replace procedure addsal2( salnum in number,empid in number) is
curentsal emp.sal%type ;
begin
   DBMS_OUTPUT.PUT_LINE(salnum);

   select SAL into curentsal from EMP where EMPNO = empid;
   DBMS_OUTPUT.PUT_LINE('当前薪水'||curentsal);

   update emp set sal = sal + salnum where empid = emp.EMPNO;
   commit ;
end;
--- 调用
begin
   addsal2(100,7369);
end;

5.存储方法

存储方法和存储过程类，只是增加了返回值

语法

create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is
结果变量数据类型;
begin

return(结果变量);
end 函数名;

create or replace function income(empid in number) return number is

account number;
begin
   select ( SAL*12 + nvl(COMM,0)) into account from EMP where EMPNO = empid;
   DBMS_OUTPUT.PUT_LINE(account);
   return account;
end;

begin
   DBMS_OUTPUT.PUT_LINE('调用方法:'||income(7369));
end;

存储过程和存储函数的区别？

一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。

但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数，在过程和函数中实

现返回多个值

6触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句

(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。

作用

 数据确认

 实施复杂的安全性检查

 做审计，跟踪表上所做的数据操作等

 数据的备份和同步

语法

CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL 块
End 触发器名

更新员工触发器

create or replace trigger test_triger2 ---创建触发器
after update on EMP --- 监控的动作
declare

begin
DBMS_OUTPUT.PUT_LINE('更新员工操作:'); ---发生的行为
end test_triger2;

校验员工工资

create or replace trigger sal_triger
   before update of sal on EMP for each row
declare

begin
   DBMS_OUTPUT.PUT_LINE('更新员工操作:');
   if :OLD.sal >= :NEW.sal then ---通过 :OLD.sal :NEW.sal获取前后的值
   raise_application_error(-20002,'涨工资前必须高于涨工资后'); -- 抛出异常
   end if;
end;

---测试
update EMP set SAL = SAL -1 where EMP.EMPNO=7369;