The following are the questions
Please try to answer following PL/SQL, SQL and database question,
*) Can we declare a column having number data type and its scale is
larger than pricesion
ex: column_name NUMBER(10,100), column_name NUMBAER(10,-84) ?
*) Explain the usage of WHERE CURRENT OF clause in cursors ?
*) What is Overloading of procedures ?
*) What is Pragma EXECPTION_INIT ? Explain the usage ?
*) What are the return values of functions SQLCODE and SQLERRM ?
*) Is it possible to use Transaction control Statements such a
ROLLBACK or COMMIT in Database Trigger ? Why ?
*) How we can create a table in PL/SQL block. insert records into
it??? is it possible by some procedure or function?? please give
example...
*) What are advantages for Stored Procedures?
*) PL/SQL bench and profile:
Two piece of PL/SQL blocks, finish the same task, can you tell which
one is better and why?
Please bench it on 9i and 10g both, and list the bench detail figures.
1.1:
ORA920> begin
2 for i in 1 .. 5000
3 loop
4 for x in ( select ename, empno, hiredate from emp )
5 loop
6 null;
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
1.2
ORA920> declare
2 l_ename dbms_sql.varchar2_table;
3 l_empno dbms_sql.number_table;
4 l_hiredate dbms_sql.date_table;
5 begin
6 for i in 1 .. 5000
7 loop
8 select ename, empno, hiredate
9 bulk collect into l_ename, l_empno, l_hiredate
10 from emp;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
Please try to answer following PL/SQL, SQL and database question,
*) Can we declare a column having number data type and its scale is
larger than pricesion
ex: column_name NUMBER(10,100), column_name NUMBAER(10,-84) ?
*) Explain the usage of WHERE CURRENT OF clause in cursors ?
*) What is Overloading of procedures ?
*) What is Pragma EXECPTION_INIT ? Explain the usage ?
*) What are the return values of functions SQLCODE and SQLERRM ?
*) Is it possible to use Transaction control Statements such a
ROLLBACK or COMMIT in Database Trigger ? Why ?
*) How we can create a table in PL/SQL block. insert records into
it??? is it possible by some procedure or function?? please give
example...
*) What are advantages for Stored Procedures?
*) PL/SQL bench and profile:
Two piece of PL/SQL blocks, finish the same task, can you tell which
one is better and why?
Please bench it on 9i and 10g both, and list the bench detail figures.
1.1:
ORA920> begin
2 for i in 1 .. 5000
3 loop
4 for x in ( select ename, empno, hiredate from emp )
5 loop
6 null;
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
1.2
ORA920> declare
2 l_ename dbms_sql.varchar2_table;
3 l_empno dbms_sql.number_table;
4 l_hiredate dbms_sql.date_table;
5 begin
6 for i in 1 .. 5000
7 loop
8 select ename, empno, hiredate
9 bulk collect into l_ename, l_empno, l_hiredate
10 from emp;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
error code是标准的code
error message自己定的
然后在exception段里面就可以用自己定的error message来抓系统标准的error code,这样代码可读性比较强
因为bulk collect提供了对数据的快速检索,比一般的for循环肯定要快。答完了,希望大牛们更正、补充:)
larger than pricesion
ex: column_name NUMBER(10,100), column_name NUMBAER(10,-84) ?answer:一般情况刻度范围要小于精度的,否则会报错。例如:
declare
v_num number(6,3);
begin
v_num := 123.456;
end;是正确的,declare
v_num number(6,-9);
begin
v_num := 123.456;
end;也是正确的declare
v_num number(6,4);或者v_num number(6,7);
begin
v_num := 123.456;
end;
d
都是错误的
it??? is it possible by some procedure or function?? please give
example...直接在pl/sql中是不允许执行ddl语句的,但是可以通过动态sql语句来执行
larger than pricesion
ex: column_name NUMBER(10,100), column_name NUMBAER(10,-84) 是可以的,number(6,4)或者number(6,7)也是正确的,只不过数据精度不是123.456这样的而已*) How we can create a table in PL/SQL block. insert records into
it??? is it possible by some procedure or function?? please give
example..当然可以在PL/SQL块中创建表和插入数据:
SQL>begin
2 execute immediate 'create table ttt(id number)' ;
3 execute immediate 'insert into ttt values(1)' ;
4 end;
5 /PL/SQL 过程已成功完成。SQL>select *from ttt; ID
----------
1同样可以在存储过程中做这两件事情,但是不可以在自定义函数中做这两件事情
larger than pricesion这个和NUMBER的精度限制有关系,number(10,100)这样的是错误的,而number(10,-84)是可以的
for example :
SQL> declare
2 is_num number(10,100);
3 begin
4 is_num := 1;
5 end;
6 /declare
is_num number(10,100);
begin
is_num := 1;
end;ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高
ORA-06512: 在line 4SQL>
SQL> declare
2 is_num number(10,-84);
3 begin
4 is_num := 1;
5 end;
6 /PL/SQL procedure successfully completedExecuted in 0 secondsSQL>
SQL> declare
2 is_num number(10,11);
3 begin
4 is_num := 1;
5 end;
6 /declare
is_num number(10,11);
begin
is_num := 1;
end;ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高
ORA-06512: 在line 4
2 is_num number(10,100);
3 begin
4 is_num := 0;
5 end;
6 /PL/SQL 过程已成功完成。
number(p, s) p 1到38;s -84到127number(10, 100)为啥不对阿
db2inst2@HASL>declare
2 is_num number(10,11);
3 begin
4 is_num := 0.00000000001;
5 end;
6 /PL/SQL 过程已成功完成。number(10, 11)也是对的阿,哪里不对啦
SQL> l
1 declare ii number(8,-2);
2 begin
3 ii := -34567.89;
4 dbms_output.put_line(ii);
5* end;
SQL> /
-34600PL/SQL 过程已成功完成。SQL> 1
1* declare ii number(8,-2);
SQL> c/-2/-10
1* declare ii number(8,-10);
SQL> /
0PL/SQL 过程已成功完成。SQL> 1
1* declare ii number(8,-10);
SQL> c/-10/10
1* declare ii number(8,10);
SQL> /
declare ii number(8,10);
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 3
SQL>
还是 bobfang(匆匆过客) 的理解比较深刻:)发现自己不懂的东西越来越多了
1 declare ii number(8,10);
2 begin
3 ii := 0.00999999999;
4 dbms_output.put_line(ii);
5* end;
SQL> /
declare ii number(8,10);
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 3
SQL> 3
3* ii := 0.00999999999;
SQL> c/0.00999999999/0.00999999991
3* ii := 0.00999999991;
SQL> /
.0099999999PL/SQL 过程已成功完成。SQL> 3
3* ii := 0.00999999991;
SQL> c/0.00999999991/0.00000000001
3* ii := 0.00000000001;
SQL> /
0PL/SQL 过程已成功完成。
1 declare ii number(8,-10);
2 begin
3 ii := 999999999000000000;
4 dbms_output.put_line(ii);
5* end;
SQL> /
declare ii number(8,-10);
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 3
SQL> 3
3* ii := 999999999000000000;
SQL> c/999999999000000000/999999994000000000
3* ii := 999999994000000000;
SQL> /
999999990000000000PL/SQL 过程已成功完成。SQL> 3
3* ii := 999999994000000000;
SQL> c/999999994000000000/-999999994000000000;
3* ii := -999999994000000000;
SQL> /
-999999990000000000PL/SQL 过程已成功完成。SQL> 3
3* ii := -999999994000000000;
SQL> c/-999999994000000000/4000000000/
3* ii := 4000000000;
SQL> /
0PL/SQL 过程已成功完成。SQL>
说的粉具体的说