PL/SQL语言基础--1

基础

PL/SQL结构

1.声明
2.执行
3.异常

declare
	变量名字 类型;
begin
	程序运行
exception
	when 异常 then
		处理
end;

语法

字符集

不区别大小写
包括：
	所有大小写字母
	数字
	空白符
	符号：+-*等

分隔符

分隔符	描述
+,-, *, /	加法，减法/负，乘法，除法
%	游标属性绑定或代表任意字符的通配符
’	字符串分隔符
.	从属关系
,	表达式或列表分隔符
:	主机变量指示符
"	引用标识符分隔符
=	关系等
@	数据库链接符
;	声明或语句终止符
:=	赋值运算符
=>	位置定位符
ΙΙ	字符串连接运算符
**	指数运算符
<<, >>	标签分隔符(开始和结束)
/*, */	多行注释分隔符(开始和结束)
–	单行注释指示符
…	范围运算符
<, >, <=, >=	关系运算符
<>, '=, ~=, ^=	不同版本的”不等于”运算符

常量

1.字符型
2.数字
3.布尔
4.日期

注释

单行 --
多行 /* */

初值

每一行只能定义一个变量
constant代表常量必须赋初值
not null 关键字不行赋初值
:= / default 赋初值

sql语句

SELECT语句

select into语句
注意这个语句一次只能记录一个信息
如果没有查到则会发生NO_DATA_FOUND异常
如果查到多个则会产生TOO_MANY_ROWS异常
查询b员工的信息和id为109员工的名字

declare
	v_emp emp%rowtype;
	v_name emp.name%type;
begin
	select * into v_emp from emp where name='b';
	dbms_output.put_line(v_emp.id||' '||v_emp.salary);
	select name into v_name from emp where id = 109;
	dbms_output.put_line(v_name);
end;

DML语句

pl/sql中对dml语句进行了扩展，允许使用变量

控制结构

选择判断

if

if 条件 then 语句;
elsif 条件2 then 语句2;
else 语句3;
end if;

条件中：如果值为null则视为flase

case 语句

case
	when 条件 then ..;
	when ... then ...;
end case;

循环

简单循环

loop
	.....;
	exit [when 退出条件];
end loop;

while

while 进入条件 loop
	.....;
end loop;

for

for 循环变量 in {reverse} 下界..上界
	.....;
end loop;

1.循环变量不需要显示的定义
2.系统默认变量从下界到上界，如果使用reverse 则从上界到下界递减
3.循环变量只能在循环用

例子求1-100之间偶数和

//普通语句
declare
	v_count binary_integer :=1;
	v_sum number :=0;
begin
	loop
		if mod(v_count,2)=0 then
			v_sum:=v_sum+v_count;
		end if;
		v_count := v_count + 1;
		exit when v_count > 100;
	end loop;
	dbms_output.put_line(v_sum);
end;

declare
	v_count binary_integer :=1;
	v_sum number :=0;
begin
	while v_count <=100 loop
		if mod(v_count,2)=0 then
			v_sum:=v_sum+v_count;
		end if;
		v_count := v_count + 1;
	end loop;
	dbms_output.put_line(v_sum);
end;

	declare
	v_sum number :=0;
begin
	for v_count in 1..100 loop
		if mod(v_count,2)=0 then
			v_sum:=v_sum+v_count;
		end if;
	end loop;
    dbms_output.put_line(v_sum);
end;

跳转结构

goto实现
<<>>标记
goto 标记;

pl/sql内部可以跳转，内可以跳到外，外不能跳到内

输入

虽然我不知道sql需要这种输入干嘛，不过要考，还是搞一下。

输入可以在定义阶段和代码阶段来进行,通过&符号来表示
变量:=&提示;

输出

	dbms_output.put_line(输出的内容)；

游标

前面我们知道了select into一次只能查询一条语句。
这里介绍利用游标处理select语句返回多行数据

还可以通过select……bulk collect into处理多行数据

游标分为：
	显示游标：用户定义，多行select查询
	隐式游标：系统自动，DML和单行select查询

显示游标

定义游标

语法：

	cursor 名字 is select查询

1.必须在声明部分定义
2.定义时可以引用PL/SQL变量，但必须在游标定义前
3.定义时没有生成数据，只是保存在数据字典中。
4.可以使用名字%rowtype 定义记录类型的变量。

打开游标

	open 变量名

