[Sorting] Oracle interview questions (basic)

1. What is the difference between Oracle and SQL Server 2005?
MACRO:
1 ). the biggest difference is that oracle can run on different platforms, and SQL server can only run on windows platforms. The stability and security of windows platforms affect the stability and security of SQL server.
2). The scripting language used by oracle is the PL-SQL, and the script used by SQL server is the T-SQL
Micro-level: answers from data types, database structures, and so on

2. How to use Oracle cursor?
1). the cursor in oracle is divided into display cursor and implicit cursor.
2 ). cursor is used to display the cursor... is command-defined cursor, which can process multiple records returned by the query statement (select); implicit cursor is executing insert, delete, and update) and the query (select) statement that returns a single record is automatically defined by PL/SQL.
3) Explicit cursor operations: open, operate, and close the cursor; PL/SQL implicitly open the SQL cursor, and process the SQL statement inside it, and then close it

3. What is the difference between function and procedure in Oracle?
1). It is understandable that a function is a type of stored procedure.
2). The function can have no parameters, but a return value must be required. The stored procedure can have no parameters and no return value is required.
3) the return value of the function does not return the parameter mode. The stored procedure uses the out parameter to return the value. If you need to return multiple parameters, we recommend that you use the stored procedure.
4). In SQL data manipulation statements, only functions can be called, but stored procedures cannot be called.

4. What are the differences between Oracle Import and Export methods?
1). Use the oracle tool exp/imp
2). Use plsql related tools
Method 1. binary data is imported/exported; 2. plsql imports/exports text files of SQL statements.
5. What types of files are available in Oracle?
Data Files (generally suffixed with. dbf or. ora), log files (suffix. log), and control files (suffix. ctl)

6. How can I optimize the Oracle database?
I personally understand that the most important factor in database performance is I/O, because the operation memory is fast, but the read/write speed is very slow. The most critical problem in optimizing the database is to reduce the disk I/O, personal Understanding should be divided into physical and logical optimization. Physical optimization refers to the optimization of oracle products and logical optimization refers to application-level optimization.
Some principles of physical optimization:
1). Oracle operating environment (Network, hardware, etc)
2). Use the appropriate Optimizer
3) reasonably configure oracle instance Parameters
4). Create appropriate indexes (reduce IO)
5). Separate the index data and table data in different tablespaces (reduce IO conflicts)
6) create a table partition and store the data in different partitions respectively (Exchange Space for time and reduce IO)
Logical optimization:
1) The table can be logically divided. For example, the China Mobile User table can be divided into 10 tables based on the number of mobile phone tails, which will play a role in performance.
2). SQL statements use placeholder statements, and SQL statements (such as all uppercase and lowercase letters) must be compiled during development. oracle parsing statements are placed in the shared pool.
For example, select * from Emp where name =? This statement only has one entry in the Shared Pool. If it is a string, different statements exist based on different names, so the placeholder efficiency is better.
3 ). A database is not just a place to store data, but also a programming place. Some time-consuming operations can be performed in the case of few users, such as stored procedures, in this way, the peak time used by the system is staggered to improve the database performance.
4) Try not to use the * number, for example, select * from Emp, because it is time-consuming to convert to a specific column name to query the data dictionary.
5) Select a valid table name.
For multi-table join queries, the oracle optimizer may not be optimized to this level. In oracle, multi-table queries are performed based on data FROM right to left in the FROM clause, in this case, it is best to select a table with less data in the right (that is, the basic table), so that the sorting is faster. If there is a link table (Multiple-to-multiple intermediate tables ), the link Table is placed on the rightmost side as the base table. oracle will automatically optimize the link Table by default. However, if the optimizer is configured, The link Table may not be automatically optimized, therefore, it is best to write SQL statements in this way.
6). Where clause rules
The Where clause in Oracle is processed from right to left. before other conditions are written, the join between tables can filter out many data conditions and put them at the end of the where clause! = Indexes are not used for a column to be compared with symbols. indexes are not used for a column after calculation (for example, if it is changed to uppercase) (a function is required ), is null, is not null, and other optimizers do not use Indexes
7). Replace In Not in with Exits Not Exits
8) reasonably use transactions and reasonably set transaction isolation
Database Data Operations consume database resources. Use batch processing whenever possible to reduce the number of transaction operations

