（如果我分享的干貨內(nèi)容對你有幫助，可以通過贊或者評論的方式告訴我，我會持續(xù)分享；或者留言你想要的IT方面的支持，我將分享大家感興趣的IT類技術(shù)干貨；如果沒有收到大家的反饋，10天后我將停止技術(shù)類干貨文章的分享）

引言

/*

使用Oracle數(shù)據(jù) 在使用第三方工具時需要先啟動兩個服務(wù)

監(jiān)聽器服務(wù)：lsnrctl start

數(shù)據(jù)庫服務(wù)：net start OracleServiceOrcl

登錄方式：

dos窗口：sqlplus system/accp

第三方工具(pl/sql developer):

很久未使用 忘記密碼怎么辦？

sqlplus / as sysdba;

alter user system identified by accp;

scott普通用戶 一般安裝默認(rèn)鎖定狀態(tài)locked 密碼為tiger

alter user scott account unlock;

提供了兩張常用示范表 emp、dept

*/

一、表空間

/*1.創(chuàng)建表空間*/

create tablespace worktbs --表空間名稱

datafile 'D:T146WORDTBS01.dbf' --文件路徑

size 10M --表空間大小

autoextend on next 10M --自動擴(kuò)展10M

/*2.查看當(dāng)前用戶下的表空間*/

select * from dba_data_files;

--3.擴(kuò)展表空間

/*3.1.修改表空間數(shù)據(jù)文件大小*/

alter database

datafile 'D:T146WORDTBS01.dbf'

resize 20M;

/*3.2.向表空間內(nèi)添加數(shù)據(jù)文件*/

alter tablespace worktbs

add datafile

'D:T146WORDTBS02.dbf'

size 10M

autoextend on;

/*4.更改表空間為只讀*/

ALTER TABLESPACE worktbs READ ONLY;

/*5.刪除表空間*/

drop tablespace worktbs including contents and datafiles;

二、用戶權(quán)限管理

--1.創(chuàng)建用戶

CREATE USER king --用戶名

IDENTIFIED BY 123456 --密碼

DEFAULT TABLESPACE worktbs --表空間

[TEMPORARY TABLESPACE tablespace] --指定臨時表空間 可省略

--2.對用戶進(jìn)行權(quán)限管理

grant connect,resource to king; --授予權(quán)限

revoke connect,resource from king;--回收權(quán)限

grant create session to king;

revoke create session from king;

grant insert,delete,update,select on scott.dept to king;--授予對表操作的權(quán)限

grant all on scott.emp to king;

--3.修改用戶密碼

alter user king identified by accp;

--4.刪除用戶

DROP USER king CASCADE; --當(dāng)用戶 模式下有模式對象時需要添加CASCADE關(guān)鍵字進(jìn)行級聯(lián)刪除

--刪除表

drop table depts;

--創(chuàng)建表(不要表數(shù)據(jù) 只要表結(jié)構(gòu))

create table depts

as

select * from scott.dept where 1>1;

--創(chuàng)建表(復(fù)制表結(jié)構(gòu)以及表數(shù)據(jù))

create table depts

as

select * from scott.dept ;

--查看表

select * from depts;

三、序列

--1.創(chuàng)建序列

create sequence seq_dept --指定序列名稱

start with 10 --指定序列起始值

increment by 10 --指定增長量

maxvalue 50 --指定序列最大值

nocycle --不循環(huán)

cache 10; --緩存10個序列值

create sequence seq1;

/*

序列中有兩個非常重要的屬性

1.nextval 下一個值

2.currval 當(dāng)前值

在序列沒有使用過nextval之前，不能使用currval獲取序列的當(dāng)前值，因?yàn)樾蛄猩形闯跏蓟?/p>

*/

--2.修改序列

alter sequence seq1

increment by 14

maxvalue 100

minvalue 10

cycle

cache 7;

--3.使用序列

select seq_dept.nextval from dual;

select seq_dept.currval from dual;

insert into depts values(seq_dept.nextval,'hr','shenzhen');

select * from depts;

select seq1.nextval from dual;

select seq1.currval from dual;

--4.刪除序列

drop sequence seq_dept;

--5.使用GUID 生成唯一標(biāo)識符 推薦在并行環(huán)境下使用

select sys_guid() from dual;

四、同義詞

/*創(chuàng)建員工表*/

create table employee(

empon number(4) not null,

ename varchar(10),

job varchar(9),

mgr number(4),

hiredate date,

sal number(9,2),

comm number(7,2),

deptno number(2)

);

select * from employee

--將訪問員工表的權(quán)限授予A_oe用戶

GRANT SELECT ON employee TO A_oe;

--創(chuàng)建私有同義詞

CREATE OR REPLACE SYNONYM emp FOR employee;

--以A_oe用戶登錄

SELECT * FROM emp;

--創(chuàng)建公有同義詞

CREATE PUBLIC SYNONYM public_emp FOR A_hr.employee;

---以A_oe用戶登錄

select * from public_emp

--刪除私有同義詞

DROP SYNONYM emp;

--刪除公有同義詞

DROP PUBLIC SYNONYM public_emp;

五、索引

/*1.B樹索引*/

create unique index index_empno on employee(empno);

/*2.反向鍵索引*/

create index index_reverse_empno on employee(empno) reverse;

/*3.位圖索引*/

create bitmap index index_bit_job on employee(job);

/*4.重建索引*/

alter index index_reverse_empno rebuild noreverse;

/*5.刪除索引*/

drop index index_bit_job;

六、分區(qū)表

--1.創(chuàng)建分區(qū)表 范圍分區(qū)

create table sales1(

sales_id NUMBER NOT NULL,

product_id varchar2(5),

SALES_DATE DATE,

SALES_COST NUMBER(10),

AREACODE VARCHAR2(5)

)

partition BY RANGE(SALES_DATE)

(

partition part1 values less than(to_date('2013/04/1','yyyy/mm/dd')),

partition part2 values less than(to_date('2013/07/1','yyyy/mm/dd')),

partition part3 values less than(to_date('2013/10/1','yyyy/mm/dd')),

partition part4 values less than(to_date('2014/01/1','yyyy/mm/dd')),

partition part5 values less than(maxvalue)

)

--查詢分區(qū)情況

select table_name,partition_name

from user_tab_partitions

where table_name=UPPER('sales1');

--插入數(shù)據(jù)

insert into sales1 values(1000,'p1',to_date('2013/07/1','yyyy/mm/dd'),1000,'A1')

insert into sales1 values(1001,'p2',to_date('2013/11/13','yyyy/mm/dd'),1001,'A2')

--使用分區(qū)查詢

select * from sales1 partition(part2)

--刪除分區(qū)

alter table sales1 drop partition(P1);

--2.創(chuàng)建分區(qū)表 間隔分區(qū)

create table sales2(

sales_id NUMBER NOT NULL,

product_id varchar2(5),

SALES_DATE DATE,

SALES_COST NUMBER(10),

AREACODE VARCHAR2(5)

)

partition by range(sales_date)

interval(numtoyminterval(3,'Month'))

(partition p1 values less than (to_date('2013/07/1','yyyy/mm/dd')));

--插入數(shù)據(jù)

insert into sales2 values(1000,'p1',to_date('2013/08/1','yyyy/mm/dd'),1000,'A1')

insert into sales1 values(1001,'p2',to_date('2013/11/13','yyyy/mm/dd'),1001,'A2')

--獲取分區(qū)情況

select table_name,partition_name

from user_tab_partitions

where table_name=UPPER('sales2');

--查看分區(qū)數(shù)據(jù)

select * from sales2 partition(sys_P41)

七、PL/SQL基礎(chǔ)知識

/*1.PL/SQL組成部分

[DECLARE]

--聲明部分，在此聲明PL/SQL用到的變量、類型及游標(biāo)，以及局部的存儲過程和函數(shù)

BEGIN

--執(zhí)行部分：過程及SQL語句，即程序的主要部分

[EXCEPTION]

--異常處理部分：錯誤處理

END;

--執(zhí)行部分不可省略

*/

/*2.PL/SQL變量與常量語法

變量名 數(shù)據(jù)類型[（范圍大小）][:= 值]; --變量聲明賦值語法

常量名 CONSTANT 數(shù)據(jù)類型 := 值; --常量聲明賦值語法

*/

--例：

DECLARE

v_ename VARCHAR2(20);

v_rate NUMBER(7,2)；

c_rate_incr CONTRANT NUMBER(7,2) := 1.10;

BEGIN

--賦值方式一，通過SELECT INFO

SELECT ename,sal* c_rate_incr INTO v_ename, v_rate

FROM employee

WHERE empno = '7788';

--賦值方式二,通過賦值操作符“：=”

v_ename := 'SCOTT';

END;

/*3.PL/SQL控制語句*/

--3.1條件控制

--3.1.1

BEGIN

IF false THEN

DBMS_OUTPUT.PUT_LINE('t142');

ELSE

DBMS_OUTPUT.PUT_LINE('AAAAAA');

END IF;

END;

--3.1.2

DECLARE

v_num NUMBER(2,0) := 10;

BEGIN

IF v_num>10 THEN

DBMS_OUTPUT.PUT_LINE('100');

ELSIF v_num=10 THEN

DBMS_OUTPUT.PUT_LINE('10');

ELSE

DBMS_OUTPUT.PUT_LINE('0');

END IF;

END;

--3.2.1循環(huán)控制-LOOP

DECLARE

v_num NUMBER(2,0) := 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE(v_num);

v_num+:=1;

EXIT WHEN v_num=11;

END LOOP;

END;

--3.2.2循環(huán)控制-FOR

DECLARE

v_num NUMBER(2,0) := 1;

v_num2 NUMBER(2,0);

BEGIN

FOR v_num2 IN REVERSE 1 .. 5 LOOP

DBMS_OUTPUT.PUT_LINE(v_num2);

END LOOP;

END;

八、異常處理

--1.預(yù)定義異常

create table employee as select * from scott.emp;

select * from employee;

--未進(jìn)行異常處理

DECLARE

v_ename employee.ename%TYPE;

BEGIN

SELECT ename INTO v_ename

FROM employee

WHERE empno=1234;

dbms_output.put_line('雇員名:'||v_ename);

END;

--進(jìn)行異常處理

DECLARE

v_ename employee.ename%TYPE;

BEGIN

SELECT ename INTO v_ename

FROM employee

WHERE empno=1234;

dbms_output.put_line('雇員名:'||v_ename);

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('雇員號不正確');

WHEN TOO_MANY_ROWS THEN

dbms_output.put_line('查詢只能返回單行');

WHEN OTHERS THEN

dbms_output.put_line('錯誤號：'||SQLCODE||'錯誤描述：'||SQLERRM);

END;

--2.定義自定義異常

--查詢編號為7788的雇員的福利補(bǔ)助（comm列）。

DECLARE

v_comm employee.comm%TYPE;

e_comm_is_null EXCEPTION; --定義異常類型變量

BEGIN

SELECT comm INTO v_comm FROM employee WHERE empno=7788;

IF v_comm IS NULL THEN

RAISE e_comm_is_null;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('雇員不存在！錯誤為:'||SQLCODE||SQLERRM);

WHEN e_comm_is_null THEN

dbms_output.put_line('該雇員無補(bǔ)助');

WHEN others THEN

dbms_output.put_line('出現(xiàn)其他異常');

END;

九、游標(biāo)

/*顯式游標(biāo)使用步驟*/

declare

v_ename employee.ename%type;

v_sal employee.sal%type;

--1.聲明游標(biāo)

cursor cursor_emp is

select ename,sal from employee

for update of sal;

begin

--2.打開游標(biāo)

open cursor_emp;

--3.提取游標(biāo) 讀取游標(biāo)中存儲數(shù)據(jù) 使用fetch...into 還可以使用for循環(huán)讀取

/*loop

fetch cursor_emp into v_ename,v_sal;

exit when cursor_emp%notfound;

dbms_output.put_line('雇員姓名：'||v_ename||' 雇員薪水：'||v_sal);

end loop;*/

--提取游標(biāo) 進(jìn)行修改

loop

fetch cursor_emp into v_ename,v_sal;

exit when cursor_emp%notfound;

update employee set sal = sal+200

where current of cursor_emp;

end loop;

--4.關(guān)閉游標(biāo)

close cursor_emp;

end;

十、存儲過程

/*1.創(chuàng)建存儲過程*/

CREATE OR REPLACE PROCEDURE add_employee(

eno NUMBER, --輸入?yún)?shù),雇員編號

name VARCHAR2, --輸入?yún)?shù),雇員名稱

salary NUMBER, --輸入?yún)?shù),雇員薪水

job VARCHAR2 DEFAULT 'CLERK', --輸入?yún)?shù),雇員工種默認(rèn)'CLERK'

dno NUMBER --輸入?yún)?shù),雇員部門編號

)

IS

BEGIN

INSERT INTO employee

(empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);

END;

/*2.sql*plus下調(diào)用存儲過程*/

--EXEC add_employee(1111,'MARY',2000,'MANAGER',10);

--EXEC add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>1112, job=>'MANAGER');

--EXEC add_employee(1113,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');

--EXEC add_employee(1114,dno=>10,name=>'MARY',salary=>2000);

/*3.PL/SQL下調(diào)用存儲過程*/

BEGIN

--按位置傳遞參數(shù)

add_employee(2111,'MARY',2000,'MANAGER',10);

--按名字傳遞參數(shù)

add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>2112, job=>'MANAGER');

--混合方法傳遞參數(shù)

add_employee(3111,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');

--默認(rèn)值法

add_employee(4111,dno=>10,name=>'MARY',salary=>2000);

END;

總結(jié)

以上是生活随笔為你收集整理的oracle 同义词_【干货7】Oracle知识关键代码摘要的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。