--存储过程练习 01
CREATE OR REPLACE PROCEDURE TEST_XJT(X OUT VARCHAR2,
Y OUT VARCHAR2,
Z OUT VARCHAR2,
S IN VARCHAR2) IS
TYPE listtest IS TABLE OF TEST2%ROWTYPE;
V_TM_NEXT_WEIGHT_PRICE listtest := listtest();
sss TEST2%ROWTYPE;
sql_string varchar2(30000);
STR_SQL VARCHAR2(200);
BEGIN
SELECT T.* BULK COLLECT INTO V_TM_NEXT_WEIGHT_PRICE FROM TEST2 T;
FOR I IN V_TM_NEXT_WEIGHT_PRICE.FIRST .. V_TM_NEXT_WEIGHT_PRICE.LAST LOOP
DBMS_OUTPUT.put_line(V_TM_NEXT_WEIGHT_PRICE(I).CT1);
-- fetch V_TM_NEXT_WEIGHT_PRICE
-- INTO sss;
END LOOP;
FOR I IN 1 .. 3 LOOP
DBMS_OUTPUT.put_line(V_TM_NEXT_WEIGHT_PRICE(I).CT1);
END LOOP;
sql_string := 'UPDATE TEST T SET T.A1=1 WHERE T.A2 = :a';
EXECUTE IMMEDIATE sql_string
USING TO_CHAR(NULL);
--STR_SQL := 'SELECT * FROM TT_BIL_SFBANK_CZ_COST T WHERE T.EMP_CODE = :S';
STR_SQL := 'UPDATE TEST T SET T.A1 = :S WHERE T.A3 = :D';
EXECUTE IMMEDIATE STR_SQL
USING SYSDATE, '20000';
X := '1';
Y := '2';
Z := '3';
INSERT INTO TEST
(A1, A2, A3)
SELECT C1,
C2,
(SELECT T1.DEPT_CODE
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.EMP_CODE ORDER BY T.CREATED_TM DESC) ROWNO
FROM TM_EMPLOYEE T
WHERE T.VALID_FLG = 1) T1
WHERE T1.ROWNO = 1
AND T1.EMP_CODE = K3.C1) A3
FROM (SELECT J.*,
ROW_NUMBER() OVER(PARTITION BY J.C2, J.C3 ORDER BY J.C1) ROWNO
FROM TEST1 J
WHERE J.C1 = 1
AND NOT EXISTS (SELECT 1
FROM TT_BANK_COST T
WHERE J.C2 = T.EMP_CODE
AND J.C3 = T.COST_DT)) K3
WHERE K3.ROWNO = 1;
/* SELECT SUM(A3) A3
FROM (SELECT T.A1, NULL A2, NULL A3
FROM TEST T
UNION ALL
SELECT NULL, T2.CT2, NULL
FROM TEST2 T2
UNION ALL
SELECT NULL, NULL, T1.C3 FROM TEST1 T1);*/
-- SELECT 1 FROM DUAL CONNECT BY LEVEL <3;
--提交
COMMIT;
EXCEPTION
WHEN OTHERS THEN
X := SQLERRM;
Y := SQLCODE;
Z := SYSDATE;
DBMS_OUTPUT.put_line(SQLERRM);
ROLLBACK;
END TEST_XJT;
-- 存储过程练习，过程之间的调用
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
PROCEDURE SP_MAIN IS
L_LIST T_TAB;
BEGIN
SELECT ROWNUM BULK COLLECT
INTO L_LIST
FROM DUAL
CONNECT BY LEVEL <= 200;
/* for i in 1..l_list.count loop
dbms_output.put_line(l_list(i));
end loop;*/
SP_CALL(L_LIST);
END;
PROCEDURE SP_CALL(P_LIST IN T_TAB) IS
L_SQL VARCHAR2(32767) := NULL;
L_RES NUMBER;
BEGIN
L_SQL := 'select max(column_value)
from table(:p_fid)
where column_value <=100';
DBMS_OUTPUT.PUT_LINE(L_SQL);
EXECUTE IMMEDIATE L_SQL
INTO L_RES
USING P_LIST;
DBMS_OUTPUT.PUT_LINE(L_RES);
END;
END PKG_TEST;
-- SQL优化
--CTRL + E 本机执行的SQL
--SELECT * FROM V$SQL; 执行的SQL
--Hint
SELECT * FROM TEST1;
--索引 Unique 唯一索引 Normal 正常型B树 Bitmap 位图索引
-- 创建主键 默认 创建 唯一索引
/*+INDEX(T IDX_TEST_01) LEADING(T)*/ T为表名，IDX_TEST_01索z引名
--create bitmap index index_name on 表名(字段名);
CREATE BITMAP INDEX index_test ON TEST1(C2); --创建位图索引
CREATE INDEX index_test ON TEST1(C1); --创建正常型 B树索引
DROP INDEX index_test; --删除索引
--SELECT /*+ INDEX(T index_test)*/ T.* FROM TEST1 TWHERE T.C1 = 'A';
--SELECT /*+ LEADING(T) */ T.* FROM TEST1 T; --在多表关联查询中，指定哪个表作为驱动表，即告诉优化器首先要访问哪个表上的数据。
--SELECT /*+parallel(T 4)*/ T.* FROM TEST T; --开并行
SELECT /*+full(A) use_hash(A,B) leading(B)*/
A.CODE
FROM TEST A, TEST2 B, TEST3 M --leading 先查询那一个表，最好先查小表
/* 二．oracle访问数据的存取方法
1） 全表扫描（Full Table Scans， FTS）
2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
3）索引扫描（Index Scan或index lookup）有4种类型的索引扫描：
（1） 索引唯一扫描（index unique scan）
（2） 索引范围扫描（index range scan）
在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况：
（a） 在唯一索引列上使用了range操作符（> < <> >= <= between）
（b） 在组合索引上，只使用部分列进行查询，导致查询出多行
（c） 对非唯一索引列上进行的任何查询。
（3） 索引全扫描（index full scan）
（4） 索引快速扫描（index fast full scan）
跳跃式索引(Skip Scan Index)。
INDEX (FAST FULL SCAN)和INDEX (FULL SCAN)的区别:前者不会按照索引的顺序执行，因此不保证查询结果按照索引排序。而后者是根据索引本身的顺序进行扫描。
三、表之间的连接
1，排序 - - 合并连接（Sort Merge Join， SMJ）
2，嵌套循环（Nested Loops， NL）
3，哈希连接（Hash Join， HJ）
另外，笛卡儿乘积（Cartesian Product）*/
/* nested loops适用于一大一小表。其中内表是小表，每取一次值，然后与外表匹配。
若内表复杂度为n，外表复杂度为m，则整体为O(n*m)
hash join 适用于大表关联。若内表复杂度为n，外表复杂度为m，则整体为O(n+m) */
SELECT TO_CHAR(SYSDATE-5,'yyyy-MM-dd hh24:mm:ss') from dual;
SELECT TO_date('2014-06-10 12:05:22','yyyy-MM-dd HH24:mi:ss') from dual;
SELECT j.*,j.rowid FROM DBA_JOBS J WHERE J.WHAT LIKE 'PRO_SIC_TEST%' --查看过程执行JOB的时间
SELECT * FROM DBA_JOBS_RUNNING T WHERE T.JOB = 19197; --查找JOB
--创建 JOB脚本
DECLARE
JOBNO NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT(JOB => JOBNO,
WHAT => 'PKG_TEST.PRO_STY_TEST(SYSDATE);',
NEXT_DATE => SYSDATE + 1/24,
INTERVAL => 'TRUNC(SYSDATE+1)+11/24');
COMMIT;
END;
SELECT SUBSTR('ABCDE',2,3) FROM DUAL;
SELECT LENGTH('12345') FROM DUAL;
SELECT LPAD('B',10,'-') FROM DUAL;
SELECT RPAD('B',10,'-') FROM DUAL;
--查看Session
select *
from gv$process s, Gv$session gn
where s.ADDR = gn.PADDR
and gn.STATUS = 'ACTIVE'
and gn.SCHEMANAME = 'EXB4' --所有者
AND SID = 1159
select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='表名'; --查看表的大小
--Oracle SQL 数据字典
ALL_TABLE_COMMENTS Oracle 数据字典表 可能通过模糊查询，来查询表的注释
Oracle查询表的名字和comments
select a.table_name,b.comments from user_tables a,ALL_TAB_COMMENTS b where a.table_name=b.table_name
SELECT * FROM ALL_TAB_COMMENTS T WHERE T.COMMENTS LIKE '%客户%';
--Oracle 创建Job
DECLARE
JOBNO NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT(JOB => JOBNO,
WHAT => 'PRO_MONTH(TRUNC(ADD_MONTHS(SYSDATE,-1),''MM''),TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))));',
NEXT_DATE => ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) + 5 +
3 / 24,
INTERVAL => 'ADD_MONTHS(TRUNC(SYSDATE,''MM''),1)+5+ 3/24');
COMMIT;
END;
-----Oralce OVER()函数
--ROW_NUMBER() OVER()
SELECT *
FROM (SELECT EMP.*,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ROWNUM) CN
FROM EMP)
WHERE CN = 1;
--MAX() OVER()
SELECT MAX(T.NAME) OVER(),T.* FROM EMP T WHERE T.CODE_NO = '762151550'
--CONNECT BY
SELECT 1 FROM DUAL CONNECT BY ROWNUM <= 5
SELECT DECODE(LEVEL, 1, '1', '2'),LEVEL FROM DUAL CONNECT BY LEVEL <= 5
INSERT /*+APPEND */ INTO TEST (A,B,C,D) SELECT AA,BB,CC,DD FROM T;
--Oracle Function
--L_CURSTR_CODE VARCHAR2(30) := 'SE';
FUNCTION FNT_GEN_NO(P_HEAD_CHAR VARCHAR2,
P_DATE DATE,
P_DEPT_CODE VARCHAR2,
P_CUR_CODE VARCHAR2) RETURN VARCHAR2 IS
L_CE_NO VARCHAR2(30);
BEGIN
SELECT P_HEAD_CHAR || TO_CHAR(P_DATE, 'YYMM') ||
DECODE(P_CUR_CODE,
L_CURSTR_CODE,
LPAD(SEQ_MON_INV.NEXTVAL, 9, '0'),
LPAD(SEQ_ABROAD_INV.NEXTVAL, 9, '0'))
INTO L_CE_NO
FROM DUAL;
RETURN L_CE_NO;
EXCEPTION
WHEN OTHERS THEN
RETURN '000000000000';
END FNT_GEN_NO;
