compare
Posted by TQ on 八月 23rd, 20091 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | CREATE OR REPLACE PROCEDURE del_UserContent(phoneNum VARCHAR2, oresultcode OUT INT) IS memberId NUMBER; --用户id cnt INTEGER; BEGIN oresultcode := 0; --删除临时注册表中的信息 SELECT COUNT(*) INTO cnt FROM member_reg t WHERE t.phone=phoneNum; IF cnt>0 THEN BEGIN DELETE FROM member_reg t WHERE t.phone=phoneNum; END; END IF; SELECT member_id INTO memberId FROM member WHERE phone = phoneNum; --删除所有好友和群组的映射关系 DELETE FROM grp_friend_rel t WHERE t.MEMBER_ID=memberId; --删除所有群组信息 DELETE FROM friend_grp t WHERE t.member_id=memberId; --删除所有好友信息 DELETE FROM friend t WHERE t.member_id = memberId; --删除定制信息中包含的彩像信息 DELETE FROM winks_pkg t WHERE t.member_id = memberId; --删除定制信息 DELETE FROM winks_script t WHERE t.member_id = memberId; --删除我的彩像库中的信息 DELETE FROM member_winks t WHERE t.member_id=memberId; --删除彩像库中本用户diy上传的彩像文件 --delete from winks_file t where t.winks_id in (select w.winks_id from winks w where w.owner_id=memberId); --删除彩像库中本用户diy上传的彩像信息 --delete from winks t where t.owner_id=memberId; UPDATE winks t SET t.owner_type=2 , t.owner_id=10001 WHERE t.owner_id=memberId; --删除订单明细信息 DELETE FROM line_item t WHERE t.contract_id IN (SELECT a.contract_id FROM contract a WHERE a.buy_account_id=memberId); DELETE FROM line_item t WHERE t.contract_id IN (SELECT a.contract_id FROM contract a WHERE a.sell_account_id=memberId); --删除订单信息 DELETE FROM contract t WHERE t.buy_account_id=memberId; DELETE FROM contract t WHERE t.sell_account_id=memberId; --删除注册激活日志信息 DELETE FROM member_scr_log t WHERE t.member_id=memberId; --删除用户客户端配置信息 DELETE FROM member_client t WHERE t.member_id=memberId; --删除推荐用户信息 DELETE FROM member_invited t WHERE t.member_id=memberId; --删除社区元素明细信息 DELETE FROM member_action t WHERE t.invited_member_action_id IN (SELECT a.member_action_id FROM member_action a WHERE a.member_id=memberId); DELETE FROM member_action t WHERE t.member_id=memberId; --更新member表中推荐人信息 UPDATE member t SET t.invited_by = NULL WHERE t.invited_by = memberId; COMMIT; --删除修改密码的信息 DELETE FROM member_at_site t WHERE t.member_id=memberId; --删除用户信息 DELETE FROM member t WHERE t.member_id=memberId; --删除此用户相关消息 DELETE FROM message t WHERE t.party_id=memberId OR t.send_from=memberId; --删除参与者信息 DELETE FROM party t WHERE t.party_id=memberId; IF SQL%NOTFOUND THEN ROLLBACK; DBMS_OUTPUT.put_line('NO SUCH USER'); oresultcode := -21000; RETURN; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; oresultcode := SQLCODE; END; / |
———————————————————————
CREATE OR REPLACE PROCEDURE del_UserContent(phoneNum VARCHAR2, oresultcode OUT INT) IS memberId NUMBER; --用户id cnt INTEGER; BEGIN oresultcode := 0; --删除临时注册表中的信息 select count(*) into cnt from member_reg t where t.phone=phoneNum; IF cnt>0 THEN BEGIN delete from member_reg t where t.phone=phoneNum; END; END IF; select member_id into memberId from member WHERE phone = phoneNum; --删除所有好友和群组的映射关系 delete from grp_friend_rel t where t.MEMBER_ID=memberId; --删除所有群组信息 delete from friend_grp t where t.member_id=memberId; --删除所有好友信息 delete from friend t where t.member_id = memberId; --删除定制信息中包含的彩像信息 delete from winks_pkg t where t.member_id = memberId; --删除定制信息 delete from winks_script t where t.member_id = memberId; --删除我的彩像库中的信息 delete from member_winks t where t.member_id=memberId; --删除彩像库中本用户diy上传的彩像文件 --delete from winks_file t where t.winks_id in (select w.winks_id from winks w where w.owner_id=memberId); --删除彩像库中本用户diy上传的彩像信息 --delete from winks t where t.owner_id=memberId; update winks t set t.owner_type=2 , t.owner_id=10001 where t.owner_id=memberId; --删除订单明细信息 delete from line_item t where t.contract_id in (select a.contract_id from contract a where a.buy_account_id=memberId); delete from line_item t where t.contract_id in (select a.contract_id from contract a where a.sell_account_id=memberId); --删除订单信息 delete from contract t where t.buy_account_id=memberId; delete from contract t where t.sell_account_id=memberId; --删除注册激活日志信息 delete from member_scr_log t where t.member_id=memberId; --删除用户客户端配置信息 delete from member_client t where t.member_id=memberId; --删除推荐用户信息 delete from member_invited t where t.member_id=memberId; --删除社区元素明细信息 delete from member_action t where t.invited_member_action_id in (select a.member_action_id from member_action a where a.member_id=memberId); delete from member_action t where t.member_id=memberId; --更新member表中推荐人信息 update member t set t.invited_by = null where t.invited_by = memberId; commit; --删除修改密码的信息 delete from member_at_site t where t.member_id=memberId; --删除用户信息 delete from member t where t.member_id=memberId; --删除此用户相关消息 delete from message t where t.party_id=memberId or t.send_from=memberId; --删除参与者信息 delete from party t where t.party_id=memberId; IF SQL%NOTFOUND THEN ROLLBACK; dbms_output.put_line('NO SUCH USER'); oresultcode := -21000; RETURN; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; oresultcode := sqlcode; END; /
Google Syntax Highlighter for WordPress
Posted by TQ on 八月 23rd, 2009winks@CCDB> select name,ceil(bytes/1024/1024) MB,status from v$datafile; NAME MB STATUS ---------------------------------------------------------------------- ---------- -------------- /home/oracle/app/oracle/oradata/ccdb/system01.dbf 730 SYSTEM /home/oracle/app/oracle/oradata/ccdb/sysaux01.dbf 1037 ONLINE /home/oracle/app/oracle/oradata/ccdb/undotbs01.dbf 440 ONLINE /home/oracle/app/oracle/oradata/ccdb/users01.dbf 5 ONLINE /home/oracle/app/oracle/oradata/ccdb/example01.dbf 100 ONLINE /home/oracle/app/oracle/product/11.1.0/db_1/dbs/USER_DATA_01.DBF 5 ONLINE /home/oracle/app/oracle/product/11.1.0/db_1/dbs/WINKS_DATA_01.DBF 62 ONLINE /home/oracle/app/oracle/oradata/ccdb/WINKS_DATA_EN_01.DBF 37 ONLINE 已选择8行。
escaped="true"
winks@CCDB> set autotrace traceonly explain winks@CCDB> select * from member; 执行计划 ---------------------------------------------------------- Plan hash value: 3441279308 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1358 | 224K| 13 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| MEMBER | 1358 | 224K| 13 (0)| 00:00:01 | ----------------------------------------------------------------------------
winks@CCDB> set autotrace traceonly statistics winks@CCDB> select * from member_client; 已选择1363行。 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 149 consistent gets 0 physical reads 0 redo size 380811 bytes sent via SQL*Net to client 1354 bytes received via SQL*Net from client 92 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1363 rows processed
测试wp-syntax
Posted by TQ on 八月 23rd, 2009Example 1: PHP, no line numbers
<div id="foo"> <?php function foo() { echo "Hello World!\\n"; } ?> </div>
Example 2: Java, with line numbers
1 2 3 4 5 | public class Hello { public static void main(String[] args) { System.out.println("Hello World!"); } } |
Example 3: Ruby, with line numbers starting at 18
18 19 20 21 22 | class Example def example(arg1) return "Hello: " + arg1.to_s end end |
Example 4: If your code already has html entities escaped, use escaped=”true” as an option
<xml>Hello</xml>
winks@CCDB> SELECT name,CEIL(bytes/1024/1024) MB,status FROM v$datafile; NAME MB STATUS ---------------------------------------------------------------------- ---------- -------------- /home/oracle/app/oracle/oradata/ccdb/system01.dbf 730 SYSTEM /home/oracle/app/oracle/oradata/ccdb/sysaux01.dbf 1037 ONLINE /home/oracle/app/oracle/oradata/ccdb/undotbs01.dbf 440 ONLINE /home/oracle/app/oracle/oradata/ccdb/users01.dbf 5 ONLINE /home/oracle/app/oracle/oradata/ccdb/example01.dbf 100 ONLINE /home/oracle/app/oracle/product/11.1.0/db_1/dbs/USER_DATA_01.DBF 5 ONLINE /home/oracle/app/oracle/product/11.1.0/db_1/dbs/WINKS_DATA_01.DBF 62 ONLINE /home/oracle/app/oracle/oradata/ccdb/WINKS_DATA_EN_01.DBF 37 ONLINE 已选择8行。
escaped=”true”
1 2 3 4 5 6 7 8 9 10 11 12 13 | winks@CCDB> SET autotrace traceonly EXPLAIN winks@CCDB> SELECT * FROM member; 执行计划 ---------------------------------------------------------- PLAN hash VALUE: 3441279308 ---------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1358 | 224K| 13 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| MEMBER | 1358 | 224K| 13 (0)| 00:00:01 | ---------------------------------------------------------------------------- |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | winks@CCDB> SET autotrace traceonly statistics winks@CCDB> SELECT * FROM member_client; 已选择1363行。 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 149 consistent gets 0 physical reads 0 redo size 380811 bytes sent via SQL*Net TO client 1354 bytes received via SQL*Net FROM client 92 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 1363 rows processed |
Oracle 创建只读用户
Posted by TQ on 八月 20th, 2009set pages 999; set heading off; spool d:\run_grant.sql select 'grant select on '||owner||'.'||object_name|| ' to user1;' from dba_objects where object_type='TABLE' and owner='SCOTT'; spool off;
Recent Comments