DOCX

Oracle PL/SQL Command

By John Turner,2014-07-27 19:30
8 views 0
oracle

    ORACLE 数据库

    SQL基本语句 ............................................. 2

    ALTER: ........................................................................................................................................ 2

    Create: ....................................................................................................................................... 4

    CONNECT: .................................................................................................................................. 7

    DROP: ........................................................................................................................................ 7

    DELETE....................................................................................................................................... 8

    GRANT ....................................................................................................................................... 8

    INSERT ....................................................................................................................................... 8

    REVOKE...................................................................................................................................... 8

    SELECT: ...................................................................................................................................... 9

    基本查询 ........................................................................................................................... 9

    SELECT 格式 ................................................................................................................... 10 SAVE: ....................................................................................................................................... 12

    SHUTDOWN: ........................................................................................................................... 12

    STARTUP: ................................................................................................................................. 12

    UPDATE.................................................................................................................................... 13

    ORAPWD ................................................................................................................................. 13

    PL/SQL ...................................................... 13

    定义......................................................................................................................................... 13

    声明......................................................................................................................................... 14

    循环......................................................................................................................................... 16

    LOOP?????EXIT??????END ....................................................................................................... 16

    LOOP???????EXIT WHEN???????END ........................................................................................ 17

    WHILE?????LOOP??????END LOOP ......................................................................................... 17

    FOR ?????IN??????LOOP?????END LOOP .................................................................................... 18 异常处理................................................................................................................................. 20

    数值型函数............................................................................................................................. 21

    字符函数................................................................................................................................. 23

    统计函数................................................................................................................................. 27

    游标 ......................................................... 29

    隐式游标................................................................................................................................. 29

    显式游标................................................................................................................................. 29

    游标属性................................................................................................................................. 30

    %ISOPEN .......................................................................................................................... 30

    %FOUND%NOTFOUND ............................................................................................ 31

    %ROWCOUNT ................................................................................................................ 32 游标FOR循环 ........................................................................................................................ 34

    RECORD ........................................................................................................................... 34

    FOR循环 ......................................................................................................................... 35

    存储过程管理 .......................................... 37

    过程......................................................................................................................................... 37

    函数......................................................................................................................................... 38

    程序包!................................................................................................................................. 41

    PACKAGE:......................................................................................................................... 41

    还可以!PACKAGEBODY ................................................................................................. 42

    触发器..................................................................................................................................... 44 SQL基本语句

    ALTER:

    ALTER SYSTEM DISABLE RESTICTED SESSION; ALTER DATABASE MOUNT;

    ALTER DATABASE OPEN;

    ALTER DATABASE ARCHIVELOG;

    ALTER DATABASE OPEN READ ONLY; ALTER TABLESPACE TABLE1 RENAME TO TABLE2;改名

    ALTER TABLESPACE TABLE1

    ADD DATAFILE TABLE2 加表

    ALTER TABLESPACE TABLE1

    RESIZE + 大小

    ALTER TABLESPACE TABLE1 OFFLINE; 改状态

    ALTER TABLESPACE TABLE1 ONLINE; ALTER TABLESPACE TABLE1 READ ONLY; ALTER TABLESPACE TABLE1 READ WRITE;

ALTER DATABASE

     ADD LOGFILE(log1c.rdo,log2c.rdo) SIZE 5000K;

    ALTER DATABASE

     ADD LOGFILE GROUP 10 (log1a.rdo,log2a.rdo) SIZE 5000K; ALTER DATABASE

     ADD LOGFILE MEMBER log3a.rdo TO GROUP 10;

    ALTER DATABASE

    RENAME FILE URL/OLD1,URL/OLD2 TO URL/NEW1,URL/NEW2;

    ALTER DATABASE DROP LOGFILE GROUP 10; ALTER DATABASE DROP LOGFILE MEMBER log1a.rdo;

    ALTER DATABASE CLEAR LOGFILE GROUP 10; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 10; ALTER SYSTEM ARCHIVE LOG ALL;

    ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; ALTER USER USERMAN IDENTIFIED BY NEWPASSWORD; ALTER SUSER USERMAN PASSWORD EXPIER; ALTER USER USERMAN ACCOUNT LOCK;

    ALTER USER USERMAN ACCOUNT UNLOCK; ALTER ROLE MYROLE NOT IDENTIFIED; ALTER ROLE MYOWNROLE IDENTIFIED BY *****; ALTER TABLE STUDENT

    ADD(SH VARCHAR(20));

ALTER TABLE STUDENT

    DROP COLUMN SG;

    ALTER TABLE STUDENT

    SET UNUSED (SH);

    ALTER TABLE TEACHER

    RENAME COLUMN TDEP TO ADDRESS; ALTER TABLE STUDENT

    DROP UNUSED COLUMNS;

    ALTER INDEX INDEX_TAGE UNUSABLE; ALTER INDEX INDEX_TAGE REBUILD; ALTER INDEX INDEX_TAGE RENAME TO INDEX_AGE;

    ALTER SEQUENCE SYSTEM.TEACHER MAXVALUE 10000;

    Create:

    BIGFILE TABLESPACE

    TABLESPACE 时用DATAFILE

    TEMPORARY TABLESPACE TEMPFILE

    LOCAL AUTOALLOCATE

    LOCAL UNIFORM SIZE + 大小

    临时表示不允许用AUTOALLOCATE

    CREATE TABLESPACE ORCLTBS01

