将数字关联到绑定变量pl/sql [英] Assinging a number to binding variable pl/sql
问题描述
我正在尝试将数字值分配给绑定变量以进行pl/sql分配,这给了我错误:
I am trying to assign a number value to a binding variable for a pl/sql assignment and it is giving me the error :
Error report:
ORA-01403: no data found
ORA-06512: at line 15
01403. 00000 - "no data found"
Cause:
Action:
b_emp_id
------
b_emp_id
和代码
VARIABLE b_emp_id NUMBER
DECLARE
v_emp_id employees.employee_id%TYPE;
v_FIRST_NAME employees.first_name%TYPE;
v_LAST_NAME employees.last_name%TYPE;
v_JOB_ID employees.job_id%TYPE;
v_HIRE_DATE employees.hire_date%TYPE;
v_message VARCHAR2(30);
v_difference NUMBER(3);
BEGIN
:b_emp_id:=110;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, job_id, HIRE_DATE
INTO v_emp_id, v_FIRST_NAME, v_LAST_NAME, v_JOB_ID, v_HIRE_DATE
FROM employees
WHERE EMPLOYEE_ID = :b_emp_id;
提前谢谢大家!
推荐答案
This seems to be a problem with SQL Developer, which I noticed on a recent answer. The code you have will work in SQL*Plus. This shows the problem too:
variable b_emp_id number
set serveroutput on
begin
:b_emp_id := 110;
if :b_emp_id is null then
dbms_output.put_line('b_emp_id is null');
else
dbms_output.put_line('b_emp_id is not null: ' || :b_emp_id);
end if;
end;
/
print b_emp_id;
anonymous block completed
b_emp_id is null
B_EMP_ID
---
110
在SQL * Plus中提供:
In SQL*Plus that gives:
b_emp_id is not null: 110
PL/SQL procedure successfully completed.
B_EMP_ID
---
110
...正如您所期望的. select 110 into :b_emp_id from dual
技巧在这里也不起作用.无论哪种方式,分配给bind变量的值都无法在块内部使用,但是在外部可见,这很奇怪.我认为这是一个SQL Developer错误,尽管我认为它可能只是不确定的行为.
... as you'd expect. The select 110 into :b_emp_id from dual
trick doesn't work here either. Either way the value assigned to the bind variable isn't usable inside the block, but is visible outside, which is weird. I assume it's an SQL Developer bug, though I guess it might just be undefined behaviour.
似乎唯一的选择是使用单独的exec
块,因为在您击中实际块时,设置在其中的绑定值将在范围内(如Max所建议);或使用替代变量代替;还是使用局部变量(在declare
块中)而不是在外部声明的variable
-取决于您为什么要这样做,并首先使用显式绑定变量.
It seems your only options are to use a separate exec
block, since the bind value set within that is then in scope when you hit your real block (as Max suggested); or to use substitution variables instead; or to use a local variable (in the declare
block) rather than an externally-declared variable
- depends why you're doing it quite like this and using an explicit bind variable in the first place.
这篇关于将数字关联到绑定变量pl/sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!