Oracle PL / SQL Program Manual (SQL Daquan) (transfer)
A, SQLPLUS 1 Introduction SQL command as the beginning of the following 17 statements keywords: alterdroprevoke auditgrantrollback * commit * insertselect commentlockupdate create
SQL not in SQL * PLUS command These commands are not stored in the SQL buffer @ definepause # delquit $ describeremark / disconnectrun acceptdocumentsave append editset breakexitshow btitlegetspool changehelpsqlplus clearhoststart columninputtiming computelistttitle connectnewpageundefine copy - -------- 2 all columns in the base table created by users CATALOG SELECT select SELECT empno, ename, job fromemp; select * fromdept orderbydeptnodesc; logical operator = = or <> > > = < <= ! et slex job fromemp wherejobin ( 'clerk', 'analyst'); selectename, job fromemp wherejobnotin ( 'clerk', 'analyst') , salfromemp wheresalnotbetween2000and3000; predicate like, not like selectename, deptnofromemp whereenamelike'S% '; (beginning with a letter S) selectename, deptnofromemp whereenamelike'% K '; (ending K) selectename, deptnofromemp whereenamelike'W ___'; (beginning with W, only three letter later) selectename, jobfrom EMP aales% '; (which employee's work name is not starting with Sales) nullnull, ISNOT [NULL) No bonus (NULL) selectename, jobfromemp wherecommisnull; selectename, jobfromemp wherecommisnotnull; multi-criteria query selectename, job From Select 5% of the bonus than its salary? Select SELECT [ , Sal, Comm, Comm / Sal From Offem WHERENCOMM>
.05 * SAL Mar-87 Add ? 6-Mar-87 6-Mar-87 15 / (24 * 60 * 60) = alias 6-Mar 87and15secs-column names selectenameemployeefrom emp wheredeptno = 10; (alias: employee) selectename, sal, comm, comm / sal "C / SRATIO" fromemp wherecomm> .05 * sal order display the current SQL command, and put the fourth line as the current line, There is a * behind this 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 @sert dept (dname, deptno) values ( 'accounting', 10); selected insert data from other tables insertintoemp (empno, ename, deptno) selectid, name, department FromOld_emp SQL / PLUS will be for each parameter s when the user is prompted parameters or character data corresponding to the date, the parameter may be added to the quote, without quotation marks insertintodept values can be input (& deptno, '& dname', '& loc'); inserted Null (NULL) Download Data Default Format: DD-MON-YY INSERT EMP (Empno , ename, hiredate) values (7963, 'stone', '07-APR-87'); system time: SYSDATE insertintoemp (empno, ename, hiredate) values (7600, 'kohn' , SYSDATE); data update updateemp setjob = 'manager' whereename =' martin '; updateemp setjob =' marketrep' whereename = 'salesman' ; Update EMP = 40, Job = 'market r j =' salesman '; Data Deletion DELETE Update submission Submit Method @ inesRT, Update, Delete will be submitted immediately. Update cancellation creating a base table DEPTNONUMBER (2), DName JaChar (14), LOCTCHAR (13)); Data Dictionary automatically update. A base table is up to 254 columns. Name name name naming rules: The first character must be a letter, which can be arbitrarily (including ". 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. Use Double Quotes If the table name is enclosed in double quotes, the above rules can be not met; Only the use of double quotes can distinguish large, lower-write;
data type: CHAR (N) Number (N, D) null value processing Sometimes the value of the value Cannot be empty Create_Number (2) Add a list in the base Table ALTERTABLE dept add (headcntnumber (3)); modify an existing column attributes altertabledept modifydnamechar (20); Note: only when all the values in a column is empty, Can reduce its column value width. 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), locchar (12)); create a view createviewmanagersas selectename, job, sal fromemp wherejob = 'manager'; alias names for the view columns createviewmydept (person, title, salary) asselectename, job, sal fromemp wheredeptno = 10; withcheckoption s options use withcheckoption, To ensure that the view is inserted or updated, the data must meet the conditions specified in the select command in the view definition. Update DEPT20 occurs when doing the following setdeptno = 30 whereename = 'ward'; s copy s base table, view createtableemp2 asselect * fromemp; base table, delete the view droptable DROPVIEW ------------ SQL * Plus report function SQL * PLUS Some basic format command DEPTNO DEPTNO enameheadingname columnsalheadingsalary columnsalformat $ 99,999.00 ttitlesamplereportfor | hitechcorp btitlestrictlyconfidential breakondeptno computesum ofsalondeptno run headers and footers ttitlesamplereportfor | hitechcorp btitlerightstrictlyconfidential "|" represents a new line, the end does not have to add a semicolon options There are three types: left center using 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.
title the following command to make the statement invalid TTITLEOFF BTITLEOFF column name column defined command is used to display the column names if the name is a word, do not add quotation marks columnenameheadingemployee columnename heading'employee | name' (| linefeed) columnenameformatA15 columnsalformat cancel column format defined columnenameclear column $ 9,999.99 columncommlike SAL \ Like clause makes a column format refers to another column format, it copies the column name and its format selex DEPTNO, ENAME SELECT DEPTNO, ENAME emp orderbydeptno; (ORDERBY clause for controlling BREAK) s shown as 10clark niller 20smith scott 30allen blake only one command from BREAK Role, but once can be used on multiple columns deptno deptno SELECT DEPTNO, ENAME orderbydeptno; two blank spaces clearbreak (canceled BREAK command) between each deptno breakonpage (each time starting a new page) breakonreport (each from a new report start) breakonpageonreport (joint use) breakondeptnoskip2 computesumofsalondeptno grouping calculate calculate each department The sum of salary is separated by the information between the SKIP clause to separate the information between the department MAX MINM MIN STD Once a compute is defined, it has been valid until it shuts down Compute (Clear ) SQL / PLUS environment command show option s (display current parameter settings case) showall (All values are displayed) s s setting parameter values or options set setautocommiton SET switch command comprises setautocommit {off | on | immediate} (automatic submission, OFF default) setecho {off | on} (command file execution, whether the command itself, OFF default in the terminal) setfeedback {off | on} (ON: the end of the query, the results are given, the number of information recording, by default; OFF: no query result, the number of information recording) setheading {off | on} (ON: column header Displayed on the report, default; OFF: Not displayed on the report) The maximum number of characters displayed in a row,
The default is 80.PageSize {n} The number of rows per page, the default is 14 {OFF | ON | text} (ON: When the report exceeds one page, each display screen Suspend the display, wait for the user to return the vehicle button, then display; ? 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. The commission of no commissions in department 30 is "NO DATA". set Null is the SQL * Plus command, with it to identify null values (NULL), which can be set to any string. Save command SAVE save file name input 1selectempno, ename, job 2fromemp 3wherejob = 'analyst' saveresearch directory will add a research.sql file. Edit Command EDIT EDIT Edit the content in the current buffer. Edit a file get GET ASEARCH to transfer the file content on the disk into the buffer, and display it on the screen, the file name does not have to add .sql. START command Run the specified file START not only display the query results, but also save the result to file SPOOLFF The result is output on the printer, first deposit them in a file, then do not have to use spool FF, and use: spoolout spoolOut Close this file and outputs the production report on the system default printer \ intoemp (empno, ename, deptno) values (3333, 'samson', 20); spoolnew_emp select * fromemp wheredeptno = 20 ordeptnoisnull spool ------ 6 One letter is changed to uppercase. Jack Smith - jack
substr (job, 1,4); lower upper least other strings taken alphabetical list at the top of a string greatest removed alphabetical list of strings in the last row of a Skewers Date Function Add_MONTH (HIREDATE, 5) Month_between (Sysdate, HiRedate) Calculate the number of months between employment time and system time NEXT_DAY (HIREDATE, 'Friday') NEXT_DAY (HIREDATE, 'FRIDAY') Date of the first Friday after calculating the date of employment SELECT [SESDATE, 'Friday') to_char not function, default date format in ORACLE is' DD_MON_YY' to_char (date, datepicture) selectename, to_char (hiredate, 'DyMondd, yyyy') hired fromemp Date data format DD dy DY DDSPTHTWELFTH MM03 MON MONTHMARCH YY 'Mondd, yyyy' MAR12,1987'MONdd, yyyy' ThursdayMARCH12'DayMONTHdd' Mar1211: 00 am'Monddhh : miam' Thu, thetwelfth'Dy, "the" ddspth' arithmetic function least (v1, v2) selectename, empno, mgr, least (empno, mgr) lownum fromemp WHERE- NG value function NVL (V1, V2) 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.
Poly group functions minmaxcount be used for any type of data selectmin (ename) fromemp; selectmin (hiredate) fromemp; selectmin (sal) fromemp; number Is there a job? SELECT (JOB) How many different types of work? Select other agglomerations (Digital Data) Calculate average wage SELECT_EMP; STDDEV calculation of the average difference of salary Group Employee of each department SELECT DEPTNO, Count (COUNT) SELECT DEPTNO, COUNT (SAL) * Employees of each department of each department Select DEPTNO, Job, count (*) Conditional group (where is for SELECT, Having is a for groupby) Which departments have more salary and more than 9000 select deptno, sum (sal) Sum (SAL)> 9000; SELECT small junction Remove staff, which department's total salary is more than 8000 SELECT DEPTNO, SUM (SAL) group = 'clerk' groupby havingsum (sal)> 8000 orderbysum (sal); --------- 7 advanced query equivalent join selectempno, ename, job, emp.deptno , dname fromemp, dept whereemp.deptno = dept.deptno; s outer coupling selectename, dept.deptno, loc fromemp, dept whereemp.deptno ( ) = dept. DEPTNO; If there is a value in dept.deptno, there is no in Emp.Deptno (such as deptno = 40), and the result will generate an null value in the result of the results: the same base table Have to do it Connect, you can use the self-join manger.ename, manager.ename = manager.empno; manger s value which is coupled wages of employees belonging to the third level selectename, sal fromemp, salgrade wheregrade = 3 andsalbetweenlosalandhisal; (base table salgrade: grade Losal
Connection set operation s s s set operation line to two or more query results into one union-setunion Rowsoffirstqueryplusofsecondquery, lessduplicaterows intersect-setintersection Rowsbothquerieshaveincommon minus-setdifference rowsuniquetothefirstquery describes several views accountview enamesal Job returned to a query result, combined with records in the base table or view all sectors in which employees earn more than 2000 corresponding column data types must be the same selectename, sal fromaccount wheresal> 2000 union selectename, sal fromresearch wheresal > 2000 union INTERSECTOTOMIBOUNSETOUSETOUSETOUSETOUSETOUNSET The partial record of the second query result is different in the first query results.
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 ? > (selectsal fromemp whereename = 'clark'); plurality of base tables and subqueries selectename, job, sal fromemp, dept whereloc = 'new york' andemp.deptno = dept.deptno andsal> (selectsal fromemp whereename = 'scott'); subquery using poly-function selectename, hiredate eMP Register, create a new foundation Table CONNECT, registration, and inquiry Only DBA has the right to create new users dba or user can change the user password? Grant connecttoscott identifiedbyleopard; base table permissions 1 there are two ways to obtain permission for the operation of the base table to create your own base table obtain permission to create user base table of grantselect, insert These privileges select selex SELECT * Creating synonyms Create a synonym EMPLOYEEEE CRE for users Allen Ate_Synym EMPLOYEE alLlen.emp You can make other users have such power, that is, other users can grant your base table authority to others grand scott withgrantoption; recover permissions system permissions recover only the DBA privilege base table can always withdraw revokeinsert onemp fromscott; -------- -9 9 Create About indexes (at least 50 records To establish an index, insert data Establish any number of indexes for a base table Generally, the index is established as the primary key Establish an index, it does not affect the execution of the SQL command. After the index is established, Oracle is automatically maintained and used. Index Ensure that the data uniqueness increases the execution speed, the index can also ensure that each column value in each record is not repetitive. ? 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 percentage of employee names and bonuses accounted for a percentage. Require display time columnnamism clearly, sorted by employee name, excluding an unknown employee. Select Work in Chicago (Department 30) in Chicago (Department 30) All employees' salary rose 10%. updateemp setsal = 1.1 * sal wheredeptno = 30; updateemp setsal = 1.1 * sal wheredeptno = (selectdeptno fromdept whereloc = ' Chicago '); To Hitech newly built a department, numbered 50, and other information are not known. insertintodept (dname, deptno) values ( 'faclities', 50); create a view, three column name, which does not include the employee information createviewemployee ( "employeename", " Employee "" Employee Job ") Make a wage report, including employee name, employment time (press Week calculation), wage and department number, one page shows the information of a department, the end of each page, the sum of the wages of the page, and the sum of the employment time, Time summary, wage calculate according to US dollars, employment time is calculated according to week, and there should be titles per page. ttitle'service' breakondeptnoonpageonreport computesumofsalondeptno computesumofsalonreport computesumof service_lengthondeptno computesumofservice_lengthonreport columnsalformat $ 99,999.00 columnservice_lengthformat9999 selectdeptno, enameemployee, (sysdate-hiredate) / 7 YYYY, the total income includes the total income of employees without bonuses, named alphabetical order.
"Hirepha -date" format "EMPLOYEE", "EMPLOYEE", (SAL NVL (COMM) "EMPLOYEE" , 0) "Compensation", "hiredate" "hirephar Date" Lists more than 7 surrounding countries Name and area. selectnation, area fromnation wherecodein (selectnation_code fromborder groupbynation_code havingcount (*)> 7); lists all equal area larger than Japan The national name and population of the island country. selectnation, population fromnation, border wherecode = nation_code ( ) andnation_codeisnull andarea> = (selectarea fromnation whereupper (nation ) = 'Japan'); List all the borders in other countries, and show their border countries.
breakonnation selectnation1.nation, nation2.nationborderin_country fromnationnation1, border, nationnation2 wherenation1.code = border.nation_code andborder.border_code = nation2. Code ----------- ----------- PL / SQL 2PL / SQL block structure and data type The characteristics of block structure Nested end; No more than 30 characters The first character must be letters Can be arranged, and unparalleled in the case. No SQL reservation word single quote s zero-length string (no characters between the two single quotes), said NULL character: 1 length string of data definition syntax s s identifier [constant> data type [NOT NULL> [: = PL / SQL expressions>; ': =' s represent variables are assigned numeric data types include s number (7,2) character char (120) date type Boolean date Boolean (value True, False or NULL, not stored in the database) notnull: = false; availableboolean; (initial value NULL) % type matching the type books_printednumber (6); books_soldbook_printed% type; manager_nameemp.ename% type; variable Assignment Variable Name: = PL / SQL Expression Numvar: = 5; BOOLV Ar: = true; Datevar: = '11-jun-87'; Null value in digital expression null
President '; eXception ); begin selectempnointov1fromemp wherejob = 'manager'; exception whentoo_many_rowsthen insertintojob_errors values ( 'morethanonemanager '); End; Exception ---- 3SQL and PL / SQL inserted declare my_salnumber (7,2): = 3040.55; my_enamechar (25): = 'wanda'; my_hiredatedate: = '08 -SEP-88 '; begin INSERT in in (Empno, Enmae, Job, HiRedate, Sal, Deptno) VALUES (2741, My_ENAME,' CABDRIVER ', MY_HIREDATE, MY_SAL, 20); End ; s delete declare bad_child_typechar (20): = 'naughty'; begin deletefromsantas_gift_listwhere kid_rating = bad_child_type; end; transaction commit [WORK>; rollback [WORK> ; (optional WORK keyword, but has no effect on command execution) savepoint tag name; (save the current point) marks the current point in the transaction rollback [WORK> to [SAVEPOINT> tag name (Return to the current save point) All the modifications of the database after the savePoint command Keyword Work and SavePoint are optional, execute the command S function has no effect PL / SQL block SQL commands can be used for all functions insertintophonebook (lastname) value (upper (my_lastname)); selectavg (sal) intoavg_salfrom EMP; For non-SQL commands, most individual functions can be used x: = SQRT (Y); LastName: = Upper (lastname); age_diff: = months_between (birthday1, birthday2) / 12; s assignment of data type conversion 4 Species assignment form: s variable name: = expression insertinto yl table values (expression 1, expression 2 ,. ..); UPDATE Number Number Transfer into Char CHAR MTE to CHAR_VAR: = NM_VAR; Digital Conversion Brand Type Date_Var: = '25-DEC-88';
Character conversion into date type Example declare num_jobsnumber (4); begin selectcount (*) intonum_jobsfromauditions whereactorid = && actor_idandcalled_back = 'yes'; ifnum_jobs> 100 then updateactorsetactor_rating = 'wordclass' whereactorid = && actor_id; elsifnum_job = 75then updateactorsetactor_rating =' daytimesoaps' whereactorid = && actor_id ; else updateactorsetactor_rating = 'waiter' whereactorid = && actor_id; endif; endif; commit; end; -------- 5 Circulation End 3): = 0; begin @Sert inToTable1Values ('TastesGreat'); INSERT filling '); CTR: = CTR 1; End ... endloop; declare my_indexchar (20): = 'fettucinialfredo'; bowlchar (20); begin formy_indexinreverse21..30loop INSERT INTOTOTEMP (COLL ) values (my_index); / * cycles from 30 to 21 * / endloop; bowl: = my_index; end; with digits after inreverse must be ascending order, must be Is integer, can not be variables or expressions ---------- notfound% found% rowcount% isopen embodiment fetchmy_curintomy_var; whilemy_cur% foundloop (data processing) fetchmy_curintomy_var; exitwhenmy_cur % rowcount = 10; endloop; % notfound s attribute values as follows: fetch operation returns no record, a record of the value returned true fetch operation, then the value is not on the cursor fetch false Take the value of NULL
Note: If there is no Fetch action,
---------- 8. Pre-defined abnormal situation No Oracle error will automatically generate an exception information some abnormal conditions have been named, such as: no_data_found When SELECT statement is not returned When you record it, it is generated to define a cursor, while the SELECT statement returns a plurality of records. WHENEVER User-defined anomalies By the user's own partial definition: declare declare declare declare x Number; User-defined anomalous case Follow the general scope rule Obtain an exception: Raise_Something_ISNT_Right Note: You can also get predefined exceptions EXCEPTION_INIT statement Allow Oracle errors s call naming format: pragmaexception_init (
product_status column values of 'shipped' and 'ordered' inventory: product_idnumber (6) product_descriptionchar (30) product_statuschar (20) std_shipping_qtynumber (3) contract_item: contract_no number (12) item_nonumber (6) arrival_datedate order: order_idnumber (6) product_idnumber (6) qtynumber (3) 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 SET-ar ar = itex; c tem setarrival_date = add_months (sysdate, 1) whereitem_no = && itemnoandcontract_no = && contractno; else updatecontract_item setarrival_date = add_months (sysdate, 2) whereitem_no = && itemnoand contract_no = && contractno; insertintoorders values (100, i_product_id, i_std_shipping_qty); endif; endif; commit; end; 2: 1. identify all the specified sectors Employee 2. Use the variable with '&' to prompt the user input department number 3. Switch the employee name and wage into the PRNTTable table, the base structure is: Create_TablePlePle (7), LINE 4. Experient situation, the employee information of the bonus is not null 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; closeemp_cur; commit; end; Java Research organization - Copyright 2002-2002RE: ORACLE database objects and user management (rpm)
Author: UB Time: 2003-08-14 21:06:59 [modify] [Reply] [Delete] ORACLE database objects and a user management, content management and major ORACLE database schema objects of the maintenance of this section is on ORACLE database The mode object management and maintenance, 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 The tablespace is necessary to maintain it first because the table space is a logical space containing these mode objects. Create a table space sql> createphablespace @ '/ usr / usr / oracle / dbs / jxzy.dbf'> online; modified table space sql> altertablespace jxzy @tablesNormal; SQL> AltertableSpace / usr / oracle / dbs / jxzy.dbf '> to' / usr / oracle / dbs / jxzynew.dbf '> Online SQL> CREATETABLESPACE > DROPTABLESPACE JXZY> INCLUDINGTENTENTS 1. 图片 2 The table maintenance table is the basic unit of data storage in the database, and a table contains several columns, each column name, type, length, etc. The establishment of the table sql> createphable (> office_num NOT / NULL); SQL> Altertable SQL> Alter_Table cascade constraints // Delete The integrity constraints of other tables of the table are referenced 1. 3 The view maintenance 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 (selectpole_path_num 安 ASPATH, WELL asSupport_Device asFrom: SQL> DROPVIEW; 1.4 sequence maintenance sequence is the unique integer generated by the sequence generator.
Establishing sequence SQL> CREATESEQUENCEjxzy.sequence_cable> STARTWITH1> INCREMENTBY1> NO_MAXVALUE; established sequence, jxzy.sequence_cable.currval returns the current value, jxzy.sequence_cable.nextval return current The new value of the value after 1 is added. SQL> alter_cable> Start_1 // The starting point cannot be modified, if you modify, you should delete, then redefine> IncTement 2> MaxValue_1000; Deletion of the Sequence SQL> DROPSQUENCE sSEquence_cable 1. 5 5 The index maintenance index is a structure associated with the table, which is established to improve the 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 unique index index creation SQL> Create ipspace (switch_name)> TableSpace \ (office_num, switch_name> TABLESPACE jxzy; Index Delete SQL> DROPINDEX jxzy.idx_switch; 1. 6 完 完 约 约 约 管理 管理 管理 正 正 正 正 正 性 性 性 性 性 性 性 性; 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 constraint. A.NOTNULL b.UNIQUE non-null primary key unique key c.PRIMATYKEY a table can have a non-empty outer d.FOREIGAKEY The key E.CHECK NN_DESC not NN_DESC not NN_DESC SQL> altertable SQL> altertable / witch (switch_code u ((8)> constraint NUMBER (8),> office_numnumber (3)> CONSTRAINTfk_officenum> REFERENCESoffice_organization (office_num)> ONDELETECASCADE); define inspection SQL> CREATETABLEoffice_organization (> office_numNUMBER (3) ,> CHECK 二 二 9 数据 数据 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户management system is configured with a good security mechanism 2.1ORACLE database security policy to establish system-level security guarantees system-level privileges is achieved by granting user rights system-level, system-level rights (the system privileges) includes: establishing Table space, build users, modify users' rights, delete users, etc. System privileges can be granted to users, or they can be recycled at any time. Oracle system privileges 8 More than 0. 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. Content 2.2 User Management ORACLE user management including user creation, modification and deletion of users to establish SQL> CREATEUSERjxzy> IDENTIFIEDBYjxzy_password> DEFAULTTABLESPACEsystem> QUATA5MONsystem; sql> create sql> DROPUSER JXZYCASCADE; simultaneously delete its established entity 2.3 System privilege management and control Oracle offer more than 80 system privileges, each of which allows users to perform one or a type of database operation.
