oracle 表 視圖 存儲過程 序列 job

table 表

--delete table

drop?table?Test1;

--?Create?table

create?table?TEST1

(

ID?????NUMBER,

T_NAME?VARCHAR2(100),

DT?????DATE

);

-- 添加注釋

comment?on?column?TEST1.T_NAME?is?'名稱';

--添加age字段

alter?table?Test1?add?(age??NUMBER(8));

--刪除字段

alter?table?TABLE_NAME?drop?column?COLUMN_NAME;

--修改字段數據類型

alter?table?tablename?modify?filedname?varchar2(20);

---

view 視圖

---刪除視圖

drop?view?v_viewName;

---創建視圖

create?or?replace?view?v_viewName

as

select * from tableName

with?read?only;

---

sequence 序列

create?sequence?RUN_LOG_SEQ

minvalue?1

maxvalue?9999999999999999999999999999

start?with?1--從1開始計數

increment?by?1--每次加1

cache?20;

使用run_log_seq.nextval

---

index 索引

---刪除索引

drop index?indexName?

---創建索引

create?index?indexName?ON?tableName?(columnName);

---

procedures 存儲過程

--定義參數

CREATE?OR?REPLACE?PROCEDURE?pro_test(yyyy_mm_s?date,yyyy_mm_e?date)?IS

var_b_time?DATE;--定義變量?

BEGIN

--游標循環

for?v?in?(

select?sysdate?t?from?DUAL

)?loop

--業務處理...

var_b_time:=v.t;

end?loop;

--提交

commit;

END;

---

funcations 函數

--par_master_bill_id參數

CREATE?OR?REPLACE?FUNCTION?get_testFunction(par_master_bill_id?VARCHAR2)?

RETURN?varchar2?IS

PRAGMA?AUTONOMOUS_TRANSACTION;

ret_value????VARCHAR2(100);--返回值

BEGIN

select?sysdate||par_master_bill_id?into?ret_value?from?DUAL;

RETURN?ret_value;

END?get_testFunction;--函數名結束

使用如下：

select?get_testFunction('hello')?from?DUAL;

---

job 定時任務

delete?hk_amast_list;

--查詢所有定時器

SELECT?*?FROM?ALL_JOBS;

--刪除job

begin

dbms_job.remove(9);--先查詢定時器id

end;

--創建定時器

declare?

job?number;?

begin?

dbms_job.submit(job,?'p_amastList;',?sysdate,?'TRUNC(SYSDATE+1)+(1*60+30)/(24*60)');?

end

commit;

select?t.*?from?t_test?t;

--'TRUNC(SYSDATE+1)+(1*60+30)/(24*60)'?每天凌晨1:30執行一次

--例如：每天的凌晨2點執行Interval?=>?TRUNC(sysdate)?+?1?+2?/?(24)

--每天早上8點30分?'TRUNC(SYSDATE?+?1)?+?(8*60+30)/(24*60)'

--每分鐘執行一次'TRUNC(sysdate,''mi'')+1/(24*60)'

---

merge

merge?into?TEST2?t2

using?(

select?t1.id,t1.t_name,t1.dt?from?TEST1?t1

)?t1?on(t2.id=t1.id?and?t2.t_name=t1.t_name)

when?matched?then

update?set?t2.t_name=t1.t_name,t2.dt=t1.dt

when?not?matched?then

insert?(id,t_name,dt)

values(t1.id,t1.t_name,t1.dt);

commit;

?

關注我的微信共享學習,討論更多技術知識

?個人網站:https://www.liyuan3210.com