ORACLE PL/SQL问题解决
1.使用表emp,dept
2.写成一个可以用在SQL Plus中执行的Script
使用SQLPlus的column等定义
3.不准用UNION
4. Format
部门名称 人数 工资总额
A 2 1000
B 3 2000
sum 3000
2， 创建一个Table1
id 流水号码,primary key,以sequence产生
name 姓名,字符 30
address 地址,字符 120
Table2
id 以table1的 id 为外键
category 分类(A,B,C类), 字符 1
friend 朋友,字符 50
View 统计各个姓名各类朋友有多少.
禁止使用group by.
3，写一个函数:
输入参数为数字,如 123456789
输出结果为字符,如 一亿二千三百四十五万六千七百八十九
4，TABLE如下
日期 收入 支出
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10
能否用SELECT语句得出以下结果
1.
日期 收入 支出 余额
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/5 60 10 55
2.
TABLE加入一行
2000/3/5 60 10
结果
日期 收入 支出 余额
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/5 120 20 105
3.
日期 收入 支出 余额
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/3 0 0 5
2000/3/4 0 0 5
最新回答
送沵的獨白
2024-05-02 16:33:25
Q1:
COL DEPTNO FORM 999
COL DNAME FORM A15
SELECT NVL(A.DEPTNO,100) DEPTNO,NVL(B.DNAME,'SUM') DNAME,A.人数,A."SUM"
FROM
(select deptno,count(ename) 人数,SUM(SAL) "SUM" from emp group by rollup(deptno)) A
LEFT JOIN DEPT B
ON A.DEPTNO=B.DEPTNO
ORDER BY 1
;
DEPTNO DNAME 人数 SUM
------ --------------- ---------- ----------
10 ACCOUNTING 3 8750
20 RESEARCH 5 10875
30 SALES 6 9400
100 SUM 14 29025
Q2:
SQL> CREATE SEQUENCE SEQ_TAB1;
序列已创建。
SQL> CREATE OR REPLACE TRIGGER TRG_TAB1
2 BEFORE INSERT ON TABLE1
3 FOR EACH ROW
4 BEGIN
5 SELECT SEQ_TAB1.NEXTVAL INTO :NEW.ID FROM DUAL;
6 END;
7 /
触发器已创建
SQL> INSERT INTO TABLE1(NAME,ADDRESS) VALUES('AAA','ABC');
已创建 1 行。
SQL> COL NAME FORM A10
SQL> COL ADDRESS FORM A10
SQL> SELECT * FROM TABLE1;
ID NAME ADDRESS
---------- ---------- ----------
1 AAA ABC
SQL> CREATE TABLE TABLE2(ID NUMBER,
2 CATEGORY CHAR(1) CHECK(CATEGORY IN ('A','B','C')),
3 FRIEND VARCHAR2(50),
4 FOREIGN KEY(ID) REFERENCES TABLE1(ID));
表已创建。
SQL> INSERT INTO TABLE2 VALUES(2,'A','BB');
INSERT INTO TABLE2 VALUES(2,'A','BB')
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.SYS_C005786) - 未找到父项关键字
SQL> INSERT INTO TABLE2 VALUES(1,'A','BB');
已创建 1 行。
SQL> INSERT INTO TABLE2 VALUES(1,'D','CC');
INSERT INTO TABLE2 VALUES(1,'D','CC')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005785)
SQL> INSERT INTO TABLE2 VALUES(1,'B','CC');
已创建 1 行。
SQL> COL FRIEND FORM A10
SQL> SELECT * FROM TABLE2;
ID CA FRIEND
---------- -- ----------
1 A BB
1 B CC
SQL> CREATE VIEW V_FRIENDS AS
2 SELECT A.NAME,B.CATEGORY,B."COUNT"
3 FROM TABLE1 A
4 INNER JOIN (SELECT ID,CATEGORY,COUNT(DISTINCT FRIEND) "COUNT"
5 FROM TABLE2
6 GROUP BY ID,CATEGORY) B
7 ON A.ID=B.ID
8 /
视图已创建。
SQL> SELECT * FROM V_FRIENDS;
NAME CA COUNT
---------- -- ----------
AAA A 1
AAA B 1
Q3：可修改中文输出形式
CREATE OR REPLACE FUNCTION FUNC_CHN_NUM(V_NUM NUMBER)
RETURN VARCHAR2
IS
V_FLAG NUMBER:=0;
V_OUT VARCHAR2(10);
V_OUTSTR VARCHAR2(200);
V_LEN NUMBER;
BEGIN
SELECT LENGTH(V_NUM) INTO V_LEN FROM DUAL;
IF V_LEN>12
THEN RETURN '超出选值范围！';--到千亿
ELSE
WHILE V_FLAG<V_LEN
LOOP
-- SUBSTR(V_NUM,LENGTH(V_NUM)-V_FLAG,1) into v_out FROM DUAL;
SELECT DECODE(SUBSTR(V_NUM,LENGTH(V_NUM)-V_FLAG,1),0,'零',1,'壹',2,'贰',3,'叁',4,'肆',5,'伍',6,'陆',7,'柒',8,'捌',9,'玖') INTO V_OUT FROM DUAL;
IF MOD(V_FLAG,4)=1 THEN V_OUT:=V_OUT||'拾'; END IF;
IF MOD(V_FLAG,4)=2 THEN V_OUT:=V_OUT||'佰'; END IF;
IF MOD(V_FLAG,4)=3 THEN V_OUT:=V_OUT||'仟'; END IF;
IF V_FLAG=0 THEN V_OUT:=V_OUT||'个'; END IF;
IF V_FLAG=4 THEN V_OUT:=V_OUT||'万'; END IF;
IF V_FLAG=8 THEN V_OUT:=V_OUT||'亿'; END IF;
V_OUTSTR:=V_OUT||V_OUTSTR;
V_FLAG:=V_FLAG+1;
End LOOP;
V_OUTSTR:=REPLACE(V_OUTSTR,'零拾','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零佰','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零仟','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零零','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零零','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零万','万');
V_OUTSTR:=REPLACE(V_OUTSTR,'零亿','亿');
V_OUTSTR:=REPLACE(V_OUTSTR,'亿万','亿');
V_OUTSTR:=REPLACE(V_OUTSTR,'零零','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'壹拾','拾');
V_OUTSTR:=REPLACE(V_OUTSTR,'零个','个');
V_OUTSTR:=REPLACE(V_OUTSTR,'个','');
RETURN V_OUTSTR;
END IF;
END;
测试：
SQL> SELECT FUNC_CHN_NUM(03100000780) FROM DUAL;
FUNC_CHN_NUM(03100000780)
------------------------------------------------
叁拾壹亿零柒佰捌拾
SQL> SELECT FUNC_CHN_NUM(12345678910123) FROM DUAL;
FUNC_CHN_NUM(12345678910123)
---------------------------------------------------
超出选值范围！
SQL> SELECT FUNC_CHN_NUM(300000000008) FROM DUAL;
FUNC_CHN_NUM(300000000008)
-------------------------------------------------
叁仟亿零捌
SQL> SELECT FUNC_CHN_NUM(300007000008) FROM DUAL;
FUNC_CHN_NUM(300007000008)
-------------------------------------------------
叁仟亿零柒佰万零捌
SQL> SELECT FUNC_CHN_NUM(300007000018) FROM DUAL;
FUNC_CHN_NUM(300007000018)
-------------------------------------------------
叁仟亿零柒佰万零拾捌
SQL> SELECT FUNC_CHN_NUM(300007000010) FROM DUAL;
FUNC_CHN_NUM(300007000010)
-------------------------------------------------
叁仟亿零柒佰万零拾
SQL> SELECT FUNC_CHN_NUM(300007000100) FROM DUAL;
FUNC_CHN_NUM(300007000100)
-------------------------------------------------
叁仟亿零柒佰万零壹佰
SQL> SELECT FUNC_CHN_NUM(300007001000) FROM DUAL;
FUNC_CHN_NUM(300007001000)
-------------------------------------------------
叁仟亿零柒佰万壹仟
Q4:
SQL> CREATE TABLE TEST(日期 DATE, 收入 NUMBER(8),支出 NUMBER(8));
表已创建。
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/1','YYYY/MM/DD'),50,30);
已创建 1 行。
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/2','YYYY/MM/DD'),45,60);
已创建 1 行。
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/5','YYYY/MM/DD'),60,10);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT TO_CHAR(日期,'YYYY/MM/DD') 日期,收入,支出 FROM TEST;
日期 收入 支出
---------- ---------- ----------
2000/03/01 50 30
2000/03/02 45 60
2000/03/05 60 10
Q4-1:
SQL> SELECT A.日期,收入,支出,SUM(A.余额) OVER(ORDER BY A.日期) 余额
2 FROM
3 (SELECT TO_CHAR(日期,'YYYY/MM/DD') 日期,
4 SUM(收入) 收入,
5 SUM(支出) 支出,
6 SUM(收入)-SUM(支出) 余额
7 FROM TEST
8 GROUP BY TO_CHAR(日期,'YYYY/MM/DD')
9 ) A;
日期 收入 支出 余额
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/05 60 20 55
Q4-2:
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/5','YYYY/MM/DD'),60,10);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT A.日期,收入,支出,SUM(A.余额) OVER(ORDER BY A.日期) 余额
2 FROM
3 (SELECT TO_CHAR(日期,'YYYY/MM/DD') 日期,
4 SUM(收入) 收入,
5 SUM(支出) 支出,
6 SUM(收入)-SUM(支出) 余额
7 FROM TEST
8 GROUP BY TO_CHAR(日期,'YYYY/MM/DD')
9 ) A;
日期 收入 支出 余额
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/05 120 20 105
Q4-3:
SQL> SELECT
2 TO_CHAR(B.日期,'YYYY/MM/DD') 日期,
3 NVL(A.收入,0) 收入,
4 NVL(A.支出,0) 支出,
5 SUM(A.余额) OVER(ORDER BY B.日期) 余额
6 FROM
7 (
8 SELECT 日期,
9 SUM(收入) 收入,
10 SUM(支出) 支出,
11 SUM(收入)-SUM(支出) 余额
12 FROM TEST
13 GROUP BY 日期
14 ) A
15 RIGHT JOIN
16 (
17 SELECT (SELECT MIN(日期) FROM TEST)+ROWNUM-1 日期
18 FROM DUAL
19 CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期) FROM TEST)
20 ) B
21 ON A.日期=B.日期;
日期 收入 支出 余额
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/03 0 0 5
2000/03/04 0 0 5
--如果要看5号的余额，修改CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期) FROM TEST)成CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期)+1 FROM TEST)
SQL> SELECT
2 TO_CHAR(B.日期,'YYYY/MM/DD') 日期,
3 NVL(A.收入,0) 收入,
4 NVL(A.支出,0) 支出,
5 SUM(A.余额) OVER(ORDER BY B.日期) 余额
6 FROM
7 (
8 SELECT 日期,
9 SUM(收入) 收入,
10 SUM(支出) 支出,
11 SUM(收入)-SUM(支出) 余额
12 FROM TEST
13 GROUP BY 日期
14 ) A
15 RIGHT JOIN
16 (
17 SELECT (SELECT MIN(日期) FROM TEST)+ROWNUM-1 日期
18 FROM DUAL
19 CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期)+1 FROM TEST)
20 ) B
21 ON A.日期=B.日期;
日期 收入 支出 余额
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/03 0 0 5
2000/03/04 0 0 5
2000/03/05 120 20 105
热门标签