7. What symbols are used to link strings in Oracle?
Used in Oracle | connection string such as 'abc' | 'D'
8. How Does Oracle partition optimize the database?
Oracle partitions can be divided into list partitions, range partitions, hash partitions, and composite partitions.
1). Enhanced availability: If a partition in the table cannot be used due to system failure, the remaining good partitions in the table can still be used;
2). Reduce the shutdown time: if the system failure only affects one partition of the table, only this partition needs to be repaired, which may take less time to repair than the entire large table;
3). Easy Maintenance: If you need to create a table, it is much easier to manage each public partition in a single production environment than to manage a single large table;
4). Balanced I/O: You can allocate different partitions of the table to different disks to balance I/O and improve performance;
5). Improved performance: operations such as querying, adding, and modifying large tables can be divided into different partitions of the table for parallel execution, which can speed up the operation.
6) partitions are transparent to users, and the end user does not feel the existence of partitions.

9. How is Oracle paging?
In Oracle, rownum is used for paging. This is the most efficient paging method. hibernate also uses rownum for oralce paging.
Select * from
(Select rownum r, a from tabName where rownum <= 20)
Where r> 10
10. How does Oralce store files and what files can be stored?
Oracle can store clob, nclob, blob, and bfile
Clob variable-length structured data, that is, text data types mentioned in other databases
Nclob variable character type data, but it stores the character data of the Unicode Character Set
Binary data with variable Blob Length
Variable binary data stored outside the Bfile Database
11. Oracle uses an indexed column to perform where condition query, grouping, sorting, and clustering for this column. Which indexes are used?
Indexes are used. It is worth noting that compound indexes (for example, the indexes created on column A and column B) may vary.
12. How can I back up a database every 30 minutes?
Export the database using the script for calling scheduled tasks of the Operating System

13. What is the performance comparison between where condition query and sorting in Oracle?
Order by has very strict conditions for using indexes. indexes can be used only when the following conditions are met,
1). the columns in order by must contain the same index and the index order is the same as the sorting order.
2). Columns with null values are not allowed.
Therefore, the sorting performance is usually not high, so we recommend that you avoid order by as much as possible.

14. What are the differences and advantages of cold backup and hot backup?
Cold backup occurs when the database is normally shut down. It is a statement to copy key files to another location.
Hot Backup is a method for backing up data in archive mode when the database is running.
Advantages and disadvantages of Cold Standby:
1). It is a very fast backup method (just copy files)
2). Easy to archive (simple copy)
3). It is easy to restore to a certain point in time (you only need to copy the file back)
4). It can be combined with the archiving method to restore the database to the "latest State.
5). Low maintenance and high security.
Insufficient cold backup:
1). When used separately, only recovery at a certain time point can be provided.
2). During the whole backup process, the database must be backed up instead of other work. That is to say, in the cold backup process, the database must be closed.
3) if the disk space is limited, it can only be copied to tape and other external storage devices, and the speed will be very slow.
4). You cannot restore data by table or by user.

Advantages and disadvantages of hot standby
1). It can be backed up at the tablespace or data file level, with a short backup time.
2) The database is still available during Backup.
3). It can be restored in seconds (recovered to a certain point in time ).
4). Almost all database entities can be restored.
5). Recovery is fast and can be recovered when the database is still working in most cases.
Hot Backup is insufficient:
1). No error is allowed; otherwise, the consequences are serious.
2) If the Hot Backup fails, the result cannot be used for restoration at a time point.
3) because it is difficult to maintain, be careful and do not allow "end with failure ".

