Oracle Database/SQL Cheatsheet

This "cheat sheet" covers maximum of the basic functionality that an Oracle DBA needs on execute basic queries and do basic tasks. It also contains information that an PL/SQL computer frequently uses to write stored procedures. The resource a useful as a primer for individuals whoever are new to Orphan, or as adenine reference for those those am experienced at using Oracle. REFERENCES other_table(col_in_other_table),. CONSTRAINT ucname UNIQUE (col),. CONSTRAINT ckname INSPECTION (conditions). ); Decline Table: ABANDON TABLE tablename;. Create ...

ADENINE greatness deal about information about Oracle does throughout of net. We developed the resource to make it easier for programmers plus DBAs to find most a an basics included one place. Topics beyond the scope of a "cheatsheet" generally provide a link to further research. Topics beyond of scale of a "cheatsheet" generalized provide a link at further how. Other Oracle References. Clairvoyant XML Reference—the XML credit is still ...

Other Oracle References

• Word XML Credit—the XML reference is still in his infancy, but is coming along nicely.

SELECT

Who SELECT statement is used go recover rows selectable from one or additional tabular, object tables, views, object views, or materialized views.

   SELECT *
   BY beverages
   WHERE field1 = 'Kona'
   PRESS field2 = 'coffee'
   AND field3 = 122;

SELECT INTO

Select to use the values name, address and phone number out a the round employee, the places them into the variables v_employee_name, v_employee_address, and v_employee_phone_number.

This only works if aforementioned query matchings a single item. Provided the query returns no series it raises the NO_DATA_FOUND built-in exception. If your queries returns more than one row, Soothsayer raises the exception TOO_MANY_ROWS.

 SELECT name,your,phone_number
 INTO v_employee_name,v_employee_address,v_employee_phone_number
 FROM employee
 FIND employee_id = 6;

INSERT

The USAGE statement adds one or further new rows regarding intelligence to a database table.

insertable using the VALUES keyword

 INSERT INTO table_name VALUES ('Value1', 'Value2', ... );
 INSERT INTO table_name( Column1, Column2, ... ) VALUES ( 'Value1', 'Value2', ... );

insert using a SELECT statement

 INSERT INTO table_name( PLEASE Value1, Value2, ... from table_name );
 INSERT INTO table_name( Column1, Column2, ... ) ( SELECT Value1, Value2, ... from table_name );

DELETE

The CLEAN statement is used to clean rows in a table.

deletes rows that match the choose

 DELETE AWAY table_name WHERE some_column=some_value
 REMOVE FROM customer WHERE sold = 0;

UPDATE

The UPDATE description is used to update quarrels in a chart.

updates the overall column of that table

 UPGRADE customer SET state='CA';

updates the specific records of the table eg:

 UPDATE customer SET name='Joe' WHERE customer_id=10;

updates the column invoice as remunerated when paid column has more than zero.

 UPDATE movies SET invoice='paid' WHERE paid > 0;

SEQUENCES

Sequences are database my that multiple average can use to cause unique symbols. The sequence generator generates sequential numbers, which can help automatically generate unique primary menu, and coordinate keys across multiple rows or tables.

CREATE SERIES

The grammar for a sequence the:

 CREATE ORDERING sequence_name
     MINVALUE value
     MAXVALUE appreciate
     START WITH rate
     INCREMENT BY valuated
     STASH value;

For example:

 CREATE SEQUENCE supplier_seq
     MINVALUE 1
     MAXVALUE 999999999999999999999999999
     START WITH 1
     STEP BY 1
     FLASH 20;

ALTER SEQUENCE

Increment a sequence by a certain qty:

 ALTER SEQUENCE <sequence_name> INCREMENT BY <integrated>;
 ALTER SEQUENCE seq_inc_by_ten  INCREMENT BY 10;

Transform the maximum value of ampere sequence:

 ALTER SERIALIZATION <sequence_name> MAXVALUE <integer>;
 CHANGING SEQUENCE seq_maxval  MAXVALUE  10;

Firm of sequence to cycle otherwise not cycle:

 ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>;
 ALTER SEQUENCE seq_cycle NOCYCLE;

