oracle学习第六天：pl/sql01

pl/sql

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

程序语法

declare
    说明部分 (变量说明,游标申明,例外说明)
begin
    语句序列  (DML语句)
exception
    例外处理语句
end;

• 例如输入hello world

declare
    begin
      DBMS_OUTPUT.PUT_LINE('hello world');
end;

Oracle高级PLSQL变量

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

变量

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

• 定义语法：
  varl char(15); Psal number(9,2)
  说明变量名、数据类型和长度后用分号结束说明语句。

每个声明后加分号

declare 
 pname varchar2(10);
begin
 pname:='zhangsan';
 dbms_output.put_line(pname);
end;

常量

常量定义：married boolean:=true;

declare 
 page number(3):=52;
begin
 dbms_output.put_line(page);
end;

引用变量

Myname emp.ename%type;
引用型变量，即Myname的类型与emp表中ename列的类型一样在sql中使用into来赋值

declare
    myname emp.ename%type;
begin
    select t.ENAME into myname from emp t where t.EMPNO=7369;
    DBMS_OUTPUT.PUT_LINE(myname);
end;

记录性变量

相当于Java中的一个对象

declare
    p EMP%rowtype;
begin
    select * into p from EMP t where t.EMPNO=7369;
    DBMS_OUTPUT.PUT_LINE(p.ENAME||''||p.EMPNO);
end;

PLSQL的if分支

语法

• 语法1：
  if 条件 then 语句1；
  语句2;
  end if;
• 语法2：
  if 条件 then 语句序列1；
  else 语句序列2；
  end if;
• 语法3：
  if 条件 then 语句;
  elseif 条件then 语句;
  else 语句；
  end if;

实例代码

• 语法1

declare 
 pro number(4):=#
 begin
   if pro<5 then
   dbms_output.put_line('编号小于5');
   end if;
   end;

• 语法2

declare
    pro number(4):=#
begin
    if pro<5 then DBMS_OUTPUT.PUT_LINE('编号小于5');
    else  DBMS_OUTPUT.PUT_LINE('编号大于5');
    end if;
end;

• 语法3

declare
  pro number(4) := #
begin
  if pro = 1 then
    dbms_output.put_line('我是1');
  elsif pro = 2 then
    dbms_output.put_line('我是2');
  end if;
end;

LOOP循环

常用第二种

语法

• 语法1：
  while total<=25000 loop
  total:=total+salary;
  end loop;
• 语法2：
  loop
  exit when total>25000；
  total:=total+salary;
  end loop;

示例

• 语法1（计算1-100的和）

declare
  total number(20) := 0;
  i     number(4) := 0;
begin
  while i <= 100 loop
    total := total + i;
    i     := i + 1;
  end loop;
  DBMS_OUTPUT.PUT_LINE(total);
end;

• 语法2(计算1-100的和)

declare
  num number(20) := 0;
  i   number(4) := 0;
begin
  loop
    exit when i > 100;
    num := num + i;
    i   := i + 1;
  end loop;
  dbms_output.put_line(num);
end;

游标

游标相当于Java对象的集合。它也叫光标，游标可存储查询返回的多条数据。

语法

• CURSOR 游标名 [(参数名 数据类型)] IS SELECT 语句；

例如：cursor col is select ename from EMP ;

游标的使用步骤：

• 打开游标 open cl; (打开游标执行查询)
• 取一行游标的值： fetch cl into pjob; (取一行到变量中)
• 关闭游标：close cl;(关闭游标释放资源)

示例

• 使用游标方式输出emp表的员工编号和姓名

declare
  prec emp%rowtype; --定义记录型变量
  cursor c1 is
    select * From emp; --定义游标
begin
  open c1; --打开游标
  loop
    fetch c1
      into prec; --从游标中取值，取值后游标会自动向下移动一步
    exit when c1%notfound;
    dbms_output.put_line(prec.empno || '' || prec.ename);
  end loop; --结束循环
  close c1; --关闭游标
end; --关闭程序

• 按员工的工种涨工资，总裁1000，经理800，其他人 400

