PLSQL-定义变量、条件判断、循环函数、游标、存储过程、自定义函数、数据同步

Winnie0522

已于 2022-05-23 11:32:18 修改

阅读量2.1k

点赞数 1

文章标签： sql oracle 数据库

于 2022-05-23 11:32:03 首次发布

本文链接：https://blog.youkuaiyun.com/Winnie0522/article/details/124879501

版权

本文详细介绍了Oracle中的PL/SQL语言，包括如何定义变量、进行条件判断、使用循环结构，以及如何操作游标、创建和调用存储过程和自定义函数。此外，还讲解了数据同步的MERGE语句应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PLSQL语言

PL/SQL:叫做过程化SQL语言，是ORACLE数据库对SQL语句的扩展，在普通SQL语句的特点

所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中

SQL：一般指结构化查询语言，用于存取数据以及查询、更新和管理关系数据库系统

一、PLSQL定义变量

DECLARE

变量值/表表.列%TYPE（引用列）/表%ROWTYPE（引用表）;

BEGIN

SELECT 字段 INTO 变量名 FROM 表 WHERE 条件;

dbms_output.put_line('字符串' || 变量值/表.列);

END;

DECLARE 
	vstu STUDENTS%rowtype;
BEGIN
	SELECT * INTO vstu FROM STUDENTS WHERE S_NAME = 'winnie';
	dbms_output.put_line('winnie的成绩是：'||vstu.score);
END;

二、条件判断

I f 函数

IF 条件1 THEN 结果1；

ELSIF 条件2 THEN 结果2；

ELSE 不满足条件，则返回结果3；

END IF;

CASE WHEN

CASE

WHEN 条件1 THEN 结果1;

WHEN 条件2 THEN 结果2;

ELSE 结果n;

END CASE;

三、循环结构

无条件循环

LOOP

循环体;

EXIT WHEN 条件; --条件为TURE的时候跳出循环

END LOOP;

************************************************************************************************************

求1加到100；

DECLARE

       vsum number(6) ; num NUMBER(6);

BEGIN

       vsum:=0 ; num:=0;

       LOOP

              num:=num+1;

              vsum:=vsum+num;

       EXIT WHEN num>=100;

       END LOOP;

       dbms_output.put_line(vsum);

END;

②WHILE 循环

WHILE 条件 LOOP --满足条件时才进入循环

循环体;

END LOOP;

DECLARE

       vsum number(6) :=0; num NUMBER(6) :=0;

BEGIN

       WHILE num < 100

       LOOP

              num:=num+1;

              vsum:=vsum+num;

       END LOOP;

       dbms_output.put_line(vsum);

END;

FOR 循环

FOR 循环变量 IN 循环下限..循环上限 LOOP

循环体;

END LOOP;

DECLARE

       vsum number(6) :=0;

BEGIN

       FOR i IN  1..100

       LOOP

              vsum:=vsum+i;

       END LOOP;

       dbms_output.put_line(vsum);

END;

四、游标：查询的结果数量>1,要使用游标循环读取

（1）作用：抓取在结果集中的数据

（2）用法：

①定义游标

CURSOR 游标名 IS SELECT 。。。

②打开游标

OPEN 游标名

③抓取游标

FETCH 游标名 INTO 变量

④关闭游标

CLOSE 游标名

（3）注意

a.游标只能打开或关闭一次

b.游标一旦关闭就不能抓取数据

（4）属性

%isopen：确认游标是否打开

%rowcount：游标抓取记录条数

%FOUND：如果前面游标fetch到数据，则返回true，如果没有抓取到数据，返回null

%NOTFOUND：与%FOUND相反

--把班级1的学生名字打印出来

--loop循环

DECLARE

  CURSOR cur_name IS SELECT * FROM STUDENTS WHERE C_ID = 1;

  name cur_name%rowtype;

BEGIN

  OPEN cur_name ;

  loop

         FETCH cur_name INTO name;

         EXIT WHEN cur_name %notfound;

  dbms_output.put_line('1班的同学有：'||name.S_NAME);

  END LOOP;

  CLOSE cur_name; 

