cursor for loops to insert record into a table

[juniorplsql]

Hello i am fairly new to plsql and i am tring to create a cursor that inserts a record into my employees table

This is the structure of my employees table:

EMP_ID NOT NULL NUMBER
DEPT_ID NOT NULL NUMBER
FIRST_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(30)
INSERTED NOT NULL DATE
LAST_UPDATE NOT NULL DATE
HIRE_DATE NOT NULL DATE
JOB_ID NUMBER
SALARY NUMBER
COMM_PCT NUMBER

Here is my code:

1 DECLARE
2 v_emp_id employees.emp_id%TYPE;
3 v_dept_id employees.dept_id%TYPE;
4 v_inserted employees.inserted%TYPE;
5 v_last_update employees.last_update%TYPE;
6 v_hire_date employees.hire_date%TYPE;
7 CURSOR c_emp IS
8 SELECT EMP_ID, DEPT_ID, INSERTED, LAST_UPDATE, HIRE_DATE
9 FROM EMPLOYEES;
10 BEGIN
11 OPEN c_emp;
12 LOOP
13 FETCH c_emp INTO v_emp_id, v_dept_id, v_inserted, v_last_update, v_hire_date;
14 EXIT when c_emp%NOTFOUND;
15 INSERT INTO EMPLOYEES(emp_id, dept_id, inserted, last_update, hire_date)
16 VALUES(EMPLOYEES_SEQ.NEXTVAL, 10, SYSDATE, SYSDATE, '08-JUN-02');
17 END LOOP;
18 CLOSE c_emp;
19* END;

When I run it I get:

PL/SQL procedure successfully completed.

but when i do:

SQL> select * from employees;

no rows selected


I tried is this way but it inserted the records with the same data mulitple times:

DECLARE
CURSOR c_EMP_CURSOR IS
SELECT emp_id, dept_id, first_name, last_name, inserted, last_update, hire_date, job_id, salary, comm_pct
FROM EMPLOYEES;
emp_record c_EMP_CURSOR%ROWTYPE;
BEGIN
OPEN c_EMP_CURSOR;
LOOP
FETCH c_EMP_CURSOR INTO emp_record;
EXIT WHEN c_EMP_CURSOR%NOTFOUND;
INSERT INTO EMPLOYEES (emp_id, dept_id, first_name, last_name, inserted, last_update, hire_date, job_id, salary, comm_pct)
VALUES (EMPLOYEES_SEQ.NEXTVAL, 10, 'JOHN', 'SMITH', SYSDATE, SYSDATE, '10-JAN-02', 110, 7000, .10);
END LOOP;
CLOSE c_EMP_CURSOR;
END;


I combed in internet trying different things but cant get this to insert into my employees table. Could someone please show me the light.

 

[LKBrwnDBA]

If your EMPLOYEES table has no rows, the cursor does not return anything DUH!



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[johnherman]

An additional word of caution - there is no COMMIT within your cursor, so when your session ends, your data is gone. I believe SQL Plus still does an implicit commit when it is closed, but if you are running the PL/SQL in TOAD or SQL Workbench, it is not doing a commit.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile big data clouds)

 

[taupirho]

Things to check:

1) When you run your SQL statement in SQLPLUS

SELECT EMP_ID, DEPT_ID, INSERTED, LAST_UPDATE, HIRE_DATE
FROM EMPLOYEES;

does it actually return any data

2) If yes to the above you need to start entering debug statements into your code

use "dbms_output.put_line ..." in your cursor loop and remember do a "set server output on" before running your code.

3) Do you actually have to use PL/SQL. Why not just use insert into ... select .... from ... in SQLPLUS?

In order to understand recursion, you must first understand recursion.

 

[juniorplsql]

There isn't any data in the table. I guess what I want to do is to see if I can write a plsql statement that will insert data into a table that I just created.

 

[Andrzejek]

You created an empty table, and now you try to read non-existing data from your empty table and write the data back to that table?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[juniorplsql]

no I created a table and I wanted to write a plsql to insert data into that table. Just trying to some stuff but i think I got it to work.

