inceptor-pl/sql和sql的交互（db2方言）

最新推荐文章于 2023-12-16 17:25:24 发布

李攀_

最新推荐文章于 2023-12-16 17:25:24 发布

阅读量2.0k

点赞数 1

分类专栏： inceptor plsql 文章标签： plsql

本文链接：https://blog.csdn.net/lipan1413/article/details/99635157

版权

inceptor 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

plsql

2 篇文章 0 订阅

订阅专栏

一、将select的返回值赋值给变量

1.1将查询的结果赋值给变量

	!set plsqlUseSlash true
		BEGIN
		  DECLARE V1 STRING;
		  DECLARE V2 DOUBLE;
		   -- 查询transactions表中价格为11.11的交易时间，并赋值给变量V1。
		   SET V1 = (SELECT trans_time FROM transactions WHERE price = 11.11);
		   -- 查询transactions表中交易号为162742112的价格，并赋值给变量V2。
		   SET V2 = (SELECT price FROM transactions WHERE trans_id=162742112);
		  PUT_LINE('V1:'||V1);
		  PUT_LINE('V2:'||V2);
		END;
		/

输出结果为:

			output
			V1:XXXX
			V2:XXXX

二、 SELECT INTO

通过select into语句将行值赋给行变量
		!set plsqlUseSlash true
	    BEGIN
	     -- 定义一个行数据类型transactiontype，包含了三个字段v_acc_num , v_trans_time , v_price，其中字段v_acc_num的数据类型为STRING，v_trans_time的数据类型为STRING，v_price的数据类型为DOUBLE。
	     DECLARE TYPE transactiontype AS ROW (v_acc_num STRING, v_trans_time STRING, v_price DOUBLE);
	     -- 声明一个行变量transactionrow，其数据类型为transactiontype，该变量包含transactiontype类型的三个字段。
	     DECLARE transactionrow transactiontype;
	     -- 通过SELECT INTO语句将表transactions中股票账号为GL2547626的账号，交易时间和价格放进行变量transactionrow中。
	     SELECT acc_num,trans_time,price INTO transactionrow FROM transactions WHERE stock_id='GL2547626';
	      -- 打印出行变量transactionrow中的字段transactionrow.v_acc_num的值。
	      PUT_LINE('acc_num:'||transactionrow.v_acc_num);
	      -- 打印出行变量transactionrow中的字段transactionrow.v_trans_time的值。
	      PUT_LINE('time:'||transactionrow.v_trans_time);
	      -- 打印出行变量transactionrow中的字段transactionrow.v_price的值。
	      PUT_LINE('price:'||transactionrow.v_price);
	    END;
	    /

输出结果为:

	  	output
	    acc_num:XXXXXXXXXXX
	    
	    time:XXXXXXXXXXXXXXX
	    
	    price:XXXXXXXXXXXXXX

三、存储过程和sql的交互

inceptor中可以利用创建存储过程，对表进行增删该查等操作，将DML，update，insert，merge等语句，写进存储当中就可以，调用存储的时候，批量执行DML语句了

3.1在存储中执行update，insert，merge等语句

		-- 调用过程sql_procedure，实参的值分别为zhangsan，4.5。
		!set plsqlUseSlash true
		-- 创建名为sql_procedure的存储过程，参数类型均为IN类型，其中形参v_name的数据类型为STRING，形参v_gpa的数据类型为DOUBLE。
		CREATE OR REPLACE PROCEDURE sql_procedure(IN v_name STRING,IN v_gpa DOUBLE)
		BEGIN
		  -- 往表zara中插入一条数据，姓名为Amy，年龄为23，绩点为4.5。
		  INSERT INTO zara VALUES('Amy',23,4.5);
		  -- 将表zara中姓名和参数v_name相等的人的年龄更新为18。
		  UPDATE zara SET age= 18 WHERE name = v_name;
		  -- 删除表zara中绩点与参数v_gpa的值相等的记录。
		  DELETE FROM zara WHERE gpa = v_gpa;
		  -- 对表za进行merge操作，条件为两个表中的姓名相同。
		  MERGE INTO za z using zara r on (z.name=r.name)
		  -- 如果条件匹配，即两个表中存在姓名相同的记录，就将表za中姓名和表zara中姓名相同的人的年龄更新为40。
		  WHEN MATCHED THEN UPDATE SET z.age=40
		  -- 如果条件不匹配，就将表zara中与表za中姓名不相同的姓名和对应的年龄插入到表za中。
		  WHEN NOT MATCHED THEN INSERT (name,age) VALUES(r.name,r.age);
		END;
		/
		CALL sql_procedure('zhangsan',4.5);

