设为首页 收藏本站
查看: 693|回复: 0

[经验分享] oracle瞎记-常用语法

[复制链接]

尚未签到

发表于 2016-7-17 09:02:58 | 显示全部楼层 |阅读模式
IF condition THEN
   Statements;
[ELSEIF condition THEN
Statements;]
[ELSE
Statements;]
END IF;
CASE selector
   WHEN expr1 THEN statements1;
   …
   WHEN exprN THEN statementsN;
   [ELSE statements;]
END CASE; CASE deptno
   WHEN 10 THEN
       dbms_output.put_line(‘10部门’);
   WHEN 20 THEN
       dbms_output.put_line(‘20部门’);
   ELSE
       dbms_output.put_line(‘部门不存在’);
END CASE;
CASE
   WHEN expr1 THEN statements1;
   …
   WHEN exprN THEN statementsN;
   [ELSE statements;]
END CASE; CASE
   WHEN sales<1000 THEN
       dbms_output.put_line(‘销售小于1000’);
   WHEN sales <2000 THEN
       dbms_output.put_line(‘1000<sales<2000’);
   ELSE
       dbms_output.put_line(‘销售大于1000’);
END CASE;
LOOP
     Statements;
     …
     EXIT [WHEN condition];
END LOOP; i int := 1;
LOOP
dbms_output.put_line(‘循环中’);
EXIT WHEN i = 10;
i := i + 1;
END LOOP;
WHILE condition LOOP
     Statements;
     …
END LOOP; i int := 1;
WHILE i<=10 LOOP
     dbms_output.put_line(‘循环中’);
i := i + 1;
END LOOP;
FOR counter in [REVERSE] lower..upper LOOP
Statements;

END LOOP; FOR i IN REVERSE 1..100 LOOP
dbms_output.put_line(‘循环中’);
END LOOP;
counter是循环控制变量,由Oracle隐含定义,不需要显示定义;lower和upper分别是循环的下界值和上界值。默认情况下,FOR循环每次循环时,循环控制变量会自动增一;如果指定REVERSE选项,则每次循环时,控制变量会自动减一.

自定义记录类型和记录变量 TYPE type_name IS RECORD
(
   field_declaration
   [,field_declaration]
   …
);
identifier type_name; type_name:自定义记录类型的名称。
identifier:用于指定记录变量名
DECLARE
TYPE emp_record_type IS RECORD
( name emp.name%TYPE,
   sal   emp.sal%TYPE,
   dno  emp.deptno%TYPE
);
emp_record emp_record_type;

使用%ROWTYPE属性直接定义记录变量 Identifier table_name%ROWTYPE;

Identifier view_name%ROWTYPE; 使用%ROWTYPE属性定义记录变量时,记录成员个数,名称,类型与表或视图列的个数,名称,类型完全相同
SELECT中使用范例 SELECT name, sal, deptno INTO emp_record FROM emp WHERE empno = &no;
SELECT name, sal INTO emp_record.name, record.sal FROM emp WHERE empno = &no;

游标类型 描述 语法 范例
显式游标 专门用于处理SELECT语句返回的多行数据
使用语法一时,必须要使用循环语句处理结果集的所有数据 CURSOR cursor_name IS select_statement;
OPEN cursor_name;
语法一:FETCH cursor_name INTO var1, var2…
语法二:FETCH cursor_name BULK COLLENT INTO collect1, collect2…[LIMIT rows];
CLOSE cursor_name; DECLARE
   CURSOR emp_cursor IS
       SELECT name, sal FROM emp WHERE deptno=10;
   v_name emp.name%TYPE;
   v_sal   emp.sal%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
     FETCH emp_cursor INTO v_name,v_sal;
     EXIT WHEN emp_cursor%NOTFOUND;
     dbms_output.put_line(v_name||’ ’||v_sal);
   END LOOP;
CLOSE emp_cursor;
END;
隐式游标 用于处理SELECT INTO和DML语句


使用SQL(显式游标)游标属性
属性 描述
SQL%ISOPEN 用于确定SQL游标是否打开
SQL%FOUND 用于确定SQL语句是否成功。
注意:SQL语句执行是否成功是根据是否有作用行来判断,当SQL语句有作用行时,其属性值为TRUE;当SQL语句没有作用行时,其属性值为FALSE
SQL%NOTFOUND 用于确定SQL语句是否成功。与SQL%FOUND相反
SQL%ROWCOUNT 用于返回SQL语句所作用的总计行数---(是指操作了的行数)

