【oracle】PL/SQL编程 —— 「Procedural Language Extension to SQL」

1、PL/SQL语言基础

PL：Procedural Language，过程化语言。
SQL：Structured Quevy Language，结构化查询语言。

面向过程的三大特征：顺序/选择/循环

基本概念： Oracle数据库系统提供的扩展SQL语言。

特色：
提高应用程序的运行性能
模块化的设计思想（结构化）
采用了过程性语言的控制结构
处理运行期错误

开发工具：SQLPLUS PL/SQL developer

开发内容：块（过程、函数、触发器、包）

PL/SQL与SQL的不同

2、PL/SQL的基本结构

块是PL/SQL程序中最基本的结构。

分类：命名块和匿名块

PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成。

DECLARE --标记声明部分
…… --此处用来定义常量、变量、类型和游标等

BEGIN --标记程序体部分开始
…… --此处用来编写各种PL/SQL语句、函数和存储过程

EXCEPTION --标记异常处理部分开始
…… --此处用来编写异常处理代码

END; --标记程序体部分结束

注：命名块要比匿名块更好一些，但是匿名块相对于来说是更简单的。

说明：
declare：声明部分，如果没有变量用于说明的话，declare可以不用写。

begin：标记程序体部分开始，begin中不能用于声明变量。

exception：标记异常处理部分开始，没有异常可以不用写。

end：标记程序体部分结束，必须写！

最简单的PL/SQL语句块

begin
 insert into t values(2);
end;

3、PL/SQL编码规范

1. 语句可以写在多行
2. 语句中的关键字、字段名称等，用空格分隔
3. 每条语句必须以分号结束，包括PL/SQL结束部分的END关键字后面也需要分号
4. 标识符需要遵循相应的命名规定

注意：begin、declare、exception后面无分号，而end后面一定有分号的。

4、变量声明

语法：变量名 数据类型		　
例： v_sal number;

命名规则：
	变量必须以字母开头
	变量名可以包含数字、下划线、#、$等
	不能包含减号和空格
	变量名长度1－30（见名知意）
	大小写不区分
	不能是系统关键字

变量名可以包含数字、下划线、#、$，其余的都不可以。

下面的变量合法吗？

v_student_id
符合

v_last_name
符合

V_LAST_NAME
符合,但是不区分大小写，和上面的变量名不能同时出现

apt_#
符合

X+Y
不符合,因为出现了加号

1st_year
不符合，因为不能以数字开头命名

student id
不符合，因为不能出现空格

fr&ast
不符合，因为不能出现&符号

编程风格：v_   c_   _custor
v_ 一般表示的是变量
c_ 一般表示的是常量
_custor 一般表示的是游标
保留字一般大写，表名、列名一般小写
这只是一种编程的格式，但并不是一定的，所以不一样要这样命名。

5、常量

加一个CONSTANT

格式：constant_name CONSTANT data_type:=值或表达式

例：pi CONSTANT number:=3.14;

常量必须在定义的时候赋值！

6、赋值语句

赋值符号 :=

设定默认值：default

【例】在程序的运行过程中，对变量进行赋值操作：

DECLARE
    str VARCHAR2(20) default 'java'; 
    username varchar2(10):='admin';
BEGIN
    str:='hello world';
END;

7、注释语句

单行 －－

多行 /*   */

8、程序的执行和输出

语句的结束标志：分号;

块结束标志：点号. 可以省略

块的执行：斜杠/

输出必须声明SET ServerOutput ON

【例】输出hello world：

SET ServerOutput ON
DECLARE
    str VARCHAR2(50) := 'hello world';
BEGIN
    dbms_output.put_line(str);
END;
.
/

从上面的代码块中可以看出
end;之后语句块并没有结束
.用于结束语句块
想要把结果输出来还是需要/

【例】向表中插入语句

begin
	insert into dept values(50,'k','w');
end;
.
/

结果提示：SQL语句已成功完成

验证：select * from dept;

9、替代变量（输入变量）

输入使用替代变量:&变量名
替代变量的作用：脚本功能通用性增强

练习：求圆的面积。Pi为常量，半径可以输入

declare
    pi constant number(3,2):=3.14;
    r number(5,2):=&a;
    area number(8,2);
begin
    area:=pi*r*r;
    dbms_output.put_line('圆的面积是：'||area);
end;
.
/

