Create stored procedures and functions "Weber's products must be a boutique"

I. What are stored procedures and functions

1. is a named PL/SQL block

2. Called PL/SQL sub-Program

3. Similar to anonymous blocks, there is a block structure:

The Declarations section is optional (no declare keyword)

Must have an Executive section

Optional Exception Handling section

Ii. differences between anonymous blocks and sub-programs

Third, stored procedures: syntax

CREATE [OR REPLACE] PROCEDURE procedure_name[(argument1 [Mode1] datatype1,argument2 [Mode2] datatype2,...)] is | Asprocedure_body;

 is v_deptment_id dept.deptno%type;v_deptment_name dept.dname%type;begin  v_deptment_id: =;  V_deptment_name:'ywb';  INSERT INTO Dept (deptno,dname) VALUES (v_deptment_id,v_deptment_name);  commit;  Dbms_output.put_line (' inserted:'| | sql%rowcount| | ' Line ' );  End;

To call a stored procedure using an anonymous block:

Begin  Add_dept;end;

Four, function:

CREATE [OR REPLACE] FUNCTION function_name [(argument1 [Mode1] datatype1,  argument2 [Mode2] datatype2,  . . .)] RETURN Datatypeis| Asfunction_body;

The difference between a function and a stored procedure: a function must return data, a stored procedure can return data, or it can return no data

Create or Replace function check_salreturnBoolean isdept_id Emp.deptno%type: =Ten; Emp_no Emp.empno%type: =7788; Salary Emp.sal%type; Avg_sal Emp.sal%Type;beginSelectSal into Salary fromEmpwhereempno=Emp_no;SelectAVG (SAL) into Avg_sal fromEmpwheredeptno=dept_id;ifSalary>Avg_sal Thenreturn true; Else     return false; Endif; Exception when No_data_found thenreturn NULL; End;

To invoke a function in an anonymous block:

 begin  if  (check_sal is  null  ) then Dbms_output.put_line (   "  output null   '  ); elsif (check_sal) then Dbms_output.put_line (    Wages above average wage   " );  else   Dbms_output.put_line ( Span style= "color: #800000;" > '  

To pass parameters to a function:

Create or Replace function Check_sal (empno number)returnBoolean isdept_id employees.department_id%type; Sal Employees.salary%type; Avg_sal employees.salary%Type;beginSelectsalary, department_id into Sal, dept_id fromEmployeeswhereemployee_id =empno; Selectavg (Salary) into Avg_sal fromEmployeeswheredepartment_id =dept_id; ifSal >Avg_sal Thenreturn true; Else    return false; Endif; exception when No_data_found thenreturn NULL; end;create or Replace function check_sal (empno number)returnNumber isdept_id employees.department_id%type; Sal Employees.salary%type; Avg_sal employees.salary%Type;beginSelectsalary, department_id into Sal, dept_id fromEmployeeswhereemployee_id =empno; Selectavg (Salary) into Avg_sal fromEmployeeswheredepartment_id =dept_id; ifSal >Avg_sal Thenreturn 1; elsif (Sal=avg_sal) Thenreturn 2; Else    return 3; Endif; exception when No_data_found thenreturn NULL; End;beginif(Check_sal ( $) is NULL) then Dbms_output.put_line ('output NULL due to program exception'); elsif (Check_sal ( $) =1) then Dbms_output.put_line ('wages above average wage'); elsif (Check_sal ( $) =2) then Dbms_output.put_line ('wages equal to average salary'); ElseDbms_output.put_line ('wages below average wage'); Endif; end;

Create stored procedures and functions "Weber's products must be a boutique"