Oracle学习笔记4

1．表：

select * from cat; //显示所有用户对象。
select * from tab; //显示所有用户表。
select table_name from user_tables; //显示所有用户表

2．视图：

select text from user_views where view_name=upper('&view_name');

//显示某个视图
3．索引：

select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;

//显示用户索引
4．触发器：

select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;

//显示用户触发器
5．快照：

select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;

6．同义词：

select * from syn;

7．序列：

select * from seq;

8．数据库链路：

select * from user_db_links;

9．约束限制：

select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS
from user_constraints WHERE TABLE_name=upper('&TABLE_Name');

10．本用户读取其他用户对象的权限：

select * from user_tab_privs;

11．本用户所拥有的系统权限：

select * from user_sys_privs;

12．用户：

select * from all_users order by user_id;

13．表空间剩余自由空间情况：

select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name；

14．数据字典：

select table_name from dict order by table_name;

可以通过数据字典表查询其他系统表

select table_name from dict where table_name = ‘ ***%’order by table_name;

15．锁及资源信息：
select * from v$lock;不包括DDL锁
16．数据库字符集：

select name,value$ from props$ where name='NLS_CHARACTERSET';

simplified chiniese_china.zhs16GBK 简体中午编码
17．inin.ora参数：

select name,value from v$parameter order by name;

18．SQL共享池：

select sql_text from v$sqlarea;

19．数据库：

select * from v$database;

20．控制文件：

select * from V$controlfile;

21．重做日志文件信息：

select * from V$logfile;

22．来自控制文件中的日志文件信息：
select * from V$log;
23．来自控制文件中的数据文件信息：

select * from V$datafile;

24．NLS参数当前值：

select * from V$nls_parameters;

25．ORACLE版本信息：

select * from v$version;

26．描述后台进程：

select * from v$bgprocess;

27．查看版本信息：

select * from product_component_version;

28．查询oracle中所有用户信息

select * from dba_users;

29．只查询用户和密码

select username,password from dba_users;

30．查询当前用户信息
select * from dba_ustats;
3. PL/SQL语言
Procedure Language Structure Qurrey Language――PL/SQL

3.1. Oracle的变量类型
数据类型
数字，字符，布尔，日期
组合类型
Record(不同数据集合),table（一张表）,varray（数组）
参考型
Ref cursor, ref object_type
数字类型
Binary_integer,Dec,Float
Number
Number(3)
Number(4,3) 前面的总长，后面是小数的长度
字符类型
Char(不可变长), varchar（过时） , varchar2（现在使用） , string
Boolean，date
Declare
V_firstName varchar(20);
Declare
V_firstName students.first_name%type; ――和students.first_name的大小一致
*显示输出―――set serveroutput on
3.2. 代码注释
多行 /* */
单行 --
3.3. Type关键字：
Example 1：

Declare
V_id students.id%type:=1;
V_name students.name%type:=’mudi’;
V_age students.age%type:=24;
Begin
Dbms_ouput.put_line(v_id);
Dbms_output.put_line(v_name);
Dbms_output.put_line(v_age);
End;

DECLARE
v_LastName S_EMP.LAST_NAME%TYPE;
v_FirstName S_EMP.FIRST_NAME%TYPE;
v_ID S_EMP.ID%TYPE;

Example 2：

BEGIN
select ID,LAST_NAME,FIRST_NAME INTO v_ID,v_LastName,v_FirstName FROM S_EMP WHERE rownum = 1;
DBMS_OUTPUT.PUT_LINE(v_ID);
DBMS_OUTPUT.PUT_LINE(v_LastName);
DBMS_OUTPUT.PUT_LINE(v_FirstName);
END;
多个TYPE类型组合使用
DECLARE
v_LastName S_EMP.LAST_NAME%TYPE;
v_FirstName S_EMP.FIRST_NAME%TYPE;
v_ID S_EMP.ID%TYPE;
BEGIN
select ID,LAST_NAME,FIRST_NAME INTO v_ID,v_LastName,v_FirstName FROM S_EMP WHERE rownum = 1;
DBMS_OUTPUT.PUT_LINE(v_ID);
DBMS_OUTPUT.PUT_LINE(v_LastName);
DBMS_OUTPUT.PUT_LINE(v_FirstName);
END;

