PLSQL 學習之路（1）創建用戶，表空間，表，數據

1.用SYS用戶登錄PL/SQL

SYS@XE as SYSDBA

---

?

2.創建表空間

create tablespace Mars datafile 'F:\oracle\Mars.dbf' size 500M

autoextend on next 100M maxsize unlimited logging

extent management local autoallocate

segment space management auto;

---

?

3.創建用戶

create?user Mars?identified?by?welcome1
DEFAULT?tablespace Mars;
grant?connect?to Mars;
grant?resource?to Mars;
--?Grant/Revoke?system?privileges?
grant?create?any?sequence?to Mars;
grant?create?any?table?to Mars;
grant?create?any?view?to Mars;
grant?create?synonym?to Mars;
grant?debug?any?procedure?to Mars;
grant?debug?connect?session?to Mars;
grant?delete?any?table?to Mars;

---

?

4.創建表

-- Create table
create table Mars.ORGANIZATIONS
(
organization_id NUMBER not null,
organization_code VARCHAR2(100),
organization_name VARCHAR2(100),
parent_organization_id NUMBER,
organization_level VARCHAR2(100),
organization_type VARCHAR2(100),
organization_status VARCHAR2(100),
order_num NUMBER,
organization_desc VARCHAR2(4000),
organization_seq VARCHAR2(4000),
organization_address VARCHAR2(500),
object_version_number NUMBER default 1 not null,
creation_date DATE default sysdate not null,
created_by VARCHAR2(100) not null,
last_updated_by VARCHAR2(100) not null,
last_update_date DATE default sysdate not NULL,
attribute1 VARCHAR2(240),
attribute2 VARCHAR2(240),
attribute3 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute5 VARCHAR2(240),
attribute6 VARCHAR2(240),
attribute7 VARCHAR2(240),
attribute8 VARCHAR2(240),
attribute9 VARCHAR2(240)
)
tablespace Mars
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

---

5.創建sequence

-- Create sequence
create sequence Mars.Organizations_S
minvalue 1
maxvalue 9999999999999999999999999999
start with 10001
increment by 1
cache 20;

---

?

6.創建數據

INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
-- PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'XXX公司',
'Company',
'Enable',
1,
SYSDATE,
'MARS',
'MARS',
SYSDATE);
INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'IT部',
10001,
'Department',
'Enable',
2,
SYSDATE,
'MARS',
'MARS',
SYSDATE);
INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'財務部',
10001,
'Department',
'Enable',
2,
SYSDATE,
'MARS',
'MARS',
SYSDATE);
INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'SOA部門',
10002,
'Department',
'Enable',
3,
SYSDATE,
'MARS',
'MARS',
SYSDATE);

?