Oracle 知识

yanguz123

浏览: 570316 次
性别:
来自: 杭州

最近访客更多访客>>

strawmanTao

xway2000

binashen

yeyinzhu321

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Database

Oracle

【run；r；/的用法】

SQL> SELECT * FROM dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> run

1* SELECT * FROM dept

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> r

1* SELECT * FROM dept

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> /

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL>

【修改Oracle日期的默认格式】

SQL> alter session set NLS_date_format='YYYY-MM-DD';

【绑定变量查询】

SQL> SELECT * FROM dept t WHERE t.deptno = &dept_no;

输入 dept_no 的值: 40

原值 1: SELECT * FROM dept t WHERE t.deptno = &dept_no

新值 1: SELECT * FROM dept t WHERE t.deptno = 40

DEPTNO DNAME LOC

---------- -------------- -------------

40 OPERATIONS BOSTON

【绑定变量插入】

SQL> INSERT INTO dept(deptno,dname,loc) VALUES (&dept_no,'&dept_name','&loc_name');

输入 dept_no 的值: 90

输入 dept_name 的值: work

输入 loc_name 的值: hangzhou

原值 1: INSERT INTO dept(deptno,dname,loc) VALUES (&dept_no,'&dept_name','&loc_name')

新值 1: INSERT INTO dept(deptno,dname,loc) VALUES (90,'work','hangzhou')

已创建 1 行。

【绑定变量修改】

SQL> UPDATE dept t SET t.dname = '&d_name' WHERE t.deptno = 90;

输入 d_name 的值: job

原值 1: UPDATE dept t SET t.dname = '&d_name' WHERE t.deptno = 90

新值 1: UPDATE dept t SET t.dname = 'job' WHERE t.deptno = 90

已更新 1 行。

【绑定变量删除】

SQL> DELETE FROM dept t WHERE t.deptno = &dept_no;

输入 dept_no 的值: 90

原值 1: DELETE FROM dept t WHERE t.deptno = &dept_no

新值 1: DELETE FROM dept t WHERE t.deptno = 90

已删除 1 行。

【利用子查询创建表】

SQL> CREATE TABLE my_dept AS SELECT * FROM dept;

表已创建。

【利用子查询插入数据】

SQL> INSERT INTO my_dept SELECT * FROM dept;

已创建4行。

【事务】

多个DML语句

单个DCL语句

单个DDL语句

【事务保存点】

SQL> SAVEPOINT p;

保存点已创建。

SQL> INSERT INTO my_dept VALUES (&dept_no,'&d_name','&loc_name');

输入 dept_no 的值: 70

输入 d_name 的值: work

输入 loc_name 的值: hangzhou

原值 1: INSERT INTO my_dept VALUES (&dept_no,'&d_name','&loc_name')

新值 1: INSERT INTO my_dept VALUES (70,'work','hangzhou')

已创建 1 行。

SQL> SELECT * FROM my_dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

70 work hangzhou

SQL> SAVEPOINT s;

保存点已创建。

SQL> ROLLBACK TO SAVEPOINT p;

回退已完成。

SQL> SELECT * FROM my_dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

【别名】

SQL> SELECT deptno "部门编号", dname AS "部门名称", loc location FROM dept;

部门编号部门名称 LOCATION

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

【“||”连接符】

SQL> SELECT deptno || ' -- ' || dname || ' -- ' || loc AS list FROM dept;

LIST

---------------------------------------------------------------------------

10 -- ACCOUNTING -- NEW YORK

20 -- RESEARCH -- DALLAS

30 -- SALES -- CHICAGO

40 -- OPERATIONS -- BOSTON

【“<>” 不等于】

SQL> SELECT * FROM dept WHERE deptno <> 20;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

30 SALES CHICAGO

40 OPERATIONS BOSTON

【NULL 是否为空】

SQL> SELECT * FROM emp WHERE mgr IS NULL ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

【函数】

LOWER

UPPER

INITCAP

CONCAT

SUBSTR

LENGTH

INSTR

LPAD

ROUND

TRUNC

MOD

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

NVL

DECODE

... ...

SQL> SELECT ename , CONCAT(empno,job) ejob, LENGTH(ename) len, INSTR(ename,'A') ins , LOWER(job) ljob, LPAD(job,10,'*') pjob, SUBSTR(ename,1,3) str FROM emp ;

ENAME EJOB LEN INS LJOB PJOB STR

---------- ------------------------------------------------- ---------- ---------- --------- -------------------- ------

SMITH 7369CLERK 5 0 clerk *****CLERK SMI

ALLEN 7499SALESMAN 5 1 salesman **SALESMAN ALL

WARD 7521SALESMAN 4 2 salesman **SALESMAN WAR

JONES 7566MANAGER 5 0 manager ***MANAGER JON

MARTIN 7654SALESMAN 6 2 salesman **SALESMAN MAR

BLAKE 7698MANAGER 5 3 manager ***MANAGER BLA

CLARK 7782MANAGER 5 3 manager ***MANAGER CLA

SCOTT 7788ANALYST 5 0 analyst ***ANALYST SCO

KING 7839PRESIDENT 4 0 president *PRESIDENT KIN

TURNER 7844SALESMAN 6 0 salesman **SALESMAN TUR

ADAMS 7876CLERK 5 1 clerk *****CLERK ADA

JAMES 7900CLERK 5 2 clerk *****CLERK JAM

FORD 7902ANALYST 4 0 analyst ***ANALYST FOR

MILLER 7934CLERK 6 0 clerk *****CLERK MIL

已选择14行。

SQL> SELECT ROUND(45.923,2) a, ROUND(45.923,0) b, ROUND(45.923,-1) c FROM DUAL;

A B C

---------- ---------- ----------

45.92 46 50

SQL> SELECT TRUNC(45.923,2) a, TRUNC(45.923,0) b, TRUNC(45.923,-1) c FROM DUAL;

A B C

---------- ---------- ----------

45.92 45 40

【sqlplus命令】

SQL> save 'd:\a.txt';

已创建文件 d:\a.txt

SQL> get 'd:\a.txt';

1* SELECT TRUNC(45.923,2) a, TRUNC(45.923,0) b, TRUNC(45.923,-1) c FROM DUAL

SQL> /

A B C

---------- ---------- ----------

45.92 45 40

SQL> @ 'd:\a.txt';

A B C

---------- ---------- ----------

45.92 45 40

【连接】

SQL> conn scott/tiger@ORCL

已连接。

【访问其他用户的表】

SQL> conn sys/sys as sysdba;

已连接。

SQL> select * from emp;

select * from emp

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

【修改"SQL>"的名字】

SQL> set sqlprompt tom

tomselect * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

【授权】

SQL> grant select,update,insert,delete on dept to sys;

授权成功。

【撤销授权】

SQL> revoke select,update,insert,delete on dept from sys;

撤销成功。

【创建用户】

SQL> conn sys/sys as sysdba

已连接。

SQL> create user yangyz identified by yangyz ;

用户已创建

【for update wait语句】

SQL> select * from my_dept for update of deptno;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select * from my_dept for update wait 80;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

【锁定表-共享锁】

SQL> LOCK TABLE my_dept IN SHARE MODE ;

表已锁定。

SQL> LOCK TABLE my_dept IN SHARE UPDATE MODE;

表已锁定。

SQL> LOCK TABLE my_dept IN EXCLUSIVE MODE;

表已锁定。

SQL> LOCK TABLE my_dept IN EXCLUSIVE MODE NOWAIT ;

【数据类型】

VARCHAR

VARCHAR2

CHAR

NUMBER

DATE

LONG

CLOB

RAW

LONG RAW

BLOB

BFILE

【修改表结构】

SQL> ALTER TABLE my_dept ADD(worker varchar2(20)) ;

表已更改。

SQL> desc my_dept;

名称是否为空? 类型

----------------------------------------- -------- ---------------------------

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

WORKER VARCHAR2(20)

SQL> ALTER TABLE my_dept MODIFY(worker varchar2(10)) ;

表已更改。

SQL> desc my_dept;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

WORKER VARCHAR2(10)

SQL> ALTER TABLE my_dept DROP COLUMN worker;

表已更改。

SQL> desc my_dept;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> ALTER TABLE my_dept SET UNUSED COLUMN deptno;

表已更改。

SQL> desc my_dept;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

WORKER VARCHAR2(10)

SQL> ALTER TABLE my_dept DROP UNUSED COLUMNS;

表已更改。

SQL> create table student(stuid number,stuname varchar2(30),stuage number,score number(5,2));

表已创建。

SQL> ALTER TABLE student ADD CONSTRAINT pk_stu PRIMARY KEY (stuid);

表已更改。

SQL> ALTER TABLE student DROP CONSTRAINT pk_stu;

表已更改。

【查看约束表】

SQL> DESC USER_CONSTRAINTS;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

OWNER NOT NULL VARCHAR2(30)

CONSTRAINT_NAME NOT NULL VARCHAR2(30)

CONSTRAINT_TYPE VARCHAR2(1)

TABLE_NAME NOT NULL VARCHAR2(30)

SEARCH_CONDITION LONG

R_OWNER VARCHAR2(30)

R_CONSTRAINT_NAME VARCHAR2(30)

DELETE_RULE VARCHAR2(9)

STATUS VARCHAR2(8)

DEFERRABLE VARCHAR2(14)

DEFERRED VARCHAR2(9)

VALIDATED VARCHAR2(13)

GENERATED VARCHAR2(14)

BAD VARCHAR2(3)

RELY VARCHAR2(4)

LAST_CHANGE DATE

INDEX_OWNER VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

INVALID VARCHAR2(7)

VIEW_RELATED VARCHAR2(14)

【表分区】

范围分区法

散列分区法 Hash分区

复合分区法

列表分区法

