{當你試圖解決一個你不了解的問題時,複雜化就産生了。}
1.ORACLE使用者解鎖+檢視是否被鎖
select * from USER_TABLES where table_name='CRM_BPM_MENU' --檢視是否被鎖
alter user ireport account unlock; --解鎖
2.Delete 表之後後悔？想恢複資料？（看這裡）
1、查詢資料庫目前時間（目的是為了檢查資料庫時間是否與你電腦時間相近，避免時間不同而将資料恢複到錯誤時間點）
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
2、查詢删除資料時間點之前的資料
select * from 表名 as of timestamp to_timestamp('2019-04-30 16:12:11','yyyy-mm-dd hh24:mi:ss');
（若沒有資料 ，将時間繼續提前）
3.-開啟用行移動功能
alter table 表名 enable row movement;
4、恢複資料
flashback table 表名 to timestamp to_timestamp('2019-04-30 16:12:11','yyyy-mm-dd hh24:mi:ss');
3.查詢重複資料
select *
from tmp_lyz_prod20190821 a where (a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE) in (
select a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE from tmp_lyz_prod20190821 a group by
a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*) > 1) and rowid not in (
select min(rowid) from tmp_lyz_prod20190821 a group by a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*)>1)
3.1 删除重複資料，保留一條
delete
--select *
from tmp_lyz_prod20190821 a where (a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE) in (
select a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE from tmp_lyz_prod20190821 a group by
a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*) > 1) and rowid not in (
select min(rowid) from tmp_lyz_prod20190821 a group by a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*)>1)
4.資料庫表空間使用情況
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
5.循環+LOOP的使用
BEGIN
FOR tmp_cur IN(select 1 as sk_date ,2 as idx from dual)
LOOP gv_filedate(tmp_cur.idx)=tmp_cur.sk_date
END LOOP;
FOR tmp_cur IN(select 2 as sk_date ,3 as idx from dual)
LOOP gv_filedate(tmp_cur.idx)=tmp_cur.sk_date
END LOOP;
END;
–取前幾
row_number() over(PARTITION BY b.sk_issue ORDER BY a.opdate DESC) AS row_no
6.生成測試腳本
select 'select '''||t.table_name||''','''||u.comments||''', COUNT(1) from '||t.table_name ||' union' from user_tables t
LEFT JOIN user_tab_comments u ON t.TABLE_NAME=u.TABLE_NAME
where t.table_name like 'WDB_AM_%' and t.num_rows>0
7.錯誤小彙總
ORA-00918: column ambiguously defined 未明确定義列
異常原因：select 查詢的字段在from的兩張表中都存在，導緻資料庫無法差別需要查詢的字段來自于哪張表
ORA-0131:Insufficient privileges.調用存儲過程測試。
Note:Debugging requires the DEBUG CONNECT SESSION system privileges.
解決辦法：以SYS使用者登入資料庫，執行賦權操作：
SQL> grant DEBUG CONNECT SESSION to user_name;
有網友指出還需賦予DEBUG ANY PROCEDURE的權限，經測試，該權限可不用賦予！
select * from role_sys_privs where privilege like ‘DEBUG%’ order by 2;–可以從資料字典role_sys_privs表檢視該權限相關資訊：
8.權限問題彙總
賦予權限
grant dba to xxx;
–删除權限
revoke dba from xxx;
–賦予使用者登入資料庫的權限
grant create session to xxx;
–授予使用者操作表的權限
grant unlimited tablespace to xxx;
grant create tablespace to xxx;
grant alter tablespace to xxx;
grant drop tablespace to xxx;
grant manage tablespace to xxx;
–撤銷使用者操作表空間的權限
revoke unlimited tablespace from xxx;
revoke create tablespace from xxx;
revoke alter tablespace from xxx;
revoke drop tablespace from xxx;
revoke manage tablespace from xxx;
grant create session to zhangsan;--授予zhangsan使用者建立session的權限，即登陸權限
grant unlimited session to zhangsan;--授予zhangsan使用者使用表空間的權限
grant create table to zhangsan;--授予建立表的權限
grant drop table to zhangsan;--授予删除表的權限
grant insert table to zhangsan;--插入表的權限
grant update table to zhangsan;--修改表的權限
grant select on tablename to zhangsan;--授予zhangsan使用者檢視指定表的權限
grant drop on tablename to zhangsan--授予删除表的權限
grant insert on tablename to zhangsan;--授予插入的權限
grant update on tablename to zhangsan;--授予修改表的權限
select * from user_sys_privs;--檢視目前使用者所有權限
select * from user_tab_privs;--檢視所用使用者對表的權限
9.存儲過程中緩沖過小
問題現象：ORA-06512:字元串緩沖區太小
是以在存儲過程或函數裡定義變量時最好使用【表名.字段名%type】的方式去定義，可以避免以後表字段變大時出現的異常問題。
10.截取固定字元前面的字元串
select substr('678910.SHD',1,instr('678910.SHD','.')-1) from dual --截取.前面的字段
select substr('678910.SHD',1,instr('678910.SHD','.')-1),instr('678910.SHD','.')-1,length(substr('678910.SHD',1,instr('678910.SHD','.')-1)) from dual
11.oracle資料庫備份
grant CREATE ANY DIRECTORY to infa; --賦權
select * from dba_directories; --查找全部
select * from all_directories where DIRECTORY_NAME = 'DIR_TEST'; --查找
create directory dumpdir as 'E:\temp';--建立
create directory dir_test as '/home/oracle/secooler';--建立
create or replace directory dumpdir as '/home/dumpfiles'; --修改
grant read,write on directory DUMPDIR to system;--賦權檔案
expdp infa/[email protected] directory=dir_test dumpfile=infa%date:~0,4%%date:~5,2%%date:~8,2%.DUMP schemas=infa
grant read,write on directory dir_test to system;
select * from dba_directories;
create or replace directory dumpdir as 'E:\temp'; --修改
expdp infa/[email protected] directory=dumpdir full=y dumpfile=infa.dmp logfile=fullexp.log;
expdp 'sys/system as sysdba' directory=dumpdir full=y dumpfile=fullexp.dmp logfile=fullexp.log;
WIND中快捷鍵：compmgmt.msc 定時計劃CMD
12.想取表中部分資料中加條件限制+不在這部分的資料
解決方案：使用or，就按照意思來寫
select * from table_name
where id>0 AND name like '%小_%' or id<=0
13.問題描述
ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源, 或者逾時失效 發生異常
原因：其他Session已經對目标表做了操作，且未送出操作，導緻鎖表，新的Session無法再對表進行DDL操作。
解決方法
Plan-A：等待原session執行完對表的操作，或commit對表的操作。
Plan-B：關閉原會話。
--查詢被鎖的會話ID
select session_id from v$locked_object;
--查詢結果：SESSION_ID-------9
--查詢上面會話的詳細資訊：
SELECT sid, serial#, username, osuser FROM v$session where sid = 9;
--查詢結果：serial#------99
--将上面鎖定的會話關閉：
ALTER SYSTEM KILL SESSION '9,99';
14.檢視執行的進度
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
15.檢視表在那個存儲過程中使用
--在進行字元串比較的時候，注意去空格
SELECT DISTINCT * FROM user_source
WHERE TYPE = 'PACKAGE BODY'
AND upper(text) LIKE '%FACT_PORT_ANALISIS%';
15.取表中按照某個字段排序并輸出前幾個TOP150
select * from (select * from ods.ctl_etlrun_log c order by c.log_serialno desc) where rownum<=150;
SELECT al.*
,rank() over(PARTITION BY al.cpdm ORDER BY nvl(al.cyje, 0) DESC) rn
FROM (SELECT cpdm,cyje from dual) al)
WHERE rn <= 200;
select * from issu_mkt_issue --投資品種
select * from prod_portfolio --套帳資訊
select * from dw.mid_voucher --憑證中間表
select * from dw.dim_product --産品資訊維
16.LEF JOIN 造成資料重複
Oracle中的left join查詢可能出現重複資料 （主表中一條變多條）一對多的情況
左表與右表LEFT JOIN，關聯條件時,關聯條件中右表資料是1：多的時候，造成資料重複
解決方案：将關聯條件放在GROUP BY 裡面，分組就可以了
17.Oracle substr()與substrb()
【參數】c1是字元串，n1是開始位置，n2是字元/節數
一個漢字兩個位元組，一個字元
SUBSTR(c1, n1) 從字元串中指定的開始位置，取得後面的字元串
SUBSTR(c1, n1, n2 ) 從字元串中指定的開始位置，取得指定字元數的字元串
SUBSTRB(c1 ,n1,n2 ) 從字元串中指定的開始位置，取得指定位元組數的字元串
從上面的就是可以看出substr()和substrb()的不同之處，在于substr()是取得字元串的字元數，substrb()是取得字元串的位元組數。
例如：
1)取得字元串“今天天氣很不錯”，第3個字元開始的字元串
select substr( '今天天氣很不錯' ,3) from dual;
輸出的結果為：天氣很不錯
2)取得字元串“今天天氣很不錯”，第3個位元組開始的字元串
select substrb( '今天天氣很不錯' ,3) from dual;
輸出的結果為：天天氣很不錯
3)取得字元串“今天天氣很不錯”，第3個字元開始2個字元的字元串
select substr( '今天天氣很不錯' ,3,2) from dual;
輸出的結果為：天氣
4)取得字元串“今天天氣很不錯”，第3個位元組開始2個位元組的字元串
select substrb( '今天天氣很不錯' ,3,2) from dual;
輸出結果為：天
注：當開始位置設定為負數時，是從字元串最後開始檢索的。
與這兩個函數類似的還有：
length與lengthb 長度計算函數
select length('天氣') from dual;
輸出：2
select lengthb('你好') from dual;
輸出：4
18.oracle截取某一個字元之前或之後的值；substr();instr()
函數介紹：
截取的函數：
substr(?,?); --要截取的字元串，開始的位置（包含），預設截取到末尾
substr(?,?,?); --要截取的字元串，開始的位置（包含），結束的位置（包含）
擷取目标字元出現的位置：
instr(? , ? , ? ); --要檢索的字元串，目标字元串，參數開始的位置
instr( ? , ? , ? , ? ) --要檢索的字元串，目标字元串，參數開始的位置，第幾次出現
select substr('AAA-BBB',1,instr('AAA-BBB','-',-1)-1) 值 from dual;--結果：AAA
select substr('AAA-BBB',instr('AAA-BBB','-',-1)+1) 值 from dual; --結果：BBB
19.建立同義詞
create synonym table_name for user.table_name;
grant select/delete/update on user2;--在user使用者上執行，将賦予user2使用者操作user表的權限
select * from dba_synonyms; --檢視同義詞的情況。
20.Oracle COALESCE函數
Oracle COALESCE函數文法為COALESCE(表達式1,表達式2,…,表達式n)，n>=2,此表達式的功能為傳回第一個不為空的表達式，如果都為空則傳回空值
注意：所有表達式必須為同一類型或者能轉換成同一類型。
21.Delete 表，恢複資料
一：根據時間來恢複：
1、查詢資料庫目前時間（目的是為了檢查資料庫時間是否與你電腦時間相近，避免時間不同而将資料恢複到錯誤時間點）
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
2、查詢删除資料時間點之前的資料
select * from 表名 as of timestamp to_timestamp(‘2019-04-30 16:12:11’,‘yyyy-mm-dd hh24:mi:ss’)；
（若沒有資料 ，将時間繼續提前）
3、恢複資料（激動人心的時刻）
flashback table 表名 to timestamp to_timestamp(‘2019-04-30 16:12:11’,‘yyyy-mm-dd hh24:mi:ss’);
大功告成，資料恢複成功；
但是也可能會出現問題，比如報錯：ORA-08189：未啟用行移動功能，不能閃回表；
不要怕，這個很簡單
alter table 表名 enable row movement;
然後再次執行上面SQL即可；
22.row_number()
現在需要按照課程對學生的成績進行排序：
--row_number() 順序排序（1,2,3,4,5,6）
select name,course,row_number() over(partition by course order by score desc) rank from student;
--rank() 跳躍排序，如果有兩個第一級别時，接下來是第三級别（1,2,2,4,5,6）
select name,course,rank() over(partition by course order by score desc) rank from student;
--dense_rank() 連續排序，如果有兩個第一級别時，接下來是第二級别 （1,2,2,3,4,5）
select name,course,dense_rank() over(partition by course order by score desc) rank from student;
TIPS:
使用rank over()的時候，空值是最大的，如果排序字段為null, 可能造成null字段排在最前面，影響排序結果。
可以這樣： rank over(partition by course order by score desc nulls last)
總結：
在使用排名函數的時候需要注意以下三點：
1、排名函數必須有 OVER 子句。
2、排名函數必須有包含 ORDER BY 的 OVER 子句。
3、分組内從1開始排序。
23. ora-01537
添加表空間檔案，添加的檔案名重複
ALTER TABLESPACE TBS_ODS ADD DATAFILE
--'D:\app\Administrator\product\11.2.0\oradata\dctest\tbs_ods_02.dbf'
'/oradata/dctest/tbs_ods_05.dbf'
SIZE 50M
AUTOEXTEND ON;-- NEXT 5M MAXSIZE 100M;
select tablespace_name from dba_tablespaces;
select * from dba_data_files;
23.1 檢視表空間使用情況
select b.file_id 檔案ID,
b.tablespace_name 表空間,
b.file_name 實體檔案名,
b.bytes 總位元組數,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩餘,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩餘百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
24.DECODE函數的用途
sign(value)函數會根據value的值為0，正數，負數，分别傳回0，1,-1
decode(條件,值1,傳回值1,值2,傳回值2,…值n,傳回值n,預設值)
DEcode的用途：
1.翻譯值、
decode(t.sex, '1', '男生', '2', '女生', '其他')
2.decode比較大小(利用sign)、
3.decode分段（利用sign）、
decode(sign(sal - 3000), 1, '中等', 0, '中等', -1, '低薪')
4.搜尋字元串（利用instr(t.name, '三')）
decode(instr(t.name, '三'), 0, '姓名不含有三', '姓名含有三')
5.判斷是否為空 decode(t.sex,NULL,'暫無資料',t.sex)
25.觸發器
觸發器的定義就是說某個條件成立的時候，觸發器裡面所定義的語句就會被自動的執行。
是以觸發器不需要人為的去調用，也不能調用。
在一個表中定義的語句級的觸發器，當這個表被删除時，程式就會自動執行觸發器裡面定義的操作過程。這個就是删除表的操作就是觸發器執行的條件了。
在一個表中定義了行級的觸發器，那當這個表中一行資料發生變化的時候，比如删除了一行記錄，那觸發器也會被自動執行了。
觸發器的文法：
create [or replace] tigger 觸發器名 觸發時間 觸發事件
on 表名
[for each row]
begin
pl/sql語句
end
觸發器名：觸發器對象的名稱。由于觸發器是資料庫自動執行的，是以該名稱隻是一個名稱，沒有實質的用途。
觸發時間：指明觸發器何時執行，該值可取：
before：表示在資料庫動作之前觸發器執行;
after：表示在資料庫動作之後觸發器執行。
觸發事件：指明哪些資料庫動作會觸發此觸發器：
insert：資料庫插入會觸發此觸發器;
update：資料庫修改會觸發此觸發器;
delete：資料庫删除會觸發此觸發器。
表 名：資料庫觸發器所在的表。
for each row：對表的每一行觸發器執行一次。如果沒有這一選項，則隻對整個表執行一次。
實作功能：
1、 允許/限制對表的修改
2、 自動生成派生列，比如自增字段
3、 強制資料一緻性
4、 提供審計和日志記錄
5、 防止無效的事務處理
6、 啟用複雜的業務邏輯
下面的觸發器在更新表tb_emp之前觸發，目的是不允許在周末修改表：
create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
IF(to_char(sysdate,'DY')='星期日') THEN
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
END IF;
END;
/
使用觸發器實作序号自增
1.建立一個測試表：
create table tab_user(
id number(11) primary key,
username varchar(50),
password varchar(50)
);
2.建立一個序列
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;
3.建立一個觸發器：
CREATE OR REPLACE TRIGGER MY_TGR
BEFORE INSERT ON TAB_USER
FOR EACH ROW--對表的每一行觸發器執行一次
DECLARE
NEXT_ID NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
:NEW.ID := NEXT_ID; --:NEW表示新插入的那條記錄
END;
4.向表插入資料:
insert into tab_user(username,password) values('admin','admin');
insert into tab_user(username,password) values('fgz','fgz');
insert into tab_user(username,password) values('test','test');
COMMIT;
5.測試-查詢表結果：SELECT * FROM TAB_USER;
當使用者對test表執行DML語句時，将相關資訊記錄到日志表
–建立測試表
CREATE TABLE test(
t_id NUMBER(4),
t_name VARCHAR2(20),
t_age NUMBER(2),
t_sex CHAR
);
–建立記錄測試表
CREATE TABLE test_log(
l_user VARCHAR2(15),
l_type VARCHAR2(15),
l_date VARCHAR2(30)
);
–建立觸發器
CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON TEST
DECLARE
V_TYPE TEST_LOG.L_TYPE%TYPE;
BEGIN
IF INSERTING THEN
--INSERT觸發
V_TYPE := 'INSERT';
DBMS_OUTPUT.PUT_LINE('記錄已經成功插入，并已記錄到日志');
ELSIF UPDATING THEN
--UPDATE觸發
V_TYPE := 'UPDATE';
DBMS_OUTPUT.PUT_LINE('記錄已經成功更新，并已記錄到日志');
ELSIF DELETING THEN
--DELETE觸發
V_TYPE := 'DELETE';
DBMS_OUTPUT.PUT_LINE('記錄已經成功删除，并已記錄到日志');
END IF;
INSERT INTO TEST_LOG
VALUES
(USER, V_TYPE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')); --USER表示目前使用者名
END;
/
–下面我們來分别執行DML語句
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
–然後檢視效果
SELECT * FROM test;
SELECT * FROM test_log;
建立觸發器，它将映射emp表中每個部門的總人數和總工資
–建立映射表
CREATE TABLE dept_sal AS
SELECT deptno, COUNT(empno) total_emp, SUM(sal) total_sal
FROM scott.emp
GROUP BY deptno;
–建立觸發器
CREATE OR REPLACE TRIGGER EMP_INFO
AFTER INSERT OR UPDATE OR DELETE ON scott.EMP
DECLARE
CURSOR CUR_EMP IS
SELECT DEPTNO, COUNT(EMPNO) AS TOTAL_EMP, SUM(SAL) AS TOTAL_SAL FROM scott.EMP GROUP BY DEPTNO;
BEGIN
DELETE DEPT_SAL; --觸發時首先删除映射表資訊
FOR V_EMP IN CUR_EMP LOOP
--DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
–插入資料
INSERT INTO DEPT_SAL
VALUES
(V_EMP.DEPTNO, V_EMP.TOTAL_EMP, V_EMP.TOTAL_SAL);
END LOOP;
END;
–對emp表進行DML操作
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
建立觸發器，用來記錄表的删除資料
–建立表
CREATE TABLE employee(
id VARCHAR2(4) NOT NULL,
name VARCHAR2(15) NOT NULL,
age NUMBER(2) NOT NULL,
sex CHAR NOT NULL
);
–插入資料
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
--建立記錄表(包含資料記錄)
CREATE TABLE old_employee AS SELECT * FROM employee;
–建立觸發器
CREATE OR REPLACE TRIGGER TIG_OLD_EMP
AFTER DELETE ON EMPLOYEE
FOR EACH ROW --語句級觸發，即每一行觸發一次
BEGIN
INSERT INTO OLD_EMPLOYEE VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.SEX); --:old代表舊值
END;
/
–下面進行測試
DELETE employee;
SELECT * FROM old_employee;
建立觸發器，利用視圖插入資料
–建立表
CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2));
CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30));
–插入資料
INSERT INTO tab1 VALUES(101,'zhao',22);
INSERT INTO tab1 VALUES(102,'yang',20);
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou');
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');
–建立視圖連接配接兩張表
CREATE OR REPLACE VIEW tab_view AS SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 WHERE tab1.tid = tab2.tid;
–建立觸發器
CREATE OR REPLACE TRIGGER TAB_TRIGGER
INSTEAD OF INSERT ON TAB_VIEW
BEGIN
INSERT INTO TAB1 (TID, TNAME) VALUES (:NEW.TID, :NEW.TNAME);
INSERT INTO TAB2 (TTEL, TADR) VALUES (:NEW.TTEL, :NEW.TADR);
END;
/
–現在就可以利用視圖插入資料
INSERT INTO tab_view VALUES(106,'ljq','13886681288','beijing');
–查詢
SELECT * FROM tab_view;
SELECT * FROM tab1;
SELECT * FROM tab2;
建立觸發器，比較emp表中更新的工資
–建立觸發器
set serveroutput on;
CREATE OR REPLACE TRIGGER SAL_EMP
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
IF :OLD.SAL > :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工資減少');
ELSIF :OLD.SAL < :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工資增加');
ELSE
DBMS_OUTPUT.PUT_LINE('工資未作任何變動');
END IF;
DBMS_OUTPUT.PUT_LINE('更新前工資 ：' || :OLD.SAL);
DBMS_OUTPUT.PUT_LINE('更新後工資 ：' || :NEW.SAL);
END;
/
–執行UPDATE檢視效果
UPDATE emp SET sal = 3000 WHERE empno = '7788';
建立觸發器，将操作CREATE、DROP存儲在log_info表
–建立表
CREATE TABLE log_info(
manager_user VARCHAR2(15),
manager_date VARCHAR2(15),
manager_type VARCHAR2(15),
obj_name VARCHAR2(15),
obj_type VARCHAR2(15)
);
–建立觸發器
set serveroutput on;
CREATE OR REPLACE TRIGGER TRIG_LOG_INFO
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO LOG_INFO
VALUES
(USER,
SYSDATE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE);
END;
/
–測試語句
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;
–檢視效果
SELECT * FROM log_info;
–相關資料字典
SELECT * FROM USER_TRIGGERS;
–必須以DBA身份登陸才能使用此資料字典
SELECT * FROM ALL_TRIGGERS;SELECT * FROM DBA_TRIGGERS;
–啟用和禁用
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;
26.根據存儲過程名稱導出
SET echo off ;
SET heading off ;
SET feedback off ;
SPOOL 'D:/DC_REOPRT/PRC_REPORT_NEW_CISP.SQL' replace
SELECT CASE
WHEN LINE = 1 THEN
'CREATE OR REPLACE ' || TEXT
WHEN LINE = MAX_LINE THEN
TEXT || CHR(10 ) || '/'
ELSE
TEXT
END
FROM USER_SOURCE A
LEFT JOIN (SELECT A.NAME,A.TYPE, MAX(LINE) MAX_LINE
FROM USER_SOURCE A
WHERE TYPE in ( 'PROCEDURE', 'PACKAGE' ,'PACKAGE BODY' )
GROUP BY A.NAME,A.TYPE ) B ON A.NAME||A.TYPE = B.NAME||B.TYPE
WHERE A.TYPE in ('PROCEDURE' , 'PACKAGE', 'PACKAGE BODY' ) AND A.NAME IN
(
'PKG_WDB_AM_PROD_BASEINFO'
)
ORDER BY a.NAME||a.TYPE , LINE;
SPOOL OFF
上面内容命名為export_cisp.sql，
注意:
檔案名：D:/DC_REOPRT/PRC_REPORT_NEW_CISP.SQL
存儲過程名： 'PKG_WDB_AM_PROD_BASEINFO'
使用:在wind下CMD小窗體執行下面指令：
1.登陸
sqlplus 使用者名/密碼@庫位址
2.執行
@D:\DC_REOPRT\export_cisp.sql
27.exp指令導入導出
1.plsql developer導出工具那自己選對應的表
2.exp指令指定對應的表
3.dbms_metadata.get_ddl
自己寫sql從user_tab_columns這些系統字典表去拼
1.略
2.使用EXP
導出表資料以及表結構（以Oracle11g為例）指令行：
exp 使用者名/密碼@資料庫名字 owner=使用者名?file=dmp檔案存儲位置（即導出檔案為.dmp檔案）
導入表資料以及表結構（以Oracle11g為例）指令行：
imp 使用者名/密碼@資料庫名稱?fromuser（檔案的指定使用者，即從那個使用者導入資料）=使用者名 touser（目标使用者，即導入到目前登入的那個使用者）=使用者名 file=要導入的dmp檔案位置 ignore=y（忽略錯誤建立）
3.使用dbms_metadata.get_ddl
select dbms_metadata.get_ddl('TABLE','TMP_TRD_FA_VCH_TRAN','ODS') from dual
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
28.base64編碼與解碼 JAVA
Java 8的java.util套件中，新增了Base64的類别，可以用來處理Base64的編碼與解碼，用法如下：
final Base64.Decoder decoder = Base64.getDecoder();
final Base64.Encoder encoder = Base64.getEncoder();
final String text = "字串文字";
final byte[] textByte = text.getBytes("UTF-8");
//編碼
final String encodedText = encoder.encodeToString(textByte);
System.out.println(encodedText);
//解碼
System.out.println(new String(decoder.decode(encodedText), "UTF-8"));
【記錄時間】：2019-09-25 18:17
【記錄者】：菜萌
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)