1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
| CREATE TABLE VISITORS
(
ID_VISITORS NUMBER(2) NOT NULL,
ID_COUNTRY NUMBER(2) NOT NULL,
ID_PROFESSION NUMBER(2) NOT NULL,
USER_NAME VARCHAR2(255) NOT NULL,
PASSWORD VARCHAR2(255) NOT NULL,
SURNAME VARCHAR2(255) NOT NULL,
FIRSTNAME VARCHAR2(255) NOT NULL,
EMAIL_VISITORS VARCHAR2(255) NOT NULL,
TOWN VARCHAR2(255) NOT NULL,
ADRESS VARCHAR2(255) NOT NULL,
REGION VARCHAR2(255) NOT NULL,
NBRE_CONNECTION NUMBER(2) NOT NULL
, CONSTRAINT PK_VISITORS PRIMARY KEY (ID_VISITORS)
) ;
CREATE TABLE AGENT_PE
(
ID_AGENT NUMBER(2) NOT NULL,
PASSWORD VARCHAR2(255) NOT NULL,
NICKNAME VARCHAR2(255) NOT NULL,
EMAIL_AGENT VARCHAR2(255) NOT NULL,
CONNECTED NUMBER(1) NOT NULL,
BUSY NUMBER(1) NOT NULL
, CONSTRAINT PK_AGENT_PE PRIMARY KEY (ID_AGENT)
) ;
CREATE SEQUENCE agent_pe_id_agent_pe
INCREMENT BY 1
START WITH 1
MAXVALUE 9999
NOCACHE
NOCYCLE;
CREATE SEQUENCE visitors_id_visitors
INCREMENT BY 1
START WITH 1
MAXVALUE 9999
NOCACHE
NOCYCLE;
CREATE OR REPLACE FUNCTION add_visitors (v_idcountry IN visitors.id_country%TYPE, v_idprofession IN visitors.id_profession%TYPE, v_username IN visitors.user_name%TYPE, v_password IN visitors.password%TYPE, v_surname IN visitors.surname%TYPE, v_firstname IN visitors.firstname%TYPE, v_mail IN visitors.email_visitors, v_town IN visitors.town%TYPE, v_adress IN visitors.adress%TYPE, v_region IN visitors.region%TYPE) RETURN NUMBER
IS
CURSOR agent_cursor IS
SELECT *
FROM agent_pe
WHERE nickname = v_username;
CURSOR visitor_cursor IS
SELECT * FROM visitors
WHERE user_name = v_username;
rec_visitor visitors%ROWTYPE;
rec_agent agent_pe%ROWTYPE;
v_return NUMBER :=0;
BEGIN
OPEN agent_cursor;
FETCH agent_cursor INTO rec_agent;
IF agent_cursor%NOTFOUND THEN
OPEN visitor_cursor;
FETCH visitor_cursor INTO rec_visitor;
IF visitor_cursor%NOTFOUND THEN
INSERT INTO visitors (id_visitors, id_country, id_profession, user_name, password, surname, firstname, email_visitors, town, adress, region, nbre_connection)
VALUES(visitors_id_visitors.NEXTVAL, v_idcountry,v_idprofession, v_username, v_password, v_surname, v_firstname, v_mail, v_town, v_adress, v_region,0);
COMMIT;
v_return :=1;
END IF;
END IF;
RETURN v_return;
END;
/ |
Partager