^^ sql oracle 表达式,Oracle/SQL概念认识及应用

Oracle/SQL 概念认识及应用第一章：PL/SQL简介

1.1 什么是PL、SQL

PL/SQL是一种比较复杂的程序设计语言，用于从各种环境访问oracle数据库。现在PL/SQL和服务器集成在一起。这样可以快速而有效的处理PL/SQL代码。

SQL是一种第四代语言，这意味着它会描述应该做什么，但不会描述如何去做，所以相对第三代语言来说，SQL非常简单，它把用户与实际的数据结构和算法隔离开来，对数据的处理完全由该语言来完成实现。

而在某些情况下，第三代语言的过程结构对表达程序过程来说是非常有用的，这也是引入PL/SQL的原因

Oracle数据库支持ANSI标准的SQL。PL/SQL是在SQL语言中扩充了面向过程语言中使用的程序结构：

变量和类型

控制语句

过程和函数

对象类型和方法

1.2 PL、SQL功能性

块结构

PL/SQL称为结构化语言，PL/SQL块是PL/SQL语言的基本单位，其中可能包含程序代码、变量声明、错误处理，过程、函数、甚至包含其他PL/SQL块

DECLARE

/*声明部分：要定义的变量、游标定义及过程函数的声明*/

v_DeptName VARCHAR2(20);

v_CurrentDate DATE;

v_Credits NUMBER()3;

v_CurrentlyRegistered BOOLEAN;

v_LoopCounter BINARY_INTEGER;

BEGIN

/*执行部分：为程序执行的主体，它由PL/SQL语句构成，只有执行部分是必须的，可执行部分也必须至少包含一条可执行语句*/

EXCEPTION

/*异常部分*/

END;

变量和类型

变量的类型定义了可以存放的信息的类别，PL/SQL变量可以与数据库列具有同样的数据类型，也可以是其他类型，也支持自定义数据类型，如记录类型，表类型等。

程序控制结构

Oracle程序控制结构有选择结构和循环结构

IF...ELSE语句

CASE语句

LOOP循环

FOR循环

WHILE循环

游标：

用来处理使用SELECT语句从数据库中检索到的多行记录的工具，借助于游标，数据库应用程序可以对一组记录逐个处理，每次处理一行。这在对某一查询结果逐个进行统计及分析登录数据处理时特别有用

过程和函数：

Pl/SQL中的过程和函数(通称为子程序)是Pl/SQL块的一种特殊类型，这种类型的子程序可以以编译的形式存放在数据库中，并为后续的程序块调用.过程和函数的区别是过程没有返回值，而函数有返回值

包：

Pl/SQL程序中的子程序可以和变量与类型共同组成包。Pl/SQL的包由两部分组成，即说明部分和包体。一个包可以带多个相关的过程。通过包将相关操作的函数和过程组成一个容器，可以方便对函数和过程的调用。

动态SQL：

借助于动态SQL，一个Pl/SQL应用程序可以在运行期间构造并执行SQL语句。

对象类型：

Oracle9支持对象类型，还支持对象类型之间的继承

集合：

Pl/SQL的集合类似其他第三代计算机语言中的数组。Pl/SQL提供了三种不同的集合类型：按表索引、嵌套表和可变长度数组

内置包：

Oracle还提供了若干有特殊功能的内置包，实现一些底层操作和高级功能。

第二章：PL/SQL基础

2.1 PL/SQL程序结构

2.1.1 PL/SQL块的类型

所有的PL/SQL程序都是以块作为基本单位，以及都是由块组成的。

这些块可以是按顺序出现的，也可以是嵌套的

块可以根据其带名称和不带名称以及在数据库中的存储方式分为以下类型：

匿名块：只能执行一次，不能存储在数据库中

带名块：一种带标签的匿名块

子程序：可以存储在数据库中的过程，函数和包，可以多次执行，可以在外部的程序中显示调用

触发器：存储在数据库中的带名块，当相应的触发事件发生时触发器就被自动执行，触发器事件一般是针对特定的数据库表进行的DML语句，如INSERT,UPDATE,DELTE

<>

DECLARE

v_num1 NUMBER := 1;

v_num2 NUMBER := 2;

v_str1 VARCHAR2 (30) := '你好！';

v_str2 VARCHAR2 (30) := '这是你第一次接触PL/SQL';

v_outputstr VARCHAR2 (30);

BEGIN

INSERT INTO test_table(ncolumn, vcolumn)VALUES (v_num1, v_str1);

