Declaration Syntax
identifier [CONSTANT] datatype [NOT NULL] [:= expr | DEFAULT expr]
Note: Square brace indicates optional
Valid variable declarations
DECLARE
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := ’Atlanta’;
c_comm CONSTANT NUMBER := 1400;
v_NoOfSeats NUMBER DEFAULT 45;
v_FirstName VARCHAR2(20) DEFAULT ‘SCOTT’
Invalid variable Declarations
v_deptno number(2) NOT NULL;
v_name varchar2 DEFAULT ‘Sachin’;
Inheriting data type
You can declare variables to inherit the data type of a database column or other variable
v_empno emp.empno%TYPE ;
Constants:using the CONSTANT keyword we can declare a constant
c_max_size CONSTANT NUMBER := 100;
Bind Variable:a variable declared in a host environment and used in many blocks by referencing it with “:” prefix
Row Type Variable:holds one record/row at a time
e.g. v_record emp%ROWTYPE;
PL/SQL Record type Variable:A record is a group of related data items stored in fields, each with its own name and data type.
A record containing a field for each item lets you treat the data as a logical unit.
The variable based on a PL/SQL record type is a composite data member having fields as defined in the corresponding record type
Syntax:
TYPE type_name IS RECORD (field_declaration[,field_declaration]...);
variable_name type_name;
Example:
-- Type declaration
TYPE DeptRec IS RECORD (
dept_id dept.deptno%TYPE,
dept_name VARCHAR2(14),
dept_loc VARCHAR2(13)
);
-- Record type variable declaration
vDeptRec DeptRec;
Guidelines for Declaring PL/SQL Variables
Ø Follow the naming conventions
Ø Initialize variables designated as NOT NULL and CONSTANT
Ø Declare one identifier per line
Ø Initialize identifiers by using the assignment operator (:=) or the reserved word “DEFAULT”
Hello There,
ReplyDeleteSeems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on PL/SQL Variables
I need to setup auditing (or logging) for RESULTS of queries that are run by certain users against certain tables.
Fine-grained auditing will result in one audit record with the text of the select statement. But how can I audit the actual result of a select query? It seems to me that it should be "select for each row" trigger for this that does not exist.
For example, when they run some select statement against "patients" table I need to log all PatientID that were selected from this table. So if there were 100 records selected, there should be 100 "audit" records with patientID logged. Is this possible?
Awesome! Thanks for putting this all in one place. Very useful!
Thank you,
Samson
Hello There,
ReplyDeleteI genuinely do look forward for the time where you post some new write ups. Your blog make me feel so educated! Continue soaring and writing please.
I am trying to count the number of rows each view has in an oracle database.
There are over 100 views.
I want it to display the view name and the number of rows.
I tried
SELECT VIEW_NAME, COUNT(*) FROM ALL_VIEWS
But this doesn't work.
I tried a similar query using a group by of view_name and max(rownum) but this too didn't work.
The SQL I'm writing is generating the count of view_names or max(rownum)'s assigned to view_names. I need the count of rows for each individual view_name.
Is there a simple way to do this?
Once again thanks for your tutorial.
Kind Regards,
Preethi.