目录
目录
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;