Oracle DBA FAQ - Working with Database Objects in PL/SQL

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

A collection of 23 FAQs on working with database objects in PL/SQL. Clear answers are provided with tutorial exercises on running DML statements, assign table data to variables, using the implicit cursor, defining and using RECORDs with table rows. Topics included in this FAQ are:

Can DML Statements Be Used in PL/SQL?
Can DDL Statements Be Used in PL/SQL?
Can Variables Be Used in SQL Statements?
What Happens If Variable Names Collide with Table/Column Names?
How To Resolve Name Conflicts between Variables and Columns?
How To Assign Query Results to Variables?
Can You Assign Multiple Query Result Rows To a Variable?
How To Run SQL Functions in PL/SQL?
How To Retrieve the Count of Updated Rows?
What Is the Implicit Cursor?
How To Assign Data of the Deleted Row to Variables?
What Is a RECORD in PL/SQL?
How To Define a Specific RECORD Type?
How To Define a Variable of a Specific RECORD Type?
How To Assign Values to Data Fields in RECORD Variables?
How To Retrieve Values from Data Fields in RECORD Variables?
How To Define a Data Field as NOT NULL?
How To Define a RECORD Variable to Store a Table Row?
How To Assign a Table Row to a RECORD Variable?
How To Insert a Record into a Table?
How To Update a Table Row with a Record?
How To Define a Variable to Match a Table Column Data Type?

Sample scripts used in this FAQ collection assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server. It is also assumed that you are using SQL*Plus to practice the tutorial exercises.

Can DML Statements Be Used in PL/SQL?

Yes, you can run almost any DML statements in PL/SQL directly. To manipulate Oracle database data you can include INSERT, UPDATE, and DELETE statements, directly in PL/SQL programs, without any special notation, as shown in the following sample code:

(Connect to XE with SQL*Plus)

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL);
Table created.

SELECT COUNT(*) FROM student;
  COUNT(*)
----------
         0

CREATE OR REPLACE PROCEDURE HELLO AS
BEGIN
  INSERT INTO student VALUES(29, 'Bob', 'Henry');
  INSERT INTO student VALUES(30, 'Joe', 'Bush');
  UPDATE student SET first_name = 'Fyi' WHERE id = 30;
  DELETE FROM student WHERE id = 29;
END;
/

SELECT * FROM student;
ID       FIRST_NAME  LAST_NAME
-------- ----------- ----------
30       Fyi         Bush

Can DDL Statements Be Used in PL/SQL?

No, you can not run any DDL statements is PL/SQL directly. If you try to use the DROP TABLE statement inside PL/SQL, you will get a compilation error as shown below:

(Connect to XE with SQL*Plus)

BEGIN                                                          
  DROP TABLE student; -- compilation error
END;   
/

Can Variables Be Used in SQL Statements?

Yes, you can use variables in SQL statements as part of any expressions. The tutorial script provides you some good examples:

(Connect to XE with SQL*Plus)

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL);
Table created.

DECLARE
  var_id NUMBER;
  var_name CHAR(10);
BEGIN
  var_id := 29;
  var_name := 'Bob';
  INSERT INTO student VALUES(var_id, var_name, 'Henry');
  var_name := 'Joe';
  INSERT INTO student VALUES(var_id+1, var_name, 'Bush');
  var_name := 'Fyi';
  UPDATE student SET first_name = var_name
    WHERE id = var_id+1;
  DELETE FROM student WHERE id = var_id;                    
END;   
/

SELECT * FROM student;
ID       FIRST_NAME  LAST_NAME   
-------- ----------- ----------- 
30       Fyi         Bush

(Continued on next part...)

Part: 1 2 3 4 5 6

More...