ITPub博客

SQL> create or replace Function RevStr(P_str in varchar2) return varchar2 is 2 l_RevStr varchar2(2000); 3 begin 4 for x in 1 .. length(P_str) loop 5 l_RevStr := substr(P_str, x, 1) || l_RevStr; 6 end loop; 7 return l_RevStr; 8 end; 9 /函数已创建。SQL> select RevStr('高级会员') from dual;REVSTR('高级会员')--------------------------------------------------------------------------------员会级高[@more@]

declare l_Lnum number default 1; l_Sdrow number default 0; l_Edrow number default 0; begin l_Sdrow :=&Sdrow; l_Edrow :=&Edrow; while l_Lnum <= trunc(l_Sdrow/l_Edrow) loop delete from test1 where rownum <=l_Edrow; commit; l_Lnum :=l_Lnum + 1; end loop; if mod(l_Sdrow,l_Edrow) <&gt

explain plan for select * from acct_ly.OP_BILL_RENT_DETAIL_ITEM_T a /*set a.vip_flag=9 */where exists (select 1 from acct_ly.target_cust_level b where a.acct_id=b.acct_id )select * from table(dbms_xplan.display()) ------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | ||* 1 | FILTER | | | | || 2 | TABLE ACCE

Explain plan for select acct_id, acc_nbr, serv_name, service_type, region_id_serv, acct_month, charge_meter, charge_trunk, charge_data, charge_other, late_fee, bad_charge, ture_charge, charge_project, staff_name, bank_name, cust_level from (select acct_id, acc_nbr, serv_name, service_type, region_id_serv, acct_month,

--UnixMemory: 2617704K (860220K) real, 2774560K (942392K) virtual, 38440908K free Page# 1/19CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND 2 ? 20219 oracle 234 20 6390M 5384K run 31:02 100.13 99.96 oracletest1 --select * from v$session where paddr in (select addr from v$process where spid = &Pid)--20219--select * from v$session_wait where sid = 5050 383 db file sequential read file# 119 0000000000000077 block# 35698 0000000000008B72 blocks 1 000

--中间表使用，不适用于最终表，否则可能影响查询计划create table hxz_test_2 parallel(degree 4) nologging as select * from serv_t;--56.296create index idx_hxz_test_2 on hxz_test_2(serv_id) parallel(degree 4) nologging;--------------------------------------------------------------------------------------------create index idx_hxz_test_2 on hxz_test_2(ioid_id0) /*parallel(degree 4)*/ nologging;--208.219create index idx_hxz_test_3 on hxz_test_2(ioid_id0) parallel(degree 8) nologging;--30.344--select

--------------------------------------------------------------------------------------------------select a.Approve_Serial_Nbr, a.Approve_Type, a.approve_flag, a.acct_id, a.Approve_charge, a.Approve_Staff_Id, a.Approve_Site_Id, a.Approve_User_Name, a.Approve_reason, to_char(a.approve_date, 'mm/dd hh24:mi'), a.Used_flag, b.acc_nbr, b.approve_value, b.acct_month, b.acct_item_type_id from bill_cc.approve_serial_t a,

declarecursor c1(p_id int) is select * from t where id=p_id ;beginopen c1( 1) ;end; ---------------------------create table hxz_01 (a int);insert into hxz_01 values(1);insert into hxz_01 values(2);insert into hxz_01 values(2);insert into hxz_01 values(3);commit;/set serveroutput ondeclare cursor l_cursor(p int) is select * from hxz_01 where a = p; l_tmp hxz_01%rowtype; begin open l_cursor(2); loop fetch l_cursor into l_tmp; exit when l_cursor%notfound; dbms_output.put_li

declare cursor c is select * from hxz_01; l_rec hxz_01%rowtype; begin open c; loop fetch c into l_rec; exit when c%notfound; dbms_output.put_line(l_rec.a); end loop; close c; end; declare cursor c is select * from hxz_01; TYPE fetch_array IS TABLE OF hxz_01%ROWTYPE; l_rec fetch_array; begin open c; fetch c bulk collect into l_rec; for i in 1..l_rec.count loop dbms_output.put_line(l_rec(i).a); end loop; close c; end;