PL/oraSQL is a loadable procedural language for the LightDB database system. It is used to be compatible with Oracle PL/SQL applications. The design goals of PL/oraSQL were to create a loadable procedural language that
can be used to create functions, procedures, and triggers,
adds control structures to the SQL language,
can perform complex computations,
inherits all user-defined types, functions, procedures, and operators,
can be defined to be trusted by the server,
is easy to use.
Functions created with PL/oraSQL can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.
In LightDB 22.2 and later, PL/oraSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.
SQL is the language LightDB and most other relational databases use as query language. It's portable and easy to learn. But every SQL statement must be executed individually by the database server.
That means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All this incurs interprocess communication and will also incur network overhead if your client is on a different machine than the database server.
With PL/oraSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.
Compatible with Oracle PL/SQL stored procedure language
Extra round trips between client and server are eliminated
Intermediate results that the client does not need do not have to be marshaled or transferred between server and client
Multiple rounds of query parsing can be avoided
This can result in a considerable performance increase as compared to an application that does not use stored functions.
Also, with PL/oraSQL you can use all the data types, operators and functions of SQL.
Functions written in PL/oraSQL can accept
as arguments any scalar or array data type supported by the server,
and they can return a result of any of these types. They can also
accept or return any composite type (row type) specified by name.
It is also possible to declare a PL/oraSQL
function as accepting record, which means that any
composite type will do as input, or
as returning record, which means that the result
is a row type whose columns are determined by specification in the
calling query.