PL/SQL also conforms to the current ANSI/ISO SQL standard.
In addition to static SQL discussed in this chapter, PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically.
It is important not to allow one operation to succeed while the other fails.
At the end of a transaction that makes database changes, Oracle makes all the changes permanent or undoes them all.
You give the cursor a name and associate it with a specific query.
You can optionally declare a return type for the cursor, such as DECLARE my_emp_id NUMBER(6); -- variable for employee_id my_job_id VARCHAR2(10); -- variable for job_id my_sal NUMBER(8,2); -- variable for salary CURSOR c1 IS SELECT employee_id, job_id, salary FROM employees WHERE salary The cursor is not a PL/SQL variable: you cannot assign values to a cursor or use it in an expression.
For additional information on assigning values to PL/SQL variables, see "Assigning a SQL Query Result to a PL/SQL Variable".
You can use CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; CREATE TABLE employees_temp2 AS SELECT employee_id, first_name, last_name FROM employees; DECLARE seq_value NUMBER; BEGIN -- Display initial value of NEXTVAL -- This is invalid: seq_value := employees_seq. CURRVAL, 'Morgan', 'Smith'); -- Because NEXTVAL values might be referenced by different users and -- applications, and some NEXTVAL values might not be stored in the -- database, there might be gaps in the sequence -- The following uses the stored value of the CURRVAL in seq_value to specify -- the record to delete because CURRVAL (or NEXTVAL) cannot used in a WHERE clause -- This is invalid: WHERE employee_id = employees_seq. CURRVAL INTO seq_value FROM dual; DELETE FROM employees_temp2 WHERE employee_id = seq_value; -- The following udpates the employee_id with NEXTVAL for the specified record UPDATE employees_temp SET employee_id = employees_seq.Each fetch retrieves the current row and advances the cursor to the next row in the result set.You can store each column in a separate variable, or store the entire row in a record that has the appropriate fields, usually declared using DECLARE v_jobid employees.job_id%TYPE; -- variable for job_id v_lastname employees.last_name%TYPE; -- variable for last_name CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK'); v_employees employees%ROWTYPE; -- record variable for row CURSOR c2 is SELECT * FROM employees WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]'); BEGIN OPEN c1; -- open the cursor before fetching LOOP FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.A transaction is a series of SQL data manipulation statements that does a logical unit of work.For example, two statements might credit one bank account and debit another.
A cursor can take parameters, which can appear in the associated query wherever constants can appear.