Oracle Stored Procedure format

Stored procedures are commonly used in programs, and I often write some in projects. But I make a mistake: I always write the SQL statement process into a long string of SQL statements, run the SQL statement normally. As a result, the code is too long, and the database needs to be modified.

For example, the primary keys of two tables table1, table2, and table1 are used as the Foreign keys of table 2. To insert data

1. StringSQL ="
2. Declare
3. Cid number;
4. Begin
5. Select count (a. id) into cid from table1 a where 1 = 1;
6. IfCid> 0
7. Then
8. Update table1;
9. Else
10. Insert table1;
11. EndIf;
12. Insert table2 (field) values (id, cid, value );
13. Commit;
14. Exception
15. When others then
16. Rollback;
17. End;
18. ";

SQL = string. format (SQL, parameter); then excutesql (SQL );

Shame. In order to be a high-level programmer, this writing must be corrected.

The create procedure syntax in Oracle is as follows:

1. Create [or replace] procedure procedure_name
2. [(Parameter_name )[In|Out|In Out] Type, [...]
3. Begin
4. Procedure body
5. End procedure_name

Or replace if the name already exists;

Procedure_name process name;

Parameter_name parameter name;

Type parameter type;

In | out | in out, default value of in. The parameter value does not change during running. The value of out is only assigned during running. in out can be assigned either in advance or in running;

Procedure_body process entity.

The subsequent code must follow the object-oriented idea and use the unique functions, processes, functions, and triggers of the database. Database and program separation is required.