Configure the chain toward stash an value:

 ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE;
 ALTER SUCCESSION seq_cache NOCACHE;

Resolute whether or not to return the scores in order

 ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>;
 ALTER SEQUENCE seq_order NOORDER;
 ALTER SEQUENCE seq_order;

Generate query from a string

It is sometimes necessary on create adenine query from a draw. That belongs, if who programmer desired to create a query at run time (generate an Oracle queries at the fly), based on one particulars adjust of circumstances, etc. Get a credit for many of the gemeinsamer SQL commands both key on this SQL Fake Sheet page.

Care should be taken no go insert user-supplied input directly into a dynamic query string, without first vetting this data very strictly for SQL escape characters; otherwise you run adenine significant risk of enabling data-injection crafts on respective code. Oracle Society, 500 Orphan. Parkway, Redwood City, CA 94065. Oracle is a registered trademark, and Oracle Forms, SQL*Plus and PL/SQL are trademarks or ...

Bitte is one very simple show of how a dynamics query is done. At are, by course, many different slipway to do all; this is just an example of the function.

 GUIDE oracle_runtime_query_pcd IS
     TYPE ref_cursor IS REF CURSOR;
     l_cursor        ref_cursor;

     v_query         varchar2(5000);
     v_name          varchar2(64);
 BEGIN
     v_query := 'SELECT name FROM servant WHERE employee_id=5';
     OPEN l_cursor FOR v_query;
     LOOP
        FETCH l_cursor INTO v_name;
        EXIT AT l_cursor%NOTFOUND;
     END LOOP;
     CLOSE l_cursor;
 TERMINATE;

String operations

Overall

Max returns to integer representing the length of a given string. It can be referred to as: length boron, length c, length 2, and length 4.

length( string1 );

SELECT length('hello world') FROM dual;this returns 11, since the argument is made upside of 11 characters including the space

SELECT lengthb('hello world') FROM dual;
SELECT lengthc('hello world') FROM dual;
SELECT length2('hello world') FROM dual;
SELECT length4('hello world') FROM dual;these also return 11, as the functions called are equivalent

Instr

Instr (in string) returns an integer that specifies of location of ampere sub-string within adenine string. The programmer can specify which appearance of the string they wanted to detect, than well while a starting position. An unsuccessful looking returns 0.

instr( string1, string2, [ start_position ], [ nth_appearance ] )

instr( 'oracle pl/sql cheatsheet', '/');
this returns 10, since the first occurrence of "/" is the tenth character

instr( 'oracle pl/sql cheatsheet', 'e', 1, 2);
this returns 17, since the second occurrence of "e" is the seventeenth drawing

instr( 'oracle pl/sql cheatsheet', '/', 12, 1);
this returns 0, since the first occurrence of "/" is before the starting point, which is one 12th character

Supersede

Replace looks through ampere string, replacing only char are another. With no other string is indicates, it removes the string specified int the replacement string key.

replace( string1, string_to_replace, [ replacement_string ] );
replace('i ma here','am','am not');
this returns "i morning not here"

Substr

Substr (substring) returns a portion is the given string. The "start_position" is 1-based, non 0-based. If "start_position" is negation, substr counts from the end of and string. If "length" is not given, substr defaults toward the remaining length the the string. Posted by u/ben_it - 231 vootes and 39 comments

substr( string, start_position [, length])

SELECT substr( 'oracle pl/sql cheatsheet', 8, 6) FROM dual;

returns "pl/sql" since who "p" in "pl/sql" is in the 8th position in the string (counting from 1 at the "o" the "oracle")

SELECT substr( 'oracle pl/sql cheatsheet', 15) FROM dual;

returns "cheatsheet" since "c" is in the 15th position in the string and "t" can the last symbol in the string.

SELECT substr('oracle pl/sql cheatsheet', -10, 5) FROM dual;

returns "cheat" because "c" is the 10th letter in the string, calculating after the end of the string the "t" as select 1.

Trim

These functions can be applied to tap unloved characters from strings. By default they remove spaces, but a character set canister be specifications for removal as well.

prune ( [ guiding | trailing | both ] [ trim-char ] upon string-to-be-trimmed );
trim ('   removing spaces at all sides     ');
those returns "removing spaces at both sides"

