使用过程插入值

Question

为了插入新员工，我创建了Procedure，但问题是，它没有插入。我需要将属性值作为参数传递。这是我的代码:

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;
/

我不知道这个哪里错了。这是我调用过程

的代码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;
/

我不知道为什么它不插入。我有一个唯一约束的错误

Answer 1

当然，在插入数据时不需要游标或循环(至少在这样的过程中不需要)。另外，你必须使用你声明过的变量，而不是那些你没有声明过的。

样本表:

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.

程序:

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.

测试:

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.

结果:

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>

Answer 2

我有一个唯一约束的错误

那么表中已经有一个雇员，在受唯一键约束或主键约束的列中具有相同的值。

你需要改变你插入到表中的值，这样你就不会插入重复的值。

如果您有表:

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)
);

然后主键将由IDENTITY列生成(从Oracle 12中可用)，并且雇员对电子邮件和电话号码也有唯一的约束(您没有为表提供DDL语句，因此这只是一个示例)。

你可以插入一个新用户:

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;
/

employee_id由IDENTITY列自动生成，不需要插入;但是，例如给定表上的约束，如果您插入重复的电子邮件或电话号码，那么您仍然会得到UNIQUE constraint violated的异常。

您可以使用:

插入值

BEGIN
p_new(
'text',
'string',
'string',
'0123456789',
DATE '2000-09-18',
'string',
4000,
0.5,
105,
30
);
END;
/