Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE DEPARTMENT
- (DEPARTMENT_ID NUMBER PRIMARY KEY,
- DEPARTMENT_NAME VARCHAR(30) NOT NULL
- );
- CREATE TABLE JOBS
- (JOB_ID NUMBER PRIMARY KEY,
- JOB_TITLE VARCHAR(35) NOT NULL,
- MIN_SALARY DECIMAL NOT NULL,
- MAX_SALARY DECIMAL NOT NULL
- );
- CREATE TABLE EMPLOYEES
- (EMPLOYEE_ID NUMBER PRIMARY KEY,
- FIRST_NAME VARCHAR(20) NOT NULL,
- LAST_NAME VARCHAR(25) NOT NULL,
- EMAIL VARCHAR(25) NOT NULL,
- PHONE_NUMBER VARCHAR(20) NOT NULL,
- HIRE_DATE DATE NOT NULL,
- JOB_ID NUMBER NOT NULL,
- SALARY DECIMAL NOT NULL,
- DEPARTMENT_ID NUMBER NOT NULL,
- CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
- CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
- );
- INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
- VALUES(1,'IT');
- INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
- VALUES(2,'Sales');
- INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
- VALUES (1,'IT Administrator',250000.00,50000.00);
- INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
- VALUES (2,'Salesman',200000.00,40000.00);
- INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
- VALUES (1,'Tony','Starc','starc@gmail.com','0123456789',TO_DATE('15/1/2008','DD/MM/YYYY'),1,45000.00,1);
- INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
- VALUES (2,'Bruce','Wayne','bruce@gmail.com','0123456788',TO_DATE('15/1/2009','DD/MM/YYYY'),1,40000.00,1);
- INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
- VALUES (3,'Larry','Ellison','larry@gmail.com','0123456787',TO_DATE('15/1/2010','DD/MM/YYYY'),1,30000.00,1);
- INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
- VALUES (4,'Steve','Jobs','steve@gmail.com','0123456786',TO_DATE('15/1/2011','DD/MM/YYYY'),2,35000.00,2);
- INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
- VALUES (5,'Remy','Lebeau','remy@gmail.com','0123456785',TO_DATE('15/1/2012','DD/MM/YYYY'),2,30000.00,2);
- INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
- VALUES (6,'Clark','Kent','clark@gmail.com','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);
- CREATE OR REPLACE PROCEDURE pr_emps_per_dept_jc450912(p_dept_id NUMBER)
- AS
- BEGIN
- DECLARE
- emp_id EMPLOYEES.EMPLOYEE_ID%TYPE;
- f_name EMPLOYEES.FIRST_NAME%TYPE;
- l_name EMPLOYEES.LAST_NAME%TYPE;
- job_name JOBS.JOB_TITLE%TYPE;
- BEGIN
- SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_TITLE
- INTO emp_id,f_name,l_name,job_name
- FROM EMPLOYEES,JOBS,DEPARTMENT
- WHERE DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
- AND JOBS.JOB_ID = EMPLOYEES.JOB_ID
- AND EMPLOYEES.DEPARTMENT_ID = p_dept_id;
- END;
- END;
- EXECUTE pr_emps_per_dept_jc450912(1);
- Error report -
- ORA-01422: exact fetch returns more than requested number of rows
- ORA-06512: at "SYS.PR_EMPS_PER_DEPT_JC450912", line 10
- ORA-06512: at line 1
- 01422. 00000 - "exact fetch returns more than requested number of rows"
- *Cause: The number specified in exact fetch is less than the rows returned.
- *Action: Rewrite the query or change number of rows requested
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement