【oracle】oracle經(jīng)典sql，exception，database link糾錯(cuò)

1111-01 oracle經(jīng)典sql，exception，database link糾錯(cuò)

1.給表table_a擴(kuò)展2個(gè)字段:studsex varchar2(4) 和 studage integer。
alter table table_a
add ( studsex varchar2(4)，studage integer );

2.建立一個(gè)視圖v_table,要求v_table是table_a和table_b的并集，當(dāng)字段studid,studname,depart一致時(shí)，而字段studscore不一致時(shí)，以table_a的學(xué)分字段值為準(zhǔn)。
create or replace view v_table as
select a.studid,a.studname,a.depart,a.studscore from table_a a
union all
select b.studid,b.studname,b.depart,b.studscore from table_b b
where not exists (select 1 from table_a aa where aa.studid=b.studid,aa.studname=b.studname,aa.depart=b.depart);

3.根據(jù)studid找出table_b中不存在table_a的數(shù)據(jù)。
select b.studid,b.studname,b.depart,b.studscore from table_b b where not exists (select 1 from table_a a where a.studid = b.studid);

?

4.幾種sql的效率
set autotrace on;

--寫法2 Plan Hash Value:1630949702
select a.studid from tanle_a a
minus
select b.studid from table_b b;

--寫法2 Plan Hash Value:4195831724
select a.studid from tanle_a a
where not exists (
select 1 from table_b b where b.studid = a.studid);

--寫法3 Plan Hash Value:
select a.studid from tanle_a a
where a.studif not in (
select b.studid from table_b b);

select least( 1630949702, 4195831724 , 4195831724 ) from dual;

——字符集
SIMPLEFIED CHINESE_CHINA
AMERICAN_AMERICA

AL32UTF8
ZHS16GBK

?

——CAP Theorem
一致性 Consistency
可用性 Availability
分區(qū)容忍性Partition Tolerance
CAP原理的意思是，一個(gè)分布式系統(tǒng)不能同時(shí)滿足一致性，可用性和分區(qū)容錯(cuò)性這三個(gè)需要，最多只能同時(shí)滿足兩個(gè)。

事務(wù)的ACID特性:
Atomic 原子性
Consistent 一致性
Isolation 隔離性
Durable 持久性

——set autotrace 查看autotrace的選項(xiàng)值有哪些
set auto[trace] {off | on |trace[only]} [exp[plain]] [stat[istics]]

——oracle修改表數(shù)據(jù)
select t*,t.rowid from <table> t;
select t* from <table> t for update;

——綁定變量 variable 與 define
ORACLR綁定變量用法總結(jié)
http://wenku.baidu.com/view/4ca502ea6f1aff00bed51eed.html?from=search

——oracle 數(shù)據(jù)類型 —— 3種集合類型
index-by表:不存儲(chǔ)在數(shù)據(jù)庫中，下標(biāo)連續(xù)
varray:存儲(chǔ)在數(shù)據(jù)庫中，下標(biāo)連續(xù)
嵌套表:存儲(chǔ)在數(shù)據(jù)庫中

——Oracle 異常 :exception
1.預(yù)定義內(nèi)部異常:ORA錯(cuò)誤碼已經(jīng)與異常名稱綁定
no_data_found
too_many_rows
dup_val_on_index
zero_divide
case_not_found
storage_error
value_error
cursor_already_open
timeout_on_resource
等 共21個(gè)
【例子1:自動(dòng)拋出】
set serveroutput on;
declare
v_name t_stu.name%type;
begin
select name into v_name from t_stu where serial_no > 5;
dbms_output.put_line('name='||v_name);
exception
when too_many_rows then
dbms_output.put_line('查詢返回了過多的行');
end;
/

【例子1:手動(dòng)拋出】
set serveroutput on;
declare
--v_name varchar2(30);
begin
--select name into v_name from t_stu where id < 1;
--dbms_output.put_line('v_name='||v_name);
raise no_data_found; --手動(dòng)拋出預(yù)定義的內(nèi)部異常
exception
when no_data_found then
dbms_output.put_out('no_data_found');
when others then
dbms_output.put_out('other');
end;
/

2.未預(yù)定義內(nèi)部異常:沒有異常名稱的ORA錯(cuò)誤碼
--將ORA錯(cuò)誤碼與異常名稱綁定 語法
pragma exception_init(<exception>,<sqlcode>);
【例子】ORA-00001 表示 違反唯一約束
set serveroutput on;
declare
uq_exception exception;
pragma exception_init(uq_exception,-1);
begin
insert into t_stu(id,name) values(1,'scott');
insert into t_stu(id,name) values(1,'scott');
commit;
exception
when uq_exception then
rollback;
dbms_output.put_line('違反唯一約束條件:sqlcode='||sqlcode||',sqlerrm='||substr(sqlerrm,1,200));
when others then
rollback;
dbms_output.put_line('sqlcode='||sqlcode||',sqlerrm='||substr(sqlerrm,1,200));
end;
/

?

3.自定義異常:需要編程者自己定義ORA錯(cuò)誤碼（即異常發(fā)生的條件），并與異常名稱綁定。
【例子】
綁定 自定義ORA錯(cuò)誤碼 與 自定義異常
拋出異常：oracle存儲(chǔ)過程 raise_application_error
語法：
raise_application_error(error_number,message[,true|false])
參數(shù) error_message:Oracle錯(cuò)誤碼范圍[-20000,-20999]
參數(shù) message:錯(cuò)誤信息是文本字符串，最多為2048字節(jié)（即2kb）
參數(shù) true|false:true表示 添加錯(cuò)誤堆（error_stack）;false表示 覆蓋錯(cuò)誤堆。默認(rèn)為false。

declare
my_exception exception;
pragma exception_init(my_exception,-20001);
message varchar2(4000);
errmsg varchar2(200);
begin
message := 'my_exception occured!';
raise_application_error(-20001,message,true); --手動(dòng)拋出自定義錯(cuò)誤碼
exception
when my_exception then
errmsg := substr(sqlerrm,1,200);
dbms_output.put_line(sqlcode||',||errmsg);
when others then
dbms_output.put_line('other exception occured!');
end;
/

說明:
異常發(fā)生后，跳轉(zhuǎn)到異常處理部分;異常處理后，程序不會(huì)返回到異常發(fā)生處的代碼，即異常發(fā)生處以下的代碼不會(huì)被執(zhí)行。

調(diào)試存儲(chǔ)過程
1.PL/SQL Developer
用于 創(chuàng)建，編譯 的調(diào)試
2.sql語句插入日志表
用于 過程的邏輯調(diào)試
3.命令 show errors
用于 創(chuàng)建，編譯，調(diào)用 的調(diào)試
/* plsql編譯警告*/
類型（3種）
severe:檢查 可能出現(xiàn)的不可預(yù)料錯(cuò)誤結(jié)果
performance:檢查 可能引起的 性能問題
informational:檢查 子程序中死代碼
all:檢查 所有警告

參數(shù) plsql_warnings
1)作用:使得數(shù)據(jù)庫在編譯子程序時(shí)發(fā)出警告信息
2)語法:
alter system|session|procedure <prc_name> set|compile plsql_warnings = 'enable|disable:severe|performance|informational|all';

使用 命名 show errors 查看具體的警告。

?

?

——oracle 的update語句
Oracle用一個(gè)表的列更新另一個(gè)表對(duì)應(yīng)的記錄
http://wenku.baidu.com/view/fb67b28f680203d8ce2f2483.html?from=search

?

——oracle partition補(bǔ)充
需求:
c1 number(12) 表示分種
c2 integer 數(shù)值總是[1,288]
在c1建立適當(dāng) 分區(qū)，在 c2 建立適當(dāng) 子分區(qū)。
--range-list
create table t_part_tab(
c1 number(12), --分種
c2 integer, --[1,288]
c3 varchar2(100)
) partition by range(c1) subpartition by list(c2)
subpartition template(
subpartition sp001 values(1),
subpartition sp002 values(2),
……
)
(
partition p20150101 values less than (201501012460),
partition p20150102 values less than (201501022460),
……
);

user_part_tables
user_tab_partitions
user_tab_subpartitions

select * from t_part_tab partition(p20150101);
select * from t_part_tab subpartition(p20150101_sp001);

——database link
grant create [public] database link to <user>;
grant drop database link to <user>; --否則只有dba用戶（system, sys）才能刪除 全局dblink

創(chuàng)建方式1:指定ip,port,SID
create [public] database link <dblink_name> connect to <user> identified by <pwd>
using '
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.137.150.243)(PORT = 1521)
)
(CONNECT_DATA = (SERVER_NAME = inomc)
)
)
)
';

創(chuàng)建方式2:指定TNS name
create [public] database link <dblink_name> connect to <user> identified by <pwd>
using '<tnsname>'; --<tnsname> 實(shí)在服務(wù)器端 $ORACLE_HOME/network/admin/tnsnames.ora文件中配置了

?

select * from <table>@<dblink>;
create synonym <synonym> for <table>@<dblink>;

1.創(chuàng)建
public和非public，普通用戶都需要授權(quán);DBA用戶（system,sys）都無需授權(quán)。
2.刪除
非public:無需授權(quán)，擁有者用戶，dba用戶 都可以刪除。
public:默認(rèn)只有dba用戶才有權(quán)限刪除，普通用戶需要授權(quán)。
3.使用
非public:僅 擁有者用戶，dba用戶 可以訪問。
public:同實(shí)例的所有用戶都可以訪問。

posted on 2018-04-05 11:58 綠Z 閱讀(...) 評(píng)論(...) 編輯 收藏

轉(zhuǎn)載于:https://www.cnblogs.com/greenZ/p/8721822.html

總結(jié)

以上是生活随笔為你收集整理的【oracle】oracle经典sql，exception，database link纠错的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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