SET ECHO ON
SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE MAKE_COPY_OF_TRIDY AUTHID CURRENT_USER AS
	
	TYPE TRIDY_TABLE_TYPE IS TABLE OF Tridy%ROWTYPE INDEX BY PLS_INTEGER;
	TRIDY_BACKUP TRIDY_TABLE_TYPE;

	CURSOR TRIDY_CURSOR IS
	SELECT *
	FROM tridy
	WHERE rocnik = 1
	ORDER BY nazev;

   	v_rowcnt PLS_INTEGER := 0;

	BEGIN
	   	OPEN TRIDY_CURSOR;
		   	FETCH TRIDY_CURSOR
		   	 BULK COLLECT INTO TRIDY_BACKUP;
		CLOSE TRIDY_CURSOR; 	
	   	
	   	FOR i IN TRIDY_BACKUP.FIRST..TRIDY_BACKUP.LAST
	    LOOP
	     DBMS_OUTPUT.PUT_LINE(TRIDY_BACKUP(i).id
	     					  ||chr(9)||'|'||chr(9)||TRIDY_BACKUP(i).mistnosti_id
	     					  ||chr(9)||'|'||chr(9)||TRIDY_BACKUP(i).rocnik
	     					  ||chr(9)||'|'||chr(9)||TRIDY_BACKUP(i).nazev);
	    END LOOP;
	    
	    DBMS_OUTPUT.NEW_LINE;
	    DBMS_OUTPUT.PUT_LINE('[Info] Table tridy_backup has '||TRIDY_BACKUP.COUNT||' rows.');
	    
	    EXECUTE IMMEDIATE 'CREATE TABLE tridy_kopie AS (SELECT * FROM tridy WHERE 1 = 2)';
      	COMMIT;
      	
      	DECLARE
         insert_command VARCHAR2(200) := 'INSERT INTO tridy_kopie(id,mistnosti_id,rocnik,nazev) VALUES(:id, :m_id, :rocnik, :nazev)';
      	
      	BEGIN
         FOR i IN TRIDY_BACKUP.FIRST..TRIDY_BACKUP.LAST 
          LOOP 
           EXECUTE IMMEDIATE insert_command USING TRIDY_BACKUP(i).id, TRIDY_BACKUP(i).mistnosti_id, TRIDY_BACKUP(i).rocnik, TRIDY_BACKUP(i).nazev; 
           COMMIT;
          END LOOP;
       	END;
      
      	DECLARE
       	 select_command VARCHAR(80) := 'SELECT COUNT(*) FROM tridy_kopie';
      	BEGIN 
      	 EXECUTE IMMEDIATE select_command INTO v_rowcnt;
      	END; 
	    DBMS_OUTPUT.PUT_LINE('[Info] Table tridy_kopie has '||v_rowcnt||' rows.');
      
      EXECUTE IMMEDIATE 'DROP TABLE tridy_kopie';
      COMMIT;
     
END MAKE_COPY_OF_TRIDY;
/
SHOW ERRORS
--
-- Testování -------------------------
--
SET ECHO ON
SET SERVEROUTPUT ON

BEGIN
  MAKE_COPY_OF_TRIDY;
END;
/
SHOW ERRORS