PL/SQL
1.刪除表/序列號/同義詞/視圖
DROP TABLE/SEQUENCE/PUBLIC SYNONYM/VIEW XXX
2.創建表
CREATE TABLE XXX(
id NUMBER,
account VARCHAR2(30) NOT NULL ,
created_date DATE DEFAULT SYSDATE ,
CONSTRAINT pk_XXX PRIMARY KEY (id)
)
TABLESPACE AAA
3.ALTER
ALTER TABLE XXX ADD CONSTRAINT pk_XXX PRIMARY KEY (id)
ALTER TABLE XXX ADD CONSTRAINT fk_XXX FOREIGN KEY (account)
REFERENCES YYY (id)
ON DELETE SET NULL/CASCADE
ALTER TABLE XXX ADD sal NUMBER(8,2)
ALTER TABLE XXX MODIFY sal NUMBER(9,2)
4.函數
DECODE (dname,'資訊課','DDDFD','財務課','DDDDD')，ROUND((NVL((SYSDATE -hiredate),0))/365,2)，TRUNC(sal,1)，
LOWER(ename)，UPPER(ename)，INITCAP(ename)，CONCAT(ename,sal)，SUBSTR(hiredate,2,1)，LENGTH(hiredate)，MOD(sal,1)，
MONTHS_BETWEEN(SYSDATE-30,SYSDATE)，ADD_MONTHS(SYSDATE,1)，NEXT_DAY(SYSDATE,1)，LAST_DAY(SYSDATE)，
TO_CHAR(SYSDATE,'yyyy/MM/dd HH24:mi:ss') ,TO_NUMBER('11111'),TO_DATE('20200101','yyyyMMdd HH24:mi:ss')，
INSTR('JKKKK','K') ，INSTR('JKKKK','K',3,2)，CEIL(1.34),FLOOR(3.34)，REPLACE('SWHH','H','h') ，
5.創建別名
COMMENT ON TABLE/COLUMN XXX/XXX.字段名 IS ‘別名’
6.創建索引/唯一索引
CREATE INDEX/UNIQUE INDEX XXCC_SWH_TEST_N1 ON XXCC_SWH_TEST
(
ename ASC,
hiredate ASC--重複率較少的字段放下面
)
7.創建序列號
CREATE SEQUENCE XXX
INCREMENT BY 1
START WITH 0
MINVALUE 0
MAXVALUE 100--最大可以9999
CYCLE
NOORDER
CACHE 20
8.創建視圖
CREATE VIEW v_XXX
AS select......
9.游標
--顯式游標
--傳入部門號，返回總薪資，年資>0,+1000
CREATE OR REPLACE FUNCTION xx_z10_f(f_deptno NUMBER )
RETURN VARCHAR2 IS
v_sal_total NUMBER ;
v_sal NUMBER ;
v_years NUMBER ;
v_return VARCHAR2(50);
CURSOR c IS
SELECT NVL (sal,0) sal ,ROUND (NVL((SYSDATE -hiredate )/365,0),1) years
FROM xxcc_swh_test
WHERE deptno=f_deptno;
BEGIN
v_sal_total:=0;
OPEN c;
LOOP
FETCH c INTO v_sal,v_years;
EXIT WHEN c%NOTFOUND;
IF v_years>0 THEN
v_sal_total:=v_sal_total+v_sal+1000;
ELSE
v_sal_total:=v_sal_total+v_sal;
END IF ;
END LOOP ;
CLOSE c;
v_return:='部門號為'||f_deptno||'的全體員工總工資：'||v_sal_total;
RETURN v_return;
END ;
10.觸發器
CREATE OR REPLACE TRIGGER xx_z10_tr
BEFORE
DELETE OR INSERT OR UPDATE
ON xxcc_swh_test
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_tip VARCHAR2(20);
BEGIN
IF DELETING THEN
UPDATE xxcc_swh_test1 SET loc=UPPER(loc)
WHERE loc='cc';
v_tip:='del觸發成功';
END IF ;
IF INSERTING THEN
UPDATE xxcc_swh_test1 SET loc =LOWER(loc)
WHERE loc='cc';
v_tip:='ins觸發成功';
END IF ;
IF :NEW.sal<>:OLD.sal THEN --updating(sal)
UPDATE xxcc_swh_test1 SET loc=UPPER(loc)
WHERE dname='財務課';
v_tip:='upd sal觸發成功';
END IF ;
IF UPDATING THEN
UPDATE xxcc_swh_test1 SET loc=CONCAT(UPPER(loc),loc)
WHERE dname='財務課';
v_tip:='upd觸發成功';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
11.存儲過程（無參）
--員工年資超過0.1年者加薪1000元 （存儲過程，無參）
DECLARE
CURSOR c IS
SELECT empno,ROUND (NVL( (SYSDATE-hiredate)/365,0),1) years,sal
FROM xxcc_swh_test;
BEGIN
--隱式游標
FOR c1 IN c LOOP
IF c1.years>0 THEN
UPDATE xxcc_swh_test SET sal=sal+1000 WHERE empno=c1.empno;
ELSE
UPDATE xxcc_swh_test SET sal=sal WHERE empno=c1.empno;
END IF;
END LOOP ;
END ;
12.存儲過程（有參）（簽核流程）
1 CREATE OR REPLACE PROCEDURE xx_remoteconnect_flow_p(p_application_id VARCHAR2,p_account VARCHAR2,p_comments VARCHAR2 DEFAULT '.',p_undertakes VARCHAR2 DEFAULT '無') 2 IS 3 v_exception EXCEPTION; 4 v_flow xx_remoteconnect_apply_flow%ROWTYPE; 5 v_flow_count NUMBER ; 6 v_person VARCHAR2(30) DEFAULT '申請者OR承辦者'; 7 v_updated_by VARCHAR2(30) DEFAULT '誰修改的流程記錄'; 8 v_created_by VARCHAR2(30) DEFAULT '誰創建的流程記錄'; 9 v_flag NUMBER; 10 CURSOR c1(P_BOSS_ACCOUNT VARCHAR2) IS -----一階 11 SELECT DEPT,CHINESE_NAME,ACCOUNT,TITLE FROM USER_INFO_N 12 WHERE ACCOUNT= (SELECT 13 CASE WHEN A.ACCOUNT = B.BOSS_ACCOUNT THEN 14 (SELECT BOSS_ACCOUNT FROM DEPARTMENT WHERE CODE = B.PARENT_CODE) 15 ELSE 16 B.BOSS_ACCOUNT 17 END AS BOSS_ACCOUNT 18 FROM USER_INFO_N A ,DEPARTMENT B 19 WHERE A.DEPT = B.CODE 20 AND A.JOB_STATUS='employed' 21 AND A.ACCOUNT = P_BOSS_ACCOUNT 22 AND ROWNUM=1 ) 23 AND JOB_STATUS='employed' 24 AND TITLE NOT LIKE '%總經理' 25 AND TITLE NOT LIKE '%廠長' 26 AND ROWNUM=1; 27 ------------------------------- 28 CURSOR C2(P_BOSS_ACCOUNT VARCHAR2) IS-----二階 29 SELECT DEPT,CHINESE_NAME,ACCOUNT,TITLE FROM USER_INFO_N 30 WHERE ACCOUNT= (SELECT 31 CASE WHEN A.ACCOUNT = B.BOSS_ACCOUNT THEN 32 (SELECT BOSS_ACCOUNT FROM DEPARTMENT WHERE CODE = B.PARENT_CODE) 33 ELSE 34 B.BOSS_ACCOUNT 35 END AS BOSS_ACCOUNT 36 FROM USER_INFO_N A ,DEPARTMENT B 37 WHERE A.DEPT = B.CODE 38 AND A.JOB_STATUS='employed' 39 AND A.ACCOUNT = P_BOSS_ACCOUNT 40 AND ROWNUM=1 ) 41 AND JOB_STATUS='employed' 42 AND TITLE NOT LIKE '%總經理' 43 AND TITLE NOT LIKE '%廠長' 44 AND ROWNUM=1; 45 ------------------------------- 46 CURSOR C3(p_role_seq VARCHAR2) IS-----查詢相鄰兩角色 47 SELECT role_seq,role_name 48 FROM xx_remoteconnect_apply_role 49 WHERE role_seq =p_role_seq OR role_seq=TO_CHAR(TO_NUMBER(p_role_seq)+10) 50 ORDER BY role_seq ; 51 BEGIN 52 v_flag:=1; 53 SELECT COUNT (flow_num) 54 INTO v_flow_count 55 FROM xx_remoteconnect_apply_flow 56 WHERE application_id=p_application_id; 57 58 IF v_flow_count = 0 THEN 59 v_flow.flow_num:='1'; 60 v_flow.role_seq:='10'; 61 v_flow.sign_result:='開單'; 62 ELSE 63 SELECT flow_num,role_seq,sign_result 64 INTO v_flow.flow_num,v_flow.role_seq,v_flow.sign_result 65 FROM xx_remoteconnect_apply_flow 66 WHERE application_id=p_application_id 67 AND flow_num= ( 68 SELECT NVL(TO_CHAR(MAX(TO_NUMBER(flow_num))),'0') 69 FROM xx_remoteConnect_apply_flow 70 WHERE application_id=p_application_id 71 ); 72 IF v_flow.sign_result = '開單' THEN 73 v_flow.sign_result:='已經開單' ; 74 END IF ; 75 IF SQL%NOTFOUND THEN 76 RAISE v_exception; 77 END IF ; 78 END IF ; 79 80 dbms_output.Put_line('當前流程記錄的序號和角色序號和審核結果：v_flow.flow_num:'||v_flow.flow_num||'，v_flow.role_seq:'||v_flow.role_seq||'v_flow.sign_result:'||v_flow.sign_result); 81 IF v_flow.role_seq = '10' THEN--申請者申請 82 FOR c3_rec IN c3( v_flow.role_seq) LOOP 83 IF c3_rec.role_seq = v_flow.role_seq AND v_flow.sign_result ='開單' THEN 84 SELECT dept||'-'||chinese_name||title 85 INTO v_flow.action_klw 86 FROM USER_INFO_N 87 WHERE account=p_account ; 88 89 INSERT INTO xx_remoteConnect_apply_flow(application_id,flow_num,role_seq,role_name,sign_result,Comments,action_klw,created_by,created_date,action_klw_account) 90 VALUES (p_application_id,v_flow.flow_num,c3_rec.role_seq,c3_rec.role_name,v_flow.sign_result,p_comments,v_flow.action_klw,p_account,SYSDATE,p_account); 91 ELSIF c3_rec.role_seq <> v_flow.role_seq AND v_flow.sign_result ='已經開單' THEN 92 FOR c1_rec IN c1(p_account) LOOP 93 v_flag:=0; 94 INSERT INTO xx_remoteConnect_apply_flow(application_id,flow_num,role_seq,role_name,sign_result,Comments,action_klw,created_by,created_date,action_klw_account) 95 VALUES (p_application_id,TO_CHAR(TO_NUMBER(v_flow.flow_num)+1),c3_rec.role_seq,c3_rec.role_name,'尚未審核',p_comments,c1_rec.dept||'-'||c1_rec.chinese_name||c1_rec.title,p_account,SYSDATE,'xdomain\wenhaosu');--c1_rec.account 96 END LOOP ; 97 IF v_flag = 1 THEN 98 SELECT dept||'-'||chinese_name||title,b.boss_account 99 INTO v_flow.action_klw,v_flow.action_klw_account 100 FROM USER_INFO_N a,DEPARTMENT b 101 WHERE b.code LIKE '%Z50' AND 102 UPPER(a.account) = UPPER(b.boss_account) 103 AND title NOT LIKE '%主任' 104 AND ROWNUM = 1; 105 v_created_by:=p_account; 106 INSERT INTO xx_remoteConnect_apply_flow(application_id,flow_num,role_seq,role_name,sign_result,Comments,action_klw,created_by,created_date,action_klw_account) 107 VALUES (p_application_id,TO_CHAR(TO_NUMBER(v_flow.flow_num)+1),TO_CHAR(TO_NUMBER(c3_rec.role_seq+20)),'Z50 課長','尚未審核','.',v_flow.action_klw,v_created_by,SYSDATE,'xdomain\wenhaosu');--v_flow.action_klw_account 108 END IF ; 109 END IF; 110 END LOOP ; 111 ELSIF v_flow.role_seq <> '10' THEN--階級審核 112 IF p_undertakes = '無' THEN 113 v_person:=p_account; 114 ELSE 115 v_person:=p_undertakes; 116 END IF ; 117 FOR c1_rec IN c1(v_person) LOOP 118 FOR c3_rec IN c3( v_flow.role_seq) LOOP 119 IF c3_rec.role_seq = v_flow.role_seq THEN--流程記錄中的當前角色 120 IF v_flow.role_seq = '30' OR v_flow.role_seq = '50' THEN 121 FOR c2_rec IN c2(c1_rec.account) LOOP 122 v_updated_by:= c2_rec.account; 123 END LOOP ; 124 ELSIF v_flow.role_seq ='60' THEN 125 v_updated_by:= p_undertakes; 126 ELSE 127 IF v_person='xdomain\zhiliangli' OR v_person='xdomain\zekangyao' OR v_person='xdomain\saihuali' THEN 128 SELECT BOSS_ACCOUNT 129 INTO v_updated_by 130 FROM department 131 WHERE CODE = 'FSZ50' 132 AND ROWNUM = 1; 133 ELSE 134 v_updated_by:= c1_rec.account; 135 END IF ; 136 END IF ; 137 dbms_output.Put_line('當前流程記錄：v_updated_by:'||v_updated_by); 138 139 UPDATE xx_remoteConnect_apply_flow SET Comments= p_comments, sign_result='同意',updated_by=v_updated_by,updated_date=SYSDATE 140 WHERE application_id= p_application_id AND flow_num=v_flow.flow_num ; 141 ELSE --流程記錄中的下一角色 142 FOR c2_rec IN c2(c1_rec.account) LOOP 143 IF v_flow.role_seq = '30' THEN 144 SELECT dept||'-'||chinese_name||title,b.boss_account 145 INTO v_flow.action_klw,v_flow.action_klw_account 146 FROM USER_INFO_N a,DEPARTMENT b 147 WHERE b.code LIKE '%Z50' AND 148 UPPER(a.account) = UPPER(b.boss_account) 149 AND title NOT LIKE '%主任' 150 AND ROWNUM = 1; 151 152 v_created_by:=c2_rec.account; 153 ELSIF v_flow.role_seq = '50' THEN 154 SELECT dept||'-'||chinese_name||title, account 155 INTO v_flow.action_klw,v_flow.action_klw_account 156 FROM USER_INFO_N 157 WHERE account=p_undertakes ; 158 159 v_created_by:=c2_rec.account; 160 ELSIF v_flow.role_seq = '60' THEN 161 IF v_person='xdomain\zhiliangli' OR v_person='xdomain\zekangyao' OR v_person='xdomain\saihuali' THEN 162 SELECT dept||'-'||chinese_name||title,b.boss_account 163 INTO v_flow.action_klw,v_flow.action_klw_account 164 FROM USER_INFO_N a,DEPARTMENT b 165 WHERE b.code LIKE '%Z50' AND 166 UPPER(a.account) = UPPER(b.boss_account) 167 AND title NOT LIKE '%主任' 168 AND ROWNUM = 1; 169 ELSE 170 v_flow.action_klw_account:=c1_rec.account; 171 v_flow.action_klw:=c1_rec.dept||'-'||c1_rec.chinese_name||c1_rec.title; 172 END IF ; 173 v_created_by:=p_undertakes; 174 ELSE 175 v_flow.action_klw_account:= c2_rec.account ; 176 v_flow.action_klw:=c2_rec.dept||'-'||c2_rec.chinese_name||c2_rec.title; 177 IF v_person='xdomain\zhiliangli' OR v_person='xdomain\zekangyao' OR v_person='xdomain\saihuali' THEN 178 SELECT BOSS_ACCOUNT 179 INTO v_created_by 180 FROM department 181 WHERE CODE = 'FSZ50' 182 AND ROWNUM = 1; 183 ELSE 184 v_created_by:=c1_rec.account; 185 END IF ; 186 187 dbms_output.Put_line( c2_rec.account ); 188 END IF ; 189 dbms_output.Put_line('下一個流程記錄：v_flow.action_klw:'||v_flow.action_klw||'，v_created_by:'||v_created_by); 190 v_flag:=0; 191 INSERT INTO xx_remoteConnect_apply_flow(application_id,flow_num,role_seq,role_name,sign_result,Comments,action_klw,created_by,created_date,action_klw_account) 192 VALUES (p_application_id,TO_CHAR(TO_NUMBER(v_flow.flow_num)+1),c3_rec.role_seq,c3_rec.role_name,'尚未審核','.',v_flow.action_klw,v_created_by,SYSDATE,'xdomain\wenhaosu');--v_flow.action_klw_account 193 END LOOP ; 194 IF v_flag = 1 THEN 195 SELECT dept||'-'||chinese_name||title,b.boss_account 196 INTO v_flow.action_klw,v_flow.action_klw_account 197 FROM USER_INFO_N a,DEPARTMENT b 198 WHERE b.code LIKE '%Z50' AND 199 UPPER(a.account) = UPPER(b.boss_account) 200 AND title NOT LIKE '%主任' 201 AND ROWNUM = 1; 202 v_created_by:=c1_rec.account; 203 INSERT INTO xx_remoteConnect_apply_flow(application_id,flow_num,role_seq,role_name,sign_result,Comments,action_klw,created_by,created_date,action_klw_account) 204 VALUES (p_application_id,TO_CHAR(TO_NUMBER(v_flow.flow_num)+1),TO_CHAR(TO_NUMBER(c3_rec.role_seq+10)),'Z50 課長','尚未審核','.',v_flow.action_klw,v_created_by,SYSDATE,'xdomain\wenhaosu');--v_flow.action_klw_account 205 END IF ; 206 END IF ; 207 END LOOP ; 208 END LOOP ; 209 END IF ; 210 EXCEPTION 211 WHEN v_exception THEN 212 dbms_output.Put_line('v_exception:::當前流程記錄的序號和角色序號：v_flow.flow_num:'||v_flow.flow_num||'，v_flow.role_seq:'||v_flow.role_seq); 213 WHEN OTHERS THEN 214 dbms_output.put_line(SQLCODE||':'||SQLERRM); 215 END;
--查詢主管
select a.account,a.chinese_name,a.dept,a.tel,substr(a.account,9)||'@compeq.huizhou.gd.cn;' email
from user_info_n a,
(
select code,parent_code,boss_account
from department
where level<=5
START WITH code = 'SHZ10'
CONNECT BY PRIOR parent_code=code
) b
where a.account = b.boss_account;
--分頁查詢
SELECT *
FROM (SELECT ROWNUM nums,xx_emp_info.* FROM xx_emp_info)
WHERE nums > 5 AND nums <= 10
--去除重複數據
SELECT * FROM material
WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM material GROUP BY PARTNO)
判断其是否等于某天：
1.如果Check字段不带时间，只是年月日：
Where CheckDate ='2013-01-01'
2.如果CheckDate字段是携带时间（CheckDate 实际存储值可能是: 20130101 08:50:54:000 或 20130101 22:50:54:000）：
Where CheckDate >= '2013-01-01' And CheckDate < '2013-01-02'
3.把'2013-01-01'，定义为参数@Date DateTime
Where CheckDate >= @Date And CheckDate < @Date+1
