깡통

mysql 원격접속 권한 설정

>use mysql
>grant all privileges on *.* to 'oops'@'192.168.1.1'
>identified by 'passwd' with grant option;

ip대신 %를 주면 모든 호스트 접속허용!

oops라는 유저가 모든 권한을 부여받을 수 있다. 단 해당 IP의 Host 에서만,

이와 같은 설정은 select host, user from user;로 확인해 볼 수 있다.

http://mysql.holywar.net/Downloads/Contrib/9168/177/oracledump.pl 

#!/usr/bin/perl -w
use strict;
use vars qw ( @ARGV $dbh $db_database $db_host $db_port $db_user $db_password
$default_precision $default_scale $default_index_length
$with_table_comments $with_col_comments
$no_data $no_foreign_keys $extended_inserts $complete_inserts $add_drop_table
$add_locks $insert_delayed $lock_tables $table_type $net_buffer_length
$nls_date_format $nls_time_format $nls_timestamp_format $verbose $output_tail);
use DBI;

#############################################
#
# oracledump.pl - Dumps table(s) from an Oracle database to MySQL format
#
# Use ./oracledump.pl without args to see usage info.
# You may also change internal default settings below
#
#
# Written by Johan Andersson <johan@andersson.net>, May 2001
# Needs DBI and DBD::Oracle Perl modules to work.
#############################################
#
# Changes by Guilhem Bichot <guilhem.bichot@mines-paris.org>, April 2002 :
# - Fixed little bugs
# * a one-column primary key was never included in the CREATE TABLE statement
# * an Oracle RAW column was not given any MySQL type
# * statements starting by ';' were produced for empty tables with extended-inserts
# - Oracle RAW columns are converted into VARCHAR BINARY or BLOB, instead of
# VARCHAR or TEXT.
# - Added foreign keys information in final ALTER TABLE ADD FOREIGN KEY statements
# and ALTER TABLE ADD INDEX (commented) as InnoDB may need additional indexes
# for referencing and referenced columns
# - Added option --no-foreign-keys, as retrieving foreign keys information
# from the Oracle views takes a long time
# - Added option --table-type=... to specify the MySQL table type
# in the CREATE TABLE statement
# - Added option --net-buffer-length. If using extended inserts, switches to a new INSERT
# when the length of the INSERT statement reaches net-buffer-length.
# - Made the connection method a bit more versatile : if the user specifies the database name,
# it can either be a valid connection descriptor
# alias specified in the tnsnames.ora file, or (this is what I have added) be of the type
# SID@host or SID@host:port .
# - Suppressed the tnsnames.ora check. In certain situations you had to tell the script
# where the tnsnames.ora was, while the Oracle OCI connection was able to find it without help.
# Moreover, 'grep' does not exist on Windows. Finally, if you specify a wrong
# connection alias descriptor, the Oracle connection dies with "TNS : could not resolve
# service name" which is a sufficient error message. So we do not need to check in the script,
# the Oracle connection does it enough.
# - Lenghtened the help message a bit

### SETTINGS #################################
#
# All settings here are default settings that are used if they're
# not specified as argument.
#

# DATABASE SETUP ####
$db_database = $ENV{'ORACLE_SID'} if defined($ENV{'ORACLE_SID'}); # Default Oracle SID
$db_host = undef;
$db_user = getlogin(); # Default username
$db_password = ''; # Default password

# Oracle session parameters for date/time formats. Do not change if you not
# know exactly what you're doing!
$nls_date_format = 'RRRR-MM-DD';
$nls_time_format = 'HH24:MI:SSXFF';
$nls_timestamp_format = "$nls_date_format $nls_time_format";


# DATA TYPE SETTINGS ####
$default_precision = 18; # Default when precision is missing on NUMBER/FLOAT
$default_scale = 0; # - " " - scale - " " - - " " - - " " -
$default_index_length = 10; # This only affects indexes that contain BLOB fields.
# Sets how big in bytes the index should be for those columns.