四、函数与SQL的交互

与存储过程类似，在Inceptor中也可以将UPDATE，INSERT，DELETE，MERGE等DML语句，放进函数里，在调用函数时，执行函数内的SQL语句。

在函数内执行UPDATE，INSERT，DELETE，MERGE等DML语句

		!set plsqlUseSlash true
		-- 创建一个名为sql_function的函数，参数的类型为IN类型，参数的数据类型为STRING。
		CREATE OR REPLACE FUNCTION sql_function(IN v_name STRING)
		-- sql_function函数的返回值的数据类型为DOUBLE。
		RETURNS DOUBLE
		BEGIN
		  DECLARE v_gpa DOUBLE;
		  -- 查询表zara中姓名和参数相等的人的绩点，并放进一个DOUBLE类型的变量v_gpa里。
		  SELECT gpa INTO v_gpa FROM zara WHERE name=v_name;
		  -- 函数返回值为v_gpa的值。
		  RETURN v_gpa;
		END;
		/
		BEGIN
		  -- 在一个语句块内执行DML语句DELETE，删除表zara中绩点与sql_function('lily')返回值相等的记录。
		  DELETE FROM zara WHERE gpa= sql_function('lily');
		END;
		/

五、游标

在Inceptor中可以通过游标查询数据，与SQL进行交互。游标相关的用法请查看其他笔记

		!set plsqlUseSlash true
		BEGIN
		  -- 声明一个名为c1的游标，用来查找表transactions中价格为12.13的交易时间。
		  DECLARE c1 CURSOR FOR SELECT trans_time FROM transactions WHERE price=12.13;
		  -- 声明一个名为v_time的变量，数据类型为字符串。
		  DECLARE v_time STRING;
		  -- 打开游标。
		  OPEN c1;
		  -- 将游标查询到的结果放进变量v_time里。
		  FETCH c1 INTO v_time;
		  -- 关闭游标。
		  CLOSE c1;
		  -- 打印出变量v_time的值。
		  PUT_LINE('the time is:'||v_time);
		END;
		/

六、动态sql

6.1 EXECUTE IMMEDIATE

Dynamic SQL中最常使用EXECUTE IMMEDIATE语句来执行动态SQL语句，使用EXECUTE
IMMEDIATE语句可以创建执行DDL语句，DCL语句，DML语句以及单行的SELECT 语句，但该方法不能用于处理多行查询语句。
在具体的使用过程中，是将相应的DDL语句，DCL语句，DML语句以及单行的SELECT 语句等SQL语句，放进一个已声明的字符串变量中，再用EXECUTE IMMEDIATE去执行该字符串变量，或者EXECUTE IMMEDIATE关键字后直接跟含有SQL语句的字符串常量。

6.1.1 处理DDL语句

CREATE

使用execute immediate创建表sqlpl_create

	!set plsqlUseSlash ture
	drop table if exits sqlpl_create;
	BEGIN
		DECLARE create_table STRING
		--注意字符串和连接符之间的空格，否则inceptor可能会报错
		SET create_table = ' create table ' ||'sqlpl_create' ||'(sno string , sname string)';
		EXECUTE IMMEDIATE create_table;
	END;
	/

ALTER

