LightDB 23.2 plorasql支持open游标大小写问题

前言

由于oracle在匿名块、函数、存储过程和包中，open打开游标时，指定的游标变量可以是不敏感的。但是LightDB不支持这种情况，因为plorasql词法扫描使用的是LightDB内核的词法解析器导致的。众所周知，LightDB内核的词法解析器将标识符转换成小写。然而在plorasql在对open子句处理时，采用的是硬编码的逻辑。获取的标识符为输入的标识符，大小写原文保存。在该段逻辑处理过程中，比较的时候采用的又是大小写敏感的比较方法，因此造成了open使用含有大写字母的游标出现找不到变量名的错误。

解决方法

open子句处理时，将其比较方式替换成strcasecmp函数，及大小写不敏感的比较。

测试

建表及插入值

CREATE TABLE tt(a int,b boolean);
INSERT INTO tt VALUES(888,true);
INSERT INTO tt VALUES(999,false);

create table t_hs_open(id int);
insert into t_hs_open values(1);
insert into t_hs_open values(2);
insert into t_hs_open values(3);
insert into t_hs_open values(4);

匿名块

DECLARE
  CURSOR D_tmp(zxc NUMBER,bnm NUMBER,ff smallint default 1234) IS
		SELECT ff || a as feiwu FROM tt;
  ddd D_tMp%ROWTYPE;
  BEGIN
    OPEN D_tmP(5,6);
	FETCH d_tmp INTO ddd;
	DBMS_OUTPUT.PUT_LINE(ddd.feiwu);
	CLOSE D_tmP;
end;
/

函数

create or replace function f_hs_open(id int) return int as
  v_Sql_cursor varchar2(1000);
  f_Hs_cur refcursor;
  emp_Rec  t_hs_open%ROWTYPE;
  begin
    v_Sql_cursor = 'select id from t_hs_open';
    begin
	  open f_Hs_cur for v_Sql_cursor;
    end;
   close f_hs_cur;
   return 1;
end;
/

select * from f_hs_open(1);
 f_hs_open 
-----------
         1
(1 row)

存储过程

CREATE OR REPLACE PROCEDURE p23
IS
    CURSOR C_tmp IS SELECT a as feiwu FROM tt;
    aaa C_Tmp%ROWTYPE;
begin
	OPEN C_tmp;
		FETCH C_tMp INTO aaa;
		DBMS_OUTPUT.PUT_LINE(aaa.feiwu);
	    CLOSE C_tmP;
END;
/
call p23();
drop procedure p23();

包

CREATE OR REPLACE PACKAGE aa
AS
	CURSOR c_tmp(zxc NUMBER,bnm NUMBER,ff smallint := 1234) IS
		SELECT ff || a as feiwu FROM tt;
	PROCEDURE c_pro;
	FUNCTION c_fun RETURN INTEGER;
end aa;
/


CREATE OR REPLACE PACKAGE BODY aa
as	
	PROCEDURE c_pro IS
	  aaa c_tmp%ROWTYPE;
	  CURSOR p_tmp(zxc NUMBER,bnm NUMBER,ff smallint := 1234) IS
			SELECT ff || 'hundsun' || a as feiwu FROM tt;
	  bbb p_tmp%ROWTYPE;
	  BEGIN
		OPEN c_tmp(5,6);
		FETCH c_tmp INTO aaa;
		DBMS_OUTPUT.PUT_LINE(aaa.feiwu);
	    CLOSE c_tmp;
		OPEN p_tmp(5,6);
		FETCH p_tmp INTO bbb;
		DBMS_OUTPUT.PUT_LINE(bbb.feiwu);
		CLOSE p_tmp;
	end c_pro;
	FUNCTION c_fun RETURN INTEGER AS
	  aaa c_tmp%ROWTYPE;
	  CURSOR f_tmp(zxc NUMBER,bnm NUMBER,ff smallint := 1234) IS
		SELECT ff || 'hundsun' || a as feiwu FROM tt;
      bbb f_tmp%ROWTYPE;
	  BEGIN
		OPEN c_tmp(5,6);
		FETCH c_tmp INTO aaa;
		DBMS_OUTPUT.PUT_LINE(aaa.feiwu);
		CLOSE c_tmp;
		OPEN f_tmp(5,6);
		FETCH f_tmp INTO bbb;
		DBMS_OUTPUT.PUT_LINE(bbb.feiwu);
		CLOSE f_tmp;
      return 1;
	end c_fun;
end aa;
/

DECLARE
  CURSOR d_tmp(zxc NUMBER,bnm NUMBER,ff smallint := 1234) IS
		SELECT ff || a as feiwu FROM tt;
  ddd d_tmp%ROWTYPE;
  BEGIN
    OPEN d_tmp(5,6);
	FETCH d_tmp INTO ddd;
	DBMS_OUTPUT.PUT_LINE(ddd.feiwu);
	CLOSE d_tmp;
end;
/

call aa.c_pro;

select aa.c_fun();
 c_fun 
-------
     1
(1 row)

drop PACKAGE aa;