oracle常用内容

类型：char，nchar,varchar2(),nvarchar2,number(),date,blob(binary二进制流的大对象),clob(文件大对象)。

NVL函数 用于处理空值的影响 NVL(salary,0)

escape定义转义字符 where name like 'S\_%' escape '\'

lower函数

upper函数

initcap函数 首字符大些

concat函数 连接字符串 等效于||

substr函数 切割字符串

length函数

round函数四舍五入

trunc函数 截取，不管后面数字 trunc(5.3266,3)

to_char函数 格式转换

to_date

to_number

avg

count

max

min

sum

等值连接

自连接

外连接 a.id=b.id(+)  b表补空

创建表 create table 用户.表名 (字段名, 字段类型 约束条件);

主键约束 create table  a( c number primary key )列级主键约束 create table a (c number,primary key(c))表级约束 create table a(c number constraints pkc1 primary key)

外键约束 create table b(d number primary key ,c1 number references a(c)); 列约束 create table b(d number primary key,c1 number, foreign key(c1) references a(c));表约束

not null

check

查看表约束  desc user_constraints;  select constraint_name,constraint_type from user_constraints where table_name='s_EMP'

查看列约束 desc user_cons_columns; select   column_name,position from user_cons_columns  where constraint_type=‘R’ and table_name='a'

rownum分页显示

创建索引 create index 索引名 on 表(字段)

set transaction

编号自动增加 创建序列 create sequence check_no_seq nocycle maxvalue 9999999999  start with 2; 建立触发器 create or replace trigger set _checkup_no before insert on checkup_history for each row declare next_checkup_no number; begin --get the next checkup number from the sequence  select check_no_seq.nextval into next_checkup_no from dual;

 

create or replace procedure spaddflowdate
(
varAppTypeId               in varchar2,
varFlowId                  in varchar2,
DateLength                 in number,
ReturnValue                out number    --返回值
)
is
begin
insert into td values(varAppTypeId,varFlowId,DateLength)
returning 1 into ReturnValue;   --返回值
commit;
exception
when others then
rollback;
end;

 

create or replace function get_empname(v_id in number) return varchar2 as  

 v_name varchar2(50);   

begin  

select name into v_name from employee where id = v_id;   

return v_name;   

exception   

when no_data_found then  

raise_application_error(-20001, '你输入的ID无效！');   

end get_empname;  

 

 

--oracle连接oracle   
CREATE DATABASE LINK SRMAGENCY242 CONNECT TO jztsrm identified by jztsrm2012 using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.242)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME=SRM)
)
)'
--切割字符串
CREATE OR REPLACE TYPE TYPE_SPLIT AS TABLE OF VARCHAR2(500); --切割字符串后 字符表

CREATE OR REPLACE FUNCTION SPLIT
(
--切割字符串自定义函数
P_LIST VARCHAR2,--字符串
P_SEP VARCHAR2 := ','--切割字符
) RETURN TYPE_SPLIT PIPELINED
IS
L_IDX PLS_INTEGER;
V_LIST VARCHAR2(500) := P_LIST;
BEGIN
LOOP
L_IDX := INSTR(V_LIST,P_SEP);
IF L_IDX > 0 THEN
PIPE ROW(SUBSTR(V_LIST,1,L_IDX-1));
V_LIST := SUBSTR(V_LIST,L_IDX+LENGTH(P_SEP));
ELSE
PIPE ROW(V_LIST);
EXIT;
END IF;
END LOOP;
RETURN;
END SPLIT;
--测试
SELECT * FROM TABLE(SPLIT('1,2,3,4,5,6,',','));

/*FOR CUR_STR IN (SELECT * FROM TABLE(SPLIT('1,2,3,4,5,6,',','));)
         LOOP
         BEGIN
           DBMS_OUTPUT.PUT_LINE (CUR_STR.COLUMN_VALUE);
         END LOOP;*/
--查询分组后的最小行
SELECT FMEASUREUNIT,/* 计量单位 */FASSIGNMENTSDOWN,/* 任务量下限 */FASSIGNMENTSUP,/* 任务量上限 */FFULLOVER,/* 全额/超额 1全额 2超额 */
    FAWARDMODE,/* 奖励方式 1比例 2金额 */FREWARDAMOUNT,/* 奖励额度 */FINDEX,/* 排序字段 */FISTAX,/* 是否含税 0否 1是 */FISCJER,/* 是否冲价2 0否 1是 */
    FISJSCBM,/* 是否计算长编码 0否 1是 */FISQPZ,/* 是否全品种 0否 1是 */FSTARTDATE,/* 开始日期 */FENDDATE,/* 结束日期 */
    FISJGLGYS,/* 是否仅关联供应商 0否 1是 */FISTCSP,/* 是否剔除商品 0否 1是 */
    ROW_NUMBER() OVER (PARTITION BY FGROUP ORDER BY FINDEX) FINDEX
    FROM APP_PAC_GENERALPOLICY@SRMAGENCY242 WHERE FPOLICYTYPE=1 AND FINDEX=1 AND FSTATUS=2

   
--重新编译无效存储过程
CREATE OR REPLACE PROCEDURE PROC_COMPILEINVALIDPROC(
    P_OWNER VARCHAR2 -- 所有者名称，即 SCHEMA
) AS

--编译某个用户下的无效存储过程
STR_SQL VARCHAR2(200);

BEGIN
    FOR INVALID_PROCEDURES IN (SELECT OBJECT_NAME FROM ALL_OBJECTS
       WHERE STATUS = 'INVALID' AND OBJECT_TYPE = 'PROCEDURE' AND OWNER=UPPER(P_OWNER))
    LOOP
      STR_SQL := 'ALTER PROCEDURE ' ||INVALID_PROCEDURES.OBJECT_NAME || ' COMPILE';
      BEGIN
        EXECUTE IMMEDIATE STR_SQL;
       
        EXCEPTION
          WHEN OTHERS THEN NULL;
      END;
    END LOOP;
END PROC_COMPILEINVALIDPROC;

--查看版本及dblink信息
SELECT * FROM V$VERSION;
SELECT * FROM ALL_DB_LINKS ;

--结束会话session
select a.object_name objectname, 
           b.session_id, 
           c.serial#, 
           c.program     program, 
           c.username    username, 
           c.command, 
           c.machine  machine, 
           c.lockwait 
      from all_objects a, v$locked_object b, v$session c 
     where a.object_id = b.object_id 
       and c.sid = b.session_id; 
      
ALTER SYSTEM KILL SESSION '137,48863' IMMEDIATE;

--创建 定时作业
Declare SRMSJCKDATA number;
begin
dbms_job.submit(SRMSJCKDATA,'PROC_SRMHELPDATA;',TRUNC(sysdate) + 1 +20/24,'TRUNC(sysdate) + 1 +20/24');
commit;
end;

select * from user_jobs;

--dbms_job.broken()重新将broken标记为false
begin
     dbms_job.broken (:job,false,next_date)
end;