By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE EMPP(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(15),
JOB VARCHAR2(15),
SAL NUMBER(7,2)
);
INSERT INTO empp (empno, ename, job, sal)
SELECT 1, 'Alice', 'Job1', 500 FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Job2', 600 FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Job3', 600 FROM DUAL;
3 rows affected
DECLARE
v_same_sal EXCEPTION;
v_sal EMPP.SAL%TYPE := 500;
v_no EMPP.EMPNO%TYPE;
v_ename EMPP.ENAME%TYPE;
v_job EMPP.JOB%TYPE;
v_total_row NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total_row
FROM EMPP
WHERE SAL = v_sal;
dbms_output.put_line(v_sal);
IF (v_total_row > 1) THEN
raise v_same_sal;
ELSE
SELECT EMPNO, ENAME, JOB
INTO v_no, v_ename, v_job
FROM EMPP
WHERE SAL = v_sal;
dbms_output.put_line('No : ' || v_no);
dbms_output.put_line('Emp Name : ' || v_ename);
dbms_output.put_line('Job : ' || v_job);
END IF;
EXCEPTION
WHEN v_same_sal THEN
dbms_output.put_line('There are more than 1 employee with same salary');
END;
/
1 rows affected
dbms_output:
500
No : 1
Emp Name : Alice
Job : Job1
DECLARE
v_same_sal EXCEPTION;
v_sal EMPP.SAL%TYPE := 600;
v_no EMPP.EMPNO%TYPE;
v_ename EMPP.ENAME%TYPE;
v_job EMPP.JOB%TYPE;
v_total_row NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total_row
FROM EMPP
WHERE SAL = v_sal;
dbms_output.put_line(v_sal);
IF (v_total_row > 1) THEN
raise v_same_sal;
ELSE
SELECT EMPNO, ENAME, JOB
INTO v_no, v_ename, v_job
FROM EMPP
WHERE SAL = v_sal;
dbms_output.put_line('No : ' || v_no);
dbms_output.put_line('Emp Name : ' || v_ename);
dbms_output.put_line('Job : ' || v_job);
END IF;
EXCEPTION
WHEN v_same_sal THEN
dbms_output.put_line('There are more than 1 employee with same salary');
END;
/
1 rows affected
dbms_output:
600
There are more than 1 employee with same salary
DECLARE
v_same_sal EXCEPTION;
v_sal EMPP.SAL%TYPE := 400;
v_no EMPP.EMPNO%TYPE;
v_ename EMPP.ENAME%TYPE;
v_job EMPP.JOB%TYPE;
v_total_row NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total_row
FROM EMPP
WHERE SAL = v_sal;
dbms_output.put_line(v_sal);
IF (v_total_row > 1) THEN
raise v_same_sal;
ELSE
SELECT EMPNO, ENAME, JOB
INTO v_no, v_ename, v_job
FROM EMPP
WHERE SAL = v_sal;
dbms_output.put_line('No : ' || v_no);
dbms_output.put_line('Emp Name : ' || v_ename);
dbms_output.put_line('Job : ' || v_job);
END IF;
EXCEPTION
WHEN v_same_sal THEN
dbms_output.put_line('There are more than 1 employee with same salary');
END;
/
dbms_output:
400
ORA-01403: no data found
ORA-06512: at line 19