//递归父子信息sql
select c.catalogid, c.lastmodifytime ,c.parentcatalogid
from T_E_CATALOG c
CONNECT BY PRIOR c.CATALOGID = c.PARENTCATALOGID
START WITH C.PARENTCATALOGID = :CATALOGID
//递归父子信息sql
SELECT t.catalogid,t.parentcatalogId,t.type
FROM T_E_CATALOG t
--where t.type = '0'
CONNECT BY prior t.parentcatalogId = t.catalogId
START WITH t.CATALOGID = 100006
//merge operation
MERGE INTO t_us_rank_result a
USING (select count(1) co
from t_us_rank_result
where contentId = :contentId) b
ON (b.co <> 0)
WHEN MATCHED THEN
UPDATE
SET a.rank = :avgRank,
a.count = :count,
a.lastModifyTime = sysdate
where a.contentId = :contentId
WHEN NOT MATCHED THEN
INSERT
(contentId, rank, count, lastModifyTime)
VALUES
(:contentId, :avgRank, :count, sysdate)
//添加字段
alter table egy_us_failbill add(SENDTIME TIMESTAMP(6) WITH TIME ZONE,PRODUCTINFO VARCHAR2(100));
//删除字段
alter table egy_us_failbill drop (sendtime1,productinfo2);
//改变字段
alter table egy_us_failbill rename column sendtime_temp to sendtime;
//diff between %type and %rowtype in oracle
-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
//procedure
create or replace procedure P_RETURN_COMMON_CATALOGID(in_catalogId in varchar2,
out_comCatalogId out varchar2) is
v_type VARCHAR2(1);
cursor v_cursor(i_catalogId varchar2) is
select t.type from t_e_catalog t where t.catalogId = i_catalogId;
--exception when no_data_found then return;
begin
out_comCatalogId := null;
open v_cursor(in_catalogId);
loop
fetch v_cursor
into v_type;
if v_cursor%found then
if v_type = '3' then
select t.catalogId
into out_comCatalogId
from t_e_catalog t
where t.type = '0'
and rownum = 1
connect by prior t.parentCatalogId = catalogId
start with t.catalogId = in_catalogId;
else
select t.catalogId
into out_comCatalogId
from t_e_catalog t
where t.type in ('0', '1', '2', '4')
and rownum = 1
connect by prior t.parentCatalogId = catalogId
start with t.catalogId = in_catalogId;
end if;
else
exit;
end if;
end loop;
close v_cursor;
end P_RETURN_COMMON_CATALOGID;
//trigger
CREATE OR REPLACE TRIGGER DUPLICATE_EMP
AFTER UPDATE OR INSERT OR DELETE ON egy_us_balance
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO egy_us_balance_bak(ACCOUNT,BALANCE,LASTMODIFIED)
VALUES
(:new.account, :new.balance, sysdate);
ELSIF DELETING THEN
DELETE FROM egy_us_balance_bak t WHERE t.account = :old.account;
ELSE
UPDATE egy_us_balance_bak t
SET t.account = :new.account, t.balance = :new.balance
WHERE t.account = :old.account;
END IF;
END;
//procedure
create or replace procedure P_XL_RANK_PURCHASEDCOMMEND as
cursor curCatalog is(
-- 查询专区ID不为9999，并且内容正常的专区、内容、产品
select catalogId
from t_e_catalog
where catalogId != 9999
and catalogId != -1
and catalogId != 0);
begin
-- 首先清空xl_rank_purchasedcommend
EXECUTE IMMEDIATE 'truncate table xl_rank_purchasedcommend';
for curCatalogTmp in curCatalog loop
dbms_output.put_line(curCatalogTmp.catalogId);
-- 单个专区 top10内容
insert into xl_rank_purchasedcommend
(catalogid, contentid, count, offsetcount, rankdate)
select curCatalogTmp.catalogId, e.*, '0', sysdate
from (select *
from (select c.contentId, count(pc.productId) count
from t_e_catalog2content cc,
t_e_content c,
t_e_product2content pc,
t_e_productinfo p,
t_us_subscribeinfo s
where 1 = 1
and cc.contentid = c.contentid
and c.contentid = pc.contentid
and pc.productid = s.productid
and pc.productid = p.productid
and cc.catalogid = curCatalogTmp.catalogId
and cc.status = '0' -- 专区和内容的关系正常
and c.status = '1' -- 内容正常
and pc.status = 0 --内容和产品的关系正常
and p.status = 1 --对应的产品当前有效
and p.validfrom <= sysdate
and p.validto >= sysdate
group by c.contentId) d
order by d.count desc) e
where rownum <= 10;
commit;
end loop;
exception
when others then
dbms_output.put_line(Sqlerrm);
ROLLBACK;
end P_XL_RANK_PURCHASEDCOMMEND;
//mysql procedure
proc:begin
declare vAppId int unsigned;
declare vUserId int unsigned;
declare vStatus enum('nor', 'del','disable');
if(pIdOnMarket is not null) then
select id, userId, status into vAppId, vUserId, vStatus
from t_apps
where idOnMarket = pIdOnMarket
and systemId = pSystemId;
if(vAppId is not null) then
if(vUserId <> pUserId) then -- 不同用户创建相同ID的应用，报内部错误
select 205 into pRetCode;
leave proc;
else
select 201 into pRetCode;
leave proc;
end if;
update t_apps set
name = ifnull(pName, name),
platform = ifnull(pPlatform, platform),
transition = ifnull(pTransition, transition),
cycleTime = ifnull(pCycleTime, cycleTime),
location = ifnull(pLocation, location),
category = ifnull(pCategory, category),
status = 'nor'
where id = vAppId;
/* 判断是否修改成功 */
if row_count() <= 0 then
select 500 into pRetCode;
leave proc;
end if;
select 200 into pRetCode;
leave proc;
end if;
else
select replace(uuid(), '-', '') into pIdOnMarket;
end if;
insert into t_apps(
name,
systemId,
userId,
platform,
transition,
cycleTime,
location,
category,
idOnMarket)
values(
pName,
pSystemId,
pUserId,
pPlatform,
pTransition,
pCycleTime,
pLocation,
pCategory,
pIdOnMarket);
select @@Identity into vAppId;
if vAppId is null or vAppId <= 0 then /* 判断用户是否增加成功 */
select 500 into pRetCode;
leave proc;
end if;
insert into t_updatedApps(appId, action)
values(vAppId, 'refresh');
select 200 into pRetCode;
select vAppId as appId;
end
//mysql procedure about cursor ~~
-- Dumping structure for procedure hiad.prc_close_nw_task
DROP PROCEDURE IF EXISTS `prc_close_nw_task`;
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `prc_close_nw_task`(OUT `retcode` INT)
begin
declare v_f0 int;
declare flag boolean default true;
declare curt1 cursor for select task_id from t_task where status in('CREATIVE PENDING','RUN','AUDITED','AUDIT PENDING','REJECTED') and end_time<=date_sub(now(),interval 1 day);
declare curt2 cursor for select task_id from t_task where status in('CREATIVE PENDING','RUN','AUDITED','AUDIT PENDING','REJECTED') and delivery_model='GD' and impression_gain>=impression_goal;
declare continue handler for not found set flag = false;
open curt1;
fetch curt1 into v_f0;
while flag do
update t_task set status='DONE',modified_time=now() where task_id = v_f0;
update t_task set settlement='Y' where task_id = v_f0 and delivery_model='CPT' ;
commit;
fetch curt1 into v_f0;
end while;
close curt1;
set flag = true ;
open curt2;
fetch curt2 into v_f0;
while flag do
update t_task set status='DONE',modified_time=now() where task_id = v_f0;
commit;
fetch curt2 into v_f0;
end while;
close curt2;
call prc_close_nw_order();
set retcode = 0;
end//
DELIMITER ;
declare
val varchar2(80);
nolen int;
sublen int;
iter varchar(10);
begin
--select getserialid(23) into val from dual;
--dbms_output.put_line(val);
nolen := length(12345678);
sublen := nolen - 2;
for iter in 1 .. sublen loop
dbms_output.put_line(iter);
end loop;
end;
declare
varStr varchar2(1000);
varStr1 varchar2(100);
useIng int(1);
begin
useIng := 2;
varStr := 'select trunc(sysdate + 1 ) from dual where 2 = :1';
execute immediate varStr into varStr1 using useIng;
dbms_output.put_line(varStr1);
end;
declare
iter varchar2(20);
TYPE cur_type is ref cursor;
cur1 cur_type;
queryStr varchar2(200);
loopVal1 varchar2(20);
loopVal2 varchar2(20);
subStrs varchar2(100);
finalVal varchar2(100);
begin
queryStr := 'select 1, 2 from dual';
open cur1 for queryStr;
loop
fetch cur1 into loopVal1, loopVal2;
exit when cur1%notfound;
subStrs := 'select trunc(sysdate + 1 ) from dual where 1 = :1 and 2 = :2';
execute immediate subStrs into finalVal using loopVal1,loopVal2;
dbms_output.put_line(finalVal);
end loop;
if cur1%Isopen then
close cur1;
end if;
end;
//page effecienty
select *
from (select t.*, rownum rn
from test t
where rownum <= 3)
where rn > 1;
select * from test
where rowid in (
select rd
from (select t.*, rownum rn ,rowid rd
from test t
where rownum <= 3)
where rn > 1)
//找出重复数据用rowid
select e.rowid,e.*
from test_table e
where rowid not in (select min(rowid)
from test_table e2
where e.names = e2.names
and e.id = e2.id and
e.classes = e2.classes
)
//flashback
flashback table tablename to before drop [rename to tablename2];
select * from tab;
drop table test cascade constraints [purge];