declare 
  --定义光标
  cursor cemp is select myempno,job from myemp;
  pempno myemp.myempno%type;
  pjob   myemp.job%type;
begin
  rollback;  
  --打开光标
  open cemp;  
  loop
       --取一个员工
       fetch cemp into pempno,pjob;
       exit when cemp%notfound;
       --判断职位
       if pjob = 'PRESIDENT' then update myemp set sal=sal+1000 where myempno=pempno;
          elsif pjob = 'MANAGER' then update myemp set sal=sal+800 where myempno=pempno;
          else update myemp set sal=sal+400 where myempno=pempno;
       end if;
  end loop;
  --关闭光标
  close cemp;
  --提交  ----> why?: 事务 ACID
  commit;
  dbms_output.put_line('完成');
end;

declare
  prec myemp%rowtype;
  cursor c1 is select *From myemp;
  addsal number(4);
 begin
   rollback;
   open c1;
   loop
     fetch c1 into prec;
     exit when c1%notfound;
     if prec.job='PRESIDENT' then addsal :=1000;
     elsif prec.job='MANAGER' then addsal :=800;
     else addsal :=400;
     end if;
     update myemp t set t.sal=t.sal+addsal where t.myempno=prec.myempno;
     end loop;
     close c1;
     commit;
     dbms_output.put_line('完成');
     end;

• 写一段PL/SQL程序，为部门号为10的员工涨工资-

declare
  cursor c1(dno myemp.deptno%type) is
    select * from myemp t where t.deptno = dno;
  prec myemp%rowtype;
begin
  open c1(10);
  loop
    fetch c1
      into prec;
    exit when c1%notfound;
    update myemp t set t.sal = t.sal + 1000 where t.myempno = prec.myempno;
  end loop;
  close c1;
  commit;
end;

例外(exception)

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。
系统定义的例外

• not data found (没有找到数据)
• too many rows (select 匹配多行数据)
• zero divide (被零除)
• value error (算术或转换错误)
• timeout on resource (等待资源超时)

• 范例1 ：写出被0 除的例外的plsql程序

declare
    pnum number(4) :=5;
    begin
    pnum :=pnum/0; --发生异常
    exception  --处理异常
    when zero_divide then
    DBMS_OUTPUT.PUT_LINE('被零除');
    when value_error then
    DBMS_OUTPUT.PUT_LINE('数值转换错误');
    when others then
        DBMS_OUTPUT.PUT_LINE('其他异常');
end;

• 查询部门编号是50的员工

declare
    prec myemp%rowtype;
    cursor c1 is select *From MYEMP where DEPTNO='50';
    no_data exception ; --异常类型的定义
    begin
    open c1;
    loop
        fetch c1 into prec;
        if c1%notfound then --抛出异常
            raise no_data;
        end if;
    end loop;
    close  c1;
    exception --处理异常
    when no_data then
    DBMS_OUTPUT.PUT_LINE('没有该员工');
    when others then
        DBMS_OUTPUT.PUT_LINE('其他异常');
end;

存储过程和游标结合使用

查找生日是当天的客户(t_customer)，将相关信息(t_customer & t_policy)插入到短信提醒表中(t_message)，请使用游标（cursor），逐单提交（commit）。

create or replace procedure insertMiddleTable as
    cursor resultTable is
        with su as (
            select *from T_CUSTOMER where to_char(BIRTHDAY,'yyyy-MM-dd')=to_char(SYSDATE,'yyyy-MM-dd')
        )
        select su.CUSTOMER_ID CUSTOMER_ID,su.CUSTOMER_NAME,su.BIRTHDAY,su.PHONE,t.POLICY_NO
        from su left join T_POLICY t on su.CUSTOMER_ID=t.CUSTOMER_ID;
    begin
        for x in resultTable loop
            insert into T_MESSAGE(message_id, customer_id, customer_name, birthday, phone, policy_no)
            VALUES (S_MESSAGE_ID.nextval,x.CUSTOMER_ID,x.CUSTOMER_NAME,x.BIRTHDAY,x.PHONE,x.POLICY_NO);
            end loop;
        commit ;
    end insertMiddleTable;