oracle知识汇总

oracle实现导出命令

 

exp 用户名/密码@数据库实例 file=/usr/oracle/orcl20140107.dmp owner=表空间

 

oracle数据类型

 

LONG: 可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，可以存储长文本一个表中最多一个LONG列

LONG RAW: 可变长二进制数据，最长2G

CLOB:  字符大对象Clob 用来存储单字节的字符数据

NCLOB: 用来存储多字节的字符数据

BLOB: 用于存储二进制数据

BFILE: 存储在文件中的二进制数据，这个文件中的数据只能被只读访。但该文件不包含在数据库内。

bfile字段实际的文件存储在文件系统中,字段中存储的是文件定位指针.bfile对oracle来说是只读的,也不参与事务性控制和数据恢复.

CLOB，NCLOB，BLOB都是内部的LOB(Large Object)类型，最长4G，没有LONG只能有一列的限制

要保存图片、文本文件、Word文件各自最好用哪种数据类型?BLOB最好，LONG　RAW也不错，但Long是oracle将要废弃的类型，因此建议用BLOB。

oracle设计规范

oracle从表名称 = 主表+下划杠 + 从表表名称

oracle不要在存储过程、自定义函数、程序包中使用ddl语言

oracle表主键都是key命名而不是以ID命名

oracle主键字段会自增长

oracle 数组类型 is Table of

oracle申明变量不需要定义字段类型方式

 

declare  
    v_startDate bsc_schyear_term .startdate%type; 
    v_endDate  bsc_schyear_term.endDate%type; 
begin  
这种情况下定义变量，也不要自己定义类型了
直接用表返回对应字段类型

在oracle中

select field1, field2 into args1, args2 from tableName

以上查询没有值时会报错，因此需要加上exception 方式避免出错

SELECT STARTDATE, ENDDATE
INTO v_termStartDate, v_termEndDate
FROM TableTerm
WHERE schoolKey= v_schoolKey AND termKey= v_termKey; 
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_termStartDate := Pkg_Stm_Date.Fn_GetCurrentDate();
v_termEndDate   := Pkg_Stm_Date.Fn_GetCurrentDate();

 

oracle打印字句

 

dbms_output.put_line(v_num);

 

oracle 变量赋值

 

SELECT STARTDATE, ENDDATE
INTO v_termStartDate, v_termEndDate
FROM TableTermList
WHERE DATAAREAID = v_schoolKey AND TERMTYPEKEY = v_termKey; 

 

oracle调用程序包中的存储过程方法

1、call 程序包.存储过程名称(:args1,:arg2);

2、execute 程序包.存储过程(:args1,args2);

oracle生成连续日期的数据集

 

SELECT TO_CHAR(CurrDate, 'YYYYMMDD') AS Date_WID,

       TO_CHAR(CurrDate, 'YYYY') AS Year,

       TO_CHAR(CurrDate, 'YYYY')||'-Q'||TO_CHAR(CurrDate, 'Q') AS Quarter,

       TO_CHAR(CurrDate, 'YYYY-MM') AS Month,

       TO_CHAR(CurrDate, 'MM') AS Month_Num,

       TO_CHAR(CurrDate, 'Mon','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') AS Month_Short,

       TO_CHAR(CurrDate, 'WW') AS Week,

       TO_CHAR(CurrDate, 'Day') as Week_Day,

       TO_CHAR(CurrDate, 'YYYY-MM-DD') AS Full_Date

  FROM 
(
        select level n,

        TO_DATE('2010-12-31', 'YYYY-MM-DD') + NUMTODSINTERVAL(level, 'day') CurrDate

        from dual

        connect by level <= 5000
);

 

oracle常用工具

获取表：

 

select table_name from user_tables; //当前用户的表      

select table_name from all_tables; //所有用户的表  

select table_name from dba_tables; //包括系统表

select table_name from dba_tables where owner='用户名' 

user_tables：

table_name,tablespace_name,last_analyzed等

dba_tables：

ower,table_name,tablespace_name,last_analyzed等

all_tables：

ower,table_name,tablespace_name,last_analyzed等

all_objects：

ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

获取表字段：

select * from user_tab_columns where Table_Name='用户表';

select * from all_tab_columns where Table_Name='用户表';

select * from dba_tab_columns where Table_Name='用户表'; 

user_tab_columns：

table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

all_tab_columns ：

ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

dba_tab_columns：

ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

获取表注释：

select * from user_tab_comments

user_tab_comments：table_name,table_type,comments

--相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。

获取字段注释：

select * from user_col_comments

user_col_comments：table_name,column_name,comments

oracle中调用不带输出参数的存储过程方法

 

begin
     call/execute 程序包名.存储过程名称(输入参数列表)；
end;

 

oracle中调用带输出参数的存储过程方法

 

/*
v_out_a 和 v_out_b均为输出参数 
*/
declare 
    v_out_a pls_integral;
    v_out_b varchar2;
begin
    程序包名.存储过程名称(v_out_a,v_out_b varchar2)
