test2a

create table "Usrs"
( "usr_id" int primary key , "nume" varchar2(32) not null , "type" int not null);
-- 0 for "normal user" 1 for "superuser"

create table "BugState"
( "state_id" int primary key , "state_name" varchar2(32));

create table "Bugs"
( "bug_id" int primary key , "title" varchar2(32) not null , "description" varchar2(32) not null , "state_id" int , "usr_id" int,
constraint fk_state_id foreign key ( "state_id" )
references "BugState" ("state_id"),
CONSTRAINT fk_usr_bug_id FOREIGN KEY ("usr_id")
REFERENCES "Usrs" ("usr_id"));

create table "Annotations"
("annotation_id" int primary key , "bug_id" int , "content" varchar2(32) , "usr_id" int,
constraint fk_bugs_id foreign key ( "bug_id" )
references "Bugs" ("bug_id"),
CONSTRAINT fk_usr_anno_id FOREIGN KEY ("usr_id")
REFERENCES "Usrs" ("usr_id"));

INSERT INTO "Usrs" VALUES (1,'Ionut' ,0);
insert into "Usrs" values (2,'Dorel' ,1);

insert into "BugState" values (1,'Open');
insert into "BugState" values (2,'Closed');

insert into "Bugs" values (1,'Bug1','TestBug' , 1,1);
insert into "Bugs" values (2,'Bug2','TestBug' , 2,1);
INSERT INTO "Bugs" VALUES (3,'Bug3','TestBug' , 1,2);

insert into "Annotations" values (1,1,'Anno1',1);
INSERT INTO "Annotations" VALUES (2,1,'Anno2',2);
insert into "Annotations" values (3,2,'Anno3',2);
insert into "Annotations" values (4,1,'Anno4',1);

CREATE PROCEDURE "NextAnnoId"
(
	annoID OUT int
) AS
BEGIN
	SELECT COUNT(*) INTO annoID FROM "Annotations";
end "NextAnnoId";
/

CREATE or replace PROCEDURE "Annotate"
(
	description in varchar2,
	local_bug_id in varchar2,
	local_usr_id in int,
  annoid in out int,
  bug_title in out varchar2
) AS
begin
  "NextAnnoId"(annoID);
	annoid := annoid+1;
  select "title" into bug_title from "Bugs" where "state_id"=1 and "usr_id" = local_usr_id and "bug_id" = local_bug_id;
	INSERT INTO "Annotations" VALUES ( annoID , local_bug_id , description , local_usr_id);
  exception when no_data_found then
	dbms_output.put_line('Can not insert annotation');
end "Annotate";
/

create or replace procedure "ListBugs" (
"nousrs" in out int,
 "local_usr_id" in out int
)AS
begin
	for arow in (select "bug_id","title" from "Bugs"
				 WHERE "state_id" = 2 AND "usr_id" = "local_usr_id" )
	LOOP
		dbms_output.put_line(arow."title");
    for brow in (select "content"  from "Annotations"
				 WHERE "bug_id" = aRow."bug_id")
    LOOP
      dbms_output.put_line(brow."content");
    end loop;
    select count(distinct "usr_id") into "nousrs" from "Annotations"
    where "bug_id"= aRow."bug_id";
    dbms_output.put_line("nousrs");
    dbms_output.put_line('');
	END LOOP;

end "ListBugs";
/

SET serveroutput ON size 20000
declare
description varchar2(32) :='TestAnnoAnBlock';
local_bug_id int :=1;
local_usr_id int :=1;
annoid int :=0;
bug_title varchar2(32) :=null;
nousrs int :=0;

begin
  "Annotate"(description,local_bug_id,local_usr_id,annoid,bug_title);
  "ListBugs"(nousrs,local_usr_id);
end;
/