15. What is the difference between data block, extent, and segment?
Data block data blocks are the smallest logical unit in oracle. Generally, oracle reads and writes data blocks from disks.
The extent area is composed of several adjacent blocks.
Segment, which is composed of a group of zones
Tablespace, where data is stored logically in the database. A tablespace can contain multiple data files.
16. Compare the truncate and delete commands?
1). Both Truncate and delete can delete data entities. The truncate operations do not record rollback logs, so the operation speed is fast,However, the data cannot be recovered.
2) The Delete operation does not free up space for the tablespace.
3). Truncate cannot delete views.
4). Truncate is the Data Definition Language (DDL), while delete is the data control language (DML)
17. What is a deadlock and how to solve the deadlock in Oracle?
In short, the lock is not unlocked. It may be because the lock is not committed or rolled back the transaction. If it is a table-Level Lock, the table cannot be operated, and the client is in a waiting state, row-level locks cannot be used to lock rows.
Solution:
1). Find the locked table
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) Kill sessions in the process
Alter system kill session "sid, serial #";
18. Briefly describe the use of dml, ddl, and dcl in oracle
Dml data manipulation language, such as select, update, delete, and insert
Ddl data definition language, such as create table and drop table
Dcl data control language, such as commit, rollback, grant, and invoke
19. Let's talk about the frequently used functions in oracle.
Length, lower case, upper case, to_date conversion date, to_char conversion character
Ltrim goes to the left space, rtrim goes to the right space, and substr takes the string, add_month increases or loses the month, to_number is converted to a number
20. How to Create a stored procedure and how to use the cursor in the stored procedure? What are the advantages?
Appendix: general format of the stored procedure. For details about how to use the cursor
1. You can use a cursor to perform multiple unrelated operations. If you want to perform multiple unrelated data operations on the data in the result set after the result set is generated
2. Using a cursor can provide readability of the script
3. You can use the cursor to create a command string, use the cursor to transfer the table name, or send the variable to the parameter to create a command string that can be executed.
However, I personally think that the cursor operation is not very efficient and should be very careful when using it. Close it in time after use.
Advantages and disadvantages of stored procedures:
Advantages:
1. Stored Procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation.
2. Data security and integrity can be ensured.
3. stored procedures allow unauthorized users to indirectly access the database under control to ensure data security.
Through the stored procedure, related actions can be taken together to maintain the integrity of the database.
3. Before the stored procedure is re-run, the database has analyzed its syntax and provided an optimization execution plan. This compiled process can greatly improve the performance of SQL statements. Since most of the SQL statement execution has been completed, the stored procedure can be executed very quickly.
4. reduces network traffic and does not need to transmit many SQL statements to the database server.
5. Add the computing program that reflects the enterprise rules to the database server for centralized control
When enterprise rules change, you can change the stored procedure on the server without modifying any applications. Enterprise rules are characterized by frequent changes. If the computing program that reflects the enterprise rules is put into the application, when the enterprise rules change, it takes a lot of effort to modify the application (modifying, releasing, and installing the application ). If you put the operations that reflect the enterprise rules into the stored procedure, when the enterprise rules change, you only need to modify the stored procedure, and the application does not need to change.
Disadvantages:
1. Poor portability
2. It occupies many resources on the server side, which puts a lot of pressure on the server.
3. Poor readability and maintainability

Create [or replace] procedure process name (parameter ...) As
Vs_ym_sn_end CHAR (6); -- end month of the same period
CURSOR cur_1 IS -- defines the CURSOR (simply put, it IS a result set that can be traversed)
SELECT area_code, CMCODE, SUM (RMB _amt)/10000 RMB _amt_sn, SUM (usd_amt)/10000 usd_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
-- Input parameters are used to assign the initial value to the variable, and common functions such as SUBSTR TO_CHAR ADD_MONTHS TO_DATE of Oralce are used.
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 ");
-- Delete data with specific conditions in the table first.
Delete from xxxxxxxxxxx_T WHERE ym = is_ym;
-- Then use the put_line method of the built-in DBMS_OUTPUT object to print the number of affected records. One system variable SQL % rowcount is used.
DBMS_OUTPUT.put_line ("del last month record =" | SQL % rowcount | "items ");

Insert into xxxxxxxxxxx_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 current month records =" | SQL % rowcount | "items ");
-- The traversal cursor is processed and updated to the table. You can use the for statement to traverse a cursor in several ways.
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;
-- Error handling section. OTHERS indicates any errors except declarations. SQLERRM is a built-in variable that saves detailed information about the current error.
EXCEPTION
WHEN OTHERS THEN
Vs_msg: = "error in xxxxxxxxxxx_p (" | is_ym | "):" | SUBSTR (SQLERRM, 1,500 );
ROLLBACK;
-- Record the current error to the log table.
Insert into LOG_INFO (proc_name, error_info, op_date)
VALUES ("xxxxxxxxxxx_p", vs_msg, SYSDATE );
COMMIT;
RETURN;
END;
21. What are the advantages and disadvantages of creating one index and indexing principles?
Create a Standard Index:
Create index name ON table name (column name) TABLESPACE name;
Create a unique index:
CREATE unique INDEX name ON table name (column name) TABLESPACE name;
Create a composite index:
Create index name ON table name (column name 1, column name 2) TABLESPACE name;
Create a reverse key index:
Create index name ON table name (column name) reverse TABLESPACE table space name;
Indexing principles:
The not null constraint is recommended for index fields.
For tables that are often connected to other tables, indexes should be created on the connection fields;
Fields that frequently appear in the Where clause are highly filterable, especially those in large tables, and should be indexed;
Indexes should be created for highly selective keywords;
Low-selectivity keywords. However, when the value distribution of data varies greatly, indexes can still be used to improve efficiency when the selected data is small.
The establishment of a composite index requires careful analysis. Try to replace it with a single field index:
A. correctly select the first field in the composite index. Generally, it is highly selective and commonly used in the where clause;
B. The fields of the composite index often appear in the Where clause in the AND mode at the same time. Otherwise, a single field index can be created;
C. If the fields contained in the composite index are often contained in the Where clause, they are divided into multiple single-field indexes;
D. If the composite index contains more than three fields, consider the necessity and reduce the composite fields;
E. If there are both single-field indexes and composite indexes on these fields, you can delete composite indexes;
Frequent DML tables, do not create too many indexes;
Do not use frequently modified columns as index columns;
Advantages and disadvantages of indexes:
A bit:
1. Create a unique index to ensure the uniqueness of each row of data in the database table
2. The data retrieval speed is greatly accelerated, which is also the main reason for index creation.
3. Accelerate the connection between tables, especially for Data Reference integrity.
4. When you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.
Disadvantages:
1. indexes are created on tables and cannot be created on views.
2. It takes time to create and maintain indexes. This time increases with the increase of data volume.
3. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space will be larger.
4. When adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed.

