Questions about Oracle Functions

Oracle functions interview question 1. What are the functions for string operations? Answer: ASCII ()-the function returns the ASCII code value CHR () of the leftmost character of the character expression-the function is used to convert the ASCII code to a character-if no 0 ~ is entered ~ The CHR function returns a NULL value for the ASCII code value between 255-the chr () must be assigned a numerical value concat (str, str) to connect two strings LOWER () -function to convert all strings to lowercase UPPER ()-function to convert all strings to uppercase LTRIM ()-function to remove spaces in the string header RTRIM () -The function removes spaces at the end of the string from TRIM (). At the same time, it removes spaces at both ends. In fact, LTRIM (), RTRIM (), TRIM () is to remove the specified character from the string. The LTRIM (), RTRIM () format is xxxx (the string to be truncated), but TRIM () the format is TRIM (a character to be truncated from the intercepted String) SUBSTR ()-the function returns some strings INSTR (String, substring) -The function returns the starting position of a specified substring in the string. If the substring does not exist, 0 REPLACE is returned. (the original string, the string to be replaced, must be replaced. String) SOUNDEX () -The function returns a four-digit CAPTCHA-The SOUNDEX function can be used to search for sound-similar strings. However, the SOUNDEX function returns only NULL values for numbers and Chinese characters. 2. Transaction concept answer: A transaction is a mechanism that ensures that multiple SQL statements are processed as a single unit of work. Transactions have the following functions: * consistency: queries and updates at the same time do not conflict with each other, and other users do not see the changed but not submitted data. * Recoverability: Once a system failure occurs, the database automatically completely recovers unfinished transactions. 3. Query System Time in oracle A: select sysdate from dual4. What is the function of a trigger? When can I use a trigger to create a trigger? Can there be a commit in the trigger? Why? Answer: A trigger can be started and run by an event. It exists in a process on the database server. Its role: to implement complex constraints that cannot be completed by general constraints, thus achieving more complex integrity requirements. There are no strict limits on the use of triggers. You can use triggers to ensure database integrity as long as you want to complete the constraints that cannot be completed without manual participation. Because the trigger is mainly used to ensure the integrity OF the database, to create a trigger, you must first specify which one the trigger should belong to (DML, instead of, SYSTEM) because they have their own purposes, the second is to determine the data designed after the trigger is triggered. COMMIT cannot be used in the departure tool. 5. numeric functions abs () absolute value exp (value) e value power ceil () is greater than or equal to the minimum integer floor () is less than or equal to the maximum integer trunc (value, precision) Keep precision decimal places troughs valueround (value, precision) Keep precision decimal places round value into sign () returns 1,-1, 0mod () according to the value is positive and negative zero () evaluate the square root of the exponent power (value, exponent) value of the modulo operation sqrt. level 4: What is the difference between a relational database system and a file database system? What aspects are applicable to relational database systems? Answer: The difference between the file system of the relational database system is: first, the overall data of the relational database is structured and described using the relational data model, which is the fundamental difference between it and the file system. (Data models include: data structures, data operations, and integrity constraints) Second, relational database systems are highly shared, and low redundancy can be applied to the entire system, the file system has application limitations and is not easy to expand. Third, the relational database system uses a two-level ing mechanism to ensure the high independence of data, so that programming and data are highly independent. This is not achieved by the file system. It can only be applied to a specific application. (Two-level ing: external mode/mode ing to ensure logical independence and internal mode/mode ing to ensure physical independence. External mode: user mode, which describes the Logical Structure Features of local data of database users. Mode: Describes the logical structure features of all database data. Internal Mode: The description of the final physical storage structure of the data .) Fourth, the relational database system is managed by a unified DBMS, which provides data security protection, concurrency control, integrity check, and database restoration services. 5. Concepts of triggers and stored procedures. Answer: A trigger is a process stored in a database. It is implicitly triggered when a table is modified (add, delete, or modify. Stored Procedure: it is a collection of database-language SQL statements. It is also stored in the database, but it can be started or run directly by other applications. 6. What are basic SQL statements? Answer: select, insert, update, delete, create, drop, truncate1. intermediate 8. What is transaction consistency? Select a familiar database to handle a transaction, such as credit card withdrawal. Answer: Transaction consistency: represents the atomicity of the transaction. There is no third case for the database operations of the firm to be successful or failed. Whether the transaction is committed successfully or not, it cannot affect the consistency of database data. Transaction: a sequence of user-defined database operations. These operations are either completed successfully or not done at all, which is an integral whole. SQL statements defining transactions include BEGIN TRANSACTION, COMMIT, and ROLLBACK. Atomicity of transactions: database operations included in transactions are either performed or not performed. Isolation of transactions: operations performed by transactions on data cannot be affected by other transactions. Transaction continuity: that is, the impact of transactions on data is permanent. For the 'credit card withdrawal 'transaction, it is necessary to ensure that the 'extracted cash' and the 'Card account balance 'must be modified successfully or fail at the same time. begin transaction reads A's account BALANCE; BALANCE = BALANCE-AMOUNT transfer AMOUNT; IF (BALANCE <0) THENROLLBACK; ELSEBEGIN writes back A's new BALANCE; reads B's account BALANCE BALANCEB; BALANCEB = BALANCEB + AMOUNT transfer AMOUNT; write back the new balance of B; COMMIT; END IF; END; 9. Write and debug the stored procedure or trigger. answer: 10. How to Implement the index? What is the indexing principle? Index cost? Index type? Answer: There are two ways to implement the index: create a specific index for some fields in a table, for example, the oracle: create index name on table name (field name ); when a table is created, the system automatically creates an index for the primary key or unique constraint for the field. Indexing principle: Create an index table based on the index field, store the field value and the physical address of the corresponding record, in this way, you can search for the index table's physical address based on the field value to directly access the record. Although indexing improves the query speed, indexing takes up a certain amount of system storage capacity and processing time. You need to analyze the index based on the actual situation. index types include B-tree index, bitmap index, and function index. 11. What is the concept of view? When to apply? Answer: view is a multi-angle perspective of table-level data. It is suitable for environments with certain requirements on query security and flexibility. 12. SQL statement exception handling? Example? Answer: When an error occurs during SQL statement execution, for example, no results are found during the query, and no records are recorded during the change, measures should be taken. oracle is used as an example to test its understanding of exception. for example, the select field into variable from table. If multiple records are queried, exception Handling should be added. there are three Oracle Exception Handling Methods: 1. for pre-defined EXCEPTION handling, when exception when exception_name THEN2.EXCEPTION _ INIT generates an EXCEPTION, declare must first declare an EXCEPTION name, and then bind the EXCEPTION name and error code to pragma exception_init (error name, error code ); at last, the when error name then3. the custom EXCEPTION does not need EXCEPTION. when the judgment condition is mature, raise_application_error (-20000 ~~~ -20999, prompt information ). 13. judge whether the following statements are correct. (1) When a SQL command in a transaction is submitted, other SQL commands may not be submitted. (2) In a relational table, a primary key uniquely identifies a row of records. (3) The foreign key in a relational table must be the primary key in another table. (4) rollback can return to the data status before the transaction operation when the transaction fails. Answer: (1) error (2) Correct (3) Correct (4) Correct 14. Which of the following measures can ensure data integrity and consistency in the database? (1) custom Data Type (2) trigger (3) primary key and foreign key (4) Transaction answer: (1) (2) (3) (4) (Why 1) 15. What are the advantages of using stored procedures to access databases over using SQL statements? Answer: The stored procedure has been pre-compiled. do not compile it during execution. The execution speed is faster. The Stored Procedure encapsulates a batch of SQL statements to maintain data integrity and consistency; code can be reused. 1. advanced 16. Describe the architecture of proficient database systems, including storage, rollback, and operation mechanisms. answer: For the oracle system, describe the structure of sga; functions of processes such as pmon, ckpt, lgwr, and smon in the background; tablespace allocation policies; the structure of the rollback segment. The sga of oracle includes the following main areas: database cache, redo log cache, and shared pool (data dictionary cache and database cache ), large pools, etc. The database cache area is used to store recently used data blocks. It is mainly used to store database write processes (DBWR) and data files in background processes; the redo log cache is used to store the redo log information generated by operating database data. The redo log write process (LGWR) and redo log files are cooperated with the redo log write process (LGWR; the sharing pool mainly caches SQL/PLSQL, resource locks, and control information, the database cache mainly caches the parsed and executed SQL/PLSQL database cache, which can be divided into two zones: Shared SQL and private SQL. The shared SQL is used to store the syntax analysis results and execution plans of SQL statements, private SQL is used to store binding variables and session parameters related to the execution of specific SQL statements. Another important part of an ORACLE instance is its background processes. The main background processes include database write processes (DBWR), redo log write processes (LGWR), and system monitor (SMON ), process Monitor (PMON), Checkpoint Process (CKPT ). DBWR writes dirty and cold data in the database cache to a data file. LGWR writes the redo log information generated by database data operations to the redo log file; SMON restores the database when the database is restarted due to abnormal database shutdown. PMON is used to restore Failed User processes and service processes and release the system resources it occupies; CKPT indicates that the database is in the complete state. Logical Storage Structure: data BLOCK, partition EXTENT, SEGMENT, TABLESPACE physical storage space: TABLESPACE, data file, control file, log file, the software architecture of data dictionary is the above description of SGA and background processes. 17. describes the guarantee mechanism of data consistency for proficient database systems, including lock and transaction consistency. answer: In a concurrent environment, multiple mechanisms are used to maintain data consistency, such as transaction-level consistency, row-level locks, and table-level locks provided by the oracle system. 18. Describe the online backup and recovery mechanisms of proficient database systems and assess their understanding of logs. answer: Describe the real-time online backup policies of relevant databases, such as how the database system ensures real-time data backup during operation. When a problem occurs, measures should be taken to recover from online backup. for oracle, how should we set and modify the archive online backup mode, and how to arrange the backup space. 19. Proficient database systems describe performance optimization methods, including optimization policies, parameters, and verification methods. answer: 20. What is the difference between Truncate and delete? (This question can lead to many problems. For example, if you want to quickly clear the data in a large table, should you use the settings operation? The answer is: truncate should be used. Also, when truncate and delete commit are used for two separate tables with the same number of records, and count (*) is used to count the number of records for these two tables, which table will be faster? The answer is: If you use truncate, it will be faster .) 21. Write a 'curl' and use it. (Please note that you must close the cursor after using the cursor ). DeclareCursor cursor_name isSelect * from tablename; BeginOpen cursor_name; LoopFetch cursor_name into bianliang; Exit when cursor_name % notfound; SQL ........ End loop; Close cursor_name; End; 22. When creating a table, pctfree 10 pctused 40 is set while the index of the table is not pctused 40. Why? (The main idea of this question is that the index cannot get any benefit on pctused 40.) 23. How many rows of records are there in the largest used table? (This question is not based on the examinee's technical skills, but on whether the examinee has experience in managing large databases.) do not perform function operations on the table's index columns, in this way, the system will not be able to use indexes, making the query very slow. However, after ora8i, the system has started to support function indexes, making up for this deficiency. 24. ORACLE Database startup and shutdown steps start: Start the instance -- load the database data -- open the database and close: close database -- detach database data -- close instance 25, the difference between Delete and truncate delete is generally used to Delete a small number of records, it also uses rollback segments and displays the committed transactions. Truncate is used to delete a large amount of data and commit transactions implicitly, which is much faster than delete. 26. What does DDL and DML represent ?? DDL indicates the data definition language. In ORACLE, CREATE, ALTER, and DROPDML indicate the data operation language. DML mainly includes SELECT, INSERT, UPDATE, DELETE28, the function called when the dynamic effect is in Javascript. 29. There are several identical records in the database, and only one record is deleted. Use the SQL statement ROW_ID to write the execution process. Delete table_name where ziduan = ''and rowed <(select max (rowed) from table_name where ziduan = ''); 30. Where are oracle database tables stored on disks (what physical space does it mean )? Oracle database tables are stored in data files. 31. What statements Does Oracle use to assign permissions to users? Grant to Statement 32. on the project site, the user asks you TO add a field TO the running table. What should you do? Method 1: Shut down the database and enable it in restricted mode. sys/sysdba performs the second method: do not close the database, place the database in a silent State and use alter system quisce restricted in SYS/SYSDBA mode. in this state, only SYS/SYSDBA can operate the database, after the modification, exit the silent State. alter system unquisce reviews the two special statuses of the database: silent State (QUISCE) in the silent State, only SYS/SYSDBA with special permissions can operate on the database. After using alter system quisce restricted, the SYSTEM will wait for the Active session to stop, at the same time, the system blocks new sessions, suspends all SQL statements, and re-activates the session after recovery to execute the suspended SQL statements. The suspension State means that the system suspends all I/O operations on physical files (data files, control files, and log files) in the database, but does not prohibit non-DBA users from performing operations on the database. This status is mainly used for database backup. 33. What is the concept of Oracle rollback? What is the function of rollback segments. Rollback means to restore the database data to the database data status before the transaction is modified before the transaction is committed. The rollback segment provides a basis for rollback. It records data before the transaction operation database or operations corresponding to the previous operation. This content depends on the previous operation. For example, if the previous transaction operation is an UPDATE operation, the rollback segment stores the data before UPDATE. if the transaction is a DELETE operation, the rollback segment stores the INSERT operation statement corresponding to the transaction, on the contrary, if the transaction operation is INSERT, the corresponding record is the DELETE operation. 34. What is the difference between Oracle's 8I and 9I? 35. How can I delete any 20 records in a table with 0.1 million records? Use SQL statements to perform operations 36. How does the client access the oracle database on the server? What is the possible cause if the client cannot access the server's oralce? The client uses a network or process to obtain a connection with the server's ORACLE database as a valid user identity. If the client cannot access the server-side ORACLE, the possible cause is: the user is not authorized to access the database; the server-side database is not opened (step 3 of starting the database is not completed ); if the server is in shared mode, there may be no scheduling process Dnnn corresponding to the communication protocol used by the customer. 37. Where can I find the error message when an oracle statement execution error occurs? Do Select * from USER_ERRORS38 and select statements need to be submitted? What about insert and update statements? 39. Where are the submitted data stored after the insert statement is executed and submitted ?? 40. What types of files are stored in oracle? Data files, control files, log files, and data dictionaries 41. What is the architecture of oracle? Logical architecture: Block, zone, segment, tablespace physical architecture: tablespace, three major file Software Architecture: SGA, background process 42. What about oracle's row _ id? Brief introduction? ORACLE's row_id is a pseudo column with 18 bytes which can be divided into 6363 bytes, indicating the segment number and data file number respectively, the data block number and record number. Row_id indicates the physical storage address of a record. 43. How to delete a user in oracle? Oracle uses drop user to delete users. If drop user cascade is used, USER objects are also deleted. To delete a user without affecting the use of objects under the user, you can use alter user username account lock to lock the user. 44. What is the procedure for the client to perform oralce operations on the server? In dedicated mode: the user tries to get a connection request from the ghost client of the ORACLE database server through the application process, and the ORACLE server uses the listening process to listen to the user request, and to verify the user identity, after verification, a dedicated service process is assigned to the user. When the user submits an SQL statement, the dedicated service process is first checked in the shared pool of the SGA area to check whether there are similar SQL statements that have been parsed and executed. and the cached SQL statement, if yes, use the parsing result and execution plan to execute the SQL statement, if no, the SQL statement is parsed by syntax to generate an execution plan. If the scheduler is parsed, the operation is executed to obtain the data, and the execution result is returned to the customer. In the sharing mode: Unlike the dedicated mode, a listener does not assign a dedicated service process to a user after verifying the validity of the user. Instead, the listener associates the request with the dispatch process of the response, put the requests in a request queue, and then the response Dnnn gets a request from the scheduling queue and allocates an idle service process to it, next, a service process performs service operations on the request in the same way as that in a dedicated mode. After the processing is complete, the service process puts the result into a return queue and then the scheduling process (Dnnn) returns the results in the returned queue to the corresponding user. 45. Is the execution efficiency of exits and in the ORALCE database higher? Exits execution efficiency is higher than in. Because: 46. How do I determine that the cursor has reached the last row? Cursor_name % notfound47. Which of the following SQL statements is more efficient for clustering indexes and common indexes ?? (I can't recall the original question. The topic is the difference between clustered index and general index.) concept class: aggregate function? What is the definition and usage of a Session? Oracle stored procedures? What is a constructor? 48. Briefly describe the components of SGA in ORACLE. A: The Global System zone includes the Shared Pool, redo log cache, data cache, large pool, and JAVA pool. 49. How many steps are involved in ORACLE startup and shutdown? Start: Start the instance, load the database data, and open the database. Close: closes the database, detaches the database data, and closes the instance. 50. What table space will the data be stored in during Oracle tablespace classification and function, such as sorting? It is a logical concept to break through the limitation of storage capacity. Sort data in the temporary tablespace. 51. what changes will happen to the database when executing the COMMIT command? What about ROLLBACK? A: When committed, the redo log information corresponding to the transaction will be written to the redo log file in the physical database file, whether or not the transaction operation content is actually reflected in the data file depends on whether DBWR is started. When you insert, delete, and modify a database, it is only after the transaction is committed to the database. Only the database owner can see the transaction before it is committed, only after the final submission is complete can others see it. ROLLBACK rolls back the transactions that have not yet been committed to restore the database to the status before the transaction operation. 52. Use commands to create a user and authorize the user. Create user user_nameidentified by password/identified externally/identified blobally as 'cn = user' default tablespace tablespace_nametemporary tablespace tablespace_name; grant role/privilege to user_name; 53. Write a stored procedure, increase the salary of employees with salary values in the employee (name, age, emp_no, salary) table between 0 and 20% by (Note: The cursor is required) Cteate or replace procedure emp_salV_name employee. name % type; V_emp_no employee. emp_no % type; V_salary employee. s Alary % type; Cursor cursor_sal isSelect name, emp_no, salary from employee where salary between 0 and 1000; BeginOpen cursor_sal; LoopFetch cursor_sal into v_name, primary, v_salary; Exit when cursor_sal % notfound; update employee set salary = salary * 1.2 where name = v_name and emp_no = v_emp_no; End loop; Close cursor_sal; Commit; End; 54. What types of files are available in ORACLE databases? Data files, control files, log files, parameter files 55. Use commands to create tablespaces and users, and grant and revoke permissions to users. Create tablespace tabllespacenameDatafile 'f: \ orcl \ user001.dbf size 20 mDefault storage (Initial 512 kNext 512 kminextents 2 pctincrease 50% maxexitets 2048) minimum extent 512 revoke management dictionary; revoke permissions: revoke privilege from user; create tablespace tablespace_namedatafile ''default storage (initial xxxkbnext yykbminextents 2 pctincrease nnnnmaxextents mmm) loggingonlineextent ma Nagement dictionary/local [autoallocate/uniform size xxxmb]; create user user_nameidentified by passwore/identified externally/identified globally as 'cn = user' default tablespace tablespace_nametemporary tablespace tablespace_name [account lock/unlock] grant connect to user_name; grant create table to suer_name; grant update on table_name to user_name; revoke create table from user_name; revoke update on Table_name from user_name; 56. How do I change the user name in Oracle? 57. How can I query all the tables created by a user in Oracle? A: select * from cat; 57. After the TRUNCATE command is executed, does the bucket still exist and can it be occupied by other tables? After TRUNCATE is executed, the allocated partition space for the table will be recycled and HWM will be rolled back, if the reuse storage is not specified when TRUNCATE is used, only the partitions specified by MINEXTENTS are left after the operation is executed. Otherwise, all spaces in the table will be recycled for redistribution. 59. How can I view the current user in Oracle? (Tip: show user and select * from user_users) show user;/select username from user_users; 60. In Oracle, create the tablespace syntax structure and parameter description. Create tablespace tablespacenameDatafile ''Logging/nologgingDefault storage (initial kbNext kbMinextents xMaxextents yPctincrease z) online/not onlineextent management dictionary/local [autoallocate/uniform size kb]; 61. How can I determine whether the cursor ends? (Tip: Use % notfound) cursor_name % notfound62. in Oracle, how can I view all tablespaces of the current user? Select tablespace_name from user_tablespaces; 63. In Oracle, where are the tablespace information you created? Stored in the data dictionary. The data dictionary content corresponds to the SYSTEM tablespace. 64. When will the control file in ORACLE be read? When the ORACLE server is started, the instance is started first and then various database files are read, including control files. That is to say, it is read at the second step of database server startup. 65. Where is the Oracle table information stored? The data dictionary file of the SYSTEM tablespace. 66. How do I create a tablespace Based on the dictionary and describe the parameters? 66. In ORACLE, when a user is operating on an execution process, the Administrator cancels his or her permissions. What will happen? 68. What are your understanding of roles and common roles? A role is a set of database entities with permissions. It does not belong to any mode or user but can be granted to any user. Common roles include CONNECT, DBA, RESOURCE, SELECT_CATALOG_ROLE (query all table views), and DELETE_CATALOG_ROLE (delete permission. Role creation and authorization: similar to creating a user to authorize a user. Create role role_name identified... Grant permission to role_name. 69. Briefly describe the archiving and non-archiving modes of Oracle, respectively. Oracle archive mode specifies the ARCHIVELOG parameter when creating a database. In this mode, when the redo log file is full, the content of the redo log file is saved to the specified location (determined by the ARCHIVE_LOG_DEST_n parameter in the initialization file ). The archive operation is not automatically completed when the database is working in archive mode. In archive mode, there are two archive methods: automatic archiving (the ARCHIVE_LOG_START parameter in the initialization file is set to TRUE) and manual archiving. If automatic archiving is not enabled in archive mode, if manual archiving is not performed, when the LGWR process writes the redo log information to a fully-written redo log file, the database will be suspended until it is archived. It can be seen that archiving is a protection measure for redo log file information. In non-archive Oracle mode, when the redo log file is full, if LGWR is used to write the redo log information, the redo log information previously saved in the redo log file will be overwritten. 70. What types of Oracle indexes are used to define unique indexes and bitmap indexes. Oracle indexes include B-tree indexes, bitmap indexes, function indexes, and cluster indexes. The unique index is also a type of B-tree index. It requires that the indexed field values cannot be repeated. Use the B-tree algorithm to create the database. Bitmap indexes are not stored as a unique index (index field value, record ROWID) to create index segments. Instead, they create a bitmap for each unique field value, bitmap uses bitwise elements to correspond to the ROWID of a record. Bitwise to ROWID is mapped. 71. What are the basic data types of ORACLE? Char () Stores fixed-length characters. It can be defined without a specified length, but an error occurs if a value is inserted into it. varchar2 () must specify the length when defining variable-length characters, date stores the time and date, Number () numeric type, including integer and floating point types, clob () large-capacity string, blob () Large binary object 72, SQL, which function is used to perform rounding? Round (value, number of reserved decimal places) has a very similar function trunc (value, number of reserved decimal places) it intercepts the original number based on the number of decimal places to be retained. 73. Where are oracle database tables stored on disks? Data Files ORACLE database tables are stored in data files in physical files. 74. Where will the submitted data be stored after the insert statement is executed and submitted ?? Data files stored in the database. 75. Is the execution efficiency of Exits and in the ORALCE database higher? EXITS execution efficiency is faster than IN. 76. How do I operate functions provided by ORACLE? 77. How many physical files are there in the database? 1) Data File 2) control file 3) What information does the log file 78 and control file contain? The control file contains instance information (Instance name creation time, etc.), data files and log file information, and the system change code (SCN) recorded during system operation ), checkpoint information and archived current status information. When loading a database, the database must read the control file to obtain the physical structure information related to the database before loading the data file and Log File correctly and opening the database. 79. How to Use the Decode function? DECODE Syntax: DECODE (value, if1, then1, if2, then2, if3, then3 ,..., Else), indicating that if the value is equal to if1, the result of the DECODE function returns then1 ,..., If it is not equal to any if value, else is returned. At first glance, DECODE can only perform equals tests, but as we have seen just now, we can use some functions or computing to replace value to enable the DECODE function to have the functions greater than, less than, or equal. 2. How to Use decode for comparison with a value greater than or less? Use the sign () function and DECODE together to use select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; decode (condition, value 1, translation value 1, value 2, translation value 2 ,... Value n, translation value n, default value) The function has the following meanings: IF condition = value 1 THENRETURN (translation value 1) ELSIF condition = value 2 THENRETURN (translation value 2 )...... ELSIF condition = value n THENRETURN (translation value n) ELSERETURN (default value) end if • Usage: 1. Compare the size of select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual;-The sign () function with a small value returns 0, 1, and-1 respectively based on a value of 0, positive, or negative. For example: variable 1 = 10, variable 2 = 20, then sign (variable 1-variable 2) returns-1, and the decode decoding result is "variable 1", achieving a small value. 2. the table and view structure are converted to the existing product sales table sale. The table structure is month char (6)-month sale number ()-month sale amount existing data is: 200001 1000200002 1100200003 1200200004 1300200005 1400200006 1500200007 1600200101 1100200202 1200200301 data to be converted to the following structure: year char (4)-year month1 number (1300)-January sales amount mon2number) -Sales amount month3 number ()-sales amount month4 number ()-sales amount month5 number ()-sales amount month6 number) -Sales amount: month7 number )- June sales amount month8 number ()-June sales amount month9 number ()-June sales amount month10 number ()-June sales amount month11 number) -The SQL statement for converting the November sales amount to month12 number ()-The December sales amount structure is: create or replace viewv_sale (year, month1, mon2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12) asselectsubstrb (month,), sum (decode (substrb (month, 5, 2), '01', minimum, 0 )), sum (decode (substrb (month, 5, 2), '02 ', hour, 0), sum (Decode (substrb (month, 5, 2), '03', hour, 0), sum (decode (substrb (month, 5, 2), '04 ', hour, 0 )), sum (decode (substrb (month, 5, 2), '05 ', hour, 0), sum (decode (substrb (month, 5, 2), '06', hour, 0), sum (decode (substrb (month, 5, 2), '07 ', hour, 0), sum (decode (substrb (month, 5, 2), '08 ′, values, 0), sum (decode (substrb (month, 5, 2), '09', hour, 0), sum (decode (substrb (month, 5, 2 ), '10', hour, 0), sum (decode (substrb (month, 5, 2), '11', hour, 0), sum (deco De (substrb (month, 5, 2), '12', hour, 0) from salegroup by substrb (month,); 79. Usage of CASE statements? Oracle usage is simple: SELECT last_name, job_id, salaryCASE job_idWHEN 'it _ prog' THEN 1.10 * salaryWHEN 'st _ cler' THEN 1.15 * salaryWHEN 'sa _ REP 'THEN 1.20 * salaryELSE salary END "region" FROM employees 80, truncate and what is the difference between delete? 1. TRUNCATE is very fast on various tables, whether large or small. If the ROLLBACK command DELETE is used, the TRUNCATE command is not used. 2. TRUNCATE is a DDL language and DELETE is a DML statement. Like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command for TRUNCATE. 3. TRUNCATE will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the TRUNCATE operation is much faster than the table after the DELETE operation. 4. TRUNCATE cannot trigger, and DELETE will trigger the trigger. 5. You cannot grant anyone the permission to clear tables of others. 6. After the table is cleared, the index of the table and the table is reset to the initial size, while the delete statement is not. 7. The parent table cannot be cleared. 81. How to expand the tablespace? And use the statement to write? Two extension methods: a) add the data file alter tablespace tablespace_name add datafile ''xxMBb) expand the data file size alter database datafile'' resize newMB82 and manage the tablespace? Which method is recommended now? A) dictionary management method extent management dictionary; default method B) local management Method extent management local [autoallocate/uniform xxmb]; 83. What function is used to obtain the date? And the month, day, year to_char (sysdate, 'Year'): tow thsound six to_char (sysdate, 'yyyy'): 2006to_char (sysdate, 'month '): to_char (sysdate, 'mm'): 08to_char (sysdate, 'day'): Week 4 to_char (sysdate, 'dd'): 2284, partition table application? A) a partition table has one or more partitions. Each partition uses a range partition, hash partition, or a row that combines partition partitions. B) Each partition in the partition table is a segment, it can be located in different tablespaces. c) It is very useful for large table partitions that can be queried or operated by several processes at the same time. 85. How can I talk about index usage and principles? An index is a list of keywords of several data rows. When querying data, you can use the keywords in the index to quickly locate the data block of the record to be accessed, this greatly reduces the number of I/O reads from data blocks, thus significantly improving performance. 86. How can I have both input and output for Stored Procedure applications? Create procedure pro_name (xxxx in/out type; yyyy in/out/inout type;) is/aszzzz type; beginsqlpro; predictionexceptionxxxxx; commit; end; 87. What are common exceptions? Common pre-defined exception CURSOR_ALREADY_OPEN-ORA-06511 SQLCODE =-6511 the cursor has opened DUP_VAL_ON_INDEX-ORA-00001 SQLCODE =-1 violation of the uniqueness constraint INVALID_CURSOR-ORA-01001 SQLCODE =-1001 the INVALID_NUMBER-ORA-01722 SQLCODE =- 1722 character to numeric Conversion failed LOGIN_DENIED-ORA-01017 SQLCODE =-1017NO_DATA_FOUND-ORA-01403 SQLCODE = + 100 no data found NOT_LOGGED_ON-ORA-01012 SQLCODE =-1012 not connected to database PROGRAM_ERROR-ORA-06501 SQLCODE =-6501 internal error STORAGE _ ERROR-ORA-06500 SQLCODE =-6500TIMEOUT_ON_RESOURCE-ORA-00051 SQLCODE =-response-ORA-01422 SQLCODE =-1422 returns multiline TRANSACTION_BACKED_OUT-ORA-00061 SQLCODE =-61VALUE_ERROR-ORA-06502 SQLCODE =-6502 numeric Conversion ERROR access_resource_null try to assign ZERO_DIVIDE-ORA-01476 SQLCODE =-1476 to the property of the NULL object to get rid of OTHERS-any other error handling 88, how to use exceptions? There are three types of exceptions in oracle. Predefined exceptions non-predefined exceptions user-defined exceptions the second non-predefined exception is associated with a specific oracle error. And it is associated with PRAGM EXCEPTION_INIT (EXCEPTION_NAME, ERROR_NUMBER. But what is the purpose? Example: declare dup_primary_key exception; pragma exception_init (dup_primary_key,-1); begin insert into itemfile values ('i201 ', 'Explorer', 'spares', 12, 30 ); exception when dup_primary_key then dbms_output.put_line ('repeated item number-primary key conflict '); Use method 1 of end: exceptionwhen exception name then exception Handling Code; Use method 3: if condition thenraise_application_error (-20000 ""-20999, prompt information); end if; 89. The optimization policies generally include: • memory optimization • Operating System Optimization • Data Storage optimization • network optimization and other methods specific to different databases involves adjusting different database configuration files, different operating system parameters, and network parameters., different databases are different.