登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

PL/SQL循序渐进全面学习教程(3)_Net things Ruyan  

2009-05-11 12:08:43|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
PL/SQL循序渐进全面学习教程(3)
2009-03-27 10:54

ORACLE对象之INDEX

一、INDEX概述:

ORACLE的一种数据对象,用POINTER来加速查询行。通过快速路径存取方法定位数据并减少I/O。 INDEX独立于表。INDEX由ORACLE SERVER来使用和保持。

二、索引如何建立?

1、自动:通过PRIMARY KEY和UNIQUE KEY约束来建立。

2、用户手工建立非唯一性索引。

三、创建方法:

语法:

CREATE INDEX index

ON table (column[, column]...);

何时建立INDEX:

此列经常被放到WHERE字段或JOIN来作条件查询。

此列含有大量的数据。

此列含有大量的空值。

两个或几个列经常同时放到WHERE字段进行组合查询

表很大而且只有少于2-4% 的ROW可能被查询的时候。

以下情况不要建立索引:

表很小;

表被更新频繁。

四、查看已经存在的索引:

1、USER_INDEXES可以查询索引名和类型。

2、USER_IND_COLUMNS包含索引名、表名、列名。

实例:

SQL> SELECT ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness FROM user_indexes ix, user_ind_columns ic WHERE ic.index_name = ix.index_name AND ic.table_name = ’S_EMP’;

五、删除索引:

DROP INDEX index;

SYNONYMS 同义词

语法:

CREATE [PUBLIC] SYNONYM synonym for object;

注意:此对象不能包含在一个包里;

一个私有的同义词不能与同一USER的其他对象重名。

DROP SYNONYM D_SUM;

课程八 用户访问控制

本课重点:

1、创建用户

2、创建角色来进行安全设置

3、使用GRANT或REVOKE 来控制权限

注意:以下实例中标点均为英文半角

一、概述:

ORACLE通过用户名和密码进行权限控制。

数据库安全:系统安全和数据安全

系统权限:使用户可以访问数据库

对象权限:操纵数据库中的对象

SCHEMA:各种对象的集合

二、系统权限:

1、超过80个权限可用。

2、DBA有最高的系统权限:

CREATE NEW USER

REMOVE USERS

REMOVE ANY TABLE

BACKUP ANY TABLE

三、创建用户

1、CREATE USER user IDENTIFIED BY password;

2、系统权限:

CREATE SESSION Connect to the database.

CREATE TABLE Create tables in the user’s schema.

CREATE SEQUENCE Create a sequence in the user’s schema.

CREATE VIEW Create a view in the user’s schema.

CREATE PROCEDURE Create a stored procedure, function, or package in the user’s schema.

3、授权用户系统权限:

GRANT privilege [, privilege...] TO user [, user...];

GRANT CREATE TABLE TO SCOTT;

四、角色的使用

1、概念:角色是一组权限的命名,可以授予给用户。这样就如同给了某个用户一个权限包。

2、创建、授予给角色:

CREATE ROLE MANAGER;

GRANT CREATE TABLE,CREATE VIEW TO MANAGER;

GRANT MANAGER TO CLARK

五、修改密码:

ALTER USER user IDENTIFIED BY password;

六、对象权限:

1、语句:

GRANT {object_priv(, object_priv...)|ALL}[(columns)] ON object TO {user[, user...]|role|PUBLIC} [WITH GRANT OPTION];

2、实例:

最简单:

SQL> GRANT select ON s_emp TO sue, rich;

稍复杂:

SQL> GRANT update (name, region_id) ON s_dept TO scott, manager; SQL> GRANT select, insert ON s_dept TO scott WITH GRANT OPTION;

课程九 声明变量

本课重点:

1、了解基本的PLSQL块和区域

2、描述变量在PLSQL中的重要性

3、区别PLSQL与非PLSQL变量

4、声明变量

5、执行PLSQL块

注意:以下实例中标点均为英文半角

一、概述:

1、PLSQL 块结构:

DECLARE——可选

变量声明定义

BEGIN——必选

SQL和PLSQL语句

EXCEPTION——可选

错误处理

END——必选

二、实例:

declare vjob varchar(9); v_count number:=0; vtotal date:=sysdate +7; c_tax constant number(3,2):=8.25; v_valid boolean not null:=true; begin select sysdate into vtotal from dual; end; /

上例中,如果没有这个SELECT语句,会如何?

出错,说明必须有STATEMENTS

如果: select sysdate from dual into vtotal;

同样,也不行。而且变量与赋值的类型要匹配。

三、%TYPE的属性

声明一个变量使之与数据库某个列的定义相同或与另一个已经定义过的变量相同

所以%TYPE要作为列名的后缀:如:

v_last_name s_emp.last_name%TYPE;

