Application of Oracle functions and stored procedures

Functions and stored procedures

In Oracle databases, you can use a single statement to add, delete, modify, and query a database. In addition, you can use multiple statements to form an I statement block and execute them together.
These statement blocks can be displayed and named and called by other applications. These named statement blocks are called functions and stored procedures.

Custom functions in Oracle;
Stored Procedures in Oracle;
Package functions and stored procedures-packages.

-- Function

-- A function is a common object in Oracle. Like other programming language functions, functions in Oracle must return a value. This is also an important feature that distinguishes functions from stored procedures.

-- Function introduction:
1. division of functions and functions
2. Function Parameters
3. function return value

Create a function

1. -- 1. Create a function
3. -- Create or replace function get_hello_msg return varchar2
4. -- Begin
5. -- Return 'Hello world ';
6. -- End get_hello_msg;
7. --/
9. SQL>Create Or Replace FunctionGet_hello_msg
10. ReturnVarchar2As
11. Begin
12. Return 'Hello world';
13. EndGet_hello_msg;
14. /
16. The function has been created.
21. -- 2. view the function information in the data dictionary. select object_name, object_type, status from user_objects where lower (object_name) = 'get _ hello_msg ';
23. SQL>SetLinesize: 180;
24. SQL>SelectObject_name, object_type, statusFromUser_objectsWhere Lower(Object_name) ='Get _ hello_msg';
26. OBJECT_NAME OBJECT_TYPE STATUS
27. Certificate --------------------------------------------------------------------------------------------------------------------------------------------------
28. GET_HELLO_MSGFUNCTIONVALID
32. -- 3. View function return values
34. -- Set serverout on;
35. -- Declare msg varchar2 (20 );
36. -- Begin
37. -- Msg: = get_hello_msg;
38. -- Dbms_output_line (msg );
39. -- End;
40. --/
42. SQL>SetServeroutOn;
43. SQL>DeclareMsg varchar2 (20 );
44. BeginMsg: = get_hello_msg; dbms_output.put_line (msg );End;
45. /
47. Hello world
49. The PL/SQL process is successfully completed.

1 2 3 4 5 6 7 Next Page