具有一个输入参数和多个输出参数的Oracle Run Procedure [英] Oracle Run Procedure with one in parameter and multiple out parameter

问题描述

我刚开始与Oracle合作，并且在开始向应用程序添加内容之前使用SQL Developer进行测试，但是由于Oracle的行为不同于我使用的其他所有数据库引擎，因此我遇到了问题. /p>

我创建了一个虚拟表:

CREATE TABLE "ROOT"."EMPLOYEES" 
(   "ID" NUMBER NOT NULL ENABLE, 
"FIRSTNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
"LASTNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
"EMAIL" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
 CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("ID")
)

然后我创建了一个过程:

 create or replace PROCEDURE get_employee
(
  emp_id IN NUMBER,
  m_FirstName OUT Varchar2,
  m_LastName OUT Varchar2,
  m_Email OUT Varchar2
)
AS
BEGIN
SELECT 
   FirstName
   ,LastName
   ,Email
INTO
   m_FirstName,
   m_LastName,
   m_Email
FROM EMPLOYEES
WHERE
  ID = emp_id;
END get_employee;

问题是当我尝试运行该过程时出现编译错误:

Declare x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
exec GET_EMPLOYEE(1, :x, :y, :z);
SYS.DBMS_OUTPUT.PUT_LINE(x);
End; 

我收到此错误:

ORA-06550:第8行，第4列: PLS-00103:预期以下情况之一时遇到符号文件结尾":

(开始情况为循环mod声明goto的结束异常退出 null pragma提升返回选择更新，而与 标识符双引号 06550.00000-％s行，％s列:\ n％s" *原因:通常是PL/SQL编译错误. *行动: PL/SQL过程成功完成.

我真的不确定如何更改对过程的调用以使其与SQL Developer一起使用.任何帮助将不胜感激.

解决方案

这是正确的语法

Declare 
x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
GET_EMPLOYEE(1, x, y, z);
DBMS_OUTPUT.PUT_LINE(x);
End;

I just started working with Oracle and I'm using SQL Developer to test before I start adding stuff to my application, but I'm running into issues because Oracle behaves differently than every other database engine I've worked with.

I created a dummy table:

CREATE TABLE "ROOT"."EMPLOYEES" 
(   "ID" NUMBER NOT NULL ENABLE, 
"FIRSTNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
"LASTNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
"EMAIL" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
 CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("ID")
)

And Then I created a Procedure:

 create or replace PROCEDURE get_employee
(
  emp_id IN NUMBER,
  m_FirstName OUT Varchar2,
  m_LastName OUT Varchar2,
  m_Email OUT Varchar2
)
AS
BEGIN
SELECT 
   FirstName
   ,LastName
   ,Email
INTO
   m_FirstName,
   m_LastName,
   m_Email
FROM EMPLOYEES
WHERE
  ID = emp_id;
END get_employee;

The problem is I get compilation errors when I try to run the procedure:

Declare x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
exec GET_EMPLOYEE(1, :x, :y, :z);
SYS.DBMS_OUTPUT.PUT_LINE(x);
End; 

I get this error:

ORA-06550: line 8, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with an identifier a double-quoted 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: PL/SQL procedure successfully completed.

I'm really not sure how to change my call to the procedure to make it work with SQL Developer. Any help would be greatly appreciated.

解决方案

This is the correct syntax

Declare 
x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
GET_EMPLOYEE(1, x, y, z);
DBMS_OUTPUT.PUT_LINE(x);
End;

这篇关于具有一个输入参数和多个输出参数的Oracle Run Procedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！