PL/SQL编程

目录

PLSQL块的基本结构

PL/SQL块的分类

PL/SQL变量

变量命名规范

标量类型

复合类型

参照变量

游标变量（ref cursor）

对象类型变量（ref obj-type）

控制结构

条件分支语句

循环控制语句

顺序控制语句

存储过程

自定义例外

---

目录

PLSQL块的基本结构

PL/SQL块的分类

标示符

变量命名规范

PL/SQL变量

标量变量

复合类型变量

复合变量-嵌套表

复合变量-变长数组

参照变量

游标变量（ref cursor）

对象类型变量（ref obj-type）

控制结构

条件分支语句

循环控制语句

顺序控制语句

---

PLSQL块的基本结构

PL/SQL，Procedural Language/SQL，是一种过程化程序语言，是对结构化查询语言SQL的扩展。它允许SQL的DML、查询语句包含在块和过程代码中，使PL/SQL成为一个功能强大的事务处理语言。PL/SQL的基本单位是块，包括声明部分、执行部分、和异常处理部分。

DECLARE 
-- 声明部分，包括变量、常量、复杂数据类型、游标等
BEGIN
-- 业务执行部分
EXCEPTION
--异常处理部分
END;

PL/SQL块的分类

PL/SQL变量

变量命名规范

标示符	命名规则
变量	v_name
常量	c_name
游标变量	emp_curror
异常标识	e_name
记录类型	name_record

标量类型

标示符名 表名.列名%type;

复合类型

复合类型-PL/SQL记录

declare
-- 定义一个记录类型，有三个类型的数据
type emp_record_type is record(name emp.ename%type, salary emp.sal%type, job emp.job%type);
-- 定义一个emp_record_type变量
my_record emp_record_type;
begin 
select ename, sal, job, into my_record from emp where empno = '7788';
dbms_output.put_line('员工姓名:'||emp_record.name);
end; 

复合类型-PL/SQL表

相当于高级语言中的数组，但是在PL/SQL中表类型的下标可以为负数，表示表元素的下标没有限制，在高级语言中数组的下标不能为负数。

-- 查询结果返回一个值
declare 
-- 定义表类型，指定表中元素的类型和长度
type my_table_type is table of emp.ename%type 
-- 下标为整数，允许为负数
index by binary_integer;
-- 定义my_table_type变量
my_table my_table_type;
begin
-- 查询ename存放在下标为0的位置
select ename into my_table(0) from emp where empno=7788;
dbms_ouput.put_line('员工姓名：'||my_table(0));
end;

-- 利用参照变量
-- 将多个结果返回
declare 
-- 定义表类型，指定表中元素的类型和长度
type my_table_type is table of emp.ename%type 
-- 下标为整数，允许为负数
index by binary_integer;
-- 定义my_table_type变量
my_table my_table_type;
begin
-- 查询ename存放在下标为0的位置
select ename into my_table(0) from emp where empno=7788;
dbms_ouput.put_line('员工姓名：'||my_table(0));
end;

参照变量

参照变量是指用于存放数据指针的变量，通过参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。

游标变量（ref cursor）

使用游标时，当定义游标时不需要指定相应的select语句，但是当打开游标时需要指定select语句，这样游标就和select语句结合了。

declare
-- 定义游标类型
type my_emp_cursor_type is ref cursor;
-- 定义游标变量
my_emp_cursor my_emp_cursor_type;
-- 参数变量
v_name emp.ename%type;
v_sal emp.sal%type;

begin
open my_emp_cursor for select ename, sal from emp where deptno=&no;
loop
    fetch my_emp_cursor into v_name, v_sal;
    -- 出现notfound异常时退出
    exit when my_emp_cursor%notfound;
    dbms_oupt.put_line('名字：'||v_name||'薪水：'||v_sal);
end loop;
end;

对象类型变量（ref obj-type）

控制结构

条件分支语句

if--then，if--then-else，if--then-elsif-else

declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_name, v_sal from emp where ename=&name;
-- 判断
if v_sal < 2000
then 
    update emp set sal = sal+ sal * 10% where ename = v_name;    
end if;
end;

declare
v_name emp.ename%type;
v_comm emp.comm%type;
v_num number;
begin
select ename, comm from emp where ename=&name;
if v_comm<>0 then 
    v_num=100;
else 
    v_num=200;
end if;
update emp set comm=v_num where ename=v_name;
end;

declare
v_name;
v_sal number;
v_job emp.job%type;
begin
select v_job into v_job where ename = &name;
if v_job = 'PRESIDENT' then 
    v_sal 2000;
elsif v_job = 'MANAGER' then 
    v_sal = 1000;
else 
    v_sal = 500;
end if;
update emp set sal= sal+v_sal where ename = v_name;
end;

循环控制语句

loop循环，以loop开始，以end loop结尾，至少被执行一次，执行效果类似于java中的do-while结构。

declare
v_name varchar;
v_num = 1;
begin
v_name := &name;
loop
    insert into users values(v_num, v_name);
    exit when v_num = 10;
    v_num := v_num+1;
end loop;
end;

while循环，当循环条件为true时，才执行循环体，以while..loop开始，以end loop结束。

declare
v_name varchar;
v_num = 1;
begin
v_name := &name;
 while v_num<20 loop
    insert into users values(v_num, v_name);
    v_num := v_num+1;
end loop;
end;

for循环

declare
v_name varchar;
begin
v_name := &name;
for i in reverse 1..10 loop
 insert into users values(i, v_name);
end loop;
end;

 

顺序控制语句

goto语句，跳转到特定标号去执行语句，但是使用goto语句会增加程序的复杂性，可读性差，所以一般不建议使用goto。

用法：goto label，label为预定义的标号。

declare
i int := 1;
begin
    loop
        dbms_ouput.put_line(i);
        if i == 10 then 
            goto end_loop;
        end if;
        i := i+1;
    end loop;
    <<end_loop>>
    dbms_output.put_line('循环结束!');
end;

null语句，不会执行任何操作，并且会直接将控制传递到下一个语句，使用null语句的好处是可以提高pl/sql的可读性。

存储过程

无返回值得存储过程

create or replace procedure Pro_AddBook(bookName in varchar, price in number) is
begin
insert into book values(bookName, price);
end; 

返回单个值得存储过程

create or replace procedure Pro_AddBook(bookid in number, price out number) is
begin
select price into price from book where id=bookid;
end; 

返回集合的存储过程

-- 创建包
create or replcate package outpackage as
type my_cursor is ref cursor;
end outpackage;

-- 创建存储过程
create or replace procedure testProcedure(dno in number, outCursor out  outpackage.my_cursor) is
begin
    open outCursor for select * from emp where deptno=dno;
end;

自定义例外

create or replace procedure ex_test(eno in number)
is
-- 定义一个例外
myex exception;
begin
--更新0行数据
update emp set sal=sal+1000 where empno=eno;
--sql%notfound没有update操作
-- raise 触发myex异常
if sql%notfound then 
raise myex;
end if;
exception
    when myex then 
        dbms_out.put_line('没有更新任何数据行');
end;