;
SELECT TEXT FROM ALL_SOURCE WHERE TYPE='PACKAGE BODY' AND OWNER='SYSTEM' ORDER BY NAME,LINE;
Procedure,Function, Package, Trigger这些数据库对象的定义都可以用类似的方法得到。
//TRANSACTION
SAVEPOINT A
SELECT * FROM PERSON;
UPDATE PERSON SET AGE=30 WHERE NAME='ZHANGSAN';
SELECT * FROM PERSON;
ROLLBACK TO A
SELECT * FROM PERSON;
//TRANSACTION'S NAME
SET TRANSACTION NAME 'INSERT INTO TABLE TEST_TRANSACTION'
//INSERT INTO DATABASE
INSERT INTO TABLE TEST_TRANSACTION VALUES('KKKK',20,TO_DATE('2011-10-10','YYYY-MM-DD HH24:MI:SS'),20.8);
// SELECT TRANSACTION
SELECT NAME FROM V$TRANSACTION;
//COMMIT;
SELECT NAME FROM V$TRANSACTION;
//事务一致性(TRANSACTION-LEVEL READ CONSISTENCY)
//会话1 窗口1
//READ ONLY
SET TRANSACTION READ ONLY;
//2
SELECT * FROM PERSON;
//会话2 窗口2
INSERT INTO TABLE TEST_TRANSACTION VALUES('KKKK',20,TO_DATE('2011-10-10','YYYY-MM-DD HH24:MI:SS'),20.8);
COMMIT;
//会话1 窗口1
SELECT * FROM PERSON;
//COMMIT;
SELECT * FROM PERSON;
//INTEGRITY CONSTRAINTS 完整性约束
//CREATE TABLE
CREATE TABLE INTEGRITY_TEST(
ID INT,
NAME VARCHAR2(10));
//ALTER COLUMN OF TABLE FOR PRIMARY KEY
ALTER TABLE INTEGRITY_TEST ADD PRIMARY KEY(ID,NAME);
//DELETE
ALTER TABLE INTEGRITY_TEST DROP PRIMARY KEY CASCADE;
//ALTER COLUMN OF TABLE FOR PRIMARY KEY AND CONSTRAINT NAME
ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK1 PRIMARY KEY(ID,NAME);
//DELETE
ALTERT TABLE INTEGRITY_TEST DROP PK1;
//CREATE UNIQUE CONSTRAINT
DROP TABLE INTEGRITY_TEST;
//1
CREATE TABLE INTEGRITY_TEST(ID INT UNIQUE,NAME VARCHAR2(10));
//2
ALTER TABLE INTEGRITY_TEST ADD UNIQUE(ID,NAME);
//DELETE
ALTER TABLE INTEGRITY_TEST DROP UNIQUE(ID,NAEM);
//3
ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK3 UNIQUE(ID,NAME);
//DELETE
ALTER TABLE INTEGRITY_TEST DROP CONSTRAINT PK3 CASCADE;
//CREATE CHECK CONSTRAINTS
DROP TABLE INTEGRITY_TEST;
//CREATE CHECK CONSTRAINT OF TABLE
CREATE TABLE INTEGRITY_TEST(ID INT,
NAME VARCHAR2(10),
NUM NUMBER CONSTRAINT C_CHECK CHECK(NUM BETWEEN 10 AND 20));
//ADD CHECK CONSTRAINT OF TABLE
ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT C_CHECK CHECK(NUM BETWEEN 1 AND 20);
//DELETE
ALTER TABLE INTEGRITY_TEST DROP CONSTRAINT C_CHECK;
//ALTER COLUMN VALUES IS NULL OR NOT NULL OF TABLE
ALTER TABLE INTEGRITY_TEST MODIFY (ID NULL);
ALTER TABLE INTEGRITY_TEST MODIFY (ID NOT NULL);
//FOREIGN KEY
//CREATE STUDENT
CREATE TABLE STUDENT(
SID INT,
CID INT,
NAME VARCHAR2(20),
CONSTRAINT PK1 PRIMARY KEY(SID));
//CREATE COLLEGE
CREATE TABLE COLLEGE(
CID INT,
CNAME VARCHAR2(10),
CADDRESS VARCHAR2(100),
CONSTRAINT PK2 PRIMARY KEY(CID));
//CONSTRAINTS FOREIGN KEY
ALTER TABLE STUDENT ADD
CONSTRAINT FK1
FOREIGN KEY(CID)
REFERENCES COLLEGE(CID);
//OR CONSTRAINT FOREIGN KEY
ALTER TABLE STUDENT ADD
CONSTRAINT FK1
FOREIGN KEY(CID)
REFERENCES COLLEGE(CID) ON DELETE CASCADE;
//DELETE CONSTRAINTS FOREIGN KEY
//IF TABLE IS COLLEGE'S DELETE THEN
ALTER TABLE COLLEGE DROP CONSTRAINT FK1
//OR USEING CASCADE CONSTRAINTS TO DELETE
DROP TABLE COLLEGE CASCADE CONSTRAINTS;
//ALL TABLES AND ALL CONSTRAINTS
SELECT TABLE_NAME,CONSTRAINT_NAME FROM USER_CONSTRAINTS;
// 查看表约束
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM
USER_CONSTRAINTS WHERE TABLE_NAME=upper('department') AND
OWNER=USER;
//查看值
SELECT COLUMN_NAME,POSITION FROM
USER_CONS_COLUMNS WHERE
CONSTRAINT_NAME='SYS_C0011056';
|