1.--NC建库 2.CREATE TABLESPACE NNC_DATA01 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 3.CREATE TABLESPACE NNC_DATA02 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_data02.dbf' SIZE 300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 4.CREATE TABLESPACE NNC_DATA03 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_data03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K ; 5.CREATE TABLESPACE NNC_INDEX01 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_index01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ; 6.CREATE TABLESPACE NNC_INDEX02 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_index02.dbf' SIZE 300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ; 7.CREATE TABLESPACE NNC_INDEX03 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_index03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 8.CREATE USER NC50 IDENTIFIED BY NC50 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp; 9.GRANT connect,dba to NC50; 10. 11. 12.--根据公司编码查询公司主键 13.select c.pk_corp from nc.bd_corp c where c.unitcode='3101' 14. 15.--根据凭证号、公司主键查询分录 16.select * 17. from nc.gl_detail d 18. where d.pk_voucher = '1004A11000000000H7P1' 19. and d.pk_corp = '1004' 20.--分录号 21.and d.detailindex=219 22. 23.--根据凭证主键查询作证号 24.select v.no from nc.gl_voucher v where v.pk_voucher='1004A11000000000H7P1' 25./* 26.select 27. acc.subjcode, 28. acc.subjname 29.from bd_accsubj acc, 30. bd_glorgbook orgbook, 31. bd_corp corp, 32. bd_glorg org 33.where acc.pk_glorgbook = orgbook.pk_glorgbook 34. and orgbook.pk_glorg = org.pk_glorg 35. and org.pk_entityorg = corp.pk_corp 36. and corp.unitcode = '5002' 37. and acc.subjcode = '221114' 38.*/ 39.--根据公司编码查询公司目录主键 40.select g.pk_glorg,g.* from bd_glorg g where g.glorgcode='5003' 41.--根据公司目录主键查询公司账薄主键 42.select bd_glorgbook.pk_glorgbook, bd_glorgbook.* 43. from bd_glorgbook 44. where bd_glorgbook.pk_glorg = '0001A1100000000ARH5O' 45. 46.select acc.subjcode, acc.subjname 47. from bd_accsubj acc 48. where acc.pk_glorgbook = '0001A1100000000ARHAZ' 49. 50.--根据公司和科目编码查询余额 51.select b.pk_balance,b.* 52. from bd_corp c, bd_accsubj a, gl_balance b 53. where a.pk_corp = c.pk_corp 54. and c.unitcode = '3101' 55. and a.subjcode = '260101' 56. and a.pk_accsubj = b.pk_accsubj 57. and a.dr = 0 58. and b.year='2010' 59. 60. 61.--根据科目编码查询辅助核算 62.select distinct info.bdname, info.* 63. from bd_subjass s, bd_accsubj a, bd_bdinfo info 64. where a.pk_accsubj = s.pk_accsubj 65. and s.pk_bdinfo = info.pk_bdinfo 66. and a.subjcode = '6601' 67. 68. select distinct bd_bdinfo.bdname 69. from bd_accsubj, bd_subjass, bd_bdinfo 70. where bd_accsubj.pk_accsubj = bd_subjass.pk_accsubj 71. and bd_subjass.pk_bdinfo = bd_bdinfo.pk_bdinfo 72. and (bd_accsubj.pk_corp = '0001' or bd_accsubj.pk_corp is null) 73. and nvl(bd_accsubj.dr, 0) = 0 74. and nvl(bd_accsubj.endflag, 'N') = 'Y' 75. and bd_accsubj.subjcode = '66013301' 76. 77. 78. 79. 80.--根据年度、会计期间、公司编码、凭证号查询分录 81.select v.no, v.pk_voucher, d.* 82. from gl_voucher v, gl_detail d, bd_corp c 83. where v.pk_voucher = d.pk_voucher 84. and v.pk_corp = c.pk_corp 85. and v.year = '2009' 86. and v.period = '08' 87. and c.unitcode = '1101' 88. and v.no = '251' 89. and v.dr = 0 90. 91.--根据年度、公司编码、凭证主键查询科目 92.select a.pk_accsubj, a.subjcode, a.subjname, d.localcreditamount 93. from gl_voucher v, bd_corp c, gl_detail d, bd_accsubj a 94. where v.year = '2010' 95. and c.unitcode = '3101' 96. and c.pk_corp = v.pk_corp 97. and v.pk_voucher = d.pk_voucher 98. and a.pk_accsubj = d.pk_accsubj 99. and d.pk_voucher = '1004AQ1000000001OV9K' 100. 101.--根据公司和科目编码查询科目主键 102.select pk_accsubj, g.glorgcode,acc.dispname, acc.* 103. from nc.bd_accsubj acc, nc.bd_glorgbook gb, nc.bd_glorg g 104. where acc.subjcode like '660105%' 105. and acc.pk_glorgbook = gb.pk_glorgbook 106. and gb.pk_glorg = g.pk_glorg 107. and g.glorgcode = '5101' 108. 109.---------------------------------------------------------- 110.--2010/1/6 查询总账期初余额 111.--subj.balanorient科目方向：1：借；2：贷 112.select subj.subjcode 科目编码, 113. subj.subjname 科目名称, 114. case subj.balanorient 115. when 1 then 116. sum(gl.localdebitamount) 117. when 2 then 118. sum(gl.localcreditamount) 119. end 期初余额 120. from nc.gl_detail gl, nc.bd_corp corp, nc.bd_accsubj subj 121. where gl.pk_corp = corp.pk_corp 122. and gl.pk_accsubj = subj.pk_accsubj 123. and corp.unitcode = '00' 124. and gl.yearv = '2009' 125. and gl.periodv = '00' 126. and gl.dr = 0 127. group by subj.subjcode, subj.subjname, subj.balanorient 128. order by subj.subjcode 129.---------------------------------------------------------- 130.--根据收支项目与公司编码取收支项目主键 131.select c.pk_costsubj 132. from nc.bd_costsubj c, nc.bd_corp corp 133. where c.costcode = 'C224104' 134. and c.pk_corp = corp.pk_corp 135. and corp.unitcode = '00' 136. 137.---------------------------------------------------------- 138.--查看汇率 139.select decode(currtypecode, 140. 'HKD', 141. '02', 142. 'USD', 143. '03', 144. 'EUR', 145. '12', 146. 'JPY', 147. '05', 148. '01') c_cur_no, 149. currtypecode, 150. currtypename, 151. substr(ratedate, 0, 7) rateperiod, 152. ratedate, 153. rate 154. from nc.bd_currtype, nc.bd_currrate, nc.bd_currinfo 155. where (bd_currtype.pk_currtype = bd_currinfo.pk_currtype) 156. and (bd_currinfo.pk_currinfo = bd_currrate.pk_currinfo) 157. and (bd_currrate.pk_corp = '0001') 158. --and (currtypecode = 'USD' and substr(ratedate, 0, 7) = '2009-12') 159. order by currtypecode, ratedate 160. 161. 162. 163.---------------------------------------------------------- 164.--停止后台任务 165.update pub_alertregistry p set p.enabled = 'N' where p.accountpk = '0001AA100000000324SZ' 166.---------------------------------------------------------- 167.--查询一个列属于哪个表 168.select table_name, column_name from user_tab_columns where column_name = 'DJDL'; 169.--查询一个表的列 170.select c.COLUMN_NAME from user_tab_columns c where c.TABLE_NAME = 'ARAP_DJZB' AND c.COLUMN_NAME like 'ZYX%' 171.select concat('a.',c.COLUMN_NAME) from user_tab_columns c where c.TABLE_NAME = 'ARAP_DJZB' AND c.COLUMN_NAME like 'ZYX%' 172. 173.select a.djdl,a.djlxbm,b.zyx2 from 174.arap_djzb a, arap_djfb b 175.where trim(a.djbh) = trim(b.djbh) 176.and length(b.zyx2)!=0 177.---------------------------------------------------------- 178.--查询单据模板上自定义项的配置情况 179.select t.bill_templetname 单据名称, 180. b.defaultshowname 显示名称, 181. b.itemkey 项目主键, 182. b.editformula 编辑公式, 183. decode(b.table_code,'main','表头','table','表体') 位置 184. from nc.pub_billtemplet t, nc.pub_billtemplet_b b 185. --pub_billtemplet t, pub_billtemplet_b b 186. where t.pk_corp = '0001' 187. and t.pk_billtemplet = b.pk_billtemplet 188. and (b.itemkey like 'zyx%' or instr(b.editformula,'zyx')<>0) 189. 190. select distinct f.zyx6 from nc.arap_djfb f 191.---------------------------------------------------------- 192.--Q:在会计平台科目分类定义节点通过复制的方式会同时复制对照表但目标单位生成凭证时对照表没有启作用 193.--原因是因为影响因素如表体科目还是源单位的，并没有自动转换成目标单位的 194.--科目分类定义 195.select * from dap_insubjclass s where s.insubjclasscode = 'EC34' and s.pk_corp = '1014'; 196. 197.select * from dap_insubjclassfactor f where f.pk_insubject = '1020AQ1000000000JSYQ'; 198.--科目对照表 199.select * from dap_subjview s where s.pk_insubject = '1020AQ1000000000JSYQ'; 200. 201.select * from bd_corp c where c.unitcode = '3301'; 202. 203.select * from bd_accsubj a where a.pk_accsubj = '1014AA100000000003B7'; 204. 205. 206.select corp.unitcode 公司编码, 207. corp.unitname 公司名称, 208. insubjclass.insubjclasscode 科目分类编码, 209. insubjclass.insubjclassname 科目分类名称, 210. v.factor1 表体科目PK, 211. v.factorname1 表体科目名称, 212. v.subjcode 入账科目PK 213. from bd_corp corp 214. left join dap_insubjclass insubjclass on corp.pk_corp = 215. insubjclass.pk_corp 216. left join dap_subjview v on insubjclass.pk_insubjclass = v.pk_insubject 217. where corp.unitcode = '5002' 218. and insubjclass.insubjclasscode = 'EC34'--低耗品科目 219.---------------------------------------------------------- 220. 221.--成本系数定义明细 222.select distinct corp.unitcode, 223. corp.unitname, 224. dept.deptname, 225. k.name, 226. c.coefficientname, 227. c.coefficientcode, 228. cb.value, 229. cb.ts 230. from nc.fc_coefficient_b cb, 231. nc.fc_coefficient c, 232. nc.bd_corp corp, 233. nc.bd_deptdoc dept, 234. nc.irp_insurancekind k 235. where cb.pk_coefficient = c.pk_coefficient 236. and cb.pk_dwbm = corp.pk_corp 237. and cb.pk_deptdoc = dept.pk_deptdoc 238. and cb.pk_object = k.pk_insurancekind 239. and cb.kjnd = '2010' 240. and cb.kjqj = '06' 241. --and c.coefficientcode = 25 242. 243. order by cb.ts desc; 244. 245. 246. 247.---------------------------------------------------------- 248. 249.--结账状态 250.--固定资产 251.select * from fa_closebook where pk_corp=1006 and accyear=2010; 252. 253.--总账 254.select * from gl_syssettled;--可以手动在此插入一条记录 255.select glorgbook.pk_glorgbook 主体账薄主键,glorgbook.glorgbookname 主体账薄名称 from bd_glorgbook glorgbook ,bd_glorg glorg 256.where glorgbook.pk_glorg = glorg.pk_glorg 257.and glorg.glorgcode = '8501'; 258. 259. 260.---------------------------------------------------------- 261. 262.---------------------------------------------------------- 263.--查询单据zyx是否有值 264.select distinct f.zyx1,z.djlxbm 265. from nc.arap_djzb z, nc.arap_djfb f 266. where 267. z.djbh = trim(f.djbh) 268. and z.djlxbm = '23A8' 269. 270.---------------------------------------------------------- 271. 272.--更新所有用户密码为1 273.update sm_user u set u.user_password = 'jlehfdffcfmohiag',u.pwdlevelcode = '' 274. 275.select * from sm_user u 276.where u.user_code in ('zbjjl','cqls','zbfuzhaofui','tbadmin','100000524'); 277.--锁定用户 278.update sm_user u set u.locked_tag = 'Y' 279.where u.user_code not in ('zbjjl','cqls','zbfuzhaofui','tbadmin','100000524'); 280. 281. 282.---------------------------------------------------------- 283.--结算方式 284.select * from nc.bd_balatype 285.---------------------------------------------------------- 286. 287.---单据查询时后台取出的一段语句 288.select pk_finindex, 289. pk_sys, 290. pk_proc, 291. pk_vouchentry, 292. gl_voucher.no voucherno, 293. procmsg, 294. gl_voucher.pk_vouchertype vouchertype, 295. bd_vouchertype.vouchtypename vouchtypename, 296. bd_glorg.pk_glorg, 297. bd_glorgbook.pk_glbook, 298. bd_glorg.glorgname glorg_name, 299. bd_glorgbook.glorgbookname glbook_name, 300. checked.user_name checked, 301. manager.user_name manager, 302. totaldebit, 303. totalcredit, 304. prepareddate, 305. period, 306. dap_finindex.pk_rtvouch, 307. dap_finindex.errmsg, 308. gl_voucher.tallydate 309. from dap_finindex, 310. gl_voucher, 311. bd_vouchertype, 312. sm_user prepared, 313. sm_user checked, 314. sm_user casher, 315. sm_user manager, 316. bd_glorg, 317. bd_glorgbook 318. where dap_finindex.pk_vouchentry = gl_voucher.pk_voucher(+) 319. and gl_voucher.pk_vouchertype = bd_vouchertype.pk_vouchertype(+) 320. and gl_voucher.pk_prepared = prepared.cUserId(+) 321. and gl_voucher.pk_checked = checked.cUserId(+) 322. and gl_voucher.pk_casher = casher.cUserId(+) 323. and gl_voucher.pk_manager = manager.cUserId(+) 324. and bd_glorg.pk_glorg(+) = dap_finindex.pk_glorg 325. and bd_glorgbook.pk_glbook(+) = dap_finindex.pk_glbook 326. and bd_glorgbook.pk_glorg(+) = dap_finindex.pk_glorg 327. and (procmsg in ('1001A1100000000CC547', '1001AQ100000000CB9H5', 328. '1001AQ100000000CB7HQ', '1001AQ100000000CC3RN', 329. '1001AQ100000000CB7D3', '1001AQ100000000CB7I8', 330. '1001AQ100000000CC3RT', '1001AQ100000000CC1BF', 331. '1001AQ100000000CC3KQ', '0001AQ100000004LI2BQ', 332. '1001A1100000000CB5M9', '1001AQ100000000CB7HX', 333. '1001A1100000000CB6F0', '1001AQ100000000CB7DU', 334. '1001A1100000000CC541', '1001AA100000000CBDC8', 335. '1001A1100000000CB6E4', '1001AQ100000000CB7XO', 336. '1001AQ100000000CC3KJ', '1001A1100000000CB6E1')) 337. 338.---------------------------------------------------------- 339. 340. 341.---------------------------------------------------------- 342. 343.--成本要素科目对照 344.select a.pk_glrapport, a.subjcode, b.subjname, a.itemcode, c.itemname 345. from fc_glrapport a 346. left join bd_accsubj b on a.subjcode = b.subjcode 347. left join fc_costitemdefine c on a.itemcode = c.itemcode 348. where (b.Pk_Glorgbook = '0001') 349. or (b.pk_corp is null And b.Pk_Glorgbook is null) 350. order by a.subjcode; 351. 352.select a.pk_glrapport, a.subjcode, b.subjname, a.itemcode, c.itemname 353. from fc_glrapport a, bd_accsubj b, fc_costitemdefine c 354. where a.subjcode = b.subjcode(+) 355. and a.itemcode = c.itemcode(+) 356. and ((b.Pk_Glorgbook = '0001') or 357. (b.pk_corp is null And b.Pk_Glorgbook is null)) 358. order by a.subjcode; 359.---------------------------------------------------------- 360.--凡是公司不设置本位币的公司 361.select * from pub_sysinit where initname like '%本位币%' and pkvalue is null; 362.--将凭证子表中的垃圾数据清理掉 363.delete from gl_detail d where not exists( select pk_voucher from gl_voucher v where v.pk_voucher=d.pk_voucher); 364.--检查会计平台还存在未处理的实时凭证 365.SELECT dap_finindex.*,settlecentername FROM dap_finindex left join bd_settlecenter on(dap_finindex.pk_corp=bd_settlecenter.pk_corp) WHERE flag = 2 AND pk_sys = 'FTS' AND destsystem=1 and nvl(dap_finindex.dr,0)=0 and nvl(bd_settlecenter.dr,0)=0; 366. 367. 368.---------------------------------------------------------- 369.---------------------------------------------------------- 370.---------------------------------------------------------- 371.---------------------------------------------------------- 372.---------------------------------------------------------- 373. 374.--检查审批流是否定义 375.select pk_wf_def, fatherpk, processDefID, node_name, node_type, billmaker, billmaker_name, billmaker_type, createdate, 376.version, workflow_type from pub_wf_def where validation = 1 and pk_corp = '1001' and busitype = 'KHHH0000000000000001' 377.and 378.billtype = 'ZG54' and ( node_type = '2' or node_type = '3' ) 379.---------------------------------------- 380.--单据类型管理表,手工设置4位类型代码 381.select * From bd_billtype where pk_billtypecode='TB' 382.update bd_billtype set pk_billtypecode='TB10' where pk_billtypecode='TB' 383.---------------------------------------- 384.--手工做vo对照_主表 385.insert into pub_votable(approveid, billid, billno, billvo, busitype, def1,def2, 386.def3,dr, headbodyflag,headitemvo, itemcode, operator, pk_billtype,pk_corp,pkfield, 387. votable, pk_votable, ts) 388. values('vapproveid','pk_apply','vbillcode','nc.vo.trade.pub.HYBillVO','pk_busitype',NULL,NULL,NULL, 389.NULL,'Y', 'nc.vo.shsh.stock.tb1010.StockApplyVO','shsh_stock_apply', 'voperatorid','TB10', NULL, 390. 'pk_apply','shsh_stock_apply','0001AA10000000001RK3','2007-09-27 16:59:14') 391.--手工做vo对照_子表 392.insert into pub_votable(approveid,billid,billno,billvo, busitype,def1,def2,def3,dr, headbodyflag, 393.headitemvo, itemcode, operator,pk_billtype,pk_corp,pkfield, votable, 394. pk_votable, ts) 395. values('', '', '', 'nc.vo.trade.pub.HYBillVO','', NULL,NULL,NULL,NULL,'N','nc.vo. 396.shsh. 397.stock.tb1010.StockApplyBVO','shsh_stock_apply_b','', 'TB10', NULL, 398. 'pk_apply','shsh_stock_apply_b','0001AA10000000001RK4','2007-09-27 17:12:01') 399.---------------------------------------- 400.--自动生成单据编码 401.insert into pub_billcode_rule(billcodeshortname,controlpara,day,dr,isautofill,ischeck,ishaveshortname,ispreserve, 402.lastsn, 403.month, 404.object1,object2,pk_billcoderule,pk_billtypecode,snnum,snresetflag,ts,year) 405.values('ZA','Y',20,0,'Y','Y','Y','Y','0000',12,NULL,NULL,'smTB1000000000000001','TB10',4, 406.0,'2007-09-27 18:55:32','07') 407.---------------------------------------- 408.--操作员人员关联关系 409.select psn.psnname from bd_psndoc psn inner join sm_userandclerk on sm_userandclerk.pk_psndoc = psn.pk_psndoc 410.inner join sm_user on sm_user.cuserid = sm_userandclerk.userid 411.---------------------------------------- 412.--查询模板的单据状态下列值初始化 413.update pub_query_condition set consult_code='I,,审批不通过,审批通过,审批进行中,提交态,作废态,冲销态,终止(结算)态,冻结 414.态,自由 415.态,待经办,已经办,已核查,已作废' 416.where field_code like '%billstatus' and pk_templet in( 417.select id from pub_query_templet where model_code in ('91012010')) 418.---------------------------------------- 419.--复制数据库里的类似数据，声称INSERT语句 420.select 'insert dap_defitem values('+attrname+','+CONVERT(char(1),dr)+','+headflag+','+itemname+','+CONVERT(char(1), 421.itemtype) 422.+','+pk_billtype+','+pk_voitem+','','+ts+')' from dap_defitem where pk_billtype='TB52' 423.---------------------------------------- 424.--手动增加打印模板中的项 425.insert into PUB_PRINT_DATAITEM (DR, IDATATYPE, ITYPE, PK_CORP, PK_VARITEM, PREPARE1, RESID, TS, USERDEFFLAG, VNODECODE, 426.VTABLECODE, VTABLENAME, VVAREXPRESS, VVARNAME) 427.values (0, null, null, [email=]'@@@@'[/email], '0001AA10000000ZY1220', null, null, '2007-01-21 15:23:37', null, 'TC0106', null, null, 428.'t_pk_psndoc', '报销人(表尾)'); 429.---------------------------------------- 430.--修改单据模板模板的字段类型 431.update pub_billtemplet_b set reftype='5' 432.where pk_billtemplet in 433.(select pk_billtemplet from pub_billtemplet where nodecode like 'TD%') 434.and datatype=2 and (reftype is null or reftype='3' or reftype='2') and 435.(defaultshowname like '%费%' or defaultshowname like '%额%' 436.or defaultshowname like '%金%' or defaultshowname like '%价%') 437.---------------------------------------- 438.--参照的表和字段 439.select pk_refcolumn, fieldname, fieldshowname, ishiddenfield, isblurfield, ismnecode, 440.ispkfield, iscolumnshow, islocateshow, columnshowindex, locateshowindex, 441.bd_refcolumn.pk_reftable,datatype,tablename from 442.bd_refcolumn inner join bd_reftable on bd_refcolumn.pk_reftable = bd_reftable.pk_reftable 443.where bd_refcolumn.pk_reftable = '0001AA1000000000P4LA' order by columnshowindex 444.--查询有节点权限的人员 445.----------------------------------------- 446.select * from sm_funcregister 447.select user_name from sm_user where cuserid in( 448.select userid from sm_appuserpower where funid='0001AA10000000000UDO' 449.) 450.--查询有节点权限的用户组 451.select g.group_name,g.pk_corp from sm_group g where cgroupid in( 452.select groupid from sm_usergrouppower where funid='0001AA10000000000WKR' 453.) 454.--审批流相关表 455.---------------------------------------- 456.select * from pub_wf_instance where billid='' and billno='' 457.select * from pub_wf_task where pk_wf_instance='' 458.select * from pub_wf_actinstance where pk_wf_instance='' 459.select * from pub_workflownote where billid='' and billno='' 460.select * from pub_wf_actinstancesrc where target_actinstance=pub_wf_actinstance.pk_wf_actinstance 461.--由于各表外键约束,自行调节删除顺序
详细解决方案
NC惯用SQL
热度：24 发布时间：2016-05-05 12:13:51.0
相关解决方案