Oracle的PLSQL基础，if，case，loop

最新推荐文章于 2024-04-09 15:00:28 发布

所念皆星河73

最新推荐文章于 2024-04-09 15:00:28 发布

阅读量1.9k

点赞数 4

分类专栏： Oracl 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/weixin_60664694/article/details/128264955

版权

Oracl 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、简介

简单的PL/SQL程序，包含声明和执行两部分。最简单的，只包含BEGIN-END执行部分

DECLARE
  result number(10);
BEGIN
result := 10+3*4+40;--PLSQL中的赋值符号是 :=
  DBMS_OUTPUT.PUT_LINE('result:'|| result);
END;  --END后面有分号。敲的时候可以这样敲 begin end;然后再回去敲回车来换行

（1）标识符不区分大小写，所有的名称在存储时都被自动修改为大写。
（2）标识符中只能出现字母、数字和下划线，并且以字母开头。
（3）不能使用保留字。若与保留字同名，则必须使用双引号括起来。
（4）标识符最多30个字符。
（5）语句使用分号结束。SQL语句可以是多行的，但分号表示该语句的结束。一行中可以有多条SQL语句，它们之间以分号分隔。
（6）语句的关键字、标识符、字段的名称和表的名称都需要空格的分割。
（7）字符类型和日期类型需要使用单引号括起来。

PLSQL中赋值符号是 :=
where 后面是= 不是==
is null
is not null

2、`常量、变量与类型`

2.1`常量的声明`

常量必须赋初值。声明后，在以后的逻辑中，常量不可被改变，即只读。

常量名 CONSTANT 数据类型 [NOT NULL] {:=| DEFAULT} 常量值;
数据类型就是char/varchar2/nvarchar2/date/timestamp/number等

declare
  a constant number(10) := 99;--经常这么用
begin 
  dbms_output.put_line(a);--这个就是输出到控制台，类似java的sout
end; --小心，注意结尾有分号。可以成对敲，敲完【begin end;】，再换行

其中，CONSTANT是声明常量的关键词。NOT NULL表示常量值为非空。{:=| DEFAULT}表示常量必须显示地为其赋值。

2.1 `变量的声明`

变量名 数据类型 [[NOT NULL] {:=|DEFAULT} 变量值或表达式]];

可以只声明，也可以声明时就赋初始值

例子一：

 declare  
    a  number(10) :=100;
 begin  
    dbms_output.put_line(a+100); --输出200 
end;

例子二：从键盘读取

declare
  a number(10):= &a; --从键盘读取一个整数赋给变量a
  s varchar(10):= '&s'; --从键盘读取一个字符串赋给变量s

begin
  dbms_output.put_line(a);
  dbms_output.put_line(s);
end;
  
  
  补充：这样输入一个整数也是可以的，会自动转
declare
  a number(10):='&a';
begin
  dbms_output.put_line(a+3);
end;

例子二：使用%type
变量定义时除了使用基本数据类型，还可以使用%type，引用其他变量的类型（比如某一列的类型）,

declare
  v_name  student.Sname%TYPE; --定义了一个变量v_name与student表的Sname列具有相同的数据类型。
begin
  
    dbms_output.put_line(a+100); --输出200
    
 end;
 /*使用“%TYPE”声明具有以下两个优点：
（1）不必知道数据表列Sname确切的数据类型。
同时，“%TYPE”可以完全兼容在数据表中提取的数据，而不至于出现数据溢出或不符的情况；
（2）如果表中的Sname列的数据类型被修改了，v_name的数据类型在运行时会自动进行修改。
*/

declare
  name student.sname%type; 
  birth student.sbirth%type;
begin 
  select sname, sbirth into name,birth from student  where sid = 1;--注意 into 关键字写到select列表之后
  dbms_output.put_line(name);
  dbms_output.put_line(birth);
 end;

into前后是位置参数，查询到的sanme的值会赋给变量name，sbirth的值会赋给birth，一一对应。如果参数的类型不兼容，就会报错。

例子三：使用自定义类型，也叫记录类型
类似java的类，在Oracle的PLSQL编程中，我们也可以自定义数据类型，在Oracle中不叫类，而叫记录类型。（这的记录是名词，不是动词）

