Mega Code Archive
Select count value into a variable
<![CDATA[
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE),
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
SQL>
SQL> DECLARE
2 v_count NUMBER(10) := 0;
3
4 BEGIN
5
6 SELECT count(1)
7 INTO v_count
8 FROM employee
9 WHERE id = 5;
10
11 IF v_count = 0
12 THEN
13 INSERT INTO employee (id, first_name, last_name)
14 VALUES (5, 'Randy', 'Stauf');
15 END IF;
16 ROLLBACK;
17 EXCEPTION
18 WHEN OTHERS
19 THEN
20 DBMS_OUTPUT.PUT_LINE(SQLERRM);
21 END;
22 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
]]>