sql: Oracle 11g create table, function,trigger, sequence

?

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 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128

--书藉位置Place目录  drop table BookPlaceList;      create table BookPlaceList (     BookPlaceID INT  PRIMARY KEY,  --NUMBER     BookPlaceName nvarchar2(500) not null,     BookPlaceCode varchar(100) null,        --位置編碼     BookPlaceParent INT  null     --BookPlaceKindId nvarchar(500) null       --放置目录範圍ID );   select * from BookPlaceList;   ---自动增长ID --序列创建 drop SEQUENCE BookPlaceList_SEQ;   CREATE SEQUENCE BookPlaceList_SEQ INCREMENT BY 1     -- 每次加几个 START WITH 1     -- 从1开始计数 NOMAXVALUE        -- 不设置最大值 NOCYCLE            -- 一直累加，不循环 NOCACHE;           --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE         SELECT BookPlaceList_SEQ.Currval FROM DUAL;   SELECT BookPlaceList_SEQ.Nextval FROM DUAL;     --自增长触发器 drop TRIGGER BookPlaceList_ID_AUTO;   CREATE OR REPLACE TRIGGER BookPlaceList_ID_AUTO BEFORE INSERT ON BookPlaceList FOR EACH ROW BEGIN SELECT BookPlaceList_SEQ.NEXTVAL INTO :NEW.BookPlaceID FROM DUAL; END;         --自增长触发器      create or replace trigger BookPlaceList_ID_AUTO   before insert on BookPlaceList   --BookPlaceList 是表名   for each row declare   nextid number; begin   IF :new.BookPlaceID IS NULL or :new.BookPlaceID=0 THEN --BookPlaceID是列名     select BookPlaceList_SEQ.Nextval --BookPlaceList_SEQ正是刚才创建的     into nextid     from dual;     :new.BookPlaceID:=nextid;   end if; end;  -- BookPlaceList_ID_AUTO    --添加  insert into BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('图书位置目录','',0);    insert into BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第一柜','',1); insert into BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第二柜','',1);   insert into BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第三柜','',1);   select * from BookPlaceList;   drop table StaffReaderList; --职员信息Reader  staff member IC卡号（卡换了，卡号不一样），员工号，职位，部门,如果职员换岗或离职了，这个问题如何解决记录关联问题 create table StaffReaderList (     StaffReaderID INT  PRIMARY KEY,     StaffReaderIC varchar(100) not null,            --员工工牌IC号     StaffReaderNO varchar(20) not null,             --员工编号     StaffReaderName nvarchar2(500) not null,            --员工姓名     StaffReaderImage BFILE null,     StaffReaderDepartment int,      CONSTRAINT fky_StaffReaderDepartment             FOREIGN KEY(StaffReaderDepartment) REFERENCES DepartmentList(DepartmentID),--员工所属部门（外键）   ON DELETE SET NULL   ON DELETE CASCADE       StaffReaderPosition   int,      CONSTRAINT fky_StaffReaderPosition             FOREIGN KEY(StaffReaderPosition) REFERENCES PositionList(PositionID),   --职位Position（外键）       StaffReaderMobile varchar(50) null,               --手机     StaffReaderTel varchar(200) null,               --电话，     StaffReaderSkype varchar(50) null,              ---     StaffReaderQQ varchar(50) null,                 --     StaffReaderEmail varchar(100) null,             --电子邮件     StaffReaderIsJob char check (StaffReaderIsJob in ('N','Y')),                --是否離職     StaffReaderOperatorID int,     CONSTRAINT fky_StaffReaderOperatorID              FOREIGN KEY(StaffReaderOperatorID) REFERENCES  BookAdministratorList(BookAdminID),--操作人员ID（添加记录的人员）（外键）     StaffReaderDatetime TIMESTAMP  --               );   --判断表是否存在 SELECT COUNT(*) FROM User_Tables t WHERE t.table_name = upper('AuthorList'); create or replace FUNCTION f_BookPlacename(kid in number) RETURN nvarchar2 IS tmpVar 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:         2015/5/21       Date and Time:   2015/5/21, 12:02:38, and 2015/5/21 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; --测试 涂聚文 20150522 select f_BookPlacename(1)  FROM dual;

 

?

1 2 3 4 5 6 7 8 9 10 11 12

declare tableCount number; begin    select count(1) into tableCount  from user_tables t where t.table_name = upper('TestDu'); --从系统表中查询当表是否存在    if tableCount  = 0 then --如果不存在，使用快速执行语句创建新表       execute immediate       'create table TestDu --创建测试表          (              TestID     number   not null,              TestName   varchar2(20)  not null           )';    end if; end;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

declare --在PL/SQL 匿名块中定义变量 vName nvarchar2(200):='齐白石水彩画系列'; vEname nvarchar2(200):='丰子恺油画系列'; vId number:=2; --set serveroutput on size 5000; begin   update BookSeriesList set BookSeriesName=vName where BookSeriesID=vId;   DBMS_OUTPUT.PUT_LINE('书系列更新成功！'); IF SQL%NOTFOUND THEN  --判断，如果未更新数据，则向表中插入记录     insert into BookSeriesList(BookSeriesName) values(vName);     DBMS_OUTPUT.PUT_LINE('书系列插入成功！'); END IF; ---异常处理 EXCEPTION WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE('插入书系列记录错误！'); end;

　

Toad for Oracle 启动DBMS输出. Oracle SQL developer 和 SQL Plus 用Script启动:set serveroutput on size 5000;

见图: