PLSQL修改VARCHAR2列数据
我正在开发一个应用程序，该应用程序涉及评估对车辆所做的修改，并对 Oracle 10g 数据库中存储的数据进行一些数字处理。不幸的是，我的数据库中只有文本数据，但我需要使用数字而不是文本。我想知道是否有人可以帮助我了解如何使用 PLSQL 对 Oracle 10g 数据库中的 VARCHAR2 列数据执行字符串操作：
例如：我需要在名为 CARS 的表中获取名为 TOP_SPEED 的 VARCHAR2 列，解析文本其列中的数据将其分解为两个新值，并将这些新值插入到 CARS 表中两个新的 NUMBER 类型列中：TOP_SPEED_KMH 和 TOP_SPEED_MPH。
TOP_SPEED 列中的数据如下：例如。 “153 km/h (94.62 mph)”
我想将 153.00 的值保存到 TOP_SPEED_KMH 列中，并将 94.62 值保存到 TOP_SPEED_MPH 列中。
我认为我在查询/脚本中必须做的是：
- 将 TOP_SPEED 中的文本数据选择到本地文本变量中
- 修改本地文本变量并将新值保存到两个数字变量中
- 将这两个数字变量写回到相应的 TOP_SPEED_KMH和 TOP_SPEED_MPH 列
有人可以确认我走在正确的轨道上吗？如果有人有时间的话，我也非常感谢任何示例代码。
干杯
I am working on an app that involves evaluating modifications made to vehicles, and does some number crunching from figures stored in an Oracle 10g database. Unfortunately, I only have a text data in the database, yet I need to work with numbers and not text. I would like to know if anyone could help me with understanding how to perform string operations on VARCHAR2 column data in an Oracle 10g database with PLSQL:
For example: I need to take a VARCHAR2 column named TOP_SPEED in a table named CARS, parse the text data in its column to break it up into two new values, and insert these new values into two new NUMBER type columns in the CARS table, TOP_SPEED_KMH and TOP_SPEED_MPH.
The data in the TOP_SPEED column is as such: eg. "153 km/h (94.62 mph)"
I want to save the value of 153.00 into the TOP_SPEED_KMH column, and the 94.62 value into TOP_SPEED_MPH column.
I think what I have to do in a query/script is this:
- select the text data in TOP_SPEED into a local text variable
- modify the local text variable and save the new values into two number variables
- write back the two number variables to the corresponding TOP_SPEED_KMH and TOP_SPEED_MPH columns
Could someone please confirm that I am on the right track? I would also really appreciate any example code if anyone has the time.
Cheers
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我认为最好只保留 top_speed_kmh 列，并去掉 mph 列。由于一英里的公里数永远不会改变，因此您只需乘以 0.6 即可转换为英里。因此，您可以执行与 N West 建议的相同的更新语句，而无需 mph 列：
更新汽车设置 TOP_SPEED_KMH = TO_NUMBER(SUBSTR(1, (INSTR(UPPER(TOP_SPEED), "KM/H") -1)));
每当您需要每小时的速度时，只需这样做
选择top_speed_kmh*0.6作为汽车的top_speed_mph；
I think it's a better idea to just have the top_speed_kmh column, and get rid of the mph one. As the number of kms in a mile never changes, you can simply multiply by 0.6 to convert to miles. So you can do the same update statement as N West suggested without the mph column:
UPDATE CARS SET TOP_SPEED_KMH = TO_NUMBER(SUBSTR(1, (INSTR(UPPER(TOP_SPEED), "KM/H") -1)));
And whenever you need the mph speed, just do
Select top_speed_kmh*0.6 as top_speed_mph from cars;
对于解析位，您可能会使用 REGEXP_SUBSTR 或 INSTR 与 SUBSTR
然后使用 TO_NUMBER 转换为数字
您可以为每个解析创建一个 PL/SQL 函数，返回数字值，并对字段运行 UPDATE 查询，或者您可以创建一个 PL/SQL 过程，其中的游标循环遍历所有要更新的数据。
以下是一些内置函数的链接：
http://psoug.org/reference/substr_instr.html
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions116.htm
For the parsing bit, you would probably use either REGEXP_SUBSTR or INSTR with SUBSTR
Then use TO_NUMBER to convert to number
You can either create a PL/SQL function for each parsing, returning the number value, and run an UPDATE query on the fields, or you could create a PL/SQL procedure with a cursor looping over all the data that is to be updated.
Here are som links for some of the built-ins:
http://psoug.org/reference/substr_instr.html
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions116.htm
您甚至可能根本不需要使用 PL/SQL 来完成此操作。
只要列中的数据一致“99.99 km/h (99.99 m/h)”，您就可以直接使用 SQL 执行此操作：
设置操作通常比过程操作快得多。
You probably don't even need to do this with PL/SQL at all.
As long as the data in the column is consistent "99.99 km/h (99.99 m/h)" you could do this directly with SQL:
Set-operations are typically much faster than procedural operations.
听起来你应该有一些数字列来存储这些解析出的值。不要总是调用某些解析例程（无论是 regexp 或 substr 或自定义函数），而是一次传递表中的所有数据并填充新的数字字段。您还应该修改 ETL 流程以填充新的数字字段。
如果您需要数字并且可以解析它们，请执行一次（希望至少在集结区域或非工作时间），然后获得您想要的数字。现在您可以自由地进行算术运算以及您期望从实数中获得的所有其他内容；）
Sounds like you should have some number columns to store these parsed out values. Instead of always calling some parsing routine (be it regexp or substr or a custom function), pass through all the data in the table(s) ONCE and populate the new number fields. You should also modify the ETL process to populate the new number fields moving forward.
If you need numbers and can parse them out, do it once (hopefully in a staging area or off hours at least) and then have the numbers you want. Now you're free to do arithmetic and everything else you'd expect from real numbers ;)
谢谢大家，很高兴能够利用大家的意见来得到以下答案：
Thanks everyone, it was nice to be able to use everyone's input to get the answer below: