If count() is 0, insert data
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');
1 row created.
SQL>
SQL>
SQL> DECLARE
2
3 v_count PLS_INTEGER := 0;
4
5 BEGIN
6 SELECT COUNT(1) INTO v_count FROM emp WHERE id = 55;
7
8 IF v_count = 0
9 THEN
10 INSERT INTO emp VALUES (54, 'R', 'W');
11 DBMS_OUTPUT.PUT_LINE('Added emp');
12 ELSE
13 DBMS_OUTPUT.PUT_LINE('emp already exists');
14 END IF;
15
16 COMMIT;
17
18 EXCEPTION
19 WHEN OTHERS
20 THEN
21 DBMS_OUTPUT.PUT_LINE(SQLERRM);
22 END;
23 /
Added emp
PL/SQL procedure successfully completed.
SQL> drop table emp;
Table dropped.
SQL>
Related examples in the same category