3.4. record组合类型（相当于C中的结构体）
Example 1：

Type class is record(
Id students.id%type;
Name students.name%type;
Age studnets.age%type;
);
Sd0510 class;
Begin
Select * into sd0510 from classes where class_name=’sd0510’;
Dbms_output.put_line(sd0510.id);
End;

Example 2:

Declare
Type emp_type is record(
Emp_id s_emp.id%type,
Emp_first_name s_emp.first_name%type,
Emp_last_name s_emp.last_name%type
)
Emp emp_type;
Begin
Select * into emp from s_emp where id=1;
Dbms_output.put_line(emp.emp_id);
End;

3.5. Rowtype类型
Example 1:

declare
Emp s_emp%rowtype;
Begin
Select * into emp from s_emp where id=1;
End;

Table类型
Example 1:

Declare
Type t_emp is table of s_emp%rowtype index by binary_integer;
Emp t_emp;
Begin
Select * into emp(100) from s_emp where id = 100;
dbms_output.put_line('id===>'||emp(100).id);
dbms_output.put_line('first_name===>'||emp(100).first_name);
dbms_output.put_line('last_name===>'||emp(100).last_name);
end;

3.6. TABLE类型
TYPE Info IS TABLE OF S_EMP%ROWTYPE INDEX BY BINARY_INTEGER;
v_info Info;
3.7. 变量的作用域与可见性
如同JAVA

3.8. 控制语句
IF 判断语句
JAVA
If(logic expression){}
Else if(logic expression){}
Else{}
PL/SQL
If logic expression then ….
Elsif logic expression then
Endif
Example 1:
declare
i number;
begin
select count(*) into i from s_emp;
if i=0 then
dbms_output.put_line('no elements in s_emp');
elsif i<10 then
dbms_output.put_line('number < 10');
else
dbms_output.put_line(i);
end if;
end;
Loop 表达式
Loop
If logic_expression then exit;
End loop;
While表达式
While logic
…
End loop;
For 表达式(与JAVA的特点是)
For j in low..high loop
….
End loop;
GOTO语句
特点：只能从内部跳到外部
NULL语句
解决一些空语句的问题
Example 1:
If … then
…

Else
Null;
End if;
Example 2:
If … then
…
Goto goto_end;
End;
<<goto_end>>
End;
3.9. SQL in PL/SQL
Data Manipulation language
Data Definition language
Transation Control
System comment
ESQL
在PL/SQL只能使用DML，Transation Control
3.10. Cursor 的使用
声明 Cursor s is select * from s_emp;
打开 Open s;
提取数据 FETCH S INTO emp;
EXIT WHEN S%NOTFOUND;
Cursor的关键字
Found
Notfound
Isopen
Rowcount
在loop中使用cursor

declare
cursor emp_cur is select * from s_emp;
emp s_emp%rowtype;
begin
loop
fetch emp_cur into emp;
if emp_cur%notfound then
exit;
end if;
end loop;
close emp_cur;
end;

/
在while中使用cursor

Open emp_cur;
While emp_cur%found loop
Fetch emp_cur into emp;
Dbms_output.put_line(emp.id);
End loop;
Close emp_cur;

在for中使用cursor

open emp_cur;
for emp in emp_cur loop
dbms_output.put_line(emp.id);
end loop;
close emp_cur;

3.11. 异常的使用
异常分为：
系统异常
Dup_val_on_index
Timeout_on_resource
……
自定义异常

Example 1:
DECLARE
myException EXCEPTION;
F_NAME S_EMP.FIRST_NAME%TYPE;
F1_NAME S_EMP.FIRST_NAME%TYPE;
CURSOR S IS SELECT FIRST_NAME FROM S_EMP;
BEGIN
OPEN S;
LOOP
FETCH S INTO F_NAME;
EXIT WHEN S%NOTFOUND;
IF F_NAME IS NULL THEN
RAISE myException;
ELSE
DBMS_OUTPUT.PUT_LINE(' F_Name : ' || F_NAME);
END IF;

END LOOP;
CLOSE S;
select first_name into f1_name from s_emp;
DBMS_OUTPUT.PUT_LINE(' F1_Name : ' || F1_NAME);
EXCEPTION
WHEN myException THEN
DBMS_OUTPUT.PUT_LINE(' Captured !');
CLOSE S;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(' Captured NO DATA FOUND!');
END;

