plsql
文章平均质量分 51
aoerqileng
这个作者很懒,什么都没留下…
展开
-
pl/sql学习
pl/sql是运行在服务器上的,可以增强维护性和安全性,也减少了网翻译 2014-09-22 10:08:57 · 566 阅读 · 0 评论 -
plsql优化
from tom bad plsql FOR increc IN (SELECT * FROM big_table ORDER BY many columns) LOOP . . . much procedural code goes here. . . . UPDATE big_table SET … WHERE primary_key = inr转载 2016-01-13 18:39:35 · 370 阅读 · 0 评论 -
oracle中日期函数使用
获取日期中的年,月,日 SQL> select EXTRACT (DAY FROM SYSDATE) from dual; EXTRACT(DAYFROMSYSDATE) ----------------------- 18 SQL> select extract (year from sysdate) from dual;原创 2016-01-18 19:06:19 · 461 阅读 · 0 评论 -
oracle11g行列转换
列转行 select * from t; 1 1 a 2 2 b 3 3 c 4 3 d select id,listagg(name,',') within group(order by name) from t group by id; 1 1 a 2 2 b 3 3 c,d 行转列 with a as (select '原创 2015-09-18 16:49:50 · 922 阅读 · 0 评论 -
oracle常用函数
nvl 语法:NVL( string1, replace_with) 作用:如果string1是null,则返回replace_wit原创 2014-10-23 10:09:11 · 472 阅读 · 0 评论 -
plsql中遇到异常后返回值
--oracle sql疑难解析 create or replace function isnum(v_in varchar2) return varchar is val_err exception; pragma exception_init(val_err,-6502); scrub_num number; begin scrub_num:=to_number(v_in);原创 2015-06-15 19:45:15 · 859 阅读 · 0 评论 -
oracle connect by start with
select * from tb_menu m start with m.id=2 connect by prior m.parent= m.id; 找2的父节点, select * from tb_menu m connect by prior m.id= m.parent start with m.id=2; 找2的子节点原创 2015-04-02 15:06:12 · 568 阅读 · 0 评论 -
group by cube
SQL> select * from test; ID FIRST_NAME LAST_NAME ---------- ---------- ---------- 1 a b 2 a b 3 c d 4 e f原创 2015-06-23 14:50:27 · 605 阅读 · 0 评论 -
显示指定null值得位置
SQL> select * from t4 order by name; NAME NAME2 ---------- ----------------------------------------------- bai xiao test test test1 test1 aa SQL> sele原创 2015-06-07 11:33:39 · 608 阅读 · 0 评论 -
plsql中使用绑定变量
摘自《基于oracle的sql优化》 实例: declare vc_name varchar2(10); begin execute immediate 'select ename from emp where empno = :1' into v_name using 1234; dbms_output.put_line(v_name); end; / declare v_s原创 2015-05-30 10:21:06 · 2273 阅读 · 0 评论 -
动态sql例子
select * from dept; select * from emp; declare sql_stmt varchar2(200); type id_table is table of integer; type name_tables is table of varchar2(8); t_empno id_table:=id_table(9001,9002,9原创 2015-12-08 19:53:21 · 1874 阅读 · 0 评论 -
plsql中游标使用
打开游标后的循环 declare i_total integer:=1000; cursor emp_cur is select * from emp order by salary asc; i_emp emp%rowtype; begin open emp_cur; loop fetch emp_cur into i_emp;原创 2016-03-03 14:59:24 · 3907 阅读 · 0 评论 -
游标使用2-常用属性及引用游标
隐式游标 四个常用的属性 Sql%FOUND SQL%NOTFOUND SQL%ISOPEN SQL%ROWCOUNTDeclare Dept_no_number(4) :=50; Begin Delete from dept_temp where deptno=dept_no; If sql%found then Insert into dept_temp values(50原创 2016-06-19 09:59:05 · 1955 阅读 · 2 评论 -
oracle replace与translate的区别
下面是在官网上摘抄下来的 translate只能是单个字符一对一的替换,replace是字符串的替换,并能删除 REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitut转载 2015-12-08 16:59:46 · 650 阅读 · 0 评论 -
分析函数RATIO_TO_REPORT 的使用
该分析函数是计算记录在记录集中的比率,记录集的定义是写在over中,如果没有写,就是指的全部的结果集,下面的例子是计算job是pu_clerk中每个员工的薪资占该工作总薪资的百分比 SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr FROM employees WHERE job_id = 'PU_CL原创 2015-12-08 16:06:24 · 1822 阅读 · 0 评论 -
编写动态sql
什么是动态sql 与静态sql翻译 2014-09-23 16:54:38 · 1277 阅读 · 0 评论 -
plsql中的循环
来自pl/sql开发入门 loop与exit的示例 declare v_count number(2) := 0; begin loop v_count := v_count + 1; dbms_output.put_line('行' || v_count); if v_count = 10 then exit; end原创 2015-07-03 11:30:00 · 690 阅读 · 0 评论 -
plsql中的子类型
子类型就是在标准类型上加些限制生成的一种新类型。 declare subtype empcounttype is integer; empcount empcounttype; begin select count(*) into empcount from emp; dbms_output.put_line(empcount); end; 子类型定义示例 declare t原创 2015-07-03 11:11:42 · 538 阅读 · 0 评论 -
sum over的用法
SQL> select * from t; ID1 ID2 ---------- ---------- 1 2 2 3 3 6 4 7 SQL> select t1.id1,t2.id1,sum(t原创 2015-07-03 16:14:59 · 837 阅读 · 0 评论 -
plsql中的集合
集合有3种类型:1索引表2嵌套表3变长数组 语法:type type_name as table of element_type [not null] index by [pls_integer|binary_integer|varchar2(size)] type deptno is table of dept.deptno%type not null index by pls_intege原创 2015-07-03 15:21:57 · 547 阅读 · 0 评论 -
oracle coalesce函数
该函数返回表达式中的第一个非空表达式 select coalesce(subobject_name,object_type) from t_objects where object_name='ICOL$';原创 2015-07-05 11:10:11 · 643 阅读 · 0 评论 -
plsql中异常的传递
begin declare v_name varchar2(2) := 'ABC'; begin dbms_output.put_line(v_name); exception when others then dbms_output.put_line('xxx'); end; exception when others原创 2015-07-06 19:15:36 · 612 阅读 · 0 评论 -
plsql异常之exception_init
常用异常处理 declare e_duplicate_name exception; v_ename emp.ename%type; e_newname emp.ename%type := 'smith'; begin select ename into v_ename from emp where empno = 7639; if v_ename = v_n原创 2015-07-06 15:49:25 · 1253 阅读 · 0 评论 -
oracle分析函数使用
select o.deptno, o.job, sum(o.sal), rank() over(order by sum(o.sal) desc), dense_rank() over(order by sum(o.sal) desc), row_number() over(partition by o.deptno原创 2015-07-06 14:15:57 · 531 阅读 · 0 评论 -
oracle列相乘
SQL> select * from t; ID1 ID2 ---------- ---------- 1 2 2 3 3 6 4 7 SQL> select exp(sum(ln(id1))) f原创 2015-08-05 11:18:26 · 4315 阅读 · 0 评论 -
oracle标量子查询
SQL> select * from table( dbms_xplan.display_cursor( format=> 'allstats last' )); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------原创 2016-01-18 17:27:55 · 940 阅读 · 0 评论 -
pl/sql相关的视图
select * from user_arguments; select * from user_dependencies; select * from user_errors; select * from user_identifiers; select * from user_object_size; select * from USER_PLSQL_OBJECT_SETTINGS原创 2016-01-20 11:21:59 · 697 阅读 · 0 评论 -
plsql自治事务在异常中的使用
将记录日志单独存放成一个普通的存储过程 CREATE OR REPLACE PROCEDURE record_error IS l_code PLS_INTEGER := SQLCODE; l_mesg VARCHAR2(32767) := SQLERRM; BEGIN INSERT INTO error_log (error_code原创 2016-03-02 17:10:56 · 1167 阅读 · 0 评论