Shulou(Shulou.com)05/31 Report--

This article shows you how to create stored procedures and stored functions in Oracle. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Select * from emp

-stored procedures-

-- definition

Create [or replace] procedure stored procedure name (parameter name [in] / out data type)

Is/as

Begin

-logical expressions

End [stored procedure name]

Define stored procedures to calculate annual salary and allow output

Create or replace procedure proc_salyears (v_no in number)

Is

Sal_years number (9Pol 2)

Begin

-- calculate annual salary

Select sal*12+nvl (comm,0) into sal_years from emp where empno=v_no

-- output

Dbms_output.put_line (sal_years)

End

-- calling stored procedures

Method 1:

Call proc_salyears (7788)

Mode 2:

Begin

Proc_salyears (7369)

End

-- stored procedure for out parameter

-- calculate the annual salary and return

Create or replace procedure proc_salyears (v_no in number,sal_years out number)

Is

Begin

-- calculate annual salary

Select sal*12+nvl (comm,0) into sal_years from emp where empno=v_no

End

-- calling stored procedures

Declare

V_sal number (9Pol 2)

Begin

Proc_salyears (7876)

Dbms_output.put_line (v_sal)

End

-Storage function-

-- definition

Create or replace function stores the function name (parameter name in/out data type)

Return data type

Is | as

Begin

Return specific data

End [Storage function name]

-define the storage function name to calculate the annual salary

Create or replace function fun_salyears (f_no number)

Return number

Is

Sal_years number (9Pol 2)

Begin

Select sal*12+nvl (comm,0) into sal_years from emp where empno=f_no

Return sal_years

End

-- using storage functions

Declare

Sal_yeats number (9Pol 2)

Begin

Sal_yeats: = fun_salyears (7876)

Dbms_output.put_line (sal_yeats)

End

Can be abbreviated

Begin

Dbms_output.put_line (fun_salyears (7369))

End

-the difference between stored procedures and stored functions-

Stored procedures are mostly used for data sharing between projects, and stored functions are often called by stored procedures.

Stored functions can be called in sql statements, but stored procedures cannot.

The above is how to create stored procedures and stored functions in Oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.