The stored procedure for Oracle Learning notes

Stored Procedures

In Oracle, you can define subroutines in the database, which are called stored Procedures (procedure). It is stored in a data dictionary that can be shared between different users and applications, and can be optimized and reused for the program. The advantages of using stored procedures are:

(1) The process runs on the server side and executes faster.

(2) The process executes once after the code resides in the buffer memory, in the future operation, simply call the compiled code from the buffer memory to execute, improve the system performance.

(3) Ensure the security of the database. Instead of authorizing users to access some of the tables in the application directly, the user is authorized to perform the process of accessing those tables. Authorized users who are not tables cannot access these tables unless they pass the procedure.

(4) Automatic completion of tasks that need to be performed beforehand. The process can be executed automatically when the system starts, without having to perform manual operation after the system starts, greatly facilitates the user's use, and can automatically accomplish some tasks that need to be performed beforehand.

Note: You cannot create statements in the user's definition using the following objects:

Create VIEW, create DEFAULT, create RULE, create PROCEDURE, create TRIGGER

SQL command to create a stored procedure :

Syntax format:

CREATE [OR REPLACE] PROCEDURE [Schema.] Procedure_name describes the structure and notation of the stored procedure as follows:

Example: [A1]

Write a stored procedure:

For example, the stored procedure inquires the employee's salary and employee number in the department according to the department number, and if the employee's salary is lower than the average wage of the department, the salary increment is the average wage;

For example: The above title plus a condition, after Zhang Yizhi, will pay up the staff information in the form of a cursor back;

For example, write a stored procedure that returns the results of the empno and ename queries in the EMP with an out type cursor;

Declaring a cursor in a package:

Createorreplacepackage T is

type record_type isrecord (empno number,ename varchar2 (20));

type cursor_type isrefcursorreturn record_type;  

End T;

Stored Procedure Code:

Createorreplaceprocedure myprocedure(cur_out out t.cursor_type) is

Begin

Open cur_out forselect empno,ename from EMP;

End Myprocedure;

Test code:

--Created on 2017/10/19 by ADMINISTRATOR

Declare

type cur_type isrefcursor; -- Weakly typed cursors

cur cur_type;

type record_type isrecord (empno number,ename varchar2 (20));

employee Record_type;

Begin

myprocedure (cur);

Loop

fetch cur into employee;

Dbms_output.put_line (employee.ename| | ': ' | | employee.empno);

exitwhen cur%notfound;

Endloop;  

End

Example 2:

Method:

Create or Replace function login (username in varchar2,pwd-VARCHAR2) return number is

Res number (5);

Flag number (1): = 0;

Begin

Select COUNT (*) into res from EMP where ename=username and empno=pwd;

If Res>0 Then

Flag:=1;

End If;

return flag;

End login;

Stored procedures:

Create or Replace procedure Empprocedure (username in varchar2,pwd in Varchar2,controlflag in Varchar2,

Cur_emp out Pac.cur_type1,cur_dpet out pac.cur_type2

) is

Flag number (1);

Begin

Flag:=login (USERNAME,PWD); -- Verify that the user is logged on successfully

If Flag=1 -- Login Successful

Then

-- Judge Controlflag 1 View the Employee Information 2 View the EMP table Data 3 View the employee Department information

Case Controlflag

When 1 then -- View the employee information

Open Cur_emp for

SELECT * from EMP where empno=pwd;

When 2 Then

Open Cur_emp for

SELECT * from EMP;

When 3 Then

Open Cur_dpet for

SELECT * FROM dept where deptno= (select Deptno from emp where emp.empno=pwd);

End case;

Else-- Login failed

Dbms_output.put_line (' Landing failed ');

End If;

End Empprocedure;

Package:

Type R is record (Empno number (5), ename varchar2, Job varchar2), Mgr Number (5), HireDate date,sal number (15,2), comm Number (15,2), Deptno number (5));

Type CUR_TYPE1 is REF CURSOR return r;

Type P is record (Deptno number (5), Dname varchar2 (a), loc varchar2 (20));

Type cur_type2 is REF CURSOR return p;

Test:

--Created on 2017/10/21 by ADMINISTRATOR

Declare

Type R is record (Empno number (5), ename varchar2, Job varchar2), Mgr Number (5), HireDate date,sal number (15,2), comm Number (15,2), Deptno number (5));

Type cur_type1 is REF CURSOR;--note cannot be declared as return coercion type

Type P is record (Deptno number (5), Dname varchar2 (a), loc varchar2 (20));

Type cur_type2 is REF CURSOR;

Cur1 Cur_type1;

CUR2 cur_type2;

Res_1 R;

Res_2 p;

Flag number (1): = 2;

Begin

Empprocedure (' SMITH ', ' 123 ', FLAG,CUR1,CUR2);

If Flag=1 or flag=2 then

Loop

Fetch CUR1 into res_1;

Exit when Cur1%notfound;

Dbms_output.put_line (res_1.empno| | res_1.ename| | Res_1.hiredate);

End Loop;

Elsif Flag=3 Then

Loop

Fetch CUR2 into res_2;

Exit when Cur2%notfound;

Dbms_output.put_line (res_2.deptno| | res_2.dname| | RES_2.LOC);

End Loop;

End If;

Exception when others and then Dbms_output.put_line (' System exception ');

End

305 Class Example:

Package:

Create or Replace package Empdeptpac is

Type Cur_emptype is REF CURSOR return emp%rowtype;

Type Cur_depttype is REF CURSOR return dept%rowtype;

End Empdeptpac;

Function:

Create or Replace function Emplogin (username in varchar2,pwd with VARCHAR2) return Boolean is

Total number (10);

Flag Boolean: =false;

Begin

Select COUNT (*) into total from emp where ename =username and empno=pwd;

If Total>0 Then

Flag:=true;

else Flag:=false;

End If;

return flag;

End Emplogin;

Stored procedures:

Create or Replace procedure Empdeptpro (username in varchar2,pwd in Varchar2,flag in Varchar2,

Empitems out Empdeptpac.cur_emptype, deptitems off Empdeptpac.cur_depttype, empres out Emp%rowtype

) is

f Boolean;

Begin

F:=emplogin (USERNAME,PWD);

If F Then

Case Flag

When ' A ' then-- query all employee information

Open Empitems for SELECT * from EMP;

When the ' B ' then-- Query your department information

Open Deptitems for select dept.* from emp,dept where Dept.deptno=emp.deptno and emp.empno=pwd;

When ' C ' then-- search for personal information

Select emp.* to Empres from EMP where emp.empno=pwd;

End case;

End If;

End Empdeptpro;

Test:

--Created on 2017/11/4 by ADMINISTRATOR

Declare

Type curtype_dept is REF CURSOR;

Type curtype_emp is REF CURSOR;

Cur_dept curtype_dept;

Cur_emp curtype_emp;

Emprow Emp%rowtype;

Begin

Empdeptpro (' SMITH ', 7369, ' A ', cur_emp,cur_dept,emprow);

Loop

Fetch cur_emp into Emprow;

Dbms_output.put_line (Emprow.empno);

Exit when Cur_emp%notfound;

End Loop;

End

①in: Indicates that the parameter is input to the process;

②out: Indicates that the parameter will be assigned in the process and can be passed to the outside of the process body;

③in out: Indicates that a parameter of this type can either pass a value to the procedure body or be assigned a value in the procedure body.

The stored procedure for Oracle Learning notes