注意：
在 dbms_output.put_line(‘圆的面积是：’||area);输出语句中
字符串和变量或者常量之间需要使用  ||  进行隔开

当/执行完之后，再次/可以再次执行程序进行输入

10、课堂练习

1. 计算某个数字的平方值，使用替代变量来提供这个数字的值。然后在屏幕上显示处理结果。

set serveroutput on
declare
    r number(5,2):=&a;
    f number(8,2);
begin
    f:=r*r;
    dbms_output.put_line('数字为'||r||'的平方是：'||f);
end;
.
/

有人可能想问输出语句dbms_output.put_line(‘数字为’||r||‘的平方是：’||f);中的r能不能使用&。

答：不能，因为再次使用&a相当于再一次执行输入操作，所以要使用r

2. 根据商品促销策略，本月将所有商品进行打折销售，折扣为95%，试计算价格为2350元的商品，其打折后的价格为多少

set serveroutput on
declare
    r number(5,2):=&a;
    f number(8,2);
    zk constant number(3,2):=0.95;
begin
    f:=r*zk;
    dbms_output.put_line('价格为'||r||'打完折扣之后的价格是：'||f);
end;
.
/

注意：我们将折扣95%设置为了一个常量，而不是在begin语句中的运算语句中直接乘以0.95，因为折扣价格是固定不变的，所以使用常量是比较好的。

3. 基于当天的日期计算当日是星期几。

set serveroutput on
declare
    v_day varchar2(10);
begin
    v_day:=to_char(sysdate,'day');
    dbms_output.put_line('今天是：'|| v_day);
end;

to_char()：类型转换函数
我们这个方法使用了to_char()函数，用于将结果以自己定义的格式进行输出。
其中，sysdate表示当前日期。
day 表示的是日期，单独地把日期的形式提出来。

set serveroutput on
declare
    v_date date:=sysdate;
    v_day varchar2(10);
begin
    v_day:=to_char(v_date,'day');
    dbms_output.put_line('今天是：'|| v_day);
end;

上面这两种输出的方式都一样，只是前面的这一种更加简洁。

11、基本数据类型

1．数值类型
2．字符类型
3．日期类型
4．布尔类型

布尔类型也只有一种——即BOOLEAN，主要用于程序的流程控制和业务逻辑判断，其变量值可以是TRUE、FALSE或NULL中的一种。

12、%type类型变量

例：v_name emp.ename%type;

利用一个已经声明过的变量或者数据表的列来定义变量

新的变量类型与它所参照的类型完全相同，并且保持同步。

当表中字段的类型和长度发生改变时，该PL/SQL块是不需要修改的

在oracle sql developer中怎么运行语句

点开 查看 选项卡，选择 dbms输出 ， 点击 + 符号进行连接即可，成功之后就可以运行PL/SQL语句体，快捷键是F5

%type的用法：

declare
    v_aa number(5,2);
    v_bb v_aa%type;
begin
    v_aa:=20;
    v_bb:=90;
    dbms_output.put_line(v_aa || '       ' || v_bb);
end;

v_aa%type 的意思就是v_bb要定义的数据类型要和已经定义好了的变量v_aa的数据类型同步，当v_aa的数据类型修改之后，不用修改v_bb的数据类型，因为已经和v_aa的数据类型同步了，故自动转换成v_aa的数据类型。

13、复合变量%rowtype

%rowtype
多个数据类型的组合
该类型返回的是一个记录类型，其数据类型和表的数据结构相一致
保存从数据表中查询到的一行记录
例：myemp emp%rowtype;

%rowtype该类型返回的是一个记录类型，保存从数据表中查询到的一行记录

例：

declare 
	myemp emp%rowtype;
begin
	select * into myemp from emp where empno = 7369;
	dbms_output.put_line(myemp.ename || '    ' || myemp.sal);
end;

说明：

declare
	myrecord emp%type;
	emp_record myrecord%rowtype;
begin

上面这种格式也是可以的
emp_record myrecord%rowtype要求myrecord已经是声明好的。

14、复合变量－－记录类型（is record）

语法：

TYPE type_name IS RECORD (field_name field_type,….);
Identifier type_name;
TYPE emp_record_type IS RECORD(name emp.ename%type,sal number(5,1),job emp.job%type);
myemp emp_record_type;

举例：

