Oracle PL/SQL语言函数、匿名语句及循环

一、自定义函数

格式：

create or replace function 函数名（参数名 参数类型...）

    return  返回值类型

    as 

                  xx vachar2(20)                 --申明变量

                  begin                                --开始

                  --编写逻辑位置

    return xxxx

    end  函数名；                                --结束

例1：将名为WARD员工的工资和所有的员工的工资进行比较。

 --自定义函数
 create or replace function fn_emp_sal(asal in number,bname in varchar2)
 return varchar2 --返回值
 as                
          --申明变量
          vreturn varchar2(20);
          vsal emp.sal%type;    --vsal的数据类型与sal的数据类型一样
          begin
          --写逻辑的位置
          --将查询出的sal赋值给vsal
          select sal into vsal from emp where ename=bname;
          if asal>vsal then
             vreturn:='工资比'||bname||'高'||(asal-vsal);
          elsif asal<vsal then
             vreturn:='工资比'||bname||'低'||(vsal-asal);
          else
             vreturn:='工资一样';
          end if;
 return vreturn;
 end  fn_emp_sal;              --结束 函数名，注意要加;

之后编译成功则显示：

进行查询：

select ename,sal,fn_emp_sal(sal,'WARD')from emp;

 执行结果：

二、匿名语句块 

      格式：

declare 

--声明变量

xx varchar2(20);

begin--开始

---编写逻辑位置

end;--结束

例1：工资小于3000的员工提薪3000。

 --匿名语句块
 declare 
       --声明变量
       vsal number;
       vename varchar2(20);
    begin 
       vename:='SMITH';     --静态写入
       --若想从控制台动态输入数据，则改成 vename:=&ename;
       --输入：'SMITH'
       select sal into vsal from emp where ename=vename;
       if vsal<3000 then 
          update emp set sal=3000 where ename=vename;
       end if;
    end;

 执行结果：

 800->3000：

        

如将更新的数据添加文本中，可以再新建一个日志表和序列，增加一个触发器去记录更新的数据。

（1）增加一个日志表为t_logs：

（2）增加一个序列为log_seq:

（3）为表t_logs增加一个触发器log_xx，创建（序列中的主键）：

（4）在上一段代码的第12行添加：

 insert into t_logs(txt) values(vename||'工资增加了'||(3000-vsal));

（5）执行结果：

 

例2：对收入低于1600 发奖金300  1600-2500发奖金200 2500-5000发奖金100。

declare
    vename varchar2(20);
    vsal_comm emp.sal%type;
    vcomm emp.comm%type;
    vmsg varchar2(20);
    vrow emp%rowtype;---行变量 相当于Java的Object
  begin
    vename:='SMITH';
    select * into vrow from emp where ename=vename;
    
    --奖金
    if vrow.comm is null then
    vcomm:=0;
    else
    vcomm:=vrow.comm;
    end if;
    
    --收入
    vsal_comm:=vrow.sal+vcomm;
    
    if vsal_comm<1600 then
      vmsg:='发奖金300';
    elsif vsal_comm>1600 and vsal_comm<2500 then
      vmsg:='发奖金200';
    elsif vsal_comm>2500 and vsal_comm<5000 then
      vmsg:='发奖金100';
    else
      vmsg:='不发';
    end if;
       DBMS_OUTPUT.PUT_LINE(vename||'过节费'||vmsg); --控制台输出
end;

点击查看->DBMS输出->点击+，连接该数据库，则会显示：

 

三、循环

1、loop 循环
例：循环输出1-10，相当于do...while

declare 
    i number(2):=1;
  begin 
    loop
    exit when i>10; ---循环结束条件
    DBMS_OUTPUT.PUT_LINE(i);  --控制台输出
    i:=i+1;
    end loop;
  end;

2、 while loop 循环

例：循环输出1-7，相当于while。

declare 
   i number(2):=1;
   begin 
   while i<7 loop
   DBMS_OUTPUT.PUT_LINE(i);
   i:=i+1;
   end loop;
 end;

应用：例如插入1000条数据。

---恢复内容结束---

一、自定义函数

格式：

create or replace function 函数名（参数名 参数类型...）

    return  返回值类型

    as 

                  xx vachar2(20)                 --申明变量

                  begin                                --开始

                  --编写逻辑位置

    return xxxx

    end  函数名；                                --结束

例1：将名为WARD员工的工资和所有的员工的工资进行比较。

 --自定义函数
 create or replace function fn_emp_sal(asal in number,bname in varchar2)
 return varchar2 --返回值
 as                
          --申明变量
          vreturn varchar2(20);
          vsal emp.sal%type;    --vsal的数据类型与sal的数据类型一样
          begin
          --写逻辑的位置
          --将查询出的sal赋值给vsal
          select sal into vsal from emp where ename=bname;
          if asal>vsal then
             vreturn:='工资比'||bname||'高'||(asal-vsal);
          elsif asal<vsal then
             vreturn:='工资比'||bname||'低'||(vsal-asal);
          else
             vreturn:='工资一样';
          end if;
 return vreturn;
 end  fn_emp_sal;              --结束 函数名，注意要加;

之后编译成功则显示：

进行查询：

select ename,sal,fn_emp_sal(sal,'WARD')from emp;

 执行结果：

二、匿名语句块 

      格式：

declare 

--声明变量

xx varchar2(20);

begin--开始

---编写逻辑位置

end;--结束

例1：工资小于3000的员工提薪3000。

 --匿名语句块
 declare 
       --声明变量
       vsal number;
       vename varchar2(20);
    begin 
       vename:='SMITH';     --静态写入
       --若想从控制台动态输入数据，则改成 vename:=&ename;
       --输入：'SMITH'
       select sal into vsal from emp where ename=vename;
       if vsal<3000 then 
          update emp set sal=3000 where ename=vename;
       end if;
    end;

 执行结果：

 800->3000：

        

如将更新的数据添加文本中，可以再新建一个日志表和序列，增加一个触发器去记录更新的数据。

（1）增加一个日志表为t_logs：

（2）增加一个序列为log_seq:

（3）为表t_logs增加一个触发器log_xx，创建（序列中的主键）：

（4）在上一段代码的第12行添加：

 insert into t_logs(txt) values(vename||'工资增加了'||(3000-vsal));

（5）执行结果：

 

例2：对收入低于1600 发奖金300  1600-2500发奖金200 2500-5000发奖金100。

declare
    vename varchar2(20);
    vsal_comm emp.sal%type;
    vcomm emp.comm%type;
    vmsg varchar2(20);
    vrow emp%rowtype;---行变量 相当于Java的Object
  begin
    vename:='SMITH';
    select * into vrow from emp where ename=vename;
    
    --奖金
    if vrow.comm is null then
    vcomm:=0;
    else
    vcomm:=vrow.comm;
    end if;
    
    --收入
    vsal_comm:=vrow.sal+vcomm;
    
    if vsal_comm<1600 then
      vmsg:='发奖金300';
    elsif vsal_comm>1600 and vsal_comm<2500 then
      vmsg:='发奖金200';
    elsif vsal_comm>2500 and vsal_comm<5000 then
      vmsg:='发奖金100';
    else
      vmsg:='不发';
    end if;
       DBMS_OUTPUT.PUT_LINE(vename||'过节费'||vmsg); --控制台输出
end;

点击查看->DBMS输出->点击+，连接该数据库，则会显示：

 

三、循环

1、loop 循环
例：循环输出1-10，相当于do...while

declare 
    i number(2):=1;
  begin 
    loop
    exit when i>10; ---循环结束条件
    DBMS_OUTPUT.PUT_LINE(i);  --控制台输出
    i:=i+1;
    end loop;
  end;

2、 while loop 循环

例：循环输出1-7，相当于while。

declare 
   i number(2):=1;
   begin 
   while i<7 loop
   DBMS_OUTPUT.PUT_LINE(i);
   i:=i+1;
   end loop;
 end;

应用：例如插入1000条数据。

declare
   i number(4):=1;
  begin 
   while i<1000 loop
   insert into emp(enmae) values('老王'||i);
   i:=i+1;
   end loop;
  end;

3、for in 循环

已知循环次数的循环控制语句。

declare 
   begin
      for i in 40..50 loop
      DMBS_OUTPUT.PUT_LINE(i);
      --for in 语句自动给i+1，故这里是个空操作，加不加null都可。
      end loop；
   end；