我在Oracle PL / SQL中有一个作业。我必须计算在一个经理下工作的最大员工人数,如果该经理有7个以上的员工在工作,我必须提出一个例外,该异常信息如下:
(“经理|| manager_name ||'具有在其下工作的最大员工数”。)
否则,我必须插入新员工并给出消息:
(“为经理|| manager_name插入了新雇员)。
我已经编写了代码,但是我知道出了点问题。
create table temp_emp as select * from employees;
select * from temp_emp;
create or replace procedure insert_emp(mngrId IN temp_emp.manager_id%type)
IS
ex_hugemp EXCEPTION;
emp_counter NUMBER;
fname temp_emp.first_name%type;
BEGIN
SELECT COUNT(*) INTO emp_counter
FROM temp_emp
WHERE temp_emp.manager_id=mngrId;
IF emp_counter > 7 THEN
RAISE ex_hugemp;
ELSE
INSERT INTO temp_emp(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER
,HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT
,MANAGER_ID,DEPARTMENT_ID)
VALUES(LENGTH(EMPLOYEE_ID)+1, 'KAY', 'HORSTMAN', NULL, NULL
,'28-MAY-2013', 'IT_PROG', 24000, NULL, 103, 60);
DBMS_OUTPUT.PUT_LINE('It was inserted a new employee for the manager '||fname);
END IF;
EXCEPTION
WHEN ex_hugemp THEN
DBMS_OUTPUT.PUT_LINE('Manager '||fname||' has maximium number of employees working under him.');
END;
/
您的变量fname为空。填充:
SELECT first_name
INTO fname
FROM temp_emp
WHERE employee_id = mngrid;
用于插入相同的mngrid。为什么是103?
什么是
LENGTH(EMPLOYEE_ID)+1
第一:这将把employee_id转换成字符串,从字符串中获取长度并加1。您真的想要这个吗?。第二点:您不能在values()中使用列名。创建序列(将值更改为1即可开始):
CREATE SEQUENCE TEMP_EMP_SEQ START WITH 1;
而不是在您的程序中使用它
temp_emp_seq.nextval
。
CREATE OR REPLACE PROCEDURE insert_emp (mngrid IN temp_emp.manager_id%TYPE) IS
ex_hugemp EXCEPTION;
emp_counter NUMBER;
fname temp_emp.first_name%TYPE;
BEGIN
SELECT first_name
INTO fname
FROM temp_emp
WHERE employee_id = mngrid;
SELECT COUNT (*)
INTO emp_counter
FROM temp_emp
WHERE temp_emp.manager_id = mngrid;
IF emp_counter > 7 THEN
RAISE ex_hugemp;
ELSE
INSERT INTO temp_emp (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES( temp_emp_seq.nextval,'KAY','HORSTMAN',NULL,NULL,TO_DATE('28-05-2013','dd-mm-yyyy'),'IT_PROG',24000,NULL,mngrid,10);
DBMS_OUTPUT.put_line ('It was inserted a new employee for the manager ' || fname);
END IF;
EXCEPTION
WHEN ex_hugemp THEN
DBMS_OUTPUT.put_line ('Manager ' || fname || ' has maximium number of employees working under him.');
END;
/
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句