ACCEPT total_cust_num integer PROMPT '请问您要插入多少客户信息?'
declare
i_icount integer := 0;
s_identityno varchar2(30);
s_customname varchar2(70);
begin
loop
s_identityno := to_char(MIDENTITYNO.NEXTVAL);
s_customname := '要拼接的字符' + to_char(MNAMECLASS.NEXTVAL);
i_icount := i_icount + 1;
exit when i_icount >= &total_cust_num;
end loop;
end;为什么不能这么写啊 ?MIDENTITYNO为我创建的一个队列
请大侠帮下忙:
报错:在此上下文中不允许表,视图或序列引用 'MIDENTITYNO.NEXTVAL'
我怎么才能用这个队列呢???多多指教!!!谢谢
declare
i_icount integer := 0;
s_identityno varchar2(30);
s_customname varchar2(70);
begin
loop
s_identityno := to_char(MIDENTITYNO.NEXTVAL);
s_customname := '要拼接的字符' + to_char(MNAMECLASS.NEXTVAL);
i_icount := i_icount + 1;
exit when i_icount >= &total_cust_num;
end loop;
end;为什么不能这么写啊 ?MIDENTITYNO为我创建的一个队列
请大侠帮下忙:
报错:在此上下文中不允许表,视图或序列引用 'MIDENTITYNO.NEXTVAL'
我怎么才能用这个队列呢???多多指教!!!谢谢
解决方案 »
- ORACLE.EXE (SHAD)
- oracle安装完后,连接没有监听程序,怎么处理
- INSERT INTO TableName EXECUTE(@v_SQL)语句问题
- 500分求metalink的帐号!
- [求助]Pro*C关于集合应用的问题
- 请教大家一个sql语句,谢谢!!!
- 为什么在执行建TRIGGER语句的时候会有Cannot create triggers on objects owned by SYS这样的提示?
- 请问,三条统计的sql语句,能否写成一条?
- 关于oracle 11g安装在fedora20的问题
- oracle中设置字段的default值为什么不起作用
- 问一个循环插入报错,怎么知道我哪里错了
- 求相邻的相同的数据的个数(在线等)
/*
You can use CURRVAL and NEXTVAL in:
1.The SELECT list of a SELECT statement that is not contained in a subquery,
materialized view, or view
2.The SELECT list of a subquery in an INSERT statement
3.The VALUES clause of an INSERT statement
4.The SET clause of an UPDATE statement
You cannot use CURRVAL and NEXTVAL:
1.A subquery in a DELETE, SELECT, or UPDATE statement
2.A query of a view or of a materialized view
3.A SELECT statement with the DISTINCT operator Pseudocolumns
4.A SELECT statement with a GROUP BY clause or ORDER BY clause
5.A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
6.The WHERE clause of a SELECT statement
7.DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
8.The condition of a CHECK constraint
*/
DECLARE
i_icount INTEGER := 0;
s_identityno VARCHAR2(30);
s_customname VARCHAR2(70);
BEGIN
LOOP
--s_identityno := to_char(MIDENTITYNO.NEXTVAL);
--s_customname := '要拼接的字符' + to_char(MNAMECLASS.NEXTVAL);
--11g以下只能在sql语句中引用序列,上面两句改成如下
SELECT to_char(MIDENTITYNO.NEXTVAL) INTO s_identityno FROM dual;
SELECT '要拼接的字符' + to_char(MNAMECLASS.NEXTVAL)
INTO s_customname
FROM dual;
i_icount := i_icount + 1;
EXIT WHEN i_icount >= &total_cust_num;
END LOOP;
END;
--补充一下字符串连接符号为:||,而不是:+
ACCEPT total_cust_num integer PROMPT '请问您要插入多少客户信息?'
DECLARE
i_icount INTEGER := 0;
s_identityno VARCHAR2(30);
s_customname VARCHAR2(70);
BEGIN
LOOP
SELECT to_char(MIDENTITYNO.NEXTVAL) INTO s_identityno FROM dual;
SELECT '要拼接的字符'||to_char(MNAMECLASS.NEXTVAL)--字符串连接符为:||
INTO s_customname
FROM dual;
i_icount := i_icount + 1;
EXIT WHEN i_icount >= &total_cust_num;
END LOOP;
END;