Shulou(Shulou.com)06/01 Report--

= Example 1 =

SQL > create table emp_tmp as select * from employees

Table created.

SQL > select last_name from emp_tmp wherefirst_name='John'

LAST_NAME

-

Chen

Seo

Russell

SQL > edit

DECLARE

V_lnameVARCHAR2 (15)

BEGIN

SELECT last_nameINTO v_lname

FROM emp_tmp

WHERE first_name = 'John'

DBMS_OUTPUT.PUT_LINE ('John''s last name is:' | | v_lname)

END

/

SQL > @ notes/s62.sql

DECLARE

*

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number ofrows

ORA-06512: at line 4

SQL > edit

DECLARE

V_lnameVARCHAR2 (15)

BEGIN

SELECT last_nameINTO v_lname

FROM emp_tmp

WHERE first_name = 'John'

DBMS_OUTPUT.PUT_LINE ('John''s last name is:' | | v_lname)

EXCEPTION

WHEN TOO_MANY_ROWSTHEN

DBMS_OUTPUT.PUT_LINE ('Your select statement retrieved multiple rows.Condider using a cursor.')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('You meet an errorships')

END

/

SQL > @ notes/s62.sql

Your select statement retrieved multiple rows. Condiderusing a cursor.

PL/SQL procedure successfully completed.

SQL > truncate table emp_tmp

Table truncated.

SQL > @ notes/s62.sql

You meet an error!

PL/SQL proceduresuccessfully completed

= Example 2 =

SQL > edit

DECLARE

V_lnameVARCHAR2 (15)

BEGIN

SELECT last_nameINTO v_lname

FROM emp_tmp

WHERE first_name = 'John'

DBMS_OUTPUT.PUT_LINE ('John''s last name is:' | | v_lname)

DBMS_OUTPUT.PUT_LINE ('Welcome backgrounds')

EXCEPTION

WHEN TOO_MANY_ROWSTHEN

DBMS_OUTPUT.PUT_LINE ('Your select statement retrieved multiple rows.Condider using a cursor.')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('1: You meet an errorists')

GOTOwelcomeback

DBMS_OUTPUT.PUT_LINE ('2: Game overload')

DBMS_OUTPUT.PUT_LINE ('3: You will be endedments')

END

/

SQL > @ notes/s63.sql

GOTO welcomeback

*

ERROR at line 18:

ORA-06550: line 18, column 3:

PLS-00375: illegal GOTO statement; this GOTO cannot branchto label

'WELCOMEBACK'

ORA-06550: line 18, column 3:

PL/SQL: Statement ignored

SQL > edit

DECLARE

V_lname VARCHAR2 (15)

BEGIN

SELECT last_nameINTO v_lname

FROM emp_tmp

WHERE first_name = 'John'

DBMS_OUTPUT.PUT_LINE ('John''s last name is:' | | v_lname)

DBMS_OUTPUT.PUT_LINE ('Welcome backgrounds')

EXCEPTION

WHEN TOO_MANY_ROWSTHEN

DBMS_OUTPUT.PUT_LINE ('Your select statement retrieved multiple rows.Condider using a cursor.')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('1: You meet an errorists')

-- GOTOwelcomeback

GOTO gohere

DBMS_OUTPUT.PUT_LINE ('2: Game overload')

DBMS_OUTPUT.PUT_LINE ('3: You will be endedments')

END

SQL > @ notes/s63.sql

1: You meet an error!

3: You will be ended!

PL/SQL proceduresuccessfully completed

= Example 3 =

SQL > edit

DECLARE

E_insert_excepEXCEPTION

PRAGMAEXCEPTION_INIT (e_insert_excep,-01400)

BEGIN

INSERT INTOdepartments (department_id, department_name) VALUES (280,280, NULL)

EXCEPTION

WHENe_insert_excep THEN

DBMS_OUTPUT.PUT_LINE ('InsertOperation failed')

DBMS_OUTPUT.PUT_LINE (SQLERRM)

END

/

SQL > @ notes/s64.sql

Insert Operation Failed!

ORA-01400: cannot insert NULL into ("HR". "DEPARTMENTS". "DEPARTMENT_NAME")

PL/SQL proceduresuccessfully completed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.