DECLARE

  --自定义类（类型），官方叫定义记录类型. TYPE 和 IS RECORD 都是关键字,大小写无所谓。把TYPE关键字想象成class关键字.
  TYPE stu IS RECORD 
  ( vnum student.Snum%TYPE,  --这是逗号
    vname student.Sname%TYPE  -- 最后一个不带逗号
   ); --分号别丢。都是些小细节，多敲几遍就顺手了。
    
    --声明该类型的变量。 
  v_stu stu;  
BEGIN
SELECT Snum, Sname into v_stu -- 注意这里
  FROM student
  WHERE Snum='1506101';  
  DBMS_OUTPUT.PUT_LINE('学号:'||v_stu.vnum);--通过 . 来使用自己的属性(成员)。||是字符拼接。类似java里sout的+
  DBMS_OUTPUT.PUT_LINE('姓名:'||v_stu.vname);
END;

自定义类型用到了%type，不懂%type的同学回去好好看一下。

例子是：使用“%ROWTYPE”
当我们自定义类型的属性类型与一个表的所有字段类型一样时，如果一个个自己敲实在是太麻烦，所以，Oracle提供了%ROWTYPE.
这种声明方式可以直接引用表中的行作为变量类型。它同“%TYPE”类似，也可以避免因表中字段的数据类型而导致的PL/SQL块出错的问题。

declare
 res student%rowtype; --可以认为：直接声明student表类型的变量。表的所有字段的类型他都有. 
 --我的student表中只有sid、sname、address三个字段。
begin
  select *  into res from student  where sid = 1;
  dbms_output.put_line(res.sid);--输出语句一次只能输出一个变量
  dbms_output.put_line(res.sname);
  dbms_output.put_line(res.address);
 end;

3、`if`

IF 条件表达式 
THEN 语句;
END IF;
用法和java的if一样，就是写法不同。
敲代码时候，【if end if;】一起敲完，再换行，防止漏掉end if，或者最后的分号

例子1：

declare
 a number :=10;
begin 
  if a>0 then
    dbms_output.put_line('大于0');  
    end if;
  end;

例子2：

declare
 res student%rowtype;
begin
  select *  into res from student  where sid = 1;
  if res.address = '北京'
    then dbms_output.put_line('bbbjjj');
    end if;
 end;

例子3：

declare
  cnt number(6);
begin
   select count(*) into cnt from student where address = '北京';
   if cnt > 0 then
     dbms_output.put_line('北京人一共有'||cnt||'个');
     end if;
  end;

4 `if else`

IF 条件表达式 THEN
	语句1;
ELSE
	语句2;
END IF;

5 `if elsif else`

IF 条件表达式1 THEN
语句1;
ELSIF 条件表达式2 THEN
语句2;
...
[ELSE 语句n]--可选
END IF;

最简单的例子：
declare
 a number:=10;
begin
  if a<0 then
    dbms_output.put_line('小于0');
  elsif a<10 then
    dbms_output.put_line('(0,10)之间，开区间');
  elsif a = 10 then 
    dbms_output.put_line('a等于10');
  else
    dbms_output.put_line('a大于10');
  end if;            
  end;

6 `简单的case语句`

简单的CASE语句就是：给出一个表达式，并把表达式结果同提供的几个可预见的结果做比较，如果匹配成功，则执行对应的语句。

单独的一个变量也是表达式。

CASE 表达式
WHEN 值1 THEN
语句1;
[WHEN 值2 THEN
语句2;]
...
[ELSE 语句n;]
END CASE [标签名];



【例】简单CASE语句应用示例。根据用户输入的编号，判断课程名。
代码如下：
DECLARE
  v_num varchar(10):='&v_num';
  v_Result varchar2(16);
BEGIN
 
  CASE v_num  
    WHEN '101' THEN v_Result:= '计算机基础';
    WHEN '102' THEN v_Result:= '程序设计语言';
    WHEN '206' THEN v_Result:= '离散数学';
    WHEN '208' THEN v_Result:= '数据结构';
    ELSE --类似C或java的default
      v_Result:= 'Nothing';
  END CASE;  -- 类比 end if
  DBMS_OUTPUT.PUT_LINE (v_result);--输出结果
END;

7`搜索式case语句`

搜索式CASE语句会依次检索WHEN条件表达式的布尔值是否为TRUE，一旦为TRUE，那么它所在的WHEN子句会被执行，后面的布尔表达式将不再考虑。如果所有的布尔表达式都不为TRUE，则程序会转到ELSE子句。如果没有ELSE子句，系统会给出异常。
功能和if-elsif-else一样。

