在BEGIN与END之间不能这样写:
我给你一个存储过程你看看语法:
CREATE OR REPLACE PROCEDURE "GETSERIES" (
vl_name IN VARCHAR2,
vl_id IN VARCHAR2,
vl_len IN INTEGER,
vl_out OUT VARCHAR2
)
IS
cnt INTEGER;
temp INTEGER;
vl_zz VARCHAR(30);
BEGIN
SELECT count(*) INTO temp FROM t_sysinfo WHERE
rtrim(INFONAME)=rtrim(vl_name);
IF (temp<>0) THEN
BEGIN
SELECT info INTO temp FROM t_sysinfo WHERE rtrim(INFONAME)=rtrim(vl_name);
temp:=temp+1;
UPDATE t_sysinfo SET info=temp WHERE rtrim(infoname)=rtrim(vl_name);
END;
ELSE
BEGIN
temp:=1;
INSERT INTO t_sysinfo(infoname,info) VALUES(vl_name,to_char(temp));
END;
END IF; SELECT
concat(substr('00000000000000000000',1,20-length(to_char(temp))),to_char(temp)) c into vl_zz FROM dual; if vl_id is not null then
SELECT
concat(RTRIM(vl_id),substr(vl_zz,21-vl_len+length(RTRIM(vl_id)),vl_len)) c into vl_out
FROM dual;
else
SELECT
substr(vl_zz,21-vl_len,vl_len) c into vl_out
FROM dual;
end if;
END;
我给你一个存储过程你看看语法:
CREATE OR REPLACE PROCEDURE "GETSERIES" (
vl_name IN VARCHAR2,
vl_id IN VARCHAR2,
vl_len IN INTEGER,
vl_out OUT VARCHAR2
)
IS
cnt INTEGER;
temp INTEGER;
vl_zz VARCHAR(30);
BEGIN
SELECT count(*) INTO temp FROM t_sysinfo WHERE
rtrim(INFONAME)=rtrim(vl_name);
IF (temp<>0) THEN
BEGIN
SELECT info INTO temp FROM t_sysinfo WHERE rtrim(INFONAME)=rtrim(vl_name);
temp:=temp+1;
UPDATE t_sysinfo SET info=temp WHERE rtrim(infoname)=rtrim(vl_name);
END;
ELSE
BEGIN
temp:=1;
INSERT INTO t_sysinfo(infoname,info) VALUES(vl_name,to_char(temp));
END;
END IF; SELECT
concat(substr('00000000000000000000',1,20-length(to_char(temp))),to_char(temp)) c into vl_zz FROM dual; if vl_id is not null then
SELECT
concat(RTRIM(vl_id),substr(vl_zz,21-vl_len+length(RTRIM(vl_id)),vl_len)) c into vl_out
FROM dual;
else
SELECT
substr(vl_zz,21-vl_len,vl_len) c into vl_out
FROM dual;
end if;
END;
解决方案 »
- 分页查询
- 请问一个简单问题
- 求纵表转横表过程 要求使用游标 跪谢
- 如何能把Oracle中的数据移到其它数据库如(Sql Server,MySQL)
- 请问这样的需求是用存储过程写还是用触发器写???初学者!!
- 添加oracleODBC数据源
- 简单问题,冷备后如何恢复数据库。
- 我已将数据库清空,但是我的 blob 字段还是插入不了数据,就连原来能够插入的,很小的数据都无法插入了,是不是 blob 字段的删除不干净呢
- 什么是API?急!
- 如何在oracle8.1.7的图形界面中实现id值自动增加
- 数据倒入问题!!
- oracle815 下如何用dbms_job 包来定时运行一个外部程序(例如 notepad) ?
8i中,语句必须是PL/SQL语句。
如:select aa into a from bb;
这个只能在9i中可以,用来返回纪录集。
as
type ref_cursor is ref cursor;
cursor_test ref_cursor;
my_name test.name%type;
begin
open cursor_library for
select name from test;
loop
fetch cursor_test into my_name;
exit when cursor_test%notfound;
end loop;
close cursor_test;
end test;
/
as
type ref_cursor is ref cursor;
cursor_test ref_cursor;
my_name test.name%type;
begin
open cursor_test for
select name from table;
loop
fetch cursor_test into my_name;
exit when cursor_test%notfound;
end loop;
close cursor_test;
end test;
/
不过你返回的记录太多了。听楼上的吧,等你的系统update到9i的时候再试试。