1. google地图是如何用oracle进行图片的存储的
1）创建一个t表：
create table t (a varchar2(20),b blob);
2）写一个存储过程：
CREATE OR REPLACE PROCEDURE think (p_1 VARCHAR2, p_2 VARCHAR2 , p_3 varchar2 )
AS
v_f BFILE;
v_b blob;
BEGIN
INSERT INTO t values( p_1, EMPTY_BLOB () ) RETURN b into v_b; -- return B (field) into v_b
v_f := BFILENAME (p_2,p_3);
DBMS_LOB.FILEOPEN (v_f, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (v_b, v_f, DBMS_LOB.GETLENGTH (v_f));
DBMS_LOB.FILECLOSE (v_f);
commit;
END;
3）sys用户创建directory对象并授权：
create directory dir as 'C:\Users\Administrator\';
目录已创建。
grant all on directory dir to hr;
4）执行存储过程
begin
think( 'This is my pic 1', 'DIR', 'psu.jpg' );
end;
5）查询（基于pl/sql查询）
select * from t
2. oracle的left join，right join和full join的一点介绍
select * from a; select * from b;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 a 1 a
2 b 2 b
3 c 4 d
select a.id,b.id from a FULL JOIN b on a.id=b.id;
ID ID
---------- ----------
1 1
2 2
3
4
--left out join和oracle的加号在右结果是相同。同理，right out join和加号在左是一样的。
select a.id,b.id from a,b where a.id=b.id(+);
ID ID
---------- ----------
1 1
2 2
3
select a.id,b.id from a LEFT OUTER JOIN b on a.id=b.id;
ID ID
---------- ----------
1 1
2 2
3
1.1 内连接（inner join）
Oracle 的join连接默认就是inner join，所以在写内连接时可以把inner省略， 这种连接返回的是两表交集的部分，即如果表中至少有一行匹配则但会行。可以用下面蓝色部分表示
SELECT * FROM A INNER JOIN B ON A.ID=B.ID
SELECT * FROM A JOIN B ON A.ID=B.ID
SELECT *FROM A,B WHERE A.ID=B.ID
以上语句查询的结果一样的，都是内连接的使用场景
1.2 自然连接（natural join）
自动去匹配相同的字段，并返回数据行
自然连接需要注意一下亮点：
1、如果自然连接的多个字段的名称和类型都匹配，那么他们都会作为自然连接的连接条件；
2、若自然连接的连个表仅字段相同，但是类型不同将会返回一个错误。
1.3 外连接
在Oracle中外连接主要有 左外连接、右外连接以及全连接三种
1.4 左外连接（left join或者left outer join）
左外连接:返回的记录行数与左表相同，即使右表中没有匹配行，也从左表中返回所有行;如下图所示：
在外连接查询时，需要明白，基表和匹配表，基表即全是记录显示的表，匹配表，则是配合匹配条件进行记录显示的表，匹配不到的记录行，相应字段则以NULL显示，所以最终显示的记录数等于基表的记录数。
SELECT * FROM A LEFT JOIN B ON A.ID=B.ID，
A表是基表，所以记录全是用，再看B表，与A表中不能通过ID字段匹配的记录，则NULL显示。
SELECT * FROM A,B WHERE A.ID=B.ID(+)，效果同上
1.5 右连接（right join或者right outer join）
右连接会返回右表中的所有行，即使左表中没有匹配行，也返回右表的所有行，如下图所示：
1.6 全连接（full join 或者full outer join）
全连接：返回的是两表的全集，无论匹配不匹配都返回行，如下图所示：
1.6.1.1 笛卡尔积（cross join)
笛卡尔积 ：返回的是两表的乘积，返回的行数为两表各自行数的乘积
select s.stu_id,s.name,s.class_id,c.class_name from stu s cross join class c
2 .NET 对ORA-01000问题的正确处理
对ORACLE本身进行相应的CURSOR数量设置
Parallel.ForEach(dtMainXM.AsEnumerable(), (pDataRowXMK) =>
{
DataTable dtDetail = new DataTable();<p style="font-family: Consolas;"> string ID=pDataRowXMK["ID"].ToString();
using (var pSConn = GetSourceConnection())
{
using (OracleCommand cmdSubXM = new OracleCommand())
{
cmdSubXM.Connection = pSConn;
cmdSubXM.CommandText = "select * from tableXX where ID='"+ ID +"'";
using (var iReader = cmdSubXM.ExecuteReader(CommandBehavior.CloseConnection))
{
dtDetail.Load(iReader);
}
}
}
｝
Parallel.ForEach(dtMainXM.AsEnumerable(), (pDataRowXMK) =>
{
DataTable dtDetail = new DataTable();
string ID=pDataRowXMK["ID"].ToString();
using (var pSConn = GetSourceConnection())
{
using (OracleCommand cmdSubXM = new OracleCommand())
{
cmdSubXM.Connection = pSConn;
cmdSubXM.CommandText = "select * from tableXX where ID=:ID";
//修改为参数方式
cmdSubXM.Parameters.Add("ID", ID);
using (var iReader = cmdSubXM.ExecuteReader(CommandBehavior.CloseConnection))
{
dtDetail.Load(iReader);
}
}
}
}
通过前后SQL语句的处理方式,个人理解还是ORACLE的SQL解析计划引起的问题. 使用SQL参数,将最大程度的利用计划缓存.而前一种方式,没有重用性.
3 SQL*Plus - break的用法
在SQL*Plus提示符下输出求和报表，我们可以借助break与compute两个命令来实现。这个两个命令简单易用，可满足日常需求，其实质也相当于在编写SQL语句时使用分组及聚合函数。不同的是在报表中的分组的最下方或整个报表的最下方我们可以得到如sum,avg以及自定义的聚合字样.
1. BRE[AK] [ON report_element [action [action]]] ...
2.
3. where report_element has the following syntax:
4. {column | expression | ROW | REPORT}
5.
6. and where action has the following syntax:
7. [SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]]
1. action则表示
2. skip[n],在每个分组的最后，自动跳过n个空行。
3. skip page, 在每个分组的最后，自动换页。
4. break on row skip[n],每一行后面跳过n个空行。
5. nodup 重复的显示空，dup重复的也显示。
6. goex_admin@SYBO2SZ> break on deptno skip 1
7. goex_admin@SYBO2SZ> break
8. break on deptno skip 1 nodup
9.
10. goex_admin@SYBO2SZ> select * from emp order by deptno;
11.
12. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
13. ---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
14. 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
15. 7839 KING PRESIDENT 19811117 00:00:00 5100
16. 7934 MILLER CLERK 7782 19820123 00:00:00 1400
17.
18. 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
19. 7902 FORD ANALYST 7566 19811203 00:00:00 3100
20. 7876 ADAMS CLERK 7788 19870523 00:00:00 1200
21. 7369 SMITH CLERK 7902 19801217 00:00:00 900
22. 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
23.
1. --下面基于row来分组，且插入1空行
2. goex_admin@SYBO2SZ> break on row skip 1;
3. goex_admin@SYBO2SZ> select * from dept;
4.
5. DEPTNO DNAME LOC
6. ---------- -------------- -------------
7. 10 ACCOUNTING NEW YORK
8.
9. 20 RESEARCH DALLAS
10.
11. 30 SALES CHICAGO
1. --下面的查询中除了基于deptno分组之外，还增加了基于job进行分组
2. goex_admin@SYBO2SZ> break on deptno on job skip 1;
3. goex_admin@SYBO2SZ> select * from emp order by deptno,job;
4.
5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
6. ---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7. 7934 MILLER CLERK 7782 19820123 00:00:00 1400 10
8.
9. 7782 CLARK MANAGER 7839 19810609 00:00:00 2550
10.
11. 7839 KING PRESIDENT 19811117 00:00:00 5100
4 SQL*Plus - compute的用法
1. COMP[UTE] [function [LAB[EL] text] ...
2. OF {expr|column|alias} ...
3. ON {expr|column|alias|REPORT|ROW} ...]
1. function为常用的聚合函数，如sum,avg,maximum,minimum,std,count等等。
2. of为指定的计算列，也就是说要计算哪一列。
3. on为分组条件，基于哪个列，表达式，report，row等进行分组。
4. compute通常结合break来用，否则相当于没有分组，聚合也就没有任何意义。
5. goex_admin@SYBO2SZ> set pagesize 80
1. goex_admin@SYBO2SZ> clear break -->清除break的设置
2. breaks cleared
3.
4. goex_admin@SYBO2SZ> break on deptno skip 1
5. goex_admin@SYBO2SZ> compute sum of sal on deptno -->基于deptno对sal求和
6. goex_admin@SYBO2SZ> select * from emp order by deptno;
7.
8. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
9. ---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
10. 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
11. 7839 KING PRESIDENT 19811117 00:00:00 5100
12. 7934 MILLER CLERK 7782 19820123 00:00:00 1400
13. ---------- **********
14. 9050 sum
15.
16. 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
17. 7902 FORD ANALYST 7566 19811203 00:00:00 3100
18. 7876 ADAMS CLERK 7788 19870523 00:00:00 1200
19. 7369 SMITH CLERK 7902 19801217 00:00:00 900
20. 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
21. ---------- **********
22. 11375
1. goex_admin@SYBO2SZ> break on report skip 1
2. goex_admin@SYBO2SZ> compute sum of sal on report -->对整个report的sal进行求和
3. goex_admin@SYBO2SZ> select * from emp order by deptno;
4.
5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
6. ---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7. 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
8. 7839 KING PRESIDENT 19811117 00:00:00 5100 10
9. 7934 MILLER CLERK 7782 19820123 00:00:00 1400 10
10. 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
11. 7902 FORD ANALYST 7566 19811203 00:00:00 3100 20
12. 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 20
13. 7369 SMITH CLERK 7902 19801217 00:00:00 900 20
14. 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20
15. 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
16. 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 30
17. 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 30
18. 7900 JAMES CLERK 7698 19811203 00:00:00 1050 30
19. 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 30
20. 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30
21. ----------
22. sum 30425
1. goex_admin@SYBO2SZ> compute sum avg of sal on report -->对整个report求和以及求平均
2. goex_admin@SYBO2SZ> select * from emp order by deptno;
3.
4. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
5. ---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
6. 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7. 7839 KING PRESIDENT 19811117 00:00:00 5100 10
8. 7934 MILLER CLERK 7782 19820123 00:00:00 1400 10
9. 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
10. 7902 FORD ANALYST 7566 19811203 00:00:00 3100 20
11. 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 20
12. 7369 SMITH CLERK 7902 19801217 00:00:00 900 20
13. 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20
14. 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
15. 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 30
16. 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 30
17. 7900 JAMES CLERK 7698 19811203 00:00:00 1050 30
18. 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 30
19. 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30
20. ----------
21. avg 2173.21429
22. sum 30425
23.
1. goex_admin@SYBO2SZ> compute avg of sal on deptno; --对sal列基于分组deptno求平均
2. goex_admin@SYBO2SZ> compute sum of comm on deptno; --对comm列基于分组deptno求和
3. goex_admin@SYBO2SZ> select * from emp order by deptno;
4.
5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
6. ---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7. 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
8. 7839 KING PRESIDENT 19811117 00:00:00 5100
9. 7934 MILLER CLERK 7782 19820123 00:00:00 1400
10. ---------- ---------- **********
11. 3016.66667 avg
12. sum
5 DateTime vs Between Trick
SELECT COUNT(*)
FROM DUAL
WHERE TO_DATE('20-jul-2013 5:30:00 PM', 'dd-mon-yyyy HH:MI:SS PM')
BETWEEN to_date('20-jul-2013') AND to_date('20-jul-2013') + 1 - 1;
result:
0
SELECT COUNT(*)
FROM DUAL
WHERE TRUNC(TO_DATE('20-jul-2013 5:30:00 PM', 'dd-mon-yyyy HH:MI:SS PM'))
BETWEEN to_date('20-jul-2013') AND to_date('20-jul-2013') + 1 - 1;
result:
1
6 Merge Into (data merging)
MERGE INTO logger_prefs p
USING (
select 'PURGE_AFTER_DAYS' PREF_NAME, '7' PREF_VALUE from dual union
select 'PURGE_MIN_LEVEL' PREF_NAME, 'DEBUG' PREF_VALUE from dual union
select 'LOGGER_VERSION' PREF_NAME, 'x.x.x' PREF_VALUE from dual union --will be replaced when running script
select 'LEVEL' PREF_NAME, 'DEBUG' PREF_VALUE from dual union
select 'PROTECT_ADMIN_PROCS' PREF_NAME, 'TRUE' PREF_VALUE from dual union
select 'INCLUDE_CALL_STACK' PREF_NAME, 'TRUE' PREF_VALUE from dual union
select 'PREF_BY_CLIENT_ID_EXPIRE_HOURS' PREF_NAME, '12' PREF_VALUE from dual union
select 'INSTALL_SCHEMA' PREF_NAME, sys_context('USERENV','CURRENT_SCHEMA') PREF_VALUE from dual ) d
ON (p.pref_name = d.pref_name)
WHEN MATCHED THEN
update set p.pref_value =
case
-- Only LOGGER_VERSION should be updated during an update
when p.pref_name = 'LOGGER_VERSION' then d.pref_value
else p.pref_value
end
WHEN NOT MATCHED THEN
insert (p.pref_name,p.pref_value)
values (d.pref_name,d.pref_value);
7 Collections
1)
happy_family(1) := ‘11’;
happy_family(2) := ‘2’;
..
children(1) := ’11’;
children(2) := ‘3’;
parents := happy_family MULTISET EXCEPTION children;
2)
distinct_auth := SET (stevfen_auth) ; // remove duplicate in collection
3)
select CAST ( MULTISET(select field from T)
AS collection_type
from DUAL;
Example:
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
nt4 nested_typ := nested_typ(1,2,4);
answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4)
print_nested_table(answer);
answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3)
print_nested_table(answer);
answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1)
print_nested_table(answer);
answer := SET(nt3); -- (2,3,1)
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT nt2; -- (3)
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- ()
print_nested_table(answer);
TIPS
1) index by SIGNTYPE (1,-1,0)
2) lb_found := v1 = v2;
3) return case v1
when 'A' then TRUE
else FALSE
end;
4) nesttable1 MULTISET EXCEPT nesttable2;
5) select CAST (MULTISET (select field from table) as collection_type)
from DUAL;
6) select COLUMN_VALUE favs
from TABLE(CAST(nesttable as names_type))
8 Parse string using regexp_substr
SELECT regexp_substr('MR:DARRENSTEPHEN:WORBOYS','[^:]+', 1, 1) FROM dual;
SELECT regexp_substr('MR:DARRENSTEPHEN:WORBOYS','[^:]+', 1, 2) FROM dual;
SELECT regexp_substr('MR:DARRENSTEPHEN:WORBOYS','[^:]+', 1, 3) FROM dual;
1 – MR
2 - DARRENSTEPHEN
3 - WORBOYS
string to column
SELECT regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, LEVEL)
FROM dual
CONNECT BY regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, LEVEL) IS NOT NULL;
out:
SMITH
ALLEN
WARD
JONES
REGEXP_REPLACE(' {with parentheses} This is some text in it.','\{.*\}','')
This is some text in it. //print out
9 PIVOT & UNPIVOT
SELECT p.*
FROM ( SELECT a.sup_item_rate_dtl_seq, b.rate_type_cd,
c.charge_type_cd,
a.proposed_rate,
negotiated_rate,
rack_rate,
sell_rate,
age_from,
age_to
FROM sup_item_rate_dtl a, sup_type_rate b, sup_type_charge c
WHERE a.sup_type_rate_seq = b.sup_type_rate_seq
AND a.sup_type_charge_seq = c.sup_type_charge_seq
AND a.sup_item_rate_weekday_seq = 230 ) t
PIVOT ( SUM(t.negotiated_rate) FOR rate_type_cd IN (
'CHILD',
'QUAD',
'TRIPLE',
'TWIN',
'SINGLE') ) p
;
10 Invisible field (oracle 12c)
CREATE TABLE emp (
eno number(6),
ename_name varchar2(40),
sal number(9) INVISIBLE );
Alter Table emp MODIFY ( sal VISIBLE );
11 Table Partition
CREATE TABLE emp_part
( eno number(8), ename varchar2(40), sal number (6) )
PARTITION BY RANGE (sal)
(PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p3 VALUES LESS THAN (30000),
PARTITION p_max (MAXVALUE)
);
ALTER TABLE emp_part DROP PARTITIONS p4, p5 UPDATE GLOBAL INDEXES;
ALTER TABLE emp_part TRUNCATE PARTITIONS p4, p5 UPDATE GLOBAL INDEXES;
ALTER TABLE emp_part SPLIT PARTITIONS p_max INTO
(PARTITION p4 VALUES LESS THAN (40000));
ALTER TABLE emp_part MERGE PARTITIONS p1, p2 INTO PARTITION p_merge;
12 Oracle XML
Install war package
loadjava -verbose -resolve -user bxu/j777v@JDBD "C:\app\client\bxu\product\12.1.0\client_1\lib\xmlparserv2.jar"
install java class
loadjava -verbose -resolve -user sys/password PropertyHelper.class
packages
1) xmlparser
2) xmldom
3) xslprocessor
CREATE OR REPLACE FUNCTION submissionXML( id NUMBER ) RETURN CLOB
IS
query VARCHAR2(100);
BEGIN
query := 'SELECT *
FROM accepted_submission
WHERE id = :id';
xmlgen.clearBindValues;
xmlgen.setBindValue('id',id);
RETURN xmlgen.getXML(query);
END;
13 Three level SQL report
-- three level report
-- program
-- price
-- tax
select nvl2(x.grp_price_tax_seq, null, nvl2(x.grp_price_seq, null, x.grp_program_seq)) grp_program_seq
, NVL2(x.grp_price_tax_seq, null, NVL2(x.grp_price_seq, null, x.descr_eng)) descr_eng
, nvl2(x.grp_price_tax_seq, null, nvl2(x.grp_price_seq, null, x.coach_id)) coach_id
, nvl2(x.grp_price_tax_seq, null, nvl2(x.grp_price_seq, null, x.grp_program_type_cd)) grp_program_type_cd
, NVL2(x.grp_price_tax_seq, null, NVL2(x.grp_price_seq, null, x.grp_program_status_cd)) grp_program_status_cd
, nvl2(x.grp_price_tax_seq, null, x.grp_price_seq) grp_price_seq
, nvl2(x.grp_price_tax_seq, null, x.min_pax_count) min_pax_count
, nvl2(x.grp_price_tax_seq, null, x.grp_price_type_cd) grp_price_type_cd
, nvl2(x.grp_price_tax_seq, null, x.amount) grp_price_samounteq
, nvl2(x.grp_price_tax_seq, null, x.paying_pax_flag) paying_pax_flag
, x.grp_price_tax_seq
, x.tax_cd
, x.tax_amt
, x.rebate_amt
, x.taxable_pct
from (
SELECT a.grp_program_seq
, a.descr_eng
, a.coach_id
, a.grp_program_type_cd
, a.grp_program_status_cd
, null grp_price_seq
, null min_pax_count
, null grp_price_type_cd
, null amount
, null paying_pax_flag
, null grp_price_tax_seq
, null tax_cd
, null tax_amt
, null rebate_amt
, null taxable_pct
FROM grp_program a
WHERE a.grp_master_seq = 323
UNION ALL
SELECT a.grp_program_seq
, a.descr_eng
, a.coach_id
, a.grp_program_type_cd
, a.grp_program_status_cd
, b.grp_price_seq
, b.min_pax_count
, b.grp_price_type_cd
, b.amount
, b.paying_pax_flag
, null grp_price_tax_seq
, null tax_cd
, null tax_amt
, null rebate_amt
, null taxable_pct
FROM grp_program a
, grp_price b
WHERE a.grp_program_seq = b.grp_program_seq
AND a.grp_master_seq = 323
UNION ALL
SELECT a.grp_program_seq
, a.descr_eng
, a.coach_id
, a.grp_program_type_cd
, a.grp_program_status_cd
, b.grp_price_seq
, b.min_pax_count
, b.grp_price_type_cd
, b.amount
, b.paying_pax_flag
, c.grp_price_tax_seq
, c.tax_cd
, c.tax_amt
, c.rebate_amt
, c.taxable_pct
FROM grp_program a
, grp_price b
, grp_price_tax c
WHERE a.grp_program_seq = b.grp_program_seq
and b.grp_price_seq = c.grp_price_seq
and a.grp_master_seq = 323
order by 1, 3 nulls first, 6 nulls first, 11 nulls first ) x
14 Table Virtual Column
CREATE TABLE ta_virt_col
(
a NUMBER(10),
b NUMBER(20) GENERATED ALWAYS AS ( CAST(10*a AS NUMBER(10)) ),
c GENERATED ALWAYS AS ( CAST(100*a AS NUMBER(20)) )
);
15 ListAgg (concat )
SELECT LISTAGG (a.res_seq || ' ;')
WITHIN GROUP (ORDER BY a.client_loc_seq)
from reservation a
where a.client_loc_seq = 230
and rownum < 10;
16 window 7 + open dde for PLSQL developer
>start > type ‘make file type alwarys open in species program’
choose .sql > select PLSQL developer
17 TimeStamp with UTC
SELECT
a.create_date AS "DATE",
TO_CHAR(a.create_date, 'DD-MON-YYYY HH24:MI:SS TMZ') AS "DATE_FMT_HH24",
SYS_EXTRACT_UTC(TO_TIMESTAMP(TO_CHAR(a.create_date, 'DD-MON-YYYY HH:MI:SS AM'))) AS "DATE_UTC_FMT_AM",
SYS_EXTRACT_UTC(TO_TIMESTAMP(TO_CHAR(a.create_date, 'DD-MON-YYYY HH:MI:SS'))) AS "DATE_UTC_FMT",
SYS_EXTRACT_UTC(TO_TIMESTAMP(a.create_date, 'DD-MON-YYYY HH24:MI:SS')) AS "DATE_UTC_FMT_HH24",
SYS_EXTRACT_UTC(TO_TIMESTAMP(a.create_date, 'DD-MON-YYYY HH:MI:SS')) AS "DATE_UTC_FMT_HH",
CAST(a.create_date AS TIMESTAMP WITH LOCAL TIME ZONE) AS "DATE_ZONE",
SYSTIMESTAMP AS "TIMESATMP",
CAST(systimestamp AS TIMESTAMP WITH LOCAL TIME ZONE) AS "TIMESTAMP_ZONE",
SYS_EXTRACT_UTC(SYSTIMESTAMP) AS "TIMESTAMP_UTC"
FROM sp_output a
WHERE ROWNUM <= 2000;
18 Scientific Number to Number
AND (summary_layout.layout_order BETWEEN to_char(470000000e+003,'999999999999')
AND to_char(0.470000000e+003 + 38,'999999999999'))
19 COLLECT
CREATE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
SELECT a.client_seq,
CAST(COLLECT(a.client_loc_name ORDER BY a.client_loc_name) AS varchar2_ntt)
FROM client_location a
GROUP BY a.client_seq;
SELECT a.client_seq,
CAST(COLLECT(UNIQUE a.client_loc_name) AS varchar2_ntt)
FROM client_location a
GROUP BY a.client_seq;
SELECT a.client_seq,
CAST(COLLECT(UNIQUE a.client_loc_name) AS varchar2_ntt)
FROM client_location a
GROUP BY a.client_seq;
SELECT a.client_seq,
CAST(COLLECT(DISTINCT a.client_loc_name ORDER BY a.client_loc_name) AS varchar2_ntt)
FROM client_location a
GROUP BY a.client_seq;
CREATE TYPE client_ot AS OBJECT (
client_loc_name varchar2(100),
client_loc_id varchar2(20)
);
CREATE TYPE client_ntt IS TABLE OF client_ot;
SELECT a.client_seq,
CAST(COLLECT(client_ot(a.client_loc_name, a.client_loc_id)) AS client_ntt)
FROM client_location a
GROUP BY a.client_seq;
SELECT a.client_seq,
CAST(COLLECT(client_ot(a.client_loc_name, a.client_loc_id) ORDER BY a.client_loc_id) AS client_ntt)
FROM client_location a
GROUP BY a.client_seq;
20 NTH_VALUE
EMPLOYEE_ID LAST_NAME FIRST_NAME SALARY DEPT_ID
----------- --------- ---------- ------ -------
1000 Jackson Joe 2000 10
2000 Smith Jane 3500 10
3000 Ferguson Samantha 1900 10
4000 Reynolds Allen 4000 20
5000 Anderson Paige 3250 20
6000 Johnson Derek 2750 20
7000 Nelson Sarah 5000 30
8000 Burke Russell 1500 30
SELECT DISTINCT dept_id, NTH_VALUE(salary,2)
OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "SECOND HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;
These are the results that you should see:
DEPT_ID SECOND HIGHEST
------- --------------
10 2000
20 3250
SELECT DISTINCT dept_id, NTH_VALUE(salary,2)
OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "SECOND HIGHEST",
NTH_VALUE(salary,3)
OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "THIRD HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;
These are the results that you should see:
DEPT_ID SECOND HIGHEST THIRD HIGHEST
------- -------------- -------------
10 2000 1900
20 3250 2750
21 LAST_VALUE
EMPLOYEE_ID LAST_NAME FIRST_NAME SALARY DEPT_ID
----------- --------- ---------- ------ -------
1000 Jackson Joe 2000 10
2000 Smith Jane 3500 10
3000 Ferguson Samantha 1900 10
4000 Reynolds Allen 4000 20
5000 Anderson Paige 3250 20
6000 Johnson Derek 2750 20
7000 Nelson Sarah 5000 30
8000 Burke Russell 1500 30
SELECT DISTINCT LAST_VALUE(salary)
OVER (ORDER BY salary ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "HIGHEST"
FROM employees;
HIGHEST
-------
5000
Lower Salary
SELECT DISTINCT dept_id, LAST_VALUE(salary)
OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;
These are the results that you should see:
DEPT_ID LOWEST
------- ------
10 1900
20 2750
22 Date to day
SELECT to_char(TO_DATE('17-sep-2016'),'DY') FROM DUAL;
>>SAT.
23 Stateless Locking Method
SELECT rowid
INTO v_rowid
FROM emp
WHERE empno = 7876;
v_old_checksum := owa_opt_lock.checksum('SCOTT','EMP', v_rowid);
-------------------------------------------FUNC
CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno IN emp.empno%TYPE,
p_old_checksum IN NUMBER,
p_new_sal IN emp.sal%TYPE) AS
v_rowid ROWID;
v_new_checksum NUMBER;
BEGIN
SELECT rowid
INTO v_rowid
FROM emp
WHERE empno = p_empno
FOR UPDATE;
v_new_checksum := owa_opt_lock.checksum('SCOTT','EMP', v_rowid);
IF v_new_checksum = p_old_checksum THEN
UPDATE emp
SET sal = p_new_sal
WHERE rowid = v_rowid;
COMMIT;
ELSE
ROLLBACK;
RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
END IF;
END;
-----------------------------------------------Oracle 10g
SELECT ORA_ROWSCN, a.* FROM XX;
CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno IN emp.empno%TYPE,
p_scn IN NUMBER,
p_new_sal IN emp.sal%TYPE) AS
BEGIN
UPDATE emp
SET sal = p_new_sal
WHERE empno = p_empno
AND ORA_ROWSCN = p_scn;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
END IF;
COMMIT;
END;
24 Select within a Select
SELECT DISTINCT
sup_item_rate_dtl.sup_grp_rate_range_seq,
NVL(sup_item_rate_dtl.min_qty, (SELECT NVL(a.min_qty,0) FROM sup_grp_rate_range a WHERE a.sup_grp_rate_range_seq = sup_item_rate_dtl.sup_grp_rate_range_seq)),
NVL(sup_item_rate_dtl.max_qty, (SELECT NVL(a.max_qty,0) FROM sup_grp_rate_range a WHERE a.sup_grp_rate_range_seq = sup_item_rate_dtl.sup_grp_rate_range_seq)),
sup_item_rate_dtl.min_qty,
sup_item_rate_dtl.max_qty
FROM sup_item_rate_dtl,
sup_type_rate
WHERE sup_item_rate_dtl.sup_type_rate_seq = sup_type_rate.sup_type_rate_seq
AND sup_item_rate_dtl.sup_item_rate_weekday_seq IN (829567, 829568)
ORDER BY NVL(sup_item_rate_dtl.sup_grp_rate_range_seq, 0), sup_item_rate_dtl.min_qty;
25 Loadjava
1) set BIN path
path-ora=set path=%path%;"C:\app\client\bxu\product\12.1.0\client_1\BIN\"
2) run in cmd.exe
loadjava.bat -r -f -u bxu/j777v@jdbd "C:\bernard\program\sqldeveloper\sqldeveloper\lib\json.jar"
drop
dropjava.bat -r -f -u bxu/j777v@jdbd "C:\bernard\program\sqldeveloper\sqldeveloper\lib\json.jar"
package:
create or replace package jsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2;
function getarrval(jsonArrayStr varchar2,seqNo number) return varchar2;
function getarrlen(jsonArrayStr varchar2) return number;
end jsonpkg;
/
create or replace package body jsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2
as language java name 'JsonUtil.getValue(java.lang.String,java.lang.String) return java.lang.String';
function getarrval(jsonArrayStr varchar2,seqNo number) return varchar2
as language java name 'JsonUtil.getArrayValue(java.lang.String, java.lang.Integer) return java.lang.String';
function getarrlen(jsonArrayStr varchar2) return number
as language java name 'JsonUtil.getArrayLength(java.lang.String) return java.lang.Integer';
end jsonpkg;
26 抽出按某种方式排序的记录集中的第M条记录开始的X条记录
则抽取NAME的字母顺的第2条记录开始的3条记录的SQL语句为：
SELECT ID, NAME FROM
(
SELECT ROWNUM RECNO, ID, NAME FROM
(SELECT * FROM CUSTOMER ORDER BY NAME)
WHERE ROWNUM <= (2 + 3 - 1)
ORDER BY ROWNUM ASC
)
WHERE RECNO BETWEEN 2 AND (2 + 3 - 1)
27 pragma serially reusable
不建议使用SERIALLY_REUSABLE，它每次调用都会出现包"加载-放"操作，会占用大量内存;
usage: RESET global variable when using package
CREATE OR REPLACE PACKAGE mldn_pkg
AS
PRAGMA SERIALLY_REUSABLE ;
v_deptno dept.deptno%TYPE ;
FUNCTION get_emp_fun(p_eno emp.empno%TYPE) RETURN emp%ROWTYPE ;
END ;
28 XMLTable
SELECT *
FROM xmltable ( '/PivotSet/item'
passing XMLTYPE ( '
<PivotSet>
<item>
<column name = "B">A</column>
<column name ="SUM(A)">24</column>
</item>
<item>
<column name = "B">B</column>
<column name = "SUM(A)">15</column>
</item>
<item>
<column name = "B">C</column>
<column name = "SUM(A)">18</column>
</item>
<item>
<column name = "B">D</column>
<column name = "SUM(A)">21</column>
</item>
</PivotSet>' )
COLUMNS
name VARCHAR2 ( 100 ) path 'column[@name eq "B" ]/text()',
sum NUMBER path 'column[@name eq "SUM(A)" ]/number()' )
select *
from xmltable ('/ExportXML/record/field' passing XMLType.createXML('<ExportXML>
<record>
<field name="ProfileInformation,Candidate,Number">67264</field>
<field name="Staff_SIAAmount,Code">10,000</field>
</record>
<record>
<field name="ProfileInformation,Candidate,Number">51301</field>
<field name="Staff_SIAAmount,Code">10,000</field>
</record>
</ExportXML>') columns attribute varchar2(250) path '@name'
, value varchar2(100) path '.' );
SELECT *
FROM XMLTABLE ('/ExportXML/record' PASSING XMLType.createXML('<ExportXML>
<record>
<field name="ProfileInformation,Candidate,Number">67264</field>
<field name="Staff_SIAAmount,Code">10,000</field>
</record>
<record>
<field name="ProfileInformation,Candidate,Number">51301</field>
<field name="Staff_SIAAmount,Code">20,000</field>
</record>
</ExportXML>')
COLUMNS
CandidateNumber varchar2(250) PATH 'field[@name="ProfileInformation,Candidate,Number"]' ,
SIAAmount varchar2(100) PATH 'field[@name="Staff_SIAAmount,Code"]' );
select e.first, e.last, p.phone, e.salary
from XMLTABLE('/dept/employee'
PASSING XMLTYPE('
<dept bldg="114">
<employee id="903">
<name>
<first>Sachin</first>
<last>Fulari</last>
</name>
<office>CTS</office>
<phone>9158500222</phone>
<phone>9028044722</phone>
<salary currency="CHF">64000</salary>
</employee>
</dept>') COLUMNS
first VARCHAR2(25) PATH 'name/first',
last VARCHAR2(25) PATH 'name/last',
phone_xml XMLTYPE PATH 'phone',
salary NUMBER PATH 'salary') AS e,
XMLTable('/phone'
PASSING e.phone_xml
COLUMNS
phone VARCHAR2(12) PATH '.') AS p;
--string to array
SELECT instr('1101001', 1, 1, LEVEL)
FROM dual
CONNECT BY LEVEL <= length('1001000');
--0001 1
--0010 2
--0100 4
--1000 8
SELECT BITAND (4,4) FROM DUAL;
==> 4
-- bitand for bit signal
WITH tmp (txn_status)
AS
(
SELECT 3 FROM dual UNION ALL --0011
SELECT 0 FROM dual UNION ALL --0000
SELECT 1 FROM dual UNION ALL --0001
SELECT 7 FROM dual --0111
)
SELECT DECODE(BITAND(txn_status, 1), 1, 'YES', 'NO') "REQ_SENT",
DECODE(BITAND(txn_status, 2), 2, 'YES', 'NO') "RESPONSE_RECEIVED",
DECODE(BITAND(txn_status, 4), 4, 'YES', 'NO') "ERROR_IN_RESPONSE"
FROM tmp;
SELECT to_char(1234567890, 'FM9999999999') DEC,
to_char(1234567890, 'FMXXXXXXXXXXXXXX') HEX
FROM dual;
-- decimal to hex
SELECT TO_CHAR(15, 'x')
FROM dual;
-- hex to decimal
SELECT to_number('f', 'x')
FROM dual;
-- fill mode FM
-- fill exact FX
SELECT TO_CHAR(155, 'fmxx')
FROM dual;
SELECT TO_CHAR(155, 'fmxxxx')
FROM dual;
29 Update Text with Chinese Character
UPDATE PRODUCT_ATTRIBUTE_LANG a SET a.text = N'含欧式早餐.'
WHERE a.product_attribute_lang_seq = 1265435;
30 improving performance with pipelined table functions
30.1 replacing row-based insert
FUNCTION pipe_stocks(p_source_data IN stockpivot_pkg.stocktable_rct) RETURN stockpivot_ntt
PIPELINED IS
r_target_data stockpivot_ot := stockpivot_ot(NULL, NULL, NULL, NULL);
r_source_data stockpivot_pkg.stocktable_rt;
BEGIN
LOOP
FETCH p_source_data
INTO r_source_data;
EXIT WHEN p_source_data%NOTFOUND;
/* First row... */
r_target_data.ticker := r_source_data.ticker;
r_target_data.price_type := 'O';
r_target_data.price := r_source_data.open_price;
r_target_data.price_date := r_source_data.trade_date;
PIPE ROW(r_target_data);
/* Second row... */
r_target_data.price_type := 'C';
r_target_data.price := r_source_data.close_price;
PIPE ROW(r_target_data);
END LOOP;
CLOSE p_source_data;
RETURN;
END pipe_stocks;
INSERT INTO tickertable (ticker, price_type, price, price_date)
SELECT ticker, price_type, price, price_date
FROM TABLE(
stockpivot_pkg.pipe_stocks(
CURSOR(SELECT * FROM stocktable)));
30.2 with array-fetching
FUNCTION pipe_stocks_array
(
p_source_data IN stockpivot_pkg.stocktable_rct,
p_limit_size IN PLS_INTEGER DEFAULT stockpivot_pkg.c_default_limit
) RETURN stockpivot_ntt
PIPELINED IS
r_target_data stockpivot_ot := stockpivot_ot(NULL, NULL, NULL, NULL);
aa_source_data stockpivot_pkg.stocktable_aat;
BEGIN
LOOP
FETCH p_source_data BULK COLLECT
INTO aa_source_data LIMIT p_limit_size;
EXIT WHEN aa_source_data.COUNT = 0;
/* Process the batch of (p_limit_size) records... */
FOR i IN 1 .. aa_source_data.COUNT
LOOP
/* First row... */
r_target_data.ticker := aa_source_data(i).ticker;
r_target_data.price_type := 'O';
r_target_data.price := aa_source_data(i).open_price;
r_target_data.price_date := aa_source_data(i).trade_date;
PIPE ROW(r_target_data);
/* Second row... */
r_target_data.price_type := 'C';
r_target_data.price := aa_source_data(i).close_price;
PIPE ROW(r_target_data);
END LOOP;
END LOOP;
CLOSE p_source_data;
RETURN;
END pipe_stocks_array;
pipelined with parallel
CREATE PACKAGE stockpivot_pkg AS
<snip>
FUNCTION pipe_stocks_parallel(
p_source_data IN stockpivot_pkg.stocktable_rct
p_limit_size IN PLS_INTEGER DEFAULT stockpivot_pkg.c_default_limit
) RETURN stockpivot_ntt
PIPELINED
PARALLEL_ENABLE (PARTITION p_source_data BY ANY);
<snip>
END stockpivot_pkg;
PROCEDURE load_stocks_parallel IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
INSERT /*+ PARALLEL(t, 4) */
INTO tickertable t
(ticker, price_type, price, price_date)
SELECT ticker,
price_type,
price,
price_date
FROM TABLE(stockpivot_pkg.pipe_stocks_parallel(CURSOR (SELECT /*+ PARALLEL(s, 4) */ * FROM stocktable s)));
END load_stocks_parallel;
pipelined with merge
FUNCTION pipe_employees
(
p_source_data IN employee_pkg.employee_rct,
p_limit_size IN PLS_INTEGER DEFAULT employee_pkg.c_default_limit
) RETURN employee_ntt
PIPELINED
PARALLEL_ENABLE(PARTITION p_source_data BY ANY) IS
aa_source_data employee_pkg.employee_aat;
BEGIN
LOOP
FETCH p_source_data BULK COLLECT
INTO aa_source_data LIMIT p_limit_size;
EXIT WHEN aa_source_data.COUNT = 0;
FOR i IN 1 .. aa_source_data.COUNT
LOOP
PIPE ROW(employee_ot(aa_source_data(i).employee_id, < snip > SYSDATE));
END LOOP;
END LOOP;
CLOSE p_source_data;
RETURN;
END pipe_employees;
PROCEDURE merge_employees IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
MERGE /*+ PARALLEL(e, 4) */
INTO employees e
USING TABLE(employee_pkg.pipe_employees(CURSOR (SELECT /*+ PARALLEL(es, 4) */ *
FROM employees_staging es))) s
ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN
UPDATE
SET < snip >
WHEN NOT MATCHED THEN
INSERT
(< snip >)
VALUES
(< snip >);
END merge_employees;
CREATE TYPE customer_ot AS OBJECT
(
customer_id NUMBER
)
NOT FINAL;
-- Collection of supertype...
CREATE TYPE customer_ntt AS TABLE OF customer_ot;
-- Customer detail subtype...
CREATE TYPE customer_detail_ot UNDER customer_ot(first_name VARCHAR2(20), last_name VARCHAR2(60), birth_date DATE) FINAL;
-- Address detail subtype...
CREATE TYPE address_detail_ot UNDER customer_ot(address_id NUMBER, primary VARCHAR2(1), street_address VARCHAR2(40), postal_code VARCHAR2(10)) FINAL;
FUNCTION customer_transform_multi
(
p_source IN customer_staging_rct,
p_limit_size IN PLS_INTEGER DEFAULT customer_pkg.c_default_limit
) RETURN customer_ntt
PIPELINED
PARALLEL_ENABLE(PARTITION p_source BY HASH(customer_id)) ORDER p_source BY(customer_id, address_id) IS
aa_source customer_staging_aat;
v_customer_id customer_staging.customer_id%TYPE := -1; /* Needs a non-null default */
BEGIN
LOOP
FETCH p_source BULK COLLECT INTO aa_source LIMIT p_limit_size;
EXIT WHEN aa_source.COUNT = 0;
FOR i IN 1 .. aa_source.COUNT
LOOP
/* Only pipe the first instance of the customer details... */
IF aa_source(i).customer_id != v_customer_id THEN
PIPE ROW(customer_detail_ot(aa_source(i).customer_id,
aa_source(i).first_name,
aa_source(i).last_name,
aa_source(i).birth_date));
END IF;
PIPE ROW(address_detail_ot(aa_source(i).customer_id,
aa_source(i).address_id,
aa_source(i).primary,
aa_source(i).street_address,
aa_source(i).postal_code));
/* Save customer ID for "control break" logic... */
v_customer_id := aa_source(i).customer_id;
END LOOP;
END LOOP;
CLOSE p_source;
RETURN;
END customer_transform_multi;
SELECT *
FROM TABLE(
customer_pkg.customer_transform_multi(
CURSOR( SELECT * FROM customer_staging ) ) ) nt
WHERE ROWNUM <= 5;
CUSTOMER_ID
-----------
1
1
1
1
2
SELECT VALUE(nt) AS object_instance --could use “nt.OBJECT_VALUE” instead
FROM TABLE(
customer_pkg.customer_transform_multi(
CURSOR( SELECT * FROM customer_staging ) ) ) nt
WHERE ROWNUM <= 5;
OBJECT_INSTANCE(CUSTOMER_ID)
---------------------------------------------------------------------------
CUSTOMER_DETAIL_OT(1, 'Abigail', 'Kessel', '31/03/1949')
ADDRESS_DETAIL_OT(1, 12135, 'N', '37 North Coshocton Street', '78247')
ADDRESS_DETAIL_OT(1, 12136, 'N', '47 East Sagadahoc Road', '90285')
ADDRESS_DETAIL_OT(1, 12156, 'Y', '7 South 3rd Circle', '30828')
CUSTOMER_DETAIL_OT(2, 'Anne', 'KOCH', '23/09/1949')
SELECT CASE
WHEN VALUE(nt) IS OF TYPE (customer_detail_ot)
THEN 'C'
ELSE 'A'
END AS record_type
, TREAT(VALUE(nt) AS customer_detail_ot) AS cust_rec
, TREAT(VALUE(nt) AS address_detail_ot) AS addr_rec
FROM TABLE(
customer_pkg.customer_transform_multi(
CURSOR( SELECT * FROM customer_staging ) ) ) nt
WHERE ROWNUM <= 5;
RECORD_TYPE CUST_REC ADDR_REC
----------- ------------------------------ ------------------------------
C CUSTOMER_DETAIL_OT(1, 'Abigail
', 'Kessel', '31/03/1949')
A ADDRESS_DETAIL_OT(1, 12135, 'N
', '37 North Coshocton Street'
, '78247')
31 params.ini in (PLSQL125 folder)
#BXU for search run @commander
SQLPATH=c:\jvmis\source\ccrs\PLSQL;c:\jvmis\source\ccrs\PLSQL.DEV\cmd
Or define system variable
SQLPATH=c:\jvmis\source\ccrs\PLSQL;c:\jvmis\source\ccrs\PLSQL.DEV\cmd
32 Can’t view source code when navigator in PLSQLDEV
/* Source of PACKAGE BODY JVMIS.PKG_SP is not available */
grant SELECT on v$sqlarea to BXU ;CHK
grant SELECT on sys.dba_objects to BXU ;CHK
grant SELECT on sys.dba_source to BXU ;CHK
grant SELECT on sys.all_source to BXU ;CHK
SELECT *
FROM sys.all_source
WHERE owner = 'JVMIS' AND name = 'PKG_COM' AND TYPE = 'PACKAGE BODY'
ORDER BY line
1) Point:
grant DEBUG ANY PROCEDURE to BXU
2) Check privilige
SELECT * FROM session_privs;
JDBR
JDBA
JDBD
PRIVILEGE
PRIVILEGE
PRIVILEGE
1
CREATE SESSION
1
CREATE SESSION
1
CREATE SESSION
2
ALTER SESSION
2
ALTER SESSION
2
ALTER SESSION
3
UNLIMITED TABLESPACE
3
CREATE TABLE
3
UNLIMITED TABLESPACE
4
CREATE TABLE
4
CREATE CLUSTER
4
CREATE TABLE
5
ALTER ANY TABLE
5
CREATE SYNONYM
5
ALTER ANY TABLE
6
CREATE CLUSTER
6
CREATE SEQUENCE
6
CREATE CLUSTER
7
CREATE ANY INDEX
7
CREATE PROCEDURE
7
CREATE ANY INDEX
8
DROP ANY SYNONYM
8
CREATE TRIGGER
8
DROP ANY SYNONYM
9
CREATE PUBLIC SYNONYM
9
CREATE MATERIALIZED VIEW
9
CREATE PUBLIC SYNONYM
10
DROP PUBLIC SYNONYM
10
CREATE TYPE
10
DROP PUBLIC SYNONYM
11
CREATE VIEW
11
MANAGE ANY QUEUE
11
CREATE VIEW
12
CREATE ANY VIEW
12
ENQUEUE ANY QUEUE
12
CREATE ANY VIEW
13
CREATE SEQUENCE
13
DEQUEUE ANY QUEUE
13
CREATE SEQUENCE
14
DROP ANY SEQUENCE
14
DEBUG CONNECT SESSION
14
DROP ANY SEQUENCE
15
CREATE PROCEDURE
15
DEBUG ANY PROCEDURE
15
CREATE PROCEDURE
16
CREATE TRIGGER
16
CREATE EVALUATION CONTEXT
16
CREATE TRIGGER
17
CREATE TYPE
17
CREATE RULE SET
17
CREATE TYPE
18
CREATE OPERATOR
18
CREATE RULE
18
CREATE OPERATOR
19
CREATE INDEXTYPE
19
CREATE INDEXTYPE
20
MANAGE ANY QUEUE
20
MANAGE ANY QUEUE
21
ENQUEUE ANY QUEUE
21
ENQUEUE ANY QUEUE
22
DEQUEUE ANY QUEUE
22
DEQUEUE ANY QUEUE
23
CREATE ANY CONTEXT
23
CREATE ANY CONTEXT
24
DEBUG CONNECT SESSION
24
DEBUG CONNECT SESSION
25
CREATE EVALUATION CONTEXT
25
CREATE EVALUATION CONTEXT
26
CREATE RULE SET
26
CREATE RULE SET
27
CREATE RULE
27
CREATE RULE
28
CREATE JOB
28
CREATE JOB
33 Convert Decode Function to Case Statement
Case statements are better suited for ANSI syntax compatibility.
Example
Original SQL using DECODE function:
SELECT empno EMPNO,
DECODE (deptno, 10, 'New York', 20, 'Dallas', 'Chicago') COL
FROM emp e, dept d
WHERE e.deptno = d.deptno;
Refactored SQL using CASE statement:
SELECT empno EMPNO
CASE
WHEN deptno = 10 THEN 'New York'
WHEN deptno = 20 THEN 'Dallas'
ELSE 'Chicago'
END
COL
FROM emp e, dept d
WHERE e.deptno = d.deptno;
34 pivot | unpivot
select value
from
(
(
select
'a' v1,
'e' v2,
'i' v3,
'o' v4,
'u' v5
from dual
)
unpivot
(
value
for value_type in
(v1,v2,v3,v4,v5)
)
)
/
Here is the output:
V
-
a
e
i
o
u
35 ORA_ROWSCN
if you want to use ORA_ROWSCN, must turn on CREATE TABLE ROWDEPENCIES
--norowdepencies. result -> no change for ora_rowscn
create table t1 ( x int, y int, z char(50)) NOROWDEPENDENCIES;
insert into t1 select rownum, rownum, rownum
from dual
connect by level <= 100000;
SELECT ora_rowscn, SCN_TO_TIMESTAMP(ORA_ROWSCN), t1.* FROM t1;
UPDATE t2 SET t2.x = 100 WHERE t2.x = 296;
SELECT ora_rowscn, SCN_TO_TIMESTAMP(ORA_ROWSCN), t1.* FROM t1;
--rowdepencies. result -> change for ora_rowscn
DROP TABLE t2;
create table t2 ( x int, y int, z char(50)) ROWDEPENDENCIES;
insert into t2 select rownum, rownum, rownum
from dual
connect by level <= 100000;
SELECT ora_rowscn, SCN_TO_TIMESTAMP(ORA_ROWSCN), t2.* FROM t2
UPDATE t2 SET t2.x = 100 WHERE t2.x = 296;
SELECT ora_rowscn, SCN_TO_TIMESTAMP(ORA_ROWSCN), t2.* FROM t2
How to check if it this feature is ON?
SELECT owner, table_name, dependencies FROM all_tables a WHERE a.DEPENDENCIES <> 'DISABLED'
36 Connect by Hierarch Query
SELECT LEVEL,
a.parent_summary_layout_seq,
a.summary_layout_seq,
a.layout_order,
SYS_CONNECT_BY_PATH(a.summary_layout_seq, '/') "PATH_LAYOUT",
SYS_CONNECT_BY_PATH(SUBSTR(a.descr_eng, 1, 13), '/') "PATH_LAYPUT_DESCR",
CONNECT_BY_ROOT a.descr_eng "TOP_LAYOUT_DESCR",
PRIOR a.descr_eng "PRIOR_LAYOUT",
connect_by_isleaf "IFLEAF",
a.cost_model_method_cd,
a.descr_eng,
a.descr_fre,
a.tax_cd,
a.rebate_flag,
a.commission_flag,
a.section_flag,
a.selling_price_flag,
a.margin_flag,
a.tax_base_flag,
a.special_price_flag,
a.discount_flag
FROM SUMMARY_LAYOUT a
WHERE a.cost_model_method_cd = 'GRPCST'
CONNECT BY PRIOR a.summary_layout_seq = a.parent_summary_layout_seq
START WITH a.parent_summary_layout_seq IS NULL
ORDER BY LEVEL,
a.parent_summary_layout_seq,
a.layout_order
37 Issue for open default app (plsqldev) for .sql file
PLSQLDev 32bit DDE can’t communication with window 7 64bit, we must install plsqldev 64bit.
37.1 Solution:
I solved this problem in Windows7.
In Windows XP it is simple(folder options ->file types -> advanced...), but for Windows7 I had to use program "filetypesman".
You need register ".sql" filetype with DDE instructions for action "open":
Message: [Open("%1")]
Application: PLSQLDev
Topic: System
It is very useful for me. I can quickly open SQL|TEST from shortcut in toolbar (configuration in "Tools->Configuration Tools") in open instance. And I also use many instances running and had no problems with such configuration.
38 Member of
Member of a Nested Table Comparison
The MEMBER [OF] or NOT MEMBER [OF] condition tests whether an element is a member of a nested table, returning the result as a Boolean value. The OF keyword is optional and has no effect on the output.
Example 3-28 Using MEMBER OF on a Nested Table
SELECT graduation
FROM students
WHERE person_typ(12, 'Bob Jones', '1-800-555-1212') MEMBER OF math_majors;
4596
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
