Oracle knowledge point Introduction

Common SQL * PLUS commands:
Conn system/rr: log on to the database
Disc: disconnect
Show user: displays the current user
Exit: exit the system.
Clear: clear screen
Start d: \ aa. SQL [arg1 arg2 arg3]: run a script and specify parameters. Replace the parameters in the script with & 1 & 2 $3, and replace the parameters in sequence.
Select & a from scott. emp where deptno = 20 and job = '& B'; $ a & B is an interactive parameter, and then enter the values of a and B.
SQL> define B = clerk;/* define the value of the Interaction variable in advance */
SQL> define a = ename;
SQL> select & a from scott. emp where deptno = 20 and job = '& B ';
SQL> UNDEFINE a;/* clear the interaction variable value */
Use of variable binding:
SQL> variable num number;/* define a binding variable */
SQL> execute: num = 7788;/* set the value of the bound variable */
SQL> select * from scott. emp where empno =: num;/* Bind Variable */
SQL> print num;/* output the value of the bound variable */
SQL> alter user hr identified by hrpassword account unlock;/* unlock hr user */
SQL> save e: \ orasql \ kc. SQL Note: if the file already exists, the original file will be replaced.
SQL> GET e: \ orasql \ kc. SQL transfers the file kc. SQL stored on the disk to the buffer zone.
SQL> START e: \ orasql \ kc. SQL run the command file on the disk.
Data Types in Oracle
1. nvarchar2 (n): the maximum value of n is 4000. n indicates the number of strings that can be stored. For example, nvarchar2 (50) fields can contain 50 characters, there are no spaces at the end of less than 50.
2. char (n): the maximum value of n is 2000. n indicates the number of strings that can be stored. For example, a char (50) field can store 50 characters, there are spaces at the backend of less than 50.
3. Number (p, s) Number type. p indicates the total Number of digits, and s indicates the Number of decimal places.
4. BOOLEAN: BOOLEAN type; optional values: TRUE, FALSE, and NULL. This type can only be applied to PL/SQL, but not to column type.
5. DATE: DATE type, the default DATE format in ORACLE is '07-October-82 ', we want to modify the default DATE format 000 (based on this session) to 'yyyy-MM-DD ', you can run the command: alter session set nls_date_format = 'yyyy-mm-dd ';
6. LOB data type (large unstructured files with a maximum storage size of 4 GB ):
1) BLOB: generally images and Images
2) CLOB: only large text files
3) BFILE: stores audio and video files.
7.% Type: Make the Type of a variable refer to other types, which are generally column types.
For example:
DECLARE
V_ename emp. ename % type; -- refer to the ename type of the emp table.
For example:
Declare
V_ename emp. ename % type; -- refer to the type of the column ename In the emp table.
V_sal emp. sal % type;
V_tax_rate constant number (0.03): =;
V_sal_tax v_sal % type; -- reference the v_sal type.
Begin
Select ename, sal into v_ename, v_sal from emp where empno = & eno; -- Interactive variable eno
V_sal_tax: = v_sal * v_tax_rate;
DBMS_OUTPUT.PUT_LINE ('employee name' | v_ename );
DBMS_OUTPUT.PUT_LINE ('wage '| v_sal );
DBMS_OUTPUT.PUT_LINE ('income tax '| v_sal_tax );
End;
8. rowtype, used to reference the record type of a table. This type of variable can store a record.
For example:
Declare
V_emp emp % ROWTYPE;
Begin
Select * into v_emp from emp where empno = & eno;
DBMS_OUTPUT.PUT_LINE ('employee name' | v_emp.ename );
DBMS_OUTPUT.PUT_LINE ('wage '| v_emp.sal );
DBMS_OUTPUT.PUT_LINE ('income tax '| v_emp.job );
End;
9. Table type (equivalent to an array ):
DECLARE
Type emp_table_type is table of emp. ename % type index by BINARY_INTEGER; -- declare an array type, refer to emp. ename
Emptable emp_table_type; -- declare an array
BEGIN
Select ename into emptable (-1) from emp where empno = & eno;
Emptable (0): = 'jack ';
Dbms_output.put_line ('employee name 1' | emptable (-1 ));
Dbms_output.put_line ('employee name 1' | emptable (0 ));
End;
 
