PL/SQL编程基本概念

/*
=============================================================================
                        pl/sql编程
=============================================================================

*/

--pl/sql块的结构
declare 
  --声明部门：在此声明pl/sql用到的变量、类型以及游标，以及局部的存储过程和函数
begin
  --执行部分：过程及sql语句，即程序的组成部分
exception 
  --异常处理部分：错误处理
end;

--例子
create table toys
(
id number(20),
name varchar2(50),
price number(5,2),
sal_date date
)
insert into toys values(1,'张三',525,sysdate)
insert into toys values(2,'李四',525,'2016-05-06');
select * from toys;

declare 
  v_name varchar2(20);
  v_price number;
begin
   select name,price into v_name,v_price from toys where id=1;
   dbms_output.put_line('名字：'||v_name||',价格'||v_price);
end;
10

/*
type和rowtype
-------------------------------------------------
*/
declare 
  -- v_name varchar2(25);
  -- v_name1 toys.name%type;   --返回一个
   v_name1 toys%rowtype;  --返回多个
   e_ronull exception;--声明异常
begin 
  select * into v_name1 from toys where id=1;
  dbms_output.put_line('名字：'||v_name1.name);
exception 
  when dlp_val_on_index then
  dbms_output.put_line('将重复键插入id列');
end;

--税点
declare 
v_start constant number:=3500;   --声明常量
begin
--sql语句
end


--常量和变量的声明
  变量名称 pl/sql的数据类型(大小):=init_value;
eg:variable_name constant data_type:=value;


--应用实例
declare 
    v_ename varchar2(20);
    v_rate number(7,2);
    c_rate_incr constant number(7,2):=1.10;
begin
    --方法一：通过select into 赋值
    select ename,sal* c_rate_incr into v_ename,v_rate from employee where empno='7788';
    --方法二：通过赋值操作符“:=”给变量赋值
    v_ename:='scott';
end;

--使用序列赋值
    v_no:=emp_seq.nextval;

----------------------实例2---------------------------------
--根据员工编号查询员工信息
declare 
    v_empno employee.empno%type:=4;
    v_rec employee%rowtype;
begin
    select * into v_rec from employee where empno=v_empno;
    dbms_output.put_line
           ('姓名:'||v_rec.ename||'工资:'||v_rec.sal||'工作时间:'||v_rec.hiredate);

end;
--==执行成功之后，输出：姓名:张四工资:10000工作时间:2017-02-02 00:00:00


/*
----------------------pl/sql控制语句--------------------------------
*/
--if的语法
if <布尔表达式> then
   pl/sql和sql语句
end if;
------------------------
if<布尔表达式> then
   pl/sql和sql语句
else
   其他语句
end if;
-------------------------
if <布尔表达式> then
   pl/sql语句和sql语句
elsif <其他布尔表达式> then
   其他语句
elsif <其他布尔表达式> then
   其他语句
else
   其他语句
end if;

----注意：是elsif  不是elseif


------------------------------case的语法--------------------------
-------格式一------
case 条件表达式
     when 条件表达式结果1 then
        语句段1
     when 条件表达式结果2 then
         语句段2
     when 条件表达式结果n then
         语句段n
     [else语句段]
end case;
-------格式二------
case
     when 条件表达式1 then
         语句段1
     when 条件表达式2 then
         语句段2
     when 条件表达式n then
         语句段n   
else 语句段
end case;

------------------------------循环控制--------------------------
loop 
    要执行的语句；
    exit when <条件语句>  --条件满足时，退出循环语句
end loop;

---while循环语句的语法
while <布尔表达式> loop 
    要执行的语句;
end loop;
--for循环语句的语法
for 循环计数器 in [reverse] 下限 ...上限 loop
    要执行的语句
end loop;


------------------------------实例3-------------------------
declare 
   v_counter number:=5;
begin
    dbms_output.put_line('v_counter的当前值为：'||v_counter);
    if v_counter>=10 then
       null;--为了使语法变得有意义，去掉null会报语法错误
   else
      v_counter:=v_counter+10;
      dbms_output.put_line('v_counter 的改变值为：'||v_counter);
   end if;
   end;
   
   
--========执行成功之后输出：v_counter的当前值为：5   v_counter 的改变值为：15


/*
  =======================异常处理机制===============================
*/

--语法
begin 
    sequence_of_statements;
exception 
    when <exception_name> then
         sequence_of_statements;
     when others then
         sequence_of_statements;
end;

----------------------------实例4------------------------------------
--查询编号为7788的雇员的福利补助（comm列）
declare 
    v_comm employee.comm%type;
    e_comm_is_null exception ;--定义异常类型变量
begin 
    select comm into v_comm from employee where empno=7788;
    if v_comm is null then
       raise e_comm_is_null;
    end if;
exception 
    when no_data_found then
       dbms_output.put_line('雇员不存在！错误为：'||sqlcode||sqlerrm);
    when e_comm_is_null then
       dbms_output.put_line('该雇员无补助');
    when others then 
       dbms_output.put_line('出现其他异常！');
    end;
----================测试运行结果：雇员不存在！错误为：100ORA-01403: 未找到任何数据

--自定义异常
raise_application_error(error_number,error_message);

--实例
declare 
  ....
  begin 
  ....
  if v_com is null then
     raise_application_error(-20001,'该雇员无补助');
     end if;
end;

/*
====================================显示游标================================
*/
--1.声明游标
cursor cursor_name [(parameter [,parameter]...)]
[return return_type] is select_statement;
--2.打开游标
open cursor_name[(parameters)];
--3.提取游标
fetch cursor_name into variables;
--4.关闭游标
close cursor_name;

--------------------实例6------------------------
declare 
   name employee.ename%type;
   sal employee.sal%type;   --定义两个变量来存放ename和sal的内容
   cursor emp_cursor is select ename,sal from employee;
begin
   open emp_cursor;
   loop
        fetch emp_cursor into name,sal;
        exit when emp_cursor%notfound;
        dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员：'||name|| 'oooo' || sal);
    end loop;
    close emp_cursor;
end;

--===执行成功输出：
/*
第1个雇员：张一3000
第2个雇员：张二5000
第3个雇员：张三8000
第4个雇员：张四10000
第5个雇员：张五6300

*/

--使用显示游标删除或者更新
cursor cursor_name id select_statement for update [of columns];
--在使用for update 子句声明游标时，可以使用下面语法更新行
update table_name set column_name=column_value where current of cursor_name;

--根据编号查询雇员的姓名
declare 
       v_ename varchar2(20);
begin
  select ename into v_ename from employee where empno=&empno;
  dbms_output.put_line('雇员的名字是：'||v_ename);
end;

select * from employee;