SQL语句入门-PL/pgSQL（PostgreSQL）

1. 基本语法和用法

这个我之所以提出来是因为，我们老师上课讲的，ppt里的都是Oracle的PL/SQL语句写法，完全无法在postgreSQL上使用，所以只好自己找找资料学习学习。

1.1 基本语法

这个，其实挺复杂的，我也没有自己做总结，不过为了方便大家，我转载了一篇博客，讲的很详细：
plpgsql基本语法

1.2 基本用法

直接给个例子吧：
先看看代码怎么写的

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$BEGINRETURN subtotal * 0.06;END;
$$ LANGUAGE plpgsql;

然后用的话，可以直接这样

SELECT sales_tax(1);

结果就是这样的

2. 实战训练

题目1

题目描述

创建一个能向学生表student中插入一条记录的存储过程insert_student，该过程需要5个参数，分别用来传递学号、姓名、性别、出生日期、班级号。
写出调用存储过程insert_student的SQL语句，向数据表student中插入一个新同学，并提供相应的实参值。

思路&想法

创建一个function，传入要求的这几个参数，类型直接饮用student表的类型，函数体写上INSERT语句即可

代码实现：

CREATE OR REPLACE FUNCTION insert_student(sno student.sno%type,sname student.sname%type,ssex student.ssex%type,sbirthday student.sbirthday%type,classno student.classno%type)
RETURNS varchar AS $$
BEGININSERT INTO student(sno,sname,ssex,sbirthday,classno)VALUES(sno,sname,ssex,sbirthday,classno);RETURN 'INSERT STUDENT SUCCESSFULLY';
END;
$$ LANGUAGE plpgsql;

应用截图

查看student表会发现已经插入了这条数据

题目2

题目描述

创建一个向课程表course中插入一门新课程的存储过程insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为4，即当调用存储过程insert_course时，未给第三个参数“学分”提供实参值时，存储过程将按默认值4进行运算。
调用存储过程insert_course，向课程表course中插入一门新课程。分两种情况（给出第三个参数和未给出第三个参数）写出相应的SQL命令，并比较结果。

思路&想法

此题和上题一样，只是多了一个默认值，默认值应该是这么写的：

variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];

代码实现

CREATE OR REPLACE FUNCTION insert_course(cno course.cno%type,cname course.cname%type,ccredit integer DEFAULT 4)
RETURNS varchar AS $$
BEGININSERT INTO courseVALUES(cno,cname,ccredit);RETURN 'INSERT COURSE SUCCESSFULLY';
END;
$$ LANGUAGE plpgsql;

应用截图

题目3

题目描述

创建一个向课程表course中插入一门新课程的存储过程insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为4，即当调用存储过程insert_course时，未给第三个参数“学分”提供实参值时，存储过程将按默认值4进行运算。
调用存储过程insert_course，向课程表course中插入一门新课程。分两种情况（给出第三个参数和未给出第三个参数）写出相应的SQL命令，并比较结果。

思路&想法

直接查询即可，把结果RAISE NOTICE 或者return到结果字符串里都可以

代码实现

CREATE OR REPLACE FUNCTION query_student(num student.sno%type)
RETURNS void AS $$
DECLAREname student.sname%type;sex student.ssex%type;birthday student.sbirthday%type;classnumber student.classno%type;
BEGINSELECT sname,ssex,sbirthday,classnoINTO name,sex,birthday,classnumberFROM studentWHERE sno=num;RAISE NOTICE 'NAME IS %, SEX IS %, BIRTHDAY IS %, CLASSNO IS %', name, sex, birthday, classnumber;
END;
$$ LANGUAGE plpgsql;

应用截图

题目4

题目描述

建立存储过程，输出平均成绩大于80分的学生的姓名、性别、年龄和平均成绩。
调用该存储过程，并输出相应的结果

思路&想法

这里需要用到cursor，来把SELECT 出来的每一条结果都进行一次操作，这里操作就是输出就好了，其他的没什么难的

代码实现

CREATE OR REPLACE FUNCTION output_avg()
RETURNS void AS $$
DECLAREname student.sname%type;sex student.ssex%type;thisAge varchar(10);avggrade integer;cursor CURSOR FOR SELECT sname,ssex, (cast(to_char(current_date,'YYYY') as integer)-cast(to_char(sbirthday,'YYYY') as integer)) ,avg(grade)FROM student,scWHERE student.sno=sc.snoGROUP BY student.sname,student.ssex,student.sbirthdayHAVING avg(grade)>80;
BEGINOPEN cursor;LOOPFETCH cursor INTO name,sex,thisAge,avggrade;EXIT WHEN NOT FOUND;RAISE NOTICE 'NAME IS %, SEX IS %, AGE IS %, AVERAGE GRADE IS %', name,sex,thisAge,avggrade;END LOOP;
END;
$$ LANGUAGE plpgsql;

应用截图