1. SQL语言分类
一. 数据查询语言(SELECT)
二. 数据操作语言(DML:Date Manipulation Language) : INSERT, UPDATE, DELETE
三. 事物控制语言(TCL:Transactional Control Language) COMMIT, ROLLBACK, SAVEPOINT(设置保存点,以便取消部分数据库改变)
四. 数据定义语言(DDL:Date Definition Language) CREATE TABLE, DROP TABLE, ALTER TABLE...
(DDL语言自动提交,无法rollback)
五. 数据控制语言(DCL:Date Control Language) GRANT(赋予权限), REVOKE(回收权限)           
(DCL语言自动提交,无法rollback)

2. 在PL/SQL块中嵌入SQL语句
SELECT select_list INTO {variable_name[,variable_name]…| record_name} FROM table WHERE condition;
INSERT INTO <table> [(column[,column…])] VALUES (value[,value,…]);
INSERT INTO <table> [(column[,column…])] subQuery;
UPDATE <table|view> SET <column> = <value>[,<column> = <value>] [WHERE <condition>];
例: UPDATE emp SET (sal,comm) = (SELECT sal, comm. FROM emp WHERE name=’Jason’)
   WHERE job=(SELECT job FROM emp WHERE name = ‘Jason’);
DELETE FROM <table|view> [WHERE <condition>]

IN  : 匹配子查询结果的任意一个值即可
ANY : 只要符合子查询结果的任意一个值即可
ALL : 必须要符合子查询结果的所有值
注意: ANY,ALL不能单独使用,必须与( =,>,<,>=,<=,<> )结合使用  
SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30)

多列子查询
SELECT deptno, name, job, sal FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE name = 'JASON')
多列子查询存在成对比较和非成对比较
成对比较  : SELECT ename, sal, comm, deptno FROM emp WHERE (sal,comm) in (SELECT sal,comm FROM emp WHERE deptno=30)
非成对比较: SELECT ename, sal, comm, deptno FROM emp WHERE sal  IN (SELECT sal FROM emp WHERE deptno=30) AND comm IN (SELECT comm FROM emp WHERE deptno = 30)

EXISTS : 如果子查询存在返回结果,则条件为true; 如果子查询没有返回结果,则条件为false
SELECT name, job, sal, deptno FROM emp WHERE EXISTS (SELECT 1 FROM dept WHERE dept.deptno = emp.deptno AND dept.loc = 'SHENZHEN')

在DML语句中使用子查询
(1)在INSERT语句中使用子查询 : INSERT INTO employee(id,name,title,sal) SELECT empno,name,job,sal FROM emp
(2)在UPDATE语句中使用子查询 : UPDATE emp SET(sal,comm) = (SELECT sal,comm FROM emp WHERE name='JASON') WHERE job=(SELECT job FROM emp WHERE name = 'JASON')
(3)在DELETE语句中使用子查询 : DELETE FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE NAME='JASON')

在DDL语句中使用子查询
(1)在CREATE TABLE语句中使用子查询 : 建立新表,并复制数据
            CREATE TABLE new_emp(id,name,sal,job,deptno) AS SELECT empno,ename,sal,job,deptno FROM emp;
(2)在CREATE VIEW语句中使用子查询  : 建立视图时,必须指定视图所对应的子查询语句
                                    CREATE OR REPLACE VIEW new_view AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno;
(3)在CREATE MATERIALIZED VIEW语句中使用子查询 : 建立实体化视图时,必须要指定实体化视图所对应的SQL语句,并且该SQL语句将来可用于查询重写
                                                CREATE MATERIALIZED VIEW summary_emp AS SELECT deptno,job,avg(sal) avgsal, sum(sal) sumsal FROM emp GROUP BU cube(deptno,job);

合并查询:(语法: SELECT语句1 [UNION | UNION ALL | INTERSECT | MINUS] SELECT语句2)
必须确保不同查询的列个数和数据类型都要匹配
UNION     : 取两个结果集的并集,会自动去掉重复行
UNION ALL : 取两个结果集的并集,不会自动去掉重复行
INTERSECT : 取两个结果集的交集,并已第一列进行排序
MINUS     : 取两个结果集的差集,在第一个结果集中存在,在第二个结果集中不存在的数据,并已第一列排序

使用CASE表达式: SELECT name,sal, (CASE WHEN sal>3000 THEN 3 WHEN sal>2000 THEN 2 ELSE 1 END) AS type FROM emp WHERE deptno=10
使用WITH子句重用子查询: (显示部门工资总和高于员工工资总和三分之一的部门及工资总和)
WITH summary AS (SELECT name, SUM(sal) AS dept_total FROM emp, dept WHERE emp.deptno=dept.deptno GROUP BY name)
SELECT name,dept_total FROM summary WHERE dept_total > (SELECT SUM(dept_total) * 1/3 FROM summary);

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-245153-1-1.html 上篇帖子: 监控 oracle 连接 下篇帖子: oracle临时恢复备份
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表