1.只有打开游标才会，查询
2.一旦打开就无法再次打开，除非关闭
3.如果游标定义中变量值发生改变，则只能在下次打开才能其作用

检索游标/遍历

	fetch 游标名 into 行名

关闭游标

	close 名

游标属性

属性	数据类型	含义
%ISOPEN	布尔	游标是否打开
%FOUND	布尔	判断最近一次使用fetch语句是否有数据
%NOTFOUND	布尔	判断最近一次是否没有数据，没有返回true
%ROWCOUNT	数值	检索个数
%BULK_ROWCOUNT	数值	第i给元素所影响的行数

循环检索实例

输出各个部门的平均工资
普通循环

declare
	cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
	v_dep c_dep%rowtype;
begin
	open c_dep;
	loop
		fetch c_dep into v_dep;
		exit when c_dep%notfound;
		dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
	end loop;
	closse c_dep;
end; 

while循环

declare
	cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
	v_dep c_dep%rowtype;
begin
	open c_dep;
	while c_dep%found loop
		fetch c_dep into v_dep;
		dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
	end loop;
	closse c_dep;
end; 

for 循环
for循环会自动打开检索和关闭游标

declare
	cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
begin
	for v_dep in c_dep loop
		dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
	end loop;
end; 

对于for循环而已，都是自动进行，所以可以不在声明部分定义游标，而是直接在for语句中使用，上面代码可以写成这样。

begin
	for v_dep in (select d_id,avg(salary) avgsfrom emps group by d_id) loop
		dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
	end loop;
end; 

隐式游标

所有的sql语句都有一个执行的缓冲区，隐式游标就是该缓冲区的指针，又称sql游标

属性

属性	类型	说明
SQL%isopen	布尔	是否打开
SQL%found	布尔	判断是否对数据库产生影响，如果插入、删除、修改或者查到数据则true
SQL%notfound	布尔	判断是否没有产生影响
SQL%rowcount	布尔	影响行数

游标变量

定义

引用类型

type 名字 is ref cursor [return 类型]

系统预留一个叫sys_refcursor的引用类型

变量

名字 引用类型名字；

打开游标变量

open 游标 for select的sql；

检索游标

	loop
		fetch 名 into ...;
		exit when 名%notfound；
	end loop；

只能用简单循环和while，不能用for。

关闭游标

close 名；

异常

异常概述

异常分为：预定异常、非预定异常、用户异常。
常见异常：这些例举的是我遇到的较多的，详细的可以去搜。

预定义异常	oracle错误	说明
too_mangy_rows	ora-01422	返回多行数据
no_data_found	ora-01403	没有发现数据
zero_divide	ora-01476	除数为0

异常处理

异常定义

异常变量 exception;

还需要与一个oracle错误相关联
pragma exception_init(异常变量，错误号(-20999~-20000))

异常抛出

raise 异常;

异常捕获

exception
	when 异常 then 处理;
	...;
end;

others异常处理器

总是作为最后的一个异常处理器，处理没有被其他异常捕获的异常。
其中有2个函数

函数	描述
sqlcode	当前错误码
sqlerrm	错误文本

例题

1.要求根据输入的不同表名进行不同处理，若表名为employees，则显示高于10号部门平均工资的员工信息。若表名为departments，则显示各个部门的人数。

完成这个需求有这样几个sql
查询高于10号部门平均工资的员工信息
select * from employees where salary > (select avg(salary) from employees where department_id =10);
各个部门的人数
select count(*) num ,department_id from employees group by department_id;

declare
	v_table char(20);
	type t_cursor is ref cursor;
	v_cursor t_cursor;
	v_emp employees%rowtype;
	v_deptno employees.department_id%type;
	v_num number;
begin
	v_table:='$table_name';
	if v_table = 'employees' then
		open v_cursor for select * from employees where salary > (
			select avg(salary) from employees where department_id =10);
		while v_cursor%notfound loop
			fetch v_cursor into v_emp;
			dbms_output.put_line(v_emp....);
		end loop
	elseif v_table ='departments' then
		open v_emp for select count(*) num ,department_id from employees group by department_id;
		while v_cursor%notfound loop
			fetch v_cursor into v_num,v_deptno;
			dbms_output.put_line(v_deptno||' '||v_num);
		end loop
	else
		raise_application_error(-20000,'Input must be ''emp'' or ''dept''');
	end if
	close v_cursor
end;