oracle相关设置

服务端运行(字符)

select userenv('language') from dual; 

修改db编码的办法：（在oracle 11g上通过测试）
SQL> conn system as sysdba;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;
SQL> alter database character set internal_use AL32UTF8;
SQL> shutdown immediate;
SQL> startup 

 最后再记录几个常用的oracle sql命令：

给用户(hr)解锁：

alter user hr account unlock;

修改用户(hr)密码(新密码为hr):

alter user hr identified by hr;

授权(emp)表的select权限给用户(hr):

grant select on emp to hr;

取消授权:

remove select on emp from hr;

用户权限

create userEMFUND

  identified by EMFUND;

  grant connect to EMFUND;

  grant resource to EMFUND;

  grant dba to EMFUND;

grant connect,resource,dba to zcs;

表空间

create tablespace PAYIDX

datafile 'E:/app/PAYIDX.dbf'

size 150M

autoextend on next 5M maxsize 3000M

数据库启动

lsnrctl status

 

net start OracleDBConsolezcs

net start OracleOraDb11g_home1TNSListener

net start OracleServiceZCS

lsnrctl start

 

net stop OracleDBConsolezcs

net stop OracleOraDb11g_home1TNSListener

net stop OracleServiceZCS

lsnrctl stop

序列

CREATESEQUENCE "PAYDEV"."TB_CUSTOMERROUTER_ID_SEQ" MINVALUE1MAXVALUE9999999999999999999999999999INCREMENTBY1STARTWITH1CACHE20NOORDER NOCYCLE ;

触发器

createorreplaceTRIGGER PAYDEV."TB_CUSTOMERROUTER_TRG"BEFOREINSERTON TB_CUSTOMERROUTER

FOREACHROW

DECLARE

v_newVal NUMBER(12) :=0;

v_incval NUMBER(12) :=0;

BEGIN

  IF INSERTINGAND :new.c_idISNULLTHEN

    SELECT  TB_CUSTOMERROUTER_ID_SEQ.NEXTVALINTO v_newValFROM DUAL;

    -- If this is the first time this table havebeen inserted into (sequence == 1)

    IF v_newVal =1THEN

      --get the max indentity valuefrom the table

      SELECTNVL(max(c_id),0)INTO v_newVal FROM TB_CUSTOMERROUTER;

      v_newVal := v_newVal + 1;

      --set the sequence to thatvalue

      LOOP

           EXITWHEN v_incval>=v_newVal;

           SELECTTB_CUSTOMERROUTER_ID_SEQ.nextvalINTO v_incvalFROM dual;

      ENDLOOP;

    ENDIF;

    --used to emulate LAST_INSERT_ID()

    --mysql_utilities.identity := v_newVal;

   -- assign the value from the sequence toemulate the identity column

   :new.id := v_newVal;

  ENDIF;

END;

Oracle函数

row_number() OVER(PARTITIONBY)

 

根据BindID 排序  ACCOUNTNO分组，记录每条数据在一组数据中的排序情况

Merge into

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。

通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，

连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。

这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。

实例:

merge into users

using doctor

on (users.user_id = doctor.doctorid)

when matched then

  update setusers.user_name = doctor.doctorname

when not matched then

  insert

  values

   (doctor.doctorid,

    doctor.doctorid,

    '8736F1C243E3B14941A59FF736E1B5A8',

     doctor.doctorname,

     sysdate,

     'T',

     ' ',

    doctor.deptid,

    'b319dac7-2c5c-496a-bc36-7f3e1cc066b8');

SYS_CONNECT_BY_PATH

拼接某些规则数据（红色可以使用递归分组拼接）

selectconnect_by_iscycle,sys_connect_by_path(child,"/") pathfrom hier2start with parent is null connectby nocycle prior child =parent

group by hier2.name;

Connect by（属性数据结构）

拼接某些规则数据（红色可以使用递归分组拼接）

selectconnect_by_iscycle,sys_connect_by_path(child,"/") pathfrom hier2start with parent is null connectby nocycle prior child =parent

group by hier2.name;

Guid

sys_guid()

java语言用到UUID.randomUUID()

wm_concat函数

select wm_concat(C_CHANNELNO) from TB_CERTTYPEMAPPING

截取所有的C_CHANNELNO

存储过程异常信息+换行+哪行错误

DECLARE

  V_RATENUMBER;--比例

  V_RETINFOVARCHAR2(2000);--异常信息

BEGIN

 

  SELECT 120/0INTO V_RATE FROM DUAL;

 

  --异常处理

  EXCEPTION WHENOTHERS THEN

  V_RETINFO :=SQLERRM||CHR(10)||SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,1,1000);--获取异常信息及异常被抛出的初始位置[行]

 DBMS_OUTPUT.PUT_LINE(V_RETINFO);

END;

 

 

本机sqlplus

exp和 imp导入导出

1、EXP: (忽略 ignore=y )

      有三种主要的方式（完全、用户、表）

      1、完全：

          EXPSYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y

          如果要执行完全导出，必须具有特殊的权限

      2、用户模式：

          EXPSONIC/SONIC    BUFFER=64000FILE=C:\SONIC.DMP OWNER=SONIC

          这样用户SONIC的所有对象被输出到文件中。

      3、表模式：

          EXPSONIC/SONIC    BUFFER=64000FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)

          这样用户SONIC的表SONIC就被导出

    2、IMP:

      具有三种模式（完全、用户、表）

      1、完全：

          IMPSYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y

      2、用户模式：

          IMPSONIC/SONIC    BUFFER=64000FILE=C:\SONIC.DMP FROMUSER=SONIC TOUSER=SONIC

          这样用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据。

      3、表模式：

          EXPSONIC/SONIC    BUFFER=64000FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)

          这样用户SONIC的表SONIC就被导入。

 

 PL/SQL Developer过期的情况

1.输入指令“regedit”打开注册表

2.在注册表里按HKEY_CURRENT_USER\Software\AllroundAutomations 这个路径找到“Allround Automations ”，然后删除它。

3.删除上一步中的后，在找到HKEY_CURRENT_USER\Software\Microsoft\Security，删除“Security”。

4.这时候关闭注册表，在重新登陆PL/SQLDeveloper，这时候就可以重新用30天了。

windows plsql oracle 乱码

以前在进行oracle存储过程的编写过程中，当写中文语句的时候，对存储过程进行编译成功后，再重新打开这个文档，发现中文出现了乱码。这真是件抓狂的事情啊！在网上查找，很多信息抓住不到关键点，其实解决乱码的问题很简单，需要我们的PL/SQL Oracle客户端的字符集编码和Oracle数据库的字符集编码保持一致。

 一：查看oracle数据库的字符集编码：

       select * fromnls_database_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY','NLS_CHARACTERSET');

       那么就需要我们修改ALS_LANG=AMERICAN_AMERICA_AL32UTF8

二：修改Oracle客户端的ALS_LANG的设置：

在电脑->属性->高级->环境变量->系统变量中新建：

变量名：NLS_LANG
变量值：ALS_LANG=AMERICAN_AMERICA_AL32UTF8