当前位置：首页 > article >正文

【达梦数据库】disql工具参数绑定

article 2025/3/6 17:03:33

前言

在达梦数据库的使用过程中尽管管理工具很好用，但是命令行工具还是有着得天独厚的优势，但是在参数绑定方面就没有管理工具做的更加完美，现在就汇总下disql 工具参数绑定的常用几种方式

disql 参数绑定

使用 ?

select * from v$dm_ini where para_name =? and  para_value=?;
input value of param1:COMPATIBLE_MODE

input value of param2:2


LINEID     PARA_NAME       PARA_VALUE MIN_VALUE MAX_VALUE DEFAULT_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION                                                                                     PARA_TYPE
---------- --------------- ---------- --------- --------- ------------- ------- ---------- ---------- ----------------------------------------------------------------------------------------------- ---------
1          COMPATIBLE_MODE 2          0         6         0             Y       2          2          Server compatible mode, 0:none, 1:SQL92, 2:Oracle, 3:MS SQL Server, 4:MySQL, 5:DM6, 6:Teradata  IN FILE

优

无需给考虑给字符串加双引号，即只需要输入参数值就行
查看sqllog 内容如下，是参数绑定后触发执行

[ORA]: select * from v$dm_ini where para_name =? and  para_value=?;^M
[ORA]: select * from v$dm_ini where para_name =? and  para_value=?;^M
Load para: 0 rows^M
PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, 'COMPATIBLE_MODE'), (1, VARCHAR2, '2')}
[SEL] select * from v$dm_ini where para_name =? and  para_value=?;^M
[SEL] select * from v$dm_ini where para_name =? and  para_value=?; EXECTIME: 4(ms) ROWCOUNT: 1(rows).^M

劣

不显示参数名，当参数列表过多时，无法进行区分

使用 &变量名

select * from v$dm_ini where para_name = &para_name and  para_value= &para_value;
input para_name value:'COMPATIBLE_MODE'
input para_value value:2
old value 1:select * from v$dm_ini where para_name = &para_name and  para_value= &para_value;
new value 1:select * from v$dm_ini where para_name = 'COMPATIBLE_MODE' and  para_value= 2;

LINEID     PARA_NAME       PARA_VALUE MIN_VALUE MAX_VALUE DEFAULT_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION                                                                                     PARA_TYPE
---------- --------------- ---------- --------- --------- ------------- ------- ---------- ---------- ----------------------------------------------------------------------------------------------- ---------
1          COMPATIBLE_MODE 2          0         6         0             Y       2          2          Server compatible mode, 0:none, 1:SQL92, 2:Oracle, 3:MS SQL Server, 4:MySQL, 5:DM6, 6:Teradata  IN FILE

优

可以显示参数名

劣

该方式实际上类似于参数替换，并没有实现参数绑定效果，查看sqllog

[ORA]: select * from v$dm_ini where para_name = 'COMPATIBLE_MODE' and  para_value= 2;^M
[SEL] select * from v$dm_ini where para_name = 'COMPATIBLE_MODE' and  para_value= 2;^M
[SEL] select * from v$dm_ini where para_name = 'COMPATIBLE_MODE' and  para_value= 2; EXECTIME: 4(ms) ROWCOUNT: 1(rows).

针对字符串需要添加双引号

select * from v$dm_ini where para_name = &para_name and  para_value= &para_value;
input para_name value:COMPATIBLE_MODE
input para_value value:2
old value 1:select * from v$dm_ini where para_name = &para_name and  para_value= &para_value;
new value 1:select * from v$dm_ini where para_name = COMPATIBLE_MODE and  para_value= 2;
select * from v$dm_ini where para_name = COMPATIBLE_MODE and  para_value= 2;
[-2111]:Error in line: 1
Invalid column name [COMPATIBLE_MODE].
used time: 0.382(ms). Execute id is 0.