常用SQL

最新推荐文章于 2025-07-19 15:12:22 发布

转载最新推荐文章于 2025-07-19 15:12:22 发布 · 71 阅读

本文分享了多种SQL技巧，包括字段查询、过程内容查看、集合操作、记录筛选等，还介绍了如何进行表复制及备份的方法。

查看字段在哪些表中过。
select * from ALL_TAB_columns WHERE COLUMN_NAME='SUPPLY_SOURCE_ID'
查看过程内容。
SELECT * FROM ALL_SOURCE WHERE NAME='CH_PO_BATCH'

BEGIN
FND_CLIENT_INFO.set_org_context('439');
END;

select * from sys.ALL_TAB_columns WHERE COLUMN_NAME='SUPPLY_SOURCE_ID'

select * from dba_objects where OBJECT_NAME='AP_INVOICES_PKG'

'
-------------------一级会计科目
select flex_value_set_id,flex_value_id,flex_value,description from
fnd_flex_values_vl where flex_value_set_id=1002434 and flex_value like '0000%' and length(flex_value)=8

--------------二级科目
select structured_hierarchy_level,flex_value_id,flex_value,description from
fnd_flex_values_vl where flex_value like 'A%' and length(flex_value)=4

-------------
update b
set expense=(select expense from a where a.CONTRANT_NO=b.CONTRANT_NO)

用集合的观点来看是差集，如：

select   id   from   table1
minus
select   id   from   table2

意思是：id在表table1中，并且不在表Table2中的记录
如果一个到中没有重复的记录，那么也可以用于查找中间段记录如：想得到第10-20笔记录。
select * from dept where   rownum<=20
minus
select * from dept where   rownum<=10;

----------
select * from (select rownum r, t.* from Tablename t where r m and t1.r <= m

----------
select customer_name,count(customer_number) from ra_customers
group by customer_name
having count(customer_number)>1

----------------
elect * from (select rownum id,a.* from CMS_SALES_COEFFICIENT a) where id>5 and id<10

select index_name from user_indexes where table_name = 'DOCPROASSEMBLE'

select * from user_ind_columns where index_name = 'yourindexname';

do one thing at a time and do well.
never forget to say thanks.

一盘调有职场百味的菜肴，一面映有七彩人生的镜子。

在同一资料库中复制表:
emp如何备份一张表
create table tab2
as select * from apps.tab2

复制结构:
create table tab2
as select * from apps.tab2 where 1=2

在不同一资料库中复制表
1.先toad 中export table scripts 和table data两个scripts.sql,data.sql 两个文件。
2.再在pl/sql 中 @ e:\scrpts.sql,@ e:\data.sql

在pl/sql

set serveroutput on;
begin
dbms_output.put_line('a');
end;

SQL> declare
2    v1        number;
3 begin
4    v1 := 0;
5    <>
6    if v1 <= 10 then
7       v1 := v1 +1;
8       dbms_output.put_line(to_char(v1));
9       [color=-red]goto label1;
10    end if;
11 end;
12 /

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

转载于:http://blog.itpub.net/12122734/viewspace-407004/