oracle 高级应用

PLSQL是对sql语言的扩展，是面向过程的语言
一、程序语法结构：

set serveroutput on   --打开Oracle输出开关
declare
      --说明部分（变量，光标或者例外）
begin
     ---程序体
     dbms_output.put_line('Hello world');  --输出语句
end;

示例：

declare
  --声明变量
  pnumber number(7,2);
  pname varchar2(20);
  pdate date;
begin
  --赋值
  pnumber:=1;
  dbms_output.put_line(pnumber);
  pname:='tom';
  dbms_output.put_line(pname);
  pdate:=sysdate;
  dbms_output.put_line(pdate);
end;

******************************************************************************************************************************************
  1、引用型变量：my_name emp.ename%type

set serveroutput on
declare
  --声明引用型变量 
  pname emp.ename%type;
  psalary emp.salary%type;
begin
  --程序体
  select ename,esalary into pname,psalary from emp where eno = 7890;
  --输出
  dbms_output.put_line(pname||'的薪水是'||psalary);
end;

  2、记录型变量：emp_rec emp%rowtype  (取表中的一行数据，可以看做是一个数组)

set serveroutput on
declare
  --定义记录型变量
  emp_rec emp%rowtype;
begin
  --获取行信息
  select * into emp_rec from emp where eno = 7890;
  --输出
  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.esalary);
end;

  3、if语句：

set serveroutput on
--从键盘接收一个输入，num：地址值，含义是：在该地址上保存了输入的值
accept num prompt'请输入一个数组：'; 
declare 
  --定义变量保存用户输入的数据
  pnum number:=#
begin
  --程序体
  if pnum = 0 then dbms_output.put_line('您所输入的值是0');
  elsif pnum = 1 then dbms_output.put_line('您所输入的值是1');
  elsif pnum = 2 then dbms_output.put_line('您输入的值是2');
  else dbms_output.put_line('其他数字');
  end if;
end;

  4、while循环语句：(当循环条件成立时执行loop里的代码)

set serveroutput on;
declare
  pnum number:=1;
begin
  --循环体
  while(pnum<10) loop
  dbms_output.put_line(pnum);
  pnum = pnum + 1;
  end loop;
end;

  5、loop循环：(当条件成立时退出循环)------------------推荐使用

set serveroutput on
declare
  pnum number:=1;
begin
  --循环体
  loop
  exit when pnum>10; --当条件成立时退出循环
  dbms_output.put_line(pnum);
  pnum = pnum +1;
  end loop;
end;

  6、for循环：

set serveroutput on
declare
  pnum number:=1;
begin
  for pnum in 1..10 loop
  dbms_output.put_line(pnum);
  end loop;
end;

*************************************************************************************************************************************
 二、光标：（就是一个结果集，相当于ResultSet）
/*
1、光标的属性：
%found     :取到数据时为true
%notfound  ：没有取到数据时为true
%isopen    ：判断光标是否打开
%rowcount  ：影响的行数
2、默认情况下，oracle数据库只允许在同一会话中打开 300个光标
  修改光标数的限制数
  alter system set open_cursors = 400 scope = both;
*/
cursor c1 is select ename from emp; --定义一个光标（c1），就是is后面的select语句
open c1;--打开光标执行查询
fetch c1 into pename; --取一行光标的值放入变量中
close c1;  --关闭光标
示例：

set serveroutput on
declare
  --定义光标
  cursor cemp id select ename,salary from emp;
  --为光标定义相应的变量
  pname   emp.ename%type;
  psalary emp.salary%type;
bengin
  --打开光标  
  open cemp;
  --循环输出光标里面的内容
  loop
  fetch cemp into pname,psalary;
  exit when cemp%notfound;  --光标没有取到值时退出循环
  dbms_output.put_line(pname||'的薪水是'||psalary);
  end loop;
  --关闭光标
  close cemp;
  --注意：当在plsql中对数据库做更改时，最后一定要提交才能生效
  commit;
end;

======================================

set serveroutput on
declare 
  --定义光标
  cursor cemp id select ename,salary from emp;
  --定义变量
  pname    emp.ename%type;
  psalary  emp.salary%type;
begin
  --打开光标
  open cemp;
  --程序体
  if cemp%isopen then 
     dbms_ouput.put_line('光标已经打开');  
  else 
     dbms_output.put_line('光标没有打开');
  --关闭光标
  close cemp;
end;

======================================
带参数的光标

set serveroutput on
declare
  --定义光标
  cursor cemp(dno number) id select ename,salary from emp where deptno = dno;
  --定义变量
  pname   emp.ename%type;
  psalary emp.esalary%type;
begin
  --打开光标
  open cemp(10);
  --程序体
  loop
  fetch cemp into pname,psalary;
  exit when cemp%notfound
  dbms_output.put_line(pname);
  end loop;
  --关闭光标
  close cemp;
end;

***************************************************************************************
三、例外：程序设计语言提供的一种功能，用来增强程序的健壮性和容错性
  1、系统例外：no_data_found

set serveroutput on
declare
  --定义变量
  pname emp.ename%type;
begin
  select ename into panme from emp where eno = 1234;
  exception
  when no_data_found then dbms_output.put_line('没有找到该员工');
  when others then dbms_output.put_line('其他例外');
end;

  2、系统例外：too_many_rows

set serveroutput on
declare
  --定义变量
  pname emp.ename%type;
begin
  select ename into panme from emp where deptno = 10;
  exception
  when too_many_rows then dbms_output.put_line('select into 匹配了多行');
  when others then dbms_output.put_line('其他例外');
end;

  3、系统例外：zero_dived,value_error,
  4、自定义例外：raise 抛出自定义例外
***************************************************************************************
plsql程序设计：
瀑布模型：
1、需求分析
2、设计（概要设计，详细设计）
3、编码Coding
4、测试
5、上线
SQL语句：
select to_char(joindate,'yyyy') from emp;
-->集合-->光标-->循环-->退出条件：notfound