Shulou(Shulou.com)05/31 Report--

This article is about how to understand and practice the key issues of PLSQL. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's follow the editor to have a look.

The collection variable types used by PL/SQL in ORACLE are RECORD (class), VARRAY (sql array) and TABLE (nested table).

TABLE (nested table) can be defined with or without index. Adding means that index by is to build a primary key index, which is equivalent to an array. Without addition, it is a collection of nested tables.

1 TABLE (nested tables) defines table variable types

Type type_table_emp_empno is table of emp.empno%type index by binary_integer;--TYPE represents the field type in a row of the table

V_empnos type_table_emp_empno

If defined with% type

Define whether the collection variable v_empnos is an array with emp.empno field type. You can understand whether to store the actual or a table with only one field and an index on the field.

Operations on this collection variable (is table of index by) type include count,delete, but cannot use trim

You can use count,delete and trim for VARRAY

Form of use

Select to_char (truck_in_out_id)

Employee_id

Employee_nm

Truck_in_purpose

Bulk collect into

Carid

Empid

Empnm

Dest

Forall i in 1.. Carid.COUNT

Update cpnew.CP_VISIT_APPLY a

Set a.mgr_principal_id = empid (I)

A.mgr_principal_nm = empnm (I)

A.visit_dest = dest (I)

Where a.visit_apply_id = carid (I)

And a.mgr_principal_id is null

Type delArray1 is table of TEST.COURSE%ROWTYPE index by binary_integer;-- ROWTYPE represents the record type of a row in a table

Cur_result delArray1

If defined with% rowtype

The definition set variable cur_result is a collection of COURSE table types, which I understand is stored as a table, including all the field types of COURSE, and the table is indexed by the integer number.

Form of use

Select * bulk collect into cur_result

Forall i in 1.. Cur_result.COUNT

Update cpnew.CP_VISIT_APPLY a

Set a.mgr_principal_id = cur_result (I) .empid

A.mgr_principal_nm = cur_result (I) .empnm

A.visit_dest = cur_result (I) .dest

Where a.visit_apply_id = cur_result (I) .carid

And a.mgr_principal_id is null

Examples in practical work

Plsql deletes a large amount of data and modifies the method of FORALL plus bulk collection into

Create or replace procedure zl_del_UPDATEAPPLY_DEST187 as

-- type ridArray is table of rowid index by binary_integer

Type delArray1 is table of varchar2 (32) index by binary_integer

Type delArray2 is table of CP_2012.CP_VISIT_TRUCK_INOUT.employee_id%type index by binary_integer

Type delArray3 is table of CP_2012.CP_VISIT_TRUCK_INOUT.employee_nm%type index by binary_integer

Type delArray4 is table of CP_2012.CP_VISIT_TRUCK_INOUT.truck_in_purpose%type index by binary_integer

/ / you will find that with% type, each field has to define its type.

Carid delArray1

Empid delArray2

Empnm delArray3

Dest delArray4

Begin

Select to_char (truck_in_out_id)

Employee_id

Employee_nm

Truck_in_purpose

Bulk collect into

Carid

Empid

Empnm

Dest

From CP_2012.CP_VISIT_TRUCK_INOUT

-- where rownum < 600001

Forall i in 1.. Carid.COUNT

Update cpnew.CP_VISIT_APPLY a

Set a.mgr_principal_id = empid (I)

A.mgr_principal_nm = empnm (I)

A.visit_dest = dest (I)

Where a.visit_apply_id = carid (I)

And a.mgr_principal_id is null

DBMS_OUTPUT.PUT_LINE (to_char (carid.COUNT) | |

'records deleted from temp_mid_hubei_bak!!')

End

The biggest disadvantage of this method is that remote tables cannot be accessed or dblink can be used in forall, and only dml statements can be put, not dbms.putline.

After testing, it is found that for can replace forall, although the time is relatively slow, it is acceptable, so you can use dblink in for. The corresponding sentence is as follows:

For i in 1.. Carid.COUNT

Loop

Update cpnew.CP_VISIT_APPLY@LINK_213TO187_CPNEW a

Set a.mgr_principal_id = empid (I)

A.mgr_principal_nm = empnm (I)

A.visit_dest = dest (I)

Where a.visit_apply_id = carid (I)

And a.mgr_principal_id is null

2 Record variable type: (equivalent to java class)

Define

Type type_record_dept is record

(

Deptno dept.deptno%type

Dname dept.dname%type

Loc dept.loc%type

);

V_temp type_record_dept

3 VARRAY

Definition and use

CREATE OR REPLACE TYPE numbers_t IS VARRAY (5) OF NUMBER

DECLARE

L_list numbers_t:= numbers_t (1,2,3,4,5)

BEGIN

L_list.DELETE

DBMS_OUTPUT.put_line (CASE l_list.COUNT WHEN 0 THEN 'Empty' END)

END

Array usage examples refer to the use of the Oracle array http://blog.itpub.net/12932950/viewspace-351791/

You can also compare my previous blog to view the usage of cursors and bulk collect into http://blog.itpub.net/750077/viewspace-2075986/

Two PL/SQL anomalies

Exception type 1 predefined exception handling, 2 unpredefined (Predefined) errors, 3 user defined (User_define) errors

General predefined and user-defined exceptions are used more frequently.

1 predefined exceptions such as those already defined by oracle

ORA-1403 No_data_found SELECT INTO did not find the data

When in use, if the select does not find the data, it will directly handle the exception that the data is not found.

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('No code in the database'| | v_empno | | 'employee')

END

2 user-defined exception

The user defines the exception first

No_result EXCEPTION

If there is no updated data, throw this exception

UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno

IF SQL%NOTFOUND THEN

RAISE no_result

END IF

And then handle this exception.

EXCEPTION

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE ('your data update statement failed!')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

END

SQLCODE,SQLERRM is an ORACLE function that prints an error code and error name

The above is how to understand and practice the key issues of PLSQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.