Oracle control statement sharing and oracle statement sharing

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.