DECLARE
v_emp_id employees.emp_id%TYPE := EMPLOYEES_SEQ.NEXTVAL;
v_dept_id employees.dept_id%TYPE;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
v_inserted employees.inserted%TYPE;
v_last_update employees.last_update%TYPE;
v_hire_date employees.hire_date%TYPE;
v_job_id employees.job_id%TYPE;
v_sal employees.salary%TYPE;
v_pct employees.comm_pct%TYPE;
BEGIN
IF SQL%FOUND THEN
INSERT INTO EMPLOYEES VALUES(EMPLOYEES_SEQ.NEXTVAL, 10, 'JOHN', 'SMITH', SYSDATE, SYSDATE, '10-JAN-02', 110, 7000, .10);
END IF;
DBMS_OUTPUT.PUT_LINE('Number of rows inserted : '||SQL%ROWCOUNT);
SELECT emp_id, dept_id, first_name, last_name, inserted, last_update, hire_date, job_id, salary, comm_pct
INTO v_emp_id, v_dept_id, v_first_name, v_last_name, v_inserted, v_last_update, v_hire_date, v_job_id, v_sal, v_pct
FROM EMPLOYEES;
DBMS_OUTPUT.PUT_LINE('EMP_ID : '||v_emp_id);
DBMS_OUTPUT.PUT_LINE('DEPT_ID : '||v_dept_id);
DBMS_OUTPUT.PUT_LINE('FIRST_NAME : '||v_first_name);
DBMS_OUTPUT.PUT_LINE('LAST_NAME : '||v_last_name);
DBMS_OUTPUT.PUT_LINE('INSERTED : '||v_inserted);
DBMS_OUTPUT.PUT_LINE('LAST_UPDATE : '||v_last_update);
DBMS_OUTPUT.PUT_LINE('HIRE_DATE : '||v_hire_date);
DBMS_OUTPUT.PUT_LINE('JOB_ID : '||v_job_id);
DBMS_OUTPUT.PUT_LINE('SALARY : '||v_sal);
DBMS_OUTPUT.PUT_LINE('COMM_PCT : '||v_pct);
END;

it inserted one record of data into the table.

 

[ChrisHunt]

If you just want to insert a single row into your table, why are you using a loop, and why are you using PL/SQL? You just need a single SQL statement:

INSERT INTO EMPLOYEES
(emp_id,dept_id,first_name,last_name,inserted,last_update,hire_date,job_id,salary,comm_pct)
VALUES(EMPLOYEES_SEQ.NEXTVAL, 10, 'JOHN', 'SMITH', SYSDATE, SYSDATE, TO_DATE('10-JAN-2002','dd-mon-yyyy'), 110, 7000, .10);

Note that I list the column names that I'm going to insert into. The statement will work without this, but it's a good habit to get into as it can prevent errors in the future. Also, if hire_date is a DATE column, you should pass a date value into it instead of passing a string and hoping Oracle will interpret it how you'd like (especially if you're passing a two-digit year!)

If you have multiple rows to insert, you still almost certainly don't need to do so in a PL/SQL loop. Just use a SQL statement to select the new rows from whatever table they come from:

INSERT INTO EMPLOYEES
(emp_id,dept_id,first_name,last_name,inserted,last_update,hire_date,job_id,salary,comm_pct)
SELECT EMPLOYEES_SEQ.NEXTVAL, dept_id, first_name, last_name, SYSDATE, SYSDATE, hire_date, job_id, salary, comm_pct
FROM whatever_table_or_tables;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[juniorplsql]

Chris, thanks.... Since I am new to plsql i had one of those "hmmmm... i wonder can you do this in plsql moments"... if you know what I mean..

 

[garani]

Hi

Please note that for single row insert you can use INSERT for values clause
For Multiple row insert say 4 records you can use INSERT ALL
Advantage of INSERT ALL is you can insert records into Multiple Tables

Reference :

http://www.oratable.com/oracle-insert-all/

insert all
into colors(name, category) values('yellow', 1)
into colors(name, category) values('red', 1)
into colors(name, category) values('blue', 1)
into colors(name, category) values('yellow', 2)
into colors(name, category) values('blue', 2)
select * from dual;


Regards
Garani hv

 

[juniorplsql]

Thank you and thanks for the weblink