Introduction to Oracle Stored Procedure instances and oracle Stored Procedure instances

Introduction to Oracle Stored Procedure instances and oracle Stored Procedure instances

This document introduces the stored procedure through the imported table.

The difference between stored procedures and functions is that functions can only be used out-of-the-box and are suitable for brief configuration, but not for batch operations and background writing. This is my simple understanding of stored procedures, I think Oracle stored procedures and functions are similar to classes and functions in java, python, and other object-oriented languages.

We need to process a batch of table data and write the data in excel into SQL scripts based on the table name. This process is actually very simple. I/O using shell and java, oracle functions can be used, but this article uses a method that can better reflect the limit: writing stored procedures, taking a table as an example.

This is the table data. I am too lazy to use the figure of the previous blog, but the data is still the data.

We want to read this table TB2 to implement this script.

Insert overwrite table partition (rfq = '# rq') SELECT SWJG_DM, SWJG_MC, SJSWJG_DM, SYSJSWJG_DM, XYBZ, SWBM_BZ, expires, ssswjtpd, GETDATE (Y

PT_JGSJ, YPT_YSJCZLX, YPT_YSJCZSJ, YPT_YSJCZXL

FROM SC _JC_TY.T_TY_DZ_DM_GY_SWJG where rfq = '# RQ ';

Write the stored procedure now

The first step is to declare variables.

DECLARE -- DECLARE the keyword of the Variable

Colname VARCHAR2 (500); -- variable and Data Type

Bzbm VARCHAR2 (300 );

M number;

M2 number;

Ybm VARCHAR2 (300 );

Fhandle utl_file.file_type; -- fhandle is the custom name of the file, and utl_file.file_type is the attribute of the oracleutl_file package file, which is the keyword

CURSOR C_SAL IS

Select distinct ybm from tb2; -- cursor xxx is select * FROM TABLE_NAME; assign the selected value to XXX

BEGIN

...

END -- BEGIN and END are script processes and are mandatory for process keywords.

Step 2: start writing the script body

BEGIN

Fhandle: = utl_file.fopen ('exp _ dir', 'electronic ledger _ source standard. SQL ', 'w', 32767); --: = is a value assignment. fopen can be understood as java io.

FOR V_SAL IN C_SAL LOOP-for LOOP in each language: FOR xxx IN yyy LOOP

SELECT distinct bzbm INTO bzbm FROM tb2 where ybm = V_SAL.YBM; -- select xxx into y from assignment Y

Utl_file.PUT (fhandle, 'insert overwrite table SC _JC_BZ. '); -- the name of the target topic. PUT writes data to this file in sequence.

Utl_file.PUT (fhandle, bzbm );

Utl_file.PUT (fhandle, 'partition (rfq = '000000') select ');

SELECT max (yxh) into m from tb2 where ybm = V_SAL.YBM;

FOR I IN 1 .. m LOOP

SELECT yzdm into colname from tb2 where ybm = V_SAL.YBM and yxh = I;

If I = 1 then

Utl_file.PUT (fhandle, colname );

Else

Utl_file.PUT (fhandle, ',' | colname );

End if;

End loop; -- In the stored procedure, each if judgment and each loop must end, similar to the VB language.

Utl_file.new_line (fhandle); -- new_line adds the line terminator, which means line feed.

Utl_file.PUT (fhandle, 'From SC _JC_TY. '); --- Source Topic name

SELECT distinct jcztbm INTO ybm FROM tb2 where ybm = V_SAL.YBM;

Utl_file.PUT (fhandle, YBM); --- source table name

Utl_file.PUT_LINE (fhandle, 'where rfq = '000000'; '); -- initialize the partition

End loop;

Utl_file.fclose (fhandle); -- remember the fclose file.

END;

So far, a stored procedure written using the utl_file package is completed,

Fhandle: = utl_file.fopen ('exp _ dir', 'electronic ledger _ source standard. SQL ', 'w', 32767 );

This is to output the file to the EXP_DIR folder, so remember to create the folder and grant permissions before running the stored procedure. The command is as follows:

Create directory exp_dir as 'home/oracle/directory'; -- oracle can use d:/directory to install windows.

Grant create any directory to public;