Oracle —— PL/SQL 语句

1. 语法结构：

• 在Oracle中，提供了PL/SQL语句块，在执行的时候，可以把语句块作为整体提交到服务器
• 可以把一些语句作为整体执行

declare---声明部分(可以省略)
变量名 数据类型（长度）;
begin--语句块
语句块;
exception--异常处理（可以省略）
when 异常 then 异常处理;
end;

• 输出数据

begin
  DBMS_OUTPUT.PUT_LINE('hello pl/sql');--控制台打印输出
end;

2. 数据类型

• 变量定义语句：变量名 数据类型（数据范围）:= 赋予的值 ;
• pl/qsl变量的数据类型：
  • char
  • varchar2
  • number
  • date
  • %type
  • %rowtype

declare
  v_empno number(10):=1000;
  v_empno1 number(10) default 2000;
  v_empno2 number(10);
  PI constant number(3,2):=3.14;--常量
begin
  dbms_output.put_line('v_empno的值为'||v_empno);
  dbms_output.put_line('PI的值为'||PI);
exception
  when others then
    dbms_output.put_line('出现异常');
end;

• 查询员工编号是1234的员工姓名

select ename from emp where empno=1234;

• 用pl/sql语句块

declare
v_empno number(10):=1234;
v_ename varchar2(20);
begin
  select ename into v_ename from emp where empno=v_empno;
  dbms_output.put_line('员工姓名为：'||v_ename);  
end;

（1）%type：列类型

• 列类型：变量名 表名.列名%type;

select * from emp;

declare
v_empno emp.empno%type:=1234;
v_ename emp.ename%type;
begin 
  select ename into v_ename from emp where empno=v_empno;
  dbms_output.put_line('员工姓名为：'||v_ename);  
end;

declare
v_empno emp.empno%type:=1234;
v_ename emp.ename%type;
v_job   emp.job%type;
v_mgr   emp.mgr%type;
v_sal   emp.sal%type;
v_deptno emp.deptno%type;
begin 
  select ename,job,mgr,sal,deptno 
  into v_ename,v_job,v_mgr,v_sal,v_deptno 
  from emp where empno=v_empno;
  dbms_output.put_line('员工姓名为：'||v_ename);  
  dbms_output.put_line('员工职位为：'||v_job); 
  dbms_output.put_line('员工领导编号为：'||v_mgr); 
  dbms_output.put_line('员工薪资为：'||v_sal);
  dbms_output.put_line('员工部门编号为：'||v_deptno);  
end;

（2）%rowtype：行类型

• 行类型：变量名 表名%rowtype;
  • 把表中的一行数据整体赋值给一个变量
• 查询员工编号是1234的员工信息：ename,job,mgr,sal,deptno

declare
v_empno emp.empno%type:=1234;
v_emp emp%rowtype;--行类型：变量名 表名%rowtype;把表中的一行数据整体赋值给一个变量
begin 
  select * into v_emp
  from emp where empno=v_empno;
  dbms_output.put_line('员工姓名为：'||v_emp.ename);  
  dbms_output.put_line('员工职位为：'||v_emp.job); 
  dbms_output.put_line('员工领导编号为：'||v_emp.mgr); 
  dbms_output.put_line('员工薪资为：'||v_emp.sal);
  dbms_output.put_line('员工部门编号为：'||v_emp.deptno);  
end;

3.流程控制语句

（1）if语句

• 语法：

---if..else语法：
--单分支
if 条件 then
   语句块；
end if;

--双分支
if 条件 then 语句块；
else         语句块;
end if;

--多分支
if    条件 then 语句块;
elsif 条件 then 语句块;
elsif 条件 then 语句块;
elsif 条件 then 语句块;
elsif 条件 then 语句块;
……
else 语句块;
end if;

• 对某一编号的员工实现加薪操作：加薪原则：
• 如果原工资小于1000，则加200元，如果原工资大于等于1000且小于2000，则加150元，否则加100元

declare
  v_sal emp.sal%type;--列类型：v_sal变量与emp表的sal列数据类型一致
begin 
  select sal into v_sal from emp where empno=7369;
  --对v_sal变量做操作
  if v_sal<1000 then
    --薪资+200
    update emp set sal=sal+200 where empno=7369;
  elsif v_sal>=1000 and v_sal<2000 then
    update emp set sal=sal+150 where empno=7369;
  else 
    update emp set sal=sal+100 where empno=7369;
  end if;
end;

（2）case...when...then 分支语句

• 语法1：变量范围判断

case when 条件(v_sal<1000) then 语句块； 
     when 条件 then 语句块； 
     when 条件 then 语句块； 
……
else 语句块；
  end case;

• 语法2：变量名=具体某些值时

case 变量名 when 值 then 语句块；
            when 值 then 语句块；
            when 值 then 语句块；
else 语句块;
end case;

• 判断语句

---加薪
declare
  v_sal emp.sal%type;--列类型：v_sal变量与emp表的sal列数据类型一致