注意：for emp in emp_cur loop …..end loop;自动关闭cursor
3.12. 使用PROCEDURE
行参模式分为 in , out , int out 默认为 in

CREATE OR REPLACE PROCEDURE ModeTest(
p_In IN NUMBER,
p_Out OUT NUMBER,
p_inout IN OUT NUMBER)
IS
v_Local Number;
BEGIN
v_Local := p_In;
p_Out := 7;
p_InOut := 7;
END ModeTest;

/
注意： 如果在SQLPLUS里看详细的信息，show errors
In模式形参可以有默认值 DEFAULT 或者 :=
两种命名方法
名字标识法：

Test_procedure(outPara=>a,intPara=>b,inoutPara=>c);

outPara , intPara , inoutPara 为行参名字
位置标识法：
Test_procedure(a,b,c);
注意：要想直接在sqlplus运行procedure

Variable a number;
Variable b number;
Exec test_procedure(inpara=>100,outpara=>2);
输出结果
Print

以上命令只能用在SQLPLUS的CONSOLE
匿名块每次都编译，有名块第一次编译，以后不用。
删除PROCEDURE : drop procedure_name
3.13. 使用FUNCTION
3.13.1. 程序分两种：
1．通过create or replace 编译的存在数据库里
2．没有 create or replace 不存在数据库里
且只能被调用的父过程调用。

CREATE OR REPLACE FUNCTION lessthen_test(
src IN NUMBER,
ref IN NUMBER := 400)
RETURN BOOLEAN AS
BEGIN
IF src > ref THEN RETURN FALSE;
ELSE RETURN TRUE;
END IF;
END;

3.14. PACKAGE
3.14.1. 包的声明

CREATE OR REPLACE PACKAGE MyPackages IS
PROCEDURE AddStudent(
p_ID IN S_EMP.ID%TYPE,
p_FNAME IN S_EMP.FIRST_NAME%TYPE := '',
p_LNAME IN S_EMP.LAST_NAME%TYPE DEFAULT ''
);
FUNCTION DeleteStudent(
p_ID IN S_EMP.ID%TYPE
) RETURN BOOLEAN;

END MyPackages;
3.14.2. 包的定义

CREATE OR REPLACE PACKAGE BODY MyPackages AS
PROCEDURE AddStudent(
p_ID IN S_EMP.ID%TYPE,
p_FNAME IN S_EMP.FIRST_NAME%TYPE := '',
p_LNAME IN S_EMP.LAST_NAME%TYPE DEFAULT ''
) IS
Duplicate EXCEPTION;
i NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO i FROM S_EMP WHERE ID = p_ID AND FIRST_NAME = p_FNAME AND LAST_NAME = p_LNAME;
IF i = 0 THEN
INSERT INTO S_EMP (ID,FIRST_NAME,LAST_NAME) VALUES(p_ID,p_FNAME,p_LNAME);
COMMIT;
ELSE
RAISE Duplicate;
END IF;
EXCEPTION
WHEN Duplicate THEN
DBMS_OUTPUT.PUT_LINE('Duplicate Record');
END AddStudent;
FUNCTION DeleteStudent(
p_ID IN S_EMP.ID%TYPE
) RETURN BOOLEAN IS
i NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO i FROM S_EMP WHERE ID = p_ID;
IF i > 0 THEN
DELETE FROM S_EMP WHERE ID = p_ID;
COMMIT;
ELSE
NULL;
END IF;
RETURN TRUE;
END DeleteStudent;
END;

3.14.3. 包的初始化代码
3.15. 触发器

CREATE OR REPLACE TRIGGER qiuTrigger after DELETE OR INSERT OR UPDATE ON S_EMP
FOR EACH ROW(行触发器，不写为语句触发器)
DECLARE
i NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO i FROM S_EMP
DBMS_OUTPUT.PUT_LINE(' Only ' || i || ' Student(s) left.');
END;

三种触发条件
两种触发时间
两种触发规则 （每行或每语句）
不能有事务语句
不能在正在修改的表
不可以修改任何变化表（定义触发器的表）；
3.16. Pl/sql中执行动态SQL DDL语句

Execute immediate test_sql ;