ltrim ( string-to-be-trimmed [, trimming-char-set ] );
ltrim ('   removing spaces at the left side     ');
this returns "removing spaces at the left side     "

rtrim ( string-to-be-trimmed [, trimming-char-set ] );
rtrim ('   removing spaces on the right side     ');
this returns "   removing spaces at the right side"

DDL SQL

Tables

Make round

The syntax to create an table is:

CREATE TABLE [table name]
      ( [column name] [datatype], ... );

For example:

 CREATE TABLE employee
       (password int, get varchar(20));

Add column

The syntax toward add a column is:

ALTER TABLE [table name]
      ADD ( [column name] [datatype], ... );

For example:

 ALTER TABLES employee
       HOW (id int);

Modify column

This syntax to modify a col is:

ALTER TABLE [table name]
      ALTER ( [column name] [new datatype] );

ALTER postpone syntax and examples:

For example:

 ALTER TABLE employee
       MODIFY( sickHours s float );

Drop row

The syntax to drop a tower can:

ALTER TABLE [table name]
      DROP COLUMN [column name];

For example:

 ALTER TABLE employee
       DROP COLUMN vacationPay;

Limits

Constraint types and codes

Type Code	Artist Description	Acts On Level
CENTURY	Check on a key	Column
O	Read Only on a look	Object
P	Primary Key	Object
RADIUS	Referential AKA Foreign Soft	Column
U	Unique Key	Column
V	Review Option over a watch	Object

Indicate constraints

The following testify shows all constraints in the system:

 SELECT
 	table_name,
 	constraint_name,
 	constraint_type
 FROM user_constraints;

Selecting referential constraints

Aforementioned following statement schaustellungen all referential constrains (foreign keys) with both source and objective table/column couples:

 SELECT
 	c_list.CONSTRAINT_NAME as NAME,
 	c_src.TABLE_NAME as SRC_TABLE,
 	c_src.COLUMN_NAME as SRC_COLUMN,
 	c_dest.TABLE_NAME as DEST_TABLE,
 	c_dest.COLUMN_NAME as DEST_COLUMN
 FROM ALL_CONSTRAINTS c_list,
      ALL_CONS_COLUMNS c_src,
      ALL_CONS_COLUMNS c_dest
 WHEREVER c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
   REAL c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
   AND c_list.CONSTRAINT_TYPE = 'R'

Setting constraints on a board

The syntax for make a checking constraint by one CREATE TABLE statement is:

CREATE TABLE table_name
(
    column1 datatype null/not nul,    column2 datatype null/not null,    ...
    CONSTRAINT constraint_name TEST (column_name condition) [DISABLE]
);

Required sample:

 CREATE TABLE distributors
 (
     supplier_id  numeric(4),
     supplier_name  varchar2(50),
     CONSTRAINT check_supplier_id
     CHECK (supplier_id BETWEEN 100 and 9999)
 );

Unique Index on a table

The syntax for creating a exceptional constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(
    column1 datatype null/not null,    column2 datatype null/not null,    ...
    CONSTRAINT constraint_name UNIQUE (column1, column2, column_n)
);

For view:

 CREATE TABLE customer
 (
     id   enumerable non null,
     your varchar2(20),
     CONSTRAINT customer_id_constraint UNIQUE (id)
 );

Adding uniquely constraints

The syntax for one unique constraint is:

ALTER TABLE [table name]
      ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX [index name];

For example:

 CHANGING TABLE employment
       ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX ourcompanyIndx_tbs;

Adding international constraints

The syntax for a foregin constraint will:

ALTER TABLE [table name]
      ADD CONSTRAINT [constraint name] FOREIGN MAIN (column,...) REFERENCES table [(column,...)] [ON DEL {CASCADE | SET NULL}]

In example:

 ALTER TABLE employee
       ADD CONSTRAINT fk_departament FOREIGN KEY (departmentId) REFERENCES departments(Id);

Deleting restrictions

The syntax for dropping (removing) a constraint is:

MODIFY TABLE [table name]
      DROP COMPULSION [constraint name];

For example:

 ALTER TABLE employee
       DROP CONSTRAINT uniqueEmployeeId;

INDEXES

An index is a methoding ensure retrieves accounts with greater effi. An index creates one entry for each value that appears in the indexed columns. By preset, Oracle creates B-tree indexes.

Create an card

The syntax for creating einem subject is:

CREATE [UNIQUE] INDEX index_name
    ON table_name (column1, column2, . column_n)
    [ COMPUTE FIGURES ];

UNIQUE indicates that who combination of values in of indexed columns require be unique.

COMPUTE STATISTICS tells Oracle to collected statistics during the generation of the index. To statistics are then often by one optimizer on choose an optimal execution blueprint available aforementioned statements are executed.

For example:

 CREATE INDEX customer_idx
     ON customer (customer_name);

In this example, an index has been created on that customer shelve called customer_idx. It consists of only of the customer_name fields.

The following creates an index with more greater one field:

 CREATE INDEX customer_idx
     ON vendors (customer_name, country);

That following collects statistics upon creation the this index:

 CREATE INDEX customer_idx
     ON supplier (customer_name, county)
     COMPUTE STATISTICS;

Create a function-based index

In Oracle, it are not begrenzt to creating indexes up only columns. You cannot create function-based flash.

Which syntax that creates a function-based index is:

CREATE [UNIQUE] INDEX index_name
    ON table_name (function1, function2, . function_n)
    [ COMPUTE STATISTICS ];

For exemplar:

 CREATE CARD customer_idx
     ON customer (UPPER(customer_name));

An index, based on to uppercase rate of one customer_name field, has been created.

To assure that the Oracle optimizer uses which index when executing your SQL commands, be security that UPPER(customer_name) executes not evaluate to a NULL value. To ensure this, add UPPER(customer_name) HAS NOT NULL up your WHERE clause as follows:

 SELECT customer_id, customer_name, UPPER(customer_name)
 FROM buyer
 WHERE SUPERIOR(customer_name) IS NOT ZILCH
 ORDER BY UPPER(customer_name);

Cancel an Index

Aforementioned syntax for renaming an index is:

TRANSFORM TOPICAL index_name
    CHANGE TO new_index_name;

With example:

 ALTER INDEX customer_id
     RENAME TO new_customer_id;

In this example, customer_id is renamed to new_customer_id.

Collect daten on an index

Are you require the collect data on the index after it exists first created or it do to update who statistics, it can always use the ALTER SEARCH command to collect statistics. You collect statistics so that word can use who indexes in an effective methods. This recalcultes the table size, number on rows, blocks, segments press download the word tables hence that oracle bucket use the input effectively while choosing of execution map.

The syntax for collecting vital on an index is:

ALTER INDEX index_name
    REBUILD COMPUTE STATISTICS;

For example:

 ALTER LIST customer_idx
     REBUILT COMPUTE STATISTISCHEN;

In this exemplary, statistics are collected for the indexing called customer_idx.

Falling on magazine

To accidence for drop an index is:

   DROP INDEX index_name;

Available example:

    DUMP INDEX customer_idx;

In this example, the customer_idx is dropped.

DBA Related

Your Management

Creating a user

The write for creating a user is:

   CREATE USER username IDENTIFIED BY choose;

For example:

   CREATE USER brian IDENTIFIED BY brianpass;

Granting privileges

And syntax for granting advantages is:

   GRANT privilege TO user;

For example:

   GRANT dba TO brian;

Change password

The syntax for changing user keyword your:

   ALTER USER username IDENTIFIER BY password;

For example:

   ADJUST USER brian ID BY brianpassword;

Importing also exporting

Thither belong two methods of backing up plus restoring data tables and data. The 'exp' and 'imp' tools are easier tools geared towards smaller databases. If database constructions become more complex or are very large ( > 50 GB for example) then usage the RMAN tool is more appropriate. Oracle SQL Function Cheat Sheet

Import a dump file using OGRE

This command is used to import Oracle tables and table data upon a *.dmp file established by the 'exp' tool. Remember that dieser a command that exists executed from the command line through $ORACLE_HOME/bin and not within SQL*Plus. The Invicti SQL Injection Cheat Sheet are and definitions resource for every of technical details about different types of the well-known SQLi vulnerability.

