1、创建序列语句
-- Create sequence
create sequence GLOBAL_PKID
minvalue 100000000001
maxvalue 999999999999
start with 100000006008
increment by 1
nocache;
2、创建方法语句
CREATE OR REPLACE FUNCTION GET_GLOBAL_PKID RETURN VARCHAR2 IS
RESULT VARCHAR2(24);
BEGIN
SELECT global_PKID.Nextval INTO RESULT FROM DUAL;
RETURN(RESULT);
END GET_GLOBAL_PKID;
3、分割年月方法
SELECT D.mark_name,
D.TIMES,
D.VALUE,
D.ljz,
D.PARENT_ID,
D.PARENT_NAME,
CASE
WHEN (VALUE IS NULL AND ljz IS NULL AND LENGTH(TIMES) < 8) THEN
SUBSTR(D.TIMES, '6', '2')
WHEN VALUE IS NULL THEN
SUBSTR(D.TIMES, '8', '2')
WHEN VALUE IS NOT NULL THEN
SUBSTR(D.TIMES, '6', '2')
END MONTH
FROM ztyx D
4、逐行相减存储过程
CREATE OR REPLACE PROCEDURE CALC_SMONTH_V(T_NAME IN VARCHAR2,
ITEM_ID IN VARCHAR2,
YEARS IN VARCHAR2,
MONTHS IN VARCHAR2,
SMONTH_V OUT VARCHAR2) IS
BEGIN
V_SQL VARCHAR2(4000); --动态SQL语句
V_SUB_SQL
V_SQL :=
SELECT (SELECT T.T_VALUE
FROM DEMO_TGR T
WHERE T.M_ID = '1000001374'
AND T.YEARS = '2006'
AND T.MONTHS = '3') -
(SELECT TT.T_VALUE
FROM DEMO_TGR TT
WHERE TT.M_ID = '1000001374'
AND TT.YEARS = '2006'
AND TT.MONTHS = '2') "months"
FROM DUAL
EXECUTE IMMEDIATE V_SQL INTO SMONTH_V FROM DUAL;
SELECT
END CALC_SMONTH_V;
5、逐行相减函数
CREATE OR REPLACE FUNCTION GET_VALUE(T_NAME IN VARCHAR2,
ITEM_ID IN VARCHAR2,
YEARS IN VARCHAR2,
MONTHS IN VARCHAR2) RETURN VARCHAR2 IS
RESULT VARCHAR2(256);
V_SQL VARCHAR2(4000);
V_MONTHS INTEGER(8);
BEGIN
V_MONTHS := TO_NUMBER(MONTHS) - 1;
V_SQL := ' SELECT (SELECT T.LJZ FROM ' || T_NAME ||
' T WHERE T.MARK_ID =' || ITEM_ID || ' AND T.YEAR=' || YEARS ||
' AND T.MONTH=' || MONTHS || ')-(SELECT TT.LJZ FROM ' ||
T_NAME || ' TT WHERE TT.MARK_ID =' || ITEM_ID ||
' AND TT.YEAR=' || YEARS || ' AND TT.MONTH=' || V_MONTHS ||
') FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO RESULT;
RETURN(RESULT);
END GET_VALUE;
6、查询英文表名、注释和数据条数的语句
SELECT a.table_name,a.comments,b.num_rows FROM User_Tab_Comments a,user_tables b WHERE
a.table_name=b.table_name ORDER BY a.table_name
7、游标+存储过程批量修改表的数据
CREATE OR REPLACE PROCEDURE UPDATE_DATE IS
CURSOR USER_RS IS
SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME;
CURSOR TABLE_RS(TAB_NAME VARCHAR2) IS
SELECT COUNT(*)
FROM USER_TAB_COLUMNS COL
WHERE COL.TABLE_NAME = TAB_NAME
AND COL.COLUMN_NAME = 'CN_ITEM';
V_SQL VARCHAR2(2000);
V_TEMP NUMBER; -- 临时变量
BEGIN
FOR USER_REC IN USER_RS LOOP
OPEN TABLE_RS(USER_REC.TABLE_NAME);
LOOP
FETCH TABLE_RS
INTO V_TEMP;
EXIT WHEN TABLE_RS%NOTFOUND;
IF 1 = V_TEMP THEN
V_SQL := 'UPDATE ' || USER_REC.TABLE_NAME ||
' SET CN_ITEM = REPLACE(CN_ITEM, '' '', '''')';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('1');
ELSE
V_SQL := 'UPDATE ' || USER_REC.TABLE_NAME ||
' SET NAME = REPLACE(NAME,'' '', ''''),
PARENT_NAME = REPLACE(PARENT_NAME,'' '','''')';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE(' 2 ');
END IF;
END LOOP;
CLOSE TABLE_RS;
END LOOP;
END;
8.地税行业排名
创建视图语句
CREATEORREPLACEVIEW V_BG_DSSRFHYFSZ_ITEM_DIM AS
SELECT "PKID","ITEMID","YEARS","TAX","ACCOUNTS","UNIT","TIMESTAMPS","MONTHS"
FROM BG_DSSRFHYFSZ_FACTS T
WHERE T.ITEMID IN (SELECT D2.NAME
FROM BG_DSSRFHYFSZ_ITEM_DIM D2
WHERE D2.PARENTNAME IN
(SELECT D3.NAME
FROM BG_DSSRFHYFSZ_ITEM_DIM D3
WHERE D3.PARENTNAME = '合计')
)
;
排名语句
SELECTSUM(accounts) AS ACCOUNT_S,MAX(itemid) AS ITEMID FROM v_bg_dssrfhyfsz_item_dim WHERE YEARS=2010 AND MONTHS=3 GROUPBY itemid ORDERBY ACCOUNT_S
9.运用动态生成游标的存储过程
CREATE OR REPLACE PROCEDURE P_PM(V_YEAR IN VARCHAR2,V_MONTH IN VARCHAR2) IS
V_SQL VARCHAR2(2000);
V_ITEM VARCHAR2(255);
V_VAL VARCHAR2(255);
V_CUR_SQL VARCHAR2(2000);
type cur_type isrefcursor; --定义一个游标类型
CUR cur_type; --定义一个游标变量
BEGIN
V_CUR_SQL:='SELECT ITEMID FROM V_BG_DSSRFHYFSZ_ITEM_DIM_T WHERE YEARS ='||V_YEAR||' AND MONTHS = '||V_MONTH||' ORDER BY ACCOUNTS DESC';
OPEN CUR FOR V_CUR_SQL;
V_VAL := 1;
LOOP
FETCH CUR
INTO V_ITEM;
EXIT WHEN CUR%NOTFOUND;
V_SQL := 'UPDATE bg_dssrfhyfsz_facts F SET PM=' || V_VAL ||
' WHERE F.YEARS='||V_YEAR||' AND F.MONTHS='||V_MONTH||' AND TAX=''合计'' AND F.ITEMID='''|| V_ITEM ||'''';
--DBMS_OUTPUT.PUT_LINE(FRACT_ROW.CREATED);
EXECUTE IMMEDIATE V_SQL;
V_VAL :=V_VAL+1;
COMMIT;
END LOOP;
CLOSE CUR;
END P_PM;
10.根据一个表的内容区更新另一张表的数据
UPDATE BG_DSYYSFSMFQYLX_FACTS A
SET A.TAX = (SELECT B.NAME
FROM BG_DSYYSFSMFQYLX_TAX_DIM B
WHERE A.TAX = B.TAX),
ENTTYPE = (SELECT C.NAME
FROM BG_DSYYSFSMFQYLX_ENTTYPE_DIM C
WHERE A.ENTTYPE = C.ENTTYPE)
UPDATE B_SGZCBHTBA A SET A.TENDERERICNO = (SELECT DISTINCT B.ZZJGDM
FROM V_QYZZ B
WHERE A.TENDERERMAN=B.QYMC(+)
)
WHERE A.ID IN (SELECTIDFROM v_qyyj_false WHERE 企业名称 IN (SELECT QYMC FROM V_QYZZ))
11.oracle中用for循环调用存储过程
BEGIN
FOR years IN 2009..2010 LOOP
FOR months IN 1..12 LOOP
P_PM(years,months);
END LOOP;
END LOOP;
END;
12.讲一个表的数据插入到另一张表
INSERT INTO
BG_DSSRFHYFSZ_FACTS(PKID, ITEMID, YEARS, MONTHS, TAX, UNIT, ACCOUNTS)
(SELECT GLOBAL_PKID.NEXTVAL, ITEMID,YEARS,MONTHS,'合计',UNIT,ACCOUNTS FROM V_BG_DSSRFHYFSZ_ITEM_DIM_T)
13.ORALCE删除重复数据的方法语句
a.查找重复记录的方法
select name,age,count(*) from test group by name,age having count(*)>1
b.删除重复记录的方法
delete test where rowid not in(select min(rowid) from test group by name,age);
真正的删除重复记录的方法
deletefrom syzl_ds_wcqk_r a
where a.rowid !=
(
selectmax(b.rowid) from syzl_ds_wcqk_r b
where a.item_id = b.item_id and
a.name = b.name
)
C．查找所有重复的数据语句
SELECT * FROM RYXX_ZCJZS_NEW WHERE (xm,zjhm,pyqy) IN (
SELECT xm,zjhm,pyqy FROM RYXX_ZCJZS_NEW GROUP BY xm,zjhm,pyqy havingcount(*)>1) ORDERBY zjhm
14.oracle的替换字符函数
A．replace（’abcdef’,’ab’,’cd’）将字符串abcdef中的ab替换成cd，针对字符串的操作
B．translate（char,from,to）
返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。
若from比to字符串长，那么在from中比to中多出的字符将会被删除。
三个参数中有一个是空，返回值也将是空值。
15.oracle的rank()和dense_rank()函数
A.rank()的使用
Rank（）不仅有分析的功能，而且还有求合计的功能;
语法为：select a.*,rank() over(partition by col2 order by col1) rank from table a;
说明：列出col2分组后根据col1排序，并生成数字列。
例子：查询每门功课前几天名的分数
Select * from (select rank() over (partition by 科目 order by 分数 desc) rank,a.* from a) t where t.rk<=3;
例子：合计功能-计算出数值（4，1）在order by col1，col2排序下的排序值，也就是col1=4，col2=1在排序后的位置
Select rank(4,1) within group (order by col1,col2) rank from table;
16.oracle的rowtype和type的区别
Rowtype：记录每行的信息
Type：记录某一列的信息
例：create table student(id varchar2(3),name varchar2(32));
declare
t_record student%rowtype;
c_record student.name%type;
begin
select * into t_record from student where id='1';
select name into c_record from student where id='1';
dbms_output.put_line(t_record.id || ',' || t_record.name || ',' || c_record);
end;
17. 卸载Oracle的实现方法
1、开始->设置->控制面板->管理工具->服务停止所有Oracle服务;
2、开始->程序->Oracle – OraDb10g_home1>Oracle Installation Products-> Universal Installer 卸装所有Oracle产品，但Universal Installer本身不能被删除;
3、运行regedit，选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口;
4、运行regedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滚动这个列表，删除所有Oracle入口;
5、运行refedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application，删除所有Oracle入口;
6、开始->设置->控制面板->系统->高级->环境变量,删除环境变量CLASSPATH和PATH中有关Oracle的设定;
7、从桌面上、STARTUP(启动)组、程序菜单中，删除所有有关Oracle的组和图标;
8、删除c:\Program Files\Oracle目录;
9、重新启动计算机，重起后才能完全删除Oracle所在目录 ;
10、删除与Oracle有关的文件，选择Oracle所在的缺省目录C:\Oracle，删除这个入口目录及所有子目录，并从Windows XP目录(一般为d:\WINDOWS)下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等;
11、WIN.INI文件中若有[ORACLE]的标记段，删除该段;
12、如有必要，删除所有Oracle相关的ODBC的DSN;
13、到事件查看器中，删除Oracle相关的日志 说明：如果有个别DLL文件无法删除的情况，则不用理会，重新启动，开始新的安装，安装时，选择一个新的目录，则，安装完毕并重新启动后，老的目录及文件就可以删除掉了。
18.给另一个用户赋予访问方法权限的语句
GRANT EXECUTE ON [USER_A].FUNCTION TO [USER_B]
19.oracle的子代父代互相钻取方法
由父代钻取到所有的子代
SELECT TopicID,ParentID,TopicTitle FROM T_TOPIC CONNECT BY PRIOR TopicID = ParentID START WITH TopciID = 1
解析：topicid为子代的ID，parentid为父代的id，topictitle为子代的名称，1代表子代的id值
由子代钻取到所有父代
SELECT TopicID,ParentID,TopicTitle FROM T_TOPIC CONNECT BY TopicID = PRIOR ParentID START WITH TopicID = 10
规律：PRIOR位于谁的前面，谁就为先，泪如a中以他的子代节点为先，就会钻取所有子代，b中以父代节点为先，就会钻取所有的父代节点
20 为数据库用户分配表空间限额的方法
经查,表空间跟表空间限额两个值是不一样的.
推测按默认的话oracle应该会给每个用户分配一个默认的表空间限额,具体比例待查,但这比例肯定远小于100%.
所以说分配了400M的表空间未必能存储400M的数据.
解决办法如下:
查看用户表空间的限额
select * from user_ts_quotas;
max_bytes字段就是了
-1是代表没有限制,其它值多少就是多少了.
不对用户做表空间限额控制:
GRANT UNLIMITED TABLESPACE TO ***(用户);
这种方式是全局性的.
或者
alter user ***(用户名) quota unlimited on ***(表空间);
这种方式是针对特定的表空间的.
可以分配自然也可以回收了:
revoke unlimited tablespace from ***(用户)
或者
alter user *** quota 0 on ***
21 得到oracle某个用户下的所有表和注释
SELECT t.table_name,tt.comments FROM USER_TABLES t,User_Tab_Comments tt WHERE t.table_name=tt.table_name；
22.不区分大小写剔除字符串的方法
create or replace function StrReplace(
n_Str IN varchar2, -- 目标字符串
n_Search IN varchar2, -- 搜索的字符串
n_Replace IN varchar2) -- 替换成字串
-----------------------------------------------------------------------
-- 用 途 不区分大小写的字符替换函数。
-- 创建者 ：Andrew
-----------------------------------------------------------------------
-- 修改记录列表：（按日期的先后顺序逆序排列）
-- 修改时间 修改人 实现的功能说明
-----------------------------------------------------------------------
return varchar2
is
v_Result varchar2(2000); -- 返回结果
v_Pos number; -- sSearch在sStr中的位置
v_Len number; -- sSearch的长度
begin
v_Result := n_Str;
v_Len := length(n_Search);
v_Pos := instr(Upper(v_Result), Upper(n_Search));
while v_Pos <> 0 loop
v_Result := SubStr(v_Result, 1, v_Pos-1) || n_Replace || SubStr(v_Result, v_Pos+v_Len, 2000);
v_Pos := instr(Upper(v_Result), Upper(n_Search), v_Pos+v_Len);
end loop;
return(v_Result);
end StrReplace;
23 将全角字符转换成半角的问题
to_single_byte(to_char(sfzhm))
24 ORACLE 的not in 中的子查询中不能有null值
25 oracle的语句优化原则
1. 使用表的别名(Alias)
当在SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
(Column歧义指的是由于SQL中不同的表具有相同的Column名，当SQL语句中出现这个Column时，SQL解析器无法判断这个Column的归属)
2. 用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下， 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。
低效：
SELECT * FROM EMP(基础表) WHERE EMPNO>0 AND DEPTNO IN
(SELECT DEPTNO FROM DEPT WHERE LOC=’MELB’)
高效：
SELECT * FROM EMP(基础表) WHERE EMPNO>0 AND EXISTS (SELECT ‘X’ FROM DEPT
WEHRE DEPT.DEPTNO=EMP.DEPTNO AND LOC=’MELB’)
(相对来说，用NOT EXISTS替换NOT IN 将更显著地提高效率，下面将指出)
3. 用NOT EXISTS替代NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例如：
SELECT * FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’)
为了提高效率。改写为：
(方法一： 高效)
SELECT * FROM EMP A,DEPT B WHERE A.DEPT_NO=B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+)=’A’
(方法二： 最高效)
SELECT * FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO=E.DEPT_NO AND DEPT_ CAT=’A’)
4. 用表连接替换EXISTS
通常来说 ， 采用表连接的方式比EXISTS更有效率
SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO=E.DEPT_NO AND DEPT_CAT=’A’)
(更高效)
SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO=D.DEPT_NO AND DEPT_CAT=’A’
(在RBO的情况下，前者的执行路径包括FILTER，后者使用NESTED LOOP)
5. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换
例如：
低效：
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO=E.DEPT_NO
高效：
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS(SELECT‘X’
FROM EMPE
WHEREE.DEPT_NO=D.DEPT_NO);
EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。
6. 识别‘低效执行’的SQL语句
用下列SQL工具找出低效SQL：
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0
ANDBUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC;
(虽然目前各种关于SQL优化的图形化工具层出不穷，但是写出自己的SQL工具来解决问题始终是一个最好的方法)
7. 使用TKPROF 工具来查询SQL性能状态
SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中。 这个跟踪文件提供了许多有用的信息，例如解析次数。执行次数，CPU使用时间等。这些数据将可以用来优化你的系统。
设置SQL TRACE在会话级别：
有效
ALTERSESSIONSETSQL_TRACETRUE
设置SQL TRACE 在整个数据库有效仿， 你必须将SQL_TRACE参数在init.ora中设为TRUE， USER_DUMP_DEST参数说明了生成跟踪文件的目录
(这里并没有提到TKPROF的用法， 对SQL TRACE的用法也不够准确， 设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS， 这样才能得到那些重要的时间状态。 生成的trace文件是不可读的，所以要用TKPROF工具对其进行转换，TKPROF有许多执行参数。 大家可以参考ORACLE手册来了解具体的配置。)