如题。
INSERT INTO CHAP4
VALUES (CHAP4_SEQ.NEXTVAL, v_name);
SAVEPOINT A;
CHAP4_SEQ如果是个变量,该如何操作??
INSERT INTO CHAP4
VALUES (CHAP4_SEQ.NEXTVAL, v_name);
SAVEPOINT A;
CHAP4_SEQ如果是个变量,该如何操作??
解决方案 »
- 字符串连接(简单)
- oracle9i企业管理器中没有数据库项
- 谁能帮我把一SQL的trigger转换成ORACLE的,小弟在此先谢谢了!!!!!!!!!
- 一个关于数据库备份的问题??????
- 如何查询第二条记录?
- 在oracle中能不删除表中数据改变字段的类型吗?急啊,各位大虾,帮帮忙!
- oracle8i不支持case语句,那怎样实现如下功能?(高分)
- 请教oracle的jdbc驱动程序的类路径如何设置?
- oracle几个易混淆的术语:oracle_sid、db_name、instance_name......
- 我的oracle快要崩溃了,救救我!!!
- ORA-01200错误如何恢复(高分在线等)
- 大侠帮忙看看这是什么锁
begin
open rst for 'select * from '||yourtable||';
end sp_test;
CREATE OR REPLACE PROCEDURE P(P_TABNAME VARCHAR2)
2 AS
3 L_SQL LONG;
4 L_CNT NUMBER;
5 BEGIN
6 L_SQL := 'SELECT COUNT(*) FROM ' | |P_TABNAME;
7 EXECUTE IMMEDIATE L_SQL INTO L_CNT;
8 DBMS_OUTPUT.put_line(L_CNT);
9 END;
10 / Procedure created. Elapsed: 00:00:00.85
T@orcl> EXEC P('T');
1230966 PL/SQL procedure successfully completed. Elapsed: 00:00:12.42
T@orcl> EXEC P('DUAL');
1 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
这句比较狠CREATE OR REPLACE PROCEDURE P(P_SEQNAME in VARCHAR2)
AS
L_SQL varchar2(1000);
L_CNT NUMBER;
L_SEQ NUMBER;
BEGIN
L_SQL := 'select count(1) from user_sequences
where sequence_name='||P_SEQNAME ;
EXECUTE IMMEDIATE L_SQL INTO L_CNT;
if L_CNT= 0 then
EXECUTE IMMEDIATE 'create sequence '||P_SEQNAME;
L_SEQ :=1;
else
L_SQL := 'select '||P_SEQNAME||'.NEXTVAL from dual;
EXECUTE IMMEDIATE L_SQL into L_SEQ;
end if;
DBMS_OUTPUT.put_line(L_SEQ);
END;
L_SQL VARCHAR2(1000);
L_CNT NUMBER;
L_SEQ NUMBER;
BEGIN
L_SQL := 'select count(*) from user_sequences where sequence_name= upper(''' ||
P_SEQNAME || ''')';
EXECUTE IMMEDIATE L_SQL
INTO L_CNT;
IF L_CNT = 0 THEN
EXECUTE IMMEDIATE 'create sequence ' || P_SEQNAME;
ELSE
L_SQL := 'INSERT INTO CHAP4 VALUES (' || P_SEQNAME || '.NEXTVAL,''' ||
P_NAME || ''')';
EXECUTE IMMEDIATE L_SQL;
END IF;
END;
/BEGIN
p('seq_123','xxx');
END;
/
into L_CNT
from user_sequences
where sequence_name = s_seqname; if L_CNT = 0 then
EXECUTE IMMEDIATE 'create sequence ' || s_seqname ||
' start with 2';
Result := 1;因为这个序列名称是中文,会报错。
后来发现oracledbalgtu 写的全对!!
我只是把这个过程更改为函数了。在开发工具里面以及plsql中就可以这样执行了。INSERT INTO CHAP4 VALUES (P_SEQNAME .NEXTVAL,'aaaa')';