oracle-Process Function Programming

Process function Programming

Practical purpose

(1) Master the basic function of stored procedure and storage function advanced database object.

(2) Master the creation, modification, viewing and deletion of stored procedures and stored functions.

Practice Requirements

(1) record the execution of the command and operation of the problems encountered in the process and the solution, pay attention to explain the rationale.

(2) records the methods of managing stored procedures, stored functions, and triggers using Enterprise Manager.

(3) Record commands that use Sql*plus and plsql developer to manage stored procedures and store functions.

Practice Content

The data tables involved in the following tasks are the tables given by the Scott user.

1. Create a stored procedure

(1) Obtain the name and salary of a department employee.

(2) Obtain the sum of Wages and department name of a department.

(3) Output the name and salary grade of the employee of a department, above 5000 high, 3000-5000 Medium, 2000-3000 lower, low under 2000.

2. Create a storage function

(1) Obtaining the salary of an employee:

(2) Obtain the sum of the wages of a department.

(3) The output of an employee's name and salary grade, more than 5000 high, 3000-5000 Medium, 2000-3000 lower, 2000 low.

(4) Calculate the sum of the wages of the designated department and count the number of employees.

Experimental steps:

The data tables involved in the following tasks are the tables given by the Scott user.

1. Create a stored procedure

(1) Obtain the name and salary of a department employee.

Sql> Conn Scott/tiger

is connected.

Sql> set serveroutput on;

sql> Create or Replace procedure emp_table (E_deptno emp.deptno%type)

2 AS

3 Cursor E_cursor is

4 Select Ename,sal from emp where Deptno=e_deptno;

5 begin

6 for E_record in E_cursor loop

7 Dbms_output.put_line (e_record.ename| | ' ' | | E_record.sal);

8 End Loop;

9 End;

10/

The process has been created.

Sql> exec emp_table (20);

SMITH 800

JONES 2975

SCOTT 3000

ADAMS 1100

FORD 3000

sql> exec emp_table (10);

CLARK 2450

KING 5000

MILLER 1300

The PL/SQL process has completed successfully.

The PL/SQL process has completed successfully.

sql> exec emp_table (30);

ALLEN 1600

WARD 1250

MARTIN 1250

BLAKE 2850

TURNER 1500

JAMES 950

The PL/SQL process has completed successfully.

Sql>

(2) Obtain the sum of Wages and department name of a department.

sql> Create or Replace procedure emp_1 (E_deptno emp.deptno%type)

2 AS

3 Cursor Emp_cur is

4 Select Deptno,sum (SAL) as e_sal from EMP where Deptno=e_deptno Group by DEPTNO;

5 begin

6 for Emp_record1 in Emp_cur loop

7 Dbms_output.put_line (emp_record1.deptno| | ' ' | | Emp_record1.e_sal);

8 End Loop;

9 End;

10/

The process has been created.

sql> exec emp_1 (10);

10 8750

The PL/SQL process has completed successfully.

Sql> exec emp_1 (20);

20 10875

The PL/SQL process has completed successfully.

sql> exec emp_1 (30);

30 9400

The PL/SQL process has completed successfully.

Sql>

(3) Output the name and salary grade of the employee of a department, above 5000 high, 3000-5000 Medium, 2000-3000 lower, low under 2000.

sql> Create or Replace procedure Empno_name_level

2 (Dnum in Emp.empno%type,name out Emp.ename%type,level out varchar2)

3 AS

4 Salary Emp.sal%type;

5 begin

6 Select Sal,ename into Salary,name from EMP where dnum=empno;

7 case

8 when salary >5000 then level:= ' High ';

9 when salary between and level:= ' Medium ';

Ten when salary between and level:= ' lower ';

One else level:= ' low ';

The end case;

The end;

14/

The process has been created.

Sql> Declare

2 level VARCHAR2 (15);

3 name Emp.ename%type;

4 begin

5 empno_name_level (' 7788 ', name,level);

6 Dbms_output.put_line (' Name: ' | | name| | ' ' | | ' Level: ' | | level);

7 End;

8/

Name:scott Level: Medium

The PL/SQL process has completed successfully.

2. Create a storage function

(1) Obtaining the salary of an employee:

sql> Create or Replace function Empno_sal (Eno in Emp.empno%type)

2 return Emp.sal%type

3 AS

4 Salary Emp.sal%type;

5 begin

6 Select Sal into salary from EMP where eno=empno;

7 return salary;

8 End;

9/

The function has been created.

Sql> begin

2 Dbms_output.put_line (' Salary: ' | | Emp_sal (' 7499 '));

3 END;

4/

salary:1600

The PL/SQL process has completed successfully.

Sql> begin

2 Dbms_output.put_line (' Salary: ' | | Emp_sal (' 7788 '));

