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

Requirements: existing table dw, in which the fields bm (coding), sj (superior coding)

A new field px is added, which requires a string to be added to the new field. Cascading superiors and themselves with strings as units

And sort from top to bottom, from left to right, with "|" in the middle.

For example, the unit code is 005 and the superior unit code is 004

The superior unit of unit 004 is 003 on one side and the superior code of unit 003 is 002

The superior of unit 002 is the highest unit 001.

The data taken out should be: "001 | 002 | 003 | 004 | 005"

Achieve:

1 first get the data of the cascade superiors of a certain unit

You can use sql:

Select sj

From dw T

START WITH T.bm=:BM

CONNECT BY PRIOR T.sjbm=T.bm

Take out the unit codes of all cascading superiors of the unit

2 through the pipeline function, you can list the cascading unit code of a unit and its own unit code and return the result set.

Create or replace FUNCTION upbm

(

Bm_IN IN VARCHAR2

) RETURN bm_DATA pipelined

-obtain the superior unit coding (including its own) data set through unit coding and unit classification

AS

Bm_ROW yly_row_type

Bm_TAB bm_DATA

BEGIN

FOR MYROW IN (

Select sjbm

From dw T

START WITH T.bm=bm_IN

CONNECT BY PRIOR T.sj=T.bm

UNION ALL

SELECT bm

FROM dw

WHERE bm=bm_IN

ORDER BY 1

) LOOP

Bm_ROW: = YLY_ROW_TYPE (MYROW.sj)

PIPE ROW (bm_ROW)

END LOOP

RETURN

END upbm

At this point, the dataset is obtained through select * from table (getupperdeptwitchself (: bm)).

001

002

003

004

005

3 convert the obtained dataset into a row through the function

You can use the wm_concat function, which converts the input result set to 1 line and splits it with ",", which needs to be converted to "|".

Create or replace FUNCTION upbm_px

(

Bm_IN IN VARCHAR2

) RETURN VARCHAR2 AS

Px VARCHAR2 (400)

BEGIN

SELECT replace (wm_concat (bm),'|') INTO px from table (upbm (bm_IN))

RETURN px

END upbm_px

At this point, you can get a row of data through the function:

Select GET_UPPERDEPT_PX (bm) from dual

Get the data: "001 | 002 | 003 | 004 | 005"

By this time, the requirements have been basically completed.

Next, make a trigger so that the table automatically generates sjbm_xp data when it is inserted.

4 create a trigger

Create or replace TRIGGER "dw_SJ_TRG"

Before insert on dw

For each row

Declare

Begin

-- generate px fields when inserting data

: NEW.px: = upbm_px (: NEW.bm)

End

But when inserting the Times error: ORA-04091: table dw has changed trigger / function cannot read the table

Cause of the problem: when oracle executes the DML statement, it needs to be displayed for submission. Triggers are triggered when we insert

Execute the action table and the extension table on the trigger, but when the trigger and the insert table are in the same thing, insert the statement

Additional operations cannot be performed on the trigger table without a commit.

Solution:

Change the trigger to display submission

Create or replace TRIGGER "dw_SJ_TRG"

Before insert on dw

For each row

Declare

Pragma autonomous_transaction

Begin

-- generate px fields when inserting data

: NEW.px: = GET_UPPERDEPT_PX (: NEW.bm)

Commit

End

5 full table update

Complete the px fields of all rows of table dw

UPDATE dw SET px=GET_UPPERDEPT_PX (bm)

An error was reported at this time: ORA-04091: table dw has changed trigger / function cannot read the table

The cause of the error is similar to that just now:

The table to be updated is dw, and the function upbm that gets the result set relies on dw for circular calculation.

The source of the loop has changed since the update, and ORACLE does not allow this, which may result in an infinite loop.

Solution: create a new table data dw_BAK that is consistent with the dw table, and change the

The source table is changed from dw to dw_BAK.

Execute statement UPDATE dw SET px=GET_UPPERDEPT_PX (bm)

After submission, change the source of the function upbm back to dw.

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.