procedure

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;
/