ORACLE single row functions:
A character function that accepts character parameters, and outputs characters or numbers.
Lower (): converts lowercase letters
Upper (): converts uppercase letters
InitCap (): converts the first letter to the upper letter.
Contac ('A', 'B'): connection string, AB
Length ('abc'): obtains the Length of a string.
Instr ('ename', 'A') gets the location of a in ename, and 3 is returned.
Substr ('job', 1, 2): Truncated string: jo
TRIM ('S' FROM 'ssmith '): MITH (remove s from ssmith)
Numeric Functions
ROUND (45.926, 2) 45.93 intercepts rounding
TRUNC (45.926, 2) 45.92 truncated
MOD (1600,300) 100: Get the remainder
Date Functions
Select ename, (MONTHS_BETWEEN ('01-September-95 ', '01-February-95') m from emp; m is 7
ADD_MONTHS ('11-January-94 ', 6): 1994/07/11
LAST_DAY ('01-August 95 '): 1995/09/30
Date conversion:
Select to_char (hiredate, 'yyyy-MM-DD ') a from emp; convert the date to the specified format)
Basic PL/SQL
PL/SQL is an extension of ORACLE's standard database language. ORACLE has integrated PL/SQL into ORACLE servers and other tools, in recent years, more developers and DBAs have started to use PL/SQL. This article describes basic PL/SQL syntax, structures and components, and how to design and execute a PL/SQL program.
PL/SQL has been reliably integrated into ORACLE since version 6. Once you master the advantages of PL/SQL and its unique data management convenience, it is hard to imagine that ORACLE lacks PL/SQL. PL/SQL is not an independent product. It is a technology integrated into ORACLE servers and ORACLE tools. PL/SQL can be seen as an engine in ORACLE servers, the executor of the SQL statement processes a single SQL statement, and the PL/SQL engine processes the PL/SQL block. When the PL/SQL block is processed by the PL/SQL engine, the SQL statement executor on the ORACLE server processes the SQL statements in the pl/SQL block.
PL/SQL has the following advantages:
. PL/SQL is a high-performance transaction-based language that runs in any ORACLE environment and supports all data processing commands. Use PL/SQL program units to process SQL data definitions and data control elements.
. PL/SQL supports all SQL data types and all SQL functions, and all ORACLE Object Types
. PL/SQL blocks can be named and stored on the ORACLE server, and can also be called by other PL/SQL programs or SQL commands. Any client/server tool can access the PL/SQL program, it has good reusability.
. You can use ORACLE data tools to manage the security of PL/SQL programs stored on servers. Other database users can be authorized or revoked to access PL/SQL programs.
. PL/SQL code can be written in any ASCII text editor, so it is very convenient for any operating system that ORACLE can run.
. For SQL, ORACLE must process each SQL statement at the same time. In the network environment, this means that each independent call must be processed by the oracle server, this takes a lot of server time and causes network congestion. PL/SQL sends the entire statement block to the server, which reduces network congestion.
PL/SQL Block Structure
PL/SQL is a block-structured language. The unit of a PL/SQL program is a logical block. a pl/SQL program contains one or more logical blocks, each block can be divided into three parts (lifecycle, execution, and Exception Handling ). Like other languages, variables must be declared before they are used. PL/SQL provides an independent part dedicated to exception handling. The following describes the different parts of PL/SQL blocks:
The Declaration section contains the data types and initial values of variables and constants. This part starts with the keyword DECLARE. If you do not need to DECLARE variables or constants, you can ignore this part. It must be noted that the declaration of the cursor is also in this part.
Executable section)
The execution part is the instruction part in the PL/SQL block, starting with the keyword BEGIN. All executable statements are placed in this part, and other PL/SQL blocks can also be placed in this part.
Exception section is optional. You can handle exceptions or errors in this section. For more information about Exception handling, see the following section.
PL/SQL block syntax
[DECLARE]
--- Declaration statements
BEGIN
--- Executable statements
[EXCEPTION]
--- Exception statements
END
Each statement in the PL/SQL block must end with a semicolon. The SQL statement can contain multiple rows, but the semicolon indicates the end of the statement. A row can contain multiple SQL statements separated by semicolons. Each PL/SQL block starts from BEGIN or DECLARE and ends with END. Annotation is marked.
Process introduction in Oracle
The process in oracle is named fast and stored in the oracle database, which is equivalent to the stored procedure in SQL server.
The following is a process definition:
Create or replace procedure query_emp -- delete the duplicate name first, and then create
(
V_no in emp. empno % type, -- this parameter is assigned by an external real parameter (in)
V_name out emp. ename % type, -- this parameter is assigned internally (out)
V_sal out emp. sal % type -- this parameter is assigned internally (out)
)
Is
E_sal_error exception; -- an exception is declared.
Begin
Select ename, sal into v_name, v_sal from emp where empno = v_no;
If v_sal> 2500 then
Dbms_output.put_line ('employee payroll '| v_sal );
Raise e_sal_error;
Else
Null;
End if;
Exception
When no_data_found then
Dbms_output.put_line ('this employee does not exist ');
When e_sal_error then
Dbms_output.put_line ('the employee's salary is higher than 2500 ');
End query_emp;
Process call:
A. in SQL * plus:
SQL> variable a1 nvarchar2 (16 );
SQL> variable a2 number;
SQL> execute query_emp (7788,: a1,: a2 );
B. In other pl/SQL Blocks
Declare
V_al emp. ename % type;
V_a1 emp. sal % type;
Begin
Query_emp (v_ename => v_al, v_sal => v_a1, v_no => 5678 );
End;
The process we created is stored in the data dictionary. You can use the following statement to query the created blocks:
Select * from user_source where name = 'query _ emp ';
Grant the execution permission of the process to other users:
Grant execute procname to username;
Deletion process:
Drop procedure procname;
Introduction to functions in Oracle
Functions and procedures are also named blocks. Their main function is to return values.
For example:
Create or replace function get_salary_by_deptno
(
V_dept_no in emp. deptno % type, -- enter the Department number
V_emp_cnt out number -- number of output departments
)
Return number
Is
Result number; -- total wages to be returned
Begin
Select sum (sal), count (*) into Result, v_emp_cnt from emp where deptno = v_dept_no;
Return (Result );
End get_salary_by_deptno;
Calling a function in SQL and calling a function in PL/SQL are the same as calling a function, except that a function must be called in an expression:
Delete A Function
Drop function get_salary_by_deptno
Oracle cursor Introduction
To put it simply, Oracle cursors provide an independent processing method for each row of data in the query result set of multiple rows of data. They are commonly used programming interfaces for interactive applications.
A cursor is divided into a display cursor and an implicit cursor. An implicit cursor is a cursor provided by oracle, and a display cursor is a user-defined cursor.
Procedure:
1) Name cursor
2) Open the cursor
3) extract the cursor
4) Close the cursor
Several methods of using a cursor
1. General call Method
Declare
Cursor c1 (v_ss nvarchar2)-named parameter-based cursors
Is
Select * from stu where ss = v_ss; -- select statement of the cursor
V_stu c1 % rowtype; -- Based on the cursor-Defined variables, each row of data retrieved by the cursor can be stored.
Begin
Open c1 (224 );
Loop
Fetch c1 into v_stu;
Exit when c1 % notfound;
Dbms_output.put_line ('name' | v_stu.name | ''| 'ss' | v_stu.ss );
End loop;
Close c1;
End Cursor1;
Extended: (using a cursor in the process)
Create or replace procedure Cursor1
(
V_ss nvarchar2
)
Is
Cursor c1
Is
Select * from stu where ss = v_ss;
V_stu c1 % rowtype;
Begin
Open c1;
Loop
Fetch c1 into v_stu;
Exit when c1 % notfound;
Dbms_output.put_line ('name' | v_stu.name | ''| 'ss' | v_stu.ss );
End loop;
Close c1;
End Cursor1;
2. Use a cursor for loop to operate the cursor
Create or replace procedure Cursor2
(
V_ss nvarchar2
)
Is
Cursor c1
Is
Select * from stu where ss = v_ss;
Begin
For c1_rec in c1 -- c1_rec is the record variable that can store the cursor row.
Loop
Dbms_output.put_line ('name' | c1_rec.name | ''| 'ss' | c1_rec.ss );
End loop;
End Cursor2;
Use the cursor for loop to automatically enable and disable the cursor.
3. Use a cursor to update and delete data.
Create or replace procedure Cursor3
(
V_ss nvarchar2
)
Is
Cursor c1
Is
Select * from stu where ss = v_ss for update; -- add a for update
Begin
For c1_rec in c1
Loop
Update stu set name = 'nb' where current of c1; -- update row by row.
End loop;
End Cursor3;
Oracle triggers
A trigger is a special process. Unlike a common process, a process must be called explicitly before execution. A trigger is triggered by the oracle server when an event occurs.
Key points:
Trigger time: before and after, triggered before or after the action
Trigger event: insert update delete or three logical combinations
Condition predicate: inserting (true when an insert operation is triggered) updating [column x] (true when updating a specific column) deleting (true When deleting)
Syntax:
CREATE [or replace] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD
[INSERT] [[OR] UPDATE [OF column_list]
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old/NEW [AS] new}]
[For each row]-ROW-Level Trigger. EACH operation is triggered once.
[WHEN (condition)]
Pl/SQL _block;
For example:
Create or replace trigger test1
Before
Update on stud
Declare
Begin
DBMS_OUTPUT.PUT_LINE ('updated ');
If updating ('name') THEN
DBMS_OUTPUT.PUT_LINE ('updated name ');
End if;
End test1;
When the execution is: update stud set sex = 'male' where ID = 3; the output is "updated"
When update stud set NAME = 'test' where ID = 3; is executed, "updated" and "updated NAME" are output"
 
