N Date: 2004-06-10
2 SELECT T1. *, T2.NAME AS TABLE_NAME FROM V $ LOCK T1, SYS.OBJ $ T2 Where T1.ID1 = T2.Obj #
The above statement can detect the lock of the current database table, where the SID represents the session ID number, Table_name indicates the name of the locked table, the LMODE represents the category of the lock, as follows:
serial number
description
0
no
1
air
2
Row sharing
3
Dedicated
4
shared
5
Shared line special purpose
6
dedicated
SELECT Count (*) from V $ lock where lmode = 0;
This statement can quickly know if there is a session that is waiting for the lock of any table.
2 In PL / SQL, the grammar of the constant constant is: Constant, for example:
Pi constant real: = 3.1415926
2 In PL / SQL, test whether a variable is NULL or whether it is non-null value, uses is null and is not null, for example: if (x is null) Then ..., if (x is not null) THEN ...
2 A true flexible feature is called a "configuration" variable. You don't need to write a data type, you can use other variables, database columns, or data types. This purpose can be implemented using the% Type or% ROWTYPE property, the% Type property provides the type and length of the required database column or variable. The% ROWTYPE property allows people to define a recording variable, which has the correct type and length of each column in the table or cursor. Use the point symbol reference to each member variable in the record variable, such as:
? Quantity Orders.qty% Type - Based on Qty Columns in Table Orders
ORDERS_REC ORDERS% ROWTYPE - Based on a column in the table order
N Date: 2004-06-11
2 cursor statement statement:
Declare
- Get All Orders in Database
Cursor get_orders IS
Select * from Orders;
- Get a few columns for a specified order number Number
Cursor get_order_item (porder_no orderrs.order_no% type) IS
SELECT SEQ_NUM, Quantity, Unit_price, Extended_Price
From Orders
WHERE ORDER_NO = Porder_no;
- Get The Whole Row for a Particular Item #
Cursor get_items (pitem_no items.Item_no% type) Return Items% RowType IS
SELECT * from Items
WHERE ITEM_NO = PITEM_NO;
- get the item name for the particular item #
CURSOR GET_ITEM_NAME (Pitem_no Items.Item_no% Type) Return Items.Item_no%
TYPE IS SELECT ITEM_NAME from items where item_no = pitem_no;
2 When a cursor is turned on, execute the SQL statement and calculate the corresponding data collection, but no record is true returned to the program, use the FETCH statement to extract a record, before performing another extraction record, the extracted record is maintained for the current Only the extraction record can only be taken, and there is no control over the data set. 2 cursor properties
Attributes
return value
Description
Isopen
True / false
Point out that a cursor is open or close
Found
True / false
Indicates whether a record is found
NOTFOUND
True / false
Indicates whether a record is found
Rowcount
Number
The number of orders for each extraction record
Use example:
IF (Cur_Order% Found) THEN
Open cur_items (cur_order.order_no);
Fetch Cur_Items INTO ORDER_ITEM;
Loop
EXIT WHEN CUR_ITEMS% NotFound;
DBMS_OUTPUT.PUT_LINE ('on row #' || to_char (cur_items%)
Rowcount));
Fetch Cur_Items INTO ORDER_ITEM;
End
Loop
;
END IF;
IF Cur_Items% Isopen Then
Close Cur_Items;
END IF;
2 By defined all implicit cursors are assumed to return only one record.
2 ORACLE exception handling:
EXCEPTION
........
........
When Others the ...
2 SQLCode functions get an exception code, and the SQlerRM function obtains detailed description information.
2 The cursor can also be read using the for loop.
2 local dynamic SQL
Rpocedure INSERT_ITEMS (PTable Varchar2, Pd Varchar2, PDesc VARCHAR2)
IS
SQL_Statement Varchar2 (500);
Begin
SQL_STATEMENT: = 'INSERT INTO' || PTABLE || 'VALUES (: PROD,: DESC)';
Execute Immediate SQL_Statement Using PPROD, PDESC;
END;
2 data dictionary view of the storage subroutine and packages
View name
Description
DBA_SOURCE
All text source code for all compilation modules
DBA_ERRORS
All module compile errors text list
DBA_OBJECT_SIZE
Compilation module status, such as: Validity, Object Size
DBA_OBJECTS
Compile module type (stored procedure, package, function)
DBA_Dependencies
Object-related list, table referenced in the package
If a storage module is illegal, it must be automatically recompiled by the server or manually compiled it by the owner.
2 Check the SQL statement of all storage subroutine status
Column Object_name Format A30
Column TimeStamp Format A20
Select Object_name, Object_Type, Timestamp, Status
From user_Objects
Where object_type in ('function', 'procedure', 'package ",' package
Body ') Order by Object_name, Object_Type;
Tip: The modification of the table structure sometimes invalidates the stored procedures and objects, you can use the alter ... .compile command to recompile the object, as shown in the following example:
Alter Procedure Procedure_name Compile;
2 pack concept
The package is a collection of program objects, which can provide continuity of data, cursors and other language structures. When any object inside the package, the entire package is loaded into memory.
The package also allows subroutine overload, which is not available for pure subprograms.
2 Error number: ORU-10027 processing
The error number oru-10027 refers to the output cache of Oracle. The Oracle default output cache is 2000 bytes. When using dbms_output, it may encounter such errors, and the solution is to pass DBMS_OUTPUT.Enable (20000) Set a bigger cache.
2 About the use of global variables in the package
It should be as little as possible to use global variables or not all global variables, except for recording variables for various tables.
2 dBMS_Application_info package
This package allows developers to add tracking information in view V $ Session and V $ SQLAREA.
2 dBMS_DDL package
You can use this package to do two things:
• Re-build the storage subroutines and packages using alter_compile ()
• Analyze a table, index, or cluster using Analyze_Object ()
The following stored procedures are used to find all all invalid storage subroutines and packages of the current user and recompile it:
Create Or Replace Pro_Recompile_INValid_Object
IS
Cursor Invalid_Prog_Obj IS
Select Object_name, Object_Type from User_Objects
Where status = 'invalid';
Rec invalid_prog_obj% rowType;
Status numeric;
Begin
Open invalid_prog_obj;
Fetch invalid_prog_obj Into Rec;
Loop
EXIT WHEN INVALID_PROG_OBJ% NOTFOUND;
DBMS_DDL.alter_Compile (Rec.Object_type, null, rec.Object_name)
Fetch invalid_prog_obj Into Rec;
End
Loop
;
Close Invalid_Prog_Obj;
EXCEPTION
When Others Then
Begin
Status: = sqlcode;
DMBS_OUTPUT.PUT_LINE (SQlerRM (STATUS));
IF (Invalid_Prog_Obj% Isopen) THEN
Close Invalid_Prog_Obj;
END IF;
Exception when others
NULL;
END;
END;
2 About the use of global variables in the package
It should be as little as possible to use global variables or not all global variables, except for recording variables for various tables.
N Date: 2004-06-25
2 SYS and SYSTEM mode
SYS mode is owner of all internal database tables, structures, supplies package, procedures, etc. It has all V $ and data dictionary views, and creates all packaged database roles (DBA, Connect, Resource), SYS is a database. Root users or system administrators should try to avoid registration into the system as SYS.
The SYS user is the only user who can access a specific internal data dictionary table. The default password is change_on_install, change the password immediately after installation. The System mode is the default account for the DBA task whose default password is Manager.
2 database object
Database objects are divided into two categories. One class is an object to be used inside the RDBMS, referred to as a system database object; another class is an object that can be accessed by any program, called a user database object.
System database objects include:
l Initialization parameter file
l Control file
l Online and archive redo log files
l Track file
l RowID (within the internal address)
l Oracle block
2 logical backup and recovery
Fully logical backup:
D: / exp userid = user_id / password @ service_name full = y
FILE = backup_file_path
Fully logical recovery:
D: / IMP userid = user_id / password @ service_name full = Y
FILE = backup_file_path ignore = y
