Project5.sql

/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.5.0

Source Server         : OracleHr
Source Server Version : 110200
Source Host           : :1521
Source Schema         : HR

Target Server Type    : ORACLE
Target Server Version : 110200
File Encoding         : 65001

Date: 2017-07-08 22:59:15
*/


-- ----------------------------
-- Table structure for BADGES
-- ----------------------------
DROP TABLE "HR"."BADGES";
CREATE TABLE "HR"."BADGES" (
"BADGESID" NUMBER NOT NULL ,
"USERID" NUMBER NULL ,
"COMMUNITYID" NUMBER NULL ,
"BADGESNAME" VARCHAR2(400 BYTE) NULL ,
"BADGESDATE" DATE NULL ,
"BADGESCLASS" NUMBER NULL ,
"TAGBASED" VARCHAR2(10 BYTE) NULL 
)

;

-- ----------------------------
-- Table structure for COMMENTS
-- ----------------------------
DROP TABLE "HR"."COMMENTS";
CREATE TABLE "HR"."COMMENTS" (
"ID" NUMBER NOT NULL ,
"POSTID" NUMBER NULL ,
"SCORE" NUMBER NULL ,
"TEXT" VARCHAR2(4000 BYTE) NULL ,
"CREATIONDATE" DATE NULL ,
"USERDISPLAYNAME" VARCHAR2(400 BYTE) NULL ,
"USERID" NUMBER NULL 
)

;


-- ----------------------------
-- Table structure for GLOBALUSERS
-- ----------------------------
DROP TABLE "HR"."GLOBALUSERS";
CREATE TABLE "HR"."GLOBALUSERS" (
"ACCOUNTID" NUMBER NOT NULL ,
"DISPLAYNAME" VARCHAR2(4000 BYTE) NULL ,
"WEBSITEURL" VARCHAR2(4000 BYTE) NULL ,
"USERLOCATION" VARCHAR2(4000 BYTE) NULL ,
"ABOUTME" VARCHAR2(4000 BYTE) NULL ,
"PROFILEIMAGEURL" VARCHAR2(4000 BYTE) NULL ,
"AGE" NUMBER NULL 
)
;

-- ----------------------------
-- Table structure for POSTHISTORY
-- ----------------------------
DROP TABLE "HR"."POSTHISTORY";
CREATE TABLE "HR"."POSTHISTORY" (
"ID" NUMBER NOT NULL ,
"POSTHISTORYTYPEID" NUMBER NULL ,
"POSTID" NUMBER NULL ,
"REVISIONGUID" VARCHAR2(4000 BYTE) NULL ,
"CREATIONDATE" DATE NULL ,
"USERID" NUMBER NULL ,
"USERDISPLAYNAME" VARCHAR2(400 BYTE) NULL ,
"POSTHISTORYCOMMENT" VARCHAR2(4000 BYTE) NULL ,
"TEXT" VARCHAR2(4000 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Table structure for POSTLINKS
-- ----------------------------
DROP TABLE "HR"."POSTLINKS";
CREATE TABLE "HR"."POSTLINKS" (
"POSTLINKID" NUMBER NOT NULL ,
"CREATIONDATE" DATE NULL ,
"POSTID" NUMBER NULL ,
"RELEATEDPOSTID" NUMBER NULL ,
"LINKTYPEID" NUMBER NULL 
)

;

-- ----------------------------
-- Table structure for POSTS
-- ----------------------------
//Tags field

DROP TABLE "HR"."POSTS";
CREATE TABLE "HR"."POSTS" (
"POSTID" NUMBER NOT NULL ,
"COMMUNITYID" NUMBER NULL ,
"LOCALID" NUMBER NULL ,
"POSTTYPEID" NUMBER NULL ,
"ACCEPTEDANSWERID" NUMBER NULL ,
"PARENTID" NUMBER NULL ,
"CREATIONDATE" DATE NULL ,
"SCORE" NUMBER NULL ,
"VIEWCOUNT" NUMBER NULL ,
"POSTBODY" VARCHAR2(4000 BYTE) NULL ,
"OWNERUSERID" NUMBER NULL ,
"OWNERDISPLAYNAME" VARCHAR2(400 BYTE) NULL ,
"LASTEDITORUSERID" NUMBER NULL ,
"LASTEDITORDISPLAYNAME" VARCHAR2(400 BYTE) NULL ,
"LASTEDITDATE" DATE NULL ,
"LASTACTIVITYDATE" DATE NULL ,
"TITLE" VARCHAR2(4000 BYTE) NULL ,
"ANSWERCOUNT" NUMBER NULL ,
"COMMENTCOUNT" NUMBER NULL ,
"FAVOURITECOUNT" NUMBER NULL ,
"CLOSEDDATE" DATE NULL ,
"COMMUNITYOWNEDDATE" DATE NULL 
)
;

-- ----------------------------
-- Table structure for TAGS
-- ----------------------------
DROP TABLE "HR"."TAGS";
CREATE TABLE "HR"."TAGS" (
"TAGID" NUMBER NOT NULL ,
"TAGNAME" VARCHAR2(400 BYTE) NULL ,
"COUNT" NUMBER NULL ,
"EXCERPTPOSTID" NUMBER NULL ,
"WIKIPOSTID" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Table structure for USERS
-- ----------------------------
DROP TABLE "HR"."USERS";
CREATE TABLE "HR"."USERS" (
"USERID" NUMBER NOT NULL ,
"COMMUNITYID" NUMBER NULL ,
"ACCOUNTID" NUMBER NULL ,
"LOCALID" NUMBER NULL ,
"REPUTATION" NUMBER NULL ,
"CREATIONDATE" DATE NULL ,
"LASTACCESSDATE" DATE NULL ,
"VIEWS" NUMBER NULL 
)

;

-- ----------------------------
-- Table structure for VOTES
-- ----------------------------
DROP TABLE "HR"."VOTES";
CREATE TABLE "HR"."VOTES" (
"VOTEID" NUMBER NOT NULL ,
"POSTID" NUMBER NULL ,
"VOTETYPEID" NUMBER NULL ,
"VOTERUSERID" NUMBER NULL ,
"CREATIONDATE" DATE NULL ,
"BOUNTYAMOUNT" NUMBER NULL 
)

;


-- ----------------------------
-- Procedure structure for DEMO
-- ----------------------------
CREATE OR REPLACE PROCEDURE "HR"."DEMO"(p_sql IN VARCHAR2) AS
   l_cursor  INTEGER;
   l_dummy   NUMBER;
   timestart NUMBER;
	 timeend NUMBER;
	 totaltime NUMBER;
 BEGIN
		totaltime:=0;
		FOR i in 1..30
		LOOP
			timestart := dbms_utility.get_time();
			l_cursor  := dbms_sql.open_cursor;
			dbms_sql.parse(l_cursor, p_sql, dbms_sql.native);
			l_dummy := dbms_sql.execute(l_cursor);
					LOOP
							EXIT WHEN dbms_sql.fetch_rows(l_cursor) <= 0;
					END LOOP;
			dbms_sql.close_cursor(l_cursor);
			timeend := dbms_utility.get_time();
			dbms_output.put_line((timeend - timestart)/100);
			totaltime:= totaltime + (timeend - timestart)/100;

		END LOOP;
		dbms_output.put_line('Total Time: '||totaltime);
		dbms_output.put_line('Avg Time: '||(totaltime/30));
  END;
/