22. How can I create a view? Does the view have permission control?
Create view name as select column name [alias]… From table [unio [all] select... ]
Benefits:
1. The view can be simply understood as an SQL query statement. The biggest benefit of the view is that it does not occupy system space.
2. Some systems with high security will not publish the system's table structure, and may use views to overwrite or rename sensitive information and then publish the structure.
3. Simplified Query
If you can control permissions, grant the view permission to the user.
23. How to Create a trigger, trigger definition, and trigger cursor Definition
CREATE [or replace] TIGGER trigger name trigger time trigger event
ON Table Name
[For each row]
BEGIN
Pl/SQL statements
CURSOR name is SELECT * FROM Table Name (defining CURSOR)
END
Where:
Trigger name: name of the trigger object.
Because the trigger is automatically executed by the database, the name is only a name and has no substantive purpose.
Trigger time: specifies when the trigger is executed. This value is optional:
Before --- indicates that the trigger is executed before the database action;
After --- indicates that the logpilot executes the database action.
Trigger event: Specifies which database actions will trigger this trigger:
Insert: This trigger is triggered when the database is inserted;

24. Several ways to create tables in oracle; what should I pay attention?
I wonder if this question is wrong.
1. Use a graphical tool to create a table
2. Use the data ddl statement to create a table
3. You can dynamically create tables in plsql code.
Note: whether you have the table creation permission and the tablespace used.
25. How to move an old database data to a new database
1. Imp/exp import data from the database to the new database
2. For storage migration, directly mount the storage device to the new machine.
26. There are several primary keys;
Complex, integer, and compound
27. There are several oracle locks to define;
1. ROW SHARE)
2. ROW EXCLUSIVE)
3. SHARE)
4. SHARE ROW EXCLUSIVE)
5. EXCLUSIVE)
Usage:
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 divided into the following types:
1. automatic locks and display locks can be divided by users and systems.
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, you need to display the data required to lock database operations to make database operations better. The display lock is set by the user for database objects.
2. Locks can be divided into shared locks and exclusive locks.
Shared lock: A Shared lock allows a transaction to share access to a specific database resource. Another transaction can also access this resource or obtain the same shared lock. Shared locks provide high concurrency for transactions, but poor transaction design + Shared locks may cause deadlocks or data update loss.
Exclusive lock: After an exclusive lock is set for a transaction, the transaction obtains the resource separately. Another transaction cannot obtain the share lock or exclusive lock of the same object before the transaction is committed.
3. Divided by operation, it can be divided into DML locks and DDL locks
DML locks can be divided into row locks, table locks, and deadlocks.
Row lock: when a transaction performs database insert, update, or delete operations, the transaction automatically obtains the row exclusive lock of the operation table.
Table-Level Lock: after a transaction acquires a row lock, the transaction automatically acquires the table lock (shared lock) of the row to prevent other transactions from affecting the update of record rows by using DDL statements. A transaction can also obtain a shared or exclusive LOCK during the process. Only when the transaction displays the definition of an exclusive LOCK displayed by the lock table statement will the transaction obtain the exclusive LOCK on the TABLE, you can also use the lock table display to define a TABLE-level shared LOCK (refer to the relevant documentation for specific usage of the lock table ).
Deadlock: a deadlock occurs when two transactions need a set of conflicting locks and cannot continue the transaction.
For example, if transaction 1 records row #3 in Table A and waits for transaction 2 to record in Table A #4 to release the exclusive lock, transaction 2 records row #4 in table A and waits for the transaction. 1 records row #3 in Table A and releases the exclusive lock. Transaction 1 and transaction 2 wait for each other, therefore, a deadlock occurs. Deadlock is generally caused by poor transaction design. Only SQL statements can be used for deadlocks: alter system kill session "sid, serial #"; or commands for kill processes in the relevant operating system, such as kill-9 sid in UNIX, or use other tools to kill the deadlock process.
DDL locks can be divided into: exclusive DDL locks, shared DDL locks, and analysis locks.
Exclusive DDL lock: create, modify, and delete DDL statements of a database object to obtain the exclusive lock of the operation object. For example, when an alter table statement is used, the transaction obtains a row of DDL locks to maintain data integrity, consistency, and legitimacy.
Share DDL lock: DDL statements that need to establish dependency between database objects usually need to share to obtain the DDL lock.
If you create a package, the process in the package is different from that in the function reference database table. When this package is compiled, the transaction obtains the shared DDL lock of the referenced table.
Analysis lock: ORACLE uses a shared pool to store analysis and optimized SQL statements and PL/SQL programs, making applications that run the same statement faster. An object cached in the Shared Pool obtains the analysis lock of the database object it references. Analytics lock is a unique DDL lock type. ORACLE uses it to track Shared Pool objects and dependencies between referenced database objects. When a transaction modifies or deletes a database object holding an analysis lock in the Shared Pool, ORACLE invalidate the object in the shared pool. The next time you reference this SQL/PLSQL statement, ORACLE re-analyzes and compiles this statement.
4. Internal locks
Internal lock: this is a special lock in ORACLE for sequential access to the internal system structure. When writing information to the buffer zone, in order to use this block of memory area, ORACLE must first obtain the latch of this block of memory area to write information to this block of memory.

