oracle复习笔记之PL/SQL程序所要了解的知识点

复习内容：

PL/SQL的基本语法、记录类型、流程控制、游标的使用、

异常处理机制、存储函数/存储过程、触发器。

为方便大家跟着我的笔记练习，为此提供数据库表文件给大家下载：点我下载

为了要有输出的结果，在写PL/SQL程序前都在先运行这一句：
set serveroutput on
结构：
declare
--声明变量、类型、游标
begin
--程序的执行部分（类似于java里的main()方法）
exception
--针对begin块中出现的异常，提供处理的机制
--when...then...
--when...then...
end;
举例1：

declare
  v_sal number(10); （注意每句话后面别忘记了分号，跟java中的一样）
begin
  select salary into v_sal from employees where employee_id = 100;
  dbms_output.put_line(v_sal);
end;

</div>

举例2：

declare
  v_sal number(10); （注意，这里声明的空间大小不能比原表中的小）
  v_email varchar2(20);
  v_hire_date date;
begin
  select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 
100;
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;
或者：
declare
  v_sal employees.salary%type;
  v_email employees.email%type;
  v_hire_date employees.hire_date%type;
begin
  select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 
100;
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

</div>

记录：

declare 
  type emp_record is record(
   v_sal employees.salary%type,
   v_email employees.email%type,
   v_hire_date employees.hire_date%type
  );
  v_emp_record emp_record;
begin
  select salary,email,hire_date into v_emp_record from employees where employee_id = 100;
  dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','|| 
  v_emp_record.v_hire_date);
end;

</div>

1、pl/sql基本的语法格式
2、记录类型 type ... is ...record(,,,);
3、流程控制：
3.1 条件判断（两种）
方式一： if ... then elseif then ... else ... end if;
方式二： case ... when ... then ...end;
3.2 循环结构（三种）
方式一：loop ... exit when ... end loop;
方式二：while ... loop ... end loop;
方式三：for i in ... loop ... end loop;
3.3 goto、exit
4.游标的使用（类似于java中的Iterator）
5.异常的处理

6.会写一个存储函数（有返回值）、存储过程（没有返回值）
7.会写一个触发器

复习记录类型：

declare
type emp_record is record(
  -- v_emp_id employees.employee_id%type,
  -- v_sal employees.salary%type
  v_emp_id number(10) := 120,
  v_sal number(10,2) :=12000
);
  v_emp_record emp_record;
begin
  -- select employee_id,salary into v_emp_record from employees where employee_id = 123;
  dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' '||'salary:'|| 
  v_emp_record.v_sal);
end;

</div>

也可以升级一下，要是想对表的所有列都输出，则：（须注意输出的列名要跟表中的列名要一样）

declare
  v_emp_record employees%rowtype;
begin
  select * into v_emp_record from employees where employee_id = 123;
  dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' '||'salary:'|| 
  v_emp_record.salary);
end;
使用记录来执行update操作：
declare 
  v_emp_id number(10);
begin
  v_emp_id :=123;
  update employees
  set salary = salary + 100
  where employee_id = v_emp_id;
  dbms_output.put_line('执行成功！~~');
end;

</div>

流程控制：
查询150号员工的工资，若其工资大于或等于10000 则打印‘salary >= 10000';
若在5000到10000之间，则打印‘5000 <= salary <10000';否则打印‘salary < 5000'

declare 
  v_sal employees.salary%type;
begin
  select salary into v_sal from employees where employee_id =150;
  if v_sal >= 10000 then dbms_output.put_line('salary >= 10000');
  elsif v_sal > 5000 then dbms_output.put_line('10000 > salary >= 5000');
  else dbms_output.put_line('salary < 5000');
  end if;
  dbms_output.put_line('salary:'||v_sal);
end;
利用case ... when ... then ... when ...then ... else ... end实现上题;
declare 
  v_sal employees.salary%type;
  v_temp varchar2(20);
begin
  select salary into v_sal from employees where employee_id =150;
  v_temp :=
  case trunc(v_sal/5000) when 0 then 'salary < 5000'
                  when 1 then '5000 <= salary < 10000'
                  else 'salary >= 10000'
                  end;
  dbms_output.put_line('salary:'||v_sal||' '||v_temp);
end;

</div>

查询出122号员工的job_id,若其值为 ‘IT_PROG', 则打印‘GRADE:A'
                                                ‘AC_MGT', 则打印‘GRADE:B'
                                                ‘AC_ACCOUNT', 则打印‘GRADE:B'
                                                 否则打印‘GRADE:D'

declare 
  v_job_id employees.job_id%type;
  v_temp varchar2(20);
begin
  select job_id into v_job_id from employees where employee_id =122;
  v_temp :=
  case v_job_id when 'IT_PROG' then 'A'
            when 'AC_MGT' then 'B'
            when 'AC_ACCOUNT' then 'C'
            else 'D'
            end;
  dbms_output.put_line('job_id:'||v_job_id||' '||v_temp);
end;

</div>

使用循环语句打印：1-100

declare
  v_i number(5) :=1;

begin
  loop
  dbms_output.put_line(v_i);
  exit when v_i >=100;
  v_i := v_i + 1;
  end loop;
end;
使用while实现：
declare
  v_i number(5) :=1;
begin
  while v_i <= 100 loop
   dbms_output.put_line(v_i);
   v_i := v_i + 1;
  end loop;
end;
使用for...in...loop...end loop;实现：
begin
  for c in 1..100 loop
   dbms_output.put_line(c);
  end loop;
end;

</div>

输出2-100之间的质数

declare
  v_i number(3):= 2;
  v_j number(3):= 2;
  v_flag number(1):= 1;
begin 
  while v_i<=100 loop
   while v_j<=sqrt(v_i) loop
    if mod(v_i,v_j)=0 then v_flag:=