The most effective method for creating stored procedures in Oracle

The following articles mainly describe the simple methods for creating stored procedures related to Oracle, including warnings of compilation errors inherent in the creation process of Oracle, and the Oracle creation process contains the compilation errors and other related content.

Connect:

 
 

  
  
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production  
  
  
With the Partitioning, OLAP and Oracle Data Mining options  
  
  
JServer Release 9.2.0.1.0 - Production  
  
  
SQL> create or replace procedure get_news(  
  
  
aid in varchar2,atitle in varchar2)  
  
  
as  
  
  
begin  
  
  
select * from cf_news  
  
  
end;  
  
  
/ 
 
 

Warning the Oracle creation process has a compilation error.

 
 

  
  
SQL> create or replace procedure get_news(  
  
  
aid in varchar2 ,atitle in varchar2)  
  
  
as  
  
  
beging  
  
  
/  
 
 

Warning: the creation process has a compilation error.

 
 

  
  
SQL> create or replace procedure get_news(  
  
  
aid in varchar2)  
  
  
as  
  
  
begin  
  
  
select * from cf_news;  
  
  
end;  
  
  
/  
 
 

Warning: the creation process has a compilation error.

 
 

  
  
SQL> create or replace procedure get_news  
  
  
2　as  
  
  
3　begin  
  
  
4　select * from cf_news;  
  
  
5　end;  
  
  
6　/ 
 
 

Warning compilation errors occurred during Oracle Creation

 
 

  
  
SQL> show errors; 
 
 

PROCEDURE GET_NEWS error:

 
 

  
  
LINE/COL ERROR 
 
 

4/1 PLS-00428: The INTO clause is missing in this SELECT statement

 
 

  
  
SQL> create or replace procedure get_news  
  
  
2　as  
  
  
3　aa number;  
  
  
4　begin  
  
  
5　select count(*) into aa from cf_news;  
  
  
6　end;  
  
  
7　/ 
 
 

The process has been created.

 
 

  
  
SQL> create or replace procedure get_news  
  
  
2　as  
  
  
3　aa number;  
  
  
4　begin  
  
  
5　select count(*) into aa from cf_news;  
  
  
6　dbms_outpub.put_line('aa='||aa);  
  
  
7　end;  
  
  
8　/ 
 
 

Warning: the creation process has a compilation error.

 
 

  
  
SQL> show errors; 
 
 

PROCEDURE GET_NEWS error:

 
 

  
  
LINE/COL ERROR 
 
 

6/1 PLS-00201: The identifier 'dbms _ OUTPUB. PUT_LINE 'must be specified'

6/1 PL/SQL: Statement ignored

 
 

  
  
SQL> create or replace procedure get_news  
  
  
2　as  
  
  
3　aa number;  
  
  
4　begin  
  
  
5　select count(*) into aa from cf_news;  
  
  
6　dbms_output.put_line('aa='||aa);  
  
  
7　end;  
  
  
8　/ 
 
 

The process has been created in Oracle.

 
 

  
  
SQL> set serverout on;  
  
  
SQL> execute get_news;  
  
  
aa=3 
 
 

The PL/SQL process is successfully completed.

Article by: http://www.programbbs.com/doc/class10-2.htm