PL/SQL课堂练习(匿名块)

1、普通变量声明
1）、声明3个变量:full_name varchar2(20),salary number(9,2),fdate date
2）、用Select into语句从employees表为上面3个变量赋值，条件是rownum <= &_rownum
3）、用DBMS_OUTPUT.PUT_LINE()打印三个变量值，结果如：Mickel.Jordon 's salary is $4680, Joined in 1987-07-12
4）、另外声明常量PI，NUMBER(2,7)=3.1415926，声明变量flag BOOLEAN，默认值=FALSE，声明变量age NUMBER，无默认值
5）、打印上述变量

DECLARE
full_name VARCHAR2(20);
salary NUMBER(9,2);
fdate DATE;
pi CONSTANT NUMBER(8,7) := 3.1415926;
flag BOOLEAN := FALSE;
age NUMBER;
BEGIN
SELECT first_name||'.'||last_name, salary, hire_date
INTO full_name, salary, fdate
FROM employees
WHERE ROWNUM <= &_rownum;
DBMS_OUTPUT.PUT_LINE(full_name||' ''s salary IS $'||salary||', Joined in '||to_char(fdate,'yyyy-MM-dd'));
DBMS_OUTPUT.PUT_LINE('pi:'||pi);
IF flag THEN
DBMS_OUTPUT.PUT_LINE('true');
ELSE
DBMS_OUTPUT.PUT_LINE('false');
END IF;
DBMS_OUTPUT.PUT_LINE('age:'||age);
END;

数据库对象练习

【HR/HR】

1、用学生选课场景创建表：
1.1
Student（id,sno,sname,telnum,email）
Course（id,cno,cname,teachername）
studentcourse(id,s_id,c_id)

CREATE TABLE student(
id NUMBER,
sno VARCHAR2 (15) NOT NULL,
sname VARCHAR2 (20) NOT NULL,
telnum VARCHAR2 (12),
email VARCHAR2 (50)
);
CREATE TABLE course(
id NUMBER,
cno VARCHAR2 (6) NOT NULL,
cname VARCHAR2 (30) NOT NULL,
teachername VARCHAR2(20)
);
CREATE TABLE studentcourse(
id NUMBER,
s_id NUMBER,
c_id NUMBER
);

SQL练习

【SCOTT/TIGER】

7. 用Decode函数实现横表和竖表的转换

SELECT deptno DEPARTMENT_ID,
COUNT(*) "部门人数",
SUM(DECODE(SIGN(sal-9000),-1,0,1)) "超高收入人数（>9000）",
SUM(DECODE(SIGN(sal-9000),-1,0,( DECODE(SIGN(sal-12000),1,0,1) ) )) "极高收入人数（[9000,12000]）",
SUM(DECODE(SIGN(sal-6000),-1,0,( DECODE(SIGN(sal-9000),1,0,1) ) )) "高收入（[6000,9000]）",
SUM(DECODE(SIGN(sal-3000),-1,0,( DECODE(SIGN(sal-6000),1,0,1) ) )) "一般收入（[3000,6000]）",
SUM(DECODE(SIGN(sal-3000),-1,1,0)) "低收入（[<3000]）"
FROM emp
GROUP BY deptno;