DATAFILE'G:\oracle\product\10.2.0\oradata\orcl\ORCLTBS01.dbf' SIZE

    30M

    EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;

    BLOCKSIZE + 大小;

    CREATE UNDO TABLESPACE UDOTBS01 DATAFILE G:\oracle\product\10.2.0\oradata\orcl\ UDOTBS01.dbf SIZE 2M REUSE;

    CREATE ROLE MYROLE IDENTIFIED BY MYROLLPWD; CREATE USER <用户名>

    IDENTIFIED BY <口令>

    DEFAULT TABLESPACE <默认表空间>

    TEMPORARY TABLESPACE <临时表空间>

    CREATE USER LAODA

    IDENTIFIED BY *******;

    REATE ROLE MYOWNROLE INDETIFIED BY MYPWD; CREATE TABLE TEACHER

    (TNO NUMBER(6) PRIMARY KEY,

    TNAME VARCHAR2(10) NOT NULL,

    TAGE NUMBER(3),

    TG VARCHAR2(2)

);

    //创建视图

    CREATE VIEW TE_G_D

    AS

    SELECT TNAME,TG,DNAME

    FROM TEACHER,DEPARTMENT WHERE TDEP=DNO;

    CREATE OR REPLACE VIEW TE_G_D AS

    SELECT TNAME,TG,DNAME

    FROM TEACHER,DEPARTMENT WHERE TDEP=DNO;

     //创建索引

    CREATE INDEX INDEX_TAGE ON TEACHER(TAGE)

    TABLESPACE SYSTEM;

    CREATE UNIQUE INDEX INDEX_TAGE ON TEACHER(TAGE)

    TABLESPACE SYSTEM;

    //创建序列

    CREATE SEQUENCE SYSTEM.TEACHER MINVALUE 1

    NOMAXVALUE

    START WITH 1

INCREMENT BY 1

    NOCYCLE

    CACHE 20;

    CONNECT:

    CONNECT SYS/SYSPWD AS SYSDBA; DROP:

    DROP DATABASE;

    DROP TABLESPACE TABLE1; DROP TABLESPACE TABLE1 INCLUDING CONTENTS;同时删除段 DROP TABLESPACE TABLE1 INCLUDING CONTENTS AND DATAFILES,段和文件

    DROP ROLE MYROLE;

    DROP USER USERNAME;

    DROP TABLE USERS;

    DROP VIEW [视图名];

    DROP INDEX INDEX_AGE;

    DROP SEQUENCERE SYSTEM.TEACHER; //序列的使用

    TEACHER.NEXTAL 得到下一个序列的新值

    SQL = INSERT INTO USERS(USERID,USERNAME)

VALUES(USERS.NEXTVAL,’” + CNAME+”’);

    DELETE

    DELETE FROM TEACHER WHERE TNO=3 COMMIT WORK;

    GRANT

    GRANT SYSDBA TO ADM;

    GRANT SELECT/SELECT/UPDATE/DELETE TO USERMAN;

    GRANT CONNECT TO MYROLE;

    INSERT

    INSERT INTO TEACHER (TNO,TNAME,TAGE,TG) VALUES(000007,'李刚',50,' ');

    INSERT INTO TEACHER VALUES(9,'赵琳',29,'','崇山小区',1); REVOKE

    REVOKE SYSDBA FROM ADM;

    REVOKE <权限,角色>FROM USERMAN;

    REVOKE CONNECT FROM MYROLE; REVOKE SYSDBA FROM USERMAN;

SELECT:

    基本查询

    SELECT NAME,CREATED FROM V$DATABASE; SELECT TABLESPACE_NAME,CONTENTS,STATUS FROM DBA_TABLESPACES;

    SELECT TABLESPACE_NAME,CONTENTS,STATUS FROM DBA_TABLESPACES;

    SELECT DBID,NAME,LOG_MODE FROM V$DATABASE; SELECT TYPE,RECORD_SIZE,RECORDS_TOTAL,RECORDS_USED FROM

    V$CONTROLFILE_RECORD_SECTION;

    SELECT NAME FROM V$DATAFILE;

    SELECT MEMBER FROM V$LOGFILE;

    SELECT GROUP#,ARCHIVED,STATUS FROM V$LOG; SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE; SELECT * FROM v$ARCHIVE_PROCESSES;

    ARCHIVE LOG LIST;

    SELECT * FROM V$PWFILE_USERS;

    SELECT * FROM DBA_ROLES;

    SELECT * FROM TEACHER;

SELECT 格式

    SELECT 子句

    ] [INTO 子句

    FROM 子句

    [WHERE 子句]

    [GROUP BY 子句]

    [HAVING 子句]

    [ORDER BY 子句]

    查询时改名字

    SELECT TNAME AS 名字,TG AS 性别

    FROM TEACHER;

    SELECT * FROM TEACHER

    WHERE TNAME LIKE '%%';

    SELECT * FROM TEACHER

    ORDER BY TG;

    SELECT COUNT(*) FROM TEACHER; SELECT COUNT(*) AS NUM FROM TEACHER; SELECT TG,COUNT(*) AS NUM FROM TEACHER GROUP BY TG;

    SELECT AVG(TAGE) AS AVG FROM TEACHER; SELECT TG,AVG(TAGE) AS AVG FROM TEACHER

Report this document

For any questions or suggestions please email
cust-service@docsford.com