使用EXECUTE IMMEDIATE 修改 sqlpl_create 表中的sno 列名和字段数据类型

	!set plsqlUseSlash true
	BEGIN
		DECLARE alter_table STRING;
		set alter_table ='alter table sqlpl_create  CHANGE sno sid int'; 
		EXECUTE IMMEDIATE alter_table;
	END;
	/

DROP

删除plsql_cereate 表

!set plsqlUseSlash true
BEGIN
	DECLARE drop_table STRING;
		set drop_table = 'drop table' ||'sqlpl_create ';
	EXECUTE IMMEDIATE drop_table;
END;
/

6.1.2 处理DCL语句

GRANT

--创建存储
set plsql.use.slash=true;
CREATE OR REPLACE PROCEDURE grant_priv(priv STRING,username STRING)
BEGING
	DECLARE grant_tab STRING;
		SET grant_tbl ='grant' || priv || 'TO user' || username;
	EXECUTE IMMEDIATE grant_tbl ;
END;
/

--调用存储
set plsql.use.slash=true;
BEGIN
	grant_priv('create', 'user1');
END;
/

REVOKE

--创建过程revoke_priv

set plsql.use.slash=true;
CREATE OR REPLACE PROCEDURE revoke_priv(priv STRING, username STRING)
BEGIN
  DECLARE priv_stat STRING;
   SET priv_stat =' revoke '|| priv || ' from user  ' || username;
   EXECUTE IMMEDIATE priv_stat;
END;
/

调用过程

set plsql.use.slash=true;
BEGIN
  revoke_priv('create', 'user1');
END;
/

6.1.3 处理单行查询

注：要查看select的结果要设置plsql.show .sqlresults=true;

EXECUTE IMMEDIATE后接字符串类型的变量

set plsql.show.sqlresults=true;
!set plsqlUseSlash=true
BEGIN
	DECLARE sql_stat STRING;
		SET sql_stat='select * from sqlpl_create limit 1';
	EXECUTE IMMEDIATE sql_stat;
END;
/

EXECUTE IMMEDIATE后接字符串常量

set plsql.show.sqlresults=true;
!set plsqlUseSlash=ture
BEGIN
	EXECUTE IMMEDIATE 'select * from sqlpl_create limit 1';
END;
/

EXECUTE IMMEDIATE执行含有占位符的SELECT查询语句

set plsql.show.sqlresults=true;
!set plsqlUseSlash true
BEGIN
   -- 此处用问号作占位符，且该问号必须为英文键盘情况下的问号
   EXECUTE IMMEDIATE 'select * from sqlpl_create where sno=?' using 6513065;
END;
/

6.2 PREPARE … EXECUTE

同样，Inceptor也支持使用PREPARE关键字将要执行的SQL语句放进变量，再用EXECUTE关键字来执行的方式，但需要注意:
用来存储SQL语句的变量，必须为STATEMENT类型的变量。
用来做占位符的问号，必须是英文键盘下的问号。
PREPARE … EXECUTE只能用来处理SELECT查询及DML语句。

	!set plsqlUseSlash true
	set plsql.show.sqlresults=true;
	BEGIN
	  -- 声明一个STATEMENT类型的变量stmt。
	  DECLARE stmt STATEMENT;
	   -- 使用关键字PREPARE，将SQL语句'查询transactions表中账号名所对应的行数'放进变量stmt里。
	   PREPARE stmt FROM  ' begin select count(*) from ' || 'transactions' || ' where acc_num = ? ; end';
	  -- 使用关键字执行变量stmt内的SQL语句，查询账号为6513065的行数。
	  EXECUTE stmt using 6513065;
	  -- 使用关键字执行变量stmt内的SQL语句，查询账号为0700735的行数。
	  EXECUTE stmt using 0700735;
	END;
	/

七、GET DIAGNOSTICS

ROW_COUNT:返回前面执行的 SQL 语句处理的行数。
EXCEPTION 1:前面执行的 SQL 语句返回的 DB2 错误或警告消息文本。

7.1 ROW_COUNT

