PLSQL基础（一）

1.hello,world起步

点击file,创建一个sql window

DECLARE  v_message VARCHAR2(12):='Hello,world';BEGIN  dbms_output.put_line(v_message);END;

(F8)运行结果：

1）PLSQL的每一段程序都是一个块（block）。DECLARE 是声明部分，可以声明变量、常量；BEGIN 顾名思义就是“开始”执行命令了。

2）定义一个叫做“v_message”的变量，变量类型为可变长字符串；“：=”在PLSQL中是用来赋值的，字符串用单引号括起来。

v_message VARCHAR2(12):='Hello,world';

3）输出变量，相当于C#中的console.writeLine()

dbms_output.put_line(v_message);

2.PLSQL变量

（1）变量的声明

identifier [constant] datatype [NOT NULL][:=| DEFAULT expr];

identifier 是变量名，建议用v开头，datatype是类型，中括号的是可选参数。

（2）举个栗子

DECLARE    v_name VARCHAR2(10):='melon';    v_salary NUMBER(5,2):=800.89;    v_sname v_name%TYPE:='小明';    v_date  DATE:=SYSDATE;BEGIN  dbms_output.put_line('系统时间:'||v_date);  dbms_output.put_line(v_name||'的薪水是'||v_salary);  dbms_output.put_line(v_sname||'是学生');END;

运行结果：

1）变量类型后面的括号是变量的长度，number(5,2)是说变量长度为5，小数点后面有两位。

2）PLSQL特有的%TYPE属性，来声明和XX类型一致的变量类型。v_sname的类型和 v_name的类型一致

v_sname v_name%TYPE:='小明';

3）SYSDATE可以获取当前的系统时间;’||’是连接操作符，相当于我们通常用的’+’,用于连接字符串。

DECLARE   v_salary NUMBER(6):=&p_salary;   v_totalSalary v_salary%TYPE;BEGIN  v_totalSalary:=v_salary*12;  dbms_output.put_line('年收入是'||v_totalSalary);END;

运行结果：

&parameter在PLSQL的SQL windows的执行环境中，用于提示用户输入一个具体的值

（3）PLSQL嵌套块

<<OUT>>DECLARE    /*       PLSQL块嵌套      ********      ********      ********    */    --声明一个外部变量    v_firstName VARCHAR2(20);BEGIN  DECLARE     --内部变量     v_lastName varchar2(20);     v_name VARCHAR2(20);  BEGIN     v_lastName:='不感冒';     out.v_firstName:='假装';     v_name:=out.v_firstName;     dbms_output.put_line('your full name is '||'【'||v_name||v_lastName||'】');  END;END;

运行效果：

1）PLSQL块之间可以嵌套的，在内块可以通过下面的方式来访问外块变量，out是给外块取的名字

out.v_firstName:='假装';

2）【/**/】可用于多行注释，【–】用于单行注释

3.PLSQL控制语句

（1）PLSQL判断语句(IF,CASE)

DECLARE  v_score   NUMBER(2) := &p_score;  v_message VARCHAR2(100);BEGIN  IF v_score < 60 THEN    v_message := 'bad';  ELSIF v_score < 80 THEN    v_message := 'not very bad';  ELSIF v_score < 90 THEN    v_message := 'good';  ELSE    v_message := 'quite good';  END IF;  dbms_output.put_line('your score is '||v_score||'. level:'||v_message);END;

运行效果：

注意这里的if 条件是不用括号括起来的；其他条件是【ELSIF】不是【ELSEIF】

DECLARE  v_day   char(1) := &p_day;  v_message VARCHAR2(100);BEGIN  CASE v_day    WHEN 6 THEN v_message:='睡觉';    WHEN 7 THEN v_message:='爬山';    ELSE v_message:='工作'; END CASE;  dbms_output.put_line('星期'||v_day||'要去'||v_message);END;

(2)PLSQL循环（LOOP,WHILE,FOR）

【LOOP】

DECLARE   v_salary NUMBER(5):=&p_salary;   v_original v_salary%TYPE;   v_count  NUMBER(2):=0;BEGIN   v_original:=v_salary;  LOOP     v_salary:=v_salary*(1+0.21);     v_count:=v_count+1;     EXIT WHEN v_salary>3000;  END LOOP;  dbms_output.put_line('原始工资'||v_original||',涨后工资'||v_salary||',执行次数:'||v_count);END;

运行结果：

【WHILE】

DECLARE  v_salary   NUMBER(5) := &p_salary;  v_original v_salary%TYPE;  v_count    NUMBER(2) := 0;BEGIN  v_original := v_salary;  WHILE v_salary < 3000 LOOP    v_salary := v_salary * (1 + 0.21);    v_count  := v_count + 1;  END LOOP;  dbms_output.put_line('原始工资' || v_original || ',涨后工资' || v_salary || ',执行次数:' || v_count);END;

【FOR】

DECLARE  v_salary   NUMBER(5) := &p_salary;  v_original v_salary%TYPE;  v_count    NUMBER(2) := 0;BEGIN  v_original := v_salary;  FOR i IN 1 .. 7 LOOP    v_salary := v_salary * (1 + 0.21);    v_count  := v_count + 1;  END LOOP;  dbms_output.put_line('原始工资' || v_original || ',涨后工资' || v_salary || ',执行次数:' || v_count);END;

