oracle建表建表空间注释,oracle 建表空间，授权，建表，建存储过程脚本

-- select sysdate from dual;

-- 以管理员身份登录PL/SQL, create tablespace

create tablespace YourProject_table_space datafile 'D:\oracle\data\customed\YourProject.dbf' size 100M;

-- create user and assign tablespace for this user

create user YourProject_admin_u identified by YourProject_admin_p default tablespace YourProject_table_space;

-- grant user privilege

grant connect,resource to YourProject_admin_u;

-- 以 YourProject_admin_u 身份登录 oracle 数据库，创建如下的表 及 存储过程

create table tblStatistic

(

ID int

,UserID varchar2(50)

,UserName varchar2(50)

,Telephone int

,StatisticTypeID int --2:user login; 3:online draft; 4:audit or modify

,Title varchar2(500)

,ObjectID int -- if not have this value, fill 0

,OperDate Date

);

create table tblVideo

(

ID int

,Subject varchar2(50)

,Description varchar2(50)

,YoukuVideoUrl varchar2(1000)

,YoukuVideoID varchar(50)

,OperDate Date

,ViewCount int

,YoukuPlayUrl varchar2(1000)

,DownloadUrl varchar2(1000)

);

-- 用户收藏

create table tblUserFavorite

(

ID int

,UserID varchar2(50)

,UserName varchar2(50)

,Telephone int

,ArticleID int

,ArticleTypeID int

,Title varchar2(100)

,Description varchar2(500)

,OperDate date

,DocTypeID int -- (0-案例, 1-合同, 2-法律)

);

-- 用户咨询

create table tblUserConsultation

(

ID int

,UserID varchar2(50)

,UserName varchar2(50)

,Telephone int

,RequestPeople varchar2(50)

,BusinessType varchar2(50)

,BusinessSubType varchar2(50)

,Topic varchar2(100)

,Content varchar2(4000)

,IsPublic numeric(1)

,OperDate date

);

-- 用户反馈

create table tblUserFeedback

(

ID int

,UserID varchar2(50)

,UserName varchar2(50)

,Telephone int

,Email varchar2(100)

,Content varchar2(1000)

,OperDate date

);

-- 文章阅读信息

create table tblArticleRead

(

ID int

,ArticleID int

,ArticleTypeID int

,ArticleType varchar2(50)

,ReadCount int

);

-- 系统装机统计

create table tblInstallDevice

(

ID int

,DeviceNumber varchar2(100)

,InstallDate date

);

-- store procedure

-- 增加文章阅读

create or replace procedure spAddArticleRead

(

v_articleID int,

v_articleTypeID int,

v_articleType varchar2

)

as

v_existCount int;

begin

SELECT count(*) into v_existCount

from tblArticleRead where ArticleID=v_articleID and ArticleTypeID=v_articleTypeID;

--dbms_output.put_line(v_existCount);

if(v_existCount>=1) then

--dbms_output.put_line('exists');

update tblArticleRead set ReadCount=ReadCount+1 where ArticleID=v_articleID and ArticleTypeID=v_articleTypeID;

else

--dbms_output.put_line('not exists');

insert into tblArticleRead

(

ID,ArticleID,ArticleTypeID

,ArticleType,ReadCount

)

select nvl(max(ID),0) + 1,v_articleID,v_articleTypeID

,v_articleType,1

from tblArticleRead;

end if;

end;

-- 增加用户收藏

create or replace procedure spAddMyFavorite

(

v_UserID varchar2,

v_UserName varchar2,

v_Telephone varchar2,

v_ArticleID int,

v_ArticleTypeID int,

v_Title varchar2,

v_Description varchar2,

v_DocTypeID int

)

as

v_tmpWhere varchar2(4000);

v_tmpSQL varchar2(4000);

v_existCount int;

begin

v_tmpWhere :='';

if nvl(v_UserID,'X') !='X' then

v_tmpWhere := v_tmpWhere || ' and UserID=''' || trim(v_UserID) || '''';

end if;

if nvl(v_UserName,'X') !='X' then

v_tmpWhere := v_tmpWhere || ' and UserName=''' || trim(v_UserName) || '''';

end if;

if nvl(v_Telephone,'X') !='X' then

v_tmpWhere := v_tmpWhere || ' and Telephone=''' || v_Telephone || '''';

end if;

if v_ArticleID >0 then

v_tmpWhere := v_tmpWhere || ' and ArticleID=' || v_ArticleID ;

end if;

if v_ArticleTypeID >0 then

v_tmpWhere := v_tmpWhere || ' and ArticleTypeID=' || v_ArticleTypeID;

end if;

if nvl(v_Title,'X') !='X' then

v_tmpWhere := v_tmpWhere || ' and Title =''' || trim(v_Title) || '''';

end if;

if nvl(v_Description,'X') !='X' then

v_tmpWhere := v_tmpWhere || ' and Description =''' || trim(v_Description) || '''';

end if;

if v_DocTypeID >-1 then

v_tmpWhere := v_tmpWhere || ' and DocTypeID =' || v_DocTypeID ;

end if;

--dbms_output.put_line(v_tmpWhere);

if(length(v_tmpWhere)>0) then

v_tmpWhere := ' where ' || substr(v_tmpWhere,5);

end if;

v_tmpSQL :='SELECT count(*) from tblUserFavorite ' || v_tmpWhere;

EXECUTE IMMEDIATE v_tmpSQL into v_existCount;

--dbms_output.put_line(v_tmpSQL);

--dbms_output.put_line(v_existCount);

if(v_existCount<1) then

--dbms_output.put_line('not exists');

insert into tblUserFavorite

(

ID,UserID,UserName,Telephone

,ArticleID,ArticleTypeID,Title,Description

,OperDate,DocTypeID

)

select nvl(max(ID),0) + 1,v_UserID,v_UserName,v_Telephone

,v_ArticleID,v_ArticleTypeID,v_Title,v_Description

,to_date(sysdate),v_DocTypeID

from tblUserFavorite;

else

dbms_output.put_line('您已经收藏过此项!');

raise_application_error(-20001,'您已经收藏过此项!');

end if;

end;

-- 添加装机信息

create or replace procedure spAddInstallDevice

(

v_DeviceNumber varchar2

)

as

begin

insert into tblInstallDevice

(

ID,DeviceNumber,InstallDate

)

select nvl(max(ID),0) + 1,v_DeviceNumber,to_date(sysdate)

from tblInstallDevice;

end;

-- 添加统计信息

create or replace procedure spAddStatisticContent

(

v_UserID varchar2,

v_UserName varchar2,

v_Telephone varchar2,

v_StatisticTypeID int,

v_Title varchar2,

v_ObjectID int

)

as

begin

insert into tblStatistic

(

ID,UserID,UserName,Telephone

,StatisticTypeID,Title,ObjectID

,OperDate

)

select nvl(max(ID),0) + 1,v_UserID,v_UserName,v_Telephone

,v_StatisticTypeID,v_Title,v_ObjectID

--,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

,to_char(sysdate)

from tblStatistic;

end;