Orcale_04 使用PL/SQLF&自定义函数

最新推荐文章于 2023-03-15 09:30:32 发布

喔喔。

最新推荐文章于 2023-03-15 09:30:32 发布

阅读量95

点赞数 1

文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/weixin_66616840/article/details/124512546

版权

1.PL/SQL概述.

PL/SQL是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言(过程化SQL语言)；
PL/SQL是Oracle对SQL语言的扩展，扩展部分内容：变量、常量、存储过程、程序包、游标、函数、触发器、条件控制、循环控制等；
PL/SQL与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性；
PL/SQL语法特点：PL/SQL程序都是以块（block）为基本单位，整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以 begin开头，以end结尾）和异常处理部分（以Exception开头）。其中执行部分是必须的，其他两个部分可选。无论PL/SQL程序段的代码量有多大，其基本结构就是由这三部分组成。

2.变量和常量.

2.1变量.

2.1.1语法结构.

declare
变量名 数据类型；
begin 
       执行代码块内容；   
end；

2.1.2赋值方式.

方式一：“:=”
方式二：“select into 语句”

2.1.3案例演示.

根据给定的员工编号查询员工名字，并输出。

declare
sid number(7);          --声明变量 sid 用来保存员工编号(未初始化值)
sname varchar(10);  --声明变量 sname 用来保存查询出来的员工名字，未初始化值
begin
sid:=7499;                 --通过“:=”给变量 sid 赋值
select ename into sname from emp where empno=sid;           --通过 select into 语句给 sname 变量赋值
dbms_output.put_line('要查询的名字是：'||sname);              --打印出结果。
end;

在声明变量的同时也可以给变量赋值，如：sid number(7):=7499;

2.2常量

2.2.1语法结构.

decare
变量名1 constant 数据类型 := 值；
begin
执行代码块内容；
end；

2.2.2常量和变量的区别.

常量声明的时候要在数据类型前面加 constant 关键字，表示常量的意思；
常量必须初始值，且值不能被修改；

2.2.3案例演示.

根据给定的员工编号查询员工名字，并输出。

declare
sid constant number(7) := 7499;     --声明常量 sid 用来保存员工编号，并且初始化值
sname varchar(10);           --声明变量 sname 用来保存查询出来的员工名字，未初始化值
begin
select ename into sname from emp where empno=sid;          --通过 select into 语句给 sname 变量赋值
dbms_output.put_line('要查询的名字是：'||sname);            --打印出结果。
end;

3.%type和%rowtype.

3.1 %type.

作用：用来引用数据表里面某个字段的数据类型；
通俗解释：当我们想要声明一个变量来保存表内某个字段值但是又不知道该变量用什么数据类型的时候，我们就可以引用表内该字段的数据类型来作为变量的数据类型。

案例演示：根据员工编号查询出员工的姓名和入职日期。

declaresid number(7) :=7499;
sname varchar2(10);
mydate emp.hiredate%type;--通过%type引用 emp 表里面 hiredate 字段的数据类型。
begin
       select ename,hiredate into sname,mydate from emp
       whereempno=sid;
       dbms_output.put_line('名字是：'||sname||'，入职日期是：'||mydate);
end;

3.2 %rowtype.

作用：可以声明一个基于表的行对象，也称为记录对象。

案例：根据员工编号查询出名字和岗位(用一个变量保存)。

declaresid number :=7499;
mysum emp%rowtype;
begin
       select * into mysum from emp where empno=sid;
       dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
end;

4.控制结构.

4.1条件控制语句

4.1.1 if语句语法结构：

if(条件1) then
条件1成立执行代码块
elsif(条件2) then
条件2成立执行代码块
.......
else
以上条件都不成立执行代码块
end if;

案例演示：根据学号查询薪水，如果薪水大于3500则“要纳税”，小于则“要努力”，等于则“刚刚好”。

declaresid number:=7499;
mysal emp.sal%type;
begin
     select sal into mysal from emp where empno=sid;
     if(mysal>3500) then
        dbms_output.put_line('要纳税');
    elsif(mysal=3500) then
       dbms_output.put_line('刚刚好');
    else
      dbms_output.put_line('要努力');
    end if;
end;

4.1.2 case语句语法结构：

begin
case '&变量'
when '值1' then 执行代码块1；
when '值2' then 执行代码块2；
when '值3' then 执行代码块3；
when '值4' then 执行代码块4；
..............
else 当以上值都不满足的时候执行代码块
end case;
end;