Another example is:
Create or replace trigger test1
Before
Update on stud
Declare
Begin
Raise_application_error (-20001, 'Do not allow Update'); -- this trigger is used to notify the user not to allow updates before the update.
End test1;
View triggers in a table in the data dictionary
Select * from user_triggers where table_name = 'stud ';
Disable a trigger
Alter trigger test1 disable
Activate a trigger
Alter trigger test1 enable
Disable all triggers on a table
Alter table dept (table Name) disable all triggers
Enable all triggers on a table
Alter table dept (table Name) enable all triggers
Re-compile the trigger when changing the table structure
Alter trigger test1 compile;
Delete trigger
Drop trigger test1;
Package:
Packages are similar to classes in java and used to encapsulate functions, processes, and variables in pl/SQL.
The package is divided into two parts:
1) Package Description: Public components of the package placed inside, such as variables, constants, and processes
Creation Syntax:
Create or replace package dd is
 
-- Author: RR
-- Created: 20:38:53
-- Purpose:
 
-- Public type declarations
Type <TypeName> is <Datatype>;
 
-- Public constant declarations
<ConstantName> constant <Datatype >:=< Value>;
 
-- Public variable declarations
<VariableName> <Datatype>;
 
-- Public function and procedure declarations
Function <FunctionName> (<Parameter> <Datatype>) return <Datatype>;
 
End dd;
2) package body
Place specific implementation code
Delete package
Drop package package_name