VastbaseG100

存储过程或函数调用

无参数

# CREATE FUNCTION
CREATE OR REPLACE FUNCTION "public"."function1" () RETURNS integer AS
$body$
DECLARE
  myvar INTEGER;
BEGIN
  SELECT INTO myvar fld FROM table1 LIMIT 1;
  RETURN myvar;
END;
$body$
LANGUAGE 'plpgsql'; 

# Perl
my $sth = $dbh->prepare("SELECT function1()");
$sth->execute( ) or die $DBI::errstr;
 
# 循环输出所有数据
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}
 
$sth->finish();
$dbh->disconnect();

带参数

# CREATE FUNCTION
CREATE OR REPLACE FUNCTION "public"."function2" (uname_val varchar) RETURNS integer AS
$body$
DECLARE
  myvar INTEGER;
BEGIN
  SELECT INTO myvar fld FROM table1 where uname = uname_val ;
  RETURN myvar;
END;
$body$
LANGUAGE 'plpgsql';

# Perl
my $uname_val = 'test';
my $sth = $dbh->prepare("SELECT function2(?)");
$sth->execute( $uname_val) or die $DBI::errstr;
 
# 循环输出所有数据
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}
 
$sth->finish();