How can one see if somebody modified any code?
The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated. If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are updated.
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code.
SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');
If you run the above query from SQL*Plus, enter the string you are searching for when prompted for KEYWORD. If not, replace &KEYWORD with the string you are searching for.
What is the difference between%TYPE and%ROWTYPE?
Both%TYPE and%ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code
changes.
The%TYPE and%ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%TYPE
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
%ROWTYPE
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Examples:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/
How does one loop through tables in PL/SQL?
One can make use of cursors to loop through data within tables. Look at the following
nested loops code example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is '||emp_rec.ename);
END LOOP;
END LOOP;
END;
/
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions,
causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
Issuing frequent commits is bad, bad, BAD! It’s the WORST thing you can do… just don’t do it! In the following example I will create around 7 million rows and then attempt to update a portion of them serially. In addition, I will issue a commit every thousandth
row.
Example 1.1: Creating a somewhat large table
SQL> create table big_employee_table
2 as
3 select rownum as eid
4 , e.*
5 from hr.employees e
6 , dba_objects do;
Table created.
Elapsed: 00:00:12.23
SQL> select count(*)
2 from big_employee_table;
COUNT(*)
----------
7838713
Elapsed: 00:00:08.11
Before I go on, notice that Oracle’s “Create Table As” (CTAS) method blazed thru table creation. That’s 7.84 Million rows in 12.23 seconds. Sometimes, this is the very best method of updating large data sets. The following block updates 100,000 rows, serially,
committing every 1000 rows:
Example 1.2: Updating serially
SQL> declare
2 cursor c is
3 select *
4 from big_employee_table
5 where rownum <= 100000;
6 begin
7 for r in c loop
8 update big_employee_table
9 set salary = salary * 1.03
10 where eid = r.eid;
11
12 if mod ( r.eid, 1000 ) = 0 then
13 commit;
14 end if;
15 end loop;
16 end;
17 /
Observe that the update took more time than I have patience for;). At 20 minutes I killed the session. It is painfully slow and should never be done. Moreover, it chewed up an entire CPU core for the duration. If you’re only updating a few rows, why do
it in PL/SQL at all? I like Tom Kyte’s approach (paraphrasing):
1. Do it in SQL.
2. If SQL can’t do it, do it in PL/SQL.
3. If PL/SQL can’t do it, do it in Java.
4. If Java can’t do it ask yourself if it needs to be done.
The following block does the same work in bulk:
Example 1.3: Updating in bulk and committing at the end
SQL> declare
2 type obj_rowid is table of rowid
3 index by pls_integer;
4
5 lr_rowid obj_rowid;
6 lr_salary dbms_sql.number_table;
7
8 cursor c is
9 select rowid rid
10 , salary
11 from big_employee_table
12 where rownum <= 100000;
13 begin
14 open c;
15 loop
16 fetch c bulk collect
17 into lr_rowid
18 , lr_salary
19 limit 500;
20
21 for a in 1 .. lr_rowid.count loop
22 lr_salary ( a ) := lr_salary ( a ) * 1.03;
23 end loop;
24
25 forall b in 1 .. lr_rowid.count
26 update big_employee_table
27 set salary = lr_salary ( b )
28 where rowid in ( lr_rowid ( b ));
29
30 exit when c%notfound;
31 end loop;
32 close c;
33 commit; -- there! not in the loop
34 exception
35 when others then
36 rollback;
37 dbms_output.put_line ( sqlerrm );
38 end;
39 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.11
SQL>
Notice that the update completed in 2 seconds! I’ve seen faster but my two-gerbil sandbox machine doesn’t have the power that our newer servers do. The point is that the update was incredibly fast and chewed up only 10% of one core. So, in answer to the
question of “how often should I commit?” I say don’t until you absolutely have to
分享到:
相关推荐
最新pl/sql7.0中文手册
pl/sql例题代码pl/sql例题代码pl/sql例题代码
pl/sql developer11.0下载 pl/sql developer11.0下载 pl/sql developer11.0下载
《Oracle PL/SQL程序设计(第5版)(套装上下册)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何...
PL/SQL 程序设计 本章主要重点: PL/SQL概述 PL/SQL块结构 PL/SQL流程 运算符和表达式 游标 异常处理 数据库存储过程和函数 包 触发器
PL/SQL是Oracle对标准数据库语言的扩展,Oracle公司已经将PL/SQL整合到Oracle 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本教程将以循速渐进的方式讲述PL/SQL基础语法,结构和组件、以及...
一个对数据库的操作工具PL/SQL,能够对ORACLE\SQL进行很好的帮助操作!
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。如今,有越来越多的商业逻辑和应用...PL/SQL Developer侧重于易用性、代码品质和生产力,充分发挥Oracle应用程序开发过程中的主要优势。
PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers modern features such as data encapsulation, overloading, collection types, ...
pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl...
1、PL/SQL简介 2、PL/SQL基础 3、记录和表 4、在PL/SQL中使用SQL 5、内置SQL函数 6、游标 7、过程和函数 ...
PL/SQL developer 12.07 注册码 可以使用,不错,自己用过了,分享给大家
在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL免安装版,点击即用,非常方便!
很多时候你是不是为了32为的plsql的各种复杂配置烦恼,不要紧,现在下载64位的pl/sql,不需要繁琐的配置,让你更轻松
oracle10g pl/sql完备教程,供初学者学习与开发者参考
PL/SQL Developer 8.0.3 1510 含注册机 PL/SQL Developer is an Integrated Development Environment that is specifically targeted at the development of stored program units for Oracle Databases. Over ...
在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 ...
PL/SQL Developer是一种集成的开发环境,专门用于开发、测试、调试和优化Oracle PL/SQL存储程序单元,比如触发器等。PL/SQL Developer功能十分全面,大大缩短了程序员的开发周期。强大的PL/SQL编辑器,完善的Debugger...
虽然目前来看 PL/SQL Developer 的功能远不如 SQL Developer,但它是用纯 JAVA 开发的 ORACLE 数据库管理工具,所以可以比较轻松的支持跨操作系统平台使用,可以在非 windows OS 上使用。由于 PL/SQL Developer 在 ...
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...