How to use WITH statement

This article mainly shows you "how to use WITH sentences", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use WITH sentences" this article.

In 12c, you can use SQL to run PL/SQL functions or procedures faster, which are defined and declared by the WITH statement of the SQL statement.

How to define and declare a procedure or function in a WITH statement:

WITH PROCEDURE | FUNCTION test1 (…) BEGINEND

SELECT FROM table_name;/

Although you cannot use the WITH statement directly in the PL/SQL unit, it can be referenced by a dynamic SQL in the PL/SQL unit.

SQL > set serveroutput on

SQL > with function Is_Number

2 (x in varchar2) return varchar2 is

3 Plsql_Num_Error exception

4 pragma exception_init (Plsql_Num_Error,-06502)

5 begin

6 if (To_Number (x) is NOT null) then

7 return'Y'

8 else

9 return''

10 end if

11 exception

12 when Plsql_Num_Error then

13 return'N'

14 end Is_Number

15 select Is_Number ('test') is_num from dual

16 /

The above is all the contents of this article "how to use WITH sentences". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!