Home

Description

The Pl_Sql code rule that "Files should not have too many lines of code" is an important rule to follow when writing code. This rule helps to ensure that code is easy to read and understand, as well as maintain and debug. Too many lines of code can make it difficult to find and fix errors, as well as make the code more difficult to read. This rule encourages developers to break up their code into smaller, more manageable chunks, which can make it easier to read and debug.

Key Benefits

• Easier to read: Files with fewer lines of code are easier to read and understand.
• Easier to maintain: Files with fewer lines of code are easier to maintain and debug.
• Better performance: Files with fewer lines of code can lead to better performance.
• Reduced complexity: Files with fewer lines of code can reduce complexity and make the code more manageable.

Non-compliant Code Example

create or replace PACKAGE Employees --Non compliant code (File is having line of code more then default defined limit 100)
IS  
    procedure InsertEmployee(fname in nvarchar2, lname in nvarchar2);
	procedure UpdateEmployeeName(employeeId in integer, fname in nvarchar2, lname in nvarchar2);
	procedure DeleteEmployeeById( id in NUMBER ); 
    procedure GetAllEmployees; 
	procedure GetManagerOfEmployeeById( id in NUMBER );
	procedure OrdersByEmployee2( employee_Id in NUMBER, p_recordset OUT SYS_REFCURSOR );
	function CheckEmployee(employee_Id in NUMBER) return BOOLEAN;
	function GetFullName(fname in nvarchar2, lname in nvarchar2) return nvarchar2; 
  function GetManagerByEmployee( id in NUMBER) return int;
  procedure LogDeletingEmployee( id in NUMBER);
end Employees;
/

create or replace PACKAGE BODY Employees
IS

	procedure InsertEmployee(fname in nvarchar2, lname in nvarchar2)
    is
    begin
	
		INSERT INTO EMPLOYEE (EMP_ID,emp_fname, emp_lname) VALUES (EMPLOYEE_seq.nextval,fname,lname);
            

    end InsertEmployee;

	procedure UpdateEmployeeName(employeeId in integer, fname in nvarchar2, lname in nvarchar2)
    is
    begin
        UPDATE EMPLOYEE
			SET emp_fname=fname, emp_lname=lname
			WHERE emp_Id = employeeId;

    end UpdateEmployeeName;

    procedure GetAllEmployees
    Is 
	--Get All Employees
	 cursor c1 is
			SELECT emp_id, emp_lname, emp_fname, emp_street, city,state,zip_code,phone
			FROM ALL_EMPLOYEES;
			 
    Begin
		


		for r1 in c1 loop
        dbms_output.put_line(
            lpad(r1.emp_id,12) ||
            lpad(r1.emp_id,12) ||
            lpad(r1.emp_id,12)  
        );
		end loop;



    end GetAllEmployees;

	procedure DeleteEmployeeById(id in NUMBER )
    Is 

    Begin
		DELETE EMPLOYEE where emp_id = id;
		COMMIT;

    end DeleteEmployeeById;

	procedure GetManagerOfEmployeeById(id in NUMBER )
    Is 
	cursor c1 is
			SELECT manager_fname
			FROM ALL_EMPLOYEES_DETAILS where emp_id = id;

	
    Begin
		for r1 in c1 loop
        dbms_output.put_line(
            lpad(r1.manager_fname,12)
        );
		end loop;

    end GetManagerOfEmployeeById;

	procedure OrdersByEmployee2(employee_Id in NUMBER,p_recordset  OUT SYS_REFCURSOR )
    Is 
    Begin

		OPEN p_recordset FOR
			   SELECT sales_order.id Oid,   
							 sales_order.order_date Odate,   
							 DECODE(sales_order.fin_code_id,
							'r1','Revenue','e1','Expense'),   
							 sales_order.region Oregion,   
							 customer.fname Cfname,
							 customer.lname Clname,
							 sum (sales_order_items.quantity*unit_price) Oamount 
						FROM customer,   
							 employee,   
							 sales_order_items, 
							 sales_order,
							 ALL_PRODUCTS
						 WHERE ( employee.emp_id = employee_Id ) and  
							 ( employee.emp_id = sales_order.sales_rep ) and
							 ( customer.id = sales_order.cust_id ) and
							 ( sales_order_items.id = sales_order.id)   
						 GROUP BY sales_order.id,sales_order.order_date,   
							 sales_order.fin_code_id,   
							 sales_order.region, 
               customer.fname,
							 customer.lname;


    end OrdersByEmployee2;
    
     procedure LogDeletingEmployee( id in NUMBER)
    is
		title nvarchar2(50);
		msg nvarchar2(50);
    begin
			
			title := concat('Delete employee id: ',id);
			msg := concat('Delete employee id: ',id);
            INSERT INTO AppLog (Log_date, Action, Title,Message,Employee_Id) VALUES (SYSDATE,APPLOGPK.GetActionId('DEL EMPLOYEE'), title,msg , id);

    end LogDeletingEmployee;
    
    
    function GetManagerByEmployee( id in NUMBER) return int
    is

    begin
			
			return -1;

    end GetManagerByEmployee;


	function CheckEmployee(employee_Id in NUMBER) return BOOLEAN
	is	isChecked BOOLEAN;
	begin
		-- Check the employee
		RETURN(isChecked); 
	end CheckEmployee;


	function GetFullName(fname in nvarchar2,lname in nvarchar2) return nvarchar2
	is
	begin
		-- Check the employee
		RETURN(CONCAT(CONCAT(fname,', '),lname)); 
	end GetFullName;


end Employees;