使用过程插入值

分享于2022年11月15日 oracle plsql 问答
【问题标题】:Insert values using the procedure使用过程插入值
【发布时间】:2022-11-14 16:17:17
【问题描述】:

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

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

我不知道这一个有什么问题。这是我调用程序的代码

调用程序:

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

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

  • 您正在尝试通过从目标表中选择它们来插入(新)行。当然,您不能使用这种方法插入不存在的东西。

【解决方案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>

【讨论】:

  • 我有一个错误,其中不允许从程序中使用该列
  • 当然可以(因为你拼错了 v_lnamw ),但修复它无助于制作 您的 代码工作。