SQL> CREATE TABLE student(id number not null,name varchar2(20),score number(5,2)) PARTITION BY RANGE(score)(PARTITION p1 VALUES LESS THAN (60),PARTITION p2 VALUES LESS THAN (70),PARTITION p3 VALUES LESS THAN (80),PARTITION p4 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> CREATE TABLE teacher(id number not null,name varchar2(20),salary number(10,2)) PARTITION BY HASH(id) (PARTITION p1,PARTITION p2,PARTITION p3,PARTITION p4) ;

表已创建。

SQL> CREATE TABLE my_saldegree

(pid number,grade number,losal number,hisal number)

PARTITION BY RANGE(grade)

SUBPARTITION BY HASH(losal,hisal)(

PARTITION p1 VALUES LESS THAN(10) (SUBPARTITION sp1,SUBPARTITION sp2),

PARTITION p2 VALUES LESS THAN(20) (SUBPARTITION sp3,SUBPARTITION sp4)

);

表已创建。

SQL> CREATE TABLE customer (cusid number, name varchar2(20), state varchar2(20)) PARTITION BY LIST(state) (PARTITION asia VALUES('china','jpan','india'), PARTITION europe VALUES('england','franch'));

表已创建。

【查询分区】

SQL> SELECT * FROM user_tab_partitions ;

SQL> SELECT * FROM user_tab_partitions WHERE table_name = 'STUDENT';

SQL> SELECT * FROM user_tab_partitions WHERE LOWER(table_name) = 'student';

TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE

------------------------------ --- ------------------------------ ------------------ -----------------------

STUDENT NO P1 0 60

STUDENT NO P2 0 70

STUDENT NO P3 0 80

STUDENT NO P4 0 MAXVALUE

【修改表分区】

SQL> ALTER TABLE student DROP PARTITION p4;

表已更改。

SQL> ALTER TABLE student ADD PARTITION p5 VALUES LESS THAN (90);

表已更改。

SQL> ALTER TABLE student TRUNCATE PARTITION p5;

表已截掉。

SQL> ALTER TABLE student MERGE PARTITIONS p2,p3 INTO PARTITION p6;

表已更改。

SQL> SELECT * FROM user_tab_partitions WHERE table_name = UPPER('student');

TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE

------------------------------ --- ------------------------------ ------------------ ---------------------------

STUDENT NO P1 0 60

STUDENT NO P6 0 80

STUDENT NO P5 0 90

【按分区查询】

SQL> SELECT * FROM student PARTITION(p1);

SQL> desc user_tab_partitions;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

TABLE_NAME VARCHAR2(30)

COMPOSITE VARCHAR2(3)

PARTITION_NAME VARCHAR2(30)

SUBPARTITION_COUNT NUMBER

HIGH_VALUE LONG

HIGH_VALUE_LENGTH NUMBER

PARTITION_POSITION NUMBER

TABLESPACE_NAME VARCHAR2(30)

PCT_FREE NUMBER

PCT_USED NUMBER

INI_TRANS NUMBER

MAX_TRANS NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENT NUMBER

MAX_EXTENT NUMBER

PCT_INCREASE NUMBER

FREELISTS NUMBER

FREELIST_GROUPS NUMBER

LOGGING VARCHAR2(7)

COMPRESSION VARCHAR2(8)

NUM_ROWS NUMBER

BLOCKS NUMBER

EMPTY_BLOCKS NUMBER

AVG_SPACE NUMBER

CHAIN_CNT NUMBER

AVG_ROW_LEN NUMBER

SAMPLE_SIZE NUMBER

LAST_ANALYZED DATE

BUFFER_POOL VARCHAR2(7)

GLOBAL_STATS VARCHAR2(3)

USER_STATS VARCHAR2(3)

SQL> desc user_ind_partitions;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

INDEX_NAME VARCHAR2(30)

COMPOSITE VARCHAR2(3)

PARTITION_NAME VARCHAR2(30)

SUBPARTITION_COUNT NUMBER

HIGH_VALUE LONG

HIGH_VALUE_LENGTH NUMBER

PARTITION_POSITION NUMBER

STATUS VARCHAR2(8)

TABLESPACE_NAME VARCHAR2(30)

PCT_FREE NUMBER

INI_TRANS NUMBER

MAX_TRANS NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENT NUMBER

MAX_EXTENT NUMBER

PCT_INCREASE NUMBER

FREELISTS NUMBER

FREELIST_GROUPS NUMBER

LOGGING VARCHAR2(7)

COMPRESSION VARCHAR2(8)

BLEVEL NUMBER

LEAF_BLOCKS NUMBER

DISTINCT_KEYS NUMBER

AVG_LEAF_BLOCKS_PER_KEY NUMBER

AVG_DATA_BLOCKS_PER_KEY NUMBER

CLUSTERING_FACTOR NUMBER

NUM_ROWS NUMBER

SAMPLE_SIZE NUMBER

LAST_ANALYZED DATE

BUFFER_POOL VARCHAR2(7)

USER_STATS VARCHAR2(3)

PCT_DIRECT_ACCESS NUMBER

GLOBAL_STATS VARCHAR2(3)

DOMIDX_OPSTATUS VARCHAR2(6)

PARAMETERS VARCHAR2(1000)

【重复插入】

SQL> INSERT INTO student SELECT * FROM student;

已创建2行。

SQL> INSERT INTO student SELECT * FROM student;

已创建4行。

【同义词】

SQL> CREATE SYNONYM stu FOR student;

同义词已创建。

SQL> CREATE PUBLIC SYNONYM st FOR scott.student;

同义词已创建。

SQL> DROP SYNONYM stu;

同义词已丢弃。

SQL> DESC user_synonyms;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

SYNONYM_NAME NOT NULL VARCHAR2(30)

TABLE_OWNER VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

DB_LINK VARCHAR2(128)

【序列】

SQL> CREATE SEQUENCE seq_1 INCREMENT BY 1 START WITH 1 MAXVALUE 9999 NOMINVALUE CYCLE ;

序列已创建。

SQL> CREATE SEQUENCE seq_2 INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE;

序列已创建。

SQL> CREATE SEQUENCE seq_5 INCREMENT BY -1 START WITH 10 MAXVALUE 20 MINVALUE 0 NOCYCLE NOCACHE;

序列已创建。

SQL> SELECT seq_1.nextval FROM DUAL;

NEXTVAL

----------

SQL> SELECT seq_1.currval FROM DUAL;

CURRVAL

----------

SQL> ALTER SEQUENCE seq_1 MAXVALUE 99;

序列已更改。

SQL> DROP SEQUENCE seq_5;

序列已丢弃。

SQL> desc user_sequences;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

SEQUENCE_NAME NOT NULL VARCHAR2(30)

MIN_VALUE NUMBER

MAX_VALUE NUMBER

INCREMENT_BY NOT NULL NUMBER

CYCLE_FLAG VARCHAR2(1)

ORDER_FLAG VARCHAR2(1)

CACHE_SIZE NOT NULL NUMBER

LAST_NUMBER NOT NULL NUMBER

【视图】

SQL> CREATE VIEW job AS SELECT empno, ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno;

视图已建立。

SQL> CREATE OR REPLACE VIEW job AS SELECT empno, ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno;

视图已建立。

SQL> CREATE OR REPLACE VIEW job AS SELECT empno, ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno ORDER BY d.deptno;

视图已建立。

SQL> CREATE OR REPLACE FORCE VIEW v_none AS SELECT * FROM no_table;

警告: 创建的视图带有编译错误。

SQL> DROP VIEW v_none;

视图已丢掉。

SQL> CREATE OR REPLACE VIEW v_emp AS SELECT * FROM emp t WHERE t.deptno = 10 UNION ALL SELECT * FROM emp e WHERE e.deptno = 20 ;

视图已建立。

SQL> ALTER VIEW v_emp COMPILE ;

视图已变更。

SQL> DESC user_views;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

VIEW_NAME NOT NULL VARCHAR2(30)

TEXT_LENGTH NUMBER

TEXT LONG

TYPE_TEXT_LENGTH NUMBER

TYPE_TEXT VARCHAR2(4000)

OID_TEXT_LENGTH NUMBER

OID_TEXT VARCHAR2(4000)

VIEW_TYPE_OWNER VARCHAR2(30)

VIEW_TYPE VARCHAR2(30)

SUPERVIEW_NAME VARCHAR2(30)

【rownum，rowid】

SQL> select rownum,rowid from dual;

ROWNUM ROWID

---------- ------------------

1 AAAADeAABAAAAZSAAA

SQL> SELECT t.* ,rowid FROM emp t WHERE rowid = 'AAAHW7AABAAAMUiAAA';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWID

---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ------------------

7369 SMITH CLERK 7902 17-12月-80 800 20 AAAHW7AABAAAMUiAAA

SQL> select * from emp where rownum <= 3;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

SQL> select * from emp where rownum = 3;

未选定行

【索引】

唯一索引

组合索引

反向键索引

位图索引

键压缩索引

分区索引

SQL> CREATE UNIQUE INDEX idx_stuid ON student(id);

索引已创建。

SQL> CREATE INDEX idx_empno_deptno ON emp(empno,deptno);

索引已创建。

SQL> DROP INDEX idx_stuid;

索引已丢弃。

SQL> CREATE BITMAP INDEX idx_job ON my_emp(job);

索引已创建。

SQL> CREATE TABLE indexorg (id number primary key, name varchar2(20)) ORGANIZATION INDEX ;

表已创建。

SQL> CREATE INDEX idx_ename ON scott.my_emp(LOWER(ename));

索引已创建。

SQL> CREATE INDEX idx_job_ename ON employee(job,ename) COMPRESS 1 ;

索引已创建。

【簇】

SQL> CREATE CLUSTER class_clsuter(classno varchar2(10)) TABLESPACE users;

已创建数据簇。

SQL> CREATE INDEX idx_class_cluster ON cluster class_clsuter;

索引已创建。

SQL> CREATE TABLE stuclass(classno varchar2(10), classname varchar2(20)) CLUSTER class_clsuter(classno);

表已创建。

SQL> CREATE TABLE classstu(stuno varchar2(10), stuname varchar2(20),classno varchar2(10)) CLUSTER class_clsuter(classno) ;

表已创建。

SQL> DROP CLUSTER class_clsuter INCLUDING TABLES ;

数据簇已丢弃。

SQL> desc user_clusters;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

CLUSTER_NAME NOT NULL VARCHAR2(30)

TABLESPACE_NAME NOT NULL VARCHAR2(30)

PCT_FREE NUMBER

PCT_USED NUMBER

KEY_SIZE NUMBER

INI_TRANS NOT NULL NUMBER

MAX_TRANS NOT NULL NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NOT NULL NUMBER

MAX_EXTENTS NOT NULL NUMBER

PCT_INCREASE NUMBER

FREELISTS NUMBER

FREELIST_GROUPS NUMBER

AVG_BLOCKS_PER_KEY NUMBER

CLUSTER_TYPE VARCHAR2(5)

FUNCTION VARCHAR2(15)

HASHKEYS NUMBER

DEGREE VARCHAR2(10)

INSTANCES VARCHAR2(10)

CACHE VARCHAR2(5)

BUFFER_POOL VARCHAR2(7)

SINGLE_TABLE VARCHAR2(5)

DEPENDENCIES VARCHAR2(8)

【ORDBMS和RDBMS】

面向过程和面向对象

【抽象数据类型】

SQL> CREATE OR REPLACE TYPE addresss_type AS OBJECT (province varchar2(20), city varchar2(20), details varchar2(40))

2 /

类型已创建。

SQL> CREATE OR REPLACE TYPE provider AS OBJECT (proname varchar2(20), address addresss_type)

2 /

类型已创建。

SQL> CREATE TABLE providers (proid number, prodetail provider);

表已创建。

SQL> CREATE TABLE provider_tab OF provider ;

表已创建。

SQL> INSERT INTO providers VALUES (101,provider('XX公司',addresss_type('浙江','杭州','滨江滨康路1号')));

已创建 1 行。

SQL> CREATE OR REPLACE TYPE person AS OBJECT (pid number, pname varchar2(20)) NOT FINAL ;

2 /

类型已创建。

SQL> CREATE OR REPLACE TYPE worker UNDER person (salary number(10,2));

2 /

类型已创建。

SQL> INSERT INTO workers SELECT worker(1001,'zhangsan',5000) FROM dual;

已创建 1 行。

SQL> CREATE OR REPLACE TYPE programer AS OBJECT(pid number, pname varchar2(20), address addresss_type, MEMBER FUNCTION getaddress RETURN addresss_type, MEMBER PROCEDURE setaddress(newaddress addresss_type));

2 /

类型已创建。

SQL> CREATE OR REPLACE TYPE BODY programer AS

MEMBER FUNCTION getaddress RETURN addresss_type IS

BEGIN

RETURN address;

END;

MEMBER PROCEDURE setaddress(newaddress addresss_type) IS

BEGIN

address := newaddress;

END;

类型主体已创建。

SQL> SELECT * FROM providers;

PROID PRODETAIL(PRONAME, ADDRESS(PROVINCE, CITY, DETAILS))

101 PROVIDER('XX公司', ADDRESSS_TYPE('浙江', '杭州', '滨江滨康路1号'))

SQL> SELECT s.proid, s.prodetail.proname, s.prodetail.address.province, s.prodetail.address.city, s.prodetail.address.details FROM providers s;

PROID PRODETAIL.PRONAME PRODETAIL.ADDRESS.PR PRODETAIL.ADDRESS.CI PRODETAIL.ADDRESS.DETAILS

---------- -------------------- -------------------- -------------------- ----------------------------------------

101 XX公司浙江杭州滨江滨康路1号

SQL> SELECT s.proid id, s.prodetail.proname proname, s.prodetail.address.province province, s.prodetail.address.city city, s.prodetail.address.details addressdetail FROM providers s;

ID PRONAME PROVINCE CITY ADDRESSDETAIL

---------- -------------------- -------------------- -------------------- ----------------------------------------

101 XX公司浙江杭州滨江滨康路1号

SQL> UPDATE providers s SET s.prodetail.address.details = '西湖区文一路' WHERE s.proid = 101;

已更新 1 行。

SQL> UPDATE providers s SET s.prodetail.address = addresss_type('浙江','杭州','萧山区萧绍路') WHERE s.proid = 101;

已更新 1 行。

SQL> DESC user_types;

名称是否为空? 类型

----------------------------------------- -------- ---------------

TYPE_NAME NOT NULL VARCHAR2(30)

TYPE_OID NOT NULL RAW(16)

TYPECODE VARCHAR2(30)

ATTRIBUTES NUMBER

METHODS NUMBER

PREDEFINED VARCHAR2(3)

INCOMPLETE VARCHAR2(3)

FINAL VARCHAR2(3)

INSTANTIABLE VARCHAR2(3)

SUPERTYPE_OWNER VARCHAR2(30)

SUPERTYPE_NAME VARCHAR2(30)

LOCAL_ATTRIBUTES NUMBER

LOCAL_METHODS NUMBER

TYPEID RAW(16)

SQL> desc user_type_attrs;

名称是否为空? 类型

----------------------------------------- -------- --------------------------

TYPE_NAME NOT NULL VARCHAR2(30)

ATTR_NAME NOT NULL VARCHAR2(30)

ATTR_TYPE_MOD VARCHAR2(7)

ATTR_TYPE_OWNER VARCHAR2(30)

ATTR_TYPE_NAME VARCHAR2(30)

LENGTH NUMBER

PRECISION NUMBER

SCALE NUMBER

CHARACTER_SET_NAME VARCHAR2(44)

ATTR_NO NOT NULL NUMBER

INHERITED VARCHAR2(3)

SQL> DESC user_tab_columns;

名称是否为空? 类型

----------------------------------------- -------- -------------------------

TABLE_NAME NOT NULL VARCHAR2(30)

COLUMN_NAME NOT NULL VARCHAR2(30)

DATA_TYPE VARCHAR2(106)

DATA_TYPE_MOD VARCHAR2(3)

DATA_TYPE_OWNER VARCHAR2(30)

DATA_LENGTH NOT NULL NUMBER

DATA_PRECISION NUMBER

DATA_SCALE NUMBER

NULLABLE VARCHAR2(1)

COLUMN_ID NUMBER

DEFAULT_LENGTH NUMBER

DATA_DEFAULT LONG

NUM_DISTINCT NUMBER

LOW_VALUE RAW(32)

HIGH_VALUE RAW(32)

DENSITY NUMBER

NUM_NULLS NUMBER

NUM_BUCKETS NUMBER

LAST_ANALYZED DATE

SAMPLE_SIZE NUMBER

CHARACTER_SET_NAME VARCHAR2(44)

CHAR_COL_DECL_LENGTH NUMBER

GLOBAL_STATS VARCHAR2(3)

USER_STATS VARCHAR2(3)

AVG_COL_LEN NUMBER

CHAR_LENGTH NUMBER

CHAR_USED VARCHAR2(1)

V80_FMT_IMAGE VARCHAR2(3)

DATA_UPGRADED VARCHAR2(3)

SQL> desc user_dependencies;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

NAME NOT NULL VARCHAR2(30)

TYPE VARCHAR2(17)

REFERENCED_OWNER VARCHAR2(30)

REFERENCED_NAME VARCHAR2(64)

REFERENCED_TYPE VARCHAR2(17)

REFERENCED_LINK_NAME VARCHAR2(128)

SCHEMAID NUMBER

DEPENDENCY_TYPE VARCHAR2(4)

SQL> SELECT * from user_types;

TYPE_NAME TYPE_OID TYPECODE ATTRIBUTES METHODS PRE INC FIN INS SUPERTYPE_OWNER SUPERTYPE_NAME LOCAL_ATTRIBUTES LOCAL_METHODS TYPEID

------------------------------ -------------------------------- ------------------------------ ---------- ---------- --- --- --- --- ------------------------------ ------------------------------ ---------------- ------------- ----------------------

ADDRESSS_TYPE 53B7FBD04821463B8EDD5C3290527745 OBJECT 3 0 NO NO YES YES

PROVIDER A34E3BE1A9F343B6B74C782BB53111D4 OBJECT 2 0 NO NO YES YES

【可变数组】

SQL> CREATE OR REPLACE TYPE detail_type AS OBJECT

2 (

3 goodsid number,

4 incount number,

5 providerid number

6 ) ;

7 /

类型已创建。

SQL> CREATE OR REPLACE TYPE detail_array AS VARRAY(10) OF detail_type ;

2 /

类型已创建。

SQL> CREATE TABLE income_stock_order(orderid number not null primary key, indate date, operatorid number, orderdetail detail_array);

表已创建。

SQL> INSERT INTO income_stock_order VALUES (1001,sysdate,9010,detail_array(detail_type(111,111,111),detail_type(222,222,222),detail_type(333,333,333)));

已创建 1 行。

SQL> SELECT * FROM TABLE(SELECT orderdetail FROM income_stock_order WHERE orderid = 1001) ;

GOODSID INCOUNT PROVIDERID

---------- ---------- ----------

111 111 111

222 222 222

333 333 333

SQL> UPDATE income_stock_order SET orderdetail = detail_array(detail_type(112,121,211),detail_type(822,228,282),detail_type(533,553,343)) WHERE orderid = 1001;

已更新 1 行。

【嵌套表】

SQL> CREATE OR REPLACE TYPE order_detail_type AS OBJECT (goodsid number, incount number, customerid number) NOT FINAL ;

2 /

类型已创建。

SQL> CREATE OR REPLACE TYPE order_detail_table AS TABLE OF order_detail_type ;

2 /

类型已创建。

SQL> CREATE TABLE order_tab (oid number not null primary key, orderinfo order_detail_table) NESTED TABLE orderinfo STORE AS order_detail_tab;

表已创建。

SQL> INSERT INTO order_tab VALUES(1001,order_detail_table(order_detail_type(10001,111,9001),order_detail_type(10002,222,9002)));

已创建 1 行。

SQL> UPDATE TABLE (SELECT t.orderinfo FROM order_tab t WHERE t.oid = 1001) f SET f.incount = 888 WHERE f.goodsid = 10001 ;

已更新 1 行。

SQL> SELECT * FROM TABLE(SELECT p.orderinfo FROM order_tab p WHERE p.oid = 1001) ;

GOODSID INCOUNT CUSTOMERID

---------- ---------- ----------

10001 888 9001

10002 222 9002

SQL> DELETE FROM TABLE(SELECT t.orderinfo FROM order_tab t WHERE t.oid = 1001) p WHERE p.goodsid = 10001 ;

已删除 1 行。

【对象表】

SQL> CREATE TABLE object_table_1 OF order_detail_type ;

表已创建。

SQL> INSERT INTO object_table_1 VALUES(1,111,1111);

已创建 1 行。

SQL> SELECT REF(t) oid, goodsid, incount, customerid FROM object_table_1 t;

REF(T) ROWID GOODSID INCOUNT CUSTOMERID

-------------------------------------------------------------------------------------- ------------------ ---------- ---------- ----------

00002802098704AA06FE584639B73A9DE783CEA4B55AE86FAF762243DEA2D20C0D8CB9A22D0040D5C20000 AAAHboAABAAANXCAAA 1 111 1111

SQL> CREATE OR REPLACE TYPE office_type AS OBJECT (fid number, fname varchar2(20)) ;

2 /

类型已创建。

SQL> CREATE TABLE office_table OF office_type ;

表已创建。

SQL> INSERT INTO office_table VALUES(101,'研发部');

已创建 1 行。

SQL> CREATE TABLE worker_tab (wid number,wname varchar2(20), office REF office_type SCOPE IS office_table) ;

表已创建。

SQL> INSERT INTO worker_tab SELECT 7369, 'zhangsan', REF(t) FROM office_table t;

已创建 1 行。

SQL> SELECT w.wid ,w.wname,DEREF(w.office) FROM worker_tab w;

WID WNAME

---------- --------------------

DEREF(W.OFFICE)(FID, FNAME)

---------------------------------------------------------------

7369 zhangsan

OFFICE_TYPE(101, '研发部')

SQL> SELECT VALUE(f) FROM office_table f;

VALUE(F)(FID, FNAME)

------------------------------------------

OFFICE_TYPE(101, '研发部')

SQL> CREATE VIEW office_view OF office_type WITH OBJECT oid(fid) AS SELECT * FROM office_table;

视图已建立。

SQL> CREATE OR REPLACE TYPE dept_type AS OBJECT(deptno number, dnam varchar2(20),loc varchar2(20)) ;

2 /

类型已创建。

SQL> CREATE VIEW dept_view OF dept_type WITH OBJECT OID(deptno) AS SELECT * FROM dept;

视图已建立。

SQL> CREATE VIEW emp_view AS SELECT MAKE_REF(dept_view,deptno) dept_oid ,empno , ename FROM emp ;

视图已建立。

【用户管理】

SQL> DESC user_users;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME VARCHAR2(4000)

SQL> DESC dba_users;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2(30)

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME VARCHAR2(4000)

SQL> DESC all_users;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

CREATED NOT NULL DATE

CREATE|ALTER USER 用户名 IDENTIFIED BY 口令 [EXTERNALLY]

DEFAULT TABLESPACE 表空间名

TEMPORARY TABLESPACE 表空间名

QUOTA 整数 K|M|UNLIMITED ON 表空间名

CREATE|ALTER USER student_user

IDENTIFIED BY student_password

DEFAULT TABLESPACE student_table_space

TEMPORARY TABLESPACE temp_student_tablespace

QUOTA 5M ON student_table_space

QUOTA 10M ON temp_student_tablespace

SQL> CREATE USER student_user

IDENTIFIED BY student_password

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA 5M ON users

QUOTA 10M ON temp

;

用户已创建

SQL> ALTER USER student_user ACCOUNT LOCK;

用户已更改。

SQL> ALTER USER student_user ACCOUNT UNLOCK ;

用户已更改。

SQL> ALTER USER student_user PASSWORD EXPIRE;

用户已更改。

SQL> DROP USER student_user CASCADE;

用户已丢弃

SQL> SHOW PARAMETER;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY boolean FALSE

active_instance_count integer

aq_tm_processes integer 1

archive_lag_target integer 0

audit_sys_operations boolean FALSE

audit_trail string NONE

background_core_dump string partial

background_dump_dest string D:\oracle\admin\ORCL\bdump

backup_tape_io_slaves boolean FALSE

bitmap_merge_area_size integer 1048576

blank_trimming boolean FALSE

buffer_pool_keep string

buffer_pool_recycle string

circuits integer 170

cluster_database boolean FALSE

cluster_database_instances integer 1

cluster_interconnects string

commit_point_strength integer 1

compatible string 9.2.0.0.0

control_file_record_keep_time integer 7

control_files string D:\oracle\oradata\ORCL\CONTROL

01.CTL, D:\oracle\oradata\ORCL

\CONTROL02.CTL, D:\oracle\orad

ata\ORCL\CONTROL03.CTL

core_dump_dest string D:\oracle\admin\ORCL\cdump

cpu_count integer 2

create_bitmap_area_size integer 8388608

cursor_sharing string EXACT

cursor_space_for_time boolean FALSE

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_block_buffers integer 0

db_block_checking boolean FALSE

db_block_checksum boolean TRUE

db_block_size integer 8192

db_cache_advice string ON

db_cache_size big integer 25165824

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

db_domain string

db_file_multiblock_read_count integer 16

db_file_name_convert string

db_files integer 200

db_keep_cache_size big integer 0

db_name string ORCL

db_recycle_cache_size big integer 0

db_writer_processes integer 1

dblink_encrypt_login boolean FALSE

dbwr_io_slaves integer 0

dg_broker_config_file1 string %ORACLE_HOME%\DATABASE\DR1%ORA

CLE_SID%.DAT

dg_broker_config_file2 string %ORACLE_HOME%\DATABASE\DR2%ORA

CLE_SID%.DAT

dg_broker_start boolean FALSE

disk_asynch_io boolean TRUE

dispatchers string (PROTOCOL=TCP) (SERVICE=ORCLXD

distributed_lock_timeout integer 60

dml_locks integer 748

drs_start boolean FALSE

enqueue_resources integer 968

event string

fal_client string

fal_server string

fast_start_io_target integer 0

fast_start_mttr_target integer 300

fast_start_parallel_rollback string LOW

file_mapping boolean FALSE

filesystemio_options string

fixed_date string

gc_files_to_locks string

global_context_pool_size string

global_names boolean FALSE

hash_area_size integer 1048576

hash_join_enabled boolean TRUE

hi_shared_memory_address integer 0

hs_autoregister boolean TRUE

ifile file

instance_groups string

instance_name string ORCL

instance_number integer 0

java_max_sessionspace_size integer 0

java_pool_size big integer 33554432

java_soft_sessionspace_limit integer 0

job_queue_processes integer 10

large_pool_size big integer 8388608

license_max_sessions integer 0

license_max_users integer 0

license_sessions_warning integer 0

local_listener string

lock_name_space string

lock_sga boolean FALSE

log_archive_dest string

log_archive_dest_1 string

log_archive_dest_10 string

log_archive_dest_2 string

log_archive_dest_3 string

log_archive_dest_4 string

log_archive_dest_5 string

log_archive_dest_6 string

log_archive_dest_7 string

log_archive_dest_8 string

log_archive_dest_9 string

log_archive_dest_state_1 string enable

log_archive_dest_state_10 string enable

log_archive_dest_state_2 string enable

log_archive_dest_state_3 string enable

log_archive_dest_state_4 string enable

log_archive_dest_state_5 string enable

log_archive_dest_state_6 string enable

log_archive_dest_state_7 string enable

log_archive_dest_state_8 string enable

log_archive_dest_state_9 string enable

log_archive_duplex_dest string

log_archive_format string ARC%S.%T

log_archive_max_processes integer 2

log_archive_min_succeed_dest integer 1

log_archive_start boolean FALSE

log_archive_trace integer 0

log_buffer integer 524288

log_checkpoint_interval integer 0

log_checkpoint_timeout integer 1800

log_checkpoints_to_alert boolean FALSE

log_file_name_convert string

log_parallelism integer 1

logmnr_max_persistent_sessions integer 1

max_commit_propagation_delay integer 700

max_dispatchers integer 5

max_dump_file_size string UNLIMITED

max_enabled_roles integer 30

max_rollback_segments integer 37

max_shared_servers integer 20

mts_circuits integer 170

mts_dispatchers string (PROTOCOL=TCP) (SERVICE=ORCLXD

mts_listener_address string

mts_max_dispatchers integer 5

mts_max_servers integer 20

mts_multiple_listeners boolean FALSE

mts_servers integer 1

mts_service string ORCL

mts_sessions integer 165

nls_calendar string

nls_comp string

nls_currency string

nls_date_format string

nls_date_language string

nls_dual_currency string

nls_iso_currency string

nls_language string AMERICAN

nls_length_semantics string BYTE

nls_nchar_conv_excp string FALSE

nls_numeric_characters string

nls_sort string

nls_territory string AMERICA

nls_time_format string

nls_time_tz_format string

nls_timestamp_format string

nls_timestamp_tz_format string

object_cache_max_size_percent integer 10

object_cache_optimal_size integer 102400

olap_page_pool_size integer 33554432

open_cursors integer 300

open_links integer 4

open_links_per_instance integer 4

optimizer_dynamic_sampling integer 1

optimizer_features_enable string 9.2.0

optimizer_index_caching integer 0

optimizer_index_cost_adj integer 100

optimizer_max_permutations integer 2000

optimizer_mode string CHOOSE

oracle_trace_collection_name string

oracle_trace_collection_path string %ORACLE_HOME%\OTRACE\ADMIN\CDF

oracle_trace_collection_size integer 5242880

oracle_trace_enable boolean FALSE

oracle_trace_facility_name string oracled

oracle_trace_facility_path string %ORACLE_HOME%\OTRACE\ADMIN\FDF

os_authent_prefix string OPS$

os_roles boolean FALSE

parallel_adaptive_multi_user boolean FALSE

parallel_automatic_tuning boolean FALSE

parallel_execution_message_size integer 2148

parallel_instance_group string

parallel_max_servers integer 5

parallel_min_percent integer 0

parallel_min_servers integer 0

parallel_server boolean FALSE

parallel_server_instances integer 1

parallel_threads_per_cpu integer 2

partition_view_enabled boolean FALSE

pga_aggregate_target big integer 25165824

plsql_compiler_flags string INTERPRETED

plsql_native_c_compiler string

plsql_native_library_dir string

plsql_native_library_subdir_count integer 0

plsql_native_linker string

plsql_native_make_file_name string

plsql_native_make_utility string

plsql_v2_compatibility boolean FALSE

pre_page_sga boolean FALSE

processes integer 150

query_rewrite_enabled string FALSE

query_rewrite_integrity string enforced

rdbms_server_dn string

read_only_open_delayed boolean FALSE

recovery_parallelism integer 0

remote_archive_enable string true

remote_dependencies_mode string TIMESTAMP

remote_listener string

remote_login_passwordfile string EXCLUSIVE

remote_os_authent boolean FALSE

remote_os_roles boolean FALSE

replication_dependency_tracking boolean TRUE

resource_limit boolean FALSE

resource_manager_plan string

rollback_segments string

row_locking string always

serial_reuse string DISABLE

serializable boolean FALSE

service_names string ORCL

session_cached_cursors integer 0

session_max_open_files integer 10

sessions integer 170

sga_max_size big integer 135338868

shadow_core_dump string partial

shared_memory_address integer 0

shared_pool_reserved_size big integer 2516582

shared_pool_size big integer 50331648

shared_server_sessions integer 165

shared_servers integer 1

sort_area_retained_size integer 0

sort_area_size integer 524288

spfile string %ORACLE_HOME%\DATABASE\SPFILE%

ORACLE_SID%.ORA

sql92_security boolean FALSE

sql_trace boolean FALSE

sql_version string NATIVE

standby_archive_dest string %ORACLE_HOME%\RDBMS

standby_file_management string MANUAL

star_transformation_enabled string FALSE

statistics_level string TYPICAL

tape_asynch_io boolean TRUE

thread integer 0

timed_os_statistics integer 0

timed_statistics boolean TRUE

trace_enabled boolean TRUE

tracefile_identifier string

transaction_auditing boolean TRUE

transactions integer 187

transactions_per_rollback_segment integer 5

undo_management string AUTO

undo_retention integer 10800

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS1

use_indirect_data_buffers boolean FALSE

user_dump_dest string D:\oracle\admin\ORCL\udump

utl_file_dir string

workarea_size_policy string AUTO

【权限管理】

SQL> GRANT select,update,insert,delete ON scott.my_emp TO student_user WITH ADMIN OPTION;

授权成功。

SQL> GRANT create table, create view, create sequence TO student_user;

授权成功。

SQL> GRANT create session TO student_user;

授权成功。

SQL> GRANT all ON scott.my_emp TO PUBLIC ;

授权成功。

SQL> REVOKE all ON scott.my_emp FROM PUBLIC ;

撤销成功。

SQL> REVOKE create table FROM student_user;

撤销成功。

SQL> REVOKE insert ON scott.my_emp FROM student_user ;

撤销成功。

SQL> DESC user_sys_privs;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

SQL> DESC dba_sys_privs;

名称是否为空? 类型

----------------------------------------- -------- ---------------------------

GRANTEE NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

【角色管理】

SQL> CREATE ROLE student_role;

角色已创建

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO student_role;

授权成功。

SQL> GRANT student_role TO student_user;

授权成功。

SQL> REVOKE create session FROM student_role;

撤销成功。

SQL> DROP ROLE student_role;

角色已丢弃

【PL/SQL编程】

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE emp_no varchar2(10);

2 emp_name varchar2(20);

3 is_exist boolean default false ;

4 emp_sex constant char := 'M' ;

5 BEGIN

6 emp_no := '8888';

7 emp_name := 'zhangsan';

8 END;

9 /

PL/SQL 过程已成功完成。

SQL> DECLARE

2 e_no VARCHAR2(10) := '1001';

3 e_name VARCHAR2(20) := 'zhangsan';

4 is_exist BOOLEAN DEFAULT TRUE;

5 e_info CONSTANT VARCHAR2(30) := 'hello oracle';

6 BEGIN

7 e_no := '8888';

8 dbms_output.put_line('e_no='||e_no||' e_name='||e_name||' e_info='||e_info);

9 END;

10 /

e_no=8888 e_name=zhangsan e_info=hello oracle

PL/SQL 过程已成功完成。

SQL> DECLARE

2 e_no VARCHAR2(10);

3 e_name VARCHAR2(20);

4 is_exist BOOLEAN DEFAULT TRUE;

5 e_info CONSTANT VARCHAR2(30) := 'hello oracle';

6 BEGIN

7 SELECT empno, ename INTO e_no, e_name FROM emp WHERE empno = 7369;

8 dbms_output.put_line('e_no=' || e_no || ' e_name=' || e_name ||

9 ' e_info=' || e_info);

10 END;

11 /

e_no=7369 e_name=SMITH e_info=hello oracle

PL/SQL 过程已成功完成。

SQL> DECLARE

2 e_no VARCHAR2(10);

3 e_name VARCHAR2(20);

4 is_exist BOOLEAN DEFAULT TRUE;

5 e_info CONSTANT VARCHAR2(30) := 'hello oracle';

6 BEGIN

7 SELECT empno INTO e_no FROM emp WHERE empno = 7369;

8 e_name := 'zhangsan' ;

9 dbms_output.put_line('e_no=' || e_no || ' e_name=' || e_name ||' e_info=' || e_info);

10 END;

11 /

e_no=7369 e_name=zhangsan e_info=hello oracle

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 BEGIN

6 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 7369;

7 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

8 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

9 EXCEPTION

10 WHEN no_data_found THEN

11 dbms_output.put_line('没有数据');

12 END;

13 /

empno:7369 ename:SMITH

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 BEGIN

6 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 7369;

7 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

8 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

9 dbms_output.put_line('empno:' || emp_row.empno || ' ' || 'ename:' || emp_row.ename);

10 EXCEPTION

11 WHEN no_data_found THEN

12 dbms_output.put_line('没有数据');

13 END;

14 /

empno:7369 ename:SMITH

empno:7788 ename:SCOTT

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 today DATE;

6 BEGIN

7 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 7369;

8 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

9 today := SYSDATE;

10 IF (today > '12-1月-99')

11 THEN

12 dbms_output.put_line('YES!!!');

13 ELSE

14 dbms_output.put_line('NO!!!');

15 END IF;

16 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

17 dbms_output.put_line('empno:' || emp_row.empno || ' ' || 'ename:' ||

18 emp_row.ename);

19 EXCEPTION

20 WHEN no_data_found THEN

21 dbms_output.put_line('没有数据');

22 END;

23 /

YES!!!

empno:7369 ename:SMITH

empno:7788 ename:SCOTT

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 today DATE;

6 BEGIN

7 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 7369;

8 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

9 today := SYSDATE;

10 IF today > to_date('1999-01-01', 'YYYY-MM-DD')

11 THEN

12 dbms_output.put_line('YES!!!');

13 ELSE

14 dbms_output.put_line('NO!!!');

15 END IF;

16 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

17 dbms_output.put_line('empno:' || emp_row.empno || ' ' || 'ename:' ||

18 emp_row.ename);

19 EXCEPTION

20 WHEN no_data_found THEN

21 dbms_output.put_line('没有数据');

22 END;

23 /

YES!!!

empno:7369 ename:SMITH

empno:7788 ename:SCOTT

PL/SQL 过程已成功完成。

SQL> DECLARE

2 icount NUMBER := 0;

3 isum NUMBER := 0;

4 BEGIN

5 LOOP

6 icount := icount + 1;

7 EXIT WHEN icount > 100;

8 isum := isum + icount;

9 END LOOP;

10 dbms_output.put_line('1+2+3...+100=' || to_char(isum));

11 END;

12 /

1+2+3...+100=5050

PL/SQL 过程已成功完成。

SQL> DECLARE

2 icount NUMBER := 0;

3 isum NUMBER := 0;

4 BEGIN

5 WHILE icount < 100

6 LOOP

7 icount := icount + 1;

8 isum := isum + icount;

9 END LOOP;

10 dbms_output.put_line('1+2+3...+100=' || to_char(isum));

11 END;

12 /

1+2+3...+100=5050

PL/SQL 过程已成功完成。

SQL> DECLARE

2 isum NUMBER := 0;

3 BEGIN

4 FOR i IN 1 .. 100

5 LOOP

6 isum := isum + i;

7 END LOOP;

8 dbms_output.put_line('1+2+3...+100=' || to_char(isum));

9 END;

10 /

1+2+3...+100=5050

PL/SQL 过程已成功完成。

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

2 emp_no emp.empno%TYPE;

3 emp_name emp.ename%TYPE;

4 BEGIN

5 emp_no := 9999;

6 emp_name := 'zhangsan';

7 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name);

8 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 7788;

9 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name);

10 END;

11 /

emp_no = 9999 emp_name = zhangsan

emp_no = 7788 emp_name = SCOTT

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno%TYPE;

3 emp_name emp.ename%TYPE;

4 emp_count number := 0;

5 BEGIN

6 emp_no := 9999;

7 emp_name := 'zhangsan';

8 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name|| ' emp_count = '||emp_count);