INSERT INTO test_table(ncolumn, vcolumn)VALUES (v_num2, v_str2);

SELECT vcolumn INTO v_outputstr FROM test_table WHERE ncolumn = v_num1;

DBMS_OUTPUT.put_line (v_outputstr);

SELECT vcolumn INTO v_outputstr FROM test_table WHERE ncolumn = v_num2;

DBMS_OUTPUT.put_line (v_outputstr);

END I_Namedblock;

带名块就是在匿名块前面加上一个标签

把DECLARE关键字 变成 CREATE OR REPLACE PROCEDURE 就变成了存储过程

CREATE OR REPLACE PROCEDURE 过程名 IS

2.1.2 PL/SQL块的结构

所有块都包含3个部分：声明部分，执行部分，异常处理部分

DECLARE

v_ncolumn NUMBER :=6;

v_vcolumn VARCHAR2(30);

BEGIN

SELECT v_column INTO v_vcolumn FROM test_table WHERE ncolumn=v_ncolumn;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有找到匹配的数据');

END;

在过程和函数中声明变量是不需要DECLARE的，但在声明TRIGGER中的变量是必须要有DECLARE

如果块中不需要声明变量，类型和游标时，声明部分可以省略

执行部分是不可缺少的，一般以BEGIN开始 END结束。END关键字必须是分号结束的

2.1.3 词法单位

e代表乘以10的多少次幂

2.2 变量声明

Variable_name [CONSTANT] type [NOT NULL][:=value | DEAFULT value]

Variable_name是变量名

CONSTANT 声明的是一个常量

Type 是类型

NOT NULL 可选，表示不能为空

:=value | DEAFULT value表示赋值

缺省的变量会被初始化为NULL

不能把NULL付给一个定义为NOT NULL的变量acct_id INTEGER(5) NOT NULL;这样是不允许的

在NATURAL和POSITIVEN，他们被预先定义为NOT NULL

每行只能声明一个变量，这和其他语言不同

BEGIN END;是可以嵌套的

2.3 变量类型

4中标量类型(数字类型，布尔型，字符型，日期型)

复合型

引用型

LOB型

数字型的三种基本类型：BINARY_INTEGER,NUMBER,PLS_INTEGER

字符型:CHAR,VARCHAR2,LONG和LONG RAW,RAW,ROWID和UROWID

LOB类型:BFILE,BLOB,CLOB,NCLOB

布尔类型：BOOLEAN(TRUE,FALSE,NULL)

日期时间类型：DATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE,INTERVAL YEAR TO MONTH,INTERVAL DAY TO SENCOND

复合类型：记录，表，数组

引用类型：REF CURSOR 和ref object_type

用户自定义类型

2.4 数据类型转换

显式转换，可以使用内置函数

隐式转换，隐式转换不是好的习惯，可能会影响效率，结果可能不可预料

2.5 表达式

表达式是由操作符和运算符的结合

CASE 表达式

CASE SELECTOR

WHEN express THEN result1

WHEN express THEN result3

WHEN express THEN result2

END;

第三章： oracle SQL 常见错误及解决方法

Error(13,50): PL/SQL: ORA-00984:列在此处不允许

解决办法：列与你插入的值类型不一致

解决办法：

INSERT INTO TAB_EMPLOYEE(id,emp_no,emp_name,user_name,password,role,privilege,flag)VALUES(user_id,emp_no,emp_name,user_name, emp_password, emp_role, emp_privilege, emp_flag);

Error(13,54): PLS-00049:错误的赋值变量'EMP_NO'

解决办法： INSERT INTO TAB_EMPLOYEE(id,emp_no)VALUES(user_id,：emp_no);

Error(13,28): PL/SQL: ORA-01733:此处不允许虚拟列

INSERT INTO TAB_EMPLOYEE(id,emp_no)VALUES(user_id,emp_no);

在存储过程中的TAB_EMPLOYEE(id,emp_no)中的列名必须数数据库表中已存在的列，不能自己随便写列名

Error(6,10): PLS-00103:出现符号"VARCHAR2"在需要下列之一时：:= . ( @% ;符号":="被替换为"VARCHAR2"后继续。

解决办法：可能是声明变量的位置不对，应该在AS或IS后面，begin前面

Error(5,9): PLS-00215:字符串长度限制在范围(1...32767)

解决办法：需要指明varchar2的数据长度

CREATE OR REPLACE

FUNCTION FINDEMPID

( emp_name IN VARCHAR2

) RETURN VARCHAR2 AS

emp_id VARCHAR2(10);

BEGIN

。。。

END;