oracle PL/SQL 如何在单个存储过程中编写多条语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12784515/
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
PL/SQL how to write multiple statement in a single stored procedure
提问by Hoque Maqsud
I need to do the following problem,
我需要做以下问题，
Write a procedure that decreases the salary by a 10% for all employees who earn less than the current average salary. Treat the content of table EMP as “all employees”. Have your procedure print the name and salary of the first person whose salary is just below the average.
编写一个程序，将所有收入低于当前平均工资的员工的工资降低 10%。将表 EMP 的内容视为“所有员工”。让您的程序打印第一个工资略低于平均水平的人的姓名和工资。
What should be the approach to solve the problem?
解决问题的方法应该是什么？
Write a procedure that decreases the salary by a 10% for all employees who earn less than the current average salary.
编写一个程序，将所有收入低于当前平均工资的员工的工资降低 10%。
CREATE OR REPLACE PROCEDURE UPDATE_EMP IS
BEGIN
UPDATE EMP
SET SAL= SAL-(SAL*0.1)
WHERE SAL<(SELECT AVG(SAL) FROM EMP);
END;
AND THE OTHER ONE : Have your procedure print the name and salary of the first person whose salary is just below the average.
另一个：让你的程序打印第一个工资略低于平均水平的人的姓名和工资。
SELECT e.ename
, e.sal
from
(select ename
, sal
from emp
where sal < (select avg(sal)
from emp
)
order by sal desc
)e
where ROWNUM =1;
Now I need to connect both.. How could I do that ...
现在我需要将两者都连接起来......我怎么能做到......
回答by Eric Leschinski
It sounds like you are a bit fuzzy on what a stored procedure is and how it can help you do complicated tasks involving many SQL statements.
听起来您对什么是存储过程以及它如何帮助您完成涉及许多 SQL 语句的复杂任务有点模糊。
You follow these directions on how a construct a stored procedure.
您可以按照有关如何构造存储过程的这些说明进行操作。
http://www.devshed.com/c/a/Oracle/Oracle-Stored-Procedures/
http://www.devshed.com/c/a/Oracle/Oracle-Stored-Procedures/
Stored procedures are wonderful structures that allow you to put multiple SQL statements into one structure, saving out variables for use in the next SQL statement. So all you have to do is invoke the stored procedure, and all the sql statements are run, and your answer is returned or table modification is committed.
存储过程是一种奇妙的结构，它允许您将多个 SQL 语句放入一个结构中，从而保存变量以供下一个 SQL 语句使用。所以你所要做的就是调用存储过程，并运行所有的sql语句，并返回你的答案或提交表修改。
You need something like this:
你需要这样的东西：
CREATE OR REPLACE PROCEDURE UPDATE_EMP RETURN name, value IS
BEGIN
UPDATE EMP
SET SAL= SAL-(SAL*0.1)
WHERE SAL<(SELECT AVG(SAL) FROM EMP);
SELECT e.ename INTO name_to_return, e.sal INTO sal_to_return from
(select ename, sal from emp where sal < (select
avg(sal)from emp) order by sal desc)e where ROWNUM =1;
RETURN name_to_return, sal_to_return;
END;
The syntax may be a bit off, when you get it working, post your answer here as a new answer, and check mark it as the answer, and you are muchmore likely to get help like this in the future.
语法可能有点过，当你得到它的工作，在这里发表你的答案作为一个新的答案，查马克它作为回答，你是很多更容易得到帮助，这样的未来。
回答by Gaurav Soni
CREATE OR REPLACE PROCEDURE DISPLAY_EMP IS
IS
CURSOR emp_cur
IS
SELECT ename
,sal
FROM emp
WHERE SAL<( SELECT AVG(SAL) FROM EMP)
ORDER BY sal desc;
v_emp_row emp_cur%ROWTYPE;
BEGIN
--update all the employee having sal less than avg sal
UPDATE EMP
SET SAL= SAL-(SAL*0.1)
WHERE SAL<(SELECT AVG(SAL) FROM EMP);
--display all the employee having sal less than avg sal
OPEN emp_cur ;
LOOP
FETCH emp_cur INTO v_emp_row;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME : '||v_emp_row.ename||' '
||'SALARY : '||v_emp_row.sal);
END LOOP;
CLOSE emp_cur;
END DISPLAY_EMP;
--call the display_emp proc to display all the emp
BEGIN
DISPLAY_EMP ;
END;
回答by Hoque Maqsud
CREATE OR REPLACE PROCEDURE DISPLAY_EMP IS IS CURSOR emp_cur IS SELECT ename ,sal FROM emp WHERE SAL<( SELECT AVG(SAL) FROM EMP) ORDER BY sal desc; v_emp_row emp_cur%ROWTYPE;
创建或替换过程 DISPLAY_EMP IS 是 CURSOR emp_cur IS SELECT ename ,sal FROM emp WHERE SAL<( SELECT AVG(SAL) FROM EMP) ORDER BY sal desc; v_emp_row emp_cur%ROWTYPE;
BEGIN
--update all the employee having sal less than avg sal
UPDATE EMP
SET SAL= SAL-(SAL*0.1)
WHERE SAL<(SELECT AVG(SAL) FROM EMP);
--display all the employee having sal less than avg sal
OPEN emp_cur ;
LOOP
FETCH emp_cur INTO v_emp_row;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME : '||v_emp_row.ename||' '
||'SALARY : '||v_emp_row.sal);
END LOOP;
CLOSE emp_cur;
END DISPLAY_EMP;
--call the display_emp proc to display all the emp BEGIN DISPLAY_EMP ; END;
-- 调用 display_emp proc 来显示所有的 emp BEGIN DISPLAY_EMP ; 结尾;
回答by Luke Liu
You need one procedure to do the update, and another function or select statement to print the name and salary of the first person whose salary is just below the average.
您需要一个过程来进行更新，以及另一个函数或选择语句来打印第一个工资略低于平均水平的人的姓名和工资。
