<< < 1 2 3 4 5 6 7 > >>   Sort: Rank

Use Variables in SQL Statements in Oracle
Can Variables Be Used in SQL Statements in Oracle? 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 VA...
2018-09-24, 446👍, 0💬

Name Conflicts between Variables and Columns in Oracle
How To Resolve Name Conflicts between Variables and Columns in Oracle? The best way to resolve name conflicts is to avoid using column names for variables.   ⇒Working with Database Objects in Oracle PL/SQL ⇒⇒Oracle Database Tutorials
2018-09-24, 418👍, 0💬

Invoke Built-in Functions in PL/SQL in Oracle
How To Invoke Built-in Functions in PL/SQL in Oracle? Of course, you can invoke 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; DBM...
2018-09-13, 510👍, 0💬

Implicit Cursor in Oracle
What Is the Implicit Cursor in Oracle? 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 s...
2018-09-13, 472👍, 0💬

What Is a RECORD in PL/SQL in Oracle
What Is a RECORD in PL/SQL in Oracle? RECORD is a composite data type in PL/SQL. It can have many fields representing data elements with different data types. Variables of RECORD type can be designed to hold data from database table rows. To use RECORD data type, you need to define a specific RECORD...
2018-09-13, 469👍, 0💬

Retrieve the Count of Updated Rows in Oracle
How To Retrieve the Count of Updated Rows in Oracle? After running an UPDATE statement, the database server returns a count of updated rows. You can retrieve this count from a special predefined variable called SQL%ROWCOUT, as shown in the following tutorial: CREATE TABLE emp_temp AS SELECT * FROM e...
2018-09-13, 384👍, 0💬

Assign Data from a Deleted Row to Variables in Oracle
How To Assign Data of the Deleted Row to Variables in Oracle? 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: ...
2018-09-13, 368👍, 0💬

Define a Data Field as NOT NULL in Oracle
How To Define a Data Field as NOT NULL in Oracle? 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 y...
2018-09-01, 519👍, 0💬

Define a Specific RECORD Type in Oracle
How To Define a Specific RECORD Type in Oracle? If you want to define a specific RECORD type, you need to use the TYPE ... IS RECORD statement in the declaration part of any procedure or function. The following example script defines a RECORD type called STUDENT: CREATE OR REPLACE PROCEDURE HELLO AS...
2018-09-01, 465👍, 0💬

Define a Variable for a Specific RECORD Type in Oracle
How To Define a Variable of a Specific RECORD Type in Oracle? Once you have your specific RECORD type defined, you can define new variables with this specific RECORD type like any other data type. In the sample script below, several variables are defined with a regular data type and a specific RECOR...
2018-09-01, 438👍, 0💬

Retrieve Field Values from RECORD Variables in Oracle
How To Retrieve Values from Data Fields in RECORD Variables in Oracle? 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...
2018-09-01, 423👍, 0💬

Assign Field Values into RECORD Variables in Oracle
How To Assign Values to Data Fields in RECORD Variables in Oracle? If a variable is a RECORD variable, you can assign values to its data fields by using fields names prefixed with variable name as "variable.field_name". Here is a sample script assigning values to data fields of RECORD variables: CRE...
2018-09-01, 383👍, 0💬

Define a Variable to Match Column Data Type in Oracle
How To Define a Variable to Match a Table Column Data Type in Oracle? If you have a table, and want to define some variables to have exactly the same data types as some columns in that table, you can use table_name.column_name%TYPE as data types to define those variables. The tutorial sample below s...
2018-08-14, 529👍, 0💬

Insert a RECORD into a Table in Oracle
How To Insert a RECORD into a Table in Oracle? If you have a RECORD variable with data fields matching a table structure, you can insert a row to this table with this RECORD variable using the INSERT statement as shown in the example below: CREATE TABLE emp_temp AS SELECT * FROM employees; CREATE OR...
2018-08-14, 475👍, 0💬

Assign a Table Row to RECORD Variable in Oracle
How To Assign a Table Row to a RECORD Variable in Oracle? If you have a table, and want to assign a data row of that table to a RECORD variable, you need to define this RECORD variable to match the table column structure, then use the SELECT ... INTO statement to assign a data row that RECORD variab...
2018-08-14, 466👍, 0💬

Update a Table Row with a RECORD in Oracle
How To Update a Table Row with a RECORD in Oracle? 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 * FRO...
2018-08-14, 463👍, 0💬

Define a RECORD Variable for a Table Row in Oracle
How To Define a RECORD Variable to Store a Table Row in Oracle? 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 studen...
2018-08-14, 387👍, 0💬

Logical Operations in PL/SQL in Oracle
What Are the Logical Operations in Oracle? PL/SQL supports 3 logical operations as shown in the following sample script: PROCEDURE proc_comparison AS x BOOLEAN := TRUE; y BOOLEAN := FALSE; res BOOLEAN; BEGIN res = x AND y; res = x OR y; res = NOT x; -- more statements END;   ⇒Understanding PL/SQL La...
2018-08-06, 573👍, 0💬

Assign Values to Variables in Oracle
How To Assign Values to Variables in Oracle? You can use assignment statements to assign values to variables. An assignment statement contains an assignment operator ":=", which takes the value specified on the right to the variable on left. The script below show you some examples of assignment stat...
2018-08-06, 505👍, 0💬

Arithmetic Operations in Pl/SQL in Oracle
What Are the Arithmetic Operations in Oracle? There are 4 basic arithmetic operations on numeric values as shown in the following sample script: PROCEDURE proc_arithmetic AS addition NUMBER; subtraction NUMBER; multiplication NUMBER; division NUMBER; BEGIN addition := 7 + 8; subtraction := addition ...
2018-08-06, 493👍, 0💬

Numeric Comparison Operations in Oracle
What Are the Numeric Comparison Operations in Oracle? PL/SQL supports 6 basic numeric comparison operations as shown in the following sample script: PROCEDURE proc_comparison AS res BOOLEAN; BEGIN res := 1 = 2; res := 1 &lt; 2; res := 1 &gt; 2; res := 1 &lt;= 2; res := 1 &gt;= 2; res...
2018-08-06, 475👍, 0💬

Initialize Variables with Default Values in Oracle
How To Initialize Variables with Default Values in Oracle? There are two ways to assign default values to variables at the time of declaration: Using key word DEFAULT - Appending "DEFAULT value" to the end of declaration statements. Using assignment operator - Appending ":= value" to the end of decl...
2018-08-06, 410👍, 0💬

Retrieve Data from an Explicit Cursor in Oracle
How To Retrieve Data from an Explicit Cursor in Oracle? If you have a cursor opened ready to use, you can use the FETCH ... INTO statement to retrieve data from the cursor into variables. FETCH statement will: Retrieve all the fields from the row pointed by the current cursor pointer and assign them...
2018-07-22, 531👍, 0💬

Retrieve Data from a Cursor to a RECORD in Oracle
How To Retrieve Data from a Cursor to a RECORD in Oracle? If you have a cursor opened ready to use, you can also use the FETCH statement to retrieve data from the cursor into a RECORD variable as shown in the tutorial exercise below: CREATE OR REPLACE PROCEDURE FYI_CENTER AS CURSOR t_list IS SELECT ...
2018-07-22, 522👍, 0💬

<< < 1 2 3 4 5 6 7 > >>   Sort: Rank