!set plsqlUseSlash true
-- 创建一个名为upd_zara_gpa的过程，参数v_age，参数v_age的类型均为IN，数据类型均为整数型；参数no_of_rows的类型为OUT，数据类型为整数型。
CREATE OR REPLACE PROCEDURE upd_zara_gpa(in v_age INT, in v_gpa INT, out no_of_rows INT)
 -- 指定程序的主体语言为SQL。
 language sql
 BEGIN
   -- 查找表zara中年龄等于参数v_age的记录，并将参数v_gpa的值赋给对应的记录。
   UPDATE zara SET gpa = v_gpa WHERE age = v_age;
   -- 使用关键字GET DIAGNOSTICS获取所执行的SQL语句处理的行数，并赋给参数no_of_rows。
   GET DIAGNOSTICS no_of_rows = ROW_COUNT;
   -- 打印出SQL语句所处理的行数。
   PUT_LINE('更新行:'||no_of_rows);
 END;
/
BEGIN
   -- 在调用过程upd_zara_gpa的语句块内，声明一个整数类型的变量row。
   DECLARE row INT;
   -- 调用过程upd_zara_gpa，参数分别为22,4.5，row，此处执行的结果是，把表zara中年龄为22的人的gpa改为4.5，并将更新的行数赋值给row。
   upd_zara_gpa(22,4.5,row);
 END;
 /

7.2 EXCEPTION 1

创建存储过程

	!set plsqlUseSlash true
	-- 创建存储过程proc_11，参数b的类型为OUT，数据类型为整数型。
	CREATE OR REPLACE PROCEDURE proc_11(out b int)
	BEGIN
	 -- 分别声明两个整数类型的变量sqlcode, sql_code；三个字符串类型的变量sqlstate, sql_state，errMsg。
	 DECLARE sqlcode, sql_code int;
	 DECLARE sqlstate, sql_state string;
	 DECLARE errMsg string;
	 -- 为异常sqlexception声明一个continue的异常处理器。
	 DECLARE continue handler for sqlexception
	 BEGIN
	  -- 使用关键字GET DIAGNOSTICS获取执行SQL返回的错误或警告信息。
	  GET DIAGNOSTICS exception 1 errMsg = DB2_TOKEN_STRING;
	  -- 将sqlcode, sqlstate赋值给变量sql_code, sql_state。
	  VALUES(sqlcode, sqlstate) INTO sql_code, sql_state;
	  -- 如果sql_code不等于-438，或sql_state不等于54321，或errMsg不等于exception on purpose，就抛出异常，sqlstate的值为30001。
	  IF sql_code != -438 or sql_state != '54321' or errMsg != 'exception on purpose' THEN
	   signal sqlstate '30001';
	  END IF;
	 END;
	 -- 抛出一个异常，sqlstate的值为54321，MESSAGE_TEXT的值为exception on purpose。
	 signal sqlstate value '54321' set MESSAGE_TEXT = 'exception on purpose';
	  -- 给参数b赋值为20。
	 SET b = 20;
	 -- 分别打印出变量sql_code, sql_state以及参数b的值。
	 PUT_LINE('sqlcode is:'||sql_code);
	 PUT_LINE('sqlstate is:'||sql_state);
	 PUT_LINE('value of b:'||b);
	END;
	/

调用存储过程

	!set plsqlUseSlash true
	BEGIN
	 DECLARE b INT default -10;
	 CALL proc_11(b);
	 IF b != 20 THEN
	  signal sqlstate value '30002';
	 END IF;
	END;
	/

李攀_

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
inceptor-pl/sql和sql的交互（db2方言）

inceptor和sql的交互（db2方言）一、将select的返回值赋值给变量1.1将查询的结果赋值给变量二、 SELECT INTO三、存储过程和sql的交互3.1在存储中执行update，insert，merge等语句四、函数与SQL的交互五、游标六、动态sql6.1 EXECUTE IMMEDIATE一、将select的返回值赋值给变量1.1将查询的结果赋值给变量 !set plsq...
复制链接

扫一扫