在学习 精通Oracle.10g.Pl.SQL编程_美河提供.pdf一书籍中:
有一个小示例:
<<outer>>
declare
v_deptno number(2);
v_dname varchar2(10);
begin
<<inner>>
begin
select deptno into v_deptno from emp
where lower(ename)=lower('&name');
end;--<<inner>>
select dname into v_dname from dept
where deptno=v_deptno;
dbms_output.put_line('department name:'||v_dname);
end; --<<outer>>
/在plsql控制台里面执行报如下错误:
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as loganSQL> <<outer>>
declare
v_deptno number(2)ORA-06550: line 5, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := ; not null default characterv_dname varchar2(10)ORA-00900: invalid SQL statementbegin
<<inner>>
begin
select deptno into v_deptno from emp
where lower(ename)=lower('scott');
end;--<<inner>>
select dname into v_dname from dept
where deptno=v_deptno;
dbms_output.put_line('department name:'||v_dname);
end; --<<outer>>ORA-06550: line 4, column 24:
PLS-00201: identifier 'V_DEPTNO' must be declared
ORA-06550: line 4, column 33:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 16:
PL/SQL: ORA-00904: "V_DEPTNO": invalid identifier
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 44:
PLS-00201: identifier 'V_DNAME' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignoredSQL>
该如何解决?
有一个小示例:
<<outer>>
declare
v_deptno number(2);
v_dname varchar2(10);
begin
<<inner>>
begin
select deptno into v_deptno from emp
where lower(ename)=lower('&name');
end;--<<inner>>
select dname into v_dname from dept
where deptno=v_deptno;
dbms_output.put_line('department name:'||v_dname);
end; --<<outer>>
/在plsql控制台里面执行报如下错误:
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as loganSQL> <<outer>>
declare
v_deptno number(2)ORA-06550: line 5, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := ; not null default characterv_dname varchar2(10)ORA-00900: invalid SQL statementbegin
<<inner>>
begin
select deptno into v_deptno from emp
where lower(ename)=lower('scott');
end;--<<inner>>
select dname into v_dname from dept
where deptno=v_deptno;
dbms_output.put_line('department name:'||v_dname);
end; --<<outer>>ORA-06550: line 4, column 24:
PLS-00201: identifier 'V_DEPTNO' must be declared
ORA-06550: line 4, column 33:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 16:
PL/SQL: ORA-00904: "V_DEPTNO": invalid identifier
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 44:
PLS-00201: identifier 'V_DNAME' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignoredSQL>
该如何解决?
等待hebo2005 来解决下吧。
v_deptno number(2);
v_dname varchar2(10);
begin
<<inner>>
begin
select deptno into v_deptno from emp
where lower(ename)=lower('&name');
end;--<<inner>>
select dname into v_dname from dept
where deptno=v_deptno;
dbms_output.put_line('department name:'||v_dname);
end;
/
你写的sql在oracle 10.2.0.x版本是可以执行的,在你的版本下可能不认"<<>>"
改成如下试试:--<<outer>>
declare
v_deptno number(2);
v_dname varchar2(10);
begin
--<<inner>>
begin
select deptno into v_deptno from emp
where lower(ename)=lower('&name');
end;--<<inner>>
select dname into v_dname from dept
where deptno=v_deptno;
dbms_output.put_line('department name:'||v_dname);
end; --<<outer>>
/
SQL> set serveroutput on
SQL> desc dept;
Name Type Nullable Default Comments
--------- ------------ -------- ------- --------
DEPT_NUM NUMBER
DEPT_NAME VARCHAR2(10) Y
DEPTDATE CHAR(1) Y SQL> select * from dept; DEPT_NUM DEPT_NAME DEPTDATE
---------- ---------- --------
10 d1
20 d2
30 d3
50 d4 SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMP_NUM NUMBER
EMP_NAME VARCHAR2(40) Y
DEPT_NUM NUMBER Y SQL> select * from emp; EMP_NUM EMP_NAME DEPT_NUM
---------- ---------------------------------------- ----------
1 chol 10
2 uday 20
3 vaibhav 20
4 ghosh 30SQL>
SQL> declare
2 v_deptno number(2);
3 v_dname varchar2(10);
4 begin
5 --<<inner>>
6 begin
7 select dept_num into v_deptno from emp
8 where lower(emp_name)=lower('&name');
9 end;--<<inner>>
10 select dept_name into v_dname from dept
11 where dept_num=v_deptno;
12 dbms_output.put_line('department name:'||v_dname);
13 end; --<<outer>>
14 /department name:d1PL/SQL procedure successfully completedSQL>