在oracle中更新表数据的存储过程 [英] Stored procedure to update table data in oracle

问题描述

亲爱的所有我在数据库中首先有两个表如下：

Dear All I have two tables in Database first is the following :

EmployeeID   01/04/2017   02/04/2017    03/04/2017   04/04/2017
----------------------------------------------------------------
1             1-9,6
2
3
4

第二张表是

second table is

EmployeeID    DayDate       SCHEDULEID    SHIFT 
------------------------------------------------
1            01/04/2017        1             9,6
1            02/04/2017        1             5
1            03/04/2017        30            7,6
1            04/04/2017        30            9
2            01/04/2017        30            9
2            02/04/2017        6             3
3            01/04/2017        6             7,6
3            03/04/2017        6             9

i想要创建一个存储过程或SQL语句来更新第二个表中的第一个表数据，以便为users设置数据yeeid = 1日期01/04/2017是

SCHEDULEID-SHIFT是1-9,6



什么我试过了：



我试图用光标和循环遍历第一个表并从第二个表中选择shift的值然后更新列数据，但它无法正常工作

所以可以得到帮助！

i want make a stored procedure or SQL statement to update the first table data from the second as to set the data like for employeeid =1 in the date 01/04/2017 is
SCHEDULEID-SHIFT which is 1-9,6

What I have tried:

I have tried to do it with cursor and loop through first table and select the value of the shift from second table then update the column data but it doesn't work properly
so can get help in this !!

推荐答案

好的，首先 - 道歉，以下解决方案是在SQL Server的T-SQL中。 SQLFiddle现在不适合我，几周前我没有使用Oracle。



这就是我的意思，使用我上面评论中给出的链接调整此代码以使用Oracle（如果它还没有）

Ok, first up - my apologies, the following solution is in T-SQL for SQL Server. SQLFiddle is not working for me just now and I unistalled Oracle a few weeks ago.

This is the principle that I mean, use the link I gave in my comment above to adjust this code to work on Oracle (if it doesn't already)

select EmployeeID, ISNULL([01/04/2017],''),ISNULL([02/04/2017],''),ISNULL([03/04/2017],''),ISNULL([04/04/2017],'')
FROM
(
	select EmployeeID, DayDate, [Shift]
	from table2
) AS Q
PIVOT
(
	Max([Shift]) for DayDate IN ([01/04/2017],[02/04/2017],[03/04/2017],[04/04/2017])
) AS pvt

结果

Results are

1	9	5	7,6	9
2	9	3		
3	7,6		9	

顺便说一句，这是一个非常糟糕的数据库设计。你永远不应该用逗号分隔列中的项目 - 使用另一个表并链接它，或者允许每个员工每个日期有多个条目（只要班次号不同）





这可能仍需要一些调整才能在Oracle中工作

As an aside, that is a very bad database design. You should never separate items in a column with commas - use another table and link it, or allow for multiple entries per Employee per Date (as long as the shift number is different)


This will probably still need some adjustment to work in Oracle

INSERT INTO table1 (EmployeeID, [01/04/2017],[02/04/2017],[03/04/2017],[04/04/2017])
(
	select EmployeeID, ISNULL([01/04/2017],''),ISNULL([02/04/2017],''),ISNULL([03/04/2017],''),ISNULL([04/04/2017],'')
	FROM
	(
		select EmployeeID, DayDate, RES = TO_CHAR([SCHEDULEID]) + '-'+[Shift]
		from table2
	) 
	PIVOT
	(
		Max(RES) for DayDate IN ([01/04/2017],[02/04/2017],[03/04/2017],[04/04/2017])
	) 
);

这篇关于在oracle中更新表数据的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！