软件测试要会oracle吗,Oracle 新手注意事项

向右看齐 2023-01-18 06:40 150阅读 0赞

2.纯粹用sql：

表dept, emp

要得到如下结果

deptno, dname, employees

\---------------------------------

10, accounting, clark;king;miller

20, research, smith;adams;ford;scott;jones

30, sales, allen;blake;martin;james;turners

每个dept的employee串起来作为一条记录返回

This example uses a max of 6, and would need more cut n pasting to do more than that：

1 SQL> select deptno, dname, emps

2 from (

3 select d.deptno, d.dname, rtrim(e.ename ||', '||

4 lead(e.ename,1) over (partition by d.deptno

5 order by e.ename) ||', '||

6 lead(e.ename,2) over (partition by d.deptno

7 order by e.ename) ||', '||

8 lead(e.ename,3) over (partition by d.deptno

9 order by e.ename) ||', '||

10 lead(e.ename,4) over (partition by d.deptno

11 order by e.ename) ||', '||

12 lead(e.ename,5) over (partition by d.deptno

13 order by e.ename),', ') emps,

14 row\_number () over (partition by d.deptno

15 order by e.ename) x

16 from emp e, dept d

17 where d.deptno = e.deptno

18 )

19 where x = 1

20 /

DEPTNO DNAME EMPS

\------- ----------- ------------------------------------------

10 ACCOUNTING CLARK, KING, MILLER

20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH

30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

14.在Oracle中建一个编号会自动增加的字段，以利于查询

1、建立序列：

CREATE SEQUENCE checkup\_no\_seq

NOCYCLE

MAXVALUE 9999999999

START WITH 2;

2、建立触发器：

CREATE OR REPLACE TRIGGER set\_checkup\_no

BEFORE INSERT ON checkup\_history

FOR EACH ROW

DECLARE

next\_checkup\_no NUMBER;

BEGIN

\--Get the next checkup number from the sequence

SELECT checkup\_no\_seq.NEXTVAL

INTO next\_checkup\_no

FROM dual;

\--use the sequence number as the primary key

\--for the record being inserted

:new.checkup\_no := next\_checkup\_no;

END;

15.查看对象的依赖关系(比如视图与表的引用)

查看视图：dba\_dependencies 记录了相关的依赖关系

查东西不知道要查看哪个视图时，可以在DBA\_Objects里看，

select object\_name from dba\_objects where object\_name like '%ROLE%'(假如查看ROLE相关)

然后DESC一下就大体上知道了。

16.要找到某月中所有周五的具体日期

select to\_char(t.d,'YY-MM-DD') from (

select trunc(sysdate, 'MM')+rownum-1 as d

from dba\_objects

where rownum < 32) t

where to\_char(t.d, 'MM') = to\_char(sysdate, 'MM') --找出当前月份的周五的日期

and trim(to\_char(t.d, 'Day')) = '星期五'

\--------

03-05-02

03-05-09

03-05-16

03-05-23

03-05-30

如果把where to\_char(t.d, 'MM') = to\_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。

44/4<1234

软件测试要会oracle吗,Oracle 新手注意事项

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关软件测试要会oracle吗,Oracle 新手注意事项

相关 oracle安装教程以及使用注意事项

相关【软件测试】软件测试岗位会越来越少吗？

相关 Python访问Oracle及注意事项

相关 Oracle数据库to_date函数注意事项

相关 Oracle中编写Sql语句注意事项

相关 Oracle Java JDBC编程注意事项

相关 Navicat 客户端连接Oracle注意事项

相关 Oracle数据库几点注意事项

相关 Oracle 跨Schema访问注意事项