在Oracle SQL Developer 11g中，我试图生成一个随机日期，将其分配给变量，然后插入表中。将日期生成+分配给可变代码（效果很好）：
DECLARE Launch_date DATE; BEGIN Launch_date:=TO_DATE(TRUNC( DBMS_RANDOM.value( TO_CHAR(date '2000-01-01','J') ,TO_CHAR(date '2015-01-01','J') )),'J'); END;
尝试将其插入到表中时，出现以下错误消息：
Error starting at line : 214 in command - EXECUTE INSERT_DATA(15) Error report - ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "KALVAITYTE_INGRIDA.INSERT_DATA", line 43 ORA-06512: at line 1 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range
这是完整的代码：
DROP TABLE ACCEPT_RESULT; DROP TABLE DOCUMENTS; DROP TABLE STUDENT; DROP TABLE NEW_TABLE; DROP SEQUENCE S_ACCEPT; DROP SEQUENCE S_DOC; DROP SEQUENCE S_STUD; CREATE TABLE STUDENT (ID_STUDENT NUMBER PRIMARY KEY, F_NAME VARCHAR2(20), L_NAME VARCHAR2(20), BORN_DATE DATE ); CREATE TABLE DOCUMENTS ( DOC_ID NUMBER PRIMARY KEY, ENTERED DATE, FINISHED DATE, ID_STUDENT NUMBER REFERENCES STUDENT(ID_STUDENT) ); CREATE TABLE ACCEPT_RESULT (DETAIL_ID NUMBER PRIMARY KEY, UNIVERSITY_NAME VARCHAR2(50), FACULTY_NAME VARCHAR2(30), DOC_ID NUMBER REFERENCES DOCUMENTS(DOC_ID) ); CREATE TABLE NEW_TABLE ( F_NAME VARCHAR2(20), L_NAME VARCHAR2(20), UNIVERSITY VARCHAR2(50), FACULTY_NAME VARCHAR2(30) ); INSERT INTO NEW_TABLE VALUES ('AGNĖ', 'AGNAITĖ', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('INGRIDA', 'INGRIDAITĖ', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ANTANAS', 'ANTANAITIS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('BRONIUS', 'BRONEVIČIUS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LIEPA', 'LIEPAITĖ', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('AGNĖ', 'TAUTAVIČIŪTĖ', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ILONA', 'ŠILAITĖ', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('AKVILĖ', 'GEDRIMAITĖ', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ANDRIUS', 'STONKUS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('GRETA', 'GRETAITĖ', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('KASPARAS', 'NACIUS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ROKAS', 'DARGEVIČIUS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('JUSTINAS', 'ENGELAITIS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('VALIUS', 'DUMBRAUSKAS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('EDGARAS', 'GVOZDAS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ANTANAS', 'JASAITIS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LINAS', 'STARKUTIS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('AURIMAS', 'GEDMINTAS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LINAS', 'LINAITIS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LINAS', 'LINELIS', 'KLAIPĖDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('KRŪMAS', 'TADAS', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Mėlynas', 'Tadas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Raudonas', 'Tomas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Žalias', 'Karolis', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Geltonas', 'Robertas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Oranžinis', 'Rokas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Juodas', 'Justinas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Pilkas', 'Ignas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Dariauskas', 'Darius', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Kairys', 'Jurgis', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Dešinytė', 'Rūta', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Vyšniauskas', 'Petras', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Siemaitė', 'Aurelija', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Girtauskaitė', 'Ieva', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Piemenaitis', 'Audrius', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Grybas', 'Karolis', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Ekranavičius', 'Tomas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Klavauskas', 'Antanas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Žiurkytė', 'Sandra', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Kaleimauskas', 'Domas', 'KLAIPĖDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Blaivevičius', 'Haroldas', 'KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Pelytė', ' Aušra','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Meškaitė', ' Viktorija','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Vilkas', ' Kęstas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Krūmas', ' Arūnas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Medis', ' Artūras','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Obuolaitė', 'Ingrida','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Gaidauskienė', 'Eglė','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Žuvienė', 'Monika','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Morkavičius', 'Tadas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Dešrytė', 'Kornelija','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Sūris', 'Aurimas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Bandelė', 'Greta','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Rūkavičius', 'Edgaras','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Kreivauskas', 'Mantas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Aklaitė', 'Diana','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Nekalbys', 'Rytis','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Telefonas', 'Deividas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Lentaitė', 'Gabija','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Viniauskas', 'Hermanas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('MALKA', 'TOMAS','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); CREATE SEQUENCE S_STUD; CREATE SEQUENCE S_DOC; CREATE SEQUENCE S_ACCEPT; CREATE OR REPLACE PROCEDURE INSERT_DATA(HOW_MANY_RECORDS NUMBER) IS BEGIN /* 1 TABLE STUDENT */ FOR I IN 1..HOW_MANY_RECORDS LOOP DECLARE F_NAME_VALUE VARCHAR2(20); BEGIN SELECT F_NAME INTO F_NAME_VALUE FROM (SELECT F_NAME FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; END; DECLARE L_NAME_VALUE VARCHAR2(20); BEGIN SELECT L_NAME INTO L_NAME_VALUE FROM (SELECT L_NAME FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; END; DECLARE BORN_DATE_VALUE DATE; BEGIN BORN_DATE_VALUE :=TO_DATE(TRUNC( DBMS_RANDOM.value( TO_CHAR(date '1870-01-01','J') ,TO_CHAR(date '1995-01-01','J') )),'J'); END; INSERT INTO STUDENT(ID_STUDENT,F_NAME,L_NAME,BORN_DATE) VALUES (S_STUD.NEXTVAL,F_NAME_VALUE,L_NAME_VALUE, BORN_DATE_VALUE); ------------------------------------------------------------------- /* 2 TABLE DOCUMENTS */ DECLARE ENTERED_DATE_VALUE DATE; BEGIN ENTERED_DATE_VALUE :=TO_DATE(TRUNC( DBMS_RANDOM.value( TO_CHAR(date '1870-01-01','J') ,TO_CHAR(date '1995-01-01','J') )),'J'); END; DECLARE FINISHED_DATE_VALUE DATE; BEGIN FINISHED_DATE_VALUE :=TO_DATE(TRUNC( DBMS_RANDOM.value( TO_CHAR(date '1870-01-01','J') ,TO_CHAR(date '1995-01-01','J') )),'J'); END; INSERT INTO DOCUMENTS(DOC_ID,ENTERED, FINISHED, ID_STUDENT) VALUES(S_DOC.NEXTVAL,ENTERED_DATE_VALUE, FINISHED_DATE_VALUE, S_STUD.CURRVAL); ------------------------------------------------------------------ /* 3 TABLE ACCEPTANCE */ DECLARE UNIVERSITY_NAME_VALUE VARCHAR2(50); BEGIN SELECT UNIVERSITY INTO UNIVERSITY_NAME_VALUE FROM (SELECT UNIVERSITY FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; END; DECLARE FACULTY_NAME_VALUE VARCHAR2(30); BEGIN SELECT FACULTY_NAME INTO FACULTY_NAME_VALUE FROM (SELECT FACULTY_NAME FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; END; INSERT INTO ACCEPT_RESULT(DETAIL_ID,UNIVERSITY_NAME,FACULTY_NAME,DOC_ID) VALUES (S_ACCEPT.NEXTVAL,UNIVERSITY_NAME_VALUE, FACULTY_NAME_VALUE, S_DOC.CURRVAL); END LOOP; END; / EXECUTE INSERT_DATA(15);
我在这里想念什么？
我解决了！我的问题是变量不在其块外：
DECLARE ... BEGIN END;
因此，我不得不声明将要插入到一个表中的所有变量，并在同一块中进行插入。这是完整的工作代码：
DROP TABLE ACCEPT_RESULT; DROP TABLE DOCUMENTS; DROP TABLE STUDENT; DROP TABLE NEW_TABLE; DROP SEQUENCE S_ACCEPT; DROP SEQUENCE S_DOC; DROP SEQUENCE S_STUD; CREATE TABLE STUDENT (ID_STUDENT NUMBER PRIMARY KEY, F_NAME VARCHAR2(20), L_NAME VARCHAR2(20), BORN_DATE DATE ); CREATE TABLE DOCUMENTS ( DOC_ID NUMBER PRIMARY KEY, ENTERED DATE, FINISHED DATE, ID_STUDENT NUMBER REFERENCES STUDENT(ID_STUDENT) ); CREATE TABLE ACCEPT_RESULT (DETAIL_ID NUMBER PRIMARY KEY, UNIVERSITY_NAME VARCHAR2(50), FACULTY_NAME VARCHAR2(30), DOC_ID NUMBER REFERENCES DOCUMENTS(DOC_ID) ); CREATE TABLE NEW_TABLE ( F_NAME VARCHAR2(20), L_NAME VARCHAR2(20), UNIVERSITY VARCHAR2(50), FACULTY_NAME VARCHAR2(30) ); INSERT INTO NEW_TABLE VALUES ('AGNE', 'AGNAITE', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('INGRIDA', 'INGRIDAITE', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ANTANAS', 'ANTANAITIS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('BRONIUS', 'BRONEVICIUS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LIEPA', 'LIEPAITE', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('AGNE', 'TAUTAVICIUTE', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ILONA', 'SILAITE', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('AKVILE', 'GEDRIMAITE', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ANDRIUS', 'STONKUS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('GRETA', 'GRETAITE', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('KASPARAS', 'NACIUS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ROKAS', 'DARGEVICIUS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('JUSTINAS', 'ENGELAITIS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('VALIUS', 'DUMBRAUSKAS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('EDGARAS', 'GVOZDAS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('ANTANAS', 'JASAITIS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LINAS', 'STARKUTIS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('AURIMAS', 'GEDMINTAS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LINAS', 'LINAITIS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('LINAS', 'LINELIS', 'KLAIPEDA STATE UNIVERSITY OF APPLIED SCIENCES', 'TECHNOLOGY'); INSERT INTO NEW_TABLE VALUES ('KRUMAS', 'TADAS', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('MElynas', 'Tadas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Raudonas', 'Tomas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Zalias', 'Karolis', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Geltonas', 'Robertas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('OranZinis', 'Rokas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Juodas', 'Justinas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Pilkas', 'Ignas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Dariauskas', 'Darius', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Kairys', 'Jurgis', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('DeSinytE', 'Ruta', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('VySniauskas', 'Petras', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('SiemaitE', 'Aurelija', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('GirtauskaitE', 'Ieva', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Piemenaitis', 'Audrius', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Grybas', 'Karolis', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('EkranaviCius', 'Tomas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Klavauskas', 'Antanas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('ZiurkytE', 'Sandra', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('Kaleimauskas', 'Domas', 'KLAIPEDA UNIVERSITY', 'SOCIAL'); INSERT INTO NEW_TABLE VALUES ('BlaiveviCius', 'Haroldas', 'KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('PelytE', ' AuSra','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('MeSkaitE', ' Viktorija','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Vilkas', ' K臋stas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('KrUmas', ' ArUnas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Medis', ' ArtUras','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('ObuolaitE', 'Ingrida','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('GaidauskienE', 'EglE','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('ZuvienE', 'Monika','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('MorkaviCius', 'Tadas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('DeSrytE', 'Kornelija','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('SUris', 'Aurimas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('BandelE', 'Greta','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('RUkaviCius', 'Edgaras','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Kreivauskas', 'Mantas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('AklaitE', 'Diana','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Nekalbys', 'Rytis','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Telefonas', 'Deividas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('LentaitE', 'Gabija','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('Viniauskas', 'Hermanas','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); INSERT INTO NEW_TABLE VALUES ('MALKA', 'TOMAS','KAUNAS UNIVERSITY OF TECHNOLOGY', 'MEDICINE'); CREATE SEQUENCE S_STUD; CREATE SEQUENCE S_DOC; CREATE SEQUENCE S_ACCEPT; CREATE OR REPLACE PROCEDURE INSERT_DATA(HOW_MANY_RECORDS NUMBER) IS BEGIN /* 1 TABLE STUDENT */ FOR I IN 1..HOW_MANY_RECORDS LOOP DECLARE F_NAME_VALUE VARCHAR2(20); L_NAME_VALUE VARCHAR2(20); BORN_DATE_VALUE DATE; BEGIN SELECT F_NAME INTO F_NAME_VALUE FROM (SELECT F_NAME FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; SELECT L_NAME INTO L_NAME_VALUE FROM (SELECT L_NAME FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; BORN_DATE_VALUE := TO_DATE(TRUNC( DBMS_RANDOM.value( TO_CHAR(date '1870-01-01','J') ,TO_CHAR(date '1995-01-01','J') )),'J'); INSERT INTO STUDENT(ID_STUDENT,F_NAME,L_NAME,BORN_DATE) VALUES (S_STUD.NEXTVAL,F_NAME_VALUE,L_NAME_VALUE,BORN_DATE_VALUE); END; ------------------------------------------------------------------- /* 2 TABLE DOCUMENTS */ DECLARE ENTERED_DATE_VALUE DATE; FINISHED_DATE_VALUE DATE; BEGIN ENTERED_DATE_VALUE :=TO_DATE(TRUNC( DBMS_RANDOM.value( TO_CHAR(date '1870-01-01','J') ,TO_CHAR(date '1995-01-01','J') )),'J'); FINISHED_DATE_VALUE :=TO_DATE(TRUNC( DBMS_RANDOM.value( TO_CHAR(date '1870-01-01','J') ,TO_CHAR(date '1995-01-01','J') )),'J'); INSERT INTO DOCUMENTS(DOC_ID,ENTERED, FINISHED, ID_STUDENT) VALUES(S_DOC.NEXTVAL,ENTERED_DATE_VALUE, FINISHED_DATE_VALUE, S_STUD.CURRVAL); END; ------------------------------------------------------------------ /* 3 TABLE ACCEPTANCE */ DECLARE UNIVERSITY_NAME_VALUE VARCHAR2(50); FACULTY_NAME_VALUE VARCHAR2(30); BEGIN SELECT UNIVERSITY INTO UNIVERSITY_NAME_VALUE FROM (SELECT UNIVERSITY FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; SELECT FACULTY_NAME INTO FACULTY_NAME_VALUE FROM (SELECT FACULTY_NAME FROM NEW_TABLE ORDER BY dbms_random.value) WHERE rownum = 1; INSERT INTO ACCEPT_RESULT(DETAIL_ID,UNIVERSITY_NAME,FACULTY_NAME,DOC_ID) VALUES (S_ACCEPT.NEXTVAL,UNIVERSITY_NAME_VALUE, FACULTY_NAME_VALUE, S_DOC.CURRVAL); END; END LOOP; END; / EXECUTE INSERT_DATA(15);