# MISC. SETTINGS
$no_data = 0; # Sets whether to retrieve table data or just the table structure
$no_foreign_keys = 0; # Sets whether to retrieve foreign keys or not
$with_col_comments = 1; # Enable comments to be included for each column (if they exist in Oracle)
$with_table_comments = 1; # Same, but for comments for each table.
$extended_inserts = 0; # Use extended INSERT INTO syntax to insert
# multiple rows within one statement (MySQL)
$complete_inserts = 0; # Use of complete inserts adds list of table column
# names used, after the INTO word in INSERT INTO
# statements (MySQL)
# Eg.:
# complete: INSERT INTO foo (col1, col2) VALUES (1,2);
# compact (default): INSERT INTO foo VALUES (1,2);
$add_drop_table = 0; # Specifies if we are going to add DROP TABLE
# statements in the output
$add_locks = 0; # Specifies whether to use locks around insert
# statements or not (MySQL)
$insert_delayed = 0; # Specified wheter to use INSERT DELAYED or not (MySQL)
$lock_tables = 0; # If true, all tables will be locked for read before
# fetching anything from them (Oracle)
$net_buffer_length = 1_047_551 ;

$verbose = 0; # If enabled, program activities are printed out on STDERR
##############################################

# Trap some useful signals to avoid seg fault, DBI handles left open etc.
$SIG{'TERM'} = *interrupt;
$SIG{'QUIT'} = *interrupt;
$SIG{'INT'} = *interrupt;

# Put auto-flush on
$| = 1;

