Oracle Note01:(PL/SQL 基础)

begin
fnd_global.apps_initialize(user_id=>2356,resp_id=>53510,resp_appl_id=>201);
end;

 begin
  dbms_application_info.set_client_info(client_info=>'235');
 end;

begin
fnd_global.apps_initialize(user_id=>2673,resp_id=>53669,resp_appl_id=>201);
end;

--- RESP_ID
SELECT responsibility_id
FROM fnd_responsibility_tl
where LANGUAGE = USERENV ('LANG') and  responsibility_id = :$PROFILES$.RESP_ID

SELECT USERENV ('LANG') FROM DUAL
alter session set nls_language='AMERICAN'---change the language environment

alter session set optimizer_goal = all_rows
alter session set optimizer_goal = rule
alter session set sql_trace = true
alter table drop column name ;
---Function --
select trunc(sysdate),trunc(sysdate)+0.9999,sysdate from dual;  --  2007/7/7,
select trunc(1.23456789,5) from dual; --1.23456 
select floor(20.54) from dual;
select abs(-12345.123) from dual;
select least(1, 2, 3) from dual ;  -- get the min number, date or char
select greatest(1, 3, 2) from dual ;  -- get the max number, date or char
select to_char(sysdate,'ddd') from dual 几天
select to_char(sysdate,'mm') from dual 几月
select decode(a,b,c)
select round(10.356789,4) from dual -- si she wu ru huo ri qi jei qu
select power(2,3) from dual -- 2*2*2 

select round(mmt.primary_quantity,2) round1,
       lpad(to_char(round(mmt.primary_quantity,2),'9999999999.99'),20,' ')  tochar
from mtl_material_transactions mmt
WHERE ROWNUM < 10;
 
select sign(:a-:b) from dual;
select length(mmt.transaction_uom) from mtl_material_transactions mmt
select rtrim(substr('asdfghjkl',1,5)),ltrim(substr('asdfghjkl',1,5)) from dual
select 1 from dual where ltrim('123852','0123456789') is null
select 1 from dual where ltrim('12385x2','0123456789') is null
SELECT NVL2 (REPLACE (NVL (TRANSLATE (LTRIM (RTRIM ('2234.4345')),
                                      '.0123456789',
                                      '00000000000'
                                     ),
                           'a'
                          ),
                      '0',
                      ''
                     ),
             'no',
             'yes'
            ) isnumber
  FROM DUAL
---Out Put --
dbms_output.put_line();
fnd_file.put_line(fnd_file.output,'XML');

-- 2 function
CREATE OR REPLACE FUNCTION f_isnumber(str1 IN VARCHAR2) RETURN NUMBER
IS
 vNumber NUMBER(38,0);
BEGIN
vNumber:=TO_NUMBER(str1);
 RETURN(1);
EXCEPTION
WHEN OTHERS THEN
 RETURN(0);
END F_ISNUMBER;
SELECT f_isnumber('12'),f_isnumber('12a') FROM dual
-- version 10
select * from test where regexp_like(name,'^[0-9A-Za-z]+$');
'^[0-9]+$',    '^[0-9A-Za-z]+$'

SELECT   (  LENGTH ('oracle vs sap! so, oracle is a winner!')
          - LENGTH (REPLACE ('oracle vs sap! so, oracle is a winner!',
                             'oracle',
                             ''
                            )
                   )
         )
       / LENGTH ('oracle') string_cnt
  FROM DUAL; --How much "subString" appear in "String". But must divide the length of "subString"

