Oracle control statement sharing and oracle statement sharing

Source: Internet
Author: User

Oracle control statement sharing and oracle statement sharing

Oracle control statement:

---------- IF statement

If... then

Action code;

Elseif... then

Action code;

Else

Action code;

End if;

---------- Loop statement

Loop

Action code;

Exit when v_num = 10; -- end judgment. Here is an example of self-growth of v_num.

V_num: = v_num + 1;

End loop;

---------- While LOOP statement

While v_num <= 0 loop

Action code;

V_num: = v_num-1;

End loop;

------------ If there is a for loop, I will not talk about it, Baidu

------------- Goto statement, not recommended

Goto fan_sign; -- when this is executed, it will jump to this flag

...... -- The intermediate code is omitted

<> -- For example, to jump here, you can jump from the loop here.

------------- Null, action nothing, read easy

If... then

Action Code;

Else

Null; -- action nothing

========================================================== ==========

-- The compilation process returns parameters in JAVA

-- In: indicates that this is an input parameter. The default value is in.

-- Out: indicates that this is an output parameter.

Create or replace procedure fan_pro

(FaNum in number, faName out varchar2) is

Begin select emame into faName from mytest where empno = faNum;

End;

-- Java Code

CallableStatement cs = ct. prepareCall ("{call fan_pro (,)}");

-- Assign a value to the first user

Cs. setInt (1,101 );

-- Assign a value to the second user and register a value.

Cs. registerOutParameter (2, oracle. jdbc. OracleType. VARCHAR );

Cs.exe cute (); // execute

String name = cs. getString (2); // get value, return the second

------------------------------

Returns a list that returns multiple results.

Two parts: 1. Create a package with a cursor

Create package testpackage

Typetest_cursor is ref cursor;

End testpackage;

2. creation process

Create or replace procedure fan_pro

(FaNum in number, p_cursor out testpackage. test_cursor) is

Begin

Open p_cursor for select * from mytest where empno = faNum;

End;

3. Calling in java

CallableStatement cs = ct. prepareCall ("{call fan_pro (,)}");

-- Assign a value to the first user

Cs. setInt (1,101 );

-- Assign a value to the second user and register a value.

Cs. registerOutParameter (2, oracle. jdbc. OracleType. CURSOR );

Cs.exe cute ();

ResultSet rs = (ResultSet) cs. getObject (2 );

While (rs. next ()){

... (Rs. getString (1) + "|" rs. getString (2); // output

}

// This is just an example. I will write another output.

Fan | fan

Fan1 | fan1

Fan2 | fan2

----------- Case statement

Action code;

Case

When v_num <1000 then

Action Code;

When v_num <2000 then

Action Code;

End case;

------------ Exception

Exception

When case_not_found then // various built-in exceptions

Action code;

Exception Handling

Access_info_null (ora-06530): triggered when an object without Initialization is accessed.

Case_not_found (ora-06592): this exception is triggered when there is no necessary conditional branch and no else clause after the case process.

Collection_is_null (ora-06531): access uninitialized set elements (nested tables or varray ).

Cursor_already_open (ora-06511): reopens the opened cursor.

Dup_val_on_index (ora-00001): When you type duplicate values in the column for the unique index in.

Invalid_cursor (ora-01001): attempts to extract content on an invalid cursor, for example, if the cursor is not opened.

Invalid_number (ora-01722): when attempting to convert an invalid string to a numeric type.

No_data_found (ora-01403): When you execute select into to return No rows, or reference an element not initialized in the index table.

Too_many_rows (ora-01422): When you execute select into to return more than one row of data.

Zero_pide (ora-01476): 0 as the divisor.

Subscript_beyond_count (ora-06533): when using a nested table or a varray set, if the referenced subscript exceeds last.

Subscript_outside_limit (ora-06532): when using a nested table or a varray set, if the referenced subscript is less than first.

Value_error (ora-06502): When performing a value assignment operation, if the variable length is insufficient to accommodate actual data.

Login_denied (ora-01017): an incorrect user name or password is provided when you connect to the database.

Not_logged_on (ora-01012): execution of plsql code triggers when the program is not connected to the oracle database.

Program_error (ora-06501): plsql internal issue.

Rowtype_mismatch (ora-06504): When a value assignment is performed, if the host cursor variable and PLSQL cursor variable return type are incompatible.

Self_is_null (ora-30625): If you call a member method on a null instance when using an object type.

Storage_error (ora-06500): exceeds memory space or the memory is corrupted.

Sys_invalid_rowid (ora-01410): invalid string attempt to convert to rowid type.

Timeout_on_resource (ora-00051): timeout error while waiting for the resource.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.