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

This article mainly introduces how to automatically generate batch processing for batch execution of SQL scripts, which is very detailed and has a certain reference value. Friends who are interested must read it!

Scene:

DBA exported to me all the storage, functions and other object creation scripts, there are thousands of files.

Now you need to import these object creation scripts into another library. What can I do about it?

It is obviously not realistic to execute one by one manually.

So a batch is written manually, all the files are formed into a .SQL script, and finally imported into the target library in the form of @ generated .SQL script.

OS environment: WINDOWS xp

The script reads as follows:

The code is as follows:

@ echo off

If exist list.sql del list.sql / q

: input

Cls

Set input=:

Set / p input= Please enter the path you want to judge:

Set "input=%input:" =% "

:: the above sentence is to determine whether quotation marks exist in% input%, and if so, remove them.

If "% input%" = ":" goto input

If not exist "input%" goto input

For / f "delims="% I in ('dir / b / amurd / s "% input%") do echo @ @% ~ fnxi > > list.sql

If not exist list.sql goto no_file

Start list.sql

Exit

: no_file

Cls

An error occurred in echo cur_dir% and the list.sql script was not generated successfully!

Pause

Use:

After saving as a .bat type file, double-click to execute.

Enter the path to your script:

For example, under the CRY folder of my E disk, there are the following types of files:

TEST.PRC

..

123\ TEST1.FNC (with subfolders 123)

..

TEST2.VW

.

After executing the batch, the contents of the final generated list.sql script file are as follows (the file is automatically opened with the default editor after the batch is executed):

@ @ E:\ cry\ TEST.PRC

..

@ @ E:\ cry\ 123\ TEST1.FNC

..

@ @ E:\ cry\ TEST2.VW

..

Open SQLPLUS to specify the user to log in to the database, and then execute: (my script file is generated on the d disk)

@ D:\ list.sql

In this way, all objects are automatically generated under the specified user.

-

Note: many of the objects generated in this way are invalid, because they are not generated sequentially, so it is recommended to compile all invalid objects after automatic execution.

The above is all the contents of the article "how to automatically generate batches for batch execution of SQL scripts". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to 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.