Today comes a new task , The requirement is to make a batch file for the customer to maintain the database . It's not used before access2003 The management tools are too slow to implement . This time, the maintenance is directly carried out SQL Make a bag and use it sqlplus The call should be executed quickly . But users don't know much about databases , Request to do a batch file, you can simply input one or two conditions can be executed .

First write a title output , Let users know what this batch is for

@echo ***************************************
@echo Database Cleanup Tool
@echo ***************************************


The following requirements require the user to enter the user connection information for the database , After investigating, it may be better to write like this . Is there another way to interact later .

@echo off

set /p p_user=" enter one user name "

set /p p_password=" Please enter user password "

set /p p_sid=" Please enter database connection string (TNS name )"

set /p p_data=" Please enter the maintenance time (YYYY/MM/DD)"


Judge the input content ( Write a simple example )

if "%p_data%"=="" (goto :ERR1)


Require user confirmation before execution . This place was meant to be used choice, But mine xp Command window not supported , Later investigation and investigation .

set /p rtn=" Maintenance time is up %p_data% until , Start ?(Y/N)"

if "%rtn%"=="Y" (goto :EXECUTE_DELETE)
if "%rtn%"=="y" (goto :EXECUTE_DELETE) else (goto :END)


Next, the execution statement is called , Transfer parameters are used here , And specifying the current directory .

sqlplus %p_user%/%p_password%@%p_sid%
<mailto:%25p_user%25/%25p_password%25@%25p_sid%25> @"%~dp0DELETE_START.SQL"


%~dp0 Represents the current directory name ( use / ending ) If there are spaces in the directory name, you may not know it, so use "" In a nutshell .

The parameters passed can be in the sql In this paper &1,&2…… visit


After the call, the result is displayed , Then press a key to close the window

@echo ***************************************
@echo The database Cleanup tool is finished
@echo ***************************************

goto :END


It's below sql The content of the article , Simple writing

output to a file



Declare variables KEKKA Is the status number of the result ,KEKKA_TXT It's the result information ,LASTDATE It's a condition of execution .LASTDATE Accept time variables from batch processing

 LASTDATE   DATE :='&1';



This can basically meet the user requirements .

However, two files need to be passed to the user, and both files should be in the same directory .

Try to figure out if you can do it in one file .


It seems that only one can be called in batch processing sql Only in this way can we meet the requirements .

Make a change . In the batch file, you must call the sql file .


method 2:

Define the generated file name in the batch , Then output the contents of the file to the file

set sqlfile=sqlfile.txt
echo SPOOL ../CLEAN_UP.LOG >> %sqlfile%

echo SET SERVEROUTPUT ON >> %sqlfile%
echo DECLARE >> %sqlfile%
echo  KEKKA   NUMBER; >> %sqlfile%
echo  KEKKA_TXT  VARCHAR2(1000); >> %sqlfile%
echo  LASTDATE   DATE :='^&1'; >> %sqlfile%

Pay attention here & perhaps % Add one to the output ^, Otherwise, this line of content will not be output .


echo BEGIN >> %sqlfile%
echo     DBMS_OUTPUT.PUT_LINE(KEKKA); >> %sqlfile%
echo     DBMS_OUTPUT.PUT_LINE(KEKKA_TXT); >> %sqlfile%
echo END; >> %sqlfile%
echo / >> %sqlfile%

echo SPOOL OFF >> %sqlfile%
echo QUIT >> %sqlfile%


sqlplus %p_user%/%p_password%@%p_sid%
<mailto:%25p_user%25/%25p_password%25@%25p_sid%25> @"%sqlfile%" '%p_data%'


It's basically done .


©2019-2020 Toolsou All rights reserved,
C Review of basic language knowledge Go Language learning notes (GUI programming )Java Misunderstanding —— Method overloading is a manifestation of polymorphism ? How to achieve low cost and high stability for cloud native applications ?elementui Shuttle box el-transfer Display list content text too long C/C++ Memory model Element-Ui assembly Message Message prompt , alert Popup C# Making a simplified version of calculator Python In pycharm editor Interface style modification Tiktok refresh progress bar ( Two little balls turn ), The code is simple