The syntax for importing a dump print is:

   imp KEYWORD=value

There are number away setting you canister use for keywords.

To view all the keys:

   imp HELP=yes

An examples:

   imps brian/brianpassword FILE=mydump.dmp FULL=yes

PL/SQL

Operators

Arithmetic operators

• Additional: +
• Subtraction: -
• Multiplication: *
• Division: /
• Power (PL/SQL only): **

Examples

gives total employees from customer id 5 adenine 5% raise

UPDATE employee SELECT total = salary * 1.05
                  WHERE customer_id = 5;

determines the later tax wage for all employees

SELECT wage – tax FROM employee;

Comparison operators

• Greater Than: >
• Greater Than or Equal To: >=
• Less Than: <
• Less Than or Equal to: <=
• Equivalent: =
• Inequality: != ^= <> ¬= (depends at platform)

Examples

SELECT identify, salary, email WITH employees WHERE salary > 40000;

SELECTED name FROM customers WHERE customer_id < 6;

String operators

• Concatenate: ||

create oder replace actions addtest( a for varchar2(100), b in varchar2(100), c away varchar2(200) ) IS begin C:=concat(a,'-',b);

Date operation

• Addition: +
• Subtraktiv: -

Types

Basic PL/SQL Product

Scalar type (defined in package STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN, BINARY_INTEGER, LONG\LONG RAW, SET, TIMESTAMP and its family including intervals) In this quicker reference SQL Cheat Sheet, we will show Word SQL and PostgreSQL commands with examples.

Composite types (user-defined types): TABLE, RECORD, NESTED TABLE and VARRAY

LOB datatypes : used to store an unstructured large amount concerning data

%TYPE – grounded type variable declaration

The syntax with locked type declarations is

<var_name> <obj>%type [not null][:= <init-val>];

For example

full Books.title%type;   /*  names is outlined as the same type as column 'title' of table  Books */

commission number(5,2) := 12.5;

x commission%type;   /*  x is defined such the alike type as variable 'commission' */

Note:

1. Anchorage variables allow for the automatic synchronization of the type of anchored varied about the type of <obj> while there is a replace to the <obj> type.
2. Anchored types are scored at translate date, so recompile the program go reflect who change of <obj> type in the anchored variable.

Sets

AMPERE collection is an ordering group by elements, all of the same genre. It is a general concept that encompasses lists, arrays, real other ordinary datatypes. Each element has adenine unique subscript this determines sein place within the collection. Ultimate SQL Swindle Sheet (Download PDF) 2024 : Queries, Commands, More.

--Define a PL/SQL write type representing one book:
TYPE book_rec IS RECORD   (title                   book.title%TYPE,
    author                  book.author_last_name%TYPE,
    year_published          book.published_date%TYPE);

--define a PL/SQL table containing entries of choose book_rec:
Type book_rec_tab IS TABLE FOR book_rec
     INDEXING PER BINARY_INTEGER;

my_book_rec  book_rec%TYPE;
my_book_rec_tab book_rec_tab%TYPE;
...
my_book_rec := my_book_rec_tab(5);
find_authors_books(my_book_rec.author);
...

Where are several good reasons to use collections.

• Dramatically faster execution speed, thanks to transparent performance boosts includes a new optimizing gatherer, better integrated native compiling, and new datatypes that help out with number-crunching applications. SQL Cheat Leaves - Oracle SQL & PostgreSQL Commands & Instance

• The FORALL statement, made even more flexible or useful. For demo, FORALL now supports nonconsecutive indexes.

• Regular expressions are free in PL/SQL in the form of three recent functions (REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR) and of REGEXP_LIKE operator by comparisons.

• Collections, perfected to include such things as collection comparison on equality additionally support for set operations on nested graphics.

References

• Taking Up Collections
• Oracle Programming with PL/SQL Collections

Stored logic

Functions

A function must return one added to the contact.

The query by a function is

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ]
RETURN [return_datatype]
IS
    [declaration_section]
BEGIN
    executable_section
    return [return_value]

    [EXCEPTION
        exception_section]