9 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 7788;

10 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name|| ' emp_count = '||emp_count);

11 SELECT count(1) INTO emp_count FROM emp ;

12 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name|| ' emp_count = '||emp_count);

13 END;

14 /

emp_no = 9999 emp_name = zhangsan emp_count = 0

emp_no = 7788 emp_name = SCOTT emp_count = 0

emp_no = 7788 emp_name = SCOTT emp_count = 14

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno%TYPE;

3 emp_name emp.ename%TYPE;

4 emp_count NUMBER := 0;

5 flag BOOLEAN DEFAULT TRUE;

6 BEGIN

7 emp_no := 9999;

8 emp_name := 'zhangsan';

9 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name || ' emp_count = ' || emp_count);

10 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 7788;

11 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name || ' emp_count = ' || emp_count);

12 SELECT COUNT(1) INTO emp_count FROM emp;

13 dbms_output.put_line('emp_no = ' || emp_no || ' emp_name = ' || emp_name || ' emp_count = ' || emp_count);

14 IF flag

15 THEN

16 dbms_output.put_line('TRUE!');

17 ELSE

18 dbms_output.put_line('FALSE!');

19 END IF;

20 END;

21 /

emp_no = 9999 emp_name = zhangsan emp_count = 0

emp_no = 7788 emp_name = SCOTT emp_count = 0

emp_no = 7788 emp_name = SCOTT emp_count = 14

TRUE!

PL/SQL 过程已成功完成。

【PL/SQL异常】

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 today DATE;

6 BEGIN

7 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE empno = 9999;

8 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

9 today := SYSDATE;

10 IF today > to_date('1999-01-01', 'YYYY-MM-DD')

11 THEN

12 dbms_output.put_line('YES!!!');

13 ELSE

14 dbms_output.put_line('NO!!!');

15 END IF;

16 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

17 dbms_output.put_line('empno:' || emp_row.empno || ' ' || 'ename:' || emp_row.ename);

18 EXCEPTION

19 WHEN no_data_found THEN

20 dbms_output.put_line('没有数据');

21 END;

22 /

没有数据

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 today DATE;

6 BEGIN

7 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE sal < 5000;

8 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

9 today := SYSDATE;

10 IF today > to_date('1999-01-01', 'YYYY-MM-DD')

11 THEN

12 dbms_output.put_line('YES!!!');

13 ELSE

14 dbms_output.put_line('NO!!!');

15 END IF;

16 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

17 dbms_output.put_line('empno:' || emp_row.empno || ' ' || 'ename:' || emp_row.ename);

18 EXCEPTION

19 WHEN no_data_found THEN

20 dbms_output.put_line('没有数据');

21 WHEN too_many_rows THEN

22 dbms_output.put_line('值过多');

23 END;

24 /

值过多

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 today DATE;

6 icount NUMBER := 5;

7 BEGIN

8 icount := 10 / 0;

9 today := SYSDATE;

10 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE sal = 7788;

11 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

12 IF today > to_date('1999-01-01', 'YYYY-MM-DD')

13 THEN

14 dbms_output.put_line('YES!!!');

15 ELSE

16 dbms_output.put_line('NO!!!');

17 END IF;

18 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

19 dbms_output.put_line('empno:' || emp_row.empno || ' ' || 'ename:' || emp_row.ename);

20 EXCEPTION

21 WHEN no_data_found THEN

22 dbms_output.put_line('没有数据');

23 WHEN too_many_rows THEN

24 dbms_output.put_line('值过多');

25 WHEN OTHERS THEN

26 dbms_output.put_line('其他错误');

27 END;

28 /

其他错误

PL/SQL 过程已成功完成。

SQL> DECLARE

2 emp_no emp.empno %TYPE;

3 emp_name emp.ename%TYPE;

4 emp_row emp%ROWTYPE;

5 today DATE;

6 icount NUMBER := 5;

7 BEGIN

8 icount := 10 / 0;

9 today := SYSDATE;

10 SELECT empno, ename INTO emp_no, emp_name FROM emp WHERE sal = 7788;

11 SELECT * INTO emp_row FROM emp WHERE empno = 7788;

12 IF today > to_date('1999-01-01', 'YYYY-MM-DD')

13 THEN

14 dbms_output.put_line('YES!!!');

15 ELSE

16 dbms_output.put_line('NO!!!');

17 END IF;

18 dbms_output.put_line('empno:' || emp_no || ' ' || 'ename:' || emp_name);

19 dbms_output.put_line('empno:' || emp_row.empno || ' ' || 'ename:' || emp_row.ename);

20 EXCEPTION

21 WHEN no_data_found THEN

22 dbms_output.put_line('没有数据');

23 WHEN too_many_rows THEN

24 dbms_output.put_line('值过多');

25 WHEN zero_divide THEN

26 dbms_output.put_line('除数为0');

27 WHEN OTHERS THEN

28 dbms_output.put_line('其他错误');

29 END;

30 /

除数为0

PL/SQL 过程已成功完成。

SQL> CREATE TABLE place (id number not null primary key,name varchar2(20));

表已创建。

SQL> INSERT INTO place VALUES(1,'HANGZHOU');

已创建 1 行。

SQL> DECLARE

2 dup_value_exception EXCEPTION;

3 icount NUMBER := 0;

4 BEGIN

5 SELECT COUNT(1) INTO icount FROM place WHERE id = 1;

6 IF icount > 0

7 THEN

8 RAISE dup_value_exception;

9 END IF;

10 EXCEPTION

11 WHEN dup_value_exception THEN

12 dbms_output.put_line('place表中已经存在id为1的数据！');

13 END;

14 /

place表中已经存在id为1的数据！

PL/SQL 过程已成功完成。

SQL> DECLARE

2 dup_value_exception EXCEPTION;

3 PRAGMA EXCEPTION_INIT(dup_value_exception, -1);

4 icount NUMBER := 0;

5 BEGIN

6 SELECT COUNT(1) INTO icount FROM place WHERE id = 1;

7 IF icount > 0

8 THEN

9 RAISE dup_value_exception;

10 END IF;

11 EXCEPTION

12 WHEN dup_value_exception THEN

13 dbms_output.put_line('place表中已经存在id为1的数据！');

14 END;

15 /

PL/SQL 过程已成功完成。

SQL> DECLARE

2 dup_value_exception EXCEPTION;

3 PRAGMA EXCEPTION_INIT(dup_value_exception, -1);

4 BEGIN

5 INSERT INTO place VALUES (1, 'hangzhou');

6 EXCEPTION

7 WHEN dup_value_exception THEN

8 dbms_output.put_line('place表中已经存在id为1的数据！');

9 END;

10 /

place表中已经存在id为1的数据！

PL/SQL 过程已成功完成。

SQL> DECLARE

2 dup_value_exception EXCEPTION;

3 PRAGMA EXCEPTION_INIT(dup_value_exception, -1);

4 BEGIN

5 INSERT INTO place VALUES (1, 'hangzhou');

6 EXCEPTION

7 WHEN dup_value_exception THEN

8 raise_application_error(-20001, 'place表中已经存在id为1的数据！');

9 END;

10 /

DECLARE

ERROR 位于第 1 行:

ORA-20001: place表中已经存在id为1的数据！

ORA-06512: 在line 8

【游标】

SQL> BEGIN

2 INSERT INTO place VALUES (2, 'hangzhou');

3 dbms_output.put_line('rowcount = ' || SQL%ROWCOUNT);

4 END;

5 /

rowcount = 1

PL/SQL 过程已成功完成。

SQL> BEGIN

2 INSERT INTO place VALUES (3, 'hangzhou');

3 dbms_output.put_line('rowcount = ' || SQL%ROWCOUNT);

4 IF SQL%FOUND

5 THEN

6 dbms_output.put_line('found!');

7 END IF;

8 IF SQL%NOTFOUND

9 THEN

10 dbms_output.put_line('notfound!');

11 END IF;

12 IF SQL%ISOPEN

13 THEN

14 dbms_output.put_line('isopen!');

15 ELSE

16 dbms_output.put_line('notopen!');

17 END IF;

18 END;

19 /

rowcount = 1

found!

notopen!

PL/SQL 过程已成功完成。

SQL> BEGIN

2 UPDATE place SET NAME = 'beijing';

3 dbms_output.put_line('rowcount = ' || SQL%ROWCOUNT);

4 IF SQL%FOUND

5 THEN

6 dbms_output.put_line('found!');

7 END IF;

8 IF SQL%NOTFOUND

9 THEN

10 dbms_output.put_line('notfound!');

11 END IF;

12 IF SQL%ISOPEN

13 THEN

14 dbms_output.put_line('isopen!');

15 ELSE

16 dbms_output.put_line('notopen!');

17 END IF;

18 END;

19 /

rowcount = 6

found!

notopen!

PL/SQL 过程已成功完成。

SQL> set serveroutput on;

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp;

4 emp_row emp%ROWTYPE;

5 BEGIN

6 OPEN emp_cursor;

7 FETCH emp_cursor

8 INTO emp_row;

9 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename);

10 CLOSE emp_cursor;

11 END;

12 /

emp_no = 7369 emp_name = SMITH

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp;

4 emp_row emp%ROWTYPE;

5 BEGIN

6 OPEN emp_cursor;

7 LOOP

8 FETCH emp_cursor

9 INTO emp_row;

10 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename);

11 EXIT WHEN emp_cursor%NOTFOUND;

12 END LOOP;

13 CLOSE emp_cursor;

14 END;

15 /

emp_no = 7369 emp_name = SMITH

emp_no = 7499 emp_name = ALLEN

emp_no = 7521 emp_name = WARD

emp_no = 7566 emp_name = JONES

emp_no = 7654 emp_name = MARTIN

emp_no = 7698 emp_name = BLAKE

emp_no = 7782 emp_name = CLARK

emp_no = 7788 emp_name = SCOTT

emp_no = 7839 emp_name = KING

emp_no = 7844 emp_name = TURNER

emp_no = 7876 emp_name = ADAMS

emp_no = 7900 emp_name = JAMES

emp_no = 7902 emp_name = FORD

emp_no = 7934 emp_name = MILLER

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp;

4 emp_row emp%ROWTYPE;

5 BEGIN

6 OPEN emp_cursor;

7 LOOP

8 FETCH emp_cursor

9 INTO emp_row;

10 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename || ' rowcount =' || emp_cursor%ROWCOUNT);

11 EXIT WHEN emp_cursor%NOTFOUND;

12 END LOOP;

13 CLOSE emp_cursor;

14 END;

15 /

emp_no = 7369 emp_name = SMITH rowcount =1

emp_no = 7499 emp_name = ALLEN rowcount =2

emp_no = 7521 emp_name = WARD rowcount =3

emp_no = 7566 emp_name = JONES rowcount =4

emp_no = 7654 emp_name = MARTIN rowcount =5

