SOURCE-oracle Database Management-13th chapter-subroutine and Package-part 1 (define subroutine)

One of the surprises in this section is harvesting: the debugging of subroutines. Have not played before, experienced a.

--13th Zhang Zi Program and package--13.1 Define subroutine--13.1.1 what is subroutine--code 13.1 Create process Add new employee Create OR REPLACE PROCEDURE addnewemp (p_empno emp.empno%type
                                      ,--Employee number P_ename Emp.ename%type, employee name            P_job Emp.job%type, employee position p_sal Emp.sal%type, --Employee salary P_deptno emp.deptno%type:=20)--employee department as BEGIN--determine if incoming p_empno parameters are large
  In 0, if less than 0 throws an exception if p_empno<0 THEN raise_application_error (-20001, ' employee number must be greater than 0 ');
  End IF; --Invoke INSERT statement Insert new employee into EMP table (empno, ename, Job, Sal, Deptno) VALUES (P_empno, P_ename, P_job, p_s
AL, P_deptno);




End Addnewemp;
--Call subroutine code BEGIN addnewemp (8230, ' Li Shiyo ', ' analyst ', 3000,20);


End; --Debugging of subroutines--slightly--13.1.3 creation process--code 13.2 creation Process Add new department create OR REPLACE PROCEDURE newdept (p_deptno number, Department number P_dname VARCHAR2,--department name P_loc VARCHAR2         --position) as--variable definition part Error_deptno EXCEPTION;               --Defines a custom exception v_deptcnt INTEGER;
  --Define a counter integer BEGIN if p_deptno<=0 THEN--to determine whether the incoming parameter assigns the correct value RAISE Error_deptno;
  End IF;
  --Query whether the incoming department number exists in the Dept table with SELECT COUNT (DEPTNO) into v_deptcnt from dept WHERE Deptno=p_deptno; -If an if v_deptcnt>0 THEN is already present-call UPDATE statement Updates Dept Table Update Dept SET dname=p_dname,loc=p_loc WHERE Deptno=p_deptno
  ;
  ELSE--Invoke INSERT statement to insert a new record into the Dept table INSERT INTO Dept VALUES (P_DEPTNO,P_DNAME,P_LOC); 
End IF; EXCEPTION--Exception handling part when Error_deptno THEN--Throws a custom exception raise_application_error (-
20001, ' Please enter the correct department number ');  



End;
   BEGIN newdept (70, ' computer department ', ' Shenzhen ');  
Newdept (80, ' Operation Department ', ' Tokyo ');


End; --Create a function--code 13.3 Create a function return a number of departments created OR REPLACE function Get_dept_count (P_deptno in) returns # is V_DEPT_CNT I Nteger;
--Define function local variable No_deptno EXCEPTION; BEGIN-Query Incoming parameter p_deptno is a valid DEptno SELECT COUNT (*) into the v_dept_cnt from dept WHERE Deptno=p_deptno;         --If the DEPTNO does not exist, throw an exception if v_dept_cnt = 0 THEN RAISE no_deptno; --Throw custom exception else--otherwise query the number of employees in the department SELECT COUNT (*) into v_dept_cnt from emp WHERE deptno = P_DEP
    Tno       return v_dept_cnt;
--Returns the number of employees by using the return statement IF; EXCEPTION when No_deptno THEN--Captures a custom exception dbms_output.put_line (' nonexistent department number.
    ');              RETURN-1;


--Output the exception message and return the value end;
DECLARE v_cnt INTEGER;
  BEGIN V_cnt:=get_dept_count (20); Dbms_output.put_line (' 30 of the employees in the department are: ' | |
  Get_dept_count (30)); Dbms_output.put_line (' Department-20 of the number of employees is: ' | |
Get_dept_count (-20));
 End;