Source-pl/sql from Beginner to proficient-chapter II-pl/sql Basic Concepts-part 1

The source code accompanying the book does not have the serial number, part has the bug, the debugging process corrects simultaneously.

--code 2.1 calculates the employee salary declare v_sal1 number using the book Order structure;  V_sal2 number; V_sumsal number;  BEGIN SELECT sal into V_sal1 from emp WHERE empno = &empno1;  SELECT sal to V_sal2 from emp WHERE empno = &empno2;  V_sumsal: = V_sal1 + v_sal2; Dbms_output.put_line (' ID: ' | | &empno1 | | ',' || &empno2 | |                       ' The sum of Wages: ' | | V_sumsal); End;select * from emp ORDER by Sal desc;--Code 2.2 Use branch structure for employee pay rise declare--define salary increase ratio constants C_manager CONSTANT number:=0.15; C_salesman CONSTANT number:=0.12; C_clerk CONSTANT number:=0.10; C_sr_cont CONSTANT number:=0.88; --Define Job variables V_job VARCHAR (100); BEGIN--Query the employee information for the specified employee's code SELECT job into V_job from Scott.emp WHERE empno=&empno1; --Perform branch judgment IF v_job= ' clerk ' then UPDATE scott.emp SET sal=sal* (1+c_clerk) WHERE empno=&empno1;    elsif v_job= ' salesman ' then UPDATE scott.emp SET sal=sal* (1+c_salesman) WHERE empno=&empno1; elsif v_job= ' MANAGER ' then UPDATE scott.emp SET sal=sal* (1+c_manager) WHERE empno=&empno1; elsif v_job= ' SR.CONT ' then UPDATE scott.emp SET sal=sal* (1+c_sr_cont) WHERE empno=&empno1; END IF; --Displays completion information dbms_output. Put_Line (' already for employees ' | | &empno1| | '   A successful raise! '); EXCEPTION-handling of PL/SQL pre-defined exceptions when No_data_found and then Dbms_output. Put_Line (' No Employee data found '); end;--Code 2.3 uses a looping structure to raise the salary for all employees (without using custom functions) DECLARE--Define the salary increase ratio constant C_manager CONSTANT number:=0.15; C_salesman CONSTANT number:=0.12; C_clerk CONSTANT number:=0.10; C_cont CONSTANT number:=1.00;                         V_job VARCHAR (100);                        --Define position variable V_empno VARCHAR (20);                        --Define Employee number variable V_ename VARCHAR (60); --Define Employee name variable CURSOR c_emp is SELECT job,empno,ename from scott.emp for UPDATE;   BEGIN OPEN c_emp;   --Open cursor loop--loop cursor FETCH c_emp into v_job,v_empno,v_ename;                 --Extracting cursor data EXIT when c_emp%notfound; --If no data can be extracted from the cursor if v_job= ' clerk ' then--if the employee, pay raise 10% UPDATE scott.emp SET sal=sal* (1+c_clerk) WHERE Current of C_emp;  elsif v_job= ' salesman ' then--if the sales clerk, raise 12% UPDATE scott.emp SET sal=sal* (1+c_salesman) WHERE Current of c_emp; elsif v_job= ' manager ' then-if for manager, pay rise 15% UPDATE scott.emp SET sal=sal* (1+c_manager) WHERE Current of C_   EMP; elsif v_job= ' SR.   CONT ' then-double your salary if you are a senior consultant! UPDATE scott.emp SET sal=sal* (1+c_cont) WHERE Current of c_emp; END IF; --Displays completion information dbms_output. Put_Line (' already for employees ' | | v_empno| | ': ' | | v_ename| | '  A successful raise! '); END LOOP;                CLOSE c_emp; --Close cursor EXCEPTION when No_data_found then-handles PL/SQL pre-defined exception dbms_output. Put_Line (' No Employee data found '); END; --Execute Dynamic SQL statement drop table Books;declare v_sqlstr VARCHAR ($): = ' CREATE table books (ID int not null,bookname varchar2 (+) NU LL) ';  BEGIN EXECUTE IMMEDIATE v_sqlstr; --Execute the DDL statement end;--attempt to execute a static SQL statement (compilation error will occur) BEGIN CREATE TABLE BOOKS (ID int not null,bookname varchar2 () NULL;  end;--code 2.4 Creating an Employee object Create OR REPLACE TYPE emp_obj as OBJECT (empno number (4),--Employee ID attribute ename VARCHAR2 (10),--Employee name attribute  Job VARCHAR (9),--Employee Job Title attribute sal Number (7,2),--Employee pay attributeDeptno Number (2),--Sector numbering attribute--Raise method MEMBER PROCEDURE addsalary (radio numbers));--Define object type body, implement object method create OR REPLACE type BO  DY Emp_obj As--Implement object method MEMBER PROCEDURE addsalary (radio number) is BEGIN sal:=sal* (1+radio); --plus a specific percentage of salary END; END;--code 2.5 CREATE function encapsulates business logic create OR REPLACE function Getaddsalaryratio (p_job VARCHAR2) RETURN number as V_result number (7,2); BEGIN if p_job= ' clerk ' then--if for staff, pay 10% v_result:=0.10;     elsif p_job= ' salesman ' then--if the sales staff, pay 12% v_result:=0.12; elsif p_job= ' manager ' then--if the manager, the salary increase 15% v_result:=0.15;   elsif p_job= ' cont ' Then--if you're a consultant, double your salary! V_result:=1; END IF;   RETURN V_result;                         end;--Code 2.6 Calls the function to simplify the program logic declare v_job VARCHAR (100);                        --Define position variable V_empno VARCHAR (20);                        --Define Employee number variable V_ename VARCHAR (60); --Define Employee Name variable v_ratio number (7,2); CURSOR C_emp is a SELECT job,empno,ename from Scott.emp3 for UPDATE;  BEGIN OPEN c_emp; --Open cursor loop--loop cursor FETCH c_emp into v_job,v_empno,v_ename;                 --Extracting cursor data EXIT when c_emp%notfound;        --If no data can be extracted out of the cursor v_ratio:=getaddsalaryratio (v_job);         --Call the function to get the raise rate UPDATE Scott.emp3 SET sal2=sal2* (1+v_ratio) WHERE Current of c_emp; --Displays completion information dbms_output. Put_Line (' already for employees ' | | v_empno| | ': ' | | v_ename| | '  A successful raise! '); END LOOP;                                CLOSE c_emp; --close cursor commit; EXCEPTION when OTHERS then-handles PL/SQL pre-defined exception dbms_output.   Put_Line (' No Employee data found '); END; --code 2.7 The simplest PL/SQL block begin Dbms_output. Put_Line (' Oracle and MySQL differ quite a lot, PL/SQL is an enhancement to the. ');  end;--code 2.8 Full PL/SQL statement block declare v_deptcount number (2); V_deptno Number (2): = 60;  BEGIN SELECT COUNT (1) into V_deptcount from dept WHERE deptno = V_deptno;    IF v_deptcount = 0 Then INSERT into dept VALUES (V_deptno, ' finance department ', ' Shenzhen ');  Dbms_output.put_line (' successful insertion of departmental information '); elsif v_deptcount = 1 Then dbms_output.put_line (' the department already exists!  '); END IF; EXCEPTION when otherS then Dbms_output.put_line (' Department data insertion failed! '); End;commit;select * FROM Dept;

Source-pl/sql from Beginner to proficient-chapter II-pl/sql Basic Concepts-part 1