oracle数据库——plsql基本语法 流程控制及练习 循环结构

1. plsql基本语法
   plsql 就是在sql语句中增加了变量，流程控制，函数，使得sql语句具有更强大的功能
   基本结构：

    declare
    定义变量
    begin
    select * from 表；--执行会报错，可以增删改，但查的时候要用into
    select xxx into v_xxx from 表 where xxx;
    end;
   

emp%rowtype 行类型
emp.sal%type 和emp表中sal列的类型一样
record 记录集类型

例如：

直接赋值
  declare 
    v_name varchar2(50);
  begin
     v_name:='熊二';
     dbms_output.put_line(v_name);
  end;
  
  用'/'来执行

  打开输出功能：
set serveroutput on;

根据查询结果来赋值
declare 
    v_name varchar2(50);
  begin
     select ename into v_name from emp where empno=7782;
     dbms_output.put_line(v_name);
  end;

  根据键盘输入来赋值
  declare 
    v_name varchar2(50);
  begin
     v_name:='&输入姓名';
     dbms_output.put_line(v_name);
  end;
  
  
  输入部门编号，查询部门工资最高的员工姓名和职位
  declare 
    v_deptno number(5);
    v_ename varchar2(20);
    v_job varchar2(30);
  begin
     v_deptno:=&输入部门编号;
     select ename,job  into v_ename,v_job from emp where sal=
     (
         select max(sal) from emp where deptno=v_deptno
     );
     dbms_output.put_line(v_deptno||'部门工资最高的员工是：'||v_ename||'  职位:'||v_job);
  end;
  

  
  根据员工编号去查名字
写法1：
   declare 
    v_name varchar2(50);
  begin
     select ename into v_name from emp where empno=&empno;
     dbms_output.put_line(v_name);
  end;
  
  写法2：
参照某一列的数据类型去定义数据类型
   declare 
    v_name emp.ename%type;
  begin
     select ename into v_name from emp where empno=&empno;
     dbms_output.put_line(v_name);
  end;
  
  参照某一行的数据类型，有多少列就定义多少变量
  declare 
     v_emprow emp%rowtype;
   begin
   select * into v_emprow from emp where empno=&empno;
   dbms_output.put_line(v_emprow.ename || v_emprow.job);
   end;
  
  
   定义一个数据类型，参照其他表中的数据类型
  declare 
     type MyType is record
     (
         t_empno emp.empno%type,
         t_ename emp.ename%type,
         t_job emp.job%type,
         t_sal emp.sal%type
     );
     v_emprow MyType;
   begin
   select empno,ename,job,sal into v_emprow from emp where empno=&empno;
   dbms_output.put_line(v_emprow.t_ename || v_emprow.t_job);
   end;

2.流程控制

--if..else..
declare 
      v_job emp.job%type;
   begin
       select job into v_job from emp where empno=&empno; 
       if v_job='MANAGER' then
          dbms_output.put_line('是经理');
       else
          dbms_output.put_line('不是经理');
       end if;
   end;
--if..elsif..else..   
declare 
     v_sal emp.sal%type;
   begin
      select sal into v_sal from emp where empno=&no;
      if v_sal <=1000 then
         dbms_output.put_line('蓝领');
       elsif v_sal <=2000 then
         dbms_output.put_line('白领');
       else
         dbms_output.put_line('金领');
       end if;
      end;


--case等值

declare 
       v_job emp.job%type;
      begin
          select job into v_job from emp where empno=&no;
          case
            v_job
          when 'CLERK' then   dbms_output.put_line('科员');
          when 'ANALYST' then   dbms_output.put_line('分析师');
          when 'SALESMAN' then   dbms_output.put_line('销售员');
          when 'MANAGER' then   dbms_output.put_line('经理');
          when 'PRESIDENT' then   dbms_output.put_line('董事长');
          else dbms_output.put_line('不明职位');
          end case; 
      end;
   
   
 --case条件
    declare 
     v_sal emp.sal%type;
   begin
      select sal into v_sal from emp where empno=&no;
        case
          when v_sal>=3000 then dbms_output.put_line('金领');
          when v_sal>=2000 then dbms_output.put_line('白领');
          when v_sal>=1000 then dbms_output.put_line('蓝领');
           else dbms_output.put_line('无领');
        end case;        
      end;

