Oracle学习笔记（四）

七、PL/SQL 基本语法

​ PL/SQL是oracle对SQL语言的过程化拓展，指在SQL命令中增加了过程处理语句（如分支、循环等），是SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，是的PL/SQL面向过程但比过程语言简单、高效、灵活和实用。

7.1 PL/SQL 程序语法

​ 程序语法：

declare
	说明部分（变量说明，游标说明，例外说明）
begin
	代码逻辑（DML语句）...
exception
	异常处理语句
end;

7.2 常量和变量定义

​ 在程序的声明阶段可以来定义常量和变量。

​ 变量的基本类型就是oracle建表时字段的变量，如char,varchar2,date,number,boolean,long

​ 定义语法：

​ val char（15）；

​ Psal number（9,2）；

​ 说明变量名、数据类型和长度后用分号结束说明语句。

​ 常量定义：married constant boolean := true

​ 引用变量

​ Myname emp.ename%type;

​ 引用型变量，即my_name 的类型与emp表中的ename列的类型一样

​ 在SQL中使用into来赋值

select 列名 into 变量名 from 表名 where 条件
注意：结果必须是一条记录，有多条记录和没有记录都会报错

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

​ 属性类型

​ %type 引用型

​ 作用：引用某表某列的字段类型

​ %rowtype 记录型

​ 作用：标识某个表的行记录类型

​ 变量

声明变量的语法
变量名 类型（长度）

变量赋值的语法
变量名 := 变量值

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

7.3 异常

​ 发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分。

异常有两种类型：

预定义异常：当PL/SQL程序违反oracel规则或超越系统限制时隐式触发

用户定义异常：用户在PL/SQL块的声明部分定义异常，自定义的异常通过raise语句显示引发

7.4 if 分支

语法1：

​ if 条件 then 语句1；

​ 语句2；

​ end if；

语法2：

​ if 条件 then 语句序列1；

​ else 语句序列2；

​ end if；

语法3：

​ if 条件 then 语句；

​ elsif 语句 then 语句；

​ else 语句；

​ end if；

举例：

如果从控制台输入1则输出我是1否则输出我不是1
declare
	pnum number := #
begin
	if pnum = 1 then
		dbms_output.put_line('我是1');
	else
		dbms_output.put_line('我不是1');
	end if;
end;

判断人的不同年龄段18岁以下是未成年人，18岁以上40以下是成年人，40以上是老年人
declare
	mynum number := #
begin
	if mynum < 18 then
		dbms_output.put_line('未成年人');
	elsif mynum >= 18 and mynum < 40 then
		dbms_output.put_line('成年人');
	elsif mynum >= 40 then
		dbms_output.put_line('老年人');
	end if;
end;

7.5 loop 循环语句

语法1：

​ while total <= 15000 loop

​ …

​ total := total + salary;

​ end loop;

语法2：

​ loop

​ exit[when 条件];

​ …

​ end loop;

语法3：

​ for i in 1…3 loop

​ 语句序列；

​ end loop；

举例：

使用语法1输出1到10的数字
declare
	step number := 1;
begin
	while step <= 10 loop
		dbms_output.put_line(step);
		step := step + 1;
	end loop;
end;

使用语法2输出1到10的数字
declare
	step number := 1;
begin
	loop
		exit when step > 10;
		dbms_output.put_line(step);
		step := step + 1;
	end loop;
end;

使用语法3输出1到10的数字
declare
	step number := 1;
begin
	for step in 1..10 loop
		dbms_output.put_line(step);
	end loop;
end;

7.6 游标 Cursor

​ 游标可以存储查询返回的多条数据。

语法：

cursor 游标名 [(参数名 数据类型，参数名 数据类型，...)] is select 语句;
 例：cursor c1 is select ename from emp;

使用步骤：

打开游标： open c1；（打开游标执行查询）

取一行游标的值： fetch c1 into pjob；（取一行到变量中）

关闭游标： close c1；（关闭游标释放资源）

游标的结束方式 exit when c1%notfound

**注意：**上面的pjob必须与emp表中的job列类型一致‘

​ 定义：pjob emp.empjob%type;

举例：

使用游标方式输出emp表中的员工编号和姓名
declare
	cursor pc is
	select * from emp;
	pemp emp%rowtype;
begin
	open pc;
	loop
		fetch pc		//提取游标到变量
		into pemp;
		exit when pc%notfound;
		dbms_output.put_line(pemp.empno||' '|| pemp.ename);
	end loop;
	close pc;
end;

按员工的工种涨工资，总裁1000元，经理800元，其他人员400元。
备份出一张新表为myemp;  create table myemp as select * from emp;
declare
	cursor pc is
	select * from myemp;
	addsal myemp.sal%type;
	pemp myemp%rowtype;
begin
	open pc;
	loop
		fetch pc
		into pemp;
		exit when pc%notfound;
		if pemp.job = 'PRESIDENT' then
			addsal := 1000;
		elsif pemp.job = 'MANAGER' then
			addsal := 800;
		else
			addsal := 400;
		end if;
		update myemp t set t.sal = t.sal + addsal where t.empno = pemp.empno;
	end loop;
	close pc;
end

写一段PL/SQL程序，为部门号为10的员工涨工资。
declare
	cursor pc(dno myemp.deptno%type) is
	select empno from myemp where deptno = dno;
	pno myemp.empno%type;
begin
	open pc(20);
	loop
		fetch pc
		into pno;
		exit when pc%notfound;
		update myemp t set t.sal = t.sal + 1000 where t.empno = pno;
	end loop;
	close pc;
end;