<< < 1 2 3 4 5 >   Sort: Date

How To Insert a Record into a Table
How To Insert a Record into a Table? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 TAB...
2007-04-26, 4912👍, 0💬

What Is the Implicit Cursor
What Is the Implicit Cursor? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 ...
2007-04-27, 4907👍, 0💬

How To Define a Cursor Variable
How To Define a Cursor Variable? - Oracle DBA FAQ - Working with Cursors in PL/SQL To define cursor variable, you must decide which REF CURSOR data type to use. There are 3 ways to select a REF CURSOR data type: Define your own specific REF CURSOR types using the TYPE ... RETURN statement. Define yo...
2007-04-28, 4901👍, 0💬

How To Assign a Table Row to a RECORD Variable
How To Assign a Table Row to a RECORD Variable? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 ...
2007-04-27, 4900👍, 0💬

What Is a RECORD in PL/SQL
What Is a RECORD in PL/SQL? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 RECOR...
2007-04-27, 4891👍, 0💬

How To Pass a Cursor Variable to a Procedure
How To Pass a Cursor Variable to a Procedure? - Oracle DBA FAQ - Working with Cursors in PL/SQL A cursor variable can be passed into a procedure like a normal variable. The sample script below gives you a good example: CREATE OR REPLACE PROCEDURE FYI_CENTER AS sys_cur SYS_REFCURSOR; PROCEDURE emp_pr...
2007-04-28, 4884👍, 0💬

How To Assign Data of the Deleted Row to Variables
How To Assign Data of the Deleted Row to Variables? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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/SQ...
2007-04-27, 4878👍, 0💬

How To Define a Data Field as NOT NULL
How To Define a Data Field as NOT NULL? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 d...
2007-04-27, 4877👍, 0💬

What Is NULL in PL/SQL
What Is NULL in PL/SQL? - Oracle DBA FAQ - Understanding PL/SQL Language Basics NULL is a reserved key word and it stands for two things in PL/SQL: NULL is an executable statement, and means doing nothing. NULL is a data balue, and means no value. The following sample script shows you examples of us...
2007-04-29, 4876👍, 0💬

How To Define an Explicit Cursor
How To Define an Explicit Cursor? - Oracle DBA FAQ - Working with Cursors in PL/SQL An explicit cursor must be defined in the declaration part of a procedure or function with the CURSOR ... IS statement as shown in the following sample script: DECLARE CURSOR c_list IS SELECT * FROM countries; CURSOR...
2007-04-29, 4869👍, 0💬

How To Use Attributes of the Implicit Cursor
How To Use Attributes of the Implicit Cursor? - Oracle DBA FAQ - Working with Cursors in PL/SQL 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) PRIMAR...
2007-04-29, 4865👍, 0💬

How To Use FETCH Statement in a Loop
How To Use FETCH Statement in a Loop? - Oracle DBA FAQ - Working with Cursors in PL/SQL If you have a cursor opened ready to use, you can also use the FETCH statement in a loop to retrieve data from the cursor more efficiently. But you need to remember to use an EXIT statement break the loop when th...
2007-04-29, 4863👍, 0💬

How To Retrieve Data from an Cursor to a RECORD
How To Retrieve Data from an Cursor to a RECORD? - Oracle DBA FAQ - Working with Cursors in PL/SQL 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 PROCEDU...
2007-04-29, 4862👍, 0💬

Why Cursor Variables Are Easier to Use than Cursors
Why Cursor Variables Are Easier to Use than Cursors? - Oracle DBA FAQ - Working with Cursors in PL/SQL Cursor variables are easier to use than cursors because: Cursor variables are easier to define. No need to give a specific query statement. Cursor variables are easier to open. You can specify the ...
2007-04-28, 4857👍, 0💬

How To Define a Procedure inside Another Procedure
How To Define a Procedure inside Another Procedure? - Oracle DBA FAQ - Introduction to PL/SQL Define a procedure inside another procedure is supported by PL/SQL. The following tutorial script shows you an example: SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS 2 PROCEDURE DBA_TASK (day VARCHAR2) AS...
2007-04-26, 4856👍, 0💬

How To Define a Specific RECORD Type
How To Define a Specific RECORD Type? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 cal...
2007-04-27, 4842👍, 0💬

How Many Anonymous Blocks Can Be Defined
How Many Anonymous Blocks Can Be Defined? - Oracle DBA FAQ - Introduction to PL/SQL An anonymous block is stored in the user's current session without any name. So you can only define one anonymous block at any time. If you define another anonymous block, the new block will replace the previously de...
2007-04-25, 4819👍, 0💬

How To Create a Stored Program Unit
How To Create a Stored Program Unit? - Oracle DBA FAQ - Introduction to PL/SQL If you want to create a stored program unit, you can use the CREATE PROCEDURE or FUNTION statement. The example script below creates a stored program unit: SQL> set serveroutput on; SQL> CREATE PROCEDURE Hello AS 2 BEGIN ...
2007-04-25, 4812👍, 0💬

What Is PL/SQL
What Is PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional sta...
2007-04-25, 4807👍, 0💬

How To Define a Variable to Match a Table Column Data Type
How To Define a Variable to Match a Table Column Data Type? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 de...
2007-04-26, 4800👍, 0💬

Can Variables Be Used in SQL Statements
Can Variables Be Used in SQL Statements? - Oracle DBA FAQ - Working with Database Objects in PL/SQL 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 K...
2007-04-28, 4790👍, 0💬

What Is a Procedure
What Is a Procedure? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions A procedure is a named program unit. It consists of three parts: Declaration Part - Defining the procedure name, calling parameters, local variables and local procedures. Declaration part is required. Execution...
2007-04-26, 4771👍, 0💬

How To Open and Close an Explicit Cursor
How To Open and Close an Explicit Cursor? - Oracle DBA FAQ - Working with Cursors in PL/SQL An existing cursor can be opened or closed by the OPEN or CLOSE statement as shown in the following sample script: DECLARE CURSOR c_list IS SELECT * FROM countries; CURSOR t_list IS SELECT * FROM employees WH...
2007-04-29, 4767👍, 0💬

What Are the Types PL/SQL Code Blocks
What Are the Types PL/SQL Code Blocks? - Oracle DBA FAQ - Introduction to PL/SQL There are 3 types of PL/SQL code blocks: Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers. Stored Program Unit - A block of codes with a name....
2007-04-25, 4767👍, 0💬

<< < 1 2 3 4 5 >   Sort: Date