pstgresql 定义变量 plsql定义变量并赋值

  PL/SQL作为对SQL的过程化补充，允许通过对变量，过程和事物等数据库对象进行数据操作实现SQL编程

SQL中的对象包括

        存储过程、表、视图、函数、触发器。。。。。。等

一、PL/SQL编程

1、  变量

    变量时PL/SQL中存储单个数据的基本单元

       创建变量

    declare    变量名    数据类型

   变量赋值

     变量名:=值

1. --创建变量
2. --变量必须结合过程使用
3. declare
4. v_age int:=100;
5. v_name varchar2(20);

       使用键盘接收

              变量赋值 时在变量前加 &变量名

1. --使用键盘接收
2. v_sex varchar2(10):='&v_sex';

               begin

                       语句块;

               end;

           begin end;称为过程块。

              变量定义后必须在begin  end 语句块中使用也就是说，变量必须结合过程使用。

           在语句块中使用 dbms_output.put_line() 打印显示数据

      注意点：Oracle的过程中只能写DML语句，如果出现了DQL语句，则该语句中必须要有into关键字，

                       into关键字用于将查询的结果写入变量。

例子：

1. --打印年龄和tom一样大的学生的姓名和性别
2. declare
3. v_age int;
4. v_stuName varchar2(20);
5. v_sex varchar2(10);
6. begin
7. --查询tom的年龄,并将查询后的结果写入变量
8. --使用into写入变量的结果必须是单行数据
9. select age into v_age from users where username='tom';
10. --查询比v_age一样大的学生信息
11. select username,sex into v_stuName,v_sex from users where age=v_age and username!='tom';
12. --打印
13. dbms_output.put_line(v_stuName||' '||v_sex);
14. end;
15. --打印显示综合讨论区版块内回帖最多的主贴标题，发帖人姓名和该贴的回帖数
16. declare
17. v_title varchar2(50);
18. v_name varchar2(2);
19. v_replyCount int;
20. --综合讨论区的版块编号
21. v_sid int;
22. --回帖最高的主贴编号
23. v_tid int;
24. --发帖人的编号
25. v_uid int;
26. begin
27. --查询综合讨论区的版块编号
28. select sid into v_sid from bbssection where sname='综合讨论';
29. --查询综合讨论区版块最高的主贴的回帖数
30. select max(replyCount)into v_replyCount from((select rtid,count(*)as replyCount from bbsreply where rsid=v_sid groupby rtid));
31. --根据最高的回帖数和版块编号查询对应的主贴编号
32. select rtid into v_tid from bbsreply where rsid=v_sid groupby rtid having count(*)=v_replyCount;
33. --根据主贴编号查询主贴的标题和发帖人的编号
34. select ttopic,tuid into v_title,v_uid from bbstopic where tid=v_tid;
35. --根据发帖人的编号查询发帖人的姓名
36. select uname into v_name from bbsusers where userid=v_uid;
37. end;

2、属性列变量

    属性变量可以根据表的列类型动态定义相应的数据类型

    declare    变量名    表名.列名%type

例子：

1. --查询Java课程成绩最高的学生的姓名和他的成绩
2. declare
3. --使用属性列来动态作用变量的数据类型
4. v_name student.stuname%type;
5. v_score score.score%type;
6. v_courseId course.courseid%type;
7. v_stuId student.stuid%type;
8. begin
9. --查询Java课程的编号
10. select courseId into v_courseId from course where lower(courseName)='java';
11. --根据课程编号查询最高的成绩
12. select max(score)into v_score from score where courseId=v_courseId;
13. --根据课程编号和最高成绩查询学号
14. select stuId into v_stuId from score where courseId=v_courseId and score=v_score;
15. --根据学号查询学生的姓名
16. select stuName into v_name from student where stuId=v_stuId;
17. dbms_output.put_line('Java课程成绩最高的同学是：'||v_Name||'，成绩为：'||v_score);
18. end;
19. --打印sales部门第一位入职的员工姓名和入职时间
20. declare
21. v_firstName emp.first_name%type;
22. v_lastName emp.last_name%type;
23. v_date emp.hire_date%type;
24. v_depId int;
25. begin
26. --查询salse部门的编号
27. select dep.department_id into v_depId from dep where dep.department_name='Sales';
28. --查询该部门第一位入职的员工信息
29. select first_name,last_name,hire_date into v_firstName,v_lastName,v_date from
30. (select first_name,last_name,hire_date from emp where emp.department_id=v_depId order by hire_date)
31. where rownum=1;
32. dbms_output.put_line('sales部门第一位入职的员工姓名是：'||v_firstName||v_lastName||',入职时间是：'||to_char(v_date,'yyyy-mm-dd'));
33. end;

3、属性行变量

        declare    变量名    表名%rowtype

该变量表示行数据

       行类型必须使用变量名.列名的方式分别获取该行中每个列的值

可以将部分数据写入行类型变量，如果行类型中有部分列没有进行过赋值则该列的默认值为空字符。

1. --打印员工编号为100的员工的所有信息
2. declare
3. --属性行类型，该变量表示emp表中的行数据
4. v_empInfo emp%rowtype;
5. begin
6. --将整行数据写入变量
7. --select*into v_empInfo from emp where emp.employee_id=100;
8. --打印显示数据,行类型必须使用变量名.列名的方式分别获取该行中每个列的值进行打印
9. --dbms_output.put_line(v_empInfo.employee_id||' '||v_empInfo.first_name);
10. --将部分数据写入行类型变量
11. select first_name,last_name,hire_date into v_empInfo.first_name,v_empInfo.last_name,v_empInfo.hire_date from emp where emp.employee_id=100;
12. --如果行类型中有部分列没有进行过赋值则该列的默认值为空字符
13. dbms_output.put_line(v_empInfo.first_name||v_empInfo.last_name||v_empInfo.hire_date||v_empInfo.salary);
14. end;

4、复合类型

        复合类型即自定义的属性变量                （变量名基本使用  t_  开头）

        declare    type    变量名    is    record

        （

                    变量名    数据类型,

                    变量名    数据类型，

                      ......

                    变量名    数据类型

         ）

设置复合属性的结构时，可以使用属性列或者属性行。

1. --打印显示工资最高的员工的姓名，薪水，所在部门名称，岗位名称
2. --定义一个复合类型，复合类型即自定义属性行
3. declare
4. type t_myRow is record
5. (
6. --设定复合类型的结构时，可以使用属性列或者属性行
7. firstName emp.first_name%type,
8. lastName emp.last_name%type,
9. depName varchar2(50),
10. jobName varchar2(50)
11. );
12. --创建变量
13. v_max emp.salary%type;
14. v_info t_myRow;
15. v_depId int;
16. v_jobId job.job_id%type;
17. begin
18. --查询最高的工资
19. select max(salary)into v_max from emp;
20. --根据最高工资查询员工的姓名，部门编号，岗位编号
21. select first_name,last_name,department_id,job_id into v_info.firstName,v_info.lastName,v_depId,v_jobId from emp where salary=v_max;
22. --根据部门编号查询部门名称
23. select dep.department_name into v_info.depName from dep where dep.department_id=v_depId;
24. --根据岗位编号查询岗位名称
25. select job.job_title into v_info.jobName from job where job.job_id=v_jobId;
26. dbms_output.put_line(v_info.firstName||' '||v_info.lastName||' '||v_info.depName||' '||v_info.jobName);
27. end;

        使用属性列的复合类型

1. --查询部门人数最多的部门名称和平均薪资，最高薪资以及最低薪资
2. declare
3. type empType is record
4. (
5. depName dep.department_name%type,
6. avgSalary float,
7. maxSalary float,
8. minSalary float
9. );
10. v_emp empType;
11. v_depId int;
12. begin
13. --select emp.department_id,count(*)as empCount from emp groupby emp.department_id order by empCount desc;
14. select emp.department_id into v_depId from emp groupby emp.department_id having count(*)=
15. (select max(empCount)as maxCount from(select count(*)as empCount from emp groupby emp.department_id));
16. --根据部门编号查询平均薪资，最高薪资，最低薪资
17. select avg(salary),max(salary),min(salary)into v_emp.avgSalary,v_emp.maxSalary,v_emp.minSalary from emp where emp.department_id=v_depId;
18. --根据部门编号查询部门名称
19. select dep.department_name into v_emp.depName from dep where dep.department_id=v_depId;
20. dbms_output.put_line(v_emp.depName||' '||round(v_emp.avgSalary,1)||' '||v_emp.maxSalary||' '||v_emp.minSalary);
21. end;

        使用属性行的复合类型

1. --查询所有成绩分数最高的学生的详细信息，课程名称和分数
2. declare
3. type stuType is record
4. (
5. stuInfo users%rowtype,
6. courseName course.coursename%type,
7. score float
8. );
9. v_stu stuType;
10. v_stuId int;
11. v_courseId int;
12. begin
13. --查询成绩最高的学生的学号,课程编号，分数
14. select stuId,courseId,score into v_stuId,v_courseId,v_stu.score from score where score=(select max(score)from score);
15. --根据学号查询学生的信息
16. select*into v_stu.stuInfo from users where userId=v_stuId;
17. --根据课程编号查询课程名称
18. select courseName into v_stu.courseName from course where courseId=v_courseId;
19. dbms_output.put_line(v_stu.stuInfo.userName||' '||v_stu.courseName||' '||v_stu.score);
20. end;

二、PL/SQL的流程控制

    PL/SQL可以实现对数据进行条件判断循环处理等操作

       （1） 条件判断的语句

                  if

                case      （相当于JAVA的switch结构）

        （2）循环语句

               loop

               while

                for

1） 

 if    条件

            if    表达式    then

            语句;

            end if;

 if    else条件

               if    表达式    then

                       语句；

               else

                        语句；

               end    if;

if  elsif   else语句

                if    表达式  then

                        语句;

                elsif    表达式    then

                        语句;

                else

                        语句;

                end if；

1. declare
2. v_num1 int:='&v_num1';
3. v_num2 int:='&v_num2';
4. begin
5. if v_num1>v_num2 then
6. dbms_output.put_line('num1大于num2');
7. elsif v_num1=v_num2 then
8. dbms_output.put_line('num1等于num2');
9. else
10. dbms_output.put_line('num1小于num2');
11. endif;
12. end;

例：

1. --对DavidAustin进行加薪，如果David的薪水是高于该部门的平均薪水则加薪100，如果是和平均相同则加薪300，如果是低于平均薪资则加500
2. declare
3. v_salary float;
4. v_avg float;
5. v_empId int;
6. v_money float;
7. begin
8. --查询DavidAustin的薪水
9. --into v_salary
10. select salary,employee_id into v_salary,v_empId from emp where first_name||last_name='DavidAustin';
11. --查询他所在部门的平均薪资
12. select avg(salary)into v_avg from emp where department_id=(select department_id from emp where first_name||last_name='DavidAustin');
13. --比较薪资
14. if v_salary>v_avg then
15. v_money:=100;
16. elsif v_salary=v_avg then
17. v_money:=300;
18. else
19. v_money:=500;
20. endif;
21. update emp set salary=salary+v_money where emp.employee_id=v_empId;
22. end;

2） case    条件

                case  变量

                when    值    then

                    语句;

                when    值    then

                    语句；

                 else

                    语句；

                end  case；

1. --使用case进行等值判断
2. case v_num1
3. when10then
4. dbms_output.put_line('num1的值是10');
5. when20then
6. dbms_output.put_line('num1的值是20');
7. endcase;

       case    语句可以代替if    elsif    语句

                    case

                           when    表达式    then    

                               语句;

                           when    表达式    then    

                               语句;

                    end    case;

1. --使用case实现ifelsif语句
2. case
3. when v_num1>v_num2 then
4. dbms_output.put_line('num1大于num2');
5. when v_num1=v_num2 then
6. dbms_output.put_line('num1等于num2');
7. when v_num1<v_num2 then
8. dbms_output.put_line('num1小于num2');
9. endcase;

在查询语句中可以使用case

        select    case    列

                            when    值    then   

                                            语句

                            when    值    then

                                             语句

                       end    case                            

            from    表名；

 

1. --查询所有用户的姓名和性别，性别要求以中文来呈现
2. select userName,case sex
3. when'male'then'男'
4. when'female'then'女'
5. endcase
6. from users
7. select userName,
8. case
9. when sex='male'then'男'
10. when sex='female'then'女'
11. endcase
12. from users

if    和    case    的区别：

语句与 if 语句都能实现条件结构

语句可以结合DQL以及DML语句使用，也可以在过程块中使用

语句只能在过程块中使用

3）loop循环

                loop    

                    循环语句

                end loop；

通常loop循环需要结合 if条件一起使用

                  loop    

                      if    条件    then

                            exit；

                      end if;

                  end loop；

但是一般使用    exit    when    条件 ；退出循环        --满足条件退出循环

1. --实现打印此10次的循环
2. declare
3. i int:=1;
4. begin
5. loop
6. /*if i>10 then
7. --退出循环
8. exit;
9. end if;*/
10. exitwhen i>10;        
11. dbms_output.put_line('我是第'||i||'次循环');
12. i:=i+1;
13. end loop;
14. end;

4）while    loop循环

            while    表达式

            loop

                    循环语句;

            end    loop;

1. --while循环
2. declare
3. i int:=1;
4. begin
5. while i<=10
6. loop
7. dbms_output.put_line('我是第'||i||'次循环');
8. i:=i+1;
9. end loop;
10. end;

5）for    循环

        for    变量    in    起始数值 .. 结束数值

        loop

                循环语句;

        end loop;

for循环中的变量在for语句中已经初始化并自动递增1

1. --for循环
2. declare
3. i int:=1;
4. begin
5. for i in1..10
6. loop
7. dbms_output.put_line('我是第'||i||'次循环');
8. --换行
9. dbms_output.new_line();
10. end loop;
11. end;

dbms_output.new_line();表示换行