代码之家  ›  专栏  ›  技术社区  ›  Freak0345

如何在oracle中启动过程?

  •  -2
  • Freak0345  · 技术社区  · 7 年前

    我有一个SELECT语句:

         SELECT emp.emp_name, pos_emp.POSITION_NAME
         from EMPLOYEE emp
         join POSITION_EMPLOYEE pos_emp
         on emp.POSITION_EMPLOYEE_POSITION_ID=pos_emp.POSITION_ID
         where emp.EMP_NAME='&employee_name';
    

    当我从键盘输入员工姓名时,它会返回他的姓名(来自employee表)和职位(来自position_employee表)。但我想用存储过程来实现这一点:

        create or replace PROCEDURE emp_pos(   EMPLOYEE_NAME IN EMPLOYEE.EMP_NAME%TYPE,
                                           POSITION_NAME OUT POSITION_EMPLOYEE.POSITION_NAME%TYPE )
    AS
    BEGIN
        SELECT pos_emp.POSITION_NAME
        INTO
            POSITION_NAME
        FROM EMPLOYEE emp
            JOIN
                POSITION_EMPLOYEE pos_emp
                ON
                    emp.POSITION_EMPLOYEE_POSITION_ID 
                    = pos_emp.POSITION_ID
        WHERE emp.EMP_NAME 
              = EMPLOYEE_NAME
        ;
    
    END;
    

    我试图用begin开始这个过程:

    begin emp_pos('&employee_name');
    end;
    

    编译器给了我错误:调用“EMP\u POS”时参数的数量或类型错误。我哪里错了?

    1 回复  |  直到 7 年前
        1
  •  0
  •   Barbaros Özhan    7 年前
    SQL>set serveroutput on;
    SQL>    create or replace PROCEDURE emp_pos(   EMPLOYEE_NAME IN EMPLOYEE.EMP_NAME%TYPE,
                                           POSITION_NAME OUT POSITION_EMPLOYEE.POSITION_NAME%TYPE )
    AS
    BEGIN
        SELECT pos_emp.POSITION_NAME
        INTO
            POSITION_NAME
        FROM EMPLOYEE emp
            JOIN
                POSITION_EMPLOYEE pos_emp
                ON
                    emp.POSITION_EMPLOYEE_POSITION_ID 
                    = pos_emp.POSITION_ID
        WHERE emp.EMP_NAME 
              = EMPLOYEE_NAME
        ;
      dbms_output.put_line(EMPLOYEE_NAME||'''s position is : '||POSITION_NAME);
    END;
    

    定义此过程后,您可以这样使用:

    SQL>var v_emp_pos varchar2;
    SQL>exec emp_pos('&employee_name',:v_emp_pos);
    SQL>print v_emp_pos; -- just to print out this "out" parameter.