oracle oracle中如何编写存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10281348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write a stored procedure in oracle?
提问by ashishjmeshram
I am new to oracle so not sure about how to write stored procedure in oracle. Currently I am googling and reading some articles/blogs on net on SP's in oracle. I am begining to get some ideas about it. Even then I have following few queries.
我是 oracle 的新手，所以不确定如何在 oracle 中编写存储过程。目前，我正在谷歌搜索并阅读有关 oracle 中 SP 的网络上的一些文章/博客。我开始对它有了一些想法。即使那样，我也有以下几个疑问。
What I am trying to do is,
我想做的是，
I have a Employee table with emp_id, Region, Division, Product, Title etc.
我有一个带有 emp_id、Region、Division、Product、Title 等的 Employee 表。
I also have emp_update table with emp_id, column (what is changed), old_value, new_value and date.
我还有带有 emp_id、列（更改内容）、old_value、new_value 和日期的 emp_update 表。
e.g. emp_update table will have a row for for emp_id = 10, which says region is changed from Asia to UK on 30 April.
例如，emp_update 表将有一行 for emp_id = 10，表示地区在 4 月 30 日从亚洲更改为英国。
Another row for same emp which says product changed from A to B on 30 April.
同一 emp 的另一行表示产品在 4 月 30 日从 A 更改为 B。
So, multiple entries for same employee and on same date.
因此，同一员工和同一日期的多个条目。
Now on that particular date I am running a cron job which will run this Sp to update employee table to these updates.
现在在那个特定的日期我正在运行一个 cron 作业，它将运行这个 Sp 来更新员工表到这些更新。
e.g. On date 30 April, update employee set region = UK, Product = B where emp_id = 10.
例如，在 4 月 30 日，更新员工集 region = UK，Product = B，其中 emp_id = 10。
I want to run a single update statement for a employee with all its updates from emp_update table on that date.
我想为员工运行单个更新语句，并在该日期从 emp_update 表中获取所有更新。
So basically,
所以基本上，
get all employees from emp_update with update on today. iterate over that employees updates, assembple them in a single update statement and then execute that update query. repeat for each employee.
从 emp_update 获取所有员工，并在今天更新。迭代员工更新，将它们组装在单个更新语句中，然后执行该更新查询。对每位员工重复一遍。
Please help
请帮忙
EDIT 1:-
编辑 1:-
CREATE OR REPLACE
PROCEDURE SP_RUN_EMPLOYEE_UPDATES
IS
CURSOR
c_emp
IS
SELECT DISTINCT(employee_id) as employee_id FROM BI_EMPLOYEE_UPDATE WHERE EFFECTIVE_DATE = to_date('30-Apr-2012','dd-mm-yy');
BEGIN
FOR employee in c_emp
LOOP
CURSOR
c_emp_update
IS
SELECT * FROM BI_EMPLOYEE_UPDATE WHERE employee_id = :employee.employee_id AND EFFECTIVE_DATE = to_date('30-Apr-2012','dd-mm-yy');
FOR emp_update in c_emp_update
LOOP
-- dbms_output.put_line(emp_update.column_name);
END LOOP;
END LOOP;
END;
Currently this is what I have done so far
目前这是我迄今为止所做的
采纳答案by Adam Hawkes
This question seems to be less about stored procedures and more about creating dynamic queries. Basically, you want this:
这个问题似乎与存储过程有关，而与创建动态查询有关。基本上，你想要这个：
- Cron job calls stored procedure
- Proc will iterate over the records in
emp_updatewith today's date, sorted byemployee_id - You will start building a dynamic SQL for the set expressionof the update statement based off the values from the query. Simply "COLUMN_NAME = NEW_VALUE," concatenated over and over for each column for that employee_id/date combination.
- Build your SQL with the template
UPDATE EMP SET <previous string here> WHERE EMPLOYEE_ID = <emp_id>. - Execute dynamic update sql
- Cron 作业调用存储过程
- Proc 将迭代
emp_update今天日期的记录，按以下顺序排序employee_id - 您将根据查询中的值开始为更新语句的集合表达式构建动态 SQL 。只需“COLUMN_NAME = NEW_VALUE”，为该员工 ID/日期组合的每一列一遍又一遍地连接。
- 使用模板构建您的 SQL
UPDATE EMP SET <previous string here> WHERE EMPLOYEE_ID = <emp_id>。 - 执行动态更新sql
