Oracle PL / SQL Program Manual (SQL Daquan) (Turn) Oracle PL / SQL Program Manual (SQL Daquan) 1, SQLPLUS 1 引
The following SQL command 17 as the beginning of the sentence keywords: alterdroprevoke auditgrantrollback * commit * insertselect commentlockupdate createnoauditvalidate deleterename These commands must be ";" ending
SQL is not the SQL * PLUS command These commands are not stored in the SQL buffer @ definepause # delquit $ describeremark / disconnectrun acceptdocumentsave append Edit et et 图 图 p p p p p p p p
--------- 2
Data Dictionary TAB all basic tables, views and synonyms created by users
DTAB all tables in the data dictionary
COL all list definitions of the base table created by users
Catalog
SELECT *
Describe command Describe the structural information of the base table DESCRIBE DEPT
SELECT *
Select mPNO, ENAME, JOB
Select DEPTNO
Logical operator = or <> not NOT NULL
Presence in and NOT 'clerk', 'analyst';
Select ename, Job
Predicates Between and NOT bBetWeen's salary between 2000 and 3000 select , job, SAL
Select [Job, SAL From] Petween
Predicate like, notlike selectename, deptnofromemp whereenamelike'S% '; (beginning with a letter S) selectename, deptnofromemp whereename Likephot (with W start, only three letters behind) select , Job
Predicate isnull, isnotnull no employees bonuses (ie commision is null) selectename, jobfromemp wherecommisnull; selectename, jobfromemp WHERE
Multi-conditional query select select = 20
Expression
Arithmetic expressions select bonus higher than 5% of their salary employees selectename, sal, comm, comm / salfromemp wherecomm> .05 * sal orderbycomm / saldesc ;
Date data add- 6-mar-87 87 6-Mar-87 2 / 24 = 6-Mar-87and2hrs add15secondsto6-Mar-87 6-Mar-87 15 / (24 * 60 * 60)
Alias select selex EMP where omm> .05 * SAL
Editing of the SQL command display the contents of the buffer List display the fourth line of the current SQL command, and put the fourth line as the current line, there is a * behind the line number *. changeorc original first time with the new content occurs in a row replacement content SQL> c / (...) / ( 'analyst') / inputori increase in one or more rows Del Del Delete the current line in the SQL buffer running the SQL buffer in the SQL buffer Edit writes commands in the SQL buffer to text files under the operating system, and call the editor provided by the operating system to perform modifications.
------------- 3
INSERT ?
Selected from other tables insert data insertintoemp (empno, ename, deptno) selectid, name, department fromold_emp wheredepartmentin (10,20,30,40);
Use parameters INSERT SQL / PLUS to each parameter prompt user input
When the parameter corresponds to date or character data, you can add quotation on the parameter. You can use it when entering the quotation. Insert
Insert Null Value (NULL) D Data Data Default Format: DD-MON-YY @Sert inEmp Empno, Ename, HIREDATE) VALUES (7963, 'Stone', '07-APR-87');
System time: sysdate insert
Data Update Update EMP set = 'manager'
Update = 'market =' sale
Update EMP = 40, Job = 'Market = "
Data Deletion delete EMP Where m = 765;
Updated submission commit
Automatic Submit Method SETOTOCOMMIT in in 设 设 开, use inesrt, update, and delete will be submitted immediately.
Update cancellation rollback
The operation between two consecutive successful Commit, called a transaction
--------------- 4 Creating a base table, view Creating a base table Create_Number (2), DNAMETCHAR (14), locome (13));
The data dictionary will automatically update. A base table is up to 254 columns.
Table Name Name Name Rules: The first character must be a letter, which can be arbitrarily (including ", but not a comma). Name must not exceed 30 characters.
The only user's base table name must be unique, can't be the reserved word of Oracle, the column names of the same base table are different.
Using Double Quotes If the table name is enclosed with a double quotes, the above rules can be not met; Only the use of double quotes can distinguish large, lower-write; Naming double quotes, in the future operation, two quotes must also be used.
Data Type: char (n) (not exceed 240 characters) number (n, d) date long (65,536 characters) raw (raw binary data) s
Null value processing Sometimes the required column value cannot be empty Create_Number (2)
Add a list in the base table altertable DEPT
Modifying the list attribute DEPT Note: The width of its column value can only be reduced when all values are empty. Only the type of column value can be changed when all values are empty. Only the list as NOT-NULL can only be defined when all values are not spaced. Example s: altertabledeptmodify (locchar (12)); altertabledeptmodifylocchar (12); altertabledeptmodify (dnamechar (13), LOC-medly (12)); create view Create_View , Job, Sal = 'manager';
It is a view of the column name alias createviewmydept (person, title, salary) asselectename, job, sal fromemp wheredeptno = 10;
WITHCHECKOPTION Options Using WITHCHECKOPTION to ensure that the data must meet the conditions specified in the select command in the view definition when the view is inserted or updated. Update DEPT20 occurs when doing the following SET DEPTNO = 30 Where [
Bask table, view copy Create_Table omeptedEmp2
Bask Table, View Delete DROPTABLE DROPVIEW Name Name
------------ 5SQL * PLUS reporting SQL * PLUS some basic formatting commands columndeptnoheadingdepartment
Column
Column Sal
Column $ 99,999.00
Ttitle Sample hitechcorp
Btitle onstrictly comfidential
Breakon DEPTNO
Compute_Sum DEPTNO
Run
Head and Tail Tail Ttitle Sample hitechcorp btitle ignition
"|" Indicates that the wrap is, the end does not have to be divided into the semicolon. There are three options: leftRight CENTER
With TTITLE, the system will automatically display the date and page number at the top of each page. TTITLET and BTITLE commands are valid until the header or tail is reset, or exit SQL * Plus.
The following command fails to make the title statement
column column names defined command is used to display the column names if the name is a word, do not add quotation marks columnenameheadingemployee
Column name'employee | name'Employee |
Cancelumn definition
Column format column
Column format $ 9,999.99
Columncomm
Control record display packet order DEPTNO (no repetition value)
SELECT DEPTNO, ENAME
Shown as 10clark niller 20smith scott 30allen blake
Each time there is only one break command, but can use Break commands on multiple columns in multiple columns.
Record group device DEPTNO, ENAME
Each DEPTNO is empty two rows onPage
Group calculation SKIP clause enabled the information between the department to separate the information
Other computing commands MAX MIN Number Number of lines
Make the compute command Once a compute is defined, it has been valid until it is closed (Clear compute)
SQL / PLUS Environment Command Show (Show Current Parameter Settings)
Show tyll (display all parameters)
Set parameters set
SETOCOMMIT
Set commands include set (automatic)
Set {OFF | ON} (Command File Execution, Whether to display the command itself on the terminal, OFF is default)
SET over: The query ends, the result, the number of records, the default;
SET
SetLinesize {n} The maximum number of characters shown in a row, the default is 80
SETPPAGESIZE {n} The number of rows per page is 14α
SET (ON: When the report exceeds one page, the display is paused, waiting for the user to return the vehicle button, then display; By default; text: Page and Pause, and prompt information to users)
Set [buffer set the command buffer, usually, the SQL command buffer has been the current buffer. Since the SQL command buffer can only store a SQL command, it is possible to store SQL commands and SQL * PLUS commands with other buffers. Regularly used settings can be placed in the Login.sql file.
SET LL
Select enename, COMM The commission of no commissions in department 30 is "NO DATA".
SET NULL is the SQL * Plus command, with it to identify null values, which can be set to any string.
Save command save SAVE
INPUT ^ ^ ^ ^ ^ ^ ^
Save
A research.sql file will be added in the directory.
Edit Command Edit EDIT
Edit Edit the content in the current buffer.
Edit a file edit
Turn into the command GET get the file content on the disk into the buffer and appear on the screen, and the file name does not need .sql.
START command Run the specified file Start Arsearch
Output Command SpoolTryFile not only allows the results of the query to display on the screen, but also deposit the result to the file
Stop output to file output spooloff
Put the results of the query in the printer, first store them in a file, then do not have to use spool oFf, and use: spoolout spoolOut Close this file and output on the default printer default printer
Example of Production Reports EDIT_TRYFILE
SETOTOCOMIT \ (Empno, Ename, Deptno) Values (3333, 'Samson', 20); spool spool spool spool spool spool spool OffitoCommit tyff
Execute this file with the start command
-------- 6. Function jack - jack
Length (ename); calculates the length of the string.
Substr (job, 1, 4);
Other
Date function add_mont (hiredate, 5) Month_between (Sysdate, HiRedate) Calculate the number of months between employment time and system time NEXT_DAY (HIREDATE, 'FRIDAY') Calculation The date of the first Friday after the employment date
Example selectename, sal, next_day (sysdate, 'FRIDAY') as_of fromemp wheredeptno = 20; (as_of is an alias) s
If you don't need to_char functions, the default format in Oracle is' DD_MON_YY' EMP aptno = 10;
TO_DATE (string, format)
INSERT in tEMP (Empno, ENAME, HIREDATE) VALUES (7999, 'asms', to_date ('070387083000', 'mmddyyhmiss'));
Date data format DD dy fri dayfriday DDSPTHTWELFTH
MM monymarms MONTHMARCH
YY 87 yyyyy 1987 1987
Example MAR 12, 1987 ddd, yyyy'mondd, yyyy' ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ 00 am .
Arithmetic function Least (V1, V2)
Select [Empno, Mgr, Least (Empno, Mgr)
Trunc (SAL, 0) Take the approximate value of SAL (truncation)
Null Value Functions NVL (V1, V2) V1 is column name, if V1 is not a null value, NVL returns its column value. V1 is null, return to V2 value.
Grouplay function select
Characteristic, date, digital grouped gauge function mAx-count can be used for any data type
SELECT MIN (ENAME)
SELECT
SELECT MIN (SAL)
How many people have work? SELECT (JOB)
How many different types are there? SELECT (Distinct No.Job)
Count Distinct Calculate the number of different values in a field
Other polygroup functions (only for digital data) Calculate average salary SELECTAVG (SAL)
STDDEV calculate the average difference of salary select "stddev (sal)
Calculating total salary - SELECT
Group
Packets in multiple conditions Employees of each department Select deptno, count (*)
Number of employees of each type of work per department SELECT DEPTNO, JOB, COUNT (*)
Meeting of conditions (where is for SELECT, HAVING is a for groupby) Which departments have more salary and more than 9000 select deptno, sum (sal) groupa DEPTNO HAVINGSUM (SAL)> 8000 or Sum (SAL);
--------- 7
Outline connection select dept.deptno, loc telep, dept = dept.deptno; If there is a value in Dept.Deptno, there is no in Emp.Deptno ( If deptno = 40), when the external connection is used, an null value is generated.
Self-join: Different lines of the same base table To do join, you can use self-join select in Worker.ename, Manager.ename, MANAGER Worker.mgr = manager.empno;
Nonequijoins s salary of employees which belongs to a third level selectename, sal fromemp, salgrade wheregrade = 3 andsalbetweenlosalandhisal; (base table salgrade: Grade
S connection set operation set operation s of the two rows or more query results into one union-setunion Rowsoffirstqueryplusofsecondquery, lessduplicaterows
INTERSECT-SET in tTERSECTION qcommon
Minus-SETDIFCERENCE u n TOTHE
Introduce several views account
SalesView ename
ResearchviewView ename
Union operations returns a row in a query result, which combines the records in the base table or view together. Which employees in all departments have the same amount of pay more than 2000. The data type of the corresponding column must be the same. Select [Aname , SAL , slag, Sal, Sal , Sal \ 2000, Sal, Sal, Sal , Sal
Intersect Operation Back to the same part of the query results s meaning
Minus operations returns part of the line records that are different from the second query result in the first query result. What kind of work is in the Terrorist, but not in the sales department? selectjobfromaccount minus selectjobfromsales; subquery slectename, deptno fromemp wheredeptno = (selectdeptno fromemp whereename = 'smith');
Multi-level query select select ename, Job, Sal = From EMP wE [enename = 'CLARK');
A plurality of base tables and subqueries selectename, job, sal fromemp, dept whereloc = 'newyork' andemp.deptno = dept.deptno andsal> (select SAL mapp = '
Poly group function in child query select slammed;
------------ CONNECT, registration, query
Only DBA has the right to create new users b
DBA or users can change the user password GRANTNECTTOTOTOSCOTT ^TiFied
Bask Table Permissions 1 There are two ways to obtain permission to the base watch operation.
Create your own base table get a license for the base Table to create a user. GRANT_Select, INSERT
These privileges are select insert oddate delete Ω
Give all permissions
Synonym SELECT * Scott.emp
Create synonym Create synonyms EMPLOYEEEEEEE employee
Base Table Permission 2 You can make other users such power, other users can grant your base table authority to others
Return rights System authority only by DBA
Basic Table Permissions
Revoke inSERTOERT
--------- 9. Establish index create
Delete index DROPINDEXEEMP_ENAME;
About Index Only for larger base tables (at least 50 records) To establish an index, insert data Establish any multiple indexes for a base table Generally, establish an index in the column of primary keys After establishing an index? , Do not affect the execution of the SQL command After establishing an index, Oracle automatically maintains and uses an index.
Ensure that the data uniqueness is improved while the execution can ensure that each column value in each record is not repetitive. create
-------- Practice and answer Does there are employees who have more salary than prize? If there is, arrange it according to the descending order of salary. If there are more than two employees pay the same, they are sorted by their name. Select
List the information about the employee name and bonus accounts for the percentage. Require display time columnnamism clearly, sorted by employee name, excluding an unknown employee. Select
The salary of all employees working in Chicago (department 30) rose 10%. Update decent
Update EMP SEL = 1.1 * SAL = (select deptno from DEPT );
For Hitech newly built a department, numbered 50, and other information are not known. Insert DEPT (DNAME, Deptno) VALUES ('Faclities', 50);
Create a view, three column name, which does not include employee information createviewemployee ( "employeename", "employeenumber", "employeejob") asselectename, empno, job 'clerk';
Produce wage reports, including employees name, employment time (calculated as weeks), wages and department numbers, a page shows information about a department, at the end of each page, showing the sum of the wages of the page and the sum of the time of employment time, At the end of the report, there is shown that all employees have salary and the sum of the employment time. ttitle'service' breakondeptnoonpageonreport computesumofsalondeptno computesumofsalonreport computesumof service_lengthondeptno computesumofservice_lengthonreport columnsalformat $ 99,999.00 columnservice_lengthformat9999 selectdeptno, enameemployee, (sysdate-hiredate) / 7 SALVICE_LENGTH, SAL
Produce reports, including employees name, total income and commission, Alphabetical order. "Hirepha -date" format "EMPLOYEE", "EMPLOYEE", (SAL NVL (COMM) , 0) "Compensation", "hiredate" "hirephar Date" Lists more than 7 surrounding countries Name and area. SELECT
List all area is more than the country name and population of the island country equivalent to Japan. selectnation, population fromnation, border wherecode = nation_code ( ) andnation_codeisnull andarea> = (selectarea fromnation whereupper (nation ) = 'Japan');
List all the borders in other countries, and show its border country name. breakonnation selectnation1.nation, nation2.nationborderin_country fromnationnation1, border, nationnation2 wherenation1.code = border.nation_code andborder.border_code = nation2. Code
----------- ----------- PL / SQL
2 block structure and data type of PL / SQL
Characteristics of block structure Nested begin ... end;
Identifier: No more than 30 characters The first character must be letters Arranged, and differentiate in the form of case No sql reservation word
Data type Digital: integer, real, and index
String: Use single quotes to indicate single quotes in the string, use two single quotes to zero (there is no character between the two single quotes), then null
Character: The length of the length is 1 string
Data Definition NOT [NOT] NULL> [: = PL / SQL Expression>; ': =' Indicates to the variable assignment
Data types include numeric s number (7,2) character char (120) date date type Boolean boolean (value of true, false, or null, not stored in the database) s
Date Type anniversarydate: = '05-JUL-95'; project_completiondate; Boolean over_budgetbooleannotnull: = false; availableboolean; (initial value NULL)
Type% type matching books_printednumber (6); books_soldbook_printed% type; manager_nameemp.ename% type;
Variable assignment Variable name: = PL / SQL expression Numvar: = 5; Boolvar: = true; Datevar: = '11-jun-87';
Null
Variable Scope The identifier is valid in the declaration of its block. Effective block
Example declare e_messchar (80); begin / * subblock 1 * / declare v1number (4); begin selectempnointov1fromemp wherejob = 'president'; exception whentoo_many_rowsthen insertintojob_errors values ( 'morethanonepresident'); end; / * subblocks 2 * / declare v1number ( 4); begin selectempnointov1fromemp wherejob = 'manager'; exception whentoo_many_rowsthen insertintojob_errors values ( 'morethanone Manager '); End; Exception
--------- 3 SQL and PL / SQL
Insert declare my_salnumber (7,2): = 3040.55; my_enamechar (25): = 'wanda'; my_hiredatedate: = '08-SEP-88'; begin insertinto EMP (EMPNO, ENMAE, Job, HiRedate, Sal, Deptno) Values (2741, My_ENAME, 'CABDRIVER', MY_HIREDATE, MY_SAL, 20); END;
Delete declare
Transaction processing (save current point) [Work> (Return to Current Save Point) Cancel all the modifications of the database after the savePoint command Keyword work and savepoint are optional, no impact on the command execution
Function All functions of the SQL command can be used in the PL / SQL block
For non-SQL commands, you can use most individual functions x: = sqrt (y); LastName: = Upper (lastname); Age_diff: = Months_between Birthday1, birthday2) / 12;
Assignment data type conversion 4 Species assignment form: s variable name: = expression insertinto yl table values (expression 1, expression 2, ...); update yl table s SET 列 list = expression; select
The data type can be converted to: CHAR to Number ?
Example CHAR_VAR: = nm_var; Digital conversion DATE_VAR: = '25-DEC-88'; character type conversion to date type ; error, unable to successfully convert data types
--------- 4 controlled conditions in Example declare num_jobsnumber (4) ; begin selectcount (*) intonum_jobsfromauditions whereactorid = && actor_id andcalled_back = 'yes'; ifnum_jobs> 100then updateactorsetactor_rating =' wordclass' whereactorid = && actor_id; elsifnum_job = 75then updateactor setactor_rating = 'daytimesoaps' whereactorid = && actor_id; else updateactorsetactor_rating =' waiter' whereactorid = && actor_id; endif; endif; commit; End;
-------- 5 cycle s syntax loop ...... endloop; exit; (exit loop) exit [when>; (exit the loop, when satisfied WHEN ) s Example 1 declare ctrnumber (3): = 0; begin loop insertintotable1values ( 'tastesgreat'); insertintotable2values ( 'lessfilling '); ctr: = CTR 1; EXIT Variable
declare my_indexchar (20): = 'fettucinialfredo'; bowlchar (20); begin formy_indexinreverse21..30loop insertintotemp (coll) e; Integer, can not be variables or expressions
---------- 6 Explicit cursor
Open the cursor ?
Cursor properties % notfound% found% rowcount% isopen embodiment fetchmy_curintomy_var; whilemy_cur% foundloop (data processing) fetchmy_curintomy_var; exit MY_CUR % rowcount = 10; ENDLOOP;
% NOTFOUND attribute fetch operation does not return a record, then take a value to the true FETCH operation Returns a record, the value is false no fetch operation, value null <游 标 name> Note: If there is no Fetch action,
Close Cursor CLOSE
Cursor's for cycle The index is based on the value of each record. The record name does not have to declare that each value corresponds to the record name, the column name Initialization tour finger open the cursor
Implicit cursor Call format is SQL% Storage about the latest SQL command Processing information
Implicit cursor's attribute sql% notfound SQL% NOTFOUND SQL% Found SQL% ROWCOUNT: Implicit cursor included the number of records: delete IF @ The SQL command is executed, and the PL / SQL immediately turns off the implicit cursor.
---------
Correct use = x 1 f > B_Then = B C; goto
Musical use goto jail;
Numeral: Solution Function Fuzzy Number Use to define variables of the column value DEPTNO ( If the label and label limit, this command will modify each record.
---------- 8 abnormal handling abnormal situations predefined ORACLE any errors will automatically generate an exception information some unusual circumstances named, such as: no_data_found when the SELECT statement returns no records too_many_rows generated when the cursor is not defined, without generating whenevernotfound return records corresponding to a plurality of records s SELECT statements
User-defined anomalies are obtained by the user Declare part definition:
EXCEPTION_INIT statement Allow Oracle error to name
Call format: pragmaexception_init (
RAISE statement Use the raise command separately to get the current exception (like the abnormal situation is repeated). This statement can only be used separately in an exception handling.
Anomaly Handling Identifier A set of statements for processing exceptions: THEN - the last handle "Group of statements> END; End the PL / SQL block part, but also end the abnormal processing section
-------- exercises and answers 1: receiving contract_no and item_no value, look in the inventory table, if the product: has shipped, assigned to seven days after today in arrival_date in order, The value is assigned to the day after arrival, and there is no delivery in Arrival_Date, it is two months after the arrival of today, and a new order record is added to the Order table. The column value of Product_Status is' shipped 'and' Ordered'
Inventory:
Contract_Item: arrival_date Date
order: order_idnumber (6) product_idnumber (6) qtynumber (3)
The answer: declare i_product_idinventory.product_id% type; i_product_descriptioninventory.product_description% type; i_product_statusinventory.product_status% type; i_std_shipping_qtyinventory.std_shipping_qty% type;
begin selectproduct_id, product_description, product_status, std_shipping_qty intoi_product_id, i_product_description, i_product_status, i_std_shipping_qty frominventory whereproduct_id = ( selectproduct_id fromcontract_item wherecontract_no = && contractnoanditem_no = && itemno); ifi_product_status = 'shipped'then updatecontract_item setarrival_date sysdate 7 whereitem_no = && itemnoandcontract_no = && contractno =; elsifi_product_status =' ordered'then updatecontract_item setarrival_date = add_months (sysdate, 1) whereitem_no = && itemnoandcontract_no = && contractno; else updatecontract_item setarrival_date = add_months (sysdate, 2) whereitem_no = && itemno Contract_no = && contractno; INSERT
2: 1. Find all the employees in the designated department 2. Tip with '&' to prompt the user input department number 3. Put the employee name and salary into the prthttable table, the base structure is: CreateTable (SeqNumber (7), Linephar (80)); 4. Abnormal situation, the employee information of the bonus is not empty value can be deposited in the prNtTable table. answer: declare cursoremp_curis selectename, sal, comm fromempwheredeptno = & dno; emp_recemp_cur% rowtype; null_commissionexception; begin openemp_cur; FetchEmp_Cur fetchemp_curintoemp_rec; endloop; closeemp_sur; exception whennull_commissionthen openemp_cur; fetchemp_curintoemp_rec; while (emp_cur% found) loop ifemp_rec.commisnotnullthen insertintotempvalues (emp_rec.sal, emp_rec.ename); endif; fetchemp_curintoemp_rec; end Loop; Close
Java Research organization
-------------------------------------------------- ------------------------------
Re: Oracle Database Objects and User Management (Transfer)
Author: UB Time: 2003-08-14 21:06:59 [modify] [Reply] [Delete]
Oracle Database Objects and User Management
First, the management and maintenance of the mode object of the Oracle database
The main content of this section is the management and maintenance of the mode objects of the Oracle database, including: tablespace, table, view, index, sequence, synonym, aggregation, and integrity constraints. For each mode object, the definition is described, which describes its function, and finally how to manage them in the SQL language-based instance.
1.1 table space
Since the tablespace is a logical space containing these mode objects, it is necessary to maintain it first.
Create a table space sql> createphablespace jxzy
> Datafile@ / usr / oracle / dbs / jxzy.dbf '
> Online;
Modify table space sql> alterphablespace jxzy tyffline
SQL> altertablespace jxzy
> Rename Datafile@ / usr / oracle / dbs / jxzy.dbf '
> TO ^ / USR / Oracle / DBS / JXZYNEW.DBF '
> Online
SQL> CreatephableSpace online
Delete table space SQL> DROPTABLESPACE xjxzy
> Incruding Contents
1. 图 2 maintenance
The table is the basic unit of the data store in the database, and a table contains several columns, each column name, type, length, etc. The establishment of the table SQL> Create_Table [JXZy.Switch (
> Office_num
> Switch_code Number (8, 0)
> Switch_name
Movement of Table SQL> AlterphableTable Jxzy.Switch
> Add (desc varchar2 (30));
Delete of Table SQL> Droptable JXZy.Switch
> Cascade constraints
// Remove the integrity constraints of other tables that reference the table
1. 3 视 maintenance
The view is a collection of data generated by one or several base meters, but the view does not account for storage. Create a view to protect data security (only some of the ranks that you can see), simplify the independence of the query operation and protect the data.
View of the view SQL> Create_Well_View ω
> (Selectpole_path_num asasPath,
Pole asDevice_num FromPole
> Union
> Selectpipe_path_num ωpath,
> dervice_num from well
View replacement SQL> Replace_Well_View ω
> (Selectpole_path_num asasPath,
Pole asSupport_Device frompole ics> Union
> Selectpipe_path_num ωpath,
Well asSupport_Device from well);
1.4 sequence maintenance
The sequence is the unique integer generated by the sequence generator.
Establishment of sequence SQL> CREATE SEQUENCE JXZY.SEQUENCE_CABLE
> Start_with
> Increment 1
> NO_MAXVALUE;
Create a sequence, jxzy.sequence_cable.currval returns the current value, jxzy.sequence_cable.nextval return the current value after 1
Sequence Modification SQL> Alter SEquence_Cable
> Start // The starting point cannot be modified, if you modify, delete first, then redefine
> Incote 2
> MaxValue1000;
Sequence deletion SQL> DROP SEQUENCE JXZY.SEQUENCE_CABLE
1. 5 5 号 Index Maintenance
The index is a structure associated with the table, which is established to improve the retrieval speed of the data. Therefore, in order to increase the index speed on the table, one or more indexes can be created on the table, and an index can be built on one or more columns.
For the query type, establish multiple indexes, greatly improve the query speed, the updated table, if the index is too large, will increase the overhead.
Index unique index and non-only index
Establishment of Index SQL> CREATE create
> ON SWITCH (Switch_name)
> TABLESPACE JXZY;
Modification of the index sql> alter
> ON SWITCH (Office_num, Switch_name)
> TABLESPACE jxzy; Deletion of Index SQL> DROP
1. 6 完 约 约束
The integrity of database data refers to the correctness and compatibility of data. Data Type Check Prevents data in the database from doing semantics.
Integrity constraints are defined a set of rules description methods for columns. Oracle provides the following integrity constraints.
A. Null
B.
C.Primaty Key Key a table can only have one, non-empty
D.ForeiGa key
E.CHECK Each line of the table must be true or unknown (for null)
E.g:
A column definition non-empty constraint SQL> altertable / office_organization
> Modify (Descvarchar2 (20)
> Constraint nn_desc NOT otNULL)
Some column defined a unique keyword SQL> alterphable
> Modify (office_name vatchar2 (20)
> Constraint qQ_officename @Unique)
Define primary key constraints, primary key requirements are not empty SQL> Create_Table [Switch_code Number (8)
> Constraint pk_switchcode primary KEY,)
Make the primary key constraints sql> altertable switch
Definition outside the key SQL> Create_Tablepole (pole_code Number (8),
> Office_num Number (3)
> Constraint Fk_officenum
> References office_organization (office_num)
> ONELETE cascade;
Definition check SQL> CREATETABLE fffice_organization
> Office_num Number (3),
> Constraint CHECK_OFFICENUM
> Check (office_num between → 199);
Second, Oracle Database User and Permission Management
Oracle is a multi-user system that allows many users to share system resources. In order to ensure the security of the database system, the database management system is configured with a good security mechanism.
2.
Establishing a system-level security guarantee System-level privilege is achieved by granted the rights of user system levels, and the system-level rights (system privileges) include: establishing a table space, establishing users, modifying users, and deleting users. System privileges can grant users or recycled at any time. There are more than 80 privileges privileged in Oracle system.
Establishing an object-level security guarantee Object-level privilege is achieved by granting the user's right to operate (query, increasing, deleting), etc. in the database.
Establish user-level security guarantee User-level security guarantee through user passwords and role mechanisms (a set of rights). The purpose of introducing role mechanism is to simplify authorization and management of users. The practice is to packet the user according to its function, establish a role for each user, and assign the role to the user, and the same role has the same privilege.
2.2 User Management
Oracle user management content mainly includes user establishment, modification, and deletion
User's establishment SQL> CREATE jxzy
> Identified @jxzy_password
> DefaultTablesPace 日System
> Quata 5m
User's modification sql> create jxzy
> Identified @ @ @ oddy_pw
> Quata 10m
Delete users and their builds SQL> DROPUSER JXZYCASCADE;
2.3 System privilege management and control
Oracle provides more than 80 system privileges, each of which allows users to perform one or a type of database operation.
Grant system privilege sql> grant ccreate gT
> TO JXZY_NEW
ω;
Recycling System Privilege SQL> Revoke create
> From JXZY_NEW
/ / Without cascading
Display system privileges (system-level privileges for a user) sql> select * from .dba_sys_privs
2.4 Object Privilege Management and Control
Oracle object privileges refers to the right to conduct special operations on the specified table. These special operations include increasing, deleting, changing, viewing, executing (stored procedures), reference (other table fields as foreign bonds), index, and more.
Grant object privilege sql> grant ellect, insert (office_num, office_name),
> Update (DESC) ON / Office_organization
> To @New_AdminIDTrator
> With
// Cascade authorization
SQL> grant
> TO nw_administrator
Recycling object privilege SQL> Revoke oddate
> From nw_administrator
// Claced recipient function
SQL> Revoke ImageAll
> From nw_administrator
Display all object privileges have been granted SQL> SELECT * FROMS.DBA_TAB_PRIVS
2.5 Role Management
Oracle's role is named related privilege group (including system privileges and object privileges), Oracle uses it to simplify privilege management, grant it to users or other roles.
The Oracle database system pre-defines Connect, Resource, DBA, Connect has privileges for creating tables, views, sequences; resource has privileges, triggers, tables, sequences, etc., DBA has all system privileges; Exp_Full_Database,
You can understand the rights owned for each role by query sys.dba_sys_privs.
Grant user role sql> grant TO
> With
