create or replace procedure seq_reset
(
o_ret out int,
o_msg out varchar2,
i_seqname varchar2
)
AS
n number(10);
tsql varchar2(100);
v_count int;
BEGIN
SELECT count(1) INTO v_count FROM all_sequences WHERE sequence_name = UPPER(i_seqname);
IF v_count = 0 THEN
o_ret := 0;
o_msg := '该序列不存在!';
RETURN;
END IF;
EXECUTE IMMEDIATE 'select ' || i_seqname || '.nextval from dual' INTO n;
n := -(n-1);
tsql := 'alter sequence ' || i_seqname || ' increment by '|| n;
EXECUTE IMMEDIATE tsql;
EXECUTE IMMEDIATE 'select ' || i_seqname || '.nextval from dual' INTO n;
tsql := 'alter sequence ' || i_seqname ||' increment by 1';
EXECUTE IMMEDIATE tsql;
o_ret := 0;
o_msg := '已重置序列值';
END seq_reset;
/
CREATE TABLE ZWF_T(code VARCHAR2(20),name VARCHAR2(20));
INSERT INTO zwf_t VALUES ('005129', 'ZWF01');
INSERT INTO zwf_t VALUES ('005128', 'ZWF02');
CREATE OR REPLACE PROCEDURE procedure_02
AS
CURSOR cur IS SELECT code FROM zwf_t;
BEGIN
FOR objResult IN cur LOOP
BEGIN
dbms_output.put_line(objResult.code);
END;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE proc_helloworld
IS
BEGIN
DBMS_OUTPUT.put_line ('Hello World!');
END;
/