Procedure
\nProcedures can be used for Insert, Update and Delete operations. As their structures aren’t suitable, they aren’t used that often for Select operations.<\/p>\n
It is possible to specify the action to apply such as a drop or purge, on a user basis, when they want to delete a table by saving it into a procedure and enforcing it.<\/p>\n
This is decided upon by the database administrator.
\n[crayon]create or replace procedure drop_table (p_tablename varchar2) is
\nbegin
\nexecute immediate ‘drop table ‘ || p_tablename || ‘ purge’ ; — has to be coded as ‘ purge’. Its necessary to have a single space between the ‘ and purge.
\ndbms_output.put_line(p_tablename || ‘ table has been dropped. ‘);
\nend;
\nbegin
\ndrop_table (’employees_2′) ;
\nend;[\/crayon]
\nCan alternatively be run as:
\n[crayon]exec drop_table(’employees_3′);
\ncreate or replace procedure add_dept (p_deptid number, p_deptname varchar2) is
\nbegin
\ninsert into dept(department_id, department_name) — As this is a DDL command, it can be written directly without an exec
\nvalues (p_deptid, p_deptname);
\ncommit;
\nend;
\nexec add_dept(800, ‘eighthundred’)
\nselect upper(‘ibrahim\u0130BRAH\u0130M’) from dual; —–> IBRAHIM\u0130BRAH\u0130M
\nselect lower(‘I\u011eDIR\u0131\u011fd\u0131r\u0130BRAH\u0130M’) from dual; —> i\u011fdir\u0131\u011fd\u0131r\u0131brah\u0131m[\/crayon]
\nThis is a bug in Oracle.
\n[crayon]select ‘ismail’ from dual;
\nselect replace(‘ismail’,’i’,’\u0130’) from dual;[\/crayon]
\nA workaround is to replace the lowercase i’s
\n[crayon]select upper(‘ibrahim\u0130BRAH\u0130M’) from dual;
\nselect lower(‘I\u011eDIR\u0131\u011fd\u0131r\u0130BRAH\u0130M’) from dual;
\nselect ‘ismail’ from dual;
\nselect replace(‘ismail’,’i’,’\u0130’) from dual;
\nfrom personel
\nwhere upper(lower(ad))=upper(lower(‘omer’))
\nwhere increase(ad)=increase(‘omer’)[\/crayon]
\nThe following calculates the statistics of the tables and the indexes of the users who run it.
\n[crayon]create or replace procedure calculate_table_statistics is
\ncursor c_table is
\nselect table_name
\nfrom user_tables;
\nbegin
\nfor r_table in c_table
\nloop
\nexecute immediate ‘analyze table ‘ || r_table.table_name || ‘compute statistics’;
\ndbms_output.put_line(r_table.table_name || ‘ has been analyzed.’);
\nend loop;
\nend;[\/crayon]
\nThe code can also be run with: exec calculate_table_statistics<\/p>\n
Alternatively, the procedure can be scheduled in the toad application’s jobs tab. This is shown how in a command line environment with the following code:
\n[crayon]DECLARE
\njobno NUMBER;
\nBEGIN
\nDBMS_JOB.submit
\n(job => jobno,
\nwhat => ‘HR.CALCULATE_TABLE_STATISTICS;’,
\nnext_date => trunc(sysdate)+23\/24,
\ninterval => ‘SYSDATE + 1’,
\nno_parse => TRUE );
\nDBMS_OUTPUT.put_line (‘Created Job – the job number is:’ || TO_CHAR (jobno));
\nCOMMIT;
\nEND;
\n\/[\/crayon]
\nNote: If a table hs been created using “create table xxxxx as select * from yyy”, then its constraints cant be migrated.<\/p>\n
The indexes get the table record count from user_tables.<\/p>\n
Insert Stress
\n[crayon]begin
\nfor i in 1..5
\nloop
\ninsert into emp2
\nselect * from emp2 ;
\ncommit;
\nend loop ;
\nend;[\/crayon]
\nThis procedure is to compare the row counts between the tables in a schema.
\n[crayon]Create or replace procedure compare_row_count is
\ncursor c_table is
\nselect table_name, num_rows
\nfrom user_tables;
\noracle_count number;
\nactual_count number;
\nbegin
\ndbms_output.put_line(‘ACTUAL ORACLE’);
\ndbms_output.put_line(‘—— ——‘);
\nfor r_table in c_table
\nloop
\noracle_count := r_table.num_rows;
\nexecute immediate ‘select count(*) from ‘ ||r_table.table_name into actual_count;
\nif (oracle_count = actual_count) then
\nnull;
\n— dbms_output.put_line(r_table.table_name||’-‘||actual_count||
\n— ‘*****’||r_table.table_name||’-‘||oracle_count);
\nelse
\ndbms_output.put_line(‘Attention : ‘|| r_table.table_name||’-‘||actual_count||
\n‘********’||r_table.table_name||’-‘||oracle_count);
\nend if ;
\nend loop;
\nend;[\/crayon]
\nNote: ‘is’ and ‘as’ are both supported. Writing ‘end procedure’ is also optional.<\/p>\n
in & out
\n[crayon]create or replace procedure get_emp_name_surname
\n(p_employee_id number, name out varchar2, surname out varchar2, salary out number) is — (p_employee_id in number, ) not having ‘in’ is interpreted the same as having ‘in’. i.e. optional
\nbegin
\nselect first_name, last_name, salary
\ninto name, surname, salary
\nfrom employees
\nwhere employee_id = p_employee_id ;
\nend ;[\/crayon]
\nThe syntax below can also be run with SQLPlus
\n[crayon]variable name varchar2(20)
\nvariable surname varchar2(20) — both of these are in SQLPlus and probably won’t work in toad
\nexec easg(206, :name, :surname); — easg retrieves both name and surname
\nprint name
\nprint surname
\ncreate synonym easg for get_emp_name_surname
\ndeclare
\nname varchar2(20);
\nsurname varchar2(20);
\nsalary number;
\nbegin
\neasg(206, name, surname, salary); — Colons are used to retrieve variables declared in SQLPlus which is why they havent been used here.
\ndbms_output.put_line(name||’ ‘||surname||’ ‘||salary);
\nend;[\/crayon]
\nThe advantage of using a Procedure over a Function: A function can only retrieve a single value, e.g. only name, only surname, single token name_surname but not two separate values.<\/p>\n
A function can’t, for example, retrieve a name and assign it to a variable. However this is possible with a procedure.<\/p>\n
Functions can either be a varchar2 or a number type. Procedures are able to simultaneously output multiple variables and types.<\/p>\n
Both functions and procedures are considered to be program units. Only execute privileges are granted to these program units.
\n[crayon]grant execute on raise_salary to student01;[\/crayon]
\nErrors that could be caused by creating functions and procedures.
\n[crayon]show errors — to view errors
\nselect * from user_errors — an alternative way of viewing errors
\nshow errors procedure raise_salary — if there are many errors[\/crayon]
\nCompile\/Recompile
\n[crayon]alter procedure raise_salary compile ;
\nalter function raise_salary compile ;[\/crayon]
\nNote: With 10G, the dbms attempts to recompile called program routines that have become invalid.
\n[crayon]select object_name, status
\nfrom user_objects
\ndrop procedure raise_salary ;
\ndrop function salary_valid ;
\ndrop type name ;
\nselect * from
\nfrom user_objects
\nwhere object_type=’PROCEDURE'[\/crayon]
\nThis code can be viewed in toad. If toad hasnt been installed, it can be viewed using user_source view\/table.<\/p>\n","protected":false},"excerpt":{"rendered":"
Procedure<\/p>\n","protected":false},"author":1,"featured_media":3498,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[356],"tags":[],"class_list":["post-2881","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pl-sql"],"_links":{"self":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2881","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/comments?post=2881"}],"version-history":[{"count":0,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2881\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/media?parent=2881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/categories?post=2881"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/tags?post=2881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}