Oracle creates stored procedures

1. Basic grammar

Create or replace procedure Update_emp_sal
(Name in Out type,
Name in Out type, ...
) is
Begin
End Update_emp_sal;

2. Write a simple example to modify the Ename field of the EMP table

Create or replace procedure Update_emp
(
V_empno VARCHAR2,
V_ename VARCHAR2
) is
Begin
Update emp set ename=v_ename where Empno=v_empno;
End Update_emp;

The calling method is as follows:

Sql>exec update_emp (' 7935 ', ' test ');

2. Stored procedure with return value

Just write a simple Sal value that returns empno=7935.

Create or replace procedure Emp_out_sal
(
V_empno in Varchar2,
V_sal out number
) is
Vsal Number (7,2);
Begin
Select Sal into Vsal from EMP where empno=v_empno;
V_sal:=vsal;
End

Call a procedure that has a return value

sql> var vsal number
sql> exec emp_out_sal (' 7935 ',: vsal);
PL/SQL procedure successfully completed
Vsal
---------
the
sql> var vsal number
Sql> call Emp_out_sal (' 7935 ',: vsal);
Method called
Vsal
---------
700

Ii. Oracle Creation functions (function)

1. The basic grammar rules are as follows:

Create or Replace function (name in type, name in type, ...) return number is
Result number;
Begin
return (Result);
End;

2. Write a simple query example to query the SAL value of empno=7935

Create or Replace function ret_emp_sal (v_ename varchar2)
return number
Is
V_sal Number (7,2);
Begin
Select NVL (Sal,0) into the v_sal from EMP where lower (ename) =lower (v_ename);
return v_sal;
End

Call this function:

sql> var vsla number
Sql> call Ret_emp_sal (' 7935 ') into:vsal;
Method called
Vsal
---------
700

Iii. Oracle Create Package

Packages are used to assemble logically related procedures and functions, which consist of a package specification and a package body of two parts. Package specifications are used to define common constants, variables, procedures, and functions, and creating package specifications can use the Create Package command, creating packages that can be

1. Create Package Specification

Create Package emp_pkg is
Procedure Emp_update_ename (V_empno varchar2,v_ename varchar2);
function Emp_get_sal (v_empno varchar2) return number;
End

2. Create the package body

Create or replace package body emp_pkg
Is
Procedure Emp_update_ename
(
V_empno VARCHAR2,
V_ename VARCHAR2
)
Is
Vename varchar2 (+);
Begin
Update emp set ename=v_ename where Empno=v_empno;
Commit
Select Ename to Vename from EMP where empno=v_empno;
Dbms_output.put_line (' Employee Name: ' | | Vename);
End
function Emp_get_sal
(
V_empno VARCHAR2
)
Return number is
Vsal Number (7,2);
Begin
Select Sal into Vsal from EMP where empno=v_empno;
return vsal;
End
End

In this tip, creating the package body without creating the package specification will fail, to use the package, you must first create the package specification, and then create the package body

When you want to invoke the procedure and function of a package, precede the name of the procedure and function with the package name as the prefix (package name. subroutine name), and if you want to access packages for other scenarios, precede the name of the package with the schema name

(Scenario name. Package name. Subroutine name)

Instance:

sql> var vsla number
Sql> call Emp_pkg.emp_get_sal (' 7935 ') into:vsal;
Method called
Vsal
---------
the
sql> exec emp_pkg.emp_update_ename (' 7935 ', ' Helong ');
Employee Name: Helong
Sql> call Emp_pkg.emp_update_ename (' 7935 ', ' Helong ');
Method called
Sql> SELECT * from dual;
DUMMY
-----
X
Employee Name: Helong

Oracle creates stored procedures