# int main( void )
# Main sub routine
sub main {

# Parse arguments and get tables..
my @arg_tables = parseArgs();

# Create a database handle
$dbh = db_connect($db_database, $db_host, $db_port, $db_user, $db_password);
my $sth;

# Use the ALTER SESSION command to change the date format used
$dbh->do("ALTER SESSION SET NLS_DATE_FORMAT = '$nls_date_format'");
$dbh->do("ALTER SESSION SET NLS_TIME_FORMAT = '$nls_time_format'");
$dbh->do("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '$nls_timestamp_format'");

# Store tables in an array
my @tables;

# Check if any tables are specified as argument(s)
if($#arg_tables < 0) {
# Fetch all tables from USER_TABLES
$sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES");
$sth->execute();

my @row;
while(@row = $sth->fetchrow_array()) {
push @tables, $row[0];
}
$sth->finish();
}
else {
while(<;@arg_tables>) {
# Check if table exists
$sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ?");
$sth->execute($_);

if($sth->fetchrow_array()) {
push @tables, $_;
}
else {
warn "Table $_ does not existn";
}
$sth->finish();
}
}


# for each table

my $table;
foreach $table ( @tables ) {
print STDERR "* Checking table structure for table $tablen" if $verbose;

my @cols = describe_table($dbh, $table);
my %blobcols = (); # Keeps track on blob columns in indexes
my @colslist = ();
my $output = "";
my @quotecol = ();
my $lastcomment;

$output .= "nDROP TABLE IF EXISTS $table;" if $add_drop_table;
$output .= "nCREATE TABLE $table (";
for ( my $i = 0; $i <= $#cols; $i++ ) {
my(undef, undef, $datatype) = convert_dt($cols[$i]{'TYPE'}, $cols[$i]{'LENGTH'});
$output .= "nt".$cols[$i]{'NAME'}."t".$datatype;
$output .= " DEFAULT '".$cols[$i]{'DEFAULT'}."'" if defined($cols[$i]{'DEFAULT'});
$output .= " NOT NULL" if $cols[$i]{'NULL'} eq 'N';
$output .= "," if $i != $#cols;
$output .= "t# ".$cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i != $#cols;

# We must save the last comment if such exists, otherwise we may get
# an error later since a separating comma may be printed on the wrong place
$lastcomment = $cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i == $#cols;

# Save the insert values string

if($cols[$i]{'TYPE'} =~ /^(VAR)?CHAR2?|LONG( RAW)?|RAW|DATE$/) { #bug fix
$quotecol[$i] = 1;
}
else {
$quotecol[$i] = 0;
}

# Keep track on blob columns for indexes
if($datatype eq 'TEXT') {
$blobcols{$cols[$i]{'NAME'}} = 1;
}

# Keep a list of the columns to use when fetching data later
push @colslist, $cols[$i]{'NAME'};
}

# Get keys
print STDERR "* Fetching keys for table $tablen" if $verbose;
my %keys = get_keys($dbh, $table, $no_foreign_keys);

# Get primary key
my @pkcols = @{ $keys{'PRIMARY KEY'} };
my $pkstr = "";

if($#pkcols > -1) {
$output .= ",";

# print last column comment if such exist
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}

$output .= "ntPRIMARY KEY (";

for(my $i=0; $i<=$#pkcols; $i++) {
# Keep track on columns in the PK in a string format (used later with indexes)
$pkstr .= $pkcols[$i]."-";

$output .= $pkcols[$i];
# Add index size if column is a blob column. Required by MySQL
$output .= "(".$default_index_length.")" if defined($blobcols{$pkcols[$i]});
$output .= "," unless $i == $#pkcols;
}
$output .= ")";
}
# Primary key done!

# Get foreign keys
my $type;
my @fkcols;
my $rtable;
my @rfkcols;
my $fkstr;
my $add_fk_statement="";


$type='FOREIGN KEY';
my %foreign_keys = %{ $keys{$type} };

my $number_of_foreign_keys = scalar keys(%foreign_keys);
if ($number_of_foreign_keys>=1) {
$add_fk_statement.= "nALTER TABLE $table" ;
}

my $j=0;

while( my($key) = each %foreign_keys ) {
@fkcols = @{ $foreign_keys{$key}{'COLUMNS'} };
$rtable = $foreign_keys{$key}{'RTABLE'} ;
@rfkcols = @{ $foreign_keys{$key}{'RCOLUMNS'} };

$fkstr = "ntADD ".$type." (";
$output_tail .= "n#ALTER TABLE $table ADD INDEX (";

for(my $i=0; $i<=$#fkcols; $i++) {
$fkstr .= $fkcols[$i];
$fkstr .= "," if $i != $#fkcols;
$output_tail .= $fkcols[$i];
$output_tail .= "," if $i != $#fkcols;
}

$fkstr .= ") REFERENCES ".$rtable." (";
$output_tail .= ");n#ALTER TABLE $rtable ADD INDEX (";
for(my $i=0; $i<=$#rfkcols; $i++) {
$fkstr .= $rfkcols[$i];
$fkstr .= "," if $i != $#rfkcols;
$output_tail .= $rfkcols[$i];
$output_tail .= "," if $i != $#rfkcols;

}

$fkstr .= ")";
$fkstr .= "," if $j != ($number_of_foreign_keys-1) ;
$output_tail .= ");";
$j++;

$add_fk_statement .= $fkstr;
}

if ($number_of_foreign_keys>=1) {
$output_tail .= "n#If the ALTER TABLE $table...ADD FOREIGN KEY... statement below fails with errno 150,n#uncomment some of the lines above and re-run the failed statementn";
$output_tail .= $add_fk_statement;
$output_tail .= ";n" ;
}

# Get indexes (non-unique and unique)
my @idxcols;
my $idxstr;
my $tmp_cols;

print STDERR "* Fetching indexes for table $tablen" if $verbose;

for $type ( 'UNIQUE', 'INDEX' ) {
my %indexes = %{ $keys{$type} };

while( my($key) = each %indexes ) {
@idxcols = @{ $indexes{$key} };

$idxstr = "nt".$type." ".$key." (";
$tmp_cols = "";

for(my $i=0; $i<=$#idxcols; $i++) {
# Keep track on index columns in a string format
$tmp_cols .= $idxcols[$i]."-";

$idxstr .= $idxcols[$i];
$idxstr .= "(".$default_index_length.")" if defined($blobcols{$idxcols[$i]});
$idxstr .= "," if $i != $#idxcols;
}

$idxstr .= ")";

# Check if this index is the PK index
# Since the PK already is specified above we should not
# include that index here.. But if the type not is UNIQUE
# this index is not the PK (just a non-unique duplicate of the
# PK index)
#
# You shouldn't be able to create such a index in Oracle
# (it would return an ORA-01408 error), but since MySQL may
# have duplicate indexes of different uniquenesses we do it
# the MySQL way to be sure..
if($tmp_cols ne $pkstr || $type eq 'INDEX') {
$output .= ",";

# Append last column comment if exists
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}

$output .= $idxstr;
}
}
}

# Append last column comment if exists
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}

$output .= "n)".( (defined($table_type)) ? " TYPE=$table_type" : "" ) .";n";

print STDERR "* Printing CREATE TABLE statement for table $tablen" if $verbose;
print $output;

