Using kettle to invoke stored procedures in the database

Use kettle to call the stored procedure, passing in parameter outgoing parameters.

The contents of the stored procedure are as follows, mainly the return value of passing in a table name and a numeric type.

You can then get the number of rows in the table.

Createor Replace Procedure p_emp_cnt (t in Varchar2, Num out number) is V_sql VARCHAR2 (600); LV_CNT number;   Begin Select Count (*) into lv_cnt from User_tables Where Upper (table_name) = Upper (t); If lv_cnt > 0 Then v_sql: = ' Select Count (*) from ' | |    T  Execute Immediate v_sql into Num; End If; End;

The calling process is as follows:

Declare Num number;  Begin p_emp_cnt (' t1 ', Num); Dbms_output.put_line (Num); end;/

The experiment consists of two steps:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7F/55/wKiom1cZ_xWSZ-qmAAALfD-Q-Yg833.png "title=" 1.png " alt= "Wkiom1cz_xwsz-qmaaalfd-q-yg833.png"/>

1. Custom constant data:

This step defines two columns, one column is the table name, and the other column is a null value (to receive the return value)

As shown in the following:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7F/55/wKiom1cZ_2-gnAsyAAAQdIDTpOc116.png "title=" Image002.png "alt=" Wkiom1cz_2-gnasyaaaqdidtpoc116.png "/>

2. Call the DB stored procedure

This step needs to be connected to the target database, and the parameter name needs to be obtained through the previous steps.

The return value is obtained after the procedure is called.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/53/wKioL1caAE-AiQG5AAA2kxhmH7A161.png "style=" float: none; "title=" Image003.png "alt=" Wkiol1caae-aiqg5aaa2kxhmh7a161.png "/>

3. Preview Data

As shown, get the exact number of rows in each table.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7F/55/wKiom1cZ_4yCeq6bAAAXMUV9WaY794.png "style=" float: none; "title=" Image004.png "alt=" Wkiom1cz_4yceq6baaaxmuv9way794.png "/>

To summarize the steps of calling a stored procedure, you just need to write the name of the incoming table. For example, I have only one column in my build constants record.

Then you just need to pass this column into the process.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/53/wKioL1caAFCSjF9BAAAdaiQ0yAA914.png "style=" float: none; "title=" Image005.png "alt=" Wkiol1caafcsjf9baaadaiq0yaa914.png "/>

Preview the data to get the result:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/55/wKiom1cZ_42A2d8wAAAXMeagOPM599.png "style=" float: none; "title=" Image006.png "alt=" Wkiom1cz_42a2d8waaaxmeagopm599.png "/>

This article is from the "long name will be good to remember" blog, please be sure to keep this source http://xiaoyiyi.blog.51cto.com/1351449/1766836

Using kettle to invoke stored procedures in the database