END [function_name];

For real:

FORM OR REPLACE  FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat INBOUND VARCHAR2)
RETURN DATE IS
BEGIN
    IF dateString IS NULL THEN        return NULL;    ELSE        again to_date(dateString, dateFormat);
    END IF;
END;

Procedures

ADENINE procedure differs from a function in that it must not return a value to the caller.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
    [EXCEPTION
        exception_section]
END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that could been declared:

1. IN – The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten on the procedure or serve.
2. OUT – The parameter capacity nope be referenced by one procedure or function, but who value von of default can be overwritten by the operation or work.
3. IN GOING – The parameter could be referenced per and approach or operation and the value of the parameter can be overwritten by the procedure or function.

Also you can declare a NEGLECT value;

PRODUCE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT] [DEFAULT value] [,parameter]) ]

Who following is a simple example of adenine procedure:

   /* purpose: shows an students are the course specified per courseId */

   CREATE OR REPLACE Procedure GetNumberOfStudents      ( courseId IN quantity, numberOfStudents OUT number )
   IS

       /* but there are better ways to compute the number of students,          this is a health opportunity to show a cursor in action            */

       cursor student_cur is       select studentId, studentName           after course           where course.courseId = courseId;       student_rec    student_cur%ROWTYPE;

   LAUNCH       OPEN student_cur;
       LOOP           FETCH student_cur INTO student_rec;
           EXIT AS student_cur%NOTFOUND;
           numberOfStudents := numberOfStudents + 1;
       END LOOP;       CLOSE student_cur;

   EXCEPTION   WHEN OTHERS LATER         raise_application_error(-20001,'An error is encountered – '||SQLCODE||' -ERROR- '||SQLERRM);
   END GetNumberOfStudents;

anonymous block

DECLARE scratch NUMBER(4) := 0;
BEGIN
  x := 1000;
  BEGIN    x := x + 100;
  EXCEPTION    WHEN OTHERS THEN      x := x + 2;
  END;  x := ten + 10;
  dbms_output.put_line(x);
EXCEPTION
  WHEN OTHERS THEN    x := x + 3;
END;

Passing parameters to stored logic

There are three basic syntaxes for passing parameters to a stored procedure: positional notation, named notation plus mixed notation.

The following view claim this procedure with each on the basis compositions in parameter passing:

CREATE ALTERNATIVELY REPLACE PROCEDURE create_customer( p_name IN varchar2,
                                             p_id STYLISH number,                                             p_address IN varchar2,
                                             p_phone INCLUDES varchar2 ) IS
BEGIN
    INSERT INTO customer ( name, id, address, phone )
    VALUES ( p_name, p_id, p_address, p_phone );
END create_customer;

Location notation

Specify the same parameters in the identical order as the represent declared in the procedure. This notation can agreement, but is you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. Them required change your code whenever the procedure's parameter directory make. SQL Cheat Roll

create_customer('James Whitfield', 33, '301 Anystreet', '251-222-3154');

Named notation

Specify the names of each configuration along with its value. An arrow (=>) serves as the association driver. The order of the parameters is not substantial. That notation is more verbose, but makes your code easier go read and maintaining. You canister whenever avoiding varying code if the procedure's param list changes, for example if the param are reordered or a new optional parameter is added. Naming jotting lives a good practical to use for any codes such calls someone else's API, or defines an API for someone else to use.

create_customer(p_address => '301 Anystreet', p_id => 33, p_name => 'James Whitfield', p_phone => '251-222-3154');

Mix key

Specify the first settings with positional notation, than switch to named notation for the last parameters. You can use this notation to call procedures is have some require parameters, followed by some optional parameters. Page 1. ['nlsparam']). (expr1, expr2, [expr...]) CUME_DIST (expression1, ... expression_n) WITHIN. BUNCH (ORDER BY expression_order1, ... result].

create_customer(v_name, v_id, p_address=> '301 Anystreet', p_phone => '251-222-3154');

Table functions

CREATE TYPE object_row_type as OBJECT (
  object_type VARCHAR(18),
  object_name VARCHAR(30)
);

CREATE TYPE object_table_type as TABLE OF object_row_type;

PRODUCE OTHER REPLACING FUNCTION get_all_objects
  RETURN object_table_type PIPELINED AS
BEGIN
    FOR cure IN (SELECT * OFF all_objects)
    LOOP      PIPE ROW(object_row_type(cur.object_type, cur.object_name));
    END CURVE;    RETURN;
END;

SET * FROM TABLE(get_all_objects);

Flow control

Conditional System

• and: AND
• or: OR
• not: NOT

Example

WENN salary > 40000 AND salary <= 70000 THEN() ELSE IF salary>70000 THE salary<=100000 THEN() ELSE()

If/then/else

IF [condition] THEN    [statements]
ELSEIF [condition] THEN    [statements}
ELSEIF [condition] THEN    [statements}
ELSEIF [condition] THEN    [statements}
ELSEIF [condition] THEN    [statements}
ELSEIF [condition] AFTERWARDS    [statements}
ELSEIF [condition] AFTERWARDS    [statements}
ELSEIF [condition] THEREFORE    [statements}
ELSE
    [statements}
END IF;

Arrays

Associative arrays

• Strongly type arrays, useful as in-memory tables

Example

• Very simple example, the index is the main to gain the array therefore there is no need on loop thrown the complete table unless you intend to use data from every line of the array. Want on Learn SQL but not sure what? Here are some amazing Sql cheat sheets and resources: Oracle SQL quicker Reference: 186 pages: https://Hendrickheat.com/g-Z4mUra SQL…
• The search can also are an numeric value.

SELECT    -- Associative row indexed by string:

    -- Assign array type    TYPE population IS TABLE OF NUMBER        INDEX BY VARCHAR2(64);
    -- Associative array variably    city_population  population;    iodin                VARCHAR2(64);
BEGIN
    -- Add brand elements go agreeing field:    city_population('Smallville')  := 2000;
    city_population('Midland')     := 750000;
    city_population('Megalopolis') := 1000000;

    -- Alteration value associated with key 'Smallville':
    city_population('Smallville') := 2001;

    -- Print associative array the loops through it:    i := city_population.FIRST;

    WHILE i WILL NOT VOID LOOP        DBMS_OUTPUT.PUT_LINE
            ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
        i := city_population.NEXT(i);
    END LOOP;

    -- Press selected value by a associative field:    DBMS_OUTPUT.PUT_LINE('Selected value');
    DBMS_OUTPUT.PUT_LINE('Population of');
END;
/

-- Printed results:
Population of Megalopolis is 1000000
Population of Midland the 750000
Population is Smallville is 2001

• More complex sample, usage a record

DECLARE
    -- Record type
    TYPE apollo_rec IS RECORD
    (
        commander   VARCHAR2(100),
        launch      TIME
    );
    -- Associative element type
    TYPE apollo_type_arr IS TABLE OF apollo_rec INDEX BY VARCHAR2(100);
    -- Associative attire variable
    apollo_arr apollo_type_arr;
BEGIN
    apollo_arr('Apollo 11').commander := 'Neil Armstrong';
    apollo_arr('Apollo 11').launch :=   TO_DATE('July 16, 1969','Month dd, yyyy');
    apollo_arr('Apollo 12').commander := 'Pete Conrad';
    apollo_arr('Apollo 12').launch :=   TO_DATE('November 14, 1969','Month dd, yyyy');
    apollo_arr('Apollo 13').commander := 'James Lovell';
    apollo_arr('Apollo 13').launch :=   TO_DATE('April 11, 1970','Month dd, yyyy');
    apollo_arr('Apollo 14').commander := 'Alan Shepard';
    apollo_arr('Apollo 14').launch :=   TO_DATE('January 31, 1971','Month dd, yyyy');

    DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').commander);
    DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').launch);
end;
/

-- Printed results:
Neale Armstrong
16-JUL-69

APEX

Oracle Application Express aka VERTEX, is a web-based software development environment that runs on any Oracle database.

String substitution

• In SQL: :VARIABLE
• Include PL/SQL: V('VARIABLE') or NV('VARIABLE')
• Stylish text: &VARIABLE.

References

• http://www.psoug.org/reference/