SQL: Oracle 11g create table, function, trigger, sequence, 11 gtrigger

SQL: Oracle 11g create table, function, trigger, sequence, 11 gtrigger

-- Place directory drop table BookPlaceList; create table BookPlaceList (BookPlaceID int primary key, -- NUMBERBookPlaceName nvarchar2 (500) not null, BookPlaceCode varchar (100) null, -- location parameter BookPlaceParent INT null -- BookPlaceKindId nvarchar (500) null -- place DIRECTORY parameter ID); select * from BookPlaceList; --- automatic growth ID -- SEQUENCE creation drop SEQUENCE BookPlaceList_SEQ; create sequence BookPlaceList_SEQINCREMENT BY 1 -- add several start with 1 each time -- count NOMAXVALUE from 1 -- do not set the maximum value NOCYCLE -- accumulate all the time, do not cycle NOCACHE; -- set cache SEQUENCE, if the system is down or otherwise, the sequence will be discontinuous. You can also set it to --------- nocache select BookPlaceList_SEQ.Currval from dual; SELECT region from dual; -- auto-increment TRIGGER drop TRIGGER BookPlaceList_ID_AUTO; create or replace trigger BookPlaceList_ID_AUTOBEFORE insert on BookPlaceList for each rowbeginselect BookPlaceList_SEQ.NEXTVAL INTO: NEW. bookPlaceID from dual; END; -- create or replace trigger BookPlaceList_ID_AUTO before insert on BookPlaceList -- BookPlaceList is the table name for each rowdeclare nextid number; begin IF: new. bookPlaceID is null or: new. bookPlaceID = 0 THEN -- BookPlaceID is the column name select BookPlaceList_SEQ.Nextval -- BookPlaceList_SEQ is exactly the into nextid from dual;: new. bookPlaceID: = nextid; end if; end; -- BookPlaceList_ID_AUTO -- add insert into BookPlaceList (BookPlaceName, BookPlaceCode, BookPlaceParent) values ('book location directory', '', 0 ); insert into BookPlaceList (BookPlaceName, BookPlaceCode, BookPlaceParent) values ('first cabinet ', '', 1); insert into BookPlaceList (BookPlaceName, BookPlaceCode, BookPlaceParent) values ('second cabinet ', '', 1); insert into BookPlaceList (BookPlaceName, BookPlaceCode, BookPlaceParent) values ('third cabinet ','', 1); select * from BookPlaceList; drop table StaffReaderList; -- employee information Reader staff member IC card number (card changed, card number different), employee number, position, department, if the employee is transferred or resigned, how to solve the record association problem: create table StaffReaderList (StaffReaderID int primary key, StaffReaderIC varchar (100) not null, -- employee id ic No. StaffReaderNO varchar (20) not null, -- employee ID StaffReaderName nvarchar2 (500) not null, -- employee name StaffReaderImage BFILE null, StaffReaderDepartment int, CONSTRAINT primary KEY (StaffReaderDepartment) REFERENCES DepartmentList (inclumentid ), -- Department (foreign KEY) on delete set null on delete cascade StaffReaderPositionint, CONSTRAINT fky_StaffReaderPositionFOREIGN KEY (StaffReaderPosition) REFERENCES PositionList (PositionID), -- Position (foreign KEY) staffReaderMobile varchar (50) null, -- phone StaffReaderTel varchar (200) null, -- phone, StaffReaderSkype varchar (50) null, --- StaffReaderQQ varchar (50) null, -- StaffReaderEmail varchar (100) null, -- email StaffReaderIsJob char check (StaffReaderIsJob in ('n', 'y'), -- whether StaffReaderOperatorID int, CONSTRAINT encrypted foreign key (StaffReaderOperatorID) REFERENCES BookAdministratorList (BookAdminID), -- operator ID (the person who added the record) (foreign key) StaffReaderDatetime TIMESTAMP --); -- determine whether the table has select count (*) FROM User_Tables t WHERE t. table_name = upper ('authorlist'); create or replace FUNCTION f_BookPlacename (kid in number) RETURN nvarchar2 IStmpVar nvarchar2 (100 ); /*************************************** * *********************************** NAME: f_BookPlacename PURPOSE: REVISIONS: Ver Date Author Description --------- ------------ --------------- ---------------------------------- 1.0 2015/5/21 geovindu 1. created this function. NOTES: Automatically available Auto Replace Keywords: Object Name: f_BookPlacename Sysdate: Date and Time:, 12:02:38, and 12:02:38 Username: geovindu (set in TOAD Options, Procedure Editor) table Name: BookPlaceList (set in the "New PL/SQL Object" dialog) **************************************** * ***********************************/BEGIN -- tmpVar: = ""; select BookPlaceName into tmpVar from BookPlaceList where BookPlaceID = kid; RETURN tmpVar; exception when NO_DATA_FOUND then null; when others then -- tmpVar: = ""; -- Consider logging the error and then re-raise RAISE; END f_BookPlacename; -- Test tu juwen 20150522 select f_BookPlacename (1) FROM dual;