- 论坛徽章:
- 0
|
sqlplus相关:
启用\禁止一个触发器:alter trigger trigger_name enable\disable;
设置oracle output的输出
set serveroutput on ; //执行dbms_output.put_line()就可以在提示行下看到输出
set heading off/on 显示数据文件的表头
set pagesize number 设置数据文件的大小
set linesize number 在数据文件中设置行的大小
set trimspool on/off 去掉输入的尾部空格
set feedback on/off 显示'已选择x行。'
set echo on/off 回显语句行
spool out.dat 输出查询结果到out.dat文件中
spool off 关闭输出的数据文件
set verify/ver on/off 显示老行和新行
set autotrace dml语句成功执行报告
define_editor ='editor_name' 设置编辑器
得到触发器,过程,函数的脚本
desc user_source
user_triggers
打印host variables : print host Variables;
-----------------------------------------------------------------------------------------
SQL相关
decode : 在sql中实现if-then逻辑;
语法:decode (column_name,comparison,action,comparison,action,....,else
aciton)
nvl(expr1,expr2 )
If expr1 is null, NVL returns expr2;
nvl(expr1,expr2,expr3)
If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns
expr3.
集合并:union
集合交:intersect
集合差:minus
------------------------------------------------------------------------------------------
一般的相等连接:
select * from a, b where a.id = b.id;
这个就属于内连接。
对于外连接:
Oracle中可以使用“(+) ”来表示,9i可以使用LEFT/RIGHT/FULL OUTER JOIN
LEFT OUTER JOIN:左外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+)
结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录。
RIGHT OUTER JOIN:右外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id
结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。
FULL OUTER JOIN:全外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录和没有
任何员工的部门记录。
------------------------------------------------------------------------------------------
sql技巧:
如何搜索字段中的‘%‘?
select * from yourtab where yourcol like '%\%%' ESCAPE '\';
-----------------------------------------------------------------------------------------
table相关
伪列(Pseudocolumn):currval,level,nextval,rowid,rownum;
CURRVAL and NEXTVAL用在创建的序列(sequence)
中
查询关键字:all,any,some,between,exists,in,is null,like;
-----------------------------------------------------------------------------------------
trigger相关
语法:
create or replace trigger Trigger_name
before/after (insert/update/delete) of col on tabName
[for each row]
declare
pragma autonomous_transaction;
begin
end;
在触发器中数据的编写update事用到自治事务(Autonomous Transactions),否则不能读取该表!
在declare段开始此事务,并且在触发器结束之前,需要提交(commit)对数据库的修改。
------------------------------------------------------------------------------------------
exception相关:
语法:
begin
select ..into variable from tabName where..
exception
when [no_data_found/too_many_rows/dup_val_on_index
/value_error/ROWTYPE_MISMATCH/others ]
then
begin
...;
end;
end;
raise no_data_found;抛出一个异常
自定义的异常:
------------------------------------------------------------------------------------------
transactions相关:
可以使用(提交)commit,(会滚)roolback ,(保存点)savepoint和set transactions去控制一个事务。
SET TRANSACTION (read only/write only) :设置事务读写访问和隔离级的属性。
savepoint :在一个正处理的事务中标志当前点。
FOR UPDATE (NOWAIT) :获得一个排他锁(是否等待);
-------------------------------------------------------------------------------------------
Cursor(游标)相关:
类型:显式(explicit)和隐式(implicit) ;
关键字:open,fetch,close;
内部变量:%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT
声明:
CURSOR cursor_name [(parameter[, parameter]...)]
[RETURN return_type] IS select_statement;
example:
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
nums NumTab;
names NameTab;
CURSOR c1 IS SELECT empno, ename FROM emp WHERE job = 'CLERK';
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO nums, names;
...
CLOSE c1;
END;
游标variables
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp; -- declare cursor variable
使用for update和current of语句
for update :在多个表的查询中,锁定一个表的一行,(for update of col_name)
current of : use the CURRENT OF clause in an UPDATE or DELETE statement to
refer to the latest row fetched from a cursor.
-------------------------------------------------------------------------------------
procedure和function相关:
创建语法:
1. CREATE [OR REPLACE] PROCEDURE procedure_name
[ ( argument[{IN | OUT | IN OUT}] type,
...
argument[{IN | OUT | IN OUT}] type) ] {IS | AS}
procedure_body
2. CREATE [OR REPLACE] FUNCTION function_name
[( argument[{IN | OUT | IN OUT}] type,
...
argument[{IN | OUT | IN OUT}] type)]
RETURN return_type{IS | AS}
function_body
3.nocopy:
为procedure和function的编译选项,指明为引用传递,无为按值传递,
对参数I N使用N O C O P Y将会产生编译错误,这是因为参数I N总是按引用传递。
4.可以按位置,或者按按名称来传递参数。
--------------------------------------------------------------------------------------------
--
lock相关
1.lock table |
|