“&”：作用相等于Java里面的Scanner；

案例演示：输入等级，如果等级是 A，则输出：优异A；如果等级是 B，则输出：优异B；如果等级是 C，则输出：优异；否则，输出：成绩太差了！

begin
    case '&scode'
    when 'A' then dbms_output.put_line('优异A');
    when 'B' then dbms_output.put_line('优异B');
    when 'C' then dbms_output.put_line('优异C');
    when 'D' then dbms_output.put_line('优异D');
    else dbms_output.put_line('成绩太差了，来个F吧！！！');
    end case;
end;

4.2循环控制语句.

4.2.1 loop循环.

案例1：输出1-100之间所有数字；

declarei number:=0;
begin
<<aloop>>                              --给loop循环取别名：aloop
        loop
           dbms_output.put_line(i);     --输出每一个值
           i:=i+1;                                   --i的值在叠加
   exit aloop when i=101;        --当i等于100的时候aloop循环退出 
       end loop;
end;

案例2：求1-100之间的和。

declarei number(7):=0;
sumb number(7):=0;               --保存和，注意这里不能定义sum
begin
<<myloop>>                             --给loop循环取
loop
    sumb:=sumb+i;
    i:=i+1;
    exit myloop when i=101;
end loop;
dbms_output.put_line(sumb);   --循环完成后输出最终求的和

end;

4.2.2 while循环.

案例：求1-100之间的和。

declarei number(8):=0;
mysum number(8):=0;
begin
      while(i<=100) loop
            mysum:=mysum+i;
            i:=i+1;
      end loop;
      dbms_output.put_line(mysum); --循环完成后输出最终求的和
end;

4.2.3 for循环.

案例：求1-100之间的和。

declaremysum number(7):=0;
begin
      for i in 1..100 loop   
          mysum:=mysum+i;
      end loop;
      dbms_output.put_line(mysum);
end;

5.异常处理.

5.1预定义异常/系统自带异常.

当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发。

5.1.1 too_many_rows.u

too_many_rows：行太多。意为：实际返回的行数超出请求的行数。

异常演示：

declaresid number:=7499;
mysum emp%rowtype;
begin
    select * into mysum from emp;
    dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
end;

处理异常：

declaresid number:=7499;
mysum emp%rowtype;
begin
  select * into mysum from emp;
  dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
  exception when too_many_rows then
            dbms_output.put_line('太多行');
end;

5.1.2 no_data_found.

no_data_found：行太少。意为：未查询到数据。

异常演示：

declaresid number:=0000;
mysal emp.sal%type;
begin
    select sal into mysal from emp where empno=sid;
    if(mysal>3500) then
    dbms_output.put_line('要纳税');
    else
      dbms_output.put_line('要努力');
    end if;
end;

处理异常：

declaresid number:=0;
mysum emp%rowtype;
begin
  select * into mysum from emp where empno=sid;
  dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
  exception when no_data_found then
            dbms_output.put_line('行太少');
end;

5.2自定义异常.

语法：

declare  异常变量 exception;
  begin
    if(触发异常条件) then
       raise 异常变量;    
    else
      未触发异常执行的代码块
    end if;
    exception when 异常变量 then
             触发异常执行的代码块
  end;

案例演示：–根据工号查询工资，如果工资低于3500则抛出异常：‘你的工资太低了’。否则就输出：‘你的工资可以哟’。

declare  sid number:=7369;
  mye exception;
  mysal emp.sal%type;
  begin
    select sal into mysal from emp where empno=sid;
    if(mysal<3500)then
       raise mye;
    else
      dbms_output.put_line('你的工资可以哟');
    end if;
    exception when mye then
              dbms_output.put_line('你的工资太低了');
  end;

6.自定义函数.

语法结构

create [or replace] function 函数名 (参数1,参数2)return 返回值类型  is|as   
定义返回变量(要取长度)；
begin
  函数要执行的sql语句
  return 变量名;          
end;

案例演示：定义函数：通过学号返回学生的名字。

create or replace function  fun_name(sid number)      --函数名，带参数
  return varchar2 is               --返回值的类型，注意这里不能指定数据类型的长度
  sname varchar2(10);          --定义返回的变量(要取长度)
  begin
      select ename into sname from emp where empno=sid;
      return sname;                   --返回定义的变量sname
    end;