emp_no = 7698 emp_name = BLAKE rowcount =6

emp_no = 7782 emp_name = CLARK rowcount =7

emp_no = 7788 emp_name = SCOTT rowcount =8

emp_no = 7839 emp_name = KING rowcount =9

emp_no = 7844 emp_name = TURNER rowcount =10

emp_no = 7876 emp_name = ADAMS rowcount =11

emp_no = 7900 emp_name = JAMES rowcount =12

emp_no = 7902 emp_name = FORD rowcount =13

emp_no = 7934 emp_name = MILLER rowcount =14

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp;

4 emp_row emp%ROWTYPE;

5 BEGIN

6 OPEN emp_cursor;

7 WHILE emp_cursor%FOUND

8 LOOP

9 FETCH emp_cursor

10 INTO emp_row;

11 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename || ' rowcount =' || emp_cursor%ROWCOUNT);

12 END LOOP;

13 CLOSE emp_cursor;

14 END;

15 /

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp;

4 emp_row emp%ROWTYPE;

5 BEGIN

6 OPEN emp_cursor;

7 FETCH emp_cursor

8 INTO emp_row;

9 WHILE emp_cursor%FOUND

10 LOOP

11 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename || ' rowcount =' || emp_cursor%ROWCOUNT);

12 FETCH emp_cursor

13 INTO emp_row;

14 END LOOP;

15 CLOSE emp_cursor;

16 END;

17 /

emp_no = 7369 emp_name = SMITH rowcount =1

emp_no = 7499 emp_name = ALLEN rowcount =2

emp_no = 7521 emp_name = WARD rowcount =3

emp_no = 7566 emp_name = JONES rowcount =4

emp_no = 7654 emp_name = MARTIN rowcount =5

emp_no = 7698 emp_name = BLAKE rowcount =6

emp_no = 7782 emp_name = CLARK rowcount =7

emp_no = 7788 emp_name = SCOTT rowcount =8

emp_no = 7839 emp_name = KING rowcount =9

emp_no = 7844 emp_name = TURNER rowcount =10

emp_no = 7876 emp_name = ADAMS rowcount =11

emp_no = 7900 emp_name = JAMES rowcount =12

emp_no = 7902 emp_name = FORD rowcount =13

emp_no = 7934 emp_name = MILLER rowcount =14

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp;

4 BEGIN

5 FOR emp_row IN emp_cursor

6 LOOP

7 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename || ' rowcount =' || emp_cursor%ROWCOUNT);

8 END LOOP;

9 END;

10 /

emp_no = 7369 emp_name = SMITH rowcount =1

emp_no = 7499 emp_name = ALLEN rowcount =2

emp_no = 7521 emp_name = WARD rowcount =3

emp_no = 7566 emp_name = JONES rowcount =4

emp_no = 7654 emp_name = MARTIN rowcount =5

emp_no = 7698 emp_name = BLAKE rowcount =6

emp_no = 7782 emp_name = CLARK rowcount =7

emp_no = 7788 emp_name = SCOTT rowcount =8

emp_no = 7839 emp_name = KING rowcount =9

emp_no = 7844 emp_name = TURNER rowcount =10

emp_no = 7876 emp_name = ADAMS rowcount =11

emp_no = 7900 emp_name = JAMES rowcount =12

emp_no = 7902 emp_name = FORD rowcount =13

emp_no = 7934 emp_name = MILLER rowcount =14

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp WHERE empno = &no;

4 BEGIN

5 FOR emp_row IN emp_cursor

6 LOOP

7 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename || ' rowcount =' || emp_cursor%ROWCOUNT);

8 END LOOP;

9 END;

10 /

输入 no 的值: 7369

原值 3: SELECT * FROM emp WHERE empno = &no;

新值 3: SELECT * FROM emp WHERE empno = 7369;

emp_no = 7369 emp_name = SMITH rowcount =1

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp WHERE ename = '&name';

4 BEGIN

5 FOR emp_row IN emp_cursor

6 LOOP

7 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename || ' rowcount =' || emp_cursor%ROWCOUNT);

8 END LOOP;

9 END;

10 /

输入 name 的值: SMITH

原值 3: SELECT * FROM emp WHERE ename = '&name';

新值 3: SELECT * FROM emp WHERE ename = 'SMITH';

emp_no = 7369 emp_name = SMITH rowcount =1

PL/SQL 过程已成功完成。

SQL> DECLARE

2 CURSOR emp_cursor IS

3 SELECT * FROM emp WHERE sal > &input_sal;

4 icount NUMBER;

5 BEGIN

6 icount := &input_count;

7 dbms_output.put_line('icount= ' || icount);

8 FOR emp_row IN emp_cursor

9 LOOP

10 dbms_output.put_line('emp_no = ' || emp_row.empno || ' emp_name = ' || emp_row.ename || ' rowcount =' || emp_cursor%ROWCOUNT);

11 END LOOP;

12 END;

13 /

输入 input_sal 的值: 100

原值 3: SELECT * FROM emp WHERE sal > &input_sal;

新值 3: SELECT * FROM emp WHERE sal > 100;

输入 input_count 的值: 999

原值 6: icount := &input_count;

新值 6: icount := 999;

icount= 999

emp_no = 7369 emp_name = SMITH rowcount =1

emp_no = 7499 emp_name = ALLEN rowcount =2

emp_no = 7521 emp_name = WARD rowcount =3

emp_no = 7566 emp_name = JONES rowcount =4

emp_no = 7654 emp_name = MARTIN rowcount =5

emp_no = 7698 emp_name = BLAKE rowcount =6

emp_no = 7782 emp_name = CLARK rowcount =7

emp_no = 7788 emp_name = SCOTT rowcount =8

emp_no = 7839 emp_name = KING rowcount =9

emp_no = 7844 emp_name = TURNER rowcount =10

emp_no = 7876 emp_name = ADAMS rowcount =11

emp_no = 7900 emp_name = JAMES rowcount =12

emp_no = 7902 emp_name = FORD rowcount =13

emp_no = 7934 emp_name = MILLER rowcount =14

PL/SQL 过程已成功完成。

SQL> DECLARE

2 TYPE ref_cursor IS REF CURSOR;

3 emp_cursor ref_cursor;

4 emp_row emp%ROWTYPE;

5 flag INT := 0;

6 BEGIN

7 flag := &input_flag;

8 IF flag = 1

9 THEN

10 OPEN emp_cursor FOR

11 SELECT *

12 FROM emp

13 WHERE sal > 100

14 AND sal < 1000;

15 ELSIF flag = 2

16 THEN

17 OPEN emp_cursor FOR

18 SELECT *

19 FROM emp

20 WHERE sal > 1000

21 AND sal < 2000;

22 ELSIF flag = 3

23 THEN

24 OPEN emp_cursor FOR

25 SELECT * FROM emp WHERE sal > 3000;

26 ELSE

27 OPEN emp_cursor FOR

28 SELECT * FROM emp;

29 END IF;

30 /**************************ERROR**********************

31 FOR emp_row IN emp_cursor

32 LOOP

33 dbms_output.put_line('ename = '|| emp_row.ename);

34 END LOOP;

35 *****************************************************/

36 LOOP

37 FETCH emp_cursor

38 INTO emp_row;

39 dbms_output.put_line('ename = ' || emp_row.ename);

40 EXIT WHEN emp_cursor%NOTFOUND;

41 END LOOP;

42 END;

43 /

输入 input_flag 的值: 2

原值 7: flag := &input_flag;

新值 7: flag := 2;

ename = ALLEN

ename = WARD

ename = MARTIN

ename = TURNER

ename = ADAMS

ename = MILLER

PL/SQL 过程已成功完成。

【子程序：过程函数】

SQL> CREATE OR REPLACE PROCEDURE my_procedure AS

2 i INTEGER;

3 j INTEGER;

4 BEGIN

5 dbms_output.put_line('9*9乘法表');

6 FOR i IN 1 .. 9

7 LOOP

8 FOR j IN 1 .. 9

9 LOOP

10 IF i >= j

11 THEN

12 dbms_output.put(to_char(i) || '*' || to_char(j) || ' = ' || to_char(i * j) || ' ');

13 END IF;

14 END LOOP;

15 dbms_output.put_line(' ');

16 END LOOP;

17 END;

18 /

过程已创建。

SQL> execute my_procedure;

9*9乘法表

1*1 = 1

2*1 = 2 2*2 = 4

3*1 = 3 3*2 = 6 3*3 = 9

4*1 = 4 4*2 = 8 4*3 = 12 4*4 = 16

5*1 = 5 5*2 = 10 5*3 = 15 5*4 = 20 5*5 = 25

6*1 = 6 6*2 = 12 6*3 = 18 6*4 = 24 6*5 = 30 6*6 = 36

7*1 = 7 7*2 = 14 7*3 = 21 7*4 = 28 7*5 = 35 7*6 = 42 7*7 = 49

8*1 = 8 8*2 = 16 8*3 = 24 8*4 = 32 8*5 = 40 8*6 = 48 8*7 = 56 8*8 = 64

9*1 = 9 9*2 = 18 9*3 = 27 9*4 = 36 9*5 = 45 9*6 = 54 9*7 = 63 9*8 = 72 9*9 = 81

PL/SQL 过程已成功完成。

SQL> BEGIN

2 my_procedure;

3 END;

4 /

9*9乘法表

1*1 = 1

2*1 = 2 2*2 = 4

3*1 = 3 3*2 = 6 3*3 = 9

4*1 = 4 4*2 = 8 4*3 = 12 4*4 = 16

5*1 = 5 5*2 = 10 5*3 = 15 5*4 = 20 5*5 = 25

6*1 = 6 6*2 = 12 6*3 = 18 6*4 = 24 6*5 = 30 6*6 = 36

7*1 = 7 7*2 = 14 7*3 = 21 7*4 = 28 7*5 = 35 7*6 = 42 7*7 = 49

8*1 = 8 8*2 = 16 8*3 = 24 8*4 = 32 8*5 = 40 8*6 = 48 8*7 = 56 8*8 = 64

9*1 = 9 9*2 = 18 9*3 = 27 9*4 = 36 9*5 = 45 9*6 = 54 9*7 = 63 9*8 = 72 9*9 = 81

PL/SQL 过程已成功完成。

SQL> CREATE OR REPLACE PROCEDURE query_procedure(find_no emp.empno%TYPE) AS

2 e_name emp.ename%TYPE;

3 e_job emp.job%TYPE;

4 BEGIN

5 SELECT ename, job INTO e_name, e_job FROM emp WHERE empno = find_no;

6 dbms_output.put_line('empno:' || find_no || ' ename:' || e_name || ' job:' || e_job);

7 EXCEPTION

8 WHEN no_data_found THEN

9 dbms_output.put_line('未找到数据！');

10 WHEN too_many_rows THEN

11 dbms_output.put_line('返回记录过多！');

12 WHEN OTHERS THEN

13 dbms_output.put_line('其他错误！');

14 END;

15 /

过程已创建。

SQL> execute query_procedure(7369);

empno:7369 ename:SMITH job:CLERK

PL/SQL 过程已成功完成。

SQL> BEGIN

2 query_procedure(7369);

3 END;

4 /

empno:7369 ename:SMITH job:CLERK

PL/SQL 过程已成功完成。

SQL> desc user_source;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

NAME VARCHAR2(30)

TYPE VARCHAR2(12)

LINE NUMBER

TEXT VARCHAR2(4000)

SQL> select * from user_source where name = 'QUERY_PROCEDURE';

NAME TYPE LINE

------------------------------ ------------ ----------

TEXT

--------------------------------------------------------------------------------------

QUERY_PROCEDURE PROCEDURE 1

PROCEDURE query_procedure(find_no emp.empno%TYPE) AS

QUERY_PROCEDURE PROCEDURE 2

e_name emp.ename%TYPE;

QUERY_PROCEDURE PROCEDURE 3

e_job emp.job%TYPE;

QUERY_PROCEDURE PROCEDURE 4

BEGIN

QUERY_PROCEDURE PROCEDURE 5

SELECT ename, job INTO e_name, e_job FROM emp WHERE empno = find_no;

QUERY_PROCEDURE PROCEDURE 6

