Scribd
Upload
6 views4 pages

PL/SQL Cursors and Triggers Overview

Uploaded by

Suresh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
6 views4 pages

PL/SQL Cursors and Triggers Overview

Uploaded by

Suresh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

cursors:

DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/

DECLARE
c_id [Link]%type;
c_name [Link]%type;
c_addr [Link]%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/

DECLARE
c customers%rowtype;
CURSOR c_customers is
SELECT * FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c;
EXIT WHEN c_customers%notfound;
dbms_output.put_line([Link] || ' ' || [Link] || ' ' || [Link]);
END LOOP;
CLOSE c_customers;
END;
/

triggers:

create table customers(id number primary key, name varchar(20),age number,address


varchar(20), salary number));

insert into customers values(1,'ramesh',32,'Hyd',2000.00);


insert into customers values(2,'Khilan',25 ,'Delhi',1500.00);
insert into customers values(3, 'kaushik',23 ,'Kota',2000.00);
insert into customers values(4, 'Chaitali',25 ,'Mumbai',6500.00);
insert into customers values(5, 'Hardik',27,'Bhopal' , 8500.00);
insert into customers values(6, ' Komal',22,'MP' ,4500.00);

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |

CREATE OR REPLACE TRIGGER display_salary_changes123


BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN ([Link] > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :[Link] - :[Link];
dbms_output.put_line('dml is going on...');
dbms_output.put_line('Old salary: ' || :[Link]);
dbms_output.put_line('New salary: ' || :[Link]);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

PL/SQL:
=======

SET SERVEROUTPUT ON
DECLARE
Myname VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
Myname := 'John';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;
/
===========================================
DECLARE
myage number:=31;
BEGIN
IF myage < 11 THEN
DBMS_OUTPUT.PUT_LINE('I ama child ');
ELSE
DBMS_OUTPUT.PUT_LINE('I am nota child ');
END IF;
END;
/

================================================

DECLARE
grade CHAR(1) := UPPER('&grade');
appraisal VARCHAR2(20);
BEGIN
appraisal:= CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade ||'Appraisal' || appraisal);
END;
/

===================================================
declare
n_num number := 1;
begin
loop
dbms_output.put(n_num||', ');
n_num := n_num
+ 1;
exit when n_num
> 5;
end loop;
dbms_output.put_line('Final: '||n_num);
end;
/
=====================================================

DECLARE
salary number;
BEGIN
dbms_output.put_line('Enter the emp no');
SELECT sal into salary from emp where empno=&empno
;
WHILE salary<= 4000
LOOP
salary := salary
* 31;
dbms_output.put_line(salary);
END LOOP;
end;
/
=========================================================
create table employees(employee_id numbe primary key, salary number);

VARIABLE emp_salary NUMBER


SET AUTOPRINT ON
DECLARE
empno NUMBER(6):=&empno;
BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = empno;
END;
/

==========================
DECLARE
step PLS_INTEGER := 2;
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (i*step);
END LOOP;
END;
/

Common questions

Powered by AI

The PL/SQL block with 'grade' utilizes a CASE statement to output different appraisals based on the value of 'grade'. This branching logic allows the program to differentiate actions based on varying conditions, demonstrating a selection control structure. Each possible 'grade' results in a predefined appraisal message, showcasing conditional logic that manages various input scenarios effectively. This design minimizes errors related to unmatched conditions and ensures outputs are aligned with expected business rules or academic standards, though it requires maintenance if additional grades or rules are introduced .

In the PL/SQL block, the variable 'Myname' is initialized but not assigned a value initially, as evidenced by the output 'My name is: ' which prints a blank value. It is then assigned the value 'John' after the initial output, and prints 'My name is: John'. This demonstrates that variables in PL/SQL must be explicitly assigned values to reflect in program execution. The execution is sequential, respecting the order of operations. This highlights the importance of understanding variable scope and lifecycles where uninitialized variables can result in unexpected outputs or logic errors .

The 'SET SERVEROUTPUT ON' command in PL/SQL enables the display of DBMS_OUTPUT information for debugging and monitoring purposes. It allows printed statements within PL/SQL blocks to be visible to developers, offering insights into variable states and execution flow, which is invaluable for identifying logic errors and verifying business logic implementation. By observing output, developers can monitor how data and program state changes over time, facilitating effective troubleshooting and optimization throughout script execution .

The WHEN clause in the 'display_salary_changes123' trigger specifies that the trigger is only executed for rows where the new ID is greater than zero. This filter ensures that the trigger's operations, particularly salary difference calculations and related outputs, are only applied to valid customer entries, preventing unnecessary processing and potential errors on non-target rows. The clause effectively limits the trigger’s execution, optimizing performance and ensuring meaningful operations .

The PL/SQL block which multiplies an employee's salary within a loop checks if the salary is less than or equal to 4000, multiplying it by 31 iteratively until the condition is no longer met. This approach ensures that salary adjustments are applied incrementally and based on specific business rules, optimizing resource utilization and data processing through conditional iteration. As a benefit, transactional data updates are controlled and applied only as needed, reducing the risks associated with batch updates, including potential data integrity issues and overhead from unnecessary operations .

The PL/SQL block using the 'step' variable and a FOR loop iterates three times. During each iteration, it multiplies the loop counter 'i' by the fixed integer 'step', printing the result. This structure is suited for iterative mathematical operations because it establishes a defined loop range and executes consistently for each iteration, ensuring predictable and correct multiplication. The loop ensures proper iteration by designating a clear start and end point, incrementing the counter automatically, and avoids off-by-one errors or infinite loops associated with manual iteration control .

In the PL/SQL block with 'n_num', an initial value of 1 is assigned to the variable, and a loop runs until 'n_num' exceeds 5. During each iteration, 'n_num' is displayed and incremented. This approach facilitates precise control over iteration by ensuring that each step is handled predictably. The block demonstrates the use of loop control structures in PL/SQL to produce sequentially ordered outputs and manage variable states efficiently, stopping as soon as the condition 'n_num > 5' is met .

The PL/SQL cursor 'c_customers' is explicitly declared to retrieve all records from the 'customers' table. It is opened, and a loop is used to fetch each record into PL/SQL variables until all rows have been processed, indicated by 'c_customers%notfound'. The loop prints out the details of each customer row-by-row. Using a cursor in this context allows for efficient processing and manipulation of query results within PL/SQL blocks, facilitating sequential processing, which is useful when managing large datasets or performing complex logic on each row .

The database trigger 'display_salary_changes123' is executed before any DELETE, INSERT, or UPDATE operations on the 'customers' table for each row where the new ID is greater than zero. It calculates the difference between the new and old salary values and outputs a message indicating the ongoing DML operation, the old salary, the new salary, and the salary difference. This helps in monitoring and auditing salary changes to ensure data integrity and track modifications .

The cursor in the PL/SQL block for the 'customers' table allows for record-by-record processing within a procedural logic context, providing more granular control over data manipulation than traditional SQL queries. By opening the cursor and fetching rows sequentially, developers can implement conditional logic on each record, handle exceptions, and manage transactions efficiently within the PL/SQL block. This capability is particularly advantageous for complex data operations requiring intermediate processing or decision-making logic, which is cumbersome or impossible with only SQL queries .

You might also like