入职时间比1985年5月1日晚的员工
SQL> declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where hiredate <= to_date(\'1985-11-11\',\'yyyy-mm-dd\');
dbms_output.put_line(v_ename);
end;
/
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
返回多行值不能使用into这种形式
SQL> declare
2 v_ename emp.ename%type;
3 begin
4 select ename into v_ename from emp where hiredate <=to_date(\'1980-12-28\',\'yyyy-mm-dd\');
5 dbms_output.put_line(v_ename);
6 end;
7 /
PL/SQL procedure successfully completed.
这个返回单行可以
另一种写法(这种写法需要了解):
点击( 此处 )折叠或打开
-
SQL > declare
-
2 v_avg number ;
-
3 v_sql varchar2 ( 100 ) ;
-
4 begin
-
5 v_sql : = \ 'select trunc(avg(sal),0) from emp where deptno = :1\' ;
-
6 execute immediate v_sql into v_avg using 10 ;
-
7 dbms_output . put_line ( v_avg ) ;
-
8 end ;
-
9 /
-
2916
-
-
PL / SQL procedure successfully completed .
insert:
点击( 此处 )折叠或打开
-
SQL > declare
-
2 begin
-
3 insert into emp values ( 7777 , \ 'haha\' , \ 'CLERK\' , 7902 , sysdate , 5500 , null , null ) ;
-
4 commit ;
-
5 end ;
-
6 /
-
-
PL / SQL procedure successfully completed .
点击( 此处 )折叠或打开
-
SQL > declare
-
2 v_sql varchar2 ( 100 ) ;
-
3 begin
-
4 v_sql : = \ 'insert into emp(empno,ename,job,mgr,hiredate,sal) values(:1,:2,:3,:4,:5,:6)\' ;
-
5 execute immediate v_sql using 8000 , \ 'Zhang\' , \ 'clerk\' , 7901 , sysdate , 3000 ;
-
6 commit ;
-
7 end ;
-
8 /
-
-
PL / SQL procedure successfully completed .
delete:
点击( 此处 )折叠或打开
-
SQL > declare
-
2 begin
-
3 delete from emp where deptno = 22 ;
-
4 if sql % notfound then
-
5 dbms_output . put_line ( \ 'not found\' ) ;
-
6 else
-
7 dbms_output . put_line ( sql % rowcount ) ;
-
8 end if ;
-
9 --commit;
-
10 end ;
-
11 /
-
not found
-
-
PL / SQL procedure successfully completed .
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-1339116/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-1339116/