{"id":120,"date":"2025-03-06T12:59:20","date_gmt":"2025-03-06T12:59:20","guid":{"rendered":"https:\/\/alpeshconnect.in\/blog\/?p=120"},"modified":"2025-03-06T12:59:20","modified_gmt":"2025-03-06T12:59:20","slug":"pl-sql-block-example","status":"publish","type":"post","link":"https:\/\/alpeshconnect.in\/blog\/2025\/03\/06\/pl-sql-block-example\/","title":{"rendered":"Pl\/Sql Block Example"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\">(a) PL\/SQL Code to Calculate Gross and Net Salary After Incrementing Basic Salary by 11%<\/h3>\n\n\n\n<p>To update the employee&#8217;s salary and calculate their gross and net salary, we will first need to increment the basic salary by 11% and then calculate the gross and net salary. The <code>gross<\/code> and <code>net<\/code> salary can be calculated using the following assumptions:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Gross Salary<\/strong> = Basic Salary + DA (Dearness Allowance) + HRA (House Rent Allowance)<\/li>\n\n\n\n<li><strong>Net Salary<\/strong> = Gross Salary &#8211; Tax<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n    v_empno employee.empno%TYPE;\n    v_basic employee.basic%TYPE;\n    v_da employee.da%TYPE;\n    v_hra employee.hra%TYPE;\n    v_tax employee.tax%TYPE;\n    v_gross employee.gross%TYPE;\n    v_net employee.net%TYPE;\nBEGIN\n    -- Get employee number from user input (for example)\n    v_empno := &amp;empno;  -- this will prompt for empno value\n\n    -- Retrieve employee details based on empno\n    SELECT basic, da, hra, tax\n    INTO v_basic, v_da, v_hra, v_tax\n    FROM employee\n    WHERE empno = v_empno;\n\n    -- Increment the basic salary by 11%\n    v_basic := v_basic * 1.11;\n\n    -- Calculate the gross salary\n    v_gross := v_basic + v_da + v_hra;\n\n    -- Calculate the net salary\n    v_net := v_gross - v_tax;\n\n    -- Update the employee record with the new basic, gross, and net salary\n    UPDATE employee\n    SET basic = v_basic,\n        gross = v_gross,\n        net = v_net\n    WHERE empno = v_empno;\n\n    -- Commit the changes\n    COMMIT;\n\n    -- Output the updated information\n    DBMS_OUTPUT.PUT_LINE('Employee ' || v_empno || ' salary updated:');\n    DBMS_OUTPUT.PUT_LINE('New Basic: ' || v_basic);\n    DBMS_OUTPUT.PUT_LINE('New Gross: ' || v_gross);\n    DBMS_OUTPUT.PUT_LINE('New Net: ' || v_net);\nEND;<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">(b) PL\/SQL Code to Display the First Five Senior Employees<\/h3>\n\n\n\n<p>Here, the &#8220;senior employees&#8221; likely refers to the employees with the longest service. Typically, seniority is based on the <code>dob<\/code> (date of birth), where older employees are considered senior. The PL\/SQL block can use a cursor to fetch the first five senior employees based on their <code>dob<\/code>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n    CURSOR senior_employees_cursor IS\n        SELECT empno, ename, dob\n        FROM employee\n        ORDER BY dob ASC;  -- Assuming that older employees are more senior\n\n    v_empno employee.empno%TYPE;\n    v_ename employee.ename%TYPE;\n    v_dob employee.dob%TYPE;\n    v_counter INT := 0;\nBEGIN\n    -- Open the cursor to fetch senior employees\n    OPEN senior_employees_cursor;\n\n    -- Fetch and display the first 5 senior employees\n    LOOP\n        FETCH senior_employees_cursor INTO v_empno, v_ename, v_dob;\n        EXIT WHEN senior_employees_cursor%NOTFOUND OR v_counter = 5;\n\n        v_counter := v_counter + 1;\n\n        -- Output employee details\n        DBMS_OUTPUT.PUT_LINE('Employee ' || v_counter || ': ' || v_ename || ', Emp No: ' || v_empno || ', DOB: ' || v_dob);\n    END LOOP;\n\n    -- Close the cursor\n    CLOSE senior_employees_cursor;\nEND;\n<\/code><\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>(a) PL\/SQL Code to Calculate Gross and Net Salary After Incrementing Basic Salary by 11% To update the employee&#8217;s salary and calculate their gross and net salary, we will first need to increment the basic salary by 11% and then calculate the gross and net salary. The gross and net salary can be calculated using [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":101,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,42],"tags":[25,26,43],"class_list":["post-120","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bca","category-concept-of-rdbms","tag-bca","tag-bca-paper-solution","tag-concept-of-rdbms"],"_links":{"self":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/comments?post=120"}],"version-history":[{"count":1,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/120\/revisions"}],"predecessor-version":[{"id":121,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/120\/revisions\/121"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/media\/101"}],"wp:attachment":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/media?parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/categories?post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/tags?post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}