plsql - SQL 过程问题
问题描述
我目前有两个表可以使用(如下所列)。下面发布的是我当前程序的代码,它接受一个employeeID 值并输出他们的信息。我需要修改它,另外列出部门主席是谁,如果员工已经是部门负责人,它将把他们列为“部门主席”。
我尝试创建一个单独的视图来告诉部门主席是谁并在循环中调用它,但没有成功。很卡在这里。
解决方案
create or replace procedure emp_records (
p_emp_id employees.employeeid%type
) is
l_department_name departments.departmentname%type;
l_department_id departments.departmentid%type;
l_employee_first_name employees.firstname%type;
l_employee_last_name employees.lastname%type;
l_employee_employee_id employees.employeeid%type;
l_chair_id departments.departmentchair%type;
l_chair_name varchar2(100);
begin
-- For employee details
select
departmentname,
firstname,
lastname,
employeeid,
departmentid
into
l_department_name,
l_employee_first_name,
l_employee_last_name,
l_employee_employee_id,
l_department_id
from departments d
join employees e
on e.departmentid = d.departmentidusing
where employeeid = p_emp_id;
--For CHair details
select departmentchair
into l_chair_id
from department
where departmentid = l_department_id;
if l_chair_id = l_employee_employee_id
then
select 'Is the department chair' into l_chair_name from dual;
else
select 'Department Chair: ' || firstname || ' ' || lastname
into l_chair_name
from employees
where employeeid = l_chair_id;
end if;
-- For output
dbms_output.put_line('Information for Employee #' || p_emp_id);
dbms_output.put_line('Full Name: ' || l_employee_first_name || ' ' || l_employee_last_name);
dbms_output.put_line('Department: ' || l_department_name);
dbms_output.put_line(l_chair_name);
exception
when no_data_found then
dbms_output.put_line('No records found for this Employee.');
end;
/
希望这会有所帮助。
推荐阅读
- google-cloud-platform - 云数据流找不到模型
- javascript - 使用 SweetAlert 创建故事
- html - 当我从 zeplin 复制样式并将其应用于我的按钮时,它不起作用
- matlab - 使用 imagesc() 绘制 3D 矩阵的对角线切片
- java - 如何将jointMatrix传递给着色器glTF?(java opengles20)
- javascript - 未找到模块:无法从与汇总打包的依赖项中解析“fs”
- python - 一旦我对views.py之外的模型进行了更改,有没有办法可以动态更新Django的详细视图?
- c# - 为 HTTP POST 请求将字符串转换为 JSON
- r - dplyr:group_by + 总结没有按预期工作
- spring-integration - Spring Integration Mail:在所有数据库插入后发送电子邮件