【例8.13】搜索式CASE语句应用示例。计算“1506101”同学所有课程的平均成绩，并进行等级判断。
代码如下：
DECLARE 
  v_grade number;
BEGIN
  SELECT AVG(grade) INTO v_grade
  FROM SC
  WHERE Snum='1506101';
CASE 
    WHEN v_grade>=90 and v_grade<100 THEN
      DBMS_OUTPUT.PUT_LINE('平均成绩优秀！');
    WHEN v_grade<90 and v_grade>=80 THEN
      DBMS_OUTPUT.PUT_LINE('平均成绩良好！');
    WHEN v_grade<80 and v_grade>=70 THEN
      DBMS_OUTPUT.PUT_LINE('平均成绩中等！');
    WHEN v_grade<70 and v_grade>=60 THEN
      DBMS_OUTPUT.PUT_LINE('平均成绩及格！');
    WHEN v_grade<60 and v_grade>=0 THEN
      DBMS_OUTPUT.PUT_LINE('平均成绩不及格！');
    ELSE
      DBMS_OUTPUT.PUT_LINE('平均成绩计算错误！');
  END CASE;
END;

8 `简单的loop`

loop 就是循环的意思
简单的loop有两种结束循环的写法。一个是用if，一个是用when

LOOP
	循环体;              	
	IF 条件表达式 THEN     
		EXIT;   /*满足退出条件，退出循环*/
	END IF;
END LOOP;

或者

LOOP
	循环体;  
	EXIT  WHEN 条件表达式     /*测试是否符合退出条件*/
END LOOP;

例子：

求10!

declare
 n number(10) :=1;
 res number(10) :=1; 
begin 
  loop 
     res := res * n;
     n:= n+1;
  if n = 11 then 
    exit;
  end if;
  end loop;
  dbms_output.put_line(res);  
  end;
  
  结果：3628800

或者使用when关键字来结束循环

declare
 n number(10) :=1;
 res number(10) :=1; 
begin 
  loop 
     res := res * n;
     n:= n+1;
     exit when n=11;
  end loop;
  dbms_output.put_line(res);  
  end;

成对敲，敲完后再分。

begin end;

loop end loop;

if end if;

9 `WHILE...LOOP`

类比java的while循环。loop和end loop类比成两个大括号{ }

WHILE...LOOP结构的语句本身可以终止LOOP循环，
当WHILE后面的条件表达式为TRUE时，LOOP和END LOOP之间的循环体
将执行一次，而后重新判断WHILE后面的表达式是否为TRUE。
如果其后的表达式为FALSE，将结束循环语句。其语法结构如下：
WHILE 条件表达式   /*测试是否符合退出条件*/
	LOOP
		循环体;       /*执行循环体*/
	END LOOP;

例子：求10！

declare 
  res number(10):=1;
  n number(10):=1;
begin 
  while n <=10        --  注意while后面不需要加then
    loop 
      res:=res*n;
      n:=n+1;
    end loop;
    dbms_output.put_line(res); 
  end;

10 `FOR LOOP`

类比Python的for循环

FOR...LOOP语句循环遍历指定范围内的整数。
该范围被FOR和LOOP关键词封闭。当第一次进入循环时，
其循环范围会被确定，并且以后不会再次计算。每循环一次，
其循环次数将会增加1。其语法结构如下：
FOR 计数变量 IN 变量初值..变量终值   /*定义跟踪循环的变量*/
	LOOP
		循环体      /*执行循环体*/
	END LOOP;

最简单的例子：

declare--因为里面没声明变量，所以declare也可以不写
begin
  for i in 0..5 --i可以不在declare中声明，直接用。很像Python。注意是2个点
    loop
      dbms_output.put_line(i);  
      end loop;
  end;
  
/*
 结果：
0
1
2
3
4
5


*/

例子2：

求10！
declare 
 n number(8);
 res number(8):=1;
 begin 
   for n in 1..10
     loop   
     	res := res * n;
     end loop;
    dbms_output.put_line(res); 
   end;

实现九九乘法表

declare 
 i number;
 j number ;
begin 
  for i in 1..9 --把loop和end loop看成大括号{}即可。
    loop 
      for j in 1..i -- 注意这里的范围
        loop 
          dbms_output.put_line(i||'*'||j||'='||i*j); 
          end loop;
          dbms_output.put_line('---------'); 
      end loop;
  end;  
  
  结果如下，截图没截全。