在 PL/SQL 中创建过程以显示表中的所有信息

Creating a Procedure in PL/SQL to display all information from a table

提问by DaBulls33

As the title states, I am trying to create a procedure to display all information about employees(Employee_ID, NAME, EMAIL_ADDRESS, HIRE_DATE, and UPDATE_DATE). The catch is that if the employee_id is not in the table then the output should display all information about employees. My output I am shooting for is:

正如标题所述，我正在尝试创建一个过程来显示有关员工的所有信息（Employee_ID、NAME、EMAIL_ADDRESS、HIRE_DATE 和 UPDATE_DATE）。问题是如果employee_id 不在表中，则输出应显示有关雇员的所有信息。我要拍摄的输出是：

EXEC get_employee_info_by_employee_id(4565)

Employee_ID:       4565
NAME:              Bob Doe
EMAIL_ADDRESS:     [email protected]
HIRE_DATE:         30-JUN-10
UPDATE DATE:       13-JULY-12

Here is my current code:

这是我当前的代码：

create or replace PROCEDURE get_employee_info_by_employee_id 
(   
p_employee_id   NUMBER DEFAULT -1
)
AS
   v_count   NUMBER;
BEGIN
      SELECT  COUNT(*)
      INTO    v_count
      FROM    employee
      WHERE   employee_id = p_employee_id;

  IF p_employee_id IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_id);
    DBMS_OUTPUT.PUT_LINE('NAME: ' || name);
    DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || email_address);
    DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || hire_date);
    DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || update_date);

  ELSE
      SELECT  COUNT(*)
      INTO    v_count
      FROM    employee
      WHERE   employee_id = p_employee_id;
  END IF;
END;

I know this is far from correct. As a beginner, I am looking for how to edit and work with the code I currently have and learn the next steps to take to solve this. I know my ELSE statement is not correct and I'm kind of stuck as to what to do next. Thanks a lot everyone!

我知道这远非正确。作为初学者，我正在寻找如何编辑和使用我目前拥有的代码并学习解决此问题的后续步骤。我知道我的 ELSE 语句是不正确的，我有点不知道下一步该做什么。非常感谢大家！

回答by Bob Jarvis - Reinstate Monica

One More Way (tm):

另一种方式（tm）：

create or replace PROCEDURE get_employee_info_by_employee_id 
    (p_employee_id   NUMBER DEFAULT NULL)
AS
BEGIN
   -- This cursor will return
   --   - a single row if p_employee_id is specified and exists in the table
   --   - all rows in the table if p_employee_id is NULL (default value, or
   --     passed in as NULL)
   --   - all rows in the table if p_employee_id is specified but does not
   --     exist in the table

   FOR aRow IN (SELECT EMPLOYEE_ID, Name, Email_Address, Hire_Date, Update_Date
                FROM Employee
                WHERE Employee_ID = p_employee_id OR
                      p_employee_id IS NULL OR
                      0 = (SELECT COUNT(*)
                             FROM EMPLOYEE
                             WHERE EMPLOYEE_ID = p_employee_id)
   LOOP
     DBMS_OUTPUT.PUT_LINE('Employee ID: ' || aRow.EMPLOYEE_ID);
     DBMS_OUTPUT.PUT_LINE('NAME: ' || aRow.NAME);
     DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || aRow.EMAIL_ADDRESS);
     DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || aRow.HIRE_DATE);
     DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || aRow.UPDATE_DATE);
   END LOOP;
END get_employee_info_by_employee_id;

Share and enjoy.

分享和享受。

回答by Ed Gibbs

Here's a working example, with annotations. Note that it uses an exception rather than an IFto handle the employee not being found.

这是一个带有注释的工作示例。请注意，它使用异常而不是 anIF来处理未找到的员工。

Also, remember than when you use DBMS_OUTPUTyou have to enable it. Type SET SERVEROUTPUT ON SIZE 10000at the SQLPlus command line before executing your procedure. The size is the maximum number of characters to report, so 10K is more than enough.

另外，请记住，当您使用时，DBMS_OUTPUT您必须启用它。SET SERVEROUTPUT ON SIZE 10000在执行过程之前在 SQLPlus 命令行中键入。大小是要报告的最大字符数，因此 10K 绰绰有余。

create or replace PROCEDURE get_employee_info_by_employee_id 
(   
p_employee_id   NUMBER DEFAULT -1
)
AS
   -- You need to query the values you're showing into variables. The
   -- variables can have the same name as the column names. Oracle won't
   -- be confused by this, but I usually am - that's why I have the "v_"
   -- prefix for the variable names here. Finally, when declaring the
   -- variable's type, you can reference table.column%TYPE to use the
   -- type of an existing column.
   v_name Employee.Name%TYPE;
   v_email_address Employee.Email_Address%TYPE;
   v_hire_date Employee.Hire_Date%TYPE;
   v_update_date Employee.Update_Date%TYPE;
BEGIN
   -- Just SELECT away, returning column values into the variables. If
   -- the employee ID isn't found, Oracle will throw and you can pick
   -- up the pieces in the EXCEPTION block below.
   SELECT Name, Email_Address, Hire_Date, Update_Date
     INTO v_name, v_email_address, v_hire_date, v_update_date
     FROM Employee
     WHERE Employee_ID = p_employee_id;
   -- Fallthrough to here means the query above found one (and only one)
   -- row, and therefore it put values into the variables. Print out the
   -- variables.
   --
   -- Also note there wasn't a v_employee_id variable defined, because
   -- you can use your parameter value (p_employee_id) for that.
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id);
   DBMS_OUTPUT.PUT_LINE('NAME: ' || v_name);
   DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || v_email_address);
   DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || v_hire_date);
   DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || v_update_date);
EXCEPTION
   -- If the query didn't find a row you'll end up here. In this case
   -- there's no need for any type of fancy exception handling; just
   -- reporting that the employee wasn't found is enough.
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee number ' || p_employee_id || ' not found.');
END;

回答by user10838478

1. you can create New Table as Employee_History_infas created Employee Table
2. Create Procedure with Parameters as Employee_Columnsinfo on your Employee Table
3. Create Trigger Pre_UpdateEmployee Table can insert new_infoin Your New Table Employee_History_inf

1. 您可以Employee_History_inf像创建员工表一样创建新表
2. 使用参数作为Employee_Columns员工表上的信息创建过程
3. 创建触发器Pre_Update员工表可以插入new_info到您的新表中Employee_History_inf

回答by David Aldridge

A query such as:

一个查询，例如：

select ...
from   employees
where  employee_id = 123
union all
select ...
from   employees
where  not exists (select null from employees where employee_id = 123)

... will return the single employee if it is found or all employees if it is not.

... 将返回单个员工（如果找到）或所有员工（如果未找到）。