END;

--while循环

DECLARE

  CURSOR cur_name IS SELECT * FROM STUDENTS WHERE C_ID = 2;

  name cur_name%rowtype;

BEGIN

  OPEN cur_name ;

         FETCH cur_name INTO name;

         WHILE  cur_name%found LOOP

         dbms_output.put_line('2班的同学有：'||name.S_NAME);

         FETCH cur_name INTO name;

         END LOOP;

  CLOSE cur_name; 

END;

--for循环

for循环无须再定义变量

DECLARE

  CURSOR cur_name IS SELECT * FROM STUDENTS WHERE C_ID = 3;

BEGIN

  FOR i IN cur_name loop

  dbms_output.put_line('3班的同学有：'||i.S_NAME);

  END LOOP;

END;

五、存储过程

无参

（1）创建存储过程

CREATE OR REPLACE PROCEDURE 存储过程名

定义变量；

BEGIN

执行的操作；

END

（2）调用存储过程

BEGIN

存储过程名；

END

（1）创建存储过程

有参in（输入类型）

CREATE OR REPLACE PROCEDURE 存储过程名（名 IN 属性）in可以省略，默认为in

定义变量；

BEGIN

执行的操作；

END

有参OUT（输出类型）

CREATE OR REPLACE PROCEDURE 存储过程名（值1 IN 属性，值2 OUT 属性）

定义变量；

BEGIN

执行的操作；

END

（2）调用存储过程

方法一

DECLARE

值1 属性 := '字符串';

值2 属性;

BEGIN

存储过程名（值1 IN 属性，值2 OUT 属性）；

END

方法二

CALL 存储过程名（值1 IN 属性，值2 OUT 属性）；

（4）in out（输入输出类型）

CREATE OR REPLACE PROCEDURE 存储过程名（值1 IN OUT属性，值2 IN/OUT属性）

定义变量；

BEGIN

执行的操作；

END

②调用存储过程

DECLARE

值1 属性 := '字符串';

值2 属性;

BEGIN

存储过程名；

END

六、自定义函数

(1)无参

CREATE OR REPLACE FUNCTION 函数名（参数）

RETURN 返回的值的数据类型

BEGIN

RETURN 语句;

END;

创建自定义函数

CREATE OR REPLACE FUNCTION sum_func

RETURN NUMBER

IS

BEGIN

  RETURN 1+5;

END;

调用自定义函数

SELECT sum_func FROM dual;

(2)有参

CREATE OR REPLACE FUNCTION 函数名（参数引用表格/列的数据类型%rowtype %type）

RETURN 引用表格/列的数据类型%rowtype %type

变量名 引用表格/列的数据类型%rowtype %type ;

BEGIN

SELECT 字段 INTO 变量 FROM 表

WHERE 过滤条件;

RETURN 变量;

END;

表1

表2

根据学号找出对应的名字

CREATE OR REPLACE FUNCTION sel_name(fx students.S_ID%TYPE )

RETURN students.S_NAME%TYPE

IS

  bl students.S_NAME%TYPE ;

BEGIN

  SELECT s.S_NAME INTO bl FROM STUDENTS s

  WHERE s.S_ID = fx;

  RETURN bl;

END;

调用自定义函数

SELECT sel_name(2) FROM DUAL;

七、数据同步

MERGE INTO 表a（主表）

USING 表b（增量表）

ON a.条件字段1=b.条件字段1 AND a.条件字段2=b.条件字段2 ...... (关联条件)

WHEN MATCHED THEN UPDATE SET a.字段=b.字段

目标表别称a和别称b不要省略

WHEN NOT MATCHED THEN

INSERT(a.字段1,a.字段2...)

VALUES(b.字段1,b.字段2...)

表1（原表）

表2（增量表）

把新信息表格更新到原信息表，并替换原表中错误的信息

MERGE INTO xinxi x

USING newxinxi nx

ON (x.id = nx.id)

WHEN MATCHED THEN

  UPDATE SET x.name = nx.name

WHEN NOT MATCHED THEN

  INSERT  VALUES (nx.id,nx.name ,nx.address)