if(!$no_data) {
# Fetch the data
print STDERR "* Obtaining locks on table $table on Oraclen" if $verbose && $lock_tables;
$dbh->do("LOCK TABLE $table IN SHARE MODE NOWAIT;") if $lock_tables;

print STDERR "* Preparing SELECT statement to fetch data from table $tablen" if $verbose;
$sth = $dbh->prepare("SELECT ".join(", ", @colslist)." FROM $table");
$sth->execute();

# Put together the insert statement. Take care of complete/"compact" options
my $insert_stmt = "INSERT ";
$insert_stmt .= "DELAYED " if $insert_delayed;
$insert_stmt .= "INTO $table ";
$insert_stmt .= "(".join(", ", @colslist).") " if $complete_inserts;
$insert_stmt .= "VALUES ";

my $istmt_nprinted = 1;
my $total_length;
my $tmpstr;

print STDERR "* Printing LOCK TABLES statement for table $tablen" if $verbose && $add_locks;
print "LOCK TABLES $table WRITE;n" if $add_locks;

print STDERR "* Starting to print INSERT INTO statement(s)n" if $verbose;


while(my @row = $sth->fetchrow_array()) {

$tmpstr = "(";

for(my $i=0; $i<=$#colslist; $i++) {
if(defined($row[$i]) && $quotecol[$i]) {
$tmpstr .= $dbh->quote(db_escape($row[$i]));
}
elsif(defined($row[$i]) && !$quotecol[$i]) {
$tmpstr .= $row[$i];
}
else {
$tmpstr .= "NULL";
}
$tmpstr .= "," if $i != $#colslist;
}
$tmpstr .= ")";

if(!$extended_inserts) {
print "$insert_stmt$tmpstr;n";
}
else {
if ($istmt_nprinted) {
print "$insert_stmt$tmpstr";$istmt_nprinted=0;
$total_length=length($insert_stmt)+length($tmpstr);
}
else {
if ( ($total_length+length($tmpstr))>($net_buffer_length-100) ) #100 if for security
#break the current extended INSERT statement and start a new one
{
print ";n$insert_stmt";
$total_length=length($insert_stmt)+length($tmpstr);
}
else {
print ",";$total_length+=length($tmpstr)+1;
}
print $tmpstr;
}
}
}

print ";n" if (($extended_inserts == 1) && ($istmt_nprinted == 0));

print "UNLOCK TABLES;n" if $add_locks;
$sth->finish();
print STDERR "* Done! Printed rows for table $tablen" if $verbose;

}
}

print STDERR "* Printing ALTER TABLE ADD FOREIGN KEY statements for all tablesn" if ($verbose && !$no_foreign_keys);
print $output_tail if !$no_foreign_keys;

$dbh->disconnect();
return 1;
}

# void interrupt ( void )
# This sub-routine is taking care of signals sent to the program
sub interrupt {
my ($sig) = @_;
print STDERR "Got $sig-signal; Issuing database disconnect and program exitn";
print STDERR "Warning: Active statements cannot be closed, exiting program now would cause a seg fault.nWarning: Quiting "safely" _without_ disconnecting from the database (to avoid seg. fault)n" if $dbh->{'ActiveKids'};

$dbh->disconnect() if ! $dbh->{'ActiveKids'};

exit(3);
}

# array describe_table( database_handle dbh, scalar table_name )
# Returns an array of hashes with info about the columns in the given table
sub describe_table {
my($dbh, $table_name) = @_;

# Get columns for the specified table
my $sth = $dbh->prepare("SELECT a.COLUMN_ID,
a.COLUMN_NAME,
a.DATA_TYPE,
a.DATA_LENGTH,
a.DATA_PRECISION,
a.DATA_SCALE,
a.NULLABLE,
a.DATA_DEFAULT,
b.COMMENTS
FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b
WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME");
$sth->execute($table_name);

my @row;
my @columns;
my $length;
while(@row = $sth->fetchrow_array()) {
if($row[2] eq "NUMBER" || $row[2] eq "FLOAT") {
# Set length to default precision/scale values when information
# is missing in the data
$length = (defined($row[4])) ? $row[4] : $default_precision;
$length .= ",";
$length .= (defined($row[5])) ? $row[5] : $default_scale;
}
else {
$length = $row[3];
}

# Trim default data (and remove quotes that oracle stores from CREATE/ALTER command)
$row[7] =~ s/^'([^']+)'s*$/$1/ if defined($row[7]);

# Strip linebreaks in comments
$row[8] =~ s/n// if defined($row[8]);

# Build hash of info about this column
my %column = ( 'NAME' => $row[1],
'TYPE' => $row[2],
'LENGTH' => $length,
'NULL' => $row[6],
'DEFAULT' => $row[7],
'COMMENT' => $row[8] );
# Add the info about this column to the @columns array,
# set the index to the COLUMN_ID to get the "original" order
$columns[--$row[0]] = { %column };
}
$sth->finish();