dbms_output.put_line('empno:' || find_no || ' ename:' || e_name || ' job:' ||

QUERY_PROCEDURE PROCEDURE 7

EXCEPTION

QUERY_PROCEDURE PROCEDURE 8

WHEN no_data_found THEN

QUERY_PROCEDURE PROCEDURE 9

dbms_output.put_line('未找到数据！');

QUERY_PROCEDURE PROCEDURE 10

WHEN too_many_rows THEN

QUERY_PROCEDURE PROCEDURE 11

dbms_output.put_line('返回记录过多！');

QUERY_PROCEDURE PROCEDURE 12

WHEN OTHERS THEN

QUERY_PROCEDURE PROCEDURE 13

dbms_output.put_line('其他错误！');

QUERY_PROCEDURE PROCEDURE 14

END;

已选择14行。

SQL> CREATE OR REPLACE PROCEDURE run_by_parameter_pro

2 (

3 in_name IN emp.ename%TYPE,

4 out_job OUT VARCHAR,

5 out_sal OUT NUMBER

6 ) AS

7 icount NUMBER;

8 BEGIN

9 SELECT COUNT(1) INTO icount FROM emp WHERE ename = in_name;

10 IF icount = 1

11 THEN

12 SELECT job, sal INTO out_job, out_sal FROM emp WHERE ename = in_name;

13 out_job := '姓名为：' || in_name || '的员工工资为：' || to_char(out_sal);

14 out_sal := out_sal;

15 ELSE

16 out_job := 'job_没有找到姓名为的记录！';

17 out_sal := 0;

18 END IF;

19 EXCEPTION

20 WHEN too_many_rows THEN

21 dbms_output.put_line('返回记录过多！');

22 WHEN OTHERS THEN

23 dbms_output.put_line('意外错误！');

24 END;

25 /

过程已创建。

SQL> DECLARE

2 i_name VARCHAR(20);

3 o_job VARCHAR2(20);

4 o_sal NUMBER;

5 BEGIN

6 i_name := 'SMITH';

7 run_by_parameter_pro(i_name, o_job, o_sal);

8 dbms_output.put_line('i_name = ' || i_name || ' o_job = ' || o_job || ' o_sal = ' || o_sal);

9 END;

10 /

意外错误！

i_name = SMITH o_job = CLERK o_sal = 800

PL/SQL 过程已成功完成。

SQL> DROP PROCEDURE my_procedure;

过程已丢弃。

SQL> CREATE OR REPLACE FUNCTION get_name(input_no NUMBER) RETURN VARCHAR IS

2 out_name VARCHAR(20);

3 BEGIN

4 SELECT ename INTO out_name FROM emp WHERE empno = input_no;

5 RETURN out_name;

6 EXCEPTION

7 WHEN too_many_rows THEN

8 dbms_output.put_line('返回记录过多！');

9 WHEN OTHERS THEN

10 dbms_output.put_line('意外错误！');

11 END;

12 /

函数已创建。

SQL> DECLARE

2 o_name VARCHAR(20);

3 BEGIN

4 o_name := get_name(7369);

5 dbms_output.put_line('o_name = ' || o_name);

6 o_name := get_name(7788);

7 dbms_output.put_line('o_name = ' || o_name);

8 END;

9 /

o_name = SMITH

o_name = SCOTT

PL/SQL 过程已成功完成。

SQL> SELECT * FROM emp WHERE ename = get_name(7369);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL> SELECT * FROM user_source WHERE name = 'GET_NAME';

NAME TYPE LINE

------------------------------ ------------ ----------

TEXT

-----------------------------------------------------------------------------------------

GET_NAME FUNCTION 1

FUNCTION get_name(input_no NUMBER) RETURN VARCHAR IS

GET_NAME FUNCTION 2

out_name VARCHAR(20);

GET_NAME FUNCTION 3

BEGIN

GET_NAME FUNCTION 4

SELECT ename INTO out_name FROM emp WHERE empno = input_no;

GET_NAME FUNCTION 5

RETURN out_name;

GET_NAME FUNCTION 6

EXCEPTION

GET_NAME FUNCTION 7

WHEN too_many_rows THEN

GET_NAME FUNCTION 8

dbms_output.put_line('返回记录过多！');

GET_NAME FUNCTION 9

WHEN OTHERS THEN

GET_NAME FUNCTION 10

dbms_output.put_line('意外错误！');

GET_NAME FUNCTION 11

END;

已选择11行。

SQL> desc user_errors;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

NAME NOT NULL VARCHAR2(30)

TYPE VARCHAR2(12)

SEQUENCE NOT NULL NUMBER

LINE NOT NULL NUMBER

POSITION NOT NULL NUMBER

TEXT NOT NULL VARCHAR2(4000)

SQL> select * from user_errors;

NAME TYPE SEQUENCE LINE POSITION

------------------------------ ------------ ---------- ---------- ----------

TEXT

--------------------------------------------------------------------------------

STUDENTTYPE TYPE 1 8 1

PLS-00103: 出现符号 ")"在需要下列之一时：

return

STUDENT_TYPE TYPE 1 8 1

PLS-00103: 出现符号 ")"在需要下列之一时：

return

SQL> CREATE OR REPLACE PROCEDURE paramter_procedure

2 (

3 isal IN emp.sal%TYPE,

4 sname OUT VARCHAR,

5 sjob IN OUT VARCHAR

6 ) AS

7 icount NUMBER;

8 BEGIN

9 SELECT COUNT(*)

10 INTO icount

11 FROM emp

12 WHERE sal > isal

13 AND job = sjob;

14 IF icount = 1

15 THEN

16 SELECT ename

17 INTO sname

18 FROM emp

19 WHERE sal > isal

20 AND job = sjob;

21 sname := '名字为：' || sname;

22 sjob := '工作为:' || sjob;

23 ELSE

24 sname := '没找到名字！';

25 sjob := '没找到工作！';

26 END IF;

27 EXCEPTION

28 WHEN too_many_rows THEN

29 dbms_output.put_line('返回记录过多！');

30 WHEN OTHERS THEN

31 dbms_output.put_line('其他错误！');

32 END;

33 /

过程已创建。

SQL> DECLARE

2 p_name VARCHAR(20);

3 p_job VARCHAR(20);

4 p_sal NUMBER;

5 BEGIN

6 p_sal := 2900;

7 p_name := '';

8 p_job := 'MANAGER';

9 paramter_procedure(p_sal, p_name, p_job);

10 dbms_output.put_line('姓名：' || p_name);

11 END;

12 /

姓名：名字为：JONES

PL/SQL 过程已成功完成。

SQL> DECLARE

2 p_name VARCHAR(20);

3 p_job VARCHAR(20);

4 p_sal NUMBER;

5 BEGIN

6 p_sal := 2900;

7 p_name := '';

8 p_job := 'MANAGER';

9 paramter_procedure(p_sal, p_name, p_job);

10 dbms_output.put_line('姓名：' || p_name);

11 p_sal := 2900;

12 p_name := '';

13 p_job := 'MANAGER';

14 paramter_procedure(sjob => p_job, sname => p_name, isal => p_sal);

15 dbms_output.put_line('姓名：' || p_name);

16 END;

17 /

姓名：名字为：JONES

PL/SQL 过程已成功完成。

SQL> CREATE OR REPLACE PROCEDURE pro_1 AS

2 BEGIN

3 INSERT INTO my_dept VALUES (90, 'development', 'hangzhou');

4 END;

5 /

过程已创建。

SQL> CREATE OR REPLACE PROCEDURE pro_2 AS

2 BEGIN

3 UPDATE my_dept SET loc = 'shanghai' WHERE deptno = 10;

4 pro_1;

5 END;

6 /

过程已创建。

SQL> execute pro_2;

PL/SQL 过程已成功完成。

SQL> SELECT * FROM my_dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING shanghai

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

90 development hangzhou

SQL> CREATE OR REPLACE PROCEDURE pro_1 AS

2 BEGIN

3 INSERT INTO my_dept VALUES (90, 'development', 'hangzhou');

4 ROLLBACK;

5 END;

6 /

过程已创建。

SQL> CREATE OR REPLACE PROCEDURE pro_2 AS

2 BEGIN

3 UPDATE my_dept SET loc = 'shanghai' WHERE deptno = 10;

4 pro_1;

5 END;

6 /

过程已创建。

SQL> execute pro_2;

PL/SQL 过程已成功完成。

SQL> SELECT * FROM my_dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> CREATE OR REPLACE PROCEDURE p1 AS

2 PRAGMA AUTONOMOUS_TRANSACTION;

3 BEGIN

4 INSERT INTO my_dept VALUES (66, 'javateam', 'zhejiang');

5 ROLLBACK;

6 END;

7 /

过程已创建。

SQL> CREATE OR REPLACE PROCEDURE p2 AS

2 BEGIN

3 UPDATE my_dept SET loc = 'wuhan' WHERE deptno = 10;

4 p1;

5 END;

6 /

过程已创建。

SQL> execute p2;

PL/SQL 过程已成功完成。

SQL> select * from my_dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING wuhan

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

90 development hangzhou

【程序包】

SQL> CREATE OR REPLACE PACKAGE student_package IS

2 TYPE cur_ref_student IS REF CURSOR RETURN student%ROWTYPE;

3 PROCEDURE select_student(find_id IN student.id%TYPE);

4 PROCEDURE insert_student(new_student IN student%ROWTYPE);

5 PROCEDURE update_student(new_student IN student%ROWTYPE);

6 PROCEDURE delete_student(del_id IN student.id%TYPE);

7 PROCEDURE return_student(in_out_student IN OUT cur_ref_student);

8 FUNCTION return_record_count RETURN NUMBER;

9 END student_package;

10 /

程序包已创建。

SQL> CREATE OR REPLACE PACKAGE BODY student_package AS

3 /*select_student*/

4 PROCEDURE select_student(find_id IN student.id%TYPE) AS

5 CURSOR find_cursor IS

6 SELECT * FROM student WHERE id = find_id;

7 BEGIN

8 FOR s IN find_cursor

9 LOOP

10 dbms_output.put_line('student id : ' || s.id || ' name: ' || s.name || ' score : ' || s.score);

11 END LOOP;

12 EXCEPTION

13 WHEN no_data_found THEN

14 dbms_output.put_line('没有找到id为' || find_id || '的学生数据!');

15 WHEN OTHERS THEN

16 dbms_output.put_line('意外错误！');

17 END select_student;

19 /*insert_student*/

20 PROCEDURE insert_student(new_student IN student%ROWTYPE) AS

21 i_recorder INTEGER;

22 not_exists_student EXCEPTION;

23 BEGIN

24 SELECT COUNT(*) INTO i_recorder FROM student WHERE id = new_student.id;

25 IF i_recorder > 0

26 THEN

27 RAISE not_exists_student;

28 ELSE

29 INSERT INTO student VALUES (new_student.id, new_student.name, new_student.score, new_student.subid);

30 COMMIT;

31 END IF;

32 EXCEPTION

33 WHEN not_exists_student THEN

34 dbms_output.put_line('要插入编号为：' || new_student.id || '的学生记录已经存在！');

35 WHEN OTHERS THEN

36 dbms_output.put_line('意外插入错误！');

37 END insert_student;

39 /*update_student*/

40 PROCEDURE update_student(new_student IN student%ROWTYPE) AS

41 i_record INTEGER;

42 BEGIN

43 SELECT COUNT(*) INTO i_record FROM student WHERE id = new_student.id;

44 IF i_record = 0

45 THEN

46 dbms_output.put_line('编号为：' || new_student.id || '的学生记录不存在!');

47 ELSE

48 UPDATE student SET NAME = new_student.name WHERE id = new_student.id;

49 COMMIT;

50 dbms_output.put_line('修改学生记录成功！');

51 END IF;

52 EXCEPTION

53 WHEN OTHERS THEN

54 dbms_output.put_line('意外更新错误！');

55 END update_student;

57 /*delete_student*/

58 PROCEDURE delete_student(del_id IN student.id%TYPE) AS

59 i_rec INTEGER;

60 BEGIN

61 SELECT COUNT(*) INTO i_rec FROM student WHERE id = del_id;

62 IF i_rec = 0

63 THEN

64 dbms_output.put_line('编号为：' || del_id || '的学生记录不存在！');

65 ELSE

66 DELETE FROM student WHERE id = del_id;

67 COMMIT;

68 dbms_output.put_line('删除编号为：' || del_id || '的学生记录成功！');

69 END IF;

70 EXCEPTION

71 WHEN OTHERS THEN

72 dbms_output.put_line('删除时意外错误！');

73 END delete_student;

75 /*return_student*/

76 PROCEDURE return_student(in_out_student IN OUT cur_ref_student) AS

77 BEGIN

78 OPEN in_out_student FOR

79 SELECT * FROM student;

80 END return_student;

82 /*return_record_student*/

83 FUNCTION return_record_count RETURN NUMBER AS

84 record_count NUMBER(10);

85 BEGIN

86 SELECT COUNT(*) INTO record_count FROM student;

87 RETURN record_count;

88 EXCEPTION

89 WHEN OTHERS THEN

90 dbms_output.put_line('意外查询错误！');

91 END return_record_count;

93 END student_package;

94 /

程序包主体已创建。

SQL> DECLARE

2 new_stu student%ROWTYPE;

3 BEGIN

4 new_stu.id := 17;

5 new_stu.name := 'zhangsan';

6 new_stu.score := 88;

7 new_stu.subid := 2;

8 student_package.insert_student(new_stu);

9 END;

10 /

要插入编号为：12的学生记录已经存在！

PL/SQL 过程已成功完成。

SQL> DECLARE

2 new_stu student%ROWTYPE;

3 BEGIN

4 new_stu.id := 17;

5 new_stu.name := 'zhangsan';

6 new_stu.score := 88;

7 new_stu.subid := 2;

8 student_package.insert_student(new_stu);

9 END;

10 /

PL/SQL 过程已成功完成。

SQL> DECLARE

2 new_stu student%ROWTYPE;

3 BEGIN

4 new_stu.id := 3;

5 new_stu.name := 'wangwu';

6 new_stu.score := 55;

7 new_stu.subid := 1;

8 student_package.update_student(new_stu);

9 EXCEPTION

10 WHEN dup_val_on_index THEN

11 dbms_output.put_line('违反唯一约束！');

12 WHEN OTHERS THEN

13 dbms_output.put_line('更新时意外错误！');

14 END;

15 /

修改学生记录成功！

PL/SQL 过程已成功完成。

SQL> BEGIN

2 student_package.delete_student(9);

3 END;

4 /

删除编号为：9的学生记录成功！

PL/SQL 过程已成功完成。

SQL> BEGIN

2 dbms_output.put_line(student_package.return_record_count);

3 END;

4 /

PL/SQL 过程已成功完成。

SQL> CREATE OR REPLACE PACKAGE restrict_package IS

2 PROCEDURE update_my_dept(d_no NUMBER);

3 PRAGMA RESTRICT_REFERENCES(update_my_dept, WNDS);

4 END;

5 /

程序包已创建。

SQL> CREATE OR REPLACE PACKAGE BODY restrict_package IS

2 PROCEDURE update_my_dept(d_no NUMBER) IS

3 BEGIN

4 UPDATE my_dept SET loc = 'zhongguo' WHERE deptno = d_no;

5 END;

6 END;

7 /

警告: 创建的包体带有编译错误。

SQL> show errors;

PACKAGE BODY RESTRICT_PACKAGE 出现错误:

LINE/COL ERROR

-------- -----------------------------------------------------------------

2/2 PLS-00452: 子程序 'UPDATE_MY_DEPT' 违反了它的相关编译指示

SQL> DROP PACKAGE restrict_package;

程序包已丢弃。

SQL> DROP PACKAGE BODY restrict_package;

SQL> set autoprint on ;

SQL> variable temp_cursor refcursor;

SQL> execute student_package.return_student(:temp_cursor);

PL/SQL 过程已成功完成。

ID NAME SCORE SUBID

---------- -------------------- ---------- ----------

1 student_1 11

2 student_2 12

3 wangwu 13

4 student_4 14

5 student_5 15

6 student_6 16

7 student_7 17

8 student_8 18

10 student_10 20

11 student_11 21

12 student_12 22

ID NAME SCORE SUBID

---------- -------------------- ---------- ----------

13 student_13 23

17 zhangsan 88 2

已选择13行。

【触发器】

SQL> CREATE OR REPLACE TRIGGER insert_student_trigger

2 BEFORE INSERT ON student

3 BEGIN

4 dbms_output.put_line('触发器被触发...');

5 END;

6 /

触发器已创建

SQL> drop trigger insert_student_trigger;

触发器已丢弃

SQL> CREATE OR REPLACE TRIGGER update_student_trigger

2 AFTER UPDATE ON student

3 BEGIN

4 dbms_output.put_line('触发器被触发...');

5 END;

6 /

触发器已创建

SQL> CREATE OR REPLACE TRIGGER update_student_trigger

2 AFTER UPDATE ON student

3 FOR EACH ROW

4 BEGIN

5 dbms_output.put_line('触发器被触发...');

6 END;

7 /

触发器已创建

SQL> update student set subid = 1;

触发器被触发...

SQL> CREATE OR REPLACE TRIGGER insert_my_dept_trigger

2 AFTER INSERT ON my_dept

3 FOR EACH ROW

4 BEGIN

5 dbms_output.put_line('触发器被触发...');

6 END;

7 /

触发器已创建

SQL> insert into my_dept values(60,'job','zhejiang');

触发器被触发...

SQL> CREATE OR REPLACE TRIGGER insert_student_trigger

2 BEFORE INSERT ON student

3 FOR EACH ROW

4 BEGIN

5 dbms_output.put_line('触发器被触发...');

6 dbms_output.put_line('new ==> stuid =' || :new.stuid || ' name = ' || :new.name || ' score = ' || :new.score || ' subid = ' || :new.subid);

7 END;

8 /

触发器已创建

SQL> INSERT INTO student VALUES(1,'zhangsan',88.8,2);

触发器被触发...

new ==> stuid =1 name = zhangsan score = 88.8 subid = 2

已创建 1 行。

SQL> INSERT INTO student VALUES(2,'lisi',79,1);

触发器被触发...

new ==> stuid =2 name = lisi score = 79 subid = 1

已创建 1 行。

SQL> CREATE OR REPLACE TRIGGER delete_student_trigger

2 BEFORE DELETE ON student

3 FOR EACH ROW

4 BEGIN

5 dbms_output.put_line('触发器被触发...');

6 dbms_output.put_line('old: stuid = ' || :old.stuid || ' name = ' || :old.name || ' score = ' || :old.score || ' subid = ' || :old.subid);

7 END;

8 /

触发器已创建

SQL> DELETE FROM student ;

触发器被触发...

old: stuid = 1 name = zhangsan score = 88.8 subid = 2

触发器被触发...

old: stuid = 2 name = lisi score = 79 subid = 1

已删除2行。

SQL> CREATE OR REPLACE TRIGGER update_student_trigger

2 BEFORE UPDATE ON student

3 FOR EACH ROW

4 BEGIN

5 dbms_output.put_line('触发器被触发...');

6 dbms_output.put_line('old: stuid = ' || :old.stuid || ' name = ' || :old.name || ' score = ' || :old.score || ' subid = ' || :old.subid);

7 dbms_output.put_line('new: stuid = ' || :new.stuid || ' name = ' || :new.name || ' score = ' || :new.score || ' subid = ' || :new.subid);

8 END;

9 /

触发器已创建

SQL> UPDATE student SET name = 'zhangsan', score = 76, subid = 1;

触发器被触发...

old: stuid = 1 name = AA score = 88 subid = 2

new: stuid = 1 name = zhangsan score = 76 subid = 1

已更新 1 行。

SQL> CREATE OR REPLACE TRIGGER delete_student_trigger

2 AFTER DELETE ON student

3 FOR EACH ROW

4 WHEN (old.subid = 1)

5 BEGIN

6 dbms_output.put_line('触发器被触发...');

7 dbms_output.put_line('old: stuid = ' || :old.stuid || ' name = ' || :old.name || ' score = ' || :old.score || ' subid = ' || :old.subid);

8 END;

9 /

触发器已创建

SQL> DELETE FROM student;

触发器被触发...

old: stuid = 1 name = zhangsan score = 76 subid = 1

已删除 1 行。

SQL> CREATE TABLE student_log AS SELECT * FROM student;

表已创建。

SQL> CREATE OR REPLACE TRIGGER student_trigger

2 BEFORE INSERT OR UPDATE OR DELETE ON student

3 FOR EACH ROW

4 BEGIN

5 IF inserting

6 THEN

7 INSERT INTO student_log VALUES (:new.stuid, :new.name, :new.score, :new.subid);

8 END IF;

9 IF deleting

10 THEN

11 INSERT INTO student_log VALUES (:old.stuid, :old.name, :old.score, :old.subid);

12 END IF;

13 EXCEPTION

14 WHEN OTHERS THEN

15 dbms_output.put_line('触发器意外错误！' || SQLCODE || ' ==> ' || SQLERRM);

16 END;

17 /

触发器已创建

SQL> insert into student values(2,'lisi',77,1);

触发器被触发...

new ==> stuid =2 name = lisi score = 77 subid = 1

已创建 1 行。

SQL> select * from student_log;

STUID NAME SCORE SUBID

---------- -------------------- ---------- ----------

2 lisi 77 1

SQL> CREATE TABLE worker_job (workid number,workname varchar2(20),salary number,job varchar2(20));

表已创建。

SQL> CREATE TABLE goods_store (goods_no int primary key,goods_count number);

表已创建。

SQL> create table goods_store_out(goods_no int,goods_count number,goods_out_date date default sysdate);

表已创建。

SQL> CREATE OR REPLACE TRIGGER goods_out_trigger

2 AFTER INSERT ON goods_store_out

3 FOR EACH ROW

4 BEGIN

5 UPDATE goods_store SET goods_count = goods_count - :new.goods_count WHERE goods_no = :new.goods_no;

6 dbms_output.put_line('出库商品编号为：' || :new.goods_no || '数量为：' || :new.goods_count);

7 END;

8 /

触发器已创建

SQL> insert into goods_store_out values(101,62,sysdate);

出库商品编号为：101数量为：62

SQL> select * from goods_store;

GOODS_NO GOODS_COUNT

---------- -----------

101 38

102 200

103 300

SQL> CREATE OR REPLACE VIEW student_subject_view

2 AS SELECT s.stuid, s.name, s.score, s.subid,b.subname FROM student s, subject b WHERE s.subid = b.subid;

视图已建立。

SQL> CREATE OR REPLACE TRIGGER student_subject_trigger

2 INSTEAD OF INSERT ON student_subject_view

3 REFERENCING NEW n

4 FOR EACH ROW

5 DECLARE

6 icount INT;

7 BEGIN

9 SELECT COUNT(*) INTO icount FROM student WHERE stuid = :n.stuid;

10 IF icount = 0

11 THEN

12 INSERT INTO student (stuid, NAME, score, subid) VALUES (:n.stuid, :n.name, :n.score, :n.subid);

13 ELSE

14 UPDATE student SET NAME = :n.name, score = :n.score, subid = :n.subid WHERE stuid = :n.stuid;

15 END IF;

17 SELECT COUNT(*) INTO icount FROM subject WHERE subid = :n.subid;

18 IF icount = 0

19 THEN

20 INSERT INTO subject VALUES (:n.subid, :n.subname);

21 ELSE

22 UPDATE subject SET subname = :n.subname WHERE subid = :n.subid;

23 END IF;

25 END;

26 /

触发器已创建

SQL> select * from student;

STUID NAME SCORE SUBID

---------- -------------------- ---------- ----------

2 lisi 77 1

1 zhangsan 92 2

3 wangwu 88 3

4 zhaoliu 68 4

SQL> select * from subject;

SUBID SUBNAME

---------- --------------------

1 java

2 c/c++

3 php

4 c#

5 javascript

SQL> select * from student_subject_view;

STUID NAME SCORE SUBID SUBNAME

---------- -------------------- ---------- ---------- --------------------

2 lisi 77 1 java

1 zhangsan 92 2 c/c++

3 wangwu 88 3 php

4 zhaoliu 68 4 c#

SQL> insert into student_subject_view values(5,'sunqi',79,6,'html');

触发器被触发...

new ==> stuid =5 name = sunqi score = 79 subid = 6

已创建 1 行。

SQL> select * from subject;

SUBID SUBNAME

---------- --------------------

1 java

2 c/c++

3 php

4 c#

5 javascript

6 html

已选择6行。

SQL> select * from subject;

SUBID SUBNAME

---------- --------------------

1 java

2 c/c++

3 php

4 c#

5 javascript

6 html

已选择6行。

SQL> select * from student_subject_view;

STUID NAME SCORE SUBID SUBNAME

---------- -------------------- ---------- ---------- --------------------

2 lisi 77 1 java

1 zhangsan 92 2 c/c++

3 wangwu 88 3 php

4 zhaoliu 68 4 c#

5 sunqi 79 6 html

SQL> CREATE TABLE emp_tab AS SELECT * FROM emp;

表已创建。

SQL> CREATE OR REPLACE TRIGGER emp_trigger

2 AFTER DELETE ON emp_tab

3 FOR EACH ROW

4 DECLARE

5 n INTEGER;

6 BEGIN

7 SELECT COUNT(*) INTO n FROM emp_tab;

8 dbms_output.put_line('数量：' || n);

9 END;

10 /

触发器已创建

SQL> delete from emp_tab;

delete from emp_tab

ERROR 位于第 1 行:

ORA-04091: 表 SCOTT.EMP_TAB 发生了变化，触发器/函数不能读

ORA-06512: 在"SCOTT.EMP_TRIGGER", line 4

ORA-04088: 触发器 'SCOTT.EMP_TRIGGER' 执行过程中出错

SQL> ALTER TRIGGER emp_trigger ENABLE;

触发器已更改

SQL> ALTER TRIGGER emp_trigger DISABLE;

触发器已更改

SQL> DESC user_triggers;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

TRIGGER_NAME VARCHAR2(30)

TRIGGER_TYPE VARCHAR2(16)

TRIGGERING_EVENT VARCHAR2(227)

TABLE_OWNER VARCHAR2(30)

BASE_OBJECT_TYPE VARCHAR2(16)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

REFERENCING_NAMES VARCHAR2(128)

WHEN_CLAUSE VARCHAR2(4000)

STATUS VARCHAR2(8)

DESCRIPTION VARCHAR2(4000)

ACTION_TYPE VARCHAR2(11)

TRIGGER_BODY LONG

SQL> desc dba_triggers;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)

