Oracle face Test (Basic article)--test interview often ask--take a good look ____oracle

Source: Internet
Author: User
Tags create index lowercase rollback rowcount

Reproduced from: https://wenku.baidu.com/view/cfa4e185fd0a79563c1e72a0.html


1. Oracle differs from SQL Server 2005. On the macro level:
1. The biggest difference is that the platform, Oracle can run on different platforms, SQL Server can only run on the Windows platform, because the stability and security of Windows platform affects SQL Server stability and security
2. Oracle uses a scripting language of pl-sql, while SQL Server uses a script that is T-SQL
Micro: From the data type, the structure of the database and so on answer
2. How to use Oracle cursors.
1. Cursors in Oracle are divided into display cursors and implicit cursors
2. A display cursor is a cursor defined with the cursor...is command that handles multiple records returned by a query statement (select), an implicit cursor that performs inserts (insert), deletes (delete), modifies (update), and returns a single record (select statement is automatically defined by Pl/sql.
3. Operation of an explicit cursor: Open the cursor, manipulate the cursor, close the cursor, pl/sql implicitly open the SQL cursor, process the SQL statement inside it, and then close it
3. The difference between function and procedure in Oracle.
1. You can understand that a function is a stored procedure
2. Function can have no parameters, but must have a return value, stored procedures can have no parameters, do not need to return the value
3. Both can return a value through the out parameter, and it is recommended that you use a stored procedure if you need to return multiple parameters
4. Only functions can be called in SQL data manipulation statements and cannot call stored procedures
4. Oracle Import and export there are several ways to differentiate.
1. Using Oracle Tools Exp/imp
2. Using Plsql related tools
Method 1. Import/export is binary data, 2.plsql Import/export is a text file of SQL statements
5. What kinds of documents are in Oracle?
Data file (General suffix is. dbf or. ora), log file (suffix name. log), control file (suffix named. ctl)
6. How to optimize Oracle database, there are several ways.
Personally understand that the most critical factor in database performance is IO, because the operating memory is fast, but read-write disk is slow, the most critical problem in optimizing the database is to reduce the disk IO, personal understanding should be divided into physical and logical optimization, physical refers to the Oracle product itself, some optimization, Logical optimization refers to application-level optimization
Some principles of physical optimization:
1. Oracle's operating Environment (network, hardware, etc.)
2. Using the appropriate optimizer
3. Reasonable configuration of Oracle instance parameters
4. Establish the appropriate index (reduce IO)
5. Separate index and table data on different table spaces (reduce IO conflicts)
6. Create table partitions, store data on separate partitions (space for time, less IO)
Logically optimized:
1. The table can be logically divided, such as the China Mobile User table, can be divided into 10 tables according to the phone mantissa, which will have a certain effect on performance
2. SQL statements use placeholder statements, and the developer must write SQL statements (such as all uppercase, all lowercase, etc.) in the shared pool after Oracle parsing statements
such as: SELECT * from EMP where name=? This statement will only have one in the shared pool, and if it is a string, there are different statements based on different names, so the placeholder is more efficient
3. The database is not only a place to store data, but also a programming place, some time-consuming operations, can be stored in the process, and so on in less users, and thus stagger the peak time used by the system, improve database performance
4. Try not to use the * number, such as SELECT * from EMP, because to be converted to a specific column name is to look up the data dictionary, more time-consuming
5. Select a valid table name
For a multiple-table join query, it is possible that Oracle's optimizer will not be optimized to this extent, and the multiple table queries in Oracle are based on the from right to left data, so the best table on the right (that is, the underlying table) selects less data tables, so the sorting is faster, If there is a link table (many-to-many multiple intermediate tables), the link table will be placed at the far right as the underlying table, and Oracle will automatically optimize by default, but if the optimizer is configured, it may not be automatically optimized, so it's usually best to write SQL in this way
6. Where clause rule
When the WHERE clause in Oracle is handled from right to left, the connection between tables is written before other conditions, the conditions that can filter out very much data are placed at the end of the where, and the columns of the!= symbol comparison do not use the index, and the columns are computed (such as uppercase, etc.) without using the index (need to establish a function), The optimizer does not use the index for IS null, is not NULL
7. Use Exits not Exits instead of in Notin
8. Reasonable use of transactions, the rational establishment of transaction isolation
Database data operations compared to consume database resources, try to use batch processing to reduce the number of transaction operations
7. What symbolic link is used in Oracle string.
Use in Oracle | | This symbolic connection string such as ' abc ' | | ' d '
8. How does Oracle partitioning optimize the database?
Oracle partitions can be divided into: List partitions, range partitions, hash partitions, and composite partitions.
1. Enhanced availability: If one partition of a table is not usable due to a system failure, the rest of the table's partitions can still be used;
2. Reduce shutdown time: If the system failure only affects a portion of the table, then only this part of the partition needs to be repaired, which may take less time than the entire large table repair;
3. Easy maintenance: If you need to build a table, single production management of each public area than the management of a single large table is much easier;
4. Balanced I/O: The different partitions of the table can be assigned to different disks to balance I/O performance improvement;
5. Improve performance: Large table query, add, modify and other operations can be decomposed into the table of different partitions to execute in parallel, can make running faster
6. The partition is transparent to the user and the end user does not feel that the partition exists.
9. How Oracle is paginated.
Oracle uses rownum for paging, which is the most efficient paging method, Hibernate is also used rownum for Oralce paging
SELECT * FROM
(select RowNum r,a from TabName where rownum <= 20)
where r > 10
oralce How to store files and what files to store.
Oracle can store Clob, NCLOB, blobs, bfile
Clob variable-length character data, which is the text-type data type mentioned in other databases
Nclob data of a mutable character type, but it stores character data for the Unicode character set
Blob variable-length binary data
Bfile variable binary data stored outside the database
One . Oracle uses indexed columns, where criteria are queried, grouped, sorted, using aggregate functions, and which are indexed.
are indexed, it is important to note that composite indexes, such as indexes established on columns A and B, may be different
12. How the database is to be backed up every 30 minutes.
To export a database through the operating system's timed task invoke script
performance comparison of Where condition queries and sorting in Oracle.
The criteria by which indexes are used are extremely restrictive and can only be used if the following conditions are true.
The columns in 1.order by must contain the same index and the index order and sort order are always
2. Columns that cannot have null values
So the performance of the sorting is often not high, so it is recommended to avoid order by
14. Explain the differences between cold backup and hot backup as well as their respective advantages.
Cold backup occurs when a database has been shut down properly, copying a key file to a different location
Hot backup is the way to back up data in an archived way while the database is running
Advantages and disadvantages of Lengbei:
1. is a very fast backup method (just copy the file)
2. Easy to archive (simple copy)
3. Easy to recover to a point in time (just copy the file back)
4. Can be combined with the archiving method to restore the "latest state" of the database.
5. Low maintenance, high safety.
Insufficient Cold backup:
1. When used alone, it can only provide a recovery at a point in time.
2. In the process of implementing a backup, the database must be backed up without any other work. In other words, the database must be closed during a cold backup.
3. If disk space is limited, it can only be copied to other external storage devices such as tape, which can be slow.
4. You cannot restore by table or by user.
Advantages and disadvantages of hot standby
1. Can be backed up at the table space or data file level for short backup time.
2. The database is still available for backup.
3. Can reach a second level recovery (revert to a point in time).
4. Almost all database entities can be recovered.
5. Recovery is quick and, in most cases, restored when the database is still working.
The shortage of hot backup is:
1. There is no mistake, otherwise the consequence is serious.
2. If a hot backup is unsuccessful, the resulting results are not available for point-in-time recovery.
3. Because it is difficult to maintain, so be particularly careful, do not allow "failure to end".
15. Explain the difference between data block, extent and segment.
Data block blocks, which are the smallest logical units of Oracle, and typically Oracle reads and writes from disk to block
The extent area is made up of several adjacent blocks.
The segment section is composed of a set of areas
Tablespace table space, where data is logically stored in a database, a tablespace can contain multiple data files
16. Compare truncate and delete commands.
1. Truncate and delete can be said to delete data entities, Truncate operations are not recorded to the rollback log, so the operation is faster, but at the same time the data department can restore
2. Delete operations to free up space in the table space
3. Truncate cannot be deleted on view
4. Truncate is the data definition language (DDL), and delete is the data Manipulation language (DML)
17. Explain what a deadlock is and how to resolve a deadlock in Oracle.
In short, there is a lock without unlocking, may be using the lock did not commit or rollback TRANSACTION, if it is a table-level lock can not operate the table, the client is in the state, if it is row-level lock can not operate locked line
Solution:
1. Find out which table is locked
Select B.owner,b.object_name,a.session_id,a.locked_mode
From V$locked_object a,dba_objects b
where b.object_id = a.object_id;

Select B.username,b.sid,b.serial#,logon_time
From V$locked_object a,v$session b
where a.session_id = B.sid order by B.logon_time;
2. Killing sessions in the process
Alter system kill session ' sid,serial# ';
18. Describe the use of DML, DDL, DCL in Oracle
DML data manipulation languages, such as SELECT, Update, Delete,insert
DDL data definition language, such as CREATE TABLE, droptable, and so on
DCL Data Control language, such as commit, rollback, grant, invoke, etc.
19. Talk about the frequently used functions in Oracle
length, lower lowercase, upper uppercase, to_date conversion date, To_char conversion character
Ltrim go to the left space, RTrim to the right space, substr take strings, add_month add or subtract the month, To_number converted to numbers
20. How do you create a stored procedure, and what are the benefits of using the cursor in a stored procedure?
Attached: General format of stored procedures, cursor use reference questions
1. You can use cursors to perform multiple unrelated operations. If you want a result set to be generated, do a variety of unrelated data operations on the data in the result set
2. Use cursors to provide readability of scripts
3. Using cursors, you can create command strings, use cursors to transfer table names, or transfer variables to parameters in order to establish a command string that can be executed.
But the individual thinks the cursor operation efficiency is not very high, and uses must be specially careful, after using, must close in time
Stored procedure Advantages and disadvantages:
Advantages:
1. Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with strong flexibility to complete complex judgments and complex operations.
2. Ensures data security and integrity.
3. Stored procedures enable users without permission to access the database indirectly under control, thereby ensuring data security.
Stored procedures allow related actions to occur together to maintain the integrity of the database.
3. Before running the stored procedure, the database has been analyzed by syntax and syntax, and the optimal execution scheme is given. This compiled process can greatly improve the performance of SQL statements. Since most of the work on executing the SQL statement has been completed, the stored procedure can be executed at a very fast rate.
4. Can reduce network traffic, do not need to pass the network to send a lot of SQL statements to the database server
5. Put the operational procedures that embody enterprise rules into the database server for centralized control
Change the stored procedure in the server when the enterprise rule changes, without modifying any applications. The characteristic of enterprise rule is to change frequently, if the operation that embodies enterprise rule is put into application, then when enterprise rule changes, need to modify the application workload very much (modify, publish and install application). If you put an operation that embodies an enterprise rule into a stored procedure, you can modify the stored procedure as long as the enterprise rules change, and the application needs no change.
Disadvantages:
1. Poor portability
2. Occupy the server side of the resources, the server caused by a lot of pressure
3. Readability and maintainability are not good

Create [or Replace] procedure the procedure name (parameter ...). As
Vs_ym_sn_end CHAR (6); – Terminating month in the same period
CURSOR cur_1 is– defines a cursor (simply a result set that can be traversed)
SELECT area_code,cmcode,sum (rmb_ AMT)/10000 Rmb_amt_sn,sum (Usd_amt)/10000usd_amt_sn
from bgd_area_cm_m_base_t
WHERE ym >= Vs_ym_sn_beg
and ym <= Vs_ym_sn_end
GROUP by Area_code,cmcode;
BEGIN
– Assign an initial value to a variable with an input parameter and use a very common function such as the Oralce Substrto_char add_months to_date.
Vs_ym_beg: = SUBSTR (is_ym,1,6);
Vs_ym_end: = SUBSTR (is_ym,7,6);
Vs_ym_sn_beg: = To_char (Add_months (To_date (Vs_ym_beg, ' yyyymm '), -12), ' yyyymm ');
Vs_ym_sn_end: = To_char (Add_months (To_date (vs_ym_end, ' yyyymm '), -12), ' yyyymm ');
– Deletes the data for a specific condition in the table first.
DELETE from xxxxxxxxxxx_t WHERE ym = Is_ym;
– and then print the number of rows of records affected with the Put_Line method of the built-in Dbms_output object, which uses a system variable Sql%rowcount
Dbms_output.put_line (' del last month's record = ' | | sql%rowcount| | ' Article ");

INSERT intoxxxxxxxxxxx_t (Area_code,ym,cmcode,rmb_amt,usd_amt)
SELECT area_code,is_ym,cmcode,sum (Rmb_amt)/10000,sum (Usd_amt)/10000
From bgd_area_cm_m_base_t
WHERE ym >= Vs_ym_beg
and YM <= Vs_ym_end
GROUP by Area_code,cmcode;

Dbms_output.put_line (' ins month record = ' | | sql%rowcount| | ' Article ");
– Update to the table after traversing the cursor processing. There are several ways to traverse a cursor, and a for statement is one of the more intuitive.
For rec in cur_1 LOOP
UPDATE xxxxxxxxxxx_t
SET rmb_amt_sn = REC.RMB_AMT_SN,USD_AMT_SN = Rec.usd_amt_sn
WHERE Area_code = Rec.area_code
and Cmcode = Rec. Cmcode
and ym = Is_ym;
End LOOP;
COMMIT;
– The error-handling section. Others represents any error except the declaration. SQLERRM is the system built-in variable that holds the details of the current error.
EXCEPTION
When others THEN
Vs_msg: = ' ERROR in xxxxxxxxxxx_p (' | | is_ym| | '): ' | | SUBSTR (sqlerrm,1,500);
ROLLBACK;
– Record the current error in the Log table.
INSERT into Log_info (proc_name,error_info,op_date)
VALUES (' xxxxxxxxxxx_p ', vs_msg,sysdate);
COMMIT;
return;
End;

21. How to create an index that uses the principles of indexing, what are the advantages and disadvantages
To create a standard index:
CREATE index index name on table name (column name) tablespace table space name;
To create a unique index:
CREATE Unique index index name on table name (column name) tablespace table space name;
To create a composite index:
CREATE index index name on table name (column name 1, column name 2) tablespace table space name;
To create a reverse key index:
CREATE index index name on table name (column name) reverse tablespace table space name;
Index usage principles:
Indexed fields recommend NOT NULL constraint
Tables that are often connected to other tables should be indexed on the connection field;
Fields that often appear in the WHERE clause and are highly filtered, especially in large tables, should be indexed;
High-selectivity keywords should be indexed;
Optional low keyword, but the data value distribution is very large, the selectivity data is relatively small, still can use index to improve efficiency
The establishment of a composite index requires careful analysis; Consider using a single field index instead:
A, the correct selection of the first field in the composite index is generally a good selectivity and in the WHERE clause commonly used fields;
B, several fields of a composite index often appear in the WHERE clause in the same way as the compound index, otherwise the single field index;
C, if the fields contained in the composite index often appear separately in the WHERE clause, they are decomposed into multiple single field indexes;
D, if the composite index contains more than 3 fields, consider the need to reduce the compound field carefully;
E, if both the Single field index, and the composite index on these fields, you can generally delete the composite index;
Frequently DML tables, do not build too many indexes;
Do not use columns that are frequently modified as indexed columns;
Advantages and disadvantages of indexes:
Little
1. Create a unique index to ensure uniqueness of each row of data in a database table
2. Greatly speed up the retrieval of data, which is the main reason to create indexes
3. The acceleration of the connection between tables and tables, especially with regard to the realization of the referential integrity of the data, is particularly relevant.
4. When you use grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in a query.
Disadvantages:
1. Indexing is created on a table and cannot be created on a view
2. It takes time to create indexes and maintain indexes, which increase as the amount of data increases
3. The index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies certain physical space, if wants to establish the clustered index, then needs the space to be bigger
4. When the data in the table to add, delete and modify the time, the index will also be dynamic maintenance, reduce the data maintenance speed

22. How do I create a view, the benefit of the view, and the view can control permissions?
Create view view name as select column name [alias] ... from table [Unio[all] Select ...]
Benefits:
1. The view can be simply interpreted as a SQL query statement, the most benefit of the view is not to occupy the system space
2. Some highly secure systems do not advertise the system's table structure, and may use views to put some sensitive information over or renamed to advertise the structure
3. Simplified query
You can control permissions, and you need to grant the user permission to use the view when you use it
23. How to create a trigger, the definition of a trigger, how a trigger cursor is defined
CREATE [OR REPLACE] Tigger Trigger name trigger Time trigger Event
On table name
[For each ROW]
BEGIN
Pl/sql statement
CURSOR cursor name is SELECT * FROM table name (definition cursor)
End
which
Trigger Name: The name of the trigger object.
Since triggers are automatically executed by the database, the name is only a name and has no real purpose.
Trigger time: Indicates when the trigger executes, and this value is preferable:
Before-indicates that the trigger executes before the database action;
After-represents the departure execution after the database action.
Trigger event: Indicates which database actions trigger this trigger:
Insert: Database insert triggers this trigger;

Oracle Create a table in several ways; what should be noted
I don't know if the subject is wrong, it's weird.
1. Create a table using a graphical tool
2. Create a table using data DDL statements
3. You can create tables dynamically in Plsql code
It should be noted whether there is permission to create a table, what table space to use, and so on
25. How to move an old database data to a new database
1. Imp/exp to import data from the database into the new Couchen
2. If a storage migration directly hangs the storage device on the new machine
26. There are several primary keys;
Character type, integer type, compound type
Oracle Lock and several, define what is the difference;
1. Row shared lock (row SHARE)
2. Row exclusive lock (row EXCLUSIVE)
3. Shared Locks (SHARE)
4. Shared row exclusive lock (SHARE row EXCLUSIVE)
5. Exclusive Lock (EXCLUSIVE)
How to use:
SELECT * from Order_master WHERE vencode= ' v002′
for UPDATE wait 5;
LOCK TABLE order_master in SHARE MODE;
LOCK TABLE itemfile in EXCLUSIVE MODE nowait;
Oracle locks are specifically grouped into the following categories:
1. According to user and System division, can be divided into automatic lock and display lock
Automatic Lock: When a database operation is performed, the system automatically obtains all necessary locks for this database operation by default.
Display Lock: In some cases, the data to be used by the user to display the locked database operation can be performed better, and the display lock is set by the user for the database object.
2. Divided into shared and exclusive locks by lock level
Shared locks: Shared locks enable a transaction to share access to a particular database resource-another transaction can also access the resource or obtain the same shared lock. Shared locks provide high concurrency for transactions, but poor transactional design + shared locks can easily cause deadlocks or data updates to be lost.
Exclusive lock: After a transaction sets the exclusive lock, the transaction obtains this resource separately, and another transaction cannot obtain a shared or exclusive lock on the same object before this transaction is committed.
3. According to Operation division, can be divided into DML locks, DDL locks
DML locks can be divided into, row locks, table locks, deadlock
Row lock: When a transaction performs a database insert, UPDATE, delete operation, the transaction automatically obtains the exclusive lock of the action row in the action table.
Table-level Lock: When a transaction obtains a row lock, the transaction also automatically obtains the table lock (shared lock) of the row to prevent other transactions from making DDL statements that affect the update of the record row. A transaction can also acquire a shared or exclusive lock during the process, only if the transaction shows an exclusive lock that is displayed using the Locktable statement, the transaction obtains the exclusive lock on the table, or the shared lock that defines a table level that is displayed by using the Lock table (lock Please refer to the relevant documentation for the specific use of the table.
Deadlock: Deadlock occurs when two transactions require a conflicting set of locks and cannot continue the transaction.
For example, transaction 1 has a row of locks in the record #3 of Table A and waits for transaction 2 to record the release of exclusive locks in #4 in table A, while transaction 2 has a row of locks in table a record row #4 and waits for the transaction; 1 in Table A record the release of the exclusive lock in #3, transaction 1 waits with transaction 2, thus causing a deadlock. Deadlocks are usually caused by poor transactional design. Deadlocks can only be used under SQL: Alter system kill session "sid,serial#", or use the commands of the associated operating system kill process, such as the Kill-9 Sid under Unix, or use other tools to kill the deadlock process.
DDL locks can be divided into: Exclusive DDL locks, shared DDL locks, analysis locks
Exclusive DDL Lock: The DDL statement that creates, modifies, deletes a database object obtains the exclusive lock of the action object. If the ALTER TABLE statement is used, the transaction obtains a row of its DDL locks in order to maintain data completion, consistency, and legality.
Shared DDL Locks: DDL statements that need to establish interdependencies between database objects often need to be shared to obtain DDL locks.
If you create a package in which the procedure and function refer to a different database table, the transaction obtains a shared DDL lock that references the table when the package is compiled.
Analysis Lock: Oracle uses shared pools to store parsed and optimized SQL statements and PL/SQL programs, making it faster to run the same statement. An object cached in a shared pool obtains an analysis lock on the database object it references. An analysis lock is a unique type of DDL lock that Oracle uses to track dependencies between shared pool objects and the database objects it references. When a transaction modifies or deletes a database object in which the shared pool holds an analysis lock, Oracle invalidates the object in the shared pool, and the next time the Sql/plsql statement is referenced, Oracle analyzes the compilation of the statement.
4. Internal latch
Internal latch: This is a special lock in Oracle for sequential access to the internal system structure. In order to use this block of memory when a transaction needs to write information to the buffer, Oracle must first obtain the latch of the memory area to write information to this block of memory.

28. How to invoke an Oracle stored procedure in the Java species;
Use CallableStatement in Java to call a stored procedure
to create the required test table: The CREATE TABLE Test (Tid varchar2 (a), tnamevarchar2);
First case: no return value.
Create or Replace procedure test_a (param1 in Varchar2,param2 invarchar2) as
begin
INSERT INTO test value (param1 , param2);
End;
Java calling code:
Package Com.test
Import java.sql.*;
Import java.io.*;
Import java.sql.*;
public class Testproca
{
Public testproca () {
}
public static void Main (String []args)
{

ResultSet rs = null;
Connection conn = null;
callablestatement proc = null;
try{
Class.forName ("Oracle.jdbc.driver.OracleDriver");
conn = Drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:test", "Test", "test");
proc = Conn.preparecall ("{Call Test_a (?,?)}");
Proc.setstring (1, "1001″");
Proc.setstring (2, "Testa");
Proc.execute ();
}catch (Exception e) {
E.printstacktrace ();
}finally{
try{
if (null!=rs) {
Rs.close ();

if (Null!=proc) {
Proc.close ();
}

if (null!=conn) {
Conn.close ();
}
}
}catch (Exception ex) {

}
}
}

}

Second scenario: A stored procedure with a return value (the return value is not a list).

Stored procedures are:
Create or Replace procedure Test_b (param1 in varchar2,param2 out varchar2)
As
Begin
Select Tname into param2 from Test where tid=param1;
End

Java calling code:

Package com.test;

Import java.sql.*;

Import java.io.*;

Import java.sql.*;

public class TESTPROCB
{
Public Testprocb () {

}
public static void Main (String []args)
{

Connection conn = null;
callablestatement proc = null;

try{
Class.forName ("Oracle.jdbc.driver.OracleDriver");
conn = Drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:test", "Test", "test");
proc = Conn.preparecall ("{Call Test_b (?,?)}");
Proc.setstring (1, "1001″");
Proc.registeroutparameter (2, Types.varchar);
Proc.execute ();
System.out.println ("Output is:" +proc.getstring (2));
}catch (Exception e) {
E.printstacktrace ();
}finally{
try{

if (Null!=proc) {
Proc.close ();
}

if (null!=conn) {
Conn.close ();
}

}catch (Exception ex) {

}
}
}

}

Third scenario: Returns the list.

Because the Oracle stored procedure does not return a value, all of its return values are replaced by out parameters, and the list is no exception, but because it is a collection, it is not possible to use the general parameters, you must use Pagkage. To write in two parts:
Create or replace package tpackage as
Type t_cursor is REF CURSOR;
Procedure Test_c (C_ref out t_cursor);
End;

Create or replace package body tpackage as
Procedure Test_c (C_ref out T_cursor) is
Begin
Open C_ref for SELECT * from test;
End Test_c;
End Tpackage;

Java calling code:

Package com.test;

Import java.sql.*;

Import java.io.*;

Import java.sql.*;

public class TESTPROCB
{
Public Testprocb () {

}

public static void Main (String []args)
{

Connection conn = null;
callablestatement proc = null;
ResultSet rs = null;
try{
Class.forName ("Oracle.jdbc.driver.OracleDriver");
conn = Drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:test", "Test", "test");
proc = Conn.preparecall ("{? = Call Tpackage.test_b (?)} ");

Proc.registeroutparameter (1,oracletypes.cursor);
Proc.execute ();
while (Rs.next ()) {
System.out.println (Rs.getobject (1) + "T" +rs.getobject (2));
}
}catch (Exception e) {
E.printstacktrace ();
}finally{
try{
if (null!=rs) {
Rs.close ();
if (Null!=proc) {
Proc.close ();
}

if (null!=conn) {
Conn.close ();
}
}
}catch (Exception ex) {

}
}
}

}

The definition of

rowid,rownum
1. rowID and rownum are virtual columns
2. ROWID is the physical address used to locate the physical storage location for specific data in Oracle
3. RowNum is the order of the output of SQL, which can be seen from the following examples.
30. oracle of stored procedures, cursors, and functions the
cursor is similar to a pointer, and a cursor can perform multiple unrelated operations. If you want to produce a result set, do a variety of unrelated data operations on the data in the result sets A
function can understand that a function is a stored procedure; a function can have no parameters, but it must need a return value, the stored procedure can have no parameters, no return value, both can return a value through the out parameter, and it is recommended that you use a stored procedure if you need to return more than one parameter ; You can only call a function in a SQL data manipulation statement, and you cannot call a stored procedure

31.Oracle The difference between stored procedures and functions. The
looks at the return of a parameter:
It's best to use a stored procedure if you return multiple parameter values, or you can use a function if you have only one return value;
from the call:
If invoked in a SQL statement (DML or select) it must be stored functions or stored encapsulation functions can not be stored procedures, but there are many restrictions on the invocation of the function and the purity of the function of the problem, you can refer to the "oracle 9i Pl\sql program Design" (Mechanical industry Press);
If it is invoked in a procedural statement, it depends on what functionality you want to implement. Functions are typically used to compute and return a calculated result, and stored procedures are typically used to perform specific data operations (such as modifying, insert database tables or execute some DDL statements, and so on), so although they are syntactically similar, the features that users need to perform when they use them are mostly different. How the
32.Oracle unlocks.
combines the v$locked_objects view with the V$session view. Find Sid and Serial# and Wrap (code) with the Altersystem kill session ' sid,serial# '
33.ext text editor.
grid.store.on (' Load ', function () {
Gridel.select ("table[class=x-grid3-row-table]"). each (function (x) {
X.addclass (' x-grid3-cell-text-visible ');
});
});
css file contents:
. x-grid3-cell-text-visible. x-grid3-cell-inner{overflow:visible;padding:3px 3px3px 5px;white-space:normal;}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.