练习

1.输入员工编号，如果工资高于他所在部门的平均工资，输出工资较高，否则输出工资较低   
declare
v_sal emp.sal%type;
v_deptno emp.deptno%type;
v_avgsal emp.sal%type;
begin
  select sal,deptno into v_sal,v_deptno from emp where empno=&no;
  select avg(sal) into v_sal from emp where deptno=v_deptno;
  dbms_output.put_line(v_sal||''||v_avgsal);
  if v_sal < v_avgsal then dbms_output.put_line('工资较低');
  else dbms_output.put_line('工资较高');
  end if;
  end;

方法2：
declare
v_sal emp.sal%type;
v_empno emp.empno%type;
v_avgsal emp.sal%type;
begin
  v_empno:=&empno;
select sal into v_sal from emp where empno=v_empno;
select avg(sal) into v_avgsal from emp where deptno= 
(
select deptno from emp where empno=v_empno
);
 if v_sal < v_avgsal then dbms_output.put_line('工资较低');
  else dbms_output.put_line('工资较高');
  end if;
end;
2.输入一个员工编号，输出他入职的季节

declare
v_hiredate emp.hiredate%type;
v_month number(4);
begin
select hiredate into v_hiredate from emp where empno=&no;
select to_number(to_char(v_hiredate,'mm')) into v_month from dual;
case 
when v_month >3 and v_month<6 then dbms_output.put_line('春');
when v_month >6 and v_month<9 then dbms_output.put_line('夏');
when v_month >9 and v_month<12 then dbms_output.put_line('秋');
else dbms_output.put_line('冬');
end case;
end;


方法2：
declare
v_month number(4);
begin
select to_number(to_char(hiredate,'mm')) into v_month from emp where empno=&no;
case 
when v_month between 1 and 3 then dbms_output.put_line('春');
when v_month between 4 and 6 then dbms_output.put_line('夏');
when v_month between 7 and 9 then dbms_output.put_line('秋');
when v_month between 10 and 12 then dbms_output.put_line('冬');
end case;
end;

3.循环结构
3.1.loop
语法：
LOOP
语句;
EXIT WHEN <条件>
END LOOP;

--输出1到10
declare
v_i number(5);
begin
v_i:=1;
loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
exit when v_i>10;
end loop;
end;
--如果要使用put不换行的输出，要结合put_line,否则可能看不到输出结果
declare
v_i number(5);
begin
v_i:=1;
loop
dbms_output.put(v_i);
v_i:=v_i+1;
exit when v_i>10;
end loop;
dbms_output.put_line(' ');
end;

3.2.WHILE LOOP
语法：
WHILE <条件>
LOOP
语句;
END LOOP;

--与loop不同的是while loop把循环条件放到前面来
declare
v_i number(5);
begin
v_i:=1;
while v_i<=10 loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
end loop;
end;

3.3.FOR
语法：
FOR <循环变量> IN 下限…上限
LOOP
语句;
END LOOP;

--从1循环到10
--当不需要定义变量时可以不写declare
begin
for v_i in 1 .. 10
loop
dbms_output.put(v_i);
end loop;
dbms_output.put_line('');
end;

练习：

--输出九九乘法表

begin
  for v_i in 1 .. 9
    loop
    for v_j in 1 .. v_i
      loop
        dbms_output.put(v_i||'*'||v_j||'='||v_i*v_j||' ');
        end loop;
        dbms_output.put_line('');
        end loop;
        end;
  

--输出金字塔

               *
              ***
             *****
            *******
           *********
          ***********
 begin
 for v_i in 1 .. 6 loop
 dbms_output.put_line(lpad(lpad('*',2*v_i-1,'*'),6+v_i,' '));         
 end loop;         
 end;