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
from tombad plsql FOR increc IN (SELECT * FROM big_table ORDER BY many columns) LOOP . . . much procedural code goes here. . . . UPDATE big_tableSET … WHERE primary_key = inr
什么是动态sql与静态sql
pl/sql是运行在服务器上的，可以增强维护性和安全性，也减少了网
SQL> select * from test; ID FIRST_NAME LAST_NAME---------- ---------- ---------- 1 a b 2 a b 3 c d 4 e f
--oracle sql疑难解析create or replace function isnum(v_in varchar2) return varchar isval_err exception;pragma exception_init(val_err,-6502);scrub_num number;begin scrub_num:=to_number(v_in);
来自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
子类型就是在标准类型上加些限制生成的一种新类型。declare subtype empcounttype is integer;empcount empcounttype;beginselect count(*) into empcount from emp;dbms_output.put_line(empcount);end;子类型定义示例declaret
SQL> select * from t; ID1 ID2---------- ---------- 1 2 2 3 3 6 4 7SQL> select t1.id1,t2.id1,sum(t
集合有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
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
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
下面是在官网上摘抄下来的translate只能是单个字符一对一的替换，replace是字符串的替换，并能删除REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitut
常用异常处理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
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
该函数返回表达式中的第一个非空表达式select coalesce(subobject_name,object_type) from t_objects where object_name='ICOL$';
SQL> select * from t; ID1 ID2---------- ---------- 1 2 2 3 3 6 4 7SQL> select exp(sum(ln(id1))) f
列转行select * from t;1 1 a2 2 b3 3 c4 3 dselect id,listagg(name,',') within group(order by name) from t group by id;1 1 a2 2 b3 3 c,d行转列with a as (select '
该分析函数是计算记录在记录集中的比率，记录集的定义是写在over中，如果没有写，就是指的全部的结果集，下面的例子是计算job是pu_clerk中每个员工的薪资占该工作总薪资的百分比SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr FROM employees WHERE job_id = 'PU_CL
获取日期中的年，月，日SQL> select EXTRACT (DAY FROM SYSDATE) from dual; EXTRACT(DAYFROMSYSDATE)----------------------- 18 SQL> select extract (year from sysdate) from dual;
SQL> select * from table( dbms_xplan.display_cursor( format=> 'allstats last' ));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------
nvl语法：NVL( string1, replace_with)作用：如果string1是null，则返回replace_wit
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的子节点
摘自《基于oracle的sql优化》实例：declarevc_name varchar2(10);beginexecute immediate 'select ename from emp where empno = :1' into v_name using 1234;dbms_output.put_line(v_name);end;/declarev_s
SQL> select * from t4 order by name;NAME NAME2---------- -----------------------------------------------bai xiaotest testtest1 test1 aaSQL> sele
将记录日志单独存放成一个普通的存储过程CREATE OR REPLACE PROCEDURE record_errorIS l_code PLS_INTEGER := SQLCODE; l_mesg VARCHAR2(32767) := SQLERRM;BEGIN INSERT INTO error_log (error_code
打开游标后的循环 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;
隐式游标 四个常用的属性 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
Copyright © 2005-2023 51CTO.COM 版权所有 京ICP证060544号