TRIGGER_NAME VARCHAR2(30)

TRIGGER_TYPE VARCHAR2(16)

TRIGGERING_EVENT VARCHAR2(227)

TABLE_OWNER VARCHAR2(30)

BASE_OBJECT_TYPE VARCHAR2(16)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

REFERENCING_NAMES VARCHAR2(128)

WHEN_CLAUSE VARCHAR2(4000)

STATUS VARCHAR2(8)

DESCRIPTION VARCHAR2(4000)

ACTION_TYPE VARCHAR2(11)

TRIGGER_BODY LONG

SQL> desc all_triggers;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)

TRIGGER_NAME VARCHAR2(30)

TRIGGER_TYPE VARCHAR2(16)

TRIGGERING_EVENT VARCHAR2(227)

TABLE_OWNER VARCHAR2(30)

BASE_OBJECT_TYPE VARCHAR2(16)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

REFERENCING_NAMES VARCHAR2(128)

WHEN_CLAUSE VARCHAR2(4000)

STATUS VARCHAR2(8)

DESCRIPTION VARCHAR2(4000)

ACTION_TYPE VARCHAR2(11)

TRIGGER_BODY LONG

【内置程序包】

DBMS_STANDARD

DBMS_ALERT

DBMS_APPLICATION_INFO

DBMS_AQ & DBMS_AQADM

DBMS_DEFER

DBMS_DEFER_SYS

DBMS_DEFER_QUERY

DBMS_DDL

CALENDAR

DBMS_DESCIRIBE

DBMS_LOB

DBMS_JOB

DBMS_LOCK

DBMS_OUTPUT

... ...

SQL> set serveroutput on;

SQL> set serveroutput on size 1500;

SP2-0547: size选项1500超出范围（自2000至1000000）

SQL> set serveroutput on size 5000;

SQL> DECLARE

2 str VARCHAR2(20) := 'Hello Oracle';

3 BEGIN

4 dbms_output.put(str);

5 dbms_output.put_line('Oracle内置包DBMS_OUTPUT显示');

6 END;

7 /

Hello OracleOracle内置包DBMS_OUTPUT显示

PL/SQL 过程已成功完成。

SQL> CREATE TABLE down_file_list (id number not null primary key,name varchar2(50) not null, file_location bfile,file_description clob);

表已创建。

SQL> CREATE OR REPLACE DIRECTORY file_dir AS 'D:\DOWN_LOAD_LIST';

目录已创建。

SQL> INSERT INTO down_file_list VALUES(1001,'oracle plsql book',bfilename(upper('file_dir'),'java.zip'),'this is clob description ...');

已创建 1 行。

SQL> INSERT INTO down_file_list VALUES(1002,'java study',bfilename(upper('file_dir'),'java.txt'),'this is clob description about java study...');

已创建 1 行。

SQL> select * from down_file_list;

SP2-0678: 列或属性类型无法通过 SQL*Plus 显示

SQL> select id,name,file_description from down_file_list;

ID NAME

---------- --------------------------------------------------

FILE_DESCRIPTION

---------------------------------------------------------------

1001 oracle plsql book

this is clob description ...

SQL> update down_file_list set file_location = bfilename(upper('file_dir'),'oracle.gif') where id = 1001;

已更新 1 行。

SQL> DECLARE

2 temp_desc CLOB;

3 i_read_count INT;

4 i_start INT;

5 out_put_desc VARCHAR2(100);

6 BEGIN

7 i_read_count := 10;

8 i_start := 1;

9 SELECT file_description INTO temp_desc FROM down_file_list WHERE id = 1002;

10 dbms_lob.read(temp_desc, i_read_count, i_start, out_put_desc);

11 dbms_output.put_line('前10个字符是：' || out_put_desc);

12 END;

13 /

前10个字符是：this is cl

PL/SQL 过程已成功完成。

SQL> DECLARE

2 temp_clob CLOB;

3 i_len INT;

4 BEGIN

5 SELECT file_description INTO temp_clob FROM down_file_list WHERE id = 1002;

6 i_len := dbms_lob.getlength(temp_clob);

7 dbms_output.put_line('长度为：' || i_len);

8 EXCEPTION

9 WHEN OTHERS THEN

10 dbms_output.put_line('其他错误！');

11 END;

12 /

长度为：44

PL/SQL 过程已成功完成。

SQL> DECLARE

2 temp_desc CLOB;

3 i_count INT;

4 i_start INT;

5 new_var VARCHAR2(60);

6 BEGIN

7 i_count := 2;

8 i_start := 1;

9 new_var := ' 这事一本好书，介绍一下。。。';

10 SELECT file_description INTO temp_desc FROM down_file_list WHERE id = 1002 FOR UPDATE;

11 dbms_output.put_line('修改前：' || temp_desc);

12 dbms_output.put_line('从 ' || i_start || '开始的' || i_count || '个字符被改成' || new_var);

13 COMMIT;

14 END;

15 /

修改前：this is clob description about java study...

从 1开始的2个字符被改成这事一本好书，介绍一下。。。

PL/SQL 过程已成功完成。

SQL> DECLARE

2 dest_desc CLOB;

3 src_desc CLOB;

4 BEGIN

5 SELECT file_description INTO dest_desc FROM down_file_list WHERE id = 1002 FOR UPDATE;

6 SELECT file_description INTO src_desc FROM down_file_list WHERE id = 1001;

7 dbms_lob.append(dest_desc, src_desc);

8 COMMIT;

9 END;

10 /

PL/SQL 过程已成功完成。

SQL> DECLARE

2 temp_desc CLOB;

3 i_count INT;

4 i_start INT;

5 BEGIN

6 i_count := 3;

7 i_start := 1;

8 SELECT file_description INTO temp_desc FROM down_file_list WHERE id = 1002 FOR UPDATE;

9 dbms_lob.erase(temp_desc, i_count, i_start);

10 COMMIT;

11 END;

12 /

PL/SQL 过程已成功完成。

SQL> DECLARE

2 dest_desc CLOB;

3 src_desc CLOB;

4 i_count INT;

5 i_dest_start INT;

6 i_src_start INT;

7 BEGIN

8 i_count := 10;

9 i_dest_start := 10;

10 i_src_start := 1;