declare
    type emp_salcount is record(avgsal number(6,2),vcount number(3));
    emp_record emp_salcount;
    v_no emp.deptno%type:=&no;
begin 
    select avg(sal),count(empno) into emp_record from emp where deptno=v_no;
    dbms_output.put_line(v_no || '部门的平均薪水是：' || emp_record.avgsal || ',人数是：' ||emp_record.vcount);
end;

15、使用SQL语句

(一)select into语句：为变量赋值

其用法特点：

1. 查询列表后必须使用into子句，用于将查询出的字段值传递给变量
2. 查询语句必须并且只能返回一行，多于一行或没有返回结果都会产生异常
3. 可以使用完整的select语法

例：查询编号是7369的员工的姓名：

declare 
	v_name emp.ename%type;
begin
	select ename into v_name from emp where empno = 7369;
	dbms_output.put_line(v_name);
end;

注意：select后面的字段名要和into后面的字段名一一对应(数据、类型、个数一一对应)。
比如：下面的代码：

declare 
	v_name emp.ename%type;
	v_sal emp.sal%type;
begin
	select ename,sal into v_name,v_sal from emp where empno = 7369;
	dbms_output.put_line(v_name);
end;

上面的select into语句中，select后面有两个字段ename,sal，那么into后面就应该有两个字段v_name,v_sal，而且v_name,v_sal顺序还不能交换，要与ename,sal的顺序保持一致才正确。

课堂练习

1、查找某个部门员工的平均薪水和人数。部门号使用替代变量

declare 
	v_no emp.deptno%type:=&no;
	avgsal number(6,2);
	v_count number(3);
begin
	select avg(sal),count(empno) into avgsal,v_count from emp where deptno = v_no;
	dbms_output.put_line(avgsal || '     ' || v_count);
end;

2、应用记录类型变量完成查找某个部门员工的平均薪水和人数。部门号使用替代变量

declare
    type emp_salcount is record(avgsal number(6,2),vcount number(3));
    emp_record emp_salcount;
    v_no emp.deptno%type:=&no;
begin 
    select avg(sal),count(empno) into emp_record from emp where deptno=v_no;
    dbms_output.put_line(v_no || '部门的平均薪水是：' || emp_record.avgsal || ',人数是：' ||emp_record.vcount);
end;

3、如果希望输出的薪水以某种格式显示，比如前面加上美元符号，有两位小数点。应该如何实现？

方法一：普通方法

declare 
    v_avgsal varchar(10);
begin
    select to_char(avg(sal),'$9999.99') into v_avgsal from emp where deptno = &no;
    dbms_output.put_line(v_avgsal);
end;

注意：v_avgsal varchar(10);
设置成字符类型而不再设置成数字类型，这是因为加上美元符号之后就应该是字符类型的了。

方法二：应用记录类型变量

declare
    type emp_sal is record(v_sal varchar2(20));
    emp_record emp_sal;
begin
    select to_char(avg(sal),'$9999.99') into emp_record from emp where deptno=&no;
    dbms_output.put_line(emp_record.v_sal);
end;

(二)DML语句（insert/update/delete）（增删改）

DML语句在PL/SQL中并没有任何改动
可以按照原语法格式使用相应的命令来修改数据库中表的数据。
练习：
增加一个新部门到部门表中。
修改部门所在的位置
删除一个部门

例：

begin
	update dept set loc = 'beijing' where deptno = 10;
end;

--验证
select * from dept;

上面是一个改的过程，增和删的过程和该类似，自己练习即可。

(三)使用事务处理语句

在PL/SQL中使用commit或rollback命令控制事务的方法和SQL语句是一样的
事务同样开始于第一个DML语句
通过commit或rollback命令显示地结束事务。
练习一：使用事务处理结束事务
练习二：使用保存点savepoint

这个和sql语句中的事务管理一样，只不过是把语句写在了块中，所以此处不再练习，自己练习即可。
oracle数据管理、事务管理

(四)执行DDL语句

语句不能直接出现在pl/sql块中
执行DDL命令的方法是使用
execute immediate
在execute immediate后面加一个用单引号引起的DDL语句字符串。

适用于create、drop、alter

【例：】

--删除一个表：
BEGIN
  execute immediate 'drop table t';
END;

--创建一张表：
BEGIN
  execute immediate 'create table temp(id number primary key,name varchar2(20))';
END;