I've created Procedure in order to insert new employees, but the problem is, it is not inserting. I need to pass the attributes values as parameters. This is my code:
CREATE OR REPLACE PROCEDURE p_new(p_empid IN employees.employee_id%type,
p_fname IN employees.first_name%type,
p_lname IN employees.last_name%type,
p_email IN employees.email%type,
p_pnum IN employees.phone_number%type,
p_hdate IN employees.hire_date%type,
p_jid IN employees.job_id%type,
p_salary IN employees.salary%type,
p_comm IN employees.commission_pct%type,
p_mid IN employees.manager_id%type,
p_deptid IN employees.department_id%type) AS
v_empid employees.employee_id%type;
v_fname employees.first_name%type;
v_lname employees.last_name%type;
v_email employees.email%type;
v_pnum employees.phone_number%type;
v_hdate employees.hire_date%type;
v_jid employees.job_id%type;
v_salary employees.salary%type;
v_comm employees.commission_pct%type;
v_mid employees.manager_id%type;
v_deptid employees.department_id%type;
CURSOR c_emp IS
select employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id
from employees
WHERE employee_id=p_empid;
BEGIN
OPEN c_emp;
FETCH c_emp INTO
v_empid, v_fname, v_lname, v_email, v_pnum, v_hdate,
v_jid, v_salary, v_comm, v_mid, v_deptid;
INSERT INTO EMPLOYEES(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (v_empid, v_fname, v_lnamw, v_email, v_pnum, v_hdate,
v_jid, v_salary, v_comm, v_mid, v_deptid);
CLOSE c_emp;
END;
/
I don't know which is wrong in this one. And this is my code for calling procedure
For calling Procedure:
DECLARE
v_empid employees.employee_id%type:=600;
v_fname employees.first_name%type:='text';
v_lname employees.last_name%type:='string';
v_email employees.email%type:='string';
v_pnum employees.phone_number%type:=123456789;
v_hdate employees.hire_date%type:='18-SEP-2000';
v_jid employees.job_id%type:='string';
v_salary employees.salary%type:=4000;
v_comm employees.commission_pct%type:=0.5;
v_mid employees.manager_id%type:=105;
v_deptid employees.department_id%type:=30;
BEGIN
p_new(v_empid, v_fname, v_lname,
v_email,v_pnum,v_hdate,v_jid,v_salary,v_comm,
v_mid,v_deptid);
END;
/
I don't know why it is not inserting. I am having an error of unique constraint
CodePudding user response：
Of course you don't need a cursor nor a loop while inserting data (at least, not with such a procedure). Also, you have to use variables you declared, not the ones you didn't.
Sample table:
SQL> CREATE TABLE employees
2 (
3 employee_id NUMBER,
4 first_name VARCHAR2 (10),
5 last_name VARCHAR2 (10),
6 email VARCHAR2 (10),
7 phone_number VARCHAR2 (10),
8 hire_date DATE,
9 job_id VARCHAR2 (10),
10 salary NUMBER,
11 commission_pct NUMBER,
12 manager_id NUMBER,
13 department_id NUMBER
14 );
Table created.
Procedure:
SQL> CREATE OR REPLACE PROCEDURE p_new (
2 p_empid IN employees.employee_id%TYPE,
3 p_fname IN employees.first_name%TYPE,
4 p_lname IN employees.last_name%TYPE,
5 p_email IN employees.email%TYPE,
6 p_pnum IN employees.phone_number%TYPE,
7 p_hdate IN employees.hire_date%TYPE,
8 p_jid IN employees.job_id%TYPE,
9 p_salary IN employees.salary%TYPE,
10 p_comm IN employees.commission_pct%TYPE,
11 p_mid IN employees.manager_id%TYPE,
12 p_deptid IN employees.department_id%TYPE)
13 AS
14 BEGIN
15 INSERT INTO EMPLOYEES (employee_id,
16 first_name,
17 last_name,
18 email,
19 phone_number,
20 hire_date,
21 job_id,
22 salary,
23 commission_pct,
24 manager_id,
25 department_id)
26 VALUES (p_empid,
27 p_fname,
28 p_lname,
29 p_email,
30 p_pnum,
31 p_hdate,
32 p_jid,
33 p_salary,
34 p_comm,
35 p_mid,
36 p_deptid);
37 END;
38 /
Procedure created.
Testing:
SQL> DECLARE
2 v_empid employees.employee_id%TYPE := 600;
3 v_fname employees.first_name%TYPE := 'text';
4 v_lname employees.last_name%TYPE := 'string';
5 v_email employees.email%TYPE := 'string';
6 v_pnum employees.phone_number%TYPE := 123456789;
7 v_hdate employees.hire_date%TYPE := DATE '2000-09-18'; -- DATE, not STRING! '18-SEP-2000';
8 v_jid employees.job_id%TYPE := 'string';
9 v_salary employees.salary%TYPE := 4000;
10 v_comm employees.commission_pct%TYPE := 0.5;
11 v_mid employees.manager_id%TYPE := 105;
12 v_deptid employees.department_id%TYPE := 30;
13 BEGIN
14 p_new (v_empid,
15 v_fname,
16 v_lname,
17 v_email,
18 v_pnum,
19 v_hdate,
20 v_jid,
21 v_salary,
22 v_comm,
23 v_mid,
24 v_deptid);
25 END;
26 /
PL/SQL procedure successfully completed.
Result:
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMB HIRE_DATE JOB_ID
----------- ---------- ---------- ---------- ---------- ---------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
600 text string string 123456789 18.09.2000 string
4000 ,5 105 30
SQL>
CodePudding user response：
I am having an error of unique constraint
Then there is already an employee in the table with an identical value in a column constrained by a unique or primary key constraint.
You need to change the values you are inserting into the table so that you do not insert duplicates.
If you have the table:
CREATE TABLE employees (
employee_id NUMBER(8,0)
GENERATED ALWAYS AS IDENTITY
CONSTRAINT employees__employee_id__pk PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(25),
email VARCHAR2(250)
CONSTRAINT employees__email__u UNIQUE,
phone_number VARCHAR2(12)
CONSTRAINT employees__phone_number__u UNIQUE,
hire_date DATE,
job_id NUMBER(8,0)
-- CONSTRAINT employees__job_id__fk REFERENCES jobs(id)
,
salary NUMBER(10,2),
commission_pct NUMBER(5,2),
manager_id NUMBER
CONSTRAINT employees__manager_id__fk REFERENCES employees (employee_id),
department_id NUMBER(8,0)
-- CONSTRAINT employees__department_id__fk REFERENCES departments (id)
);
Then the primary key will be generated by the IDENTITY column (which is available from Oracle 12) and the employee also has unique constraints on e-mail and phone number (you have not provided the DDL statement for your table so this is only an example).
Then you can insert a new user using:
CREATE PROCEDURE p_new(
p_empid IN employees.employee_id%type,
p_fname IN employees.first_name%type,
p_lname IN employees.last_name%type,
p_email IN employees.email%type,
p_pnum IN employees.phone_number%type,
p_hdate IN employees.hire_date%type,
p_jid IN employees.job_id%type,
p_salary IN employees.salary%type,
p_comm IN employees.commission_pct%type,
p_mid IN employees.manager_id%type,
p_deptid IN employees.department_id%type
)
AS
BEGIN
INSERT INTO EMPLOYEES(
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
) VALUES (
p_fname,
p_lname,
p_email,
p_pnum,
p_hdate,
p_jid,
p_salary,
p_comm,
p_mid,
p_deptid
);
END;
/
The employee_id is automatically generated by the IDENTITY column and you do not need to insert it; however, for example given the constraints on the table, if you insert duplicate e-mails or phone numbers then you would still get an exception for UNIQUE constraint violated.
You can insert the value using:
BEGIN
p_new(
'text',
'string',
'string',
'0123456789',
DATE '2000-09-18',
'string',
4000,
0.5,
105,
30
);
END;
/