使用光标电流更改数据过程 [英] Change data procedure with cursor current of

问题描述

我需要在SQL Developer中创建一个可以将数据从一列移动到另一列的过程。这些列在不同的表中，所以我不知道如何编写代码。

我假设要使用Cursor从一列传输数据，然后使用当前的语句插入到其他列，虽然我不知道如何做到，可以使用一些帮助。
我甚至不知道你怎么能做到这一点。
表1是用customernumber，first namn，last namne和password建立的
表2只是customernumber和密码。
i需要将密码从表2转移到表1

 光标c_newpass是
 select * / passwd / cnumbr / cnmbr，passwd  - 不知道在这里选择什么 -  
从customersecurity 
更新--passwd，在另一个表中的列名为customer-- 
  

在这之后，我会在这里输入什么？

开始程序，我不知道接下来要写什么，关于
的更新passwd
从客户
其中c_newpass的电流

我真的不知道，但我知道我写的例子不工作。但我想一个循环可以用来更新每一行，因为我假设你不能同时更新整个列。
我会感激，如果有人可以写整个程序，至少一个例子，所以我知道该怎么办。
感谢您的任何帮助！

解决方案

更新：您使用的是Oracle，这是 链接 可能有助于为您清理...

创建光标时，您将在两个表之间进行连接，并指定更新（您要更新的列）。从那里开始， WHERE CURRENT OF c_newpass 只是指与c_newpass相关联的FETCH语句处理的最后一行。

 游标c_newpass IS 
 select customersecurity.password ，customersecurity.cnumbr，table1.cnumbr，table1.password 
来自customersecurity，table1 
用于更新table1.password 
  

然后，您应该可以循环访问c_newpass，获取下一行并更新table1

 更新table1 
设置密码= password 
当前的c_newpass 
  

SQL SERVER示例：
它可能不是您需要的，但它可以告诉您游标的工作方式以及它们如何完成您所需要的。我有2个表，我需要转移/复制Foo中的名称到Bar的名称，因为Bar表有NULL名称开始。我创建2个变量，一个用于ID，一个用于保存光标（someCursor）当前所在位置的内容。一旦你设置了游标，你需要从它获取项目，它是 FETCH NEXT 语句，并使用 INTO 设置 @ID 和 @Name 的变量。我通过检查 @@ Fetch_Status 开始一个while循环，以确保上一条语句成功。如果是这样，我使用刚刚设置的变量来更新Bar表，匹配ID，并使用@Name的内容更新Name列。一旦完成，我再次使用 FETCH NEXT 获得游标中的下一个项目。假设游标中有另一个项目，并且它成功，它会一遍又一遍地执行。

我认为你使用的是不同于SQL Server的DBMS ，但概念应该是类似的。您将根据customersecurity表创建游标，选择ID和密码，然后根据这些列更新新表。

 创建表Foo（
 ID int标识主键，
名称varchar（20）
）
 
创建表格主键
名称varchar（20）
）
 
插入到Foo值（'ABC'）
插入到Foo值（'XYZ'）
插入到Foo值（'JMK'）
 
插入到值值（NULL）
插入到值值（NULL）
插入到值值（NULL）
 
声明@ID int，@name varchar（20）
 
声明someCursor CURSOR FOR 
选择ID，名称From Foo按ID排序ID 
 
 OPEN someCursor 
 
 FETCH NEXT FROM someCursor 
 INTO @ID，@name 
 
 WHILE @@ Fetch_Status = 0 
 BEGIN 
更新栏
 Set Name = @Name 
其中ID = @ID 
 
 FETCH NEXT FROM someCursor 
 INTO @ID，@name 
 END 
 $ b b关闭someCursor 
释放someCursor 
 
 select * from Foo 
 select * from Bar 
  

I need to make a procedure in SQL Developer that can move data from one column to another. These columns are in different tables so i don't know how to write the code.

I'm suppose to be using a Cursor to transfer the data from one column then inset in to he other column using the current of statement, though i don't know how to do it and could use some help. I don't even know how you can do this. Table 1 are build with customernumber, first namn, last namne and password table 2 is just customernumber and password. i need to transfer password from table 2 to table 1

Cursor c_newpass is
select */passwd/cnumbr/cnmbr,passwd -- dont know what to select here --
from customersecurity
for update of --passwd, which is in a column in another table called customer--

what do i type ^here^ then?

after this you begin the procedure and i don't have any idea what to write next, something about update passwd from customer where current of c_newpass

i really have no idea, but i know the examples i have written doesn't work. But i suppose a loop could be used to update each row, because i assume you can not update an entire column at the same time. I'd appreciate if someone could write the entire procedure, at least an example so i know what to do. Thanks for any kinds of help!

解决方案

UPDATE: Since you're using Oracle, the accepted answer on this link may help clear things up for you...

When you create your cursor, you'll join between the two tables you have and specify the for update of (column you want to update). From there, the WHERE CURRENT OF c_newpass just refers to the latest row processed by the FETCH statement associated with c_newpass.

This is just a rough idea of how I think it would work.

Cursor c_newpass IS 
select customersecurity.password, customersecurity.cnumbr, table1.cnumbr, table1.password
from customersecurity, table1
for update of table1.password

You should then be able to loop through c_newpass, fetching the next row and update table1

Update table1
Set password = password
WHERE CURRENT OF c_newpass

SQL SERVER example: It may not be what you need, but it can show you how cursors work and how they can accomplish what you need. I've got 2 tables, and I need to transfer/copy the names in Foo to the names in Bar because the Bar table has NULL names to start out with. I create 2 variables, one for ID and one for name which will be used to hold the contents of where the cursor (someCursor) currently is. Once you have the cursor set, you need to get items from it which is the FETCH NEXT statement and using the INTO to set the variables for @ID and @Name. I start a while loop by checking @@Fetch_Status to make sure that the previous statement was successful. If so, I use the variable that were just set to update the Bar table, matching the IDs and updating the Name column with the contents of @Name. Once that is done I get the next item in the cursor using FETCH NEXT again. Assuming that there is another item in the cursor, and it was successful, it will do it all over again.

I think you're using a different DBMS than SQL Server, but the concept should be similar. You'll create the cursor based off the customersecurity table, selecting the ID and Password, and then update the new table based off those columns.

Create Table Foo(
ID int identity primary key,
Name varchar(20)
)

Create Table Bar(
ID int identity primary key,
Name varchar(20)
)

Insert Into Foo Values('ABC')
Insert Into Foo Values('XYZ')
Insert Into Foo Values('JMK')

Insert Into Bar Values(NULL)
Insert Into Bar Values(NULL)
Insert Into Bar Values(NULL)

declare @ID int, @name varchar(20)

Declare someCursor CURSOR FOR 
Select ID, Name From Foo order by ID

OPEN someCursor

FETCH NEXT FROM someCursor
INTO @ID, @name

WHILE @@Fetch_Status = 0
BEGIN
    Update Bar
    Set Name = @Name
    Where ID = @ID

    FETCH NEXT FROM someCursor
    INTO @ID, @name
END

Close someCursor
Deallocate someCursor

select * from Foo
select * from Bar

这篇关于使用光标电流更改数据过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！