oracle数据库的plsql

oracle数据库高级

PL/SQL

什么是PL/SQL

PL/SQL是Oracle数据库对SQL语句的扩展，增加了编程语言的特点.
数据操作和查询语句被包含在PL/SQL代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算

PL/SQL的优点

使一组语句功能形成模块化程序开发
使用过程性语言控制程序结构
可以对程序中的错误进行处理
集成在数据库中
有助于提高程序性能

PL/SQL块的基本结构

DECLARE – 可选部分
变量、常量以及类型等
BEGIN – 必要部分
SQL语句
PL/SQL语句
EXCEPTION – 可选部分
程序出现异常时，捕捉异常并处理异常
END; – 必要部分

pl/sql中执行的命令

在PL/SQL程序块中可以使用各种SQL命令，但是使用的方法根据命令不同也各不相同
使用SELECT 命令，可以从数据库中取出单行数据
使用DML命令，修改数据库中的行
使用COMMIT 或ROLLBACK 命令控制事务
通过EXECUTE IMMEDIATE，执行DDL和DCL语句

存储过程

过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。用于在数据库中完成特定的操作或者任务
语法

create or replace PROCEDURE name
[(parameter,…)]
IS
–声明变量
BEGIN
pl/sql_block;
EXCEPTION
…………….
END;
parameter的语法如下：
parameter_name [IN | OUT | IN OUT] datatype
[{:= | DEFAULT} expr]

例如：
输入工号给该人的工资增长1000

create or replace procedure proc_emp(eno in number)
    as
    begin
    update emp set sal=sal+1000 where empno=eno;
    end;

输入工号给该人增长1000工资再返回多少工资

create or replace procedure proc_emp02(eno in number,money out number)
    as
    begin
        update emp set sal=sal+1000 where empno=eno;
        select sal into money from emp where empno=eno;
    end;

在oracle中调用存储过程
第一种

call proc01(7654);

第二种：在plsql块中调用

SQL> begin
	  proc01(7654);
	  end;
	 /

函数

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。
而在函数体内必须包含return语句返回的数据。
语法结构

create or replace FUNCTION funname [(parameter,…)]
RETURN datatype
IS
begin
pl/sql_block;
end funname ;

例如：创建一个函数，传入员工编号，返回员工姓名

create function fun_emp01(eno number)
    return VARCHAR2
    is
    ename1 varchar2(16);
    begin
        select ename into ename1 from emp where empno =  eno;
        return ename1;
    end;

select fun_emp01(7369) from emp；

视图

视图是从一个或几个基本表中导出的表，是从现有基本表中抽取若干子集组成用户的“专用表”。
区别:

1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，
视图只是逻辑概念的存在，表可以及时四对它进行修改，
但视图只能有创建的语句来修改
5、表是内模式，试图是外模式
6、视图是查看数据表的一种方法，
可以查询数据表中某些字段构成的数据，
只是一些SQL语句的集合。从安全的角度说，
视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，
是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。

联系

视图（view）是在基本表之上建立的表，它的结构（
即所定义的列）和内容（即所有数据行）都来自基本表，
它依据基本表存在而存在。一个视图可以对应一个基本表，
也可以对应多个基本表。
视图是基本表的抽象和在逻辑意义上建立的新关系

语法

create [ or replace ] [ force ] view [schema.]view_name
[ (column1,column2,…) ]
as
select …
[ with check option ] [ constraint constraint_name ]
[ with read only ];

1、 or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图
2、 force: “强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
3、 column1,column2,…：视图的列名, 列名的个数必须与select查询中列的个数相同; 如果select查询包含函数或表达式, 则必须为其定义列名.此时, 既可以用column1, column2指定列名, 也可以在select查询中指定列名.
4、 with check option: 指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行"检查”,要求增删改操作的数据, 必须是select查询所能查询到的数据,否则不允许操作并返回错误提示. 默认情况下, 在增删改之前"并不会检查"这些行是否能被select查询检索到.
5、 with read only：创建的视图只能用于查询数据, 而不能用于更改数据.

例如：
创建一个视图 展示员工编号、姓名、总收入 只查询基本工资>2000

create or replace view v_emp(编号,姓名,工资)
    as 
        select empno,ename,sal+nvl(comm,0) as 总收入 from emp where sal>2000
    with read only;

select * from v_emp;
delete from v_emp;--删除不了

触发器

create or replace trigger t_after_table --create or replace trigger 触发器名称
after insert —时间 after/before 事件 insert or update or delete
on student —作用的表 on tablename
FOR EACH ROW – 指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器
declare --trigger 的主题
begin
insert into student_state(SSID,Ssstate) values(:NEW.SID,:NEW.SID);
end;

例如：
向表中新增数据时，自动备份到emp+back表中

create table emp_back as select * from emp;

create or replace trigger trg_emp
after insert
on emp
for each row
begin
    insert into emp_back(empno,ename,sal) values(:NEW.empno,:NEW.ename,:NEW.sal);
end;

insert into emp(empno,ename,sal,deptno) values(6789,'张三',1200,10)

注释

before和after：指在事件发生之前或之后激活触发器。
instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。
referencing：指定新行（即将更新）和旧行（更新前）的其他名称，默认为new和old。
table_or_view_name：指要创建触发器的表或视图的名称。
for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器。
when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。
declare—end：是一个标准的PL/SQL块。

分页查询

查询前五行数据

select * from emp where rownum <=5;

查询5到10

select * from (select emp.* ,rownum rn from emp)where rn >=5 and rn <=10;

查询最后5行

select * from (select emp.* from emp order by rownum desc) where rownum<=5;

rownum内置属性

其他函数

NVL 函数

NVL(expr1,expr2)将空值转换为替换的值
支持多种数据格式可以是日期,字符,数字
NVL的两个参数数据类型必须匹配 ,否则出错。

select nvl(comm,'0') from emp;

NVL2函数

NVL2(expr0,expr1,expr2)
如果expr0不为Null,返回expr1, 为Null,返回expr2.
expr0可以为任何数据类型

NULLIF函数

NULLIF(expr1,expr2)
比较两个表达式，如果相等返回空值，如果不等返回第一个表达式。