end;

 

oracle中日期转换为中文星期

 

select to_char(sysdate,'day','nls_date_language=''simplified chinese''') from dual;

oracle打印

 

dbms_output.put_line(sysdate);

oracle中中文作为列名称必须加上双引号

oracle动态sql的使用方法

 

Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;

 

 

Declare 
    l_sql        Varchar2(500);
    l_PrimaryKey Pls_Integer := 1;
Begin
    --动态sql中使用占位符
    l_sql := 'select * from tableName where primaryKey := 1';
    --使用using绑定参数
    Execute Immediate l_sql Using l_PrimaryKey;
End ;

 

oracle 返回游标方式

 

    Procedure SP_TCH_Procedure(
        P_SchoolKey     In Varchar2        Default '',
        P_TermKey       In Varchar2        Default '0',
        P_GradeKey      In Varchar2        Default '0',
        cursorResult    Out                Sys_refcursor
    )

 

执行动态sql

 

    If Length(v_sql) > 0 Then 
        Open cursorResult For v_sql using P_SchoolKey, P_TermKey, P_Gradekey; 
    End If;	 

 

执行动态sql有以下方式

动态SQL的实现可以用：dbms_sql、execute immediate、open...for...(用于游标)

--动态sql读取字段值
execute immediate 'select name,salary from emp where id=:1 ' using p_id returning into v_name,v_salary;

oracle中type定义与使用方法

1、使用create进行创建

 

CREATE OR REPLACE TYPE Tbl_StrSplit IS TABLE OF VARCHAR2 (32767);  

使用该种方法创建类型后，在程序包、函数、存储过程中无需进行定义可以直接使用

 

2、在程序包中定义

 

TYPE Rec_Date       Is Table Of Varchar2(20); 

 

使用该种方法若需要返回数据集，则只能使用管道方式调用

Function Fn_GetDateTable(P_StartDate Varchar2, P_EndDate Varchar2) Return Rec_Date Pipelined;

 

 

   --返回日期列表，以表数据方式显示可以进行表关联
   Function Fn_GetDateTable(P_StartDate Varchar2, P_EndDate Varchar2) Return Rec_Date Pipelined
   Is
   Begin
		For dateRow In 
		(
		    Select TO_DATE(P_StartDate, 'yyyy-mm-dd') + NUMTODSINTERVAL(Level, 'day') thisDate
			From dual
			Connect By Level <= To_Date(P_EndDate,'yyyy-mm-dd') - To_Date(P_StartDate, 'yyyy-mm-dd')
		) loop
		    Pipe Row(to_char(dateRow.thisDate,'yyyy-mm-dd'));
		End Loop;
		
		Return;   
   End Fn_GetDateTable;

       Pls_Integer 与number两个数据类型的区别

1. pls_integer类型也是数字类型，但和number类型不同，number可以存储实数，而pls_integer只能存储-2147483647到+2147483647之间的整数，如果使用pls_integer类型时发生溢出，系统将会报错。
2. binary_integer与pls_integer类似，在9.2版本以前大量使用，从9.2以后，从Oracle内部一些组件可以看的出，大有被pls_integer取代之势(pls_integer比binary_integer具有更少的存储开销和更好的访问性能，所以Oracle从9.2以后推荐你尽量能使用pls_integer就使用pls_integer)。它也是只能存储-2147483647到+2147483647之间的整数。
3. 在oracle 11g中，又增加了一个新的类似的数据类型simple_integer,不过simple_integer不能包含空值，它的取值范围是[-2147483648..2147483647]。在11g中，simple_integer相对pls_integer在性能上又有所提高，如果在实际的pl/sql中既不需要overflow检查也不会包含null值，Oracle建议你使用simple_integer.

oracle隐式cursor,会自动打开关闭。

 

open storage_cur for select * from t_storage t where t.user_id = userid;

Oracle代码加密工具

ORACLE 提供了一个实用工具来加密或者包装用户的PL/SQL，它会将用户的PL/SQL改变为只有ORACLE能够解释的代码版本．

WRAP 实用工具位于$ORACLE_HOME/BIN.

 格式为：

WRAP INAME=<input_file_name> [ONAME=<output_file_name>]

wrap iname=e:\sample.txt

查看程序包的代码内容

 

select text from user_source wherename='程序包名称';

oracle实现多条记录分区块合并指定字段的值的实现命令

 

        select subjTypeKey,SubjectOrder,subjectName,wm_concat(teacherName) name 
        from VW_TCH_TEACHERSUBJECTPROPERTY
        group by subjTypeKey,SubjectOrder,subjectName
        order by SubjectOrder;

 

oracle中实现随机读取方法

 

        select dictkey 
        from vw_stm_dict_subjtype_r 
        order by dbms_random.value 

 

oracle中实现top命令方法

 

    select dictkey
    from 
    ( 
        select dictkey 
        from vw_stm_dict_subjtype_r 
        order by dbms_random.value 

    ) where rownum <= 1