CODEWITHSUNDEEP
mysqloracle-databasedb2
senior
senior

Reputation: 2276

separate queries in mysql, db2 and oracle

to separate queries in an sql file: with mysql : we can use Delimiter /

example:

Delimiter /
select * from product;
/
select * from h_product;
/
Delimiter ;
insert into ptoduct(prod_name)values('my_prod_name');

and for oracle and db2? what to use? also Delimiter?

Upvotes: 2

Views: 2538

Answers (3)

mustaccio
mustaccio

Reputation: 18955

In a DB2 CLP script you can use the SET TERMINATOR instruction to change the statement terminator "on the fly":

--#SET TERMINATOR ;
select * from sysibm.sysdummy1;

--#SET TERMINATOR /
create or replace trigger t1 ...
begin
 ...
end
/

You would then call this as db2 -tf myscript.sql. Note that the SET TERMINATOR instruction must be in upper case.

I don't think there is an option to customize terminators in the Oracle SQL*Plus scripts.

Upvotes: 2

AngocA
AngocA

Reputation: 7693

In DB2 you use ; as SQL terminator (for SQL PL and PL/SQL).

You define the statement termination character with the td option.

For example, to execute multiple SQL, you call db2 like this:

db2 -tf script.sql

script.sql

create table t1 (c1 int);
create table 
  t2 (c2 int);

When executing a script that contains Stored procedures, triggers or functions, you can separate them with any character and define the statement termination character via the -td option. Most of the time @ is used.

db2 -td@ -f scriptSQLPL.sql

scriptSQLPL.sql

CREATE PROCEDURE TEST ()
 RESULT SETS 1
 LANGUAGE SQL
BEGIN
 DECLARE value INT DEFAULT 1;

 SET value = 0;
END @

If you do not specify any statement termination character, every single line separated by a carriage return will be considered as a statement. However, you can put backslashes in order to execute multilines statements

db2

create \
table \
t1 \
( col1 int )

In the last example, Interactive mode was used instead of using a file.

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

In Oracle SQL*PLUS you use semicolon ; as SQL terminator and . as PL/SQL block

terminator. Unlike sql terminator(;), which terminates and executes SQL statement, PL/SQL block

terminator(.) only informs SQL*PLUS that the block has ended and left in the buffer for further editing

if such a need arises or execution using slash /.

You can change SQl terminator by issuing SET SQLTERMINATOR ':' (for example).

You can change PL/SQL block terminator by issuing SET BLOCKTERMINATOR '!'(for example).

To terminate and execute a PL/SQL block you use forward slash /.

Upvotes: 1

Related Questions