· 8 years ago · Jan 31, 2018, 06:46 PM
1
2Open main menu
3Wikibooks Search
4EditWatch this page
5Oracle Database/SQL Cheatsheet
6Page issues
7< Oracle Database
8This "cheat sheet" covers most of the basic functionality that an Oracle DBA needs to run basic queries and perform basic tasks. It also contains information that a PL/SQL programmer frequently uses to write stored procedures. The resource is useful as a primer for individuals who are new to Oracle, or as a reference for those who are experienced at using Oracle.
9
10A great deal of information about Oracle exists throughout the net. We developed this resource to make it easier for programmers and DBAs to find most of the basics in one place. Topics beyond the scope of a "cheatsheet" generally provide a link to further research.
11
12Other Oracle References
13
14Oracle XML Reference—the XML reference is still in its infancy, but is coming along nicely.
15SELECT Edit
16The SELECT statement is used to retrieve rows selected from one or more tables, object tables, views, object views, or materialized views.
17
18 SELECT *
19 FROM beverages
20 WHERE field1 = 'Kona'
21 AND field2 = 'coffee'
22 AND field3 = 122;
23SELECT INTO Edit
24Select into takes the values name, address and phone number out of the table employee, and places them into the variables v_employee_name, v_employee_address, and v_employee_phone_number.
25
26This only works if the query matches a single item. If the query returns no rows it raises the NO_DATA_FOUND built-in exception. If your query returns more than one row, Oracle raises the exception TOO_MANY_ROWS.
27
28 SELECT name,address,phone_number
29 INTO v_employee_name,v_employee_address,v_employee_phone_number
30 FROM employee
31 WHERE employee_id = 6;
32INSERT Edit
33The INSERT statement adds one or more new rows of data to a database table.
34
35insert using the VALUES keyword
36
37 INSERT INTO table_name VALUES ('Value1', 'Value2', ... );
38 INSERT INTO table_name( Column1, Column2, ... ) VALUES ( 'Value1', 'Value2', ... );
39insert using a SELECT statement
40
41 INSERT INTO table_name( SELECT Value1, Value2, ... from table_name );
42 INSERT INTO table_name( Column1, Column2, ... ) ( SELECT Value1, Value2, ... from table_name );
43DELETE Edit
44The DELETE statement is used to delete rows in a table.
45
46deletes rows that match the criteria
47
48 DELETE FROM table_name WHERE some_column=some_value
49 DELETE FROM customer WHERE sold = 0;
50UPDATE Edit
51The UPDATE statement is used to udpate rows in a table.
52
53updates the entire column of that table
54
55 UPDATE customer SET state='CA';
56updates the specific record of the table eg:
57
58 UPDATE customer SET name='Joe' WHERE customer_id=10;
59updates the column invoice as paid when paid column has more than zero.
60
61 UPDATE movies SET invoice='paid' WHERE paid > 0;
62SEQUENCES Edit
63Sequences are database objects that multiple users can use to generate unique integers. The sequence generator generates sequential numbers, which can help automatically generate unique primary keys, and coordinate keys across multiple rows or tables.
64
65CREATE SEQUENCE Edit
66The syntax for a sequence is:
67
68 CREATE SEQUENCE sequence_name
69 MINVALUE value
70 MAXVALUE value
71 START WITH value
72 INCREMENT BY value
73 CACHE value;
74For example:
75
76 CREATE SEQUENCE supplier_seq
77 MINVALUE 1
78 MAXVALUE 999999999999999999999999999
79 START WITH 1
80 INCREMENT BY 1
81 CACHE 20;
82ALTER SEQUENCE Edit
83Increment a sequence by a certain amount:
84
85 ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
86 ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 10;
87Change the maximum value of a sequence:
88
89 ALTER SEQUENCE <sequence_name> MAXVALUE <integer>;
90 ALTER SEQUENCE seq_maxval MAXVALUE 10;
91Set the sequence to cycle or not cycle:
92
93 ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>;
94 ALTER SEQUENCE seq_cycle NOCYCLE;
95Configure the sequence to cache a value:
96
97 ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE;
98 ALTER SEQUENCE seq_cache NOCACHE;
99Set whether or not to return the values in order
100
101 ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>;
102 ALTER SEQUENCE seq_order NOORDER;
103 ALTER SEQUENCE seq_order;
104Generate query from a string Edit
105It is sometimes necessary to create a query from a string. That is, if the programmer wants to create a query at run time (generate an Oracle query on the fly), based on a particular set of circumstances, etc.
106
107Care should be taken not to insert user-supplied data directly into a dynamic query string, without first vetting the data very strictly for SQL escape characters; otherwise you run a significant risk of enabling data-injection hacks on your code.
108
109Here is a very simple example of how a dynamic query is done. There are, of course, many different ways to do this; this is just an example of the functionality.
110
111 PROCEDURE oracle_runtime_query_pcd IS
112 TYPE ref_cursor IS REF CURSOR;
113 l_cursor ref_cursor;
114
115 v_query varchar2(5000);
116 v_name varchar2(64);
117 BEGIN
118 v_query := 'SELECT name FROM employee WHERE employee_id=5';
119 OPEN l_cursor FOR v_query;
120 LOOP
121 FETCH l_cursor INTO v_name;
122 EXIT WHEN l_cursor%NOTFOUND;
123 END LOOP;
124 CLOSE l_cursor;
125 END;
126String operations Edit
127Length Edit
128Length returns an integer representing the length of a given string. It can be referred to as: lengthb, lengthc, length2, and length4.
129
130length( string1 );
131SELECT length('hello world') FROM dual;
132this returns 11, since the argument is made up of 11 characters including the space
133SELECT lengthb('hello world') FROM dual;
134SELECT lengthc('hello world') FROM dual;
135SELECT length2('hello world') FROM dual;
136SELECT length4('hello world') FROM dual;
137these also return 11, since the functions called are equivalent
138Instr Edit
139Instr (in string) returns an integer that specifies the location of a sub-string within a string. The programmer can specify which appearance of the string they want to detect, as well as a starting position. An unsuccessful search returns 0.
140
141instr( string1, string2, [ start_position ], [ nth_appearance ] )
142
143instr( 'oracle pl/sql cheatsheet', '/');
144this returns 10, since the first occurrence of "/" is the tenth character
145
146instr( 'oracle pl/sql cheatsheet', 'e', 1, 2);
147this returns 17, since the second occurrence of "e" is the seventeenth character
148
149instr( 'oracle pl/sql cheatsheet', '/', 12, 1);
150this returns 0, since the first occurrence of "/" is before the starting point, which is the 12th character
151Replace Edit
152Replace looks through a string, replacing one string with another. If no other string is specified, it removes the string specified in the replacement string parameter.
153
154replace( string1, string_to_replace, [ replacement_string ] );
155replace('i am here','am','am not');
156this returns "i am not here"
157Substr Edit
158Substr (substring) returns a portion of the given string. The "start_position" is 1-based, not 0-based. If "start_position" is negative, substr counts from the end of the string. If "length" is not given, substr defaults to the remaining length of the string.
159
160substr( string, start_position [, length])
161
162SELECT substr( 'oracle pl/sql cheatsheet', 8, 6) FROM dual;
163returns "pl/sql" since the "p" in "pl/sql" is in the 8th position in the string (counting from 1 at the "o" in "oracle")
164
165SELECT substr( 'oracle pl/sql cheatsheet', 15) FROM dual;
166returns "cheatsheet" since "c" is in the 15th position in the string and "t" is the last character in the string.
167SELECT substr('oracle pl/sql cheatsheet', -10, 5) FROM dual;
168returns "cheat" since "c" is the 10th character in the string, counting from the end of the string with "t" as position 1.
169Trim Edit
170These functions can be used to filter unwanted characters from strings. By default they remove spaces, but a character set can be specified for removal as well.
171
172trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed );
173trim (' removing spaces at both sides ');
174this returns "removing spaces at both sides"
175
176ltrim ( string-to-be-trimmed [, trimming-char-set ] );
177ltrim (' removing spaces at the left side ');
178this returns "removing spaces at the left side "
179
180rtrim ( string-to-be-trimmed [, trimming-char-set ] );
181rtrim (' removing spaces at the right side ');
182this returns " removing spaces at the right side"
183DDL SQL Edit
184
185Tables Edit
186Create table Edit
187The syntax to create a table is:
188
189CREATE TABLE [table name]
190 ( [column name] [datatype], ... );
191For example:
192
193CREATE TABLE employee
194 (id int, name varchar(20));
195Add column Edit
196The syntax to add a column is:
197
198ALTER TABLE [table name]
199 ADD ( [column name] [datatype], ... );
200For example:
201
202ALTER TABLE employee
203 ADD (id int)
204Modify column Edit
205The syntax to modify a column is:
206
207ALTER TABLE [table name]
208 MODIFY ( [column name] [new datatype] );
209ALTER table syntax and examples:
210
211For example:
212
213ALTER TABLE employee
214 MODIFY( sickHours s float );
215Drop column Edit
216The syntax to drop a column is:
217
218ALTER TABLE [table name]
219 DROP COLUMN [column name];
220For example:
221
222ALTER TABLE employee
223 DROP COLUMN vacationPay;
224Constraints Edit
225Constraint types and codes Edit
226Type Code Type Description Acts On Level
227C Check on a table Column
228O Read Only on a view Object
229P Primary Key Object
230R Referential AKA Foreign Key Column
231U Unique Key Column
232V Check Option on a view Object
233Displaying constraints Edit
234The following statement shows all constraints in the system:
235
236SELECT
237 table_name,
238 constraint_name,
239 constraint_type
240FROM user_constraints;
241Selecting referential constraints Edit
242The following statement shows all referential constraints (foreign keys) with both source and destination table/column couples:
243
244SELECT
245 c_list.CONSTRAINT_NAME as NAME,
246 c_src.TABLE_NAME as SRC_TABLE,
247 c_src.COLUMN_NAME as SRC_COLUMN,
248 c_dest.TABLE_NAME as DEST_TABLE,
249 c_dest.COLUMN_NAME as DEST_COLUMN
250FROM ALL_CONSTRAINTS c_list,
251 ALL_CONS_COLUMNS c_src,
252 ALL_CONS_COLUMNS c_dest
253WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
254 AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
255 AND c_list.CONSTRAINT_TYPE = 'R'
256GROUP BY c_list.CONSTRAINT_NAME,
257 c_src.TABLE_NAME,
258 c_src.COLUMN_NAME,
259 c_dest.TABLE_NAME,
260 c_dest.COLUMN_NAME;
261Setting constraints on a table Edit
262The syntax for creating a check constraint using a CREATE TABLE statement is:
263
264CREATE TABLE table_name
265(
266 column1 datatype null/not null,
267 column2 datatype null/not null,
268 ...
269 CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
270);
271For example:
272
273CREATE TABLE suppliers
274(
275 supplier_id numeric(4),
276 supplier_name varchar2(50),
277 CONSTRAINT check_supplier_id
278 CHECK (supplier_id BETWEEN 100 and 9999)
279);
280Unique Index on a table Edit
281The syntax for creating a unique constraint using a CREATE TABLE statement is:
282
283CREATE TABLE table_name
284(
285 column1 datatype null/not null,
286 column2 datatype null/not null,
287 ...
288 CONSTRAINT constraint_name UNIQUE (column1, column2, column_n)
289);
290For example:
291
292CREATE TABLE customer
293(
294 id integer not null,
295 name varchar2(20),
296 CONSTRAINT customer_id_constraint UNIQUE (id)
297);
298Adding unique constraints Edit
299The syntax for a unique constraint is:
300
301ALTER TABLE [table name]
302 ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX [index name];
303For example:
304
305ALTER TABLE employee
306 ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX ourcompanyIndx_tbs;
307Deleting constraints Edit
308The syntax for dropping (removing) a constraint is:[1]
309
310ALTER TABLE [table name]
311 DROP CONSTRAINT [constraint name];
312For example:
313
314ALTER TABLE employee
315 DROP CONSTRAINT uniqueEmployeeId;
316INDEXES Edit
317An index is a method that retrieves records with greater efficiency. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
318
319Create an index Edit
320The syntax for creating an index is:
321
322CREATE [UNIQUE] INDEX index_name
323 ON table_name (column1, column2, . column_n)
324 [ COMPUTE STATISTICS ];
325UNIQUE indicates that the combination of values in the indexed columns must be unique.
326
327COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose an optimal execution plan when the statements are executed.
328
329For example:
330
331CREATE INDEX customer_idx
332 ON customer (customer_name);
333In this example, an index has been created on the customer table called customer_idx. It consists of only of the customer_name field.
334
335The following creates an index with more than one field:
336
337CREATE INDEX customer_idx
338 ON supplier (customer_name, country);
339The following collects statistics upon creation of the index:
340
341CREATE INDEX customer_idx
342 ON supplier (customer_name, country)
343 COMPUTE STATISTICS;
344Create a function-based index Edit
345In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.
346
347The syntax that creates a function-based index is:
348
349CREATE [UNIQUE] INDEX index_name
350 ON table_name (function1, function2, . function_n)
351 [ COMPUTE STATISTICS ];
352For example:
353
354CREATE INDEX customer_idx
355 ON customer (UPPER(customer_name));
356An index, based on the uppercase evaluation of the customer_name field, has been created.
357
358To assure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(customer_name) does not evaluate to a NULL value. To ensure this, add UPPER(customer_name) IS NOT NULL to your WHERE clause as follows:
359
360SELECT customer_id, customer_name, UPPER(customer_name)
361FROM customer
362WHERE UPPER(customer_name) IS NOT NULL
363ORDER BY UPPER(customer_name);
364Rename an Index Edit
365The syntax for renaming an index is:
366
367ALTER INDEX index_name
368 RENAME TO new_index_name;
369For example:
370
371ALTER INDEX customer_id
372 RENAME TO new_customer_id;
373In this example, customer_id is renamed to new_customer_id.
374
375Collect statistics on an index Edit
376If you need to collect statistics on the index after it is first created or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics. You collect statistics so that oracle can use the indexes in an effective manner. This recalcultes the table size, number of rows, blocks, segments and update the dictionary tables so that oracle can use the data effectively while choosing the execution plan.
377
378The syntax for collecting statistics on an index is:
379
380ALTER INDEX index_name
381 REBUILD COMPUTE STATISTICS;
382For example:
383
384ALTER INDEX customer_idx
385 REBUILD COMPUTE STATISTICS;
386In this example, statistics are collected for the index called customer_idx.
387
388Drop an index Edit
389The syntax for dropping an index is:
390
391 DROP INDEX index_name;
392For example:
393
394 DROP INDEX customer_idx;
395In this example, the customer_idx is dropped.
396
397DBA Related
398
399PL/SQL
400
401References
402
403APEX
404
405References
406
407Last edited 1 year ago by an anonymous user
408Wikibooks
409
410Content is available under CC BY-SA 3.0 unless otherwise noted.
411PrivacyDesktop