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"
'%p_data%'

 

%~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 ***************************************

pause
goto :END

 

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

output to a file

SPOOL ../CLEAN_UP.LOG
SET SERVEROUTPUT ON

 

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

DECLARE
 KEKKA   NUMBER;
 KEKKA_TXT  VARCHAR2(1000);
 LASTDATE   DATE :='&1';
BEGIN
    PKG_DELETE.DEL(LASTDATE,KEKKA,KEKKA_TXT);
    DBMS_OUTPUT.PUT_LINE(KEKKA);
    DBMS_OUTPUT.PUT_LINE(KEKKA_TXT);
END;
/

SPOOL OFF
QUIT

 

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     PKG_DELETE.DEL(LASTDATE,KEKKA,KEKKA_TXT); >> %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 .

 

Technology
©2019-2020 Toolsou All rights reserved,
One is called “ Asking for the train ” A small village Finally got the train SparkSQL Achieve partition overlay write Character recognition technology of vehicle license plate based on Neural Network c++ Memory Pointer out of bounds detection mechanism _CrtMemBlockHeade Change one's mind ! Tesla starts to deliver made in China to European market Model 3Qt study 7—— Modal and modeless dialog boxes JavaScript Medium Call and ApplyJAVA Convert a string to a numeric type k8s Entry to give up --k8s Key concepts Count the number of letters (java Language implementation )