select sysdate,(select 'x' from dual where rownum = 1) into a,b from dual有没有接受变量阿?
是不是语法的错误 先定义好变量l_sysdate,l_xselect sysdate,(select 'x' from dual where rownum = 1) into l_sysdate,l_x from dual
给你个例子你看看 ------------------------------------------------------------------------------ create or replace procedure proc_1 as v_sysdate date; v_x varchar2(5); begin select sysdate,(select 'x' from dual where rownum = 1) into v_sysdate,v_x from dual; dbms_output.put_line(v_sysdate||v_x); end; / 过程已创建。
arben555():兄弟不行啊。 报错:ORA-06550
create or replace procedure Insert_Test is begin insert into TableA ( A,B ) select sysdate,(select 'x' from dual ) from dual;
end Insert_Test;是这个存储过程总是报错,给位大虾,帮忙解决一下阿。
insert into TableA ( A,B ) select sysdate,x from dual;
insert into TableA ( A,B ) select sysdate,'x' from dual;
select sysdate,(select 'x' as ok from dual ) as ok2 from dual;
为什么要(select 'x' from dual ) ?直接 select sysdate,'x' from dual;不就可以了?
select sysdate,(select 'x' from dual where rownum = 1) from dual想在存储过程中执行,但总是报错。--------------------------- 过程中的 select 语句必须带 into 子句
tgm78(shop34161266.taobao.com)
需要先select出来字段给变量,在把变量insert到你需要的表中正解
create or replace procedure Insert_Test is begin insert into TableA ( A,B ) select sysdate,(select 'x' from dual ) from dual;
end Insert_Test;在存储过程中SELECT 必须和INTO一起使用 你如果想返回记录数大于1的结果集则需要用游标 对于返回单行记录的可以选择用参数形式返回CREATE OR REPLACE Procedure Insert_Test(col1 Out Date, col2 Out Char)isbegin
select sysdate,(select 'x' from dual ) Into col1,col2 from dual;
end ;
select 'x' from dual ?? 在存储过程里,你用 'x' ?不是 x 吗
可能是你字段类型不对,我用着蛮好的啊. create or replace procedure A is begin insert into A_B ( DEAL_DATE,NAME ) select sysdate,(select 'x' from dual ) from dual; COMMIT;
end ; DEAL_DATE DATE型 NAME VARCHAR2 型
select sysdate,'x' into ld_sysdate,ls_x from dual;
楼主提供的例子 create or replace procedure Insert_Test is begin insert into TableA ( A,B ) select sysdate,(select 'x' from dual ) from dual;
end Insert_Test;我一用是可以的阿。看来你不是这个存储过程的问题
select 'x' from dual 在你的存储过程里,这个查询是不是返回单一的纪录啊?还有,oracle报错了,请至少把错误码写出来。
应该如楼上所说: select 'x' from dual 返回的不是一行记录,而是多行 楼主写的存储过程应该没有别的问题
gdinsix408j() : 同意。编译成功。
确定是报错么? 帖出来看看啊。。
以下是帮助信息,你可以看看 ORA-06550 PLS-00123 program too largeCause: PL/SQL was designed primarily for robust transaction processing. One consequence of the special-purpose design is that the PL/SQL compiler imposes a limit on block size. The limit depends on the mix of statements in the PL/SQL block. Blocks that exceed the limit cause this error.Action: The best solution is to modularize the program by defining subprograms, which can be stored in an Oracle database. Another solution is to break the program into two sub-blocks. Have the first block INSERT any data the second block needs into a temporary database table. Then, have the second block SELECT the data from the table.
sql语句太长了? 写function?
可能是你字段类型不对,我用着蛮好的啊. create or replace procedure A is begin insert into A_B ( DEAL_DATE,NAME ) select sysdate,(select "x " from dual ) from dual; COMMIT;
from dual有没有接受变量阿?
先定义好变量l_sysdate,l_xselect sysdate,(select 'x' from dual where rownum = 1) into l_sysdate,l_x
from dual
------------------------------------------------------------------------------
create or replace procedure proc_1
as
v_sysdate date;
v_x varchar2(5);
begin
select sysdate,(select 'x' from dual where rownum = 1) into v_sysdate,v_x from dual;
dbms_output.put_line(v_sysdate||v_x);
end;
/
过程已创建。
报错:ORA-06550
Insert_Test
is
begin
insert
into TableA
(
A,B
)
select sysdate,(select 'x' from dual )
from dual;
end Insert_Test;是这个存储过程总是报错,给位大虾,帮忙解决一下阿。
into TableA
(
A,B
)
select sysdate,x
from dual;
into TableA
(
A,B
)
select sysdate,'x'
from dual;
from dual;
select sysdate,'x'
from dual;不就可以了?
from dual想在存储过程中执行,但总是报错。---------------------------
过程中的 select 语句必须带 into 子句
需要先select出来字段给变量,在把变量insert到你需要的表中正解
Insert_Test
is
begin
insert
into TableA
(
A,B
)
select sysdate,(select 'x' from dual )
from dual;
end Insert_Test;在存储过程中SELECT 必须和INTO一起使用
你如果想返回记录数大于1的结果集则需要用游标
对于返回单行记录的可以选择用参数形式返回CREATE OR REPLACE Procedure Insert_Test(col1 Out Date, col2 Out Char)isbegin
select sysdate,(select 'x' from dual ) Into col1,col2
from dual;
end ;
在存储过程里,你用 'x' ?不是 x 吗
create or replace procedure A is
begin
insert
into A_B
(
DEAL_DATE,NAME
)
select sysdate,(select 'x' from dual )
from dual;
COMMIT;
end ;
DEAL_DATE DATE型
NAME VARCHAR2 型
into ld_sysdate,ls_x
from dual;
create or replace procedure
Insert_Test
is
begin
insert
into TableA
(
A,B
)
select sysdate,(select 'x' from dual )
from dual;
end Insert_Test;我一用是可以的阿。看来你不是这个存储过程的问题
select 'x' from dual 返回的不是一行记录,而是多行
楼主写的存储过程应该没有别的问题
确定是报错么? 帖出来看看啊。。
ORA-06550
PLS-00123 program too largeCause:
PL/SQL was designed primarily for robust transaction processing. One consequence of the special-purpose design is that the PL/SQL compiler imposes a limit on block size. The limit depends on the mix of statements in the PL/SQL block. Blocks that exceed the limit cause this error.Action:
The best solution is to modularize the program by defining subprograms, which can be stored in an Oracle database. Another solution is to break the program into two sub-blocks. Have the first block INSERT any data the second block needs into a temporary database table. Then, have the second block SELECT the data from the table.
写function?
create or replace procedure A is
begin
insert
into A_B
(
DEAL_DATE,NAME
)
select sysdate,(select "x " from dual )
from dual;
COMMIT;
end ;
DEAL_DATE DATE型
NAME VARCHAR2 型