期末考試試題及答案

1. 40 2. 20

3.

20 4. 10 5.

10

1 2 16

45

SGA

PGAB

DBAB

SCOTT

SYSTEMDSYS

Oracle

DEFAULTTABLESPACE

UOracle

HRBSCOTT

SYSTEMDSYS

OracleHOMENAMETNSListe

ner

OracleServiceSID

OracleHOME_NAMEAgent

OracleHOMENAMEHTTPServer

5

ABCD

6SQL SAL*12 AnnualSalary

ASELECTename,sal*12AnnualSalary'FROMemp;

BSELECTename,sal*12AnnualSalary"FROMemp;

CSELECTename,sal*12ASAnnualSalaryFROMemp;

DSELECTename,sal*12ASINITCAP(AnnualSalaryFROMemp;

7 ()

A

B

C

D&()

A

9 ()SELECT

AFORINSERT

CFORDELETE

()

A

C

()

ABC

BFORUPDATE

DFORREFRESH

B

D

D

12

13

14

15

16

17

18

19

20

21

22

23

24

ACURRVALNEXTVALBNEXTVALPREVAL

CCACHENOCACHE DMAXVALUEMINVALUE

AFORCE

BWITHCHECKOPTION

CCREATEVIEWWITHERROR

DCREATEERRORVIEW

A

B

C

D

A BC D

ACREATEPRIVATESYNONYM

BCREATEPUBLICSYNONYM

CCREATESYNONYM

DCREATEANYSYNONYM

PL/SQL SQL

ASELECT BINSERT

CUPDATE DDROP

BZERO_DIVIDE

DSELF_IS_NULL

BFORUPDATE

DFORMODIFY

AVALUE_ERROR

CSTORAGE_ERROR

AWHERECURRENTOF

CFORDELETE

TOC\o"1-5"\h\z

A B

CREF D

AUSER_OBJECTSBUSER_TEXT

CUSER_SOURCEDUSER_DESC

PL/SQL

A B

C D

PL/SQL

ADBMS_OUTPUT

DBMSSTANDARD

CDBMSINPUT

DDBMSSESSION

Oracle

SYS

SYSTEM

SCOTT

PUBLIC

CONSTRAINTS

BTABLES

FULL

DFILE

40

aptech

MARTIN

martinpass

Connectmartin/martinpass@aptech___

EMP

SYS MARTINSCOTT

MARTIN

Grantselectonscott.emptomartin

employee

2

CREATETABLEemployee_copyAS Select*fromemployeewhere

itemfileitemrate

SELECT To_char__(itemrate,'C99999')FROMitemfile

0SELECT

itemfileitemdescre_level re_levelNULL

itemdesc,_NVL_(re_level,0)FROMitemfile

PL/SQL 2 50 25

BEGIN

25

26

27

28

29

30

1

2

3

4

5

6

FOR_even_number___IN_1..25_

LOOP

DBMS_OUTPUT.PUT_LINE(even_number*2)

ENDLOOP

END

7 PL/SQL emp

name VALUE_ERROR

DELCARE

empnameemployee.ename%TYPE;;

enoemployee.empno%TYPE;

BEGIN

eno:=&employee_number

__SELECTenameINTOempnameFROMemployeeWHEREempno=eno;___

DBMS_OUTPUT.PUT_LINE( ||empname);[9]

WHENVALUE_ERRORTHEN

DBMS_OUTPUT.PUT_LINE( )

END;

PL/SQL 250

DECLARE

my_toy_pricetoys.toyprice%TYPE;

CURSORtoy_curIS

SELECTtoypriceFROMtoys

WHEREtoyprice<250;

BEGIN

[10]

LOOP

[11]

EXITWHENtoy_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(toy_cur%ROWCOUNT|| '. :'||

my_toy_price);

ENDLOOP;

CLOSEtoy_cur;

END;

PL/SQL tsales

(asales) tsalesasales

DECLARE

CURSORsales_curISSELECT*FROMsalesdetails;

BEGIN

[12]

IFsales_rec.tsales>sales_rec.asalesTHEN

DBMS_OUTPUT.PUT_LINE('’||sales_rec.pid ');

ELSE

IF [13] THEN

DBMS_OUTPUT.PUT_LINE(' ’||sales_rec.pid ');ELSE

DBMS_OUTPUT.PUT_LINE('’||sales_rec.pid ');ENDIF;

ENDIF;

ENDLOOP;

END;

PL/SQL

CREATEORREPLACEPROCEDURE

swap(p1INOUTNUMBER,p2 [14] NUMBER)IS

v_tempNUMBER;

BEGIN

v_temp:=p1;

p1:=p2;

p2:=v_temp;

END;

PL/SQL dept_name

dept emp

deptno

CREATEORREPLACEFUNCTIONdept_name(emp_noNUMBER)

RETURNVARCHAR2AS

dept_noNUMBER(2);

resultdept.dname%TYPE;

BEGIN

[15]

SELECTdnameINTOresultFROMdept

WHEREdeptno=dept_no;

[16] ;

EXCEPTION

WHENOTHERSTHEN

RETURNNULL;

END;

pack_meorder_proc 002

EXECUTE [17]

PL/SQL biu_job_emp

EMPjob job

CREATEORREPLACETRIGGERbiu_job_emp

ON

_BEFORE INSERTOR UPDATE OFjob

emp [18]

FOREACHROW [19]

BEGIN

:NEW.job:= UPPER(:NEW.job)_[20]

END;

1

A

6

B

11

C

16

C

21

C

26

A

2

D

7

B

12

C

17

D

22

C

27

A

3

C

8

C

13

A

18

B

23

C

28

A

4

A

9

B

14

B

19

A

24

A

29

B

5

B

10

C

15

D

20

B

25

A

30

C

2 40

[1]

Connectmartin/martinpass@aptech

[2]

Grantselectonscott.emptomartin

[3]

Select*fromemployeewhere1=2

[4]

To_char

[5]

NVL

[6]

even_number

[7]

1..25

[8]

SELECTenameINTOempnameFROMemployeeWHEREempno=eno;

[9]

EXCEPTION

[10]

OPENtoy_cur

[11]

FEICHtoy_curINTOmy_toy_price;

[12]

FORsales_recINsales_curLOOP

[13]

sales_rec.tsales=sales_rec.asales

[14]

INOUT

[1