28. How to call the oracle stored procedure in java;
Use CallableStatement in java to call the Stored Procedure
Create a required Test table: create table Test (tid varchar2 (10), tname varchar2 (10 ));
First case: no return value.
Create or replace procedure test_a (param1 in varchar2, param2 in varchar2)
Begin
Insert into test value (param1, param2 );
End;
Java call 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: @ FIG: 1521: test", "test", "test ");
Proc = conn. prepareCall ("{call test_a (?,?) }");
Proc. setString (1, "1001 ");
Proc. setString (2, "TestA ");
Proc.exe cute ();
} 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 ){
}
}
}
}

Case 2: stored procedure with a returned value (the returned value is not a list ).
The stored procedure is:
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 call 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: @ FIG: 1521: test", "test", "test ");
Proc = conn. prepareCall ("{call test_ B (?,?) }");
Proc. setString (1, "1001 ");
Proc. registerOutParameter (2, Types. VARCHAR );
Proc.exe cute ();
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 ){
}
}
}
}

Case 3: Return to the list.

Since the oracle stored procedure does not return values, all of its return values are replaced by the out parameter, and the list is no exception. However, because it is a set, general parameters cannot be used, you must use pagkage. it should be written in two parts:
Create or replace package tpackage
Type t_cursor is ref cursor;
Procedure test_c (c_ref out t_cursor );
End;

Create or replace package body tpackage
Procedure test_c (c_ref out t_cursor) is
Begin
Open c_ref for select * from test;
End test_c;
End tpackage;

Java call 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: @ FIG: 1521: test", "test", "test ");
Proc = conn. prepareCall ("{? = Call tpackage. test_ B (?) }");

Proc. registerOutParameter (1, OracleTypes. CURSOR );
Proc.exe cute ();
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 ){
}
}
}
}

29. rowid, rownum Definition
1. Both rowid and rownum are virtual columns.
2. rowid is a physical address used to locate the physical storage location of specific data in oracle
3. rownum is the sorting of SQL output results. The differences are shown in the following example.
30. Differences between stored procedures, cursors, and functions in oracle
A cursor is similar to a pointer. A cursor can perform multiple unrelated operations. If you want to perform multiple unrelated data operations on the data in the result set after the result set is generated
A function can be understood as a type of stored procedure. A function can have no parameters, but must have a return value. A stored procedure can have no parameters and does not have a return value. Both functions can return values through the out parameter, if you need to return multiple parameters, we recommend that you use stored procedures. In SQL data manipulation statements, you can only call functions but not stored procedures.
31. Use the oracle pseudo column to delete duplicate records in the table:
Delete table t where t. rowid! = (Select max (t1.rowid) from table1 t1 where t1.name = t. name)