11 SELECT file_description INTO dest_desc FROM down_file_list WHERE id = 1002;

12 SELECT file_description INTO src_desc FROM down_file_list WHERE id = 1002;

13 dbms_output.put_line('复制前：' || dest_desc || '长度：' || dbms_lob.getlength(dest_desc));

14 dbms_output.put_line('要复制的内容：' || dbms_lob.substr(src_desc, i_count, i_src_start));

15 dbms_lob.copy(dest_desc, src_desc, i_count, i_dest_start, i_src_start);

16 COMMIT;

17 END;

18 /

SQL> set serveroutput on linesize 260;

SQL> create or replace directory filepath as 'D:\WINORACLE\';

目录已创建。

SQL> DESC dba_directories;

名称

------------------------------------------------------------------

OWNER

DIRECTORY_NAME

DIRECTORY_PATH

SQL> select * from dba_directories where directory_name = upper('filepath');

OWNER DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

----------------------------------------------------------------------------------

SYS FILEPATH

D:\WINORACLE\

SQL> INSERT INTO down_file_list VALUES (1003, '安装日志测试...', bfilename('FILEPATH', 'setup_log.txt'), '记录安装操作过程测试...');

已创建 1 行。

SQL> INSERT INTO down_file_list VALUES (1004, '发生的皇室典范', bfilename('FILEPATH', 'log.tt'), '和法国队...');

已创建 1 行。

SQL> SELECT id, NAME, file_description, dbms_lob.fileexists(file_location) FROM down_file_list;

ID NAME FILE_DESCRIPTION DBMS_LOB.FILEEXISTS(FILE_LOCATION)

---------- -------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------

1001 oracle plsql book this is clob description ... 1

1002 java study s is clob description about java study...this is clob description ... 1

1003 安装日志测试... 记录安装操作过程测试... 0

1004 发生的皇室典范和法国队... 0

SQL> DECLARE

2 temp_file BFILE;

3 i_file_exists_flag INT;

4 i_file_open_flag INT;

5 i_out_put_count NUMBER;

6 i_start INT;

7 i_dir_alias VARCHAR(20);

8 file_name VARCHAR(20);

9 out_put_var LONG;

10 BEGIN

11 SELECT file_location INTO temp_file FROM down_file_list WHERE id = 1004;

12 dbms_lob.filegetname(file_loc => temp_file, dir_alias => i_dir_alias, filename => file_name);

13 i_file_exists_flag := dbms_lob.fileexists(temp_file);

14 IF i_file_exists_flag = 1

15 THEN

16 dbms_output.put_line(i_dir_alias || '\' || file_name || '存在！');

17 ELSE

18 dbms_output.put_line('文件不存在！');

19 GOTO endblock;

20 END IF;

22 i_file_open_flag := dbms_lob.fileisopen(temp_file);

23 IF i_file_open_flag = 1

24 THEN

25 dbms_output.put_line('文件已经打开！');

26 GOTO endblock;

27 ELSE

28 dbms_output.put_line('正在打开文件...');

29 dbms_lob.fileopen(temp_file);

30 END IF;

31 i_out_put_count := 100;

32 i_start := 1;

33 dbms_lob.read(temp_file, i_out_put_count, i_start, out_put_var);

34 dbms_output.put_line('文件内容的从' || i_start || '开始的' || i_out_put_count || '内容是：');

35 dbms_output.put_line(out_put_var);

36 dbms_output.put_line('正在关闭文件...' || file_name);

37 dbms_lob.fileclose(temp_file);

38 dbms_output.put_line('完成输出操作！');

39 <<endblock>>

40 NULL;

41 EXCEPTION

42 WHEN no_data_found THEN

43 dbms_output.put_line('没有找到相应的记录！');

44 WHEN OTHERS THEN

45 dbms_output.put_line('其他错误！');

46 END;

47 /

文件不存在！

PL/SQL 过程已成功完成。

SQL> DECLARE

2 temp_file BFILE;

3 i_file_exists_flag INT;

4 i_file_open_flag INT;

5 i_out_put_count NUMBER;

6 i_start INT;

7 i_dir_alias VARCHAR(20);

8 file_name VARCHAR(20);

9 out_put_var LONG;

10 BEGIN

11 SELECT file_location INTO temp_file FROM down_file_list WHERE id = 1004;

12 dbms_lob.filegetname(file_loc => temp_file, dir_alias => i_dir_alias, filename => file_name);

13 i_file_exists_flag := dbms_lob.fileexists(temp_file);

14 IF i_file_exists_flag = 1

15 THEN

16 dbms_output.put_line(i_dir_alias || '\' || file_name || '存在！');

17 ELSE

18 dbms_output.put_line('文件不存在！');

19 GOTO endblock;

20 END IF;

22 i_file_open_flag := dbms_lob.fileisopen(temp_file);

23 IF i_file_open_flag = 1

24 THEN

25 dbms_output.put_line('文件已经打开！');

26 GOTO endblock;

27 ELSE

28 dbms_output.put_line('正在打开文件...');

29 dbms_lob.fileopen(temp_file);

30 END IF;

31 i_out_put_count := 100;

32 i_start := 1;

33 dbms_lob.read(temp_file, i_out_put_count, i_start, out_put_var);

34 dbms_output.put_line('文件内容的从' || i_start || '开始的' || i_out_put_count || '内容是：');

35 dbms_output.put_line(out_put_var);

36 dbms_output.put_line('正在关闭文件...' || file_name);

37 dbms_lob.fileclose(temp_file);

38 dbms_output.put_line('完成输出操作！');

39 <<endblock>>

40 NULL;

41 EXCEPTION

42 WHEN no_data_found THEN

43 dbms_output.put_line('没有找到相应的记录！');

44 WHEN OTHERS THEN

45 dbms_output.put_line('其他错误！');

46 END;

47 /

FILEPATH\log.tt存在！

正在打开文件...

文件内容的从1开始的100内容是：

C8F6B4F2B7A2CABFB4F3B7F2C8F6B5A9B7A8B6BCCAC7B7A2B7C5CAB1B4FAB4F3CFC3B7B4B6D4C8F6B7B6B5C2C8F8B7A2B5DAC8FDB7BDB5C4C8F8B7D2B4F2CBE3766473667662647366687364666720C9CFB5C4B7E7B8F1B6BCCAC7B7A8B9FAB7A2B4EFB9

正在关闭文件...log.tt

完成输出操作！

PL/SQL 过程已成功完成。

【PLSQL表】

SQL> DECLARE

2 TYPE table_type IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;

3 tp table_type;

4 BEGIN

5 tp(1) := 'tomcat';

6 tp(2) := 'jack';

7 dbms_output.put_line('第一个元素是：' || tp(1));

8 dbms_output.put_line('第二个元素是：' || tp(2));

9 END;

10 /

第一个元素是：tomcat

第二个元素是：jack

PL/SQL 过程已成功完成。

SQL> DECLARE

2 TYPE table_type IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;

3 tp table_type;

4 BEGIN

5 FOR i IN 1 .. 10

6 LOOP

7 tp(i) := 'table_type_test_' || i;

8 END LOOP;

9 FOR i IN 1 .. 10

10 LOOP

11 dbms_output.put_line('第' || i || '个元素是：' || tp(i));

12 END LOOP;

13 END;

14 /

第1个元素是：table_type_test_1

第2个元素是：table_type_test_2

第3个元素是：table_type_test_3

第4个元素是：table_type_test_4

第5个元素是：table_type_test_5

第6个元素是：table_type_test_6

第7个元素是：table_type_test_7

第8个元素是：table_type_test_8

第9个元素是：table_type_test_9

第10个元素是：table_type_test_10

PL/SQL 过程已成功完成。

SQL> DECLARE

2 TYPE table_type IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;

3 tp table_type;

4 BEGIN

5 FOR i IN 1 .. 10

6 LOOP

7 tp(i) := 'table_type_test_' || i;

8 END LOOP;

9 tp.delete(7);

10 FOR i IN 1 .. 10

11 LOOP

12 dbms_output.put_line('第' || i || '个元素是：' || tp(i));

13 END LOOP;

14 END;

15 /

第1个元素是：table_type_test_1

第2个元素是：table_type_test_2

第3个元素是：table_type_test_3

第4个元素是：table_type_test_4

第5个元素是：table_type_test_5

第6个元素是：table_type_test_6

DECLARE

ERROR 位于第 1 行:

ORA-01403: 未找到数据

ORA-06512: 在line 12

SQL> DECLARE

2 TYPE table_type IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;

3 tp table_type;

4 BEGIN

5 FOR i IN 2 .. 10

6 LOOP

7 tp(i) := 'table_type_test_' || i;

8 END LOOP;

10 FOR i IN 1 .. 10

11 LOOP

12 dbms_output.put_line('第' || i || '个元素是：' || tp(i));

13 END LOOP;

14 END;

15 /

DECLARE

ERROR 位于第 1 行:

ORA-01403: 未找到数据

ORA-06512: 在line 12

SQL> DECLARE

2 TYPE stu_record_type IS RECORD(

3 t_id student.stuid%TYPE,

4 t_name student.name%TYPE,

5 t_score student.score%TYPE,

6 t_subid student.subid%TYPE);

7 stu_rec stu_record_type;

8 BEGIN

9 SELECT s.stuid, s.name, s.score, s.subid INTO stu_rec FROM student s WHERE s.stuid = 2;

10 dbms_output.put_line(stu_rec.t_id || ' - ' || stu_rec.t_name || ' - ' || stu_rec.t_score || ' - ' || stu_rec.t_subid);

11 EXCEPTION

12 WHEN no_data_found THEN

13 dbms_output.put_line('没有数据！');

14 END;

15 /

2 - lisi - 77 - 1

PL/SQL 过程已成功完成。

SQL> DECLARE

2 TYPE stu_record_type IS RECORD(

3 t_id student.stuid%TYPE,

4 t_name student.name%TYPE,

5 t_dept my_dept%ROWTYPE);

6 stu_rec stu_record_type;

7 BEGIN

8 stu_rec.t_id := 111;

9 stu_rec.t_name := 'yangguo';

10 stu_rec.t_dept.deptno := 10;

11 stu_rec.t_dept.dname := 'sales';

12 stu_rec.t_dept.loc := 'hangzhou';

13 dbms_output.put_line(stu_rec.t_id || ' - ' || stu_rec.t_name || ' - ' || stu_rec.t_dept.deptno || ' - ' || stu_rec.t_dept.dname || ' - ' ||

14 stu_rec.t_dept.loc);

15 EXCEPTION

16 WHEN no_data_found THEN

17 dbms_output.put_line('没有数据！');

18 END;

19 /

111 - yangguo - 10 - sales - hangzhou

PL/SQL 过程已成功完成。

SQL> DECLARE

2 TYPE student_tab_type IS TABLE OF student%ROWTYPE INDEX BY BINARY_INTEGER;

3 stu_tp student_tab_type;

4 BEGIN

5 SELECT s.stuid, s.name, s.score, s.subid BULK COLLECT INTO stu_tp FROM student s;

6 dbms_output.put_line('记录为：' || stu_tp.count);

7 dbms_output.put_line(' student :' || stu_tp(1).stuid || ' ' || stu_tp(1).name || ' - ' || stu_tp(1).score || ' - ' || stu_tp(1).subid);

8 END;

9 /

记录为：5

student :2 lisi - 77 - 1

PL/SQL 过程已成功完成。

【成员函数和过程】

SQL> CREATE OR REPLACE TYPE per_type AS OBJECT

2 (

3 pid NUMBER,

4 pname VARCHAR2(20),

5 MEMBER FUNCTION getid RETURN INT,

6 MEMBER PROCEDURE setid(p_id INT),

7 MEMBER FUNCTION getname RETURN VARCHAR,

8 MEMBER PROCEDURE setname(p_name VARCHAR)

9 )

10 NOT FINAL;

11 /

类型已创建。

SQL> CREATE OR REPLACE TYPE BODY per_type AS

2 MEMBER FUNCTION getid RETURN INT IS

3 BEGIN

4 RETURN pid;

5 END getid;

6 MEMBER PROCEDURE setid(p_id INT) AS

7 BEGIN

8 pid := p_id;

9 END setid;

10 MEMBER FUNCTION getname RETURN VARCHAR IS

11 BEGIN

12 RETURN pname;

13 END getname;

14 MEMBER PROCEDURE setname(p_name VARCHAR) IS

15 BEGIN

16 pname := p_name;

17 END setname;

18 END;

19 /

类型主体已创建。

SQL> DECLARE

2 p per_type;

3 BEGIN

4 p := per_type(1001, 'tom');

5 dbms_output.put_line(p.pid || ' ' || p.pname);

6 dbms_output.put_line(p.getid || ' ' || p.getname);

7 END;

8 /

1001 tom

PL/SQL 过程已成功完成。

SQL> DECLARE

2 p per_type;

3 BEGIN

4 p := per_type(1001, 'tom');

5 dbms_output.put_line(p.pid || ' ' || p.pname);

6 dbms_output.put_line(p.getid || ' ' || p.getname);

7 p.setid(2001);

8 p.setname('jack');

9 dbms_output.put_line(p.pid || ' ' || p.pname);

10 dbms_output.put_line(p.getid || ' ' || p.getname);

11 END;

12 /

1001 tom

2001 jack

PL/SQL 过程已成功完成。

SQL> CREATE TABLE person_table OF per_type;

表已创建。

SQL> INSERT INTO person_table VALUES(1101,'jettry');

已创建 1 行。

SQL> INSERT INTO person_table VALUES(per_type(1102,'edsion'));

已创建 1 行。

SQL> select ref(o) oid from person_table o;

OID

--------------------------------------------------------------------------------

0000280209379FE0E0DA3D48AA9A0D2DF5655A15E223B52098BADC4E62BAAE04E32A10BAD20040D6

520000

0000280209DFE0E67D6E8A4C5686C5CF58DE4915D723B52098BADC4E62BAAE04E32A10BAD20040D6

520001

SQL> select value(o) obj from person_table o;

OBJ(PID, PNAME)

--------------------------------------------------------------------------------

PER_TYPE(1101, 'jettry')

PER_TYPE(1102, 'edsion')

SQL> select p.getid() id, p.getname() name from person_table p where p.pid = 1102;

----------

NAME

--------------------------------------------------------------------------------

1102

edsion

分享到：

Oracle 知识总结 | Oracle 语句大全

2012-06-02 13:56
浏览 1502
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle 知识

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle 知识

评论

发表评论

相关推荐

SQL语句查询优化

DB2 SQLCODE 大全

\(^_^)/ 数据库索引

SQLite 语法

Oracle 查询效率

Oracle 命名

\(^_^)/ MySQL语句

常用数据库 JDBC URL 格式

java Derby 介绍

Oracle处理重复数据

数据库取前几条记录的语句

Oracle查看存储过程内容

JDBC知识点

ORACLE中ESCAPE关键字用法

Oracle中merge用法

Oracle 知识总结

Oracle 语句大全

Oracle 分组统计

Oracle 索引

Oracle Hint

最近访客更多访客>>