Creating Procedures/Dbms_sql/Dbms_job/Triggers


Subprograms

  •       A procedure that performs an action
  •       A function that computes a value
  •       A package bring them all together
Note: use “show errors” to view compilation errors in sqlplus

EX:
Create or replace procedure raise_salary
(
     p_id number,
     p_amount number,
     p_id out employees.employee_id%TYPE
)
as
-- declaration section
begin
     update employees set salary = salary + (salary * 0.20)
     end raise_salary;
execute raise_salary (101,5000)
passing in/out parameters:
-- sqlplus
create or replace procedure query_emp
    (p_id in employees.employee_id%TYPE,
      p_name out employees.last_name%TYPE,
      p_salary out employees.salary%TYPE,
      p_comm out employees.employee_pct%TYPE)
is
-- declare local variables here
begin
    select last_name , salary , commision_pct
    into p_name, p_salary, p_comm
from employees
    where employee_id = p_id;
end query_emp;
/
   variable g_name varchar2(25)
   variable g_sal number
   variable g_comm number
   execute query_emp (171, :g_name, :g_sal, :g_comm)
print g_name

EX:
create or replace procedure p_name
   (arg1 number default 100,
    arg2 varchar2 default ‘TTTT’)
is
-- local declaration
  begin
null;
   exception
     when others then
null;
end;
   p_name (arg1 => 50, arg2 => ‘ANAR’);

drop procedure procedure_name;

EX:
CREATE OR REPLACE FUNCTION function_name RETURN NUMBER IS
tmpVar NUMBER;
BEGIN
    tmpVar := 0;
    RETURN tmpVar;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
NULL;
     WHEN OTHERS THEN
-- Consider logging the error and then re-raise
    RAISE;
END function_name;

Oracle Supplied Packages:
DBMS_SQL
Open_cursor:   open a cursor and assign an id
Parse:               parse a ddl or dml statement
                        Check the syntax and associate it with the opened cursor
                        (DDL statements are immediately executed when parsed)
bind_variable: binds the given value to the variable identified by its name in the parsed
                        statement in the open cursor
execute:          executes the sql statement and returns the number of rows affected
fetch_rows:     retrieves a row for the sepicified cursor
close_cursor:   closes the specified cursor

EX:
-- sqlplus
create or replace procedure delete_all_rows
     (p_tab_name in varchar2,
      p_rows_del out number)
is
      cursor_name integer;
begin
    cursor_name := dbms_sql.open_cursor;
        dbms_sql.parse (cursor_name,
‘Delete from ‘ || p_rab_name ,
dbms_sql.native);
   p_rows_Del := dbms_Sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name) ;
end;
/

variable delete_number number;
execute delete_all_rows (‘employees’, :deleted);
print delete_number;

execute immediate ‘delete from ’ || p_table_name;
p_rows_deleted := sql%rowcount;
DBMS_JOB:
Submit:                submits a job to the job queue
Remove:              removes a specified job from the queue
Change:               alters a specified job that has already been submitted to the queue
What:                  alters the job description
Next_date:           alters the next execution time for the specified job
İnterval:              alters the interval between executions for a job
Broken:              disable kob execution
Run:                   force a job to run

EX:
-- sqlplus
variable job_no number
begin
       dbms_job.submit
(
         job => :job_no,
         what => ‘over_pack.add_dept (“education”,2710);’,
         next_Date => trunc(sysdate+1),
         interval => ‘trunc(sysdate+1)’
;
commit;
end;
/

print job_no;
dbms_job.change (job_no,
                 null,
                 trunc(sysdate+1)+6/24,
                 ‘sysdate+4/24’);
dbms_job.run(job_no)
dbms_job.remove(job_no)
dbms_job.broken(job_no)

select * from user_jobs;    
Triggers:
create or replace trigger secure_emp
    before insert or delete or update on employees
begin
       if (to_char(sysdate, ‘dy’) in (‘sat’,’sun’) or
         to_char(sysdate,’hh24’) not between ‘08’ and ‘18’)
then
    if DELETING then
       raise_application_error (-20502,’ ... ’)
elseif INSERTING then
       raise_application_error (-20503,’ ... ’)
elseif UPDATING then
       raise_application_error (-20504,’ ... ’)
   end if ;
end if ;
  end secure_emp;

create or replace trigger secure_emp
after insert or delete or update on employees for each row
begin
           insert into audit_emp_table
(...)
values
      (:OLD.lastname, :NEW.lastname)
end;
/
create or replace trigger derive_comission_pct
      before insert or update of salary on employees
for each row
      where (new.job_id = ‘sa_rep’)
begin
if inserting then
      :NEW.comission_pct := 0;
elsif :OLD.comission_pct is null then
      :NEW.comission_pct := 0;
else
       :NEW.comission_pct :=OLD.comission_pct + 0.05;
end if;
end;
/
create or replace trigger logon_trig
     after logon on schema
begin
         insert into log_trig_table
   (user_id, lodate, action)
values
  (user, sysdate, ‘logging on’);
end;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: