Oracle PL/SQL编程基础

Oracle PL/SQL编程基础

---

首先说一哈 PL/SQL 的优点有：

1、提高引用程序的运行性能。
2、提供模块化的程序设计功能
3、允许定义标识符。
4、具有过程语言控制结构
5、具备良好的兼容性
6、可以处理运行错误

---

oNE : PL/SQL 块

块（block）是 PL/SQL 的基本程序单元，块与块之间的嵌套没有限制。
三部分组成：
定义部分、执行部分和异常处理部分
1、定义部分：定义变量、常量、游标、异常等其他数据类型；
2、执行部分：用于实现应用模块功能。
3、异常处理部分：对执行过程中遇到的异常做出处理。

结构：

	declare 
	  /*定义部分*/
	 begin
	   /*执行部分*/
	 exception
	  /*异常处理部分*/ 
	 end;	--块结束标志
	 
	定义部分和异常处理部分可以不存在,只需要执行部分也可以 
	eg:
	 begin
		   /*执行部分*/
	 end;	--块结束标志

下面来一个案例看看

	declare 
	    retname varchar2(20);
	begin
	  select username into retname  from crTale_01 where id=&getid;
	  dbms_output.put_line('查询id账户的姓名为：'||retname);
	exception
	  when no_data_found then
	    dbms_output.put_line('查无此人');
	end;

tWO: PL/SQL 数据类型

我们在PL/SQL 块中定义部分 定义参数的时候我们就需要对每个参数指定数据类型，
在PL/SQL 中，数据类型有很多种 ：标量类型、复合类型、参照类型 和LOB 类型等。我们可以通过下面的表格稍微了解点

标量类型

类型名	解释
varchar2(n)	定义可变长度的字符串,其中 n 指定字符串的最大长度
char(n)	固定长度的字符串，n 最大长度，默认为1，一般我们用来定义性别参数这样的字符
number(n,m)	定义固定长度的整数和浮点数，其中n指定数字的总长度，m指定小数位数；即（小数位数=m ; n=整数位数+m ）
date	日期和时间类型
timestamp	同date,但是有点不同的是timestamp 类型时间数据显示日期、时间、上下午标志和时区
boolean	布尔变量，其值可以为：true、false、null

属性类型

类型名	解释
%type	。。。。。
%rowtype	。。。。。

复合类型

类型名	解释
bfile	。。。。。
blog	。。。。。
clob	。。。。。
nclob	。。。。。

tHREE: PL/SQL 控制结构

条件分支语句

案例：

	declare 
	  uname  varchar2(30);
	  uwages number(11);
	  begin
	    select username ,wages into uname,uwages 
	    from crTale_01
	    inner join  crTale_02 
	    on crTale_01.Id=crTale_02.Id
	    where  crTale_01.Id=&uid;
	    if(uwages>15000) then
	    dbms_output.put_line(uname || '工资大于15000');
	    else 
	    dbms_output.put_line(uname || '工资小于15000');
	    end if;
	exception
	  when no_data_found then
	    dbms_output.put_line('查无此人');
	end;

	****************************************************
	declare 
	  uname  varchar2(30);
	  uwages number(11);
	  begin
	    select username ,wages into uname,uwages 
	    from crTale_01
	    inner join  crTale_02 
	    on crTale_01.Id=crTale_02.Id
	    where  crTale_01.Id=&uid;
	    if(uwages>15000) then
	    dbms_output.put_line(uname || '工资大于15000');
	    elsif (uwages<10000) then	--注意这里 “elsif”  不要写成“elseif” 了，还有“els”和“if”之间没有空格，是连在一起的。
	    dbms_output.put_line(uname || '工资小于10000');
	    else
	     dbms_output.put_line(uname || '工资介于10000-15000'); 
	    end if;
	exception
	  when no_data_found then
	    dbms_output.put_line('查无此人');
	end;

–case 语句基本用法

案例：

	declare
	   gnum number;
	begin
	  gnum:=#
	   case gnum
	     when 1 then
	         dbms_output.put_line('输入的数字是1');
	     when 2 then
	         dbms_output.put_line('输入的数字是2');
	     when 3 then
	         dbms_output.put_line('输入的数字是3');
	     else
	         dbms_output.put_line('输入的不合法');  
	   end case;      
	end;
	
	****************************************************
	--case 语句中使用多种条件
	
	declare
	 uwages number;
	begin
	  select wages into uwages 
	  from crTale_01 
	  inner join  crTale_02 
	  on crTale_01.Id=crTale_02.Id
	  where username=trim('&name');
	  case
	    when uwages<8500 then
	       dbms_output.put_line('工资低于 8500');
	    when uwages<15000 then
	       dbms_output.put_line('工资低于 15000 高于 8500');
	    when uwages<25000 then
	       dbms_output.put_line('工资低于 25000 高于 15000');
	    else
	      dbms_output.put_line('工资高于 25000');
	    end case;
	exception
	  when no_data_found then
	  dbms_output.put_line('查无此人');
	end;

–循环语句

–基本循环

	declare
	 i int :=1;
	 begin 
	   loop
	     dbms_output.put_line(i);
	     i:=i+1;
	     exit when i>10; --当 i 大于 10 时候退出循环
	   end loop;
	end;

– while 循环

	declare
	 i int :=1;
	begin
	  while i<=10 loop --当 i 大于 10 时候退出循环
	     dbms_output.put_line(i);
	     i:=i+1;
	     end loop;
	end;

– for 循环

	declare
	 i int :=1;
	begin
	  for i in 1..10 loop  --循环条件是 1到 10 包括 10，每次自增 1
	   dbms_output.put_line(i);
	  end loop;
	end; 

---

–异常处理

案例

	declare
	retsex  char(2);
	retname varchar(20);
	begin
	  select username,sex into retname,retsex from crTale_01 where username=trim('&name');
	  dbms_output.put_line('姓名：'||retname);
	  dbms_output.put_line('性别：'||retsex);
	exception
	  when no_data_found then
	    dbms_output.put_line('查无此人');
	end;

–处理预定义异常

– 1、case_not_found
– 对应 ORA-06592 错误，在case 语句中，如果在when 子句中没有包含必须的条件分支，有没有else 子句，就会隐含触发该异常

– 2、cursor_already_open
– 对应 ORA-06511 错误，当打开已经打开的游标时，会隐含触发该异常。

– 3、invalid_number
– 对应 ORA-01722 错误，当不能有效的把字符转化为数字时，会隐含该触发异常。

– 4、too_many_rows
– 对应 ORA-01422 错误，当执行 select into 子句时，如果返回超过一行就会触发该异常。

– 5、zero_divide
– 对应 ORA-01476 错误，如果除数为 0 时，触发该异常。

– 6、no_data_found
– 对应 ORA-01403 错误，当执行select into 未返回行，或者引用了索引表未初始化元素时，会隐含该触发异常。

–处理自定义异常

案例

	declare
	 e_integerity exception;     --定义非预定义异常
	 e_no_employee exception;    --定义自定义异常
	 pragma exception_init(e_integerity,-2291);--关联非预定义异常
	 begin
	    update crTale_01 set username='您好啊'   where id=100;
	    if sql%notfound then 
	      raise e_no_employee;       --显示触发自定义异常
	    end if;
	exception 
	  when e_integerity then
	     dbms_output.put_line('查无此人1');
	   when e_no_employee then
	     dbms_output.put_line('查无此人2');
	end;

---

游标

使用 游标存放查询到的多条数据；

创建游标：

declare cursor 游标名[(参数列表)]
is
查询SQL语句[for update]
行变量  游标名%rowtype;
begin
 //使用游标(获取变量出来的游标中的数据)
end;

读取游标中的数据：

 fetch  游标名称 into 行变量    -- 注意：一行一行 提取，需要手动 的打开和关闭游标；    
 for 变量名  in 游标名 loop
 end loop;

使用游标的注意事项：
1.如果使用游标更新数据，sql语句后必须加上[for update], update/delete 语句后面必须加上[where current of 游标名]
2.在使用前一定要打开游标 open 游标名[(参数列表)],使用后 关闭：close 游标名;
案例：