CREATE OR REPLACE PROCEDURE SITENAME AS /***************************************************************************************** ** File Name: SITENAME.SQL ** Procedure Name: SITENAME ** Author: Kent ** Function: This procedure is used to insert sitename to h_bts table from h_sitename table. ** ** Creation Date: 2002-07 ** Revision History: ** ------------------ Version 1.0 ------------------ ** ** Modification: *****************************************************************************************/ cursor v_Cursor is Select * from H_SITENAME ; v_Count NUMBER; v_Btsid H_BTS.BTSID%TYPE; v_Bscid H_BTS.BSCID%TYPE; v_Sqlstring varchar2(50); v_DBCur NUMBER; v_Dummy Integer;BEGIN/* IF NOT v_Cursor%ISOPEN THEN OPEN v_Cursor; END IF; */ -- Loop cursor for v_CursorValue in v_Cursor loop EXIT WHEN v_Cursor%NOTFOUND; -- construct btsid v_Btsid := 'OMC:' || v_CursorValue.omcid || '-' || 'MM:' || lpad(v_CursorValue.mmid,2,'0') || '-' || 'BTS:' || lpad(v_CursorValue.btsid,4,'0'); -- construct bscid v_Bscid := 'OMC:' || v_CursorValue.omcid || '-' || 'MM:' || lpad(v_CursorValue.mmid,2,'0') ; v_Count :=0 ; -- if record exist in h_bts select count(*) into v_Count from h_bts where btsid = v_Btsid; if v_Count > 0 then -- record exist, then update update h_bts set sitename = v_CursorValue.btsname where btsid = v_Btsid; else -- record not exist , then insert insert into h_bts values(v_Btsid,v_Bscid,v_CursorValue.btsname); end if; -- commit process commit; end loop; -- Truncate h_sitename table v_DBCur := DBMS_SQL.OPEN_CURSOR; v_Sqlstring := 'TRUNCATE TABLE H_SITENAME'; -- parse string DBMS_SQL.PARSE(v_DBCur,v_Sqlstring,DBMS_SQL.NATIVE); -- execute v_Dummy := DBMS_SQL.EXECUTE(v_DBCur); COMMIT; DBMS_SQL.CLOSE_CURSOR(v_DBCur); -- close cursor if v_Cursor%isopen then close v_Cursor; end if;EXCEPTION WHEN OTHERS THEN if v_Cursor%isopen then close v_Cursor; end if; raise;END; /吧上面的代码copy到sqlplus中再按一下回车键就可以了。 你还是先看看说吧。
sorry!没仔细看,我的procedure内有表,你创建不了。
具体点说,在存储过程中怎么返回序列值,序列值的返回类型是什么? (get_sequence out varchar2)is begin get_sequence:=ks_sequence.nextval; end; 编译不通过。
这样试试: (get_sequence out varchar2)is begin select ks_sequence.nextval into get_sequence from dual; end;
随便一个实际存在的表就行,不一定非要dual表。Dual 是 Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select中。使用方法: 查看当前连接用户 SQL> select user from dual;USER ------------------------------ SYSTEM 查看当前日期、时间 SQL> select sysdate from dual;SYSDATE ---------- 18-3月 -01
在sqlplus中,怎样调用上面定义的存储过程?是这样子的么 declare seq varchar2; begin getsequence(seq); dbms_output.put_line(seq); end;
应该这样: set serveroutput on declare seq varchar2(50); begin getsequence(seq); dbms_output.put_line(seq); end; /
AS
/*****************************************************************************************
** File Name: SITENAME.SQL
** Procedure Name: SITENAME
** Author: Kent
** Function: This procedure is used to insert sitename to h_bts table
from h_sitename table.
**
** Creation Date: 2002-07
** Revision History:
** ------------------ Version 1.0 ------------------
**
** Modification:
*****************************************************************************************/
cursor v_Cursor is Select * from H_SITENAME ;
v_Count NUMBER;
v_Btsid H_BTS.BTSID%TYPE;
v_Bscid H_BTS.BSCID%TYPE;
v_Sqlstring varchar2(50);
v_DBCur NUMBER; v_Dummy Integer;BEGIN/*
IF NOT v_Cursor%ISOPEN THEN
OPEN v_Cursor;
END IF;
*/
-- Loop cursor
for v_CursorValue in v_Cursor loop EXIT WHEN v_Cursor%NOTFOUND; -- construct btsid
v_Btsid := 'OMC:' || v_CursorValue.omcid || '-' ||
'MM:' || lpad(v_CursorValue.mmid,2,'0') || '-' ||
'BTS:' || lpad(v_CursorValue.btsid,4,'0'); -- construct bscid
v_Bscid := 'OMC:' || v_CursorValue.omcid || '-' ||
'MM:' || lpad(v_CursorValue.mmid,2,'0') ; v_Count :=0 ;
-- if record exist in h_bts
select count(*) into v_Count from h_bts where btsid = v_Btsid; if v_Count > 0 then
-- record exist, then update
update h_bts set sitename = v_CursorValue.btsname where btsid = v_Btsid;
else
-- record not exist , then insert
insert into h_bts values(v_Btsid,v_Bscid,v_CursorValue.btsname);
end if; -- commit process
commit; end loop; -- Truncate h_sitename table
v_DBCur := DBMS_SQL.OPEN_CURSOR; v_Sqlstring := 'TRUNCATE TABLE H_SITENAME';
-- parse string
DBMS_SQL.PARSE(v_DBCur,v_Sqlstring,DBMS_SQL.NATIVE);
-- execute
v_Dummy := DBMS_SQL.EXECUTE(v_DBCur); COMMIT;
DBMS_SQL.CLOSE_CURSOR(v_DBCur); -- close cursor
if v_Cursor%isopen then
close v_Cursor;
end if;EXCEPTION
WHEN OTHERS THEN
if v_Cursor%isopen then
close v_Cursor;
end if;
raise;END;
/吧上面的代码copy到sqlplus中再按一下回车键就可以了。
你还是先看看说吧。
(get_sequence out varchar2)is
begin
get_sequence:=ks_sequence.nextval;
end;
编译不通过。
(get_sequence out varchar2)is
begin
select ks_sequence.nextval into get_sequence from dual;
end;
查看当前连接用户
SQL> select user from dual;USER
------------------------------
SYSTEM
查看当前日期、时间
SQL> select sysdate from dual;SYSDATE
----------
18-3月 -01
declare
seq varchar2;
begin
getsequence(seq);
dbms_output.put_line(seq);
end;
set serveroutput on
declare
seq varchar2(50);
begin
getsequence(seq);
dbms_output.put_line(seq);
end;
/
有事E_Mail我也行。
[email protected]