SELECT SUBSTR
          ('\\it-49\Share\cookie\55555,647437.txt',
             INSTR
                 ('\\it-49\Share\cookie\55555,647437.txt',
                  '\',
                  1,
                  (  (  LENGTH ('\\it-49\Share\cookie\55555,647437.txt')
                      - LENGTH
                            (REPLACE ('\\it-49\Share\cookie\55555,647437.txt',
                                      '\',
                                      ''
                                     )
                            )
                     )
                   / LENGTH ('\')
                  )
                 )
           + 1,
             INSTR ('\\it-49\Share\cookie\55555,647437.txt', ',')
           - INSTR
                 ('\\it-49\Share\cookie\55555,647437.txt',
                  '\',
                  1,
                  (  (  LENGTH ('\\it-49\Share\cookie\55555,647437.txt')
                      - LENGTH
                            (REPLACE ('\\it-49\Share\cookie\55555,647437.txt',
                                      '\',
                                      ''
                                     )
                            )
                     )
                   / LENGTH ('\')
                  )
                 )
           - 1
          ) COUNT
  FROM DUAL

select VENDOR_NAME ,max(Length(VENDOR_NAME))
from po_vendors
group by vendor_name
having length(VENDOR_NAME) = max(Length(VENDOR_NAME))
order by max(Length(VENDOR_NAME)) desc

union / union all / minus

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE  '%CUST%'

SELECT MAX(ROWNUM) FROM table_name  ---find the rownum of a table

select add_months(trunc(sysdate,'MM'),-1) date_m1,
add_months(sysdate, -1) date_m2,
trunc(sysdate,'MM')    date_m3, trunc(sysdate,'YYYY'), trunc(sysdate,'DD'),
trunc(sysdate,'MM')-1  date_m4,
trunc(sysdate) date_m5, -- "trunc" select year, month and date from a date.
sysdate,
to_char(sysdate, 'YYYY-MM'), to_char(sysdate, 'MM'),
extract(year from sysdate) date_m6,
to_date(extract(year from sysdate)||'-'||extract(month from sysdate), 'YYYY-MM') date_m7
from dual
where to_char(sysdate, 'YYYY-MM') =
to_char(add_months(TO_DATE('2008-NOV-01', 'YYYY-MM-DD'), -1), 'YYYY-MM')

SELECT code_combination_id, segment2, segment3, segment4,
       (CASE
           WHEN (   segment3 || segment4 BETWEEN '70111501' AND '70111513'
                 OR segment3 || segment4 BETWEEN '70311601' AND '70311603'
                )
              THEN 'Seg'
           WHEN segment3 || segment4 IN ('71310000', '71320000')
              THEN 14
           ELSE NULL
        END
       ) case_if  ---"case ...when...then...else...end", it is just like "if...then...else...end if"
  FROM gl_code_combinations
 WHERE segment2 IN ('0311', '0360', '0362' )

--how to judge a column having char '1' ~ '9' and 'A' ~ 'Z' . the best measure is function "regexp_instr" 
select * from tbl where regexp_instr(col,'[^0-9A-Z]')=0 AND LENGTH(col)=8

----  get the week day 
select   trunc (to_date ('17/03/2009', 'DD/MM/YYYY'))
       - trunc (to_date ('09/03/2009', 'DD/MM/YYYY'), 'D') week1,
    to_char(to_date ('07/03/2009', 'DD/MM/YYYY'), 'D') week2,
       round (to_date ('09/03/2009', 'DD/MM/YYYY'), 'D') round,
       trunc (to_date ('09/03/2009', 'DD/MM/YYYY'), 'D') trunc
  from dual

insert into anny_test_oct (row_id, user_id, user_name, user_title, dept_id) values(15, 16, 'Test', 'clerk', 3);

delete from BG_EOL_LINES where EOL_LINE_ID = 10011 ;

select nvl(t.item_sub_type, 'Report') type_1,
                   decode(t.item_sub_type,
                          null,
                          'Report Total',
                          t.item_sub_type || ' Product Total:') Type_2,
                   sum(t.schedule_quantity) QTY,
                   sum(t.next_wk_qty) Next_WK_QTY,
                   count(nvl(t.item_sub_type, 'ZZZ')) * 2 rec_num
              FROM BG_MDS_DETAL_TEMP t
             group by rollup(t.item_sub_type)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-630930/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-630930/