用一个实例讲解Oracle数据库的PL/SQL语句块（共10篇）由网友“听八卦的小耳朵”投稿提供，以下是小编精心整理的用一个实例讲解Oracle数据库的PL/SQL语句块，供大家参考借鉴，希望可以帮助到有需要的朋友。
篇1：用一个实例讲解Oracle数据库的PL/SQL语句块
PL/SQL程序块以显示所给出雇员编号的雇员的详细信息
1 SQL>Declare
2 v_empno emp.empno%Type; /*声明变量v_empno,%type:使该变量的类型与emp表中的empno类型相同*/
3 v_emprecord emp%Rowtype;/*声明变量v_emprecord,%rowtype:使该变量的类型与emp表中的整行相同*/
4 Begin
5 Select * Into v_emprecord From emp Where empno=&v_empno;
6 dbms_output.put_line(雇员编号||v_emprecord.empno);
7 dbms_output.put_line(雇员姓名||v_emprecord.ename);
8 dbms_output.put_line(入职日期||v_emprecord.hiredate);
9 dbms_output.put_line(职位||v_emprecord.job);
10 dbms_output.put_line(管理员编号||v_emprecord.mgr);
11 dbms_output.put_line(工资||v_emprecord.sal);
12 dbms_output.put_line(奖金||v_emprecord.comm);
13 dbms_output.put_line(部门编号||v_emprecord.deptno);
14 End;
if条件控制语句
语法：
1 if condition then statement;
2 elsif condition then statement;
3 else statement;
4 end if;
5/*condition 是一个布尔变量（值为true，false 或null，当表达式为true是执行then）；statement是PL/SQL 或SQL 语句；*/
通过if-then-elsif，按下列加薪比执行：（加薪的百分比是以他们现有的薪水为根据的）
Deptno Raise(%age)
10 5%
20 10%
30 15%
40 20%
1 SQL>Declare
2 v_empno emp.empno%Type;
3 v_emprecord emp%Rowtype;
4 v_sal emp.sal%Type;
5 Begin
6 Select * Into v_emprecord From emp Where empno=&v_empno;
7 If v_emprecord.empno=10 Then v_sal:=v_emprecord.sal*1.05;
8 Elsif v_emprecord.empno=20 Then v_sal:=v_emprecord.sal*1.10;
9 Elsif v_emprecord.empno=30 Then v_sal:=v_emprecord.sal*1.15;
10 Elsif v_emprecord.empno=40 Then v_sal:=v_emprecord.sal*1.20;
11 End If;
12 End;
for loop循环控制语句语法：
1for counter in [reverse] lower_bower..upper_bound loop
2statement1;
3statement2;
4
5end loop;
6/*counter 是一个隐式声明的整数，每次循环一次其自增1或自减1； reverse 将counter 从最大值每次减1至最小值； lower_bound 指定counter 变量变化范围的下届 upper_bound 指定counter 变量变化范围的上届*/
利用for loop 循环控制语句实现向“emp”表添加10个新雇员编号;
1 SQL>Declare
2 v_empno emp.empno%Type;
3 Begin
4 Select Max(empno) Into v_empno From emp;
5 For i In 1..10 Loop
6 v_empno:=v_empno+1;
7 Insert Into emp (empno) Values (v_empno);
8 End Loop;
9 End;
篇2：Oracle SQL精妙SQL语句讲解
--行列转换 行转列
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;
SELECT * FROM t_change_lc;
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;
--行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;
SELECT * FROM t_change_cl;
SELECT t.card_code,
t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a.*, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;
--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;
SELECT * FROM t_change_lc_comma;
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
SELECT * FROM t_change_cl_comma;
SELECT t.card_code,
substr(t.q,
instr(';' || t.q, ';', 1, rn),
instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr(';' || a.q, ';', 1, rn) >0) t
ORDER BY 1, 2;
-- 实现一条记录根据条件多表插入
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;
DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10));
DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10));
DROP TABLE t_ia_dest_3;
CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10));
SELECT * FROM t_ia_src;
SELECT * FROM t_ia_dest_1;
SELECT * FROM t_ia_dest_2;
SELECT * FROM t_ia_dest_3;
INSERT ALL
WHEN (c1 IN ('a1','a3')) THEN
INTO t_ia_dest_1(flag,c) VALUES(flag1,c2)
WHEN (c1 IN ('a2','a4')) THEN
INTO t_ia_dest_2(flag,c) VALUES(flag2,c2)
ELSE
INTO t_ia_dest_3(flag,c) VALUES(flag1||flag2,c1||c2)
SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;
-- 如果存在就更新，不存在就插入用一个语句实现
DROP TABLE t_mg;
CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));
SELECT * FROM t_mg;
MERGE INTO t_mg a
USING (SELECT 'the code' code, 'the name' NAME FROM dual) b
ON (a.code = b.code)
WHEN MATCHED THEN
UPDATE SET a.NAME = b.NAME
WHEN NOT MATCHED THEN
INSERT (code, NAME) VALUES (b.code, b.NAME);
-- 抽取/删除重复记录
DROP TABLE t_dup;
CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=10;
INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=2;
SELECT * FROM t_dup;
SELECT * FROM t_dup a WHERE a.ROWID (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);
SELECT b.code, b.NAME
FROM (SELECT a.code,
a.NAME,
row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn
FROM t_dup a) b
WHERE b.rn >1;
-- IN/EXISTS的不同适用环境
-- t_orders.customer_id有索引
SELECT a.*
FROM t_employees a
WHERE a.employee_id IN
(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);
SELECT a.*
FROM t_employees a
WHERE EXISTS (SELECT 1
FROM t_orders b
WHERE b.customer_id = 12
AND a.employee_id = b.sales_rep_id);
-- t_employees.department_id有索引
SELECT a.*
FROM t_employees a
WHERE a.department_id = 10
AND EXISTS
(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);
SELECT a.*
FROM t_employees a
WHERE a.department_id = 10
AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);
-- FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn, dbms_random.STRING('z',10) NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual
CONNECT BY ROWNUM <=10;
CREATE INDEX idx_nonfbi ON t_fbi(dt);
DROP INDEX idx_fbi_1;
CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));
SELECT * FROM t_fbi WHERE trunc(dt) = to_date('-09-21','yyyy-mm-dd') ;
-- 不建议使用
SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';
-- LOOP中的COMMIT/ROLLBACK
DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;
SELECT * FROM t_loop;
-- 逐行提交
DECLARE
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
COMMIT;
END LOOP;
END;
-- 模拟批量提交blog.knowsky.com/
DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
v_count := v_count + 1;
IF v_count >= 100 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
-- 真正的批量提交
DECLARE
CURSOR cur IS
SELECT * FROM user_objects;
TYPE rec IS TABLE OF user_objects%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 100;
-- forall 实现批量
FORALL i IN 1 .. recs.COUNT
INSERT INTO t_loop VALUES recs (i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
-- 悲观锁定/乐观锁定
DROP TABLE t_lock PURGE;
CREATE TABLE t_lock AS SELECT 1 ID FROM dual;
SELECT * FROM t_lock;
-- 常见的实现逻辑，隐含bug
DECLARE
v_cnt NUMBER;
BEGIN
-- 这里有并发性的bug
SELECT MAX(ID) INTO v_cnt FROM t_lock;
-- here for other operation
v_cnt := v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT;
END;
-- 高并发环境下，安全的实现逻辑
DECLARE
v_cnt NUMBER;
BEGIN
-- 对指定的行取得lock
SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;
-- 在有lock的情况下继续下面的操作
SELECT MAX(ID) INTO v_cnt FROM t_lock;
-- here for other operation
v_cnt := v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT; --提交并且释放lock
END;
-- 硬解析/软解析
DROP TABLE t_hard PURGE;
CREATE TABLE t_hard (ID INT);
SELECT * FROM t_hard;
DECLARE
sql_1 VARCHAR2(200);
BEGIN
-- hard parse
-- java中的同等语句是 Statement.execute()
FOR i IN 1 .. 1000 LOOP
sql_1 := 'insert into t_hard(id) values(' || i || ')';
EXECUTE IMMEDIATE sql_1;
END LOOP;
COMMIT;
-- soft parse
--java中的同等语句是 PreparedStatement.execute()
sql_1 := 'insert into t_hard(id) values(:id)';
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE sql_1
USING i;
END LOOP;
COMMIT;
END;
-- 正确的分页算法
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500)
WHERE rn >480 ;
-- 分页算法(why not this one)
SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500 AND ROWNUM >480;
-- 分页算法(why not this one)
SELECT b.*
FROM (SELECT a.*, ROWNUM rn
FROM t_employees a
WHERE ROWNUM < = 500
ORDER BY first_name) b
WHERE b.rn >480;
-- OLAP
-- 小计合计
SELECT CASE
WHEN a.deptno IS NULL THEN
'合计'
WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
'小计'
ELSE
'' || a.deptno
END deptno,
a.empno,
a.ename,
SUM(a.sal) total_sal
FROM scott.emp a
GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());
-- 分组排序
SELECT a.deptno,
a.empno,
a.ename,
a.sal,
-- 可跳跃的rank
rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,
-- 密集型rank
dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
-- 不分组排序
rank() over(ORDER BY sal DESC) r3
FROM scott.emp a
ORDER BY a.deptno,a.sal DESC;
-- 当前行数据和前/后n行的数据比较
SELECT a.empno,
a.ename,
a.sal,
-- 上面一行
lag(a.sal) over(ORDER BY a.sal DESC) lag_1,
-- 下面三行
lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3
FROM scott.emp a
ORDER BY a.sal DESC;
篇3：Oracle SQL精妙SQL语句讲解
教程贴士：--行列转换 行转列DROP TABLE t_change_lc;CREATE TABLE t_change_lc (card_code VARCHAR2
--行列转换 行转列
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;
SELECT * FROM t_change_lc;
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;
--行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;
SELECT * FROM t_change_cl;
SELECT t.card_code,
t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a.*, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;
--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;
SELECT * FROM t_change_lc_comma;
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
SELECT * FROM t_change_cl_comma;
SELECT t.card_code,
substr(t.q,
instr(';' || t.q, ';', 1, rn),
instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr(';' || a.q, ';', 1, rn) >0) t
ORDER BY 1, 2;
-- 实现一条记录根据条件多表插入
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;
DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10));
DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10));
DROP TABLE t_ia_dest_3;
CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10));
SELECT * FROM t_ia_src;
SELECT * FROM t_ia_dest_1;
SELECT * FROM t_ia_dest_2;
SELECT * FROM t_ia_dest_3;
INSERT ALL
WHE
篇4：菜鸟学oracle：用PL/SQL画直方图
现在用的库里有一个MV是统计按小时的访问量的,数据如下代码:
SQL>select * from mv_time_stat;
TIME CNT
----------
0 187
1 51
2 34
3 19
4 19
5 20
6 50
7 107
8 682
9 1342
10 1854
11 1292
12 1416
13 1180
14 1217
15 1573
16 1785
17 1469
18 1892
19 1907
20 1602
21 1540
22 1013
23 441
24 rows selected.
--------------------------------------------------------------------------------
某年月日,突发奇想,打算在sqlplus下面按上面的数据画个直方图出来,首先写了个竖列的(因为好写):
代码:--------------------------------------------------------------------------------
SQL>COL Time FORMAT A7
SQL>SELECT LPAD(DECODE(TIME,TO_CHAR(SYSDATE,'HH24'),'NOW: '||TO_CHAR(TIME),TO_CHAR(TIME)),7,' ') AS “Time”,
2 SUBSTR('|'||LPAD('>'||CNT,CEIL(CNT/A.TOTAL*300)+LENGTH(TO_CHAR(CNT))+1,'-'),1,35) AS “ Count per hour”
3 FROM MV_TIME_STAT,(SELECT SUM(CNT) AS TOTAL FROM MV_TIME_STAT) A;
COL Time CLEAR
Time Count per hour
------- -----------------------------------------------------------------
0 |-->187
1 |>51
2 |>34
3 |>19
4 |>19
5 |>20
6 |>50
7 |->107
NOW: 8 |--------->682
9 |----------------->1342
10 |------------------------>1854
11 |----------------->1292
12 |------------------>1416
13 |--------------->1180
14 |---------------->1217
15 |-------------------->1573
16 |----------------------->1785
17 |------------------->1469
18 |------------------------->1892
19 |------------------------->1907
20 |--------------------->1602
21 |-------------------->1540
22 |------------->1013
23 |----->441
24 rows selected.--------------------------------------------------------------------------------
然后又想,怎么样把它横过来,到网上查资料,又突击学习了下分析函数,写了一个过程是这个样子的:
代码:
--------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE HISTOGRAPH(HEIGHT NUMBER DEFAULT 15) AS
MAX_ONE NUMBER;
STR_LINE VARCHAR(120);
STR_TEMP VARCHAR(120);
I NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(LPAD('^',14,' '));
SELECT MAX(CNT) INTO MAX_ONE FROM MV_TIME_STAT;
FOR I IN 1 .. HEIGHT+1 LOOP
STR_LINE:=LPAD(TO_CHAR(MAX_ONE-(I-1)*MAX_ONE/HEIGHT,'99999')||'-'||TO_CHAR(MAX_ONE-(I-2)*MAX_ONE/HEIGHT,'99999')||'|',14,'0');
SELECT MAX(SYS_CONNECT_BY_PATH(
DECODE(SIGN(CNT-MAX_ONE+(I-1)*MAX_ONE/HEIGHT),-1,
DECODE(SIGN(CNT-MAX_ONE+(I-2)*MAX_ONE/HEIGHT),-1,'^','*'),
'|'),
' ')) INTO STR_TEMP
FROM MV_TIME_STAT
START WITH TIME=0
CONNECT BY PRIOR TIME=TIME-1;
STR_LINE:=STR_LINE||STR_TEMP;
DBMS_OUTPUT.PUT_LINE(REPLACE (STR_LINE,'^',' '));
END LOOP;
SELECT MAX(SYS_CONNECT_BY_PATH(TO_CHAR(TIME,'00'),'^')) INTO STR_TEMP
FROM MV_TIME_STAT
START WITH TIME=0
CONNECT BY PRIOR TIME=TIME-1;
DBMS_OUTPUT.PUT_LINE(LPAD('0',14,' ')||LPAD('>',LENGTH(REPLACE(STR_TEMP,' '))+3,'-'));
DBMS_OUTPUT.PUT_LINE(REPLACE(LPAD('^',14,' ')||REPLACE(STR_TEMP,' '),'^',' '));
END;
/
Procedure created.
SQL>exec histograph;
^
1907- 2034| |
1780- 1907| | | | |
1653- 1780| | | | |
1526- 1653| | | | | | | |
1398- 1526| | | | | | | | | |
1271- 1398| | | | | | | | | | | |
1144- 1271| | | | | | | | | | | | | |
1017- 1144| | | | | | | | | | | | | |
890- 1017| | | | | | | | | | | | | | |
763- 890| | | | | | | | | | | | | | |
636- 763| | | | | | | | | | | | | | | |
509- 636| | | | | | | | | | | | | | | |
381- 509| | | | | | | | | | | | | | | | |
254- 381| | | | | | | | | | | | | | | | |
127- 254| | | | | | | | | | | | | | | | | |
0- 127| | | | | | | | | | | | | | | | | | | | | | | | |
0-------------------------------------------------------------------------->
00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
PL/SQL procedure successfully completed.
篇5：有关Oracle 数据库连接查询SQL语句
内连接（inner join）。
外连接：
全连接（full join）、左连接（left join）、右连接（right join）。
交叉联接(cross join)。
外连接与内连接不一样，外连接返回的查询结果中不仅包含符合条件的行，还包括左表(左外连接),右表(右外连接)或者两个连接表(全外连接)中的所有不符合条件的数据行。
1.左连接 (left [outer] join)
左外连接就是将左表的所有数据分别于右表的每条数据进行连接组合，返回的结果除内连接的数据外，还有左表中不符合条件的数据，并在右表的相应列中填上null值。
SQL语句如下：
select * from mt_pb_org o left join mt_pb_orgframe f on o.PB_ORGFRAMEID = f.PB_ORGFRAMEID;
等价语句：
select * from mt_pb_org o,mt_pb_orgframe f where o.pb_orgframeid = f.pb_orgframeid(+);
2.右连接 (right [outer] join)
右外连接就是将右表中的所有数据分别与左表的每条数据进行连接组合，返回的结果除了内连接的数据外，还有右表中不符合条件的数据，并在左表相应的列中填上null值。
SQL语句如下：
select * from mt_pb_org o right join mt_pb_orgframe on o.pb_orgframeid = f.pb_orgframeid;
等价语句：
select * from mt_pb_org o,mt_pb_orgframe f where o.pb_orgframeid(+) = f.pb_orgframeid;
3.全外连接 (full [outer] join)
全外连接就是将左表的所有数据分别与右表的每条数据进行连接组合，返回的结果除了内连接的数据外，还有两个表中不符合条件的数据，并在左表或者右表的相应列中填上null值。
SQL语句如下：
select * from mt_pb_org o full join mt_pb_orgframe o.pb_orgframeid = f.pb_orgframeid;
4.交叉连接(cross join)
交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
SQL语句如下：
select * from mt_pb_org o cross join mt_pb_orgframe f;
篇6：用pl/sql安装oracle explain plan(执行计划)
1、用pl/sql以管理员身份登录数据库
2、运行脚本文件utlxplan，路径为%oracle_home%/rdms/admin,例如D:\Oracle\rdbms\admin
注意下面要选择所有文件类型
然后运行，
运行代码： sql>create public synonym plan_table for plan_table;
sql>grant all on plan_table to public ;
接下来按照上面的方法运行第二个脚本plustrace.sql，,路径是%oracle_home%/sqlplus/admin
然后执行 SQL>grant plustrace to public;
即完成了安装过程
autotrace功能只能在SQL*PLUS里使用,因此需要打开sql*plus
sql>set autotrace on
例：
sql>select count(*) from test;
count(*)
-------------
4
Execution plan
----------------------------
0 select statement ptimitzer=choose (cost=3 card=1)
1 0 sort(aggregate)
2 1 partition range(all)
3 2 table access (full) of 't_test' (cost=3 card=900)
摘自 wudiisss的专栏
篇7：检查Oracle数据库中不合理的sql语句数据库教程
oracle|数据|数据库|语句
代码:select sql_text ,sharable_mem from v$sql where sharable_mem >'100000' order by sharable_mem ;
上面的sql语句是查询shared pool中占用内存超过100K的sql语句，
这个sql可以非常有效的检查出Oracle shared pool中那些严重占用内存的sql，根据我的经验，绝大多数有问题的sql语句都会在这里留下痕迹，通过在这里找出有问题的sql语句并进行修改，再反复运行这个sql脚本，直到所以有问题的sql都处理完毕，这就是对Oracle数据库在sql上面的最好的优化，可以保证不会因为程序员的sql语句问题导致Oracle数据库的性能问题。
共享池主要由库缓冲区（共享SQL区和PL/SQL区）和数据字典缓冲区组成。详细的情况你随便找一本介绍Oracle内存结构的书上面都有讲到。我就不把书上的内容给你打印一遍了，自己去看。
select * from v$sgastat; --显式SGA的状态信息。
有的人写的SQL语句非常复杂，嵌套了好几层，SQL语句本身写的很差，就有可能会占用很大的SQL区域。
其实现实的很多情况是本来不需要那么复杂的sql的时候，由于程序员水平不够，写了那种很糟糕很复杂的sql，造成的数据库性能问题。
另外还有一个常识问题，却很容易被忽略。比如：
代码:
select * from table_name where id = 1;
select * from table_name where id = 2;
对于这种带参数的sql，id = ? 这个地方叫做站位符(Placeholder)。
拿PHP为例，很多人喜欢这样写代码
代码:
$sql = “select * from table_name where id = ”;
$id=1;
$stmt = ociparse($conn,$sql.$id);
ociexecute($stmt);
......
$id = 2;
$stmt = ociparse($conn,$sql.$id);
ociexecute($stmt);
拿Java为例，是这样的：
代码:
String sql = “select * from table_name where id = ”;
Statement stmt = conn.createStatement;
rset = stmt.executeQuery(sql+“1”);
......
rset = stmt.executeQuery(sql+“2”);
这种写法，对于Oracle数据库来说，完全就是两条不同的sql语句，
代码:
select * from table_name where id = 1;
select * from table_name where id = 2;
每次查询都要进行sql语句的执行解析，并且每个sql都会分配一个区域来存放sql解析后的二进制可执行代码，
试想，要是id不同的10万个sql呢？Oracle就会分配10万个sql区域来分别存放10万个这样的id不同的sql语句。对于一个数据库驱动的Web网站这样情况下，SGA开的再大，也会很快被耗尽share pool的，最后报一个ORA-4031错误。数据库就连接不上了，只好重起。
正确的写法应该是：
代码:
$stmt = ociparse($conn,“select * from table_name where id = :id”);
ocibindbyname($stmt,“:id”,&$id, 12);
$id =1;
ociexecute($stmt);
...
$id = 2;
ociexecute($stmt);
代码:
PreparedStatement pstmt = conn.prepareStatement(“select * from table_name where id = ?”);
pstmt.setInt(1,1);
rset = pstmt.executeQuery();
...
pstmt.setInt(1,2);
rset = pstmt.executeQuery();
这样Oracle数据库就知道你实际上用的都是同一条sql语句，会以这样的形式：
select * from table_name where id = :1
解析执行后存放在sql区域里面，当以后再有一样的sql的时候，把参数替换一下，就立刻执行，不需要再解析sql了。既加快了sql执行速度，也不会占有过多SGA的share pool。
可惜的是，很多程序员明知道这个问题，却意识不到问题的严重性，因为上面那种写法，编程的时候很灵活，sql语句可以动态构造，实现起来很容易，后面那种写法，sql语句是写死的，参数不能再变了，编程经常会非常麻烦。
很多数据库的性能问题都是这样造成的。
有兴趣在一个生产系统中，用上面sql检查一下，看看是否选择出来的是否l有很多都是一样的sql语句，只是参数不同，如果是这样的话，就说明程序员的代码写的有问题。
篇8：DedeCms用SQL语句调用数据库任意内容方法
织梦DedeCms给我们提供了大量调用标签，供我们调用各种数据，但提供再多的标签，也有满足不了我们的时候，这时我们可以用SQL语句，灵活调用我们需要的内容，
如何任意调用数据库中的内容呢？先举个例子：大家都知道Dede后台有个频道模型里面有添加单页文档的功能，但如何在首页调用单页文档的内容呢，现在给大家讲下如何使用：
1.首页在后台单页文档管理里添加一个单页文档，内容编辑框输入你要的内容生成。
2.在需要调用单页文档的地方加入SQL万用标签{dede:sql sql=“select body from dede_sgpage where aid=2”}[field:body /]{/dede:sql} 相信熟悉SQL语言的朋友很容易看懂这句含义。意思其实就是选择数据表dede_sgpage 的body字段输入，aid=2这里的2表示你创建单页文档ID数字，你使用时做下相应修改即可。
又如这句SQL标签样例代码
1
2
3
{dede:sql sql=“select content from dede_arctype where id=2”}
[field:contentfunction=cn_substr(Html2Text(@me),600)/]
{/dede:sql}
根据上面的例子，应该很好理解了，这里主要说明一下cn_substr表示截取600个字符也就是300个汉字，
Html2Text代表把含有HTML的字符过滤掉，只显示文本。
其实除了上面的例子外，在Dede系统里面，我可以可以自由使用SQL语句来配合织梦标签进行更多的个性化调用。他们的基本参照格式为：
1
2
3
{dede:sql sql=“select 字段 From 表名称 where 条件语句”}
[field:字段名/]
{/dede:sql}
由上面这种基本格式，我们就可以基本转换出一条调用文档列表的调用标签了，代码为：
1
2
3
{dede:sql sql=“select * From dede_archives limit 10”}
·[field:title/]
{/dede:sql}
大家看到了上面的文档链接的底层模板
篇9：一个用来统计相同姓名人数的SQl语句数据库教程
sql语句查询
表结构是这样：
ID 姓名 性别
1 张三 男
2 王四 男
3 丽丽 女
4 张三 男
5 赵柳 男
6 高洁 男
7 王四 女
8 高洁 女
9 张三 女
怎么能用一条SQL语句查询出如下的结果
姓名 人员个数 男人数 女人数 包含ID
张三 3 2 1 1，4，9
王四 2 1 1 2，7
丽丽 1 0 1 3
高洁 2 1 1 6，8
赵柳 1 1 0 5
复制代码代码如下:
SELECT * FROM (SELECT DISTINCT Name,Count(ID) as 人T,
sum(case when Sex=“男” then 1 else 0 end) as 男人,
sum(case when Sex=“女” then 1 else 0 end) as 女人
FROM Test group by Name) A
OUTER APPLY(
SELECT
[包含ID]= STUFF(REPLACE(REPLACE(
(
SELECT ID FROM Test N
WHERE Name = A.Name
FOR XML AUTO
), “
)N
篇10：一个sql语句，包含有几乎所有标准查询语法数据库教程
标准|语法|语句
W_KMR01_11 ---- KMM13BR COPYWRITE BY LD 2000/09/07 15:30
SELECT DISTINCT SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) AS BGT_TYPE,
( CASE SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ) WHEN “09” THEN “1” WHEN “10” THEN “2”
WHEN “11” THEN “3” ELSE “9” END ) AS BGT_CLASS,
SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 ) AS BGT_TEAM,
( ISNULL( (SELECT SUBSTRING(KMC101.KMC101_NAME,CHARINDEX(“-”,KMC101.KMC101_NAME)+1,12) FROM KMC101
WHERE ( KMC101.KMC101_TYPE = “BUDGETCODE” )
AND ( KMC101.KMC101_CODE = SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) + “00” ) ),“” ) ) AS BGT_NAME,
SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) AS BGT_AMT01,
SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) AS BGT_AMT02,
SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) AS BGT_AMT03,
SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) AS BGT_AMT04,
SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) AS BGT_AMT05,
SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) AS BGT_AMT06,
SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) AS BGT_AMT07,
SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) AS BGT_AMT08,
SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) AS BGT_AMT09,
SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) AS BGT_AMT10,
SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) AS BGT_AMT11,
SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) AS BGT_AMT12,
( SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) +
SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) +
SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) +
SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) +
SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) +
SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) ) AS AMT01,
( “XXXXXXXX” ) AS AMT02,
SUM( ISNULL( ( CASE WHEN ( CONVERT(CHAR(8),KMM102.KMM102_CHK_DATE,112) < “YYYYMMDD” )
THEN KMM102.KMM102_CHK_AMT ELSE 0 END ),0 ) ) AS AMT09,
SUM( ISNULL( ( CASE WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) < “YYYYMMDD” )
AND KMM102.KMM102_OVER_STATUS “00” )
THEN KMM102.KMM102_OVER_AMT
WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) < “YYYYMMDD” )
AND KMM102.KMM102_OVER_STATUS = “00” )
THEN KMM102.KMM102_OVER_AMT ELSE 0 END ),0 ) ) AS AMT07,
SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < “YYYYMMDD” )
AND KMM103.KMM103_PAY_STATUS “2” )
THEN KMM103.KMM103_PAY_AMT ELSE 0 END
FROM KMM103
WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY )
AND ( KMM103.KMM103_BGT_KIND = KMM102.KMM102_BGT_KIND )
AND ( KMM103.KMM103_CASE_NO = KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT03,
SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < “YYYYMMDD” )
AND KMM103.KMM103_PAY_STATUS “1” )
THEN KMM103.KMM103_PAY_AMT ELSE 0 END
FROM KMM103
WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY )
AND ( KMM103.KMM103_BGT_KIND = KMM102.KMM102_BGT_KIND )
AND ( KMM103.KMM103_CASE_NO = KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT04
FROM KMM102,
KMM101
WHERE ( KMM102.KMM102_YY = “YYY” ) AND
( KMM102.KMM102_BGT_KIND = “1” ) AND
( KMM101.KMM101_YY =* KMM102.KMM102_YY ) AND
( KMM101.KMM101_BGT_KIND =* KMM102.KMM102_BGT_KIND ) AND
( KMM101.KMM101_BGT_TYPE =* KMM102.KMM102_BGT_TYPE )
GROUP BY SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ),
SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ),
SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 )
ORDER BY BGT_CLASS ASC,
BGT_TYPE DESC
;
★ 数据库面试题
★ oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)
【用一个实例讲解Oracle数据库的PL/SQL语句块（共10篇）】相关文章：
java软件工程师工作职责2022-10-09
oracle列目录的可行性测试2022-08-06
从零开始学Oracle―约束2022-04-30
java的实习报告2023-07-31
Java软件工程师转正申请书2023-08-27
java培训实习报告2023-06-16
java工程师简历2024-04-27
数据库工程师的工作职责2022-11-11
数据库工程师工作的职责2023-03-16
跟踪标记全攻略数据库教程2022-09-26