Oracle的PLSQL编程

1. PL/SQL介绍

PL/SQL就是过程语言（Procedural Language）与结构化语言查询语言（SQL）的结合，让我们能够通过增加变量和控制语句的方式编写更加复杂的SQL。

PL/SQL基本语法结构：

declare
	-- 变量声明
begin
	-- 执行的语句
	-- 异常处理
end;

基本注意点：

• 赋值操作要使用 :=
• end后面必须要跟上;
• 如果没有要声明的变量，declare可以省略不写
• begin和end之间必须要有至少一条语句

2. dbms_output用法

dbms_output主要用于打印一些PL/SQL中的信息或者调试作用，常见用法：

• enable：使dbms_output生效（默认开启）。
• disable：使dbms_output关闭。
• put：将内容写到内存缓冲区，等到put_line时一起输出。
• put_line：输出内存缓冲区中的内容。
• new_line：作为一行内容的结束，类似换行符。
• get_line(value, index)：获取缓存区中的一行数据。
• get_lines(array, index)：获取缓冲区中的多行数据。

示例：

begin
   dbms_output.put('a1');
   dbms_output.put('b2');
   dbms_output.new_line(); -- 输出缓存中的信息，新起一行
   dbms_output.put_line('aaaaa'); -- 会输出缓存中的信息和当前的信息，不会换行
end;

如果dbms_output无法输出，执行如下命令：
set serveroutput on;

3. 赋值操作

3.1 :=

declare
	-- 从控制台
	a number(3) := &请输入a;
	b number(3) := 20;
	num number(3);
	-- 声明常量，常量无法被修改
	f constant varchar2(20)='this is a constant value';
begin
	num := a + b;
	dbms_output.put_line(a || '+' || b || '=' || num);
end;

3.2 into

into可以将SQL查询出的字段赋值给变量。

declare
	v_name varchar2(30);
	v_sex varchar2(3);
	v_dept varchar2(30);
begin
	select name,sex,dept into v_name,v_sex,v_dept from student where id = 101;
	dbms_output.put_line(v_name || ',' || v_sex || ',' || v_dept);
end;

3.3 属性类型

给变量声明的类型与字段或者整行进行属性绑定。

• %type：变量和字段类型绑定。
• %rowtype：表结构中整行记录的类型绑定。

declare
	-- v_name的字段类型为student表的name类型
	v_name student.name%type;
	v_sex student.sex%type;
	v_dept student.department%type;
begin
	select name,sex,department into v_name,v_sex,v_dept from student where id = 101;
	dbms_output.put_line(v_name || '-' || v_sex || '-' || v_dept);
end;

declare
	-- 表结构中有多少个的字段，我们就声明多少个变量，很繁琐，所以采用%rowtype
	v_row student%rowtype;
begin
	select * into v_row from student where id = 101;
	dbms_output.put_line(v_row.id || '-' || v_row.name || '-' || v_row.sex);
end;

4. 控制语句

4.1 if语句

基本语法：

declare
	age number(3):=&请输入年龄;
begin
	if age = 18 then
		dbms_output.put_line('刚成年');
	elsif age < 18 then
		dbms_output.put_line('未成年');
	elsif age > 18 then
		dbms_output.put_line('成年人');
	else
		dbms_output.put_line('未知');
	end if;
end;

4.2 case语句

case语句语法：

declare
	age number(3):=8;
begin
	case
		when age < 18 then
			dbms_output.put_line('未成年');
		when age > 18 then
			dbms_output.put_line('成年人');
		else
			dbms_output.put_line('未知');
	end case;
end;

4.3 循环语句

4.3.1 无限循环

loop循环可以通过exit来跳出循环，如果不声明就是无限循环。

declare
	i number(3) := 1;
begin
	loop
		dbms_output.put_line(i);
		-- 退出循环
		exit when i > 10;
		i := i + 1;
	end loop;
end;

4.3.2 带条件循环

通过while制定循环条件。

declare
	i number(3) := 1;
begin
	while i <= 10 loop
		dbms_output.put_line(i);
		i := i + 1;
	end loop;
end;

4.3.3 for循环

begin
	for i in 1..10 loop
		dbms_output.put_line(i);
	end loop;
end;

begin
	for curr_row in (select id,name,sex,department from student) loop
		dbms_output.put_line(curr_row.id || '-' || curr_row.name || '-' || curr_row.sex || '-' || curr_row.department);
	end loop;
end;

4.3.4 goto

goto关键字用于跳转到我们想要制定的位置重新开始自上而下执行。

declare
	v1 number(3):=11;
begin
	if v1 > 10 then
		goto c1;
	elsif v1 < 10 then
		goto c2;
	else
		dbms_output.put_line('其他');
	end if;
	dbms_output.put_line('跳过内容');
	<<c1>>
		dbms_output.put_line('大于10');
	<<c2>>
		dbms_output.put_line('小于10');

	dbms_output.put_line('end');
end;

5. 动态SQL语句

基本语法结构：

execute immediate 动态SQL字符串
	[into 定义的变量列表]
	[using 绑定的参数列表];

declare
	v_name student.name%type := '李四';
	v_sex student.sex%type := '男';
	v_sql varchar2(200);
	v_row student%rowtype;
begin
	v_sql := 'select * from student where 1 = 1';
	if v_name is not null then
		-- Oracle中是使用单引号进行转义的，''输出'
		v_sql := v_sql || ' and name like ''%' || v_name || '%''';
	end if;
	if v_sex is not null then
		v_sql := v_sql || ' and sex = ''' || v_sex || '''';
	end if;
	execute immediate v_sql into v_row;
	dbms_output.put_line(v_row.name || '-' || v_row.sex || '-' || v_row.department);
end;

6. 异常语句

PL/SQL异常有两种类型：

• 预定义的异常：PL/SQL程序违反Oracle规则时隐式引发的异常。
• 用户定义的异常：用户可以在PL/SQL声明部分定义的异常，自定义异常通过raise语句显示引发。

处理预定义异常：

-- 例如：
--     too_many_rows 多行数据
--     no_data_found 找不到数据
--     others 其他异常
declare
	v_name student.name%type;
begin
	select name into v_name from student where id = 900;
	-- 异常处理语块
	exception
		when too_many_rows then
			dbms_output.put_line('返回太多行');
		when no_data_found then
			dbms_output.put_line('找不到数据');
		when others then
			dbms_output.put_line('其他错误');
end;

自定义异常的声明以及处理：

declare
	-- 自定义异常声明
	myException exception;
	v_name varchar2(30) := '赵六';
begin
	if v_name not in ('张三','李四','王五') then
		-- 显示抛出自定义异常
		raise myException;
	else
		dbms_output.put_line('找到了');
	end if;

	exception
		when myException then
			dbms_output.put_line('触发了自定义异常');
		when others then
			dbms_output.put_line('其他异常');
end;