PL/SQL Cursor Exercises: Show the uses of static PL/SQL statement

PL/SQL Cursor: Exercise-1 with Solution

Write a program in PL/SQL to show the uses of static PL/SQL statement.

Sample Solution:

PL/SQL Code:

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
  SELECT employee_id, first_name, last_name 
  FROM employees;

  
DECLARE
  emp_id          emp_temp.employee_id%TYPE := 285;
  emp_f_name  emp_temp.first_name%TYPE  := 'Alen';
  emp_l_name   emp_temp.last_name%TYPE   := 'Gorge';
BEGIN
  INSERT INTO emp_temp (employee_id, first_name, last_name) 
  VALUES (emp_id, emp_f_name, emp_l_name);
 
  UPDATE emp_temp
  SET first_name = 'Alen'
  WHERE employee_id = emp_id;
 
  DELETE FROM emp_temp
  WHERE employee_id = emp_id
  RETURNING first_name, last_name
  INTO emp_f_name, emp_l_name;
 
  COMMIT;
  DBMS_OUTPUT.PUT_LINE (emp_f_name || ' ' || emp_l_name);
END;
/

Sample Output:

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: PL/SQL Cursor Exercises.
Next: Write a program in PL/SQL to show the uses of CURVAL and NEXTVAL with a sequence name.