begin 
  select sal into v_sal from emp where empno=7369;
  --对v_sal变量做操作
  case when v_sal<1000 then
    update emp set sal=sal+200 where empno=7369;
       when v_sal>=1000 and v_sal<2000 then
    update emp set sal=sal+150 where empno=7369;
       else 
    update emp set sal=sal+100 where empno=7369;
  end case;
end; 

• 变量名等于具体值时

declare
  v_sal emp.sal%type;--列类型：v_sal变量与emp表的sal列数据类型一致
begin 
  select sal into v_sal from emp where empno=7369;
  --对v_sal变量做操作
  case v_sal when 1000 then
    update emp set sal=sal+200 where empno=7369;
             when 1300 then
    update emp set sal=sal+150 where empno=7369;
             else 
    update emp set sal=sal+100 where empno=7369;
  end case;
end; 

（3）loop循环

• 语法

loop
  循环语句;
  exit when 循环终止条件;
end loop；

• 向表中循环插入100条记录

create table test_loop1(
      id number(5),
      name varchar2(20)
);
declare
v_count number(5):=1;
begin
  loop
    insert into test_loop1 values(v_count,'name'||v_count);
    v_count:=v_count+1;
    exit when v_count>100;
  end loop;
end;

select * from test_loop1;

（4）while循环

• 语法

while 循环条件
      loop 
      end loop；

• 示例

declare
v_count number(5):=1;
begin
  while v_count<=100
  loop
    insert into test_loop1 values(v_count,'name'||v_count);
    v_count:=v_count+1;
  end loop;
end;

（5）for循环

• 语法

for 变量名 in 1..100
        loop 语句块
        end loop;

• 示例

begin
  for v_count in 1..100
  loop
    insert into test_loop1 values(v_count,'name'||v_count);
  end loop;
end; 

（6）goto语句/null语句

• 无条件跳转：goto <<标签名>>

declare
    v_sal emp.sal%TYPE;
begin
  select sal into v_sal from emp where empno=7369;
  IF  v_sal <2000  then
      goto UPDATION;--无条件跳转到某一个标签
  else
      null;--空语句，什么都不执行
  end if;
  --……省略了一些语句
  <<UPDATION>>  --<<标签名>>
  update emp set sal=2000 where empno=7369;
end;

4.异常处理

• 系统定义异常
  • NO_DATA_FOUND：查不到数据
  • TOO_MANY_ROWS：查询多行异常
  • ZERO_DIVIDE：除零异常```
• 语法:系统定义异常

declare
begin
exception
  when 异常名 then 异常处理;
  when others then 异常处理;
end;

（1）除零操作异常

• &后的数据表示需要输入的数据

declare 
 v_num1 number(5);
 v_num2 number(5);
 v_num3 number(5);
begin
  v_num1:=&被除数;
  v_num2:=&除数;
  v_num3:=v_num1/v_num2;
  dbms_output.put_line(v_num3);
exception
 when zero_divide then
   dbms_output.put_line('除数为0异常');
 when others then
   dbms_output.put_line('其他异常'); 
end;

（2）找不到数据异常

declare
v_emp emp%rowtype;--行变量
v_empno emp.empno%type;--列变量
begin
  v_empno:=&编号;
  select * into v_emp from emp where empno=v_empno;
  dbms_output.put_line('员工姓名为：'||v_emp.ename);
  dbms_output.put_line('员工职位为：'||v_emp.job);
exception
  when no_data_found then
    dbms_output.put_line('找不到数据'); 
  when others then
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);      
end;

（3）找到多行异常

declare
v_emp emp%rowtype;
begin
  select * into v_emp from emp;
  dbms_output.put_line('员工姓名为：'||v_emp.ename);
  dbms_output.put_line('员工职位为：'||v_emp.job);
exception
  when no_data_found then
    dbms_output.put_line('找不到数据'); 
  when too_many_rows then
    dbms_output.put_line('返回多行');  
  when others then---其他异常一般写在异常处理的末尾
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);  
end;

（4）用户自定义异常

• 操作步骤
  • 在declare中声明异常:异常名 exception;
  • 在begin中抛出异常：raise 异常名;
  • 在exception中处理异常：when 异常名 then 异常处理;

declare
exc1 exception;--声明一个异常变量;
v_emp emp%rowtype;--行变量
v_count number(2):=0;--员工编号为某个值的员工数量
begin
 select count(*) into v_count from emp where empno=1111;
 if v_count<1 then
   raise exc1;--抛出数据找不到的异常
 end if;
 --正常的业务逻辑
 select * into v_emp from emp where empno=1111;
 dbms_output.put_line('员工姓名为：'||v_emp.ename);
 dbms_output.put_line('员工职位为：'||v_emp.job);
exception 
  when exc1 then
   dbms_output.put_line('员工不存在');
   --raise_application_error(-20001,'员工找不到异常');
end;

select * from emp where empno=1234;
select count(*) from emp where empno=1234;