查詢
SELECT COUNT(*) FROM v$session;
SELECT COUNT(*) FROM v$process;
SELECT value FROM v$paramter WHERE name = 'processes';
SELECT COUNT(*) FROM v$session;
SELECT COUNT(*) FROM v$process;
SELECT value FROM v$paramter WHERE name = 'processes';
- 7月 22 週日 201203:12
Session (Oracle)
- 7月 22 週日 201203:06
小數點 Format
TO_CHAR(0.56, '999990.9') = 0.6
TO_CHAR(0, '999990.9') = 0.0
ROUND(0.56, 1) = 0.6 --還有 floor 無條件捨去
ROUND(0, 1) = 0.0
TO_CHAR(0, '999990.9') = 0.0
ROUND(0.56, 1) = 0.6 --還有 floor 無條件捨去
ROUND(0, 1) = 0.0
- 4月 07 週六 201216:38
String Split Function
create or replace type array_type as varray(2000) of varchar2(1000);
- 3月 27 週二 201217:36
oracle snapshot
connect string需加在DB Server的中
- 3月 27 週二 201217:30
計算名次
Grade_table
SUBJECT
STUDENT_ID
GRADE
Chemistry
1
60
Chemistry
2
65
Mathematics
1
80
Mathematics
2
85
Mathematics
3
90
Mathematics
4
95
Mathematics
5
100
Physics
1
85
Physics
2
90
Geography
1
95
- 3月 27 週二 201217:10
oracle計算自然週數(week of year)
create or replace function GetWeekOfYear(dt Timestamp)
return number
is
weekOfYear number;
begin
select ceil
(
(
to_number(to_char(dt, 'DDD')) -
(8 - to_char(trunc(dt, 'YYYY'), 'D'))
) / 7 + 1
) into weekOfYear from dual;
return weekOfYear;
end GetWeekOfYear;
return number
is
weekOfYear number;
begin
select ceil
(
(
to_number(to_char(dt, 'DDD')) -
(8 - to_char(trunc(dt, 'YYYY'), 'D'))
) / 7 + 1
) into weekOfYear from dual;
return weekOfYear;
end GetWeekOfYear;
- 3月 09 週五 201222:43
Oracle基本程式語法
1. 變數用法
varName1 := 0; --將varName1設為零
varName1 := varName1 + 1; --將varName1+1
varName2 := paramter1;
varName1 := 0; --將varName1設為零
varName1 := varName1 + 1; --將varName1+1
varName2 := paramter1;
- 3月 09 週五 201222:17
查詢DB執行中的物件與刪除Session
查詢DB執行中的物件:
SELECT *
FROM v$db_object_cache
WHERE locks > 0 AND pins > 0 AND type = 'PROCEDURE' --type = 查看哪種類型物件
SELECT *
FROM v$db_object_cache
WHERE locks > 0 AND pins > 0 AND type = 'PROCEDURE' --type = 查看哪種類型物件
- 3月 05 週一 201223:10
left join 和 left outer join 的區別【轉】
Sale_table
ID
Sale
a
5
b
6
c
7
a
8
DATABASES (3)