Source-pl/sql from Beginner to proficient-13th-subroutine-part 2

Debugging Nocopy function, in order to see the actual effect, set a relatively large number of cycles, the result is tragic:

Running for nearly 1 hours without results, the computer crashes (Task Manager can not open), helpless can only force shutdown, boot time is particularly long, once let me think the system crashed.

It seems that the program to debug this brute force calculation under PL/SQL developer is very risky, and I have never encountered this computer crash when debugging Java programs under Eclipse.

However, the content of this chapter is very practical, the crash is also worth (a bit distressed computer)

-code 13.11 uses%type to define form parameters create OR REPLACE PROCEDURE calcraisedsalarywithtype (p_job in Emp.job%type, p_salar                               Y in Out emp.sal%type--Define input and output parameters) as v_sal number (10,2);  --Save adjusted salary value begin if p_job= ' staff ' then--adjust the salary according to different job v_sal:=p_salary*1.12;  elsif p_job= ' sales staff ' then v_sal:=p_salary*1.18;  elsif p_job= ' manager ' then v_sal:=p_salary*1.19;  ELSE v_sal:=p_salary;  END IF;                                   P_salary:=v_sal;                 --Assigning the adjusted result to the input/output parameter end calcraisedsalarywithtype;--procedure call is constrained by the formal parameter, violation of the constraint will trigger an exception declare v_sal number (8,2);                 --Salary variable V_job VARCHAR2 (10);   --position variable begin v_sal:=1232945.45;   v_job:= ' staff ';                             Calcraisedsalarywithtype (v_job,v_sal); --Calculate the pay dbms_output.put_line (' adjusted salary after calculation: ' | |    V_sal); --Get the result after the pay exception when OTHERS then Dbms_output.put_line (sqlcode| | ' '||   SQLERRM);   END; --There are two ways to pass a parameter: pass by location (similar to a Java method signature, type one by one for a formal parameter) and pass by name(shown in the example below) DECLARE v_sal number (8,2);                 --Salary variable V_job VARCHAR2 (10);   --position variable begin v_sal:=123294.45;   v_job:= ' staff ';                             Calcraisedsalarywithtype (p_job=>v_job,p_salary=>v_sal); --Calculate the pay dbms_output.put_line (' adjusted salary after calculation: ' | |    V_sal); --Get the result after the pay exception when OTHERS then Dbms_output.put_line (sqlcode| | ' '||   SQLERRM);   END;                 --Passing parameters by name declare v_sal number (7,2);                 --Salary variable V_job VARCHAR2 (10);   --position variable begin v_sal:=124.45;   v_job:= ' staff ';                             Calcraisedsalarywithtype (P_salary=>v_sal,p_job=>v_job); --Calculate the pay dbms_output.put_line (' adjusted salary after calculation: ' | |    V_sal); --Get the result after the pay exception when OTHERS then Dbms_output.put_line (sqlcode| | ' '||   SQLERRM);   END;                 --mix use by name and pass declare v_sal number (7,2);                 --Salary variable V_job VARCHAR2 (10);   --position variable begin v_sal:=1224.45;   v_job:= ' staff ';                Calcraisedsalarywithtype (P_salary=>v_sal,v_job);             --Calculate the pay dbms_output.put_line (' adjusted salary after calculation: ' | |    V_sal); --Get the result after the pay exception when OTHERS then Dbms_output.put_line (sqlcode| | ' '||   SQLERRM);   END;   --code 13.12 Specifies the default value of the formal parameter create OR REPLACE PROCEDURE newdeptwithdefault (p_deptno dept.deptno%type default 57,--Department number P_dname dept.dname%type:= ' Management Department ',--department name P_loc Dept.loc%type DEFAULT ' Jiangsu '--location) as V_deptcount NUMB           ER;       --Save if there is an employee number begin SELECT COUNT (*) into V_deptcount from dept WHERE deptno = P_deptno; --Query If there is a department number in the Dept table if v_deptcount > 0--If the same employee record is present then--throws an exception raise   _application_error (-20002, ' the same department record appears ');   END IF; INSERT INTO Dept (Deptno, Dname, loc) VALUES (P_deptno, P_dname, P_loc);--insert record end;       BEGIN Newdeptwithdefault; --no parameters are specified, and the parameter default value end is used;       BEGIN Newdeptwithdefault (58, ' transaction group '); --no parameters are specified, and the parameter default value end is used;       BEGIN Newdeptwithdefault (58, ' transaction group '); end;--pass parameters by name, more flexibility begin NewdeptwithdefaulT (p_deptno=>59,p_loc=> ' Nanhai '); --Let dname use the default value end;               SELECT * from dept;--code 13.13 nocopy Use example declare TYPE Emptabtyp is TABLE of Emp%rowtype;             --Define nested table type Emp_tab Emptabtyp: = Emptabtyp (NULL);                                 --Define a blank nested table variable T1 number (5);   --A temporary variable that defines the save time T2 number (5);   T3 number (5);                  PROCEDURE Get_time (t out number)--Get current time is BEGIN SELECT to_char (sysdate, ' sssss ')      --Gets the number of seconds from midnight to the current into the t from DUAL; Dbms_output.           Put_Line (t);   END;   PROCEDURE do_nothing1 (tab in Out Emptabtyp)--defines a blank procedure with an in out parameter is BEGIN NULL;   END;   PROCEDURE do_nothing2 (tab in Out NOCOPY Emptabtyp)--use NOCOPY compile hint in parameters is BEGIN NULL; END;                                  BEGIN SELECT * into Emp_tab (1) from emp WHERE empno = 5093; --Query the employee in the EMP table and insert it into the Emp_tab 1th record Emp_tab.                            EXTEND (90000, 1); --copy of the 1th dollarN times get_time (t1);                                --Get the current time do_nothing1 (Emp_tab);                                        -implementation of the process without nocopy get_time (T2);                                --Get the current time do_nothing2 (Emp_tab);                                        --implementation of the process with Nocopy get_time (T3);   --Get the current time Dbms_output.put_line (' time spent in seconds ' call);   Dbms_output.put_line ('--------------------'); Dbms_output.put_line (' Call without nocopy: ' | |   To_char (T2-T1)); Dbms_output.put_line (' Call with Nocopy: ' | | To_char (T3-T2)); end;/--Code 13.14 defines a subroutine that can be called by an SQL statement (not knowing what it really means) CREATE OR REPLACE FUNCTION getempdept (p_empno emp.empno%type) RETURN               VARCHAR2--The parameter must be an Oracle database type as V_dname dept.dname%type;   BEGIN SELECT b.dname to v_dname from emp a,dept b WHERE A.deptno=b.deptno and a.empno=p_empno;                                RETURN V_dname;  --Query the data table, get the department name exception when No_data_found then RETURN NULL;                              --If the query does not have data, return to Nullend; SELECT empno Employee number, getempdept (empno) department name from emp;--code 13.15 using nested subforms sample Create OR REPLACE FUNCTION getraisedsalary_             Subprogram (p_empno emp.empno%type) RETURN numberis v_salaryratio Number (10, 2);            --the rate of salary increase v_sal emp.sal%type; --Salary variable--Define inline sub-function, return pay and pay ratio function getratio (p_sal out number) return number is N_job emp.job%ty            PE;          --Position variable n_salaryratio number (10, 2);       --Pay rate BEGIN--get payroll information in employee table SELECT job, Sal into N_job, p_sal from emp WHERE empno = p_empno;          Case N_job-to get the pay rate for different positions when ' clerk ' then n_salaryratio: = 1.09;          When ' salesperson ' then n_salaryratio: = 1.11;          When ' manager ' then n_salaryratio: = 1.18;       ELSE N_salaryratio: = 1.1;        END case;       RETURN N_salaryratio;        END; BEGIN V_salaryraTio:=getratio (v_sal); --Call the nested function, get the pay rate and the employee's salary if V_salaryratio <> 1--if there is a pay increase then RETURN ROUND (v_sal * v_salary         ratio,2);                         --Return the salary after the adjusted salary ELSE return v_sal; --otherwise do not return salary END if;                             EXCEPTION when No_data_found then RETURN 0; -If the original record is not found, return 0END; BEGIN-invokes the function to get the record dbms_output after the pay increase. Put_Line (' 7369 Employee pay record: ' | |   Getraisedsalary_subprogram (7369)); Dbms_output. Put_Line (' 7521 Employee pay record: ' | |   Getraisedsalary_subprogram (7521));   end;--code 13.17 uses the forward declaration for mutual invocation (this is in Buck Ah, hehe) DECLARE v_val binary_integer:=5;            PROCEDURE B (P_counter in Out Binary_integer); --forward declaration nested PROCEDURE a (p_counter in out Binary_integer) is--declares the nested shell a BEGIN dbms_output. Put_Line (' A (' | |      p_counter| | ');                                      IF p_counter>0 then B (p_counter);      --Call B p_counter:=p_counter-1 in the nested shell;   END IF;   END A;      PROCEDURE B (P_counter in. Binary_integer) is    --Declare nested dbms_output b BEGIN. Put_Line (' B (' | |      p_counter| | ');      P_counter:=p_counter-1;                                          A (P_counter); --Call a END B in a nested daemon;                                                 BEGIN B (V_val);    --Call nested bend;--code 13.18 nested-Shell Overload example declare PROCEDURE getsalary (p_empno in number) is--a process with one parameter BEGIN dbms_output.put_line (' Employee number: ' | |          P_EMPNO);    END; PROCEDURE getsalary (P_empname in VARCHAR2) is--the process of overloading BEGIN dbms_output.put_line (' Employee Name: ' | |    P_empname);    END; PROCEDURE getsalary (P_empno in Number,p_empname on VARCHAR) is--the process of birth BEGIN dbms_output.put_line (' Employee number: ' | | p_empno| | ' Employee Name: ' | |    P_empname);       END;                                                 BEGIN getsalary (7369);    --Call the overloaded party not to Getsalary (' Smith ');        Getsalary (7369, ' Smith ');   END; SELECT * from EMP; CREATE TABLE emp_history as SELECT * from EMP WHERE 1=2; SELECT * from emp_history;--code 13.19DECLARE PROCEDURE testautonomous (p_empno number) as PRAGMA autonomous_transaction (submit an autonomous transaction without affecting the master transaction)     --marked as an autonomous transaction BEGIN--now the process is an autonomous transaction, the master transaction is suspended INSERT into emp_history SELECT * from emp WHERE empno=p_empno;                                COMMIT; --Submit an autonomous transaction without affecting the main transaction END testautonomous;   Begin-The main transaction begins execution of INSERT into Emp_history (empno,ename,sal) VALUES (1011, ' test ', 1000);                    Testautonomous (7369);                                --The master transaction hangs and the autonomous transaction ROLLBACK is started; --Rollback MASTER Transaction End;select * from Emp_history where ename= ' test ';--code 13.20 implements recursive factorial in PL/SQL (this is interesting!)  ) DECLARE V_result INTEGER;                              FUNCTION FAC (n POSITIVE) return INTEGER is--the return result of factorial BEGIN IF N=1 Then               --If the n=1 terminates the condition dbms_output.put (' 1!=1*0! ');                               RETURN 1; ELSE dbms_output.put (n| | '! ='|| n| | '      *');                      RETURN N*FAC (n-1);   --otherwise the recursive call itself END if; END FAC; BEGIN v_result:= FAC (10);                          --Call the factorial function dbms_output.put_line (' Result: ' | | V_result); --Output factorial results end;--code 13.21 use recursion to find the Employee List example (this seems quite interesting, unfortunately the data is not enough, debugging did not see the effect) DECLARE PROCEDURE Find_staff (mgr_no number, tier number:                  = 1) is Boss_name VARCHAR2 (10);             --Define the boss's name cursor C1 (BOSS_NO number)--Define a cursor to query the list of employees under the current numbering in the EMP table is SELECT empno, ename    From emp WHERE mgr = boss_no;                      BEGIN SELECT ename to boss_name from emp WHERE empno = mgr_no;                                         --Get the manager's name if tier = 1--If tier specifies 1, the query starts at the top level and then inserts into the staff VALUES (Boss_name | |   ' is the Boss ');       -Because the 1th floor is the boss, the following is the manager END IF; For EE in C1 (MGR_NO)--Inserts employee information through a cursor for loop to the staff table loop insert into worker V Alues (Boss_name | | ' Management ' | | Ee.ename | |' At the level ' | |          To_char (tier));        Find_staff (ee.empno, tier + 1);       --In the cursor, recursively invokes the lower-level employee list END LOOP;    COMMIT; END Find_staff;                           BEGIN Find_staff (7566); --Query the list of employees under the management of 7839 and the hierarchy end; CREATE TABLE Staff (emplist VARCHAR2 (1000)); SELECT * from the staff;   SELECT * from EMP where empno;truncate table staff;--code 13.22 subroutine Dependency example Create OR REPLACE PROCEDURE testdependence asbegin   --inserting test data into the EMP table insert INTO EMP (empno,ename,sal) VALUES (1011, ' test ', 1000);                      Testsubprog (7369);                               --rollback; end;--is called by another procedure to insert data into the Emp_history table create OR REPLACE PROCEDURE Testsubprog (p_empno number) as BEGIN insert into Emp_his Tory SELECT * from EMP WHERE empno=p_empno; END Testsubprog; SELECT * from EMP;  Select COUNT (*) from emp_history;--view dependent SELECT name,type from user_dependencies WHERE referenced_name= ' emp '; EXEC Deptree_fill (' TABLE ', ' SCOTT ', ' EMP ');--View direct and indirect dependencies (need to run Utldtgree.sql script first) SELECT nested_level, NAME, TYPE from Deptree WHERE TYPE in (' PROCEDURE ', ' FUNCTION ');  --the process and function depend on the table changes, will be invalidated, need to recompile (original code is wrong, easy to mislead, now debug successfully) ALTER table Emp_history ADD emp_desc VARCHAR2 ($) NULL; ALTER TABLE emp_history DROP COLUMN emp_desc;alter procedure Testsubprog compile;alter procedure testdependence compile; SELECT object_name, object_type, status from User_objects WHERE object_name in (' Testdependence ', ' Testsubprog ');--                 Sub-program Rights Management (for follow-up learning) CREATE USER UserB identified by UserB;                       --Create user UserB, password also userbgrant resource,connect to UserB;                  --Assign role Grant EXECUTE on Find_staff to UserB for UserB; --Enables UserB to execute Find_staffdrop USER UserB; GRANT EXECUTE on Find_staff to UserB;

Source-pl/sql from Beginner to proficient-13th-subroutine-part 2