3 END;

4/

salary:3000

The PL/SQL process has completed successfully.

Sql> begin

2 Dbms_output.put_line (' Salary: ' | | Emp_sal (' 7900 '));

3 END;

4/

salary:950

The PL/SQL process has completed successfully.

(2) Obtain the sum of the wages of a department.

sql> Create or Replace function Salary_num (DNO in Emp.deptno%type)

2 return Emp.sal%type

3 is

4 Total emp.sal%type;

5 begin

6 select sum (SAL) into the total from EMP where dno=deptno;

7 return total;

8 End;

9/

The function has been created.

Sql> begin

2 Dbms_output.put_line (' total_salary: ' | | Total_salary (' 20 '));

3 END;

4/

total_salary:10875

The PL/SQL process has completed successfully.

Sql> begin

2 Dbms_output.put_line (' total_salary: ' | | Total_salary (' 10 '));

3 END;

4/

total_salary:8750

The PL/SQL process has completed successfully.

Sql> begin

2 Dbms_output.put_line (' total_salary: ' | | Total_salary (' 30 '));

3 END;

4/

total_salary:9400

The PL/SQL process has completed successfully.

(3) The output of an employee's name and salary grade, more than 5000 high, 3000-5000 Medium, 2000-3000 lower, 2000 low.

sql> Create or Replace function Empno_name_level1 (dnum in Emp.empno%type,name out Emp.ename%type)

2 return VARCHAR2

3 AS

4 Salary Emp.sal%type;

5 level VARCHAR2 (15);

6 begin

7 Select Sal,ename into Salary,name from EMP where dnum=empno;

8 case

9 when salary >5000 then level:= ' High ';

Ten when salary between and level:= ' Medium ';

When salary between and level:= ' lower ';

-Else level:= ' low ';

End case;

return level;

The end;

16/

The function has been created.

Sql> Declare

2 name Emp.ename%type;

3 level VARCHAR2 (15);

4 begin

5 Level:=empno_name_level1 (' 7499 ', name);

6 Dbms_output.put_line (' Name: ' | | name| | ' ' | | ' Level: ' | | level);

7 End;

8/

Name:allen Level: Low

The PL/SQL process has completed successfully.

Sql> Declare

2 name Emp.ename%type;

3 level VARCHAR2 (15);

4 begin

5 Level:=empno_name_level1 (' 7788 ', name);

6 Dbms_output.put_line (' Name: ' | | name| | ' ' | | ' Level: ' | | level);

7 End;

8/

Name:scott Level: Medium

The PL/SQL process has completed successfully.

Sql> Declare

2 name Emp.ename%type;

3 level VARCHAR2 (15);

4 begin

5 Level:=empno_name_level1 (' 7566 ', name);

6 Dbms_output.put_line (' Name: ' | | name| | ' ' | | ' Level: ' | | level);

7 End;

8/

Name:jones level: Lower

The PL/SQL process has completed successfully.

(4) Calculate the sum of the wages of the designated department and count the number of employees.

sql> Create or Replace function Salnum_empnonum (dnum in Emp.deptno%type,total out Emp.sal%type)

2 return number

3 AS

4 E_count number;

5 begin

6 select sum (SAL), COUNT (empno)

7 into Total,e_count from EMP where deptno=dnum;

8 return e_count;

9 End;

10/

The function has been created.

Sql> Declare

2 Total emp.sal%type;

3 begin

4 Dbms_output.put_line (' Enum: ' | | Salnum_empnonum (' Ten ', total) | | ' | | ' Total: ' | | Total);

5 end;

6/

enum:3total:8750

The PL/SQL process has completed successfully.

Experimental Summary:

1. The storage subroutine in PL/SQL includes both stored procedures and (stored) functions. A stored subroutine is stored in a database server as a standalone object, so it is a global structure that corresponds to a local subroutine, a local procedure and function nested within a PL/SQL block, whose storage location depends on the location of the parent block in which it resides.

2. The stored procedure can be easily understood as a subroutine that can execute an activity/action, can be stored in the database as a system object, and can be called repeatedly.

3. Typically, a stored procedure does not require a return value and can be implemented by a function call if it is necessary to return a value. However, if you want to return more than one value, you can do so by using the out or in out mode parameter.

4. Errors may occur during the compilation process, and you can use show errors to view the error message. There is currently only one object, to display the error can be used show ERRORS, when there are multiple objects, you can precisely indicate to see that object, such as write here: Show ERRORS PROCEDURE MyProc, the same to see the function, package, trigger errors, with show ERRORS Function/package/triger name.

5. The storage subroutine is a named PL/SQL block, which is stored in the database server in the form of compilation and can be called in the application, which is the embodiment of the PL/SQL program modularization.

oracle-Process Function Programming