在使用查询更新Sql中的记录时... 0（零）在存储在数据库中时被省略

My requirement is -- Am trying to store the value entered by user into database (One of the Table in the database). But when am entering 0921 as department, then 0 (Zero) is getting ommitted and only 921 is getting stored in the database !! Which is giving wrong output and wrong results to me.

我的要求是 - 我试图将用户输入的值存储到数据库中（数据库中的一个表）。但是当我作为部门进入0921时，0（零）被省略，只有921被存储在数据库中！这给了我错误的输出和错误的结果。

$ write SQL "set feedback on"
$ write SQL "spool  Manager:[Aksh]akshay4.log"
$ write SQL "define IEMP_ID = '" + Ref + "';"
$ write SQL "define IYEAR = '" + File + "';"
$ write SQL "define IDEPT = '" + DEPT + "';"
$ write SQL "define IACC = '" + ACC + "';"
$ write SQL "Update EMPLOYEE set DEPT=&IDEPT where EMP_ID=&IEMP_ID and year=&IYEAR and ACC=&ACC;
$ write SQL "commit;"
$ write SQL "exit;"
$ close SQL

When I run the above code then I could see the below output:

当我运行上面的代码然后我可以看到以下输出：

old   1: Update employee set DEPT=&IDEPT where EMP_ID=&EMP_ID and year=&IYEAR and ACC=&ACC;
new   1: Update employee set DEPT=02150 where EMP_ID=14447 and year=2017 and ACC=1

But when I check the database after running the code..I could see only 2150 is getting saved in the database ... Any suggestions on this ??

但是当我在运行代码后检查数据库时......我只能看到2150正在保存在数据库中......对此有何建议？

2 个解决方案

#1

You have an issue with your data type.

您的数据类型存在问题。

First of all, your column type for DEPT cannot be an integer - integers do not get stored with preceding zeroes.

首先，DEPT的列类型不能是整数 - 整数不会以前面的零存储。

So if you set an integer column to 02150, the number becomes 2150 because in the context of an integer number, the preceding 0 is pointless.

因此，如果将整数列设置为02150，则该数字将变为2150，因为在整数的上下文中，前面的0是无意义的。

In order to fix this, you should be using a VARCHAR type data type for your DEPT column in the database.

为了解决这个问题，您应该为数据库中的DEPT列使用VARCHAR类型数据类型。

Also, you need to change your SQL query to reflect that this is in fact a string and not a number you're storing (numbers don't have preceding zeroes).

此外，您需要更改SQL查询以反映这实际上是一个字符串而不是您正在存储的数字（数字没有前面的零）。

So, to recap:

所以，回顾一下：

Verify your DEPT column is using a VARCHAR type datatype.
验证DEPT列是否使用VARCHAR类型数据类型。
Change your SQL query to reflect it is a string and not a number you're storing.
更改您的SQL查询以反映它是一个字符串而不是您正在存储的数字。

Example:

例：

Update employee set DEPT='02150' where EMP_ID=14447 and year=2017 and ACC=1

更新员工集DEPT ='02150'，其中EMP_ID = 14447，年= 2017，ACC = 1

#2

Skip the middle man, being the 'defines'. Just have DCL generate the desired line. The example below assumes the DCL variables are pure strings, to preserve leading zeroes and spaces as others pointed out. If some variables are integers, then you have to force them to be a string forcing a substitution inside double quotes using

撇开中间人，成为“定义”。只需要DCL生成所需的行。下面的示例假设DCL变量是纯字符串，以保留其他人指出的前导零和空格。如果某些变量是整数，那么你必须强制它们成为一个字符串，强制使用双引号内的替换

"''numeric_variable'"

suggestion:

建议：

$ write SQL "set feedback on"
$ write SQL "spool  Manager:[Aksh]akshay4.log"
$ write SQL "Update EMPLOYEE set DEPT='" + dept 
    + "' where EMP_ID='" + ref
    + "' and YEAR='" + file 
    + "' and ACC='" + acc
    + "';"
$ write SQL "commit;" 
$ write SQL "exit;"

#1