# Return list of hashes
return @columns;
}

# hash get_keys( database_handle dbh, scalar table_name, scalar no_foreign_keys )
# returns hash of hashes with information of the constraints in the given table
#
# hash = {
# 'PRIMARY KEY' = [ COLNAME1, COLNAME2, ... ],

# 'FOREIGN KEY' = {
# KEYNAME => { 'COLUMNS' => [COLNAME1, COLNAME2, ... ],
# 'RTABLE' => RTABLENAME, "R" means referenced
# 'RCOLUMNS' => [ RCOLNAME1, RCOLNAME2, ... ]
# };
# },

# 'UNIQUE' = {
# KEYNAME => [ COLNAME1, COLNAME2, ... ]
# },
# 'INDEX' = {
# KEYNAME => [ COLNAME1, COLNAME2, ... ]
# }
# }

sub get_keys {
my($dbh, $table_name, $no_foreign_keys) = @_;

my %keys = (
'PRIMARY KEY' => [],
'FOREIGN KEY' => {},
'UNIQUE' => {},
'INDEX' => {}
);
my ( @row, $type, $sth );

# Fetch primary key

$type = 'PRIMARY KEY' ;
$sth = $dbh->prepare("SELECT a.constraint_name, a.column_name, a.position
FROM user_cons_columns a, user_constraints b
WHERE a.table_name=? AND a.constraint_name=b.constraint_name AND
b.constraint_type='P'
ORDER BY a.constraint_name, a.position");
$sth->execute($table_name);

while( @row = $sth->fetchrow_array()) {
$keys{$type}[--$row[2]] = $row[1];
}

$sth->finish();
# Primary key done!

# Retrieve foreign keys information
# Slow due to a 3-view join
if (!$no_foreign_keys)
{
$type = 'FOREIGN KEY' ;
$sth = $dbh->prepare("SELECT a.constraint_name, a.column_name, a.position,
c.table_name,c.column_name
FROM user_cons_columns a, user_constraints b,
user_cons_columns c
WHERE a.table_name=? AND a.constraint_name=b.constraint_name AND
b.constraint_type='R'
AND b.r_constraint_name=c.constraint_name
AND a.position=c.position
ORDER BY a.constraint_name");
$sth->execute($table_name);

while( @row = $sth->fetchrow_array()) {
$keys{$type}{$row[0]}{'COLUMNS'}[$row[2]-1] = $row[1];
$keys{$type}{$row[0]}{'RTABLE'} = $row[3];
$keys{$type}{$row[0]}{'RCOLUMNS'}[$row[2]-1] = $row[4];
}

$sth->finish();
# foreign keys done!
}

# Fetch indexes
$sth = $dbh->prepare("SELECT a.index_name, a.uniqueness, b.column_name, b.column_position
FROM user_indexes a, user_ind_columns b
WHERE a.table_name = ? AND b.table_name = a.table_name AND b.index_name = a.index_name
ORDER BY a.uniqueness, a.index_name");
$sth->execute($table_name);

while( @row = $sth->fetchrow_array() ) {
# Get index type; UNIQUE or INDEX (non-unique of course)
$type = "UNIQUE" if $row[1] eq 'UNIQUE';
$type = "INDEX" if $row[1] eq 'NONUNIQUE';

$keys{$type}{$row[0]}[--$row[3]] = $row[2];
}
$sth->finish();

return %keys;
}

# scalar db_escape ( scalar string )
# Escapes a string to be used within a SQL statement
sub db_escape {
my($str) = @_;

my $newstr = "";
for(my $i=0; $i<length($str); $i++) {
my $c = substr($str, $i, 1);

if(ord($c) == 10) {
$newstr .= chr(92).'n';
}
elsif(ord($c) == 13) {
$newstr .= chr(92).'r';
}
elsif(ord($c) == 9) {
$newstr .= chr(92).'t';
}
elsif(ord($c) == 34 || ord($c) == 92) {
$newstr .= chr(92).chr(ord($c));
}
else {
$newstr .= chr(ord($c));
}
}

return $newstr;
}

# list convert_dt( scalar datatype, scalar datalength )
# Converts datatype to mysql format and returns it
sub convert_dt {
my($datatype, $datalength) = @_;

############################################
# Rules are..:
# Datatype (ORA) Length Returns (MySQL)
# NUMBER Any NUMERIC
# DEC Any NUMERIC
# DECIMAL Any NUMERIC
# NUMERIC Any NUMERIC
# DOUBLE PRECISION Any NUMERIC
# FLOAT Any NUMERIC
# REAL Any NUMERIC
# SMALLINT Any SMALLINT
# VARCHAR <256 VARCHAR
# VARCHAR2 <256 VARCHAR
# CHAR <256 CHAR
# VARCHAR2 >255 TEXT
# VARCHAR >255 TEXT
# CHAR >255 TEXT
# LONG <256 VARCHAR
#LONGRAW does not exist, it is LONG RAW
# LONG RAW <256 VARCHAR BINARY (in Oracle RAW means binary data)
# RAW <256 VARCHAR BINARY
# LONG >255 TEXT
# LONG RAW >255 BLOB
# RAW >255 BLOB
# DATE - DATETIME (Since DATE in oracle can include time information!)
#
# List return consists of:
# 1. Datatype name
# 2. Datalength
# 3. Complete datatype spec. to be used within CREATE TABLE statement
#############################################
if($datalength eq '0') {
$datalength = 32767;
}

return ('NUMERIC', $datalength, 'NUMERIC('.$datalength.')') if $datatype =~ /^DEC(IMAL)?|NUMERIC|NUMBER|DOUBLE PRECISION|FLOAT|REAL$/;
return ('INTEGER', 38, 'INTEGER(38)') if $datatype =~ /^(SMALL)?INT(EGER)?$/;

return ('VARCHAR', $datalength, 'VARCHAR('.$datalength.')') if $datatype =~ /^N?VARCHAR2?|LONG$/ && $datalength <= 255;
return ('TEXT', '', 'TEXT') if $datatype =~ /^N?(VAR)?CHAR2?|LONG$/ && $datalength > 255;

return ('VARCHAR BINARY', $datalength, 'VARCHAR('.$datalength.') BINARY') if $datatype =~ /^LONG RAW|RAW$/ && $datalength <= 255;
return ('BLOB', '', 'BLOB') if $datatype =~ /^LONG RAW|RAW$/ && $datalength > 255;

return ('CHAR', $datalength, 'CHAR('.$datalength.')') if $datatype =~ /^N?CHAR$/ && $datalength <= 255;
return ('DATETIME', '', 'DATETIME') if $datatype eq 'DATE';
}

# db_connect( scalar database, scalar host, scalar port, scalar user, scalar password )
# database_handle connects to the database
sub db_connect {
my($database, $host, $port, $user, $password) = @_;

my $connect_string = defined($host) ? ("host=$host;sid=$database".(defined($port) ? ";port=$db_port" : "")) : $database ;

return DBI->connect( "dbi:Oracle:$connect_string", $user, $password,
{ AutoCommit => 0, RaiseError => 0, PrintError => 1, LongReadLen => 0, LongTruncOk => 1 } );
}

# array parseArgs( void )
# returns array of tables given as argument or invokes the printError() sub-routine
# if some error occurs, which exits the program
sub parseArgs {

if($#ARGV < 0) {
printUsage();
exit;
}

my $tmpcmd = undef;
my @arg_tables;
my $db_database_complete;
my $got_db = 0;
my $show_config_only = 0;
my $use_default_db = 0;

while(<;@ARGV>) {
my $arg = $_;
if(defined($tmpcmd)) {
SWITCH: for ($tmpcmd) {
/^user$/ && do {
$db_user = $arg;
$tmpcmd = undef;
last SWITCH;
};
/^password$/ && do {
$db_password = $arg;
$tmpcmd = undef;
last SWITCH;
};
}
}
else {
SWITCH: {
/^-h|--help$/ && do {
printUsage();
exit 0;
};
/^-u|--user=(.+)$/ && do {
if(defined($1)) {
$db_user = $1;
}
else {
$tmpcmd = 'user';
}
last SWITCH;
};
/^-p|--password=(.+)$/ && do {
if(defined($1)) {
$db_password = $1;
}

MySQL 5.0에서 Trigger 사용하기. ^^*

※ Trigger 생성하기

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

[참고] 트리거의 데이터는 DB 데이터가 위치하는 곳에 파일로 저장되며, 확장자명은 보통 TRG 이다.

해당 트리거의 Table 정보는 TRN 파일로 저장된다.

즉, 위와 같은 경우에는 ins_sum.TRG와 ins_sum.TRN 파일로 저장된다.

ins_sum.TRN 파일의 내용을 보면 다음과 같다.

TYPE=TRIGGERNAME
trigger_table=account <-- 테이블명

 

※ Trigger 시험하기

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48               |
+-----------------------+

 

※ Trigger 확인하기

mysql> show triggers;
+----------------+--------+-------------------+-------------------+--------+---------+----------+----------------+
| Trigger        | Event  | Table             | Statement         | Timing | Created | sql_mode | Definer        |
+----------------+--------+-------------------+-------------------+--------+---------+----------+----------------+
| Trigger Name   | INSERT | 관련 Table Name   | BEGIN  .....  END | BEFORE | NULL    |          | user@localhost |
+----------------+--------+-------------------+-------------------+--------+---------+----------+----------------+

※ Trigger 삭제하기

mysql> DROP TRIGGER test.ins_sum;

 

※ Trigger 생성하기의 또 다른 방법

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

※ Error 보기

mysql> show errors;
Empty set (0.00 sec)

※ Trigger에서 사용하는 변수명은 반드시!! Table의 Column명과 다른 것을 사용(대소문자 구분없음)해야 한다. 만약 같은 변수명을 사용시 해당 값이 의도된 값이 아닌 다른(보통 Default값)으로 설정되게 된다.

MySQL의 show 명령에 대한 자세한 사항을 보고자 한다면 다음을 클릭.

 http://blog.naver.com/iamfreeman/50036208287

//------------------------------------------------------------------------//

참고 사이트 : http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

17.3.1. Trigger Syntax

To create a trigger or drop a trigger, use the CREATE TRIGGER or DROP TRIGGER statement. The syntax for these statements is described in Section 12.1.10, “CREATE TRIGGER Syntax”, and Section 12.1.17, “DROP TRIGGER Syntax”.

Here is a simple example that associates a trigger with a table for INSERT statements. The trigger acts as an accumulator, summing the values inserted into one of the columns of the table.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

The CREATE TRIGGER statement creates a trigger named ins_sum that is associated with the account table. It also includes clauses that specify the trigger activation time, the triggering event, and what to do with the trigger activates:

• The keyword BEFORE indicates the trigger action time. In this case, the trigger should activate before each row inserted into the table. The other allowable keyword here is AFTER.
• The keyword INSERT indicates the event that activates the trigger. In the example, INSERT statements cause trigger activation. You can also create triggers for DELETE and UPDATE statements.
• The statement following FOR EACH ROW defines the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering statement In the example, the triggered statement is a simple SET that accumulates the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.”

To use the trigger, set the accumulator variable to zero, execute an INSERT statement, and then see what value the variable has afterward:

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48               |
+-----------------------+

In this case, the value of @sum after the INSERT statement has executed is 14.98 + 1937.50 - 100, or 1852.48.

To destroy the trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema:

mysql> DROP TRIGGER test.ins_sum;

Triggers for a table are also dropped if you drop the table.

Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

In addition to the requirement that trigger names be unique for a schema, there are other limitations on the types of triggers you can create. In particular, you cannot have two triggers for a table that have the same activation time and activation event. For example, you cannot define two BEFORE INSERT triggers or two AFTER UPDATE triggers for a table. This should rarely be a significant limitation, because it is possible to define a trigger that executes multiple statements by using the BEGIN ... END compound statement construct after FOR EACH ROW. (An example appears later in this section.)

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. A column named with NEW can be referred to if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or that are used to update a row.

In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the automatically generated sequence number that will be generated when the new record actually is inserted.

OLD and NEW are MySQL extensions to triggers.

By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is allowed within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition. The following example illustrates these points. It defines an UPDATE trigger that checks the new value to be used for updating each row, and modifies the value to be within the range from 0 to 100. This must be a BEFORE trigger because the value needs to be checked before it is used to update the row:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to invoke the same routine from within several triggers.

There are some limitations on what can appear in statements that a trigger executes when activated:

• The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are allowed to return data to the trigger through OUT or INOUT parameters.)
• The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.
• Prior to MySQL 5.0.10, triggers cannot contain direct references to tables by name.

MySQL handles errors during trigger execution as follows:

• If a BEFORE trigger fails, the operation on the corresponding row is not performed.
• A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.
• An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully.
• An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.
• For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For non-transactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.

Previous / Next / Up / Table of Contents

User Comments

Add your own comment.