PL/SQL step-by-step comprehensive learning tutorial-Oracle

Course 1 PL/SQLBasic query and sorting
Highlights of this course:
1. Write the SELECT statement for database query
2. perform mathematical operations
3. process null values
4. Use the alias ALIASES
5. Join Columns
6. Edit the buffer in SQL plus and modify SQL SCRIPTS
7. order by is used for sorting and output.
8. Use the WHERE field.
I. Write SQL commands:
Case Insensitive.
SQL statements use digital branches, which are called buffers in SQL PLUS.
End with; or.
You can also use RUN to execute the statement.
Ii. Example 1: SQL> SELECT dept_id, last_name, manager_id
2 FROM s_emp;
2: SQL> SELECT last_name, salary * 12, commission_pct
2 FROM s_emp;
For numeric or date fields, four arithmetic operations can be performed. The priority is the same as that of standard advanced languages.
SQL> SELECT last_name, salary, 12 * (salary + 100)
2 FROM s_emp;
Iii. Column alias ALIASES:
Especially useful in computing;
Followed by the column name, or added "AS" between the column name and alias ";
If the alias contains SPACE, special characters, or upper or lower case, use double quotation marks.
For example (for font reasons, remember that the Quotation marks are Double Quotation marks ("Quotation ):
SQL> SELECT last_name, salary,
2 12 * (salary + 100) "Annual Salary"
3 FROM s_emp;
Iv. Connection symbols: |
Connect different columns or connection strings
Make the result a meaningful phrase:
SQL> SELECT first_name | ''| last_name
2 | ',' | title "Employees"
3 FROM s_emp;
5. Manage NULL values:
SQL> SELECT last_name, title,
2 salary * NVL (commission_pct, 0)/100 COMM
3 FROM s_emp;
This function converts NULL to a meaningful value, which is equivalent to replacing NULL.
6. For more information about SQL plus, see <SQL PLUS simple and practical basics>
VII. order by operation:
Similar to other standard SQL92 databases, sorting is as follows:
SELECT expr
FROM table
[Order by {column, expr} [ASC | DESC];
From Oracle7 release 7.0.16, order by can be an alias.
In addition, sort by location:
SQL> SELECT last_name, salary * 12
2 FROM s_emp
3 order by 2;
This avoids writing a long expression.
In addition, multi-column sorting:
SQL> SELECT last name, dept_id, salary
2 FROM s_emp
3 order by dept_id, salary DESC;
8. Restrict the selection row:
SELECT expr
FROM table
[WHERE condition (s)]
[Order by expr];
Example 1:
SQL> SELECT first_name, last_name, start_date
2 FROM s_emp
3 WHERE start_date BETWEEN '09-may-91'
4 AND '17-jun-91 ';
Example 2:
SQL> SELECT last_name
2 FROM s_emp
3 WHERE last_name LIKE '_ a %'; // display all the last_name Network Management Network bitsCN.com with the second letter
Example 3:
If any column is NULL
SQL> SELECT id, name, credit_rating
2 FROM s_customer
3 WHERE sales_rep_id is null;
Priority:
Order Evaluated Operator
1 All comparison operators (=, <>,>, >=, <, <=, IN, LIKE, is null,)
2 AND
3 OR
Summary: Today we mainly learned how to perform the SELECT query operation. The specific Combined Query and subquery will be learned in the future class. At the same time, we hope you can explore more in your work and study, practice!
Course 2 PL/SQL query row Functions
Highlights of this course:
1. master various ROW functions available in PL/SQL
2. Basic concepts of using these functions
3. Use functions in SELECT statements
4. Use conversion functions
Note: In the following examples, punctuation marks are English halfwidth.
I. functions:
Perform data computing, modify independent data, process output of a set of records, display formats of different dates, and convert data types
Functions can be divided into: single function (ROW) and grouping Function
Note: It can be nested and can appear in SELECT, WHERE, and order.
Syntax: function_name (column | expression, [arg1, arg2,...])
Ii. Balanced Functions
1. LOWER-case LOWER conversion
2. UPPER
3. uppercase letters of INITCAP
4. CONCAT connection character, equivalent to |
5. SUBSTR (column | expression, m [, n])
6. LENGTH returns the LENGTH of the string.
7. NVL conversion Null Value
Among them, 1 and 2 are often used to sort out miscellaneous, that is, to eliminate the interference of mixed case values of insert values, such:
SQL> SELECT first_name, last_name
2 FROM s_emp
3 where upper (last_name) = 'pattern ';
FIRST_NAME LAST_NAME
Vikram Patel
Radha Patel
Iii. mathematical functions
1. ROUND
ROUND: ROUND (45.923, 2) = 45.92
ROUND (45.923, 0) = 46
ROUND (45.923,-1) = 50
2. TRUNC
Truncation Function
TRUNC (45.923, 2) = 45.92
54ne.com
TRUNC (45.923) = 45
TRUNC (45.923,-1) = 40
3. MOD Division
MOD (1, 1600,300)
Instance:
SQL> SELECT ROUND (45.923, 2), ROUND (45.923, 0 ),
2 ROUND (45.923,-1)
3 from sys. DUAL;
4. ORACLE date format and date functions:
1. The default format is DD-MON-YY.
2. SYSDATE is a function used to calculate the system time.
3. DUAL ['dju: el] is a pseudo table. Some people call it an empty table, but it is inaccurate.
SQL> SELECT SYSDATE
2 from sys. DUAL;
4. Arithmetic Operators used in dates
Example: SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS
2 FROM s_emp
3 WHERE dept_id = 43;
DATE + NUMBER = DATE
DATE-DATE = NUMBER OF DAYS
DATE + (NUMBER/24) = plus 1 hour
5. functions:
MONTHS_BETWEEN (date1, date2) month interval, which can be positive, negative, or decimal
ADD_MONTHS (date, n) plus N months. This is an integer, but it can be negative.
NEXT_DAY (date, 'Char ') for example: NEXT_DAY (restock_date, 'Friday'), from this day to the next FRIDAY.
ROUND (date [, 'fmt'])
TRUNC (date [, 'fmt'])
The following example is explained:
SQL> SELECT id, start_date,
2 MONTHS_BETWEEN (SYSDATE, start_date) TENURE,
3 ADD_MONTHS (start_date, 6) REVIEW
4 FROM s_emp
5 WHERE MONTHS_BETWEEN (SYSDATE, start_date) <48;
We can see: MONTHS_BETWEEN (SYSDATE, start_date) <48, indicating that employees who have not worked for less than one year now.
LAST_DAY (restock_date) returns the last day of the month
SQL> select round (sysdate, 'month') from dual
ROUND (SYSD
----------
Month 1-11-01
Round (sysdate, 'Year') = 01-1 month-02
The value after ROUND is the smallest conformity value greater than the base value. You can use the method of changing the system time to test. The 15-day Division is also a very impressive rounding, while the TRUNC is just the opposite, is the truncation of the existing date.
5. conversion functions:
1. TO_CHAR
Convert a number or date to CHAR
2. TO_NUMBER
Convert character to NUMBER
3. TO_DATE
Convert character to date
These functions are relatively simple, but more practices are required to look at complex instances.
SQL> SELECT ID, TO_CHAR (date_ordered, 'Mm/yy') ORDERED
2 FROM s_ord
3 WHERE sales_rep_id = 11;
During conversion, pay attention to the correct default format:
SELECT TO_DATE ('03-MAR-92 ') correct from dual; // CORRECT
SELECT TO_DATE ('20140901') correct from dual; // incorrect
SELECT TO_DATE ('20140901', 'mmddyy') ERRORR FROM DUAL
Output March 10
SELECT TO_DATE ('20140901', 'ddmmyy') ERRORR FROM DUAL
Output October 3
4. instance:
Select to_char (sysdate, 'fmddspth "of" month yyyy am ') todays from dual;
TODAYS