在plsql中编写函数 [英] Writing a function in plsql

问题描述

我正在查询数据库，需要解析字段之一以获取特定值(使用字符串函数).因此，我认为最好为此编写一个函数.我以前从未在plsql中编写过函数，因此我决定看一些示例.

I'm querying the DB and need to parse one of the fields for specific values (using string functions). So I figured it'd be best to write a function for it. I've never written a function before in plsql, so I decided to look at some examples.

我得到了一个简单的"square"函数的副本，该函数接受一个数字并返回其平方，但是不确定如何从SQL语句中调用它.

I got a copy of a simple "square" function which takes a number and returns the square of it, but am not sure how to call it from the SQL statement.

我只有读取权限.我将能够编写函数并使用它们来检索所需的数据吗?如果可以，怎么办?

I only have read access. Will I be able to write functions and use them to retrieve the data I need? And if so, how?

推荐答案

要创建功能，您的用户需要DBA授予该功能的CREATE PROCEDURE特权:

To be able to create a function your user needs the CREATE PROCEDURE privilege granted to it by the DBA:

grant create procedure to myschema;

如果您有特权，则可以创建这样的函数

If you have the privilege then you can create a function like this

create function square(n in number) return number
is
  return n*n;
end;

您可以像下面这样从SQL调用它:

And you can call it from SQL like this:

select num, square(num)
from mytable;

注意:在Oracle中，通常最好在包，在这种情况下，调用SQL就像:

Note: in Oracle it is usually preferred to create functions in packages, in which case the calling SQL would be like:

select num, mypackage.square(num)
from mytable;

这篇关于在plsql中编写函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！