三者都可以用来循环，当知道执行次数的情况下用for比较合适

4.自定义数据类型

自定义数据类型包括记录类型、PLSQL内存表类型（类似于数组）

（1）记录类型

【语法】

TYPE type_name IS RECORD     (field_declaration[,field_declaration]...);     IDENTIFIER type_name

【栗子】

DECLARE    TYPE student_record IS RECORD    (        v_name VARCHAR2(20),        v_age  NUMBER(2),        v_sex  VARCHAR2(4)    );   stu  student_record;BEGIN   stu.v_name:='小明同学';   stu.v_age:=10;   stu.v_sex:='男';  dbms_output.put_line(stu.v_name||','||stu.v_sex||','||stu.v_age||'岁了');END;

【%ROWTYPE】

顾名思义，可理解为行记录类型，表示某张表的记录类型或者用户指定的记录类型

先简单地创建一个【student】表，只是为了测试（这不规范）

CREATE TABLE student(       stu_no VARCHAR(5),       stu_name VARCHAR(20),       stu_sex  VARCHAR2(5))

往里面插入一条数据

INSERT INTO student VALUES(12004,'德玛西亚','男');

找到学号为’12004’学生的个人信息

DECLARE   student_rec student%ROWTYPE;BEGIN  SELECT * INTO student_rec         FROM student         WHERE stu_no=&p_stu_no;  dbms_output.put_line(student_rec.stu_name||'的学号是'||student_rec.stu_no);END;

输出结果：

student_rec 是和student表记录类型一样的行记录类型，简言之，student表下的字段属性和类型，student_rec也拥有。值得注意的是，它只能存放一条记录，可记为【一行多列】

（2）内存表类型

这种结构类似于数组，使用主键提供类似于数组那样的元素访问。这种类型必须包括两部分：使用BINARY_INTEGER类型构成的索引主键；一个简单类型或自定义类型的字段作为具体的数组元素

【语法】

TYPE type_name IS TABLE OF     column_type|VARIABLE%TYPE     |table.column%TYPE}[NOT NULL]     |TABLE.%ROWTYPE     [INDEX BY BINARY_INTEGER]; IDENTIFIER type_name

【栗子】

DECLARE    TYPE stu_name_type IS TABLE OF         student.stu_name%TYPE         INDEX BY BINARY_INTEGER;         name_array stu_name_type;BEGIN          name_array(1):='六郎';          name_array(2):='三毛';           dbms_output.put_line(name_array(1)||name_array(2));END;

5.游标

游标是一个私有的SQL工作区域，ORCALE有两种游标，分别为隐式和显式游标。隐式游标是Oracle服务器使用的解析和执行我们提交的SQL语句；显式游标是程序猿在程序中显式声明的。

（1）游标的属性

• SQL%ROWCOUNT ——》表示SQL语句受影响的行数
• SQL%FOUND ——》 表示SQL语句是否影响了一行以上
• SQL%NOTFOUND ——》 表示SQL语句没有影响数据
• SQL%ISOPEN ——》表示是否打开

（2）显式游标

1）通常所说的游标就是显式游标。对于返回多行结果的SQL语句的多行结果，可用显式游标独立处理每一行数据。

2)游标的相关函数可以做到：

• 一行一行的处理返回的数据
• 保持当前处理行的一个跟踪，像指针一样指示当前处理的记录
• 允许程序猿在PLSQL块中人为的控制游标的开启、关闭、上下移动

3）游标控制过程

【声明游标】=》【打开游标】=》【提取当前行到变量】=》【测试行的存在】=》【关闭游标】

（3）操作游标

DECLARE     v_name student.stu_name%TYPE;     v_no   student.stu_no%TYPE;     CURSOR stu_cursor IS            SELECT stu_name,stu_no FROM student;BEGIN      --打开游标      OPEN stu_cursor;      --循环游标的数据      LOOP        --提取一行数据        FETCH stu_cursor INTO v_name,v_no;        EXIT WHEN stu_cursor%ROWCOUNT>3 OR stu_cursor%NOTFOUND;        dbms_output.put_line(v_no||v_name);      END LOOP;      --关闭游标      CLOSE stu_cursor;END;

运行结果：

如果觉的上面对游标的遍历繁琐的话，可以使用for循环，省去了对游标的声明、打开、提取、测试、关闭等语句

【语法】

FOR record_name IN cursor_name LOOP  statement1;  statement2;  ...  END LOOP;

【栗子】

DECLARE     v_name student.stu_name%TYPE;     v_no   student.stu_no%TYPE;BEGIN    FOR stu_cursor IN (                       SELECT stu_name,stu_no                                   FROM student)         LOOP           v_name:=stu_cursor.stu_name;           v_no:=stu_cursor.stu_no;           dbms_output.put_line('【学号】:'||v_no||'【姓名】:'||v_name);         END LOOP;END;

运行结果：

（4）FOR UPDATE NOWAIT语句

有的时候我们打开一个游标是为了更新或删除一些记录，这种情况下我们希望在打开游标的时候锁定相关的记录，应该使用这条语句，如果锁定失败就停止不再继续，以免出现长时间等待资源的死锁情况。

【语法】

SELECT  ....FROM        ....FOR UPDATE [OF column_reference][NOWAIT]