v_first_name s_emp.first_name%TYPE;

——这样做的好处是我们不必去知晓此列的类型与定义

或:

v_balance NUMBER(7,2);

v_minimum_balance v_balance%TYPE := 10;

四、声明一个布尔类型的变量

1、只有TRUE、FALSE、NULL可以赋值给BOOLEAN变量

2、此变量可以接逻辑运算符NOT、AND、OR。

3、变量只能产生TRUE、FALSE、NULL。

实例:

VSAL1:=50000;

VSQL2:=60000;

VCOMMSAL BOOLEAN:=(VSAL1——其实是把TRUE赋值给此变量。

五、LOB类型的变量

共有CLOB、BLOB、BFILE、NCLOB几种,这里不做为重点。

六、使用HOST VARIABLES

SQL> variable n number

SQL> print n

:n=v_sal /12;

:n这个加了:前缀的变量不是PLSQL变量,而是HOST。

七、以下几个PLSQL声明变量,哪个不合法?

A、DECLARE V_ID NUMBER(4);

B、DECLARE V_X,V_Y,V_Z VARCHAR2(9);

C、DECLARE V_BIRTH DATE NOT NULL;

D、DECLARE V_IN_STOCK BOOLEAN:=1;

E、DECLARE TYPE NAME_TAB IS TABLE OF VARCHAR2(20)

INDEX BY BINARY_INTEGER;

DEPT_NAME NAME_TAB;

上面的习题我会在下章给出答案,这也正是声明变量的规则和难点。

课程十 写执行语句

本课重点:

1、了解PLSQL执行区间的重要性

2、写执行语句

3、描述嵌套块的规则

4、执行且测试PLSQL块

5、使用代码惯例

注意:以下实例中标点均为英文半角

一、PLSQL 块的语法规则:

1、语句可以跨跃几行。

2、词汇单元可以包括:分隔符、标识符、文字、和注释内容。

3、分隔符:

+-*/=<>||....

4、标识符:

最多30个字符,不能有保留字除非用双引号引起。

字母开头,不与列同名。

5、文字串:如 V_ENAME:='FANCY';要用单引号括起来。

数值型可以用简单记数和科学记数法。

6、注释内容:单行时用-- 多行用/* */ ,与C很相似

二、SQL函数在PL/SQL的使用:

1、可用的:

单行数值型、字符型和转换型,日期型。

2、不可用的:

最大、最小、DECODE、分组函数。

实例:

BEGIN SELECT TO_CHAR(HIREDATE,'MON,DD,YYYY') FROM EMP; END; V_comment:=user||':'||sysdate; -- 会编译出错 V_comment:=user||':'||to_char(sysdate); --正确

如果有可能,PLSQL都会进行数据一致性的转换,但ORACLE推荐你应该进行显示的转换,因为这样会提高性能。

三、嵌套块和变量作用区域

1、执行语句允许嵌套时嵌套。

2、嵌套块可以看作正常的语句块。

3、错误处理模块可以包括一个嵌套块

4、exponential指数 逻辑、算数、连接、小括号

5、看正面实例:

declare vjob varchar(9); v_count number:=0; vtotal date:=sysdate +7; c_tax constant number(3,2):=8.25; v_valid boolean not null:=true; ttt vtotal%type; begin --select sysdate into vtotal from dual;--体会有无此句与结果的影响 dbms_output.put_line (vtotal); end; /

注意:在执行块之前,要在SQL PLUS中执行:SET SERVEROUTPUT ON

四、以实例来说明函数的参数声明作用域

declare v_weight number(3):=600; v_message varchar2(255):='product10000'; begin declare --sub-block v_weight number(3):=1; v_message varchar2(255):='pro300'; begin v_weight:=v_weight +1; end; v_weight:=v_weight +1; v_message:=v_message || 'my name'; end; /

子块中的V_WEIGHT值为 2

我们可以在子块中加入:dbms_output.put_line('subblock value is '||v_weight);

在主体中加入:dbms_output.put_line('main value is '||v_weight);

我们发现MAINBLOCK中V_WEIGHT为 601

改动:

1、在主块的声明中加 v_date date default sysdate;

在子块中加入:dbms_output.put_line('subblock date value is '||v_date);

执行结果:subblock date value is 22-11月-01

* 说明:主块中的变量,如果子块中没有同名变量声明,则继承主块中的声明和初始化值;

2、在子块中加入:v_sub char(9);

dbms_output.put_line('subblock char value is '||v_sub);

此时正常输出。

在主块中加入:dbms_output.put_line('main char value is '||v_sub);

输出:ORA-06550: 第 21 行, 第 45 列:

PLS-00201: 必须说明标识符 'V_SUB'

说明:

子块中声明的变量主块中并不知晓,因此出错。

了解了此实例,一切情况的变量的值的走向就都明了了。

课程十一 与ORACLE SERVER交互

本课重点:

1、在PLSQL中成功的写SELECT语句

2、动态声明PLSQL变量类型与SIZE

3、在PLSQL中写DML语句

4、在PLSQL中控制事务

5、确定DML操作的结果

注意:以下实例中标点均为英文半角

一、PLSQL中的SQL语句:

SELECT、DML、COMMIT、ROLLBACK、SAVEPOINT、CURSOR

特殊强调:PLSQL不支持DCL,不要问为什么。(DBMS_SQL package allows you to issue DDL and DCL statements.)

二、SELECT

SELECT select_list INTO variable_name | record_name FROM table WHERE condition;

例:

SQL> r declare v_deptno number(2); v_loc varchar2(15); begin select deptno,loc into v_deptno,v_loc from dept where dname='SALES'; DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc); * end; and CHICAGO

选取字段与变量个数和类型要一致。声明的变量一定要在SIZE上大于返回的赋值,否则提示缓冲区溢出。

如果SELECT语句没有返回值:

ORA-01403: 未找到数据

ORA-06512: 在line 5

如果有多个值返回:

ORA-01422: 实际返回的行数超出请求的行数

这些我们到了错误处理时会逐一讲解。

例:

上面的例子可以改为:

declare v_deptno dept.deptno%type; v_loc dept.loc%type; begin select deptno,loc into v_deptno,v_loc from dept where dname='SALES'; DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc); end; /

这样,可以在未知其他字段大小和类型的时候定义变量,提高兼容性。

三、DML 操作:

1、实例:

declare v_empno emp.empno%type; begin select max(empno) into v_empno from emp; v_empno:=v_empno+1; insert into emp(empno,ename,job,deptno) values(v_empno,'asdfasdf','ddddd',10); end; /

这样也可以实现如SEQUENCE一样的编号唯一递增。

2、更新和删除:

这个较为简单:

DECLARE V_DEPTNO EMP.DEPtno%type :=10; begin delete from emp where deptno=v_deptno; end; /

大家多多实践即可掌握。

PL/SQL首先检查一个标识符是否是一个数据库的列名,如果不是,再假定它是一个PLSQL的标识符。所以如果一个PLSQL的变量名为ID,列中也有个ID,如:

SELECT date_ordered, date_shipped INTO date_ordered, date_shipped FROM s_ord WHERE >

就会返回TOO MANY ROWS,这是要尽量避免的。

四、SQL CURSOR

游标是一个独立SQL工作区,有两种性质的游标:

隐式游标:当PARSE 和EXECUTE 时使用隐式游标。

显式游标:是由程序员显式声明的。

游标的属性:

SQL%ROWCOUNT:一个整数值,最近SQL语句影响的行数。

SQL%FOUND BOOLEAN属性,如果为TRUE,说明最近的SQL STATEMENT有返回值。

SQL%NOTFOUND 与SQL%FOUND相反

SQL%ISOPEN 在隐式游标中经常是FALSE,因为执行后立即自动关闭了。

SQL> variable row_de number SQL> r declare v_deptno number:=10; begin delete from emp where deptno=v_deptno; :row_de:=sql%rowcount; * end;

PL/SQL 过程已成功完成。

SQL> print row_de ——这是一个SQL PLUS变量

ROW_DE

----------

4

这时其实并没有真正的删除,而是需要 COMMIT或ROLLBACK,来完成事务。

课程十二 编写控制结构语句

本课重点:

1、结构控制的的用途和类型

2、IF 结构

3、构造和标识不同的循环

4、使用逻辑表

5、控制流和嵌套

注意:以下实例中标点均为英文半角

一、控制执行流

可以是分支和循环:

IF THEN END IF IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF;

例子:

IF V_ENAME='OSBORNE' THEN V_MGR:=22; END IF;

这里我们可以注意,PLSQL和C语言或JAVA在条件上的不同,=代表关系运算,而:=代表赋值。

看一个函数:

create FUNCTION calc_val (v_start IN NUMBER) RETURN NUMBER IS BEGIN IF v_start > 100 THEN RETURN (2 * v_start); ELSIF v_start >= 50 THEN RETURN (.5 * v_start); ELSE RETURN (.1 * v_start); END IF; END calc_val;

现在,虽然我们尚未讲解CREATE 函数或过程,但可以看到IF 条件在其中的作用。

二、注意LOGIC TABLE中的逻辑对应关系

1、NOT、AND、OR

2、任何表达式中含有空值结果都为 NULL

3、连接字符串中含有空值会把NULL作为 EMPTY STRING

declare

v_deptno dept.deptno%type;

v_loc




©2009 Baidu



引文来源  PL/SQL循序渐进全面学习教程(3)_Net things Ruyan
  评论这张
 
阅读(334)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018