请创建学生信息表、成绩信息表、课程信息表、班级信息表，要求：
1.1学生信息表需要包含学号（主键），学生姓名（非空），性别（非空，且只能填写“男或女”），班级编号（外键，关联班级信息表）
1.2 成绩信息表需要包含成绩编号（主键），学号（外键，关联学生信息表），课程编号（外键，关联课程信息表），成绩得分（非空，不能填写负数），成绩录入日期（默认值当前日期）
1.3 课程信息表需要包含课程编号（主键），课程名称（非空），课程类型（非空，且只能填写“必修或选修”）
1.4 班级信息表需要包含班级编号（主键），班级名称（非空）
CREATE TABLE student (
student_id VARCHAR(20) NOT NULL PRIMARY KEY,
student_name VARCHAR(20) NOT NULL,
gender VARCHAR2(2) CHECK( gender =' 男 ' OR gender =' 女 '),
class_id VARCHAR(20) NOT NULL,
FOREIGN KEY (class_id) REFERENCES class(class_id)
);
CREATE TABLE score(
score_id VARCHAR(20) NOT NULL PRIMARY KEY,
student_id VARCHAR(20) NOT NULL,
course_id VARCHAR(20) NOT NULL,
score DECIMAL(5,2) NOT NULL,
CONSTRAINT chk_score CHECK (score >= 0),
entry_date DATE DEFAULT CURRENT_DATE,
FOREIGN KEY(student_id) REFERENCES student(student_id),
FOREIGN KEY(course_id) REFERENCES course(course_id)
);
CREATE TABLE course(
course_id VARCHAR(20) NOT NULL PRIMARY KEY,
course_name VARCHAR(20) NOT NULL,
course_type VARCHAR(10) NOT NULL,
CONSTRAINT chk_course_type CHECK (course_type IN ('必修', '选修'))
);
CREATE TABLE class(
class_id VARCHAR(20) NOT NULL PRIMARY KEY,
class_name VARCHAR(20) NOT NULL
);
------------------------------------------------数据库架构-----------------------------------------------------------------
PL/SQL程序的基本单元是块(BLOCK)，块就是实现一定功能的逻辑模块。一个PL/SQL程序由一个或多个块组成。块有固定的结构，也可以嵌套。一个块可以包括三个部分，每个部分由一个关键字标识。块中各部分的作用解释如下：
(1) DECLARE：声明部分标志。
(2) BEGIN：可执行部分标志。
(3) EXCEPTION：异常处理部分标志。
(4) END；：程序结束标志。
将使用函数DBMS_OUTPUT.PUT_LINE显示输出结果。DBMS_OUTPUT是Oracle提供的包，该包有如下三个用于输出的函数，用于显示PL/SQL程序模块的输出信息。
第一种形式： DBMS_OUTPUT.PUT(字符串表达式)；
用于输出字符串，但不换行，括号中的参数是要输出的字符串表达式。
第二种形式： DBMS_OUTPUT.PUT_LINE(字符串表达式)；
用于输出一行字符串信息，并换行，括号中的参数是要输出的字符串表达式。
第三种形式： DBMS_OUTPUT.NEW_LINE； --put一定要换行，不然显示不出来
用来输出一个换行，没有参数。调用函数时，在包名后面用一个点.”和函数名分隔，表示隶属关系。
要使用该方法显示输出数据，在SQL*Plus环境下要先执行一次如下的环境设置命令：
SET SERVEROUTPUT ON [SIZE n]
用来打开DBMS_OUTPUT.PUT_LINE函数的屏幕输出功能，系统默认状态是OFF。其中，n表示输出缓冲区的大小。n的范围在2000～1 000000之间，默认为2000。如果输出内容较多，需要使用SIZE n来设置较大的输出缓冲区。
在PL/SQL模块中可以使用查询语句和数据操纵语句(即进行DML操作)，所以PL/SQL程序是同SQL语言紧密结合在一起的。在PL/SQL程序中，最常见的是使用SELECT语句从数据库中获取信息，同直接执行SELECT语句不同，在程序中的SELECT语句总是和INTO相配合，INTO后跟用于接收查询结果的变量，形式如下：
SELECT 列名1，列名2... INTO 变量1，变量2... FROM 表名 WHERE 条件；
注意：接收查询结果的变量类型、顺序和个数同SELECT语句的字段的类型、顺序和个数应该完全一致。并且SELECT语句返回的数据必须是一行，否则将引发系统错误。当程序要接收返回的多行结果时，可以采用后面介绍的游标的方法。
使用INSERT、DELETE和UPDATE的语法没有变化，但在程序中要注意判断语句执行的状态，并使用COMMIT或ROLLBACK进行事务处理。以下训练包含了按照标准结构书写的一个包含SELECT语句的PL/SQL程序示例。
【训练1】 查询雇员编号为7788的雇员姓名和工资。
DECLARE--定义部分标识
v_name VARCHAR2(10);--定义字符串变量v_name
v_sal NUMBER(5);--定义数值变量v_sal
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=7788;--在程序中插入的SQL语句
DBMS_OUTPUT.PUT_LINE('7788号雇员是： '||v_name||' ，工资为： '||to_char(v_sal));--输出雇员名和工资
END;--结束标识结果是： 7788号雇员是：SCOTT，工资为：4000
PL/SQL和SQL两者究竟有什么区别呢？SQL查询的方法，只限于SQL环境，并且输出的格式基本上是固定的。而程序通过把数据取到变量中，可以进行复杂的处理，完成SQL语句不能实现的功能，并通过多种方式输出。以分号结束
“--”是注释符号 /*......*/中间也是注释部分，同“--”注释方法不同，它可以跨越多行进行注
变量的基本数据类型同SQL部分的字段数据类型相一致，但是也有不同
VARCHAR2是可变长度的字符串，定义时指明最大长度，存储数据的长度是在最大长度的范围自动调节的，数据前后的空格，Oracle 9i会自动将其删去。
NUMBER型可以定义数值的总长度和小数位，如NUMBER(10,3)表示定义一个宽度为10、小数位为3的数值。整个宽度减去小数部分的宽度为整数部分的宽度，所以整数部分的宽度为7。
CHAR数据类型为固定长度的字符串，定义时要指明宽度，如不指明，默认宽度为1。定长字符串在显示输出时，有对齐的效果。
DATE类型用于存储日期数据，内部使用7个字节。其中包括年、月、日、小时、分钟和秒数。默认的格式为DD-MON-YY，如：07-8月-03表示2003年8月7日。
BOOLEAN为布尔型，用于存储逻辑值，可用于PL/SQL的控制结构。
LOB数据类型可以存储视频、音频或图片，支持随机访问，存储的数据可以位于数据库内或数据库外，具体有四种类型：BFILE、BLOB、CLOB、NCLOB。但是操纵大对象需要使用Oracle提供的DBMS_LOB包。
-----------------------------------------------------变量定义--------------------------------------------------
变量的作用是用来存储数据，可以在过程语句中使用。变量在声明部分可以进行初始化，即赋予初值。变量在定义的同时也可以将其说明成常量并赋予固定的值。变量的命名规则是：以字母开头，后跟其他的字符序列，字符序列中可以包含字母、数值、下划线等符号，最大长度为30个字符，不区分大小写。不能使用Oracle的保留字作为变量名。变量名不要和在程序中引用的字段名相重，如果相重，变量名会被当作列名来使用。
变量的作用范围是在定义此变量的程序范围内，如果程序包含子块，则变量在子块中也有效。但在子块中定义的变量，仅定义变量的子块中有效，在主程序中无效。
变量定义的方法是： 变量名 [CONSTANT] 类型标识符 [NOT NULL][:=值|DEFAULT 值];
关键字CONSTANT用来说明定义的变量是常量，如果是常量，必须有赋值部分进行赋值。
关键值NOT NULL用来说明变量不能为空。
∶=或DEFAULT用来为变量赋初值。
变量可以在程序中使用赋值语句重新赋值。通过输出语句可以查看变量的值。
变量名:=值 或 PL/SQL 表达式;
DECLARE--声明部分标识
v_job VARCHAR2(9);
v_count BINARY_INTEGER DEFAULT 0;
v_total_sal NUMBER(9,2) := 0;
v_date DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE;
BEGIN
v_job:='MANAGER';--在程序中赋值
DBMS_OUTPUT.PUT_LINE(v_job);--输出变量v_job的值
DBMS_OUTPUT.PUT_LINE(v_count);--输出变量v_count的值
DBMS_OUTPUT.PUT_LINE(v_date);--输出变量v_date的值
DBMS_OUTPUT.PUT_LINE(c_tax_rate);--输出变量c_tax_rate的值
END;执行结果：
0
11-8月 -23
8.25
本训练共定义了6个变量，分别用“:=”赋值运算符或DEFAULT 关键字对变量进行了初始化或赋值。其中：c_tax_rate为常量，在数据类型前加了 “CONSTANT” 关键字；v_valid变量在赋值运算符前面加了关键字“NOT NULL” ，强制不能为空。如果变量是布尔型，它的值只能是“TRUE” 、 “FALSE”或“NULL”。本练习中的变量v_valid布尔变量的值只能取“TRUE”或“FALSE”。
DECLARE
v_ename emp.ename%TYPE;--根据字段定义变量
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE empno = 7788;
DBMS_OUTPUT.PUT_LINE(v_ename);
END;结果为:SCOTT
DECLARE
g_ename VARCHAR2(100) := ''; -- 定义并初始化结合变量g_ename：
BEGIN
g_ename:= g_ename || 'Hello~ '; -- 在程序中使用结合变量
DBMS_OUTPUT.PUT_LINE(g_ename); -- 输出结合变量的值
END;/*VARIABLE g_ename VARCHAR2(100)
BEGIN
:g_ename:=:g_ename|| 'Hello~ '; --在程序中使用结合变量
DBMS_OUTPUT.PUT_LINE(:g_ename); --输出结合变量的值
END;*/
结果：Hello~
DECLARE
emp_record emp%ROWTYPE;--定义记录变量
BEGIN
SELECT * INTO emp_record
FROM emp
WHERE empno = 7788;--取出一条记录
DBMS_OUTPUT.PUT_LINE(emp_record.ename);--输出记录变量的某个字段
END;结果为： SCOTT
DECLARE
TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;--类型说明
v_t type_table;--定义TABLE变量
BEGIN
v_t(1):='MONDAY';
v_t(2):='TUESDAY';
v_t(3):='WEDNESDAY';
v_t(4):='THURSDAY';
v_t(5):='FRIDAY';
DBMS_OUTPUT.PUT_LINE(v_t(3));--输出变量的内容
END;j结果：星期三
DECLARE
V_temprature NUMBER(5):=32;
V_result BOOLEAN:=false;
BEGIN
V_result:= v_temprature >30;
IF V_result THEN
DBMS_OUTPUT.PUT_LINE('温度'|| V_temprature ||'度,偏高');
END IF;
END;执行结果为：
DECLARE
v_sex VARCHAR2(2);
v_titil VARCHAR2(10);
BEGIN
v_sex:='男';
IF v_sex ='男' THEN
v_titil:='先生';
ELSE
v_titil:='女士';
END IF;
DBMS_OUTPUT.PUT_LINE(v_titil||'您好');
END;
DECLARE
v_sal NUMBER(5);
v_tax NUMBER(5,2);
BEGIN
SELECT sal INTO v_sal
FROM emp
WHERE empno=7788;
IF v_sal >=3000 THEN
V_tax:= v_sal*0.08;--税率8%
ELSIF v_sal>=1500 THEN
V_tax:= v_sal*0.06; --税率6%
ELSE
V_tax:= v_sal*0.04; --税率4%
END IF;
DBMS_OUTPUT.PUT_LINE('应缴税金:'||V_tax);
END;DECLARE
v_job VARCHAR2(10);
BEGIN
SELECT job INTO v_job
FROM emp
WHERE empno=7788;
CASE v_job
WHEN 'PRESIDENT' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
WHEN 'MANAGER' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:经理');
WHEN 'SALESMAN' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:推销员');
WHEN 'ANALYST' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:系统分析员');
WHEN 'CLERK' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:职员');
ELSE
DBMS_OUTPUT.PUT_LINE('雇员职务:未知');
END CASE;
END;
结果：
DECLARE
v_grade VARCHAR2(10);
v_result VARCHAR2(10);
BEGIN
v_grade:='B';
v_result:=CASE v_grade
WHEN 'A' THEN '优'
WHEN 'B' THEN '良'
WHEN 'C' THEN '中'
WHEN 'D' THEN '差'
ELSE '未知'
END;
DBMS_OUTPUT.PUT_LINE('评价等级:'||V_result);
END;DECLARE
v_sal NUMBER(5);
BEGIN
SELECT sal INTO v_sal FROM emp
WHERE empno=7788;
CASE
WHEN v_sal>=3000 THEN
DBMS_OUTPUT.PUT_LINE('工资等级：高');
WHEN v_sal>=1500 THEN
DBMS_OUTPUT.PUT_LINE('工资等级：中');
ELSE
DBMS_OUTPUT.PUT_LINE('工资等级：低');
END CASE;
END;DECLARE
v_total NUMBER(5) := 0;
v_count NUMBER(5) := 1;
BEGIN
LOOP
v_total := v_total + v_count ** 2;
EXIT WHEN v_count = 15; -- 条件退出
v_count := v_count + 2;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_total);
END;BEGIN
FOR I IN 1..8
LOOP
DBMS_OUTPUT.PUT_LINE(to_char(i)||rpad('*' ,I, '*'));
END LOOP;
END;BEGIN
FOR I IN 1..9
LOOP
IF I=1 OR I=9 THEN
DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ' ,12-I, ' ')||rpad('*' ,2*i-1, '*'));
ELSE
DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ' ,12-I, ' ')||'*'||rpad(' ' ,I*2-3, ' ')||'*');
END IF;
END LOOP;
END;--1、插入数据
DECLARE
v_count NUMBER(2) := 1;
BEGIN
WHILE v_count <6 LOOP
INSERT INTO emp(empno, ename)
VALUES (5000+v_count, '临时');
v_count := v_count + 1;
END LOOP;
COMMIT;
END;
--2、显示插入的记录：
SELECT empno,ename FROM emp WHERE ename='临时';
--3、删除插入的记录：
DELETE FROM emp WHERE ename='临时';DECLARE
v_total NUMBER(8):=0;
v_ni NUMBER(8):=0;
J NUMBER(5);
BEGIN
FOR I IN 1..10
LOOP
J:=1;
v_ni:=1;
WHILE J<=I
LOOP
v_ni:= v_ni*J;
J:=J+1;
END LOOP;--内循环求n!
v_total:=v_total+v_ni;
END LOOP;--外循环求总和
DBMS_OUTPUT.PUT_LINE(v_total);
END;DECLARE
v_total
NUMBER(8):=0;
v_ni
NUMBER(8):=1;
BEGIN
FOR I IN 1..10
LOOP
v_ni:= v_ni*I;
--求n!
v_total:= v_total+v_ni;
END LOOP;
--循环求总和
DBMS_OUTPUT.PUT_LINE(v_total);
END;--1、插入
DECLARE
v_empno NUMBER(5):=7780;
v_num VARCHAR2(10);
i NUMBER(3):=0;
BEGIN
SELECT count(*) INTO v_num FROM emp WHERE empno=v_empno;--是否存在v_empno对应的记录
IF v_num=1 THEN
DBMS_OUTPUT.PUT_LINE('雇员'||v_empno||'已经存在！');
ELSE
INSERT INTO emp(empno,ename) VALUES(v_empno, 'TOM');
COMMIT;
DBMS_OUTPUT.PUT_LINE('成功插入新雇员！');
END IF;
END;
--2、查询记录
SELECT empno,ename FROM emp WHERE empno=7780;--SET SERVEROUTPUT ON SIZE 10000
--SET LINESIZE 100
--SET PAGESIZE 100
DECLARE
v_a NUMBER(8,3);
v_p NUMBER(8,3);
BEGIN
FOR I IN 1..18
LOOP
v_a:=I*20*3.14159/180;--生成角度，并转换为弧度
v_p:=SIN(v_a)*20+25;--求SIN函数值，20为放大倍数，25为水平位移
DBMS_OUTPUT.PUT_LINE(to_char(i)||lpad('*' ,v_p, ' '));--输出记录变量的某个字段
END LOOP;
END;
扫一扫
6672
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
