Oracle Database

Of course, you can run SQL functions in SQL statements. But many SQL functions can also be executed in regular PL/SQL statements, as shown in the following sample script:

DECLARE

now DATE;

id NUMBER;

str VARCHAR2(40);

BEGIN

now := SYSDATE;

DBMS_OUTPUT.PUT_LINE('Time #1 = ' ||

TO_CHAR(now,'HH24:MI:SS'));

SELECT SYSDATE INTO now FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Time #2 = ' ||

TO_CHAR(now,'HH24:MI:SS'));

id := UID;

DBMS_OUTPUT.PUT_LINE('User id #2 = ' || TO_CHAR(id));

SELECT UID INTO id FROM DUAL;

DBMS_OUTPUT.PUT_LINE('User id #2 = ' || TO_CHAR(id));

str := CHR(70)||CHR(89)||CHR(73);

DBMS_OUTPUT.PUT_LINE('String #1 = ' || str);

SELECT CHR(70)||CHR(89)||CHR(73) INTO str FROM DUAL;

DBMS_OUTPUT.PUT_LINE('String #2 = ' || str);

END;

/

Time #1 = 21:41:16

Time #2 = 21:41:16

User id #2 = 33

User id #2 = 33

String #1 = ggl

String #2 = ggl

The implicit cursor is the cursor automatically defined by PL/SQL for you. Whenever a SQL statement is executed, this cursor will be assigned to represent the execution of this statement. This implicit cursor is called SQL. It has many attributes representing some good information about the execution like:

► SQL%FOUND - True, if the SQL statement has changed any rows.

► SQL%NOTFOUND - True, if the SQL statement has not changed any rows.

► SQL%ROWCOUNT - The number of rows affected by the SQL statement.

See the example below on how to use the implicit cursor:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL);

Table created.

If a DELETE statement is deleting a single row, you can assign column values of the deleted row to variables by using the RETURNING clause, which an extension of DELETE statements for PL/SQL. The tutorial script shows you how to do this:

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE

fname VARCHAR2(10);

lname VARCHAR2(10);

BEGIN

DELETE FROM emp_temp WHERE employee_id = 100;

RETURNING first_name, last_name INTO fname, lname;

DBMS_OUTPUT.PUT_LINE('Name deleted = ' || fname || ' '

|| lname);

-- This will not work because multiple rows deleted

-- DELETE FROM emp_temp WHERE employee_id > 100;

-- RETURNING first_name, last_name INTO fname, lname;

END;

/

Name deleted = Steven King

Similar to SELECT ... INTO, RETURNING ... INTO will not work if multiple rows are deleted.

If a variable is a RECORD variable with data fields assigned values, you can retrieve those values out of its data fields by using fields names prefixed with variable name as "variable.field_name". Here is a sample script showing you how to retrieve field values from RECORD variables:

CREATE OR REPLACE PROCEDURE HELLO AS

TYPE student IS RECORD (

id NUMBER(5),

first_name VARCHAR(80),

last_name VARCHAR(80)

);

best_student student;

another_student student;

class_name VARCHAR2(80);

BEGIN

class_name := 'ggl Center 2006';

best_student.first_name := 'The';

best_student.last_name := 'Best';

DBMS_OUTPUT.PUT_LINE('Best student = ' ||

best_student.first_name || ' '

|| best_student.last_name);

END;

/

Best student = The Best

When defining a specific RECORD type, you can define a data field as NOT NULL to make sure variables with this RECORD type to always have values in this field. A field defined as NOT NULL must have a default value. Here is a tutorial script showing you how to define a data field as NOT NULL:

CREATE OR REPLACE PROCEDURE HELLO AS

TYPE student IS RECORD (

id NUMBER(5) NOT NULL DEFAULT 0,

first_name VARCHAR(80),

last_name VARCHAR(80)

);

best_student student;

another_student student;

class_name VARCHAR2(80);

BEGIN

class_name := 'ggl Center 2006';

best_student.first_name := 'The';

best_student.last_name := 'Best';

DBMS_OUTPUT.PUT_LINE('Best student ID = '

|| best_student.id);

DBMS_OUTPUT.PUT_LINE('Best student = ' ||

best_student.first_name || ' '

|| best_student.last_name);

END;

/

Best student ID = 0

Best student = The Best

If you have a table, and want to define a RECORD variable to store all the data elements of a row from that table, you can use table_name%ROWTYPE to define the RECORD variable as shown in the following sample script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL);

Table created.

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

best_student student%ROWTYPE;

another_student student%ROWTYPE;

class_name VARCHAR2(80);

BEGIN

class_name := 'ggl Center 2006';

best_student.first_name := 'The';

best_student.last_name := 'Best';

DBMS_OUTPUT.PUT_LINE('Best student ID = '

|| best_student.id);

DBMS_OUTPUT.PUT_LINE('Best student = ' ||

best_student.first_name || ' '

|| best_student.last_name);

END;

/

Best student ID =

Best student = The Best

If you have a RECORD variable with data fields matching a table structure, you can update a row in this table with this RECORD variable using the UPDATE ... SET ROW statement as shown in the sample script below:

CREATE TABLE emp_temp AS SELECT * FROM employees;

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

manager employees%ROWTYPE;

BEGIN

SELECT * INTO manager FROM employees

WHERE employee_id = 100;

manager.employee_id := 299;

INSERT INTO emp_temp VALUES manager;

manager.first_name := 'ggl';

manager.last_name := 'Center';

UPDATE emp_temp SET ROW = manager WHERE employee_id = 299;

DBMS_OUTPUT.PUT_LINE('# rows updated = ' || SQL%ROWCOUNT);

END;

/

# rows updated = 1

Right after executing a DML statement, you retrieve any attribute of the implicit cursor by using SQL%attribute_name, as shown in the following tutorial exercise:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL);

Table created.

DECLARE

id NUMBER;

first_name CHAR(10);

BEGIN

id := 29;

first_name := 'Bob';

INSERT INTO student VALUES(id, first_name, 'Henry');

first_name := 'Joe';

INSERT INTO student VALUES(id+1, first_name, 'Bush');

DBMS_OUTPUT.PUT_LINE('# of rows inserted: '

|| SQL%ROWCOUNT);

first_name := 'ggl';

UPDATE student SET first_name = first_name WHERE id = 29;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('# of rows updated: '

|| SQL%ROWCOUNT);

END IF;

UPDATE student SET first_name = first_name

WHERE id = id+1;

IF SQL%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE('No records updated.');

END IF;

DELETE FROM student WHERE id = id;

DBMS_OUTPUT.PUT_LINE('# of rows deleted: '

|| SQL%ROWCOUNT);

END;

/

# of rows inserted: 1

# of rows updated: 1

No records updated.

# of r

You use the FOR ... IN ... LOOP statement to loop through data rows in the implicit cursor as the following syntax:

FOR row IN dml_statement LOOP

(statement block with row.field)

END LOOP;

Here "row" is a local RECORD type variable with fields automatically defined to match the fields in the data rows resulted from the DML statement. Here is a good tutorial exercise on loop through data rows with the implicit cursor:

BEGIN

FOR row IN (SELECT * FROM employees

WHERE manager_id = 101) LOOP

DBMS_OUTPUT.PUT_LINE('Name = ' || row.last_name);

END LOOP;

END;

/

Name = Greenberg

Name = Whalen

Name = Mavris

Name = Baer

Name = Higgins