Common SQL statements and functions

1. Length of the string
You can use the length function to evaluate the length of a string. Length returns a value. The value is equal to the number of characters in the parameter.
Example: Use the length Function
SQL> select last_name, length (last_name) from customer order by lastname;

2. Use the substr function to extract substrings from strings.
Syntax:
The syntax of the substr function is as follows:
Substr (string, string charcter, number of charcters)
The variables are defined as follows:
String is a character column or string expression.
String charcter is the starting position of the substring
Number of charcters is the number of returned characters C
Example: describes how to use the substr function to obtain the first four characters of the instructor's surname.
SQL> select last_name, substr (last_name, 1, 4) from instector order by last_name
Example: Use the length function in the substr function (take the last three characters)
5qt.> select last_name, substr (last_name, length (last_name)-2, 3) from instector order by last_name

3. Search for the mode in the string
Example: Use the like Operator
SQL> column description format A40 word_wrapped
SQL> Column Title format A35
SQL> select title, description from course where description like '% thory %' or description like '% theories % ';

4. replace part of the string
A common data manipulation task is to convert data from one mode to another in a specific column.
Assume that you want to change the course description in the Course table and replace the word seminar in the description with the word discussion. you can use the replace function provided by Oracle to replace the strings in a column.
Syntax:
The syntax of the replace function is as follows:
Replace (string, existion_string, [replacement_string])
The variables are defined as follows:
String is the character expression C
Existion_string is an existing string.
Replacement_string is an optional string to replace.
Example: Use the replace Function
Shows how to use replace to change the course name (title) in the Course table: first, use the query to display the current course name. In the update statement, use the replace function to change the Seminar to discussion, another query shows the effect of the update statement.
SQL> Update course set Title = Replace (title, 'minar ', "discussion ');
Note: If you do not specify the replacement string in the replace function, the original string in the column will be deleted.

5. Delete spaces of strings
If a string column contains leading or trailing spaces, you may return an error when querying the column based on a specified value.
Ltrim and rtrim.

6. lpad left filling function
Use lpad to left fill the string.
Syntax:
Lpad (string, N, pad_string );
The variables are defined as follows:
String is the direct value of the string to be left-filled or the input column of the character.
N is the length of the string returned by lpad.
Pad_string is the string left filled with string.
SQL> select lpad (my_col, 20) from test_trim;
The parameter of the lpad function is slightly reduced by using the length function:
SQL> select lpad (my_col, length (my_col) + 8, you say ') from test_trim;

7. Change the case sensitivity of the string.
Oracle provides three functions to change the case sensitivity of strings:
Initcap converts the first character of each word to uppercase.
Lower converts all characters to lowercase letters.
Upper converts all characters to write

8. Use the decode function (value Conversion Function) to convert a string
Syntax
The decode syntax is:
Decode (expression, value1, returned_value1,... valuen, returned_valuen, [default_returned_value]
The variables are defined as follows:
Dexpression is a legal Oracle expression.
Valuen possible values for this expression:
The return value of decode when returned_valuen is expression or valuen.
Default_returned_value is optional. It is the return value of decode when expression is not equal to any valuen.
SQL> select schedult_id, day, decode (day, 1, 'sun', 2, 'mon', 3, 'tue ', 4, 'wed', 5, 'thu', 6, 'fri', 7, 'sat ')
From schedult_type_details order by schedult_id, Day;

9. Convert the string to an ASCII Value
SQL> select last_name, scaii (last_name) from policuctor order by last_name;

10. Current date and time: sysdate

11. Convert the date to a string
Syntax:
The format of the t0_char function is as follows:
To_char (date_value, Format)
SQL> select last_name, first_name, to_char (hire_date, "month DD, yyyy ') h_date from employee order by hire_date;
Embedding the to_char function in the substr Function
SQL> select last_name, first_name, substr (to_char (hire_date, 'mon), 1, 1) the_fist_letter_of_the_month from employee;

12. Convert string to date
Syntax:
To_date (string_value, date_format );
SQL> select sysdate, to_date ('07-04-1976 ', 'Mm-DD-YYYY') from dual;

13. Date and Time
Use the time format in the to_char function:
SQL> select employee_id, (to_char (time, clocked_in, 'hh: mm: ss') time_clocked_in from time_clock;

14. Calculate the difference between the two dates
SQL> select sysdate + 7 from dual;

15. During insert/update operations, numbers are automatically converted to strings. Numeric fields can be converted to strings using to_char.

16. format the Value Field
Select to_char (additional_fees, '2013') from course;
Currency symbols:
Select to_char (additional_fees, '￥9,999.99') from course;
Scientific Notation:
Select to_char (additional_fees, '9. 9999eeee ') from course;

17. convert a string to a number.
To_number is the inverse operation of to_char.
Update security_price set last_qtr_eps = to_number ('$2.81', '$999.99 ');

18. Internal statistical functions
AVG (value): calculates the average value. Its parameters come from several rows that it uses.
Stddev (value): returns the standard deviation of several rows that it acts on.
Variance (value): returns the number of rows it uses as the parameter.

19. Rounding and intercepting Functions
Round (value, [scale]) round (101.8) = 102 round (123.37, 1) = 123.4
Trunc (value, [scale]) trunc (123.33) = 123 trunc (123.567, 2) = 123.56
Evaluate the maximum integer floor (value) floor (128.3) = 128 floor (129.8) = 129 except for the fractional part, floor is almost equivalent to the trunc function.
Minimum integer cell (value) cell (128.3) = 129 cell (129.8) = 130

20. Find the maximum or minimum value
Max and Min.

21. Replace null
Nvl (column, value ).
Select nvl (additional, 0) from course;

22. Internal Aggregate functions
Count function:
Find different rows: distinct
Group: Group by: Select department_id, count (*) from curse group by department_id;
Group by and having: Select department_id, count (*) from course group by department_id having count (*) = 4; -- which departments have opened four courses.
AVG and group by: Select department_id, AVG (additional_fees) from course group by department_id;

23. exists
Select last_name, fist_name from instructor I where exists (select * from Class C where I. instruetor_id = C. instructor_id );

24. SELECT statement set operations
Intersece)
Union (and): Select epicenter_latitude, epicenter_longtitude from unsd_event Union select location_lat, location_lon, richter_number from rind_event order by 1;
Minus (difference): Select last_name, first_name from policuctor minus select last_name, first_name from hoover_policuctor;

25. Create a table using a subquery
Create Table anthor as select * from instructor where 1 = 2;

26. Create a simple view
Create view stueent_no_personal
As
Select student_id, last_name, first_name from student;

27. PL/SQL Block Structure
Description
Execution part
Exception Handling Section
Example:
Declare
Max_records constant INT: = 100;
I INT: = 1;
Begin
For I in 1 .. max_records Loop
Insert into test_table (record_number, current_date) values (I, sysdate );
End loop;
Commit;
End;
/

28. Declare a variable with % Type
For example, to describe a variable used to store the name of a repair warehouse technician, you can use tech_name dept_esimate.techican % type;

29. Declare a variable with % rowtype
For example, a composite variable is used to store a row in the depot_estimate table. Depot_est_row depot_esimate % rowtype;
The depot_est_row element can be used in the following method: depot_est_row.techniciam: = 'yujj ';

30. Some common control structures
If statement:
If Mod (I, 5) = 0 then
Rec_number: = 5;
Elseif Mod (I, 7) = 0 then
Rec_number: = 7;
Else
Rec_number: = I;
End if;

Simple loop/exit statement:
Loop
Exit when ...;
Endloop;

While_loop statement:
While I <100 Loop
I: = I + 1;
End loop;

For_loop statement:
For I in 1 .. Max Loop
I: = I + 1;
Dbms_output.put_line ('J: '| to_char (j ));
End loop;

GOTO statement:
Goto more_processing;
...
<More_processing>
...
End;

Null statement:
If (mod (I, 10) = 0) then
I: = I + 1;
Else
NULL;
End if;

Assignment Statement:
In PL/SQL, use: = to assign values.

31. use SQL statements in PL/SQL
PL/SQL and select statements:
Set serveroutput on
Declare
Average_body_temp patient. body_temp.deg_f % type;
Begin
Dbms_output.enable;
Select AVG (body_temp_deg_f) into average_body_temp from patient;
Dbms_putput.put_line ('average body temp is deg. F: '| to_char (average_body_temp. '2017. 99 '));
End;

Application of sub-blocks:
An anonymous block that contains another sub-block
Declare
Max_ I constrant int := 100;
I INT: = 1;
Rec_number int;
Begin
For I in 1 .. max_ I Loop
If Mod (I, 5) = 0 then
Rec_number: = 5;
Else
Rec_number: = I;
End if;
Insert into test_table (record_number, current_date) values (record_number, sysdate );

-- Here is a sub block;
Declare
Max_j constrant INT: = 20;
J INT: = 1;
Begin
For J in 1 .. max_j loop;
Rec_number: = rec_number * J;
Insert into test_table (record_number, current_date) values (record_number, sysdate );
End loop;
End;
End loop;

Example of a process:
Declare
New_patient_id patient. patient_id % type;
High_fever constant real: = 42.0;

Procedure record_patient_temp_deg_c (patient_id varchar2, body_temp_deg_c real) is
Temp_deg_f real;
Begin
Temp_deg_f: = (9.0/5.0) * body_temp_deg_c + 32.0;
Insert into patient (patient_id, body_temp_deg_f) values (patient_id, tempdeg_f );
Commit;
End;

Begin
New_patient_id: = 'gg999999 ';
Record_patient_temp_deg_c (new_patient_id, high_fever );
End;
/
-- Variables declared in a process cannot be used in vitro.

Example of a function:
Function max_additional_fees (dept_id in varchar2)
Return varchar2 is
Additional_fees course. Additional. Fees % type;
Begin
...
Return something;
End;

Parameter types of processes and functions: in/out/In out

32. Call the dbms_output package: first enter SET serveroutput on;

33. The System View user_source stores processes, functions, and packages. It has the following four columns:
Name includes the name of a process, function, package, or package.
Type indicates whether the source code is a process, function package, or package.
Text contains a line in the source code
Line contains the number of lines in the source code of text.
Example: select line, text from user_source where name = 'drop _ class' order by line;

34. Select and storage functions:
An existing storage function can be used in the SELECT statement.
Create or replac function degf_add10 (deg_f in number)
Return number is
Deg_c number;
Begin
Deg_c: = deg_f + 10;
Return deg_c;
End degf_add10;

Select body_temp, degf_add10 (body_temp) from patient;

35. Additional PL/SQL data types
Boolean binary_integer, natural, positive % Type % rowtype PL/SQL table or array User-Defined records

36. Oracle pre-defined exceptions
Dup_val_on_index: This occurs when an SQL statement creates duplicate data in a column with a unique index.
Invalid_number: It occurs when an invalid number is specified in the SQL statement.
No_datte_found: When the SELECT statement does not return any rows.
Too_many_rows: In a PL/SQL environment, a SELECT statement is used to retrieve multiple rows of data. To retrieve any number of rows from a query, you can use a cursor to view the cursor as a window to query the returned results.
In PL/SQL, this exception occurs when a SELECT statement returns multiple data rows.
Value_error: In most cases, it is related to truncation and conversion errors. For example, assign a value of more than 5 to a varchar2 (5) field.

37. sqlcode and sqlerrm
Sqlcode contains the error status of the currently executed Oracle PL/SQL statement. If the SQL statement is correct, the sqlcode is 0;
Sqlerrm includes sqlcode-related error messages. If the SQL statement is successfully executed, sqlcode is 0 and sqlerrm is a string: ora_0000: Normal, successful completion.

38. Procedure for using a cursor
1. Declare: Specifies the cursor, which is a name and syntactically associated with the SELECT statement.
2. Open the cursor. Oracle RDBMS executes the cursor-related query and determines the row (activity set ).
3. extract data rows with a cursor: return the values of each row to the PL/SQL subroutine environment and return one row at a time.
4. Close the cursor to release related resources.

39. Description cursor
Cursor cursor_name
[(Parameter1 parameter1_datetype [: = default1],
..
[Parametern parametern_datetype [: = derfaultn])]
Is select_stmt;
 
40. Open the cursor
Open cursor_name;
Note: If the parameter type is not declared when you define a cursor, you must specify a variable or a direct value for the parameter when you open the cursor. For example, open tempcur (20, 30 );

41. Fetch rows from the cursor
Loop
Fetch tmpcur into field1, field2;
Exit when tmpcur % notfound;

Dbms_output.put_line (field1 );
End loop;

42. Close the cursor
Close tmpcur;

43. Retrieve the number of rows returned by the cursor
Dbms_output.put_line (tmpcur % rowcount );

44. The row number of the query result in the Oracle database is represented by the pseudo-column rownum (starting from 1 ).!!! You can use the database paging Function !!!
For example, select * from employee where rownum <10 returns the first 10 records. However, since rownum is assigned a value before sorting after the query, it is recommended that you write the following statement to query the 100th to 120 records sorted by birthday for the employee:
Select * from (
Select my_table. *, rownum as my_rownum from (Select name, birthday from employee order by birthday
) My_table where rownum <120
) Where my_rownum> = 100
For example, add the serial number to the multi-Table query results.
Select rownum as "serialno", B. "pname", A. "compname" from "fundcompaccount" A, "fundpersonaccount" B
Where a. "compaccount" = B. "compaccount ";

-- The above Code has been printed as 20040511
The first 20 data records selected by Oracle
Select * from "fundduty" where rownum <20;
21 to 40 data records selected by Oracle
Select * from "fundduty" where rownum <= 40
Minus
Select * from "fundduty" where rownum <= 21