user defined tables in mysql

Question

1.00/5 (2 votes)

See more:

Hi all,
I'm new to mysql,Can we create user defined tables in mysql as we do in sql server.
if possible can u provide me an example.

This is my table

CREATE TABLE `mstschoolinformation` (
  `Schoolid` int(11) NOT NULL,
  `SchoolName` varchar(100) CHARACTER SET utf8 NOT NULL,
  `RegnNo` varchar(30) CHARACTER SET utf8 NOT NULL,
  `Regdby` varchar(100) CHARACTER SET utf8 NOT NULL,
  `Establishedon` datetime NOT NULL,
  `address` varchar(300) CHARACTER SET utf8 NOT NULL,
  `PrimaryorHighSchool` bit(1) DEFAULT NULL,
  PRIMARY KEY (`Schoolid`)

this is the contact table where i have multiple contact information

create table ContactDetails
(
Schoolid int,
contactno nvarchar(20))

the stored procedure to insert the inforamtion in the schoolinformation table is the following

CREATE PROCEDURE `MstSchoolInformation_Insert_Update`(
In SchoolId int,
In SchoolName nvarchar(100),
In RegnNo nvarchar(100),
In RegdBy nvarchar(100),
In EstablishedOn datetime,
In address nvarchar(300),
In PrimaryorHighSchool bit,
In typeoffunction char
)
begin	
DECLARE MAXID INT;
SELECT ifnull(max(SchoolId),"0")+1 INTO MAXID FROM MSTSCHOOLINFORMATION;
        IF typeoffunction='I' THEN
                    insert into MstSchoolInformation 
                     (SchoolId,
                        SchoolName,
                        RegnNo,
                        RegdBy,
                        EstablishedOn,
                        address,
                        PrimaryorHighSchool) values 
                        (MAXID,
                        SchoolName,
                        RegnNo,
                        RegdBy,
                        EstablishedOn,
                        address,
                        PrimaryorHighSchool);		
 
/* here i will insert the contact info if can be passed as udt like sql*/
        ELSEIF typeoffunction='U' THEN
                /*SET TRANSACTION ISOLATION LEVEL Repeatable read
                start transaction*/
                    
                        update MstSchoolInformation  set SchoolName=SchoolName,
                            RegnNo=RegnNo,
                            RegdBy=RegdBy,
                            EstablishedOn=EstablishedOn,
                            address=address,
                            PrimaryorHighSchool=PrimaryorHighSchool 
                        where SchoolId=SchoolId;
                    
                /*commit;*/
        END IF;
end

Thanks in advance

Posted 29-Nov-10 22:17pm

bgadarsh

Updated 29-Nov-10 23:47pm

Rod Kemp

v3

Add a Solution

Comments

Rod Kemp 30-Nov-10 5:49am

Moved your table and stored procedure info from the "answer" to the question.
If you need to add information that people request add it to your original question by clicking on "Improve Question" don't add an answer.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Slacker007 · Answer 1 · 2010-11-29T22:49:00

Solution 1

Yes you can. Search Google and you will see a lot of examples. Good luck!

Posted 29-Nov-10 22:49pm

Slacker007

Support_Team · Answer 2 · 2010-11-29T22:51:00

Solution 2

yes .. plzz share ur Database nd i will help u!

Posted 29-Nov-10 22:51pm

Support_Team

Comments

bgadarsh 30-Nov-10 5:14am

This is my table
CREATE TABLE `mstschoolinformation` (
`Schoolid` int(11) NOT NULL,
`SchoolName` varchar(100) CHARACTER SET utf8 NOT NULL,
`RegnNo` varchar(30) CHARACTER SET utf8 NOT NULL,
`Regdby` varchar(100) CHARACTER SET utf8 NOT NULL,
`Establishedon` datetime NOT NULL,
`address` varchar(300) CHARACTER SET utf8 NOT NULL,
`PrimaryorHighSchool` bit(1) DEFAULT NULL,
PRIMARY KEY (`Schoolid`)

this is the contact table where i have multiple contact information
create table ContactDetails
(
Schoolid int,
contactno nvarchar(20))

the stored procedure to insert the inforamtion in the schoolinformation table is the following
CREATE PROCEDURE `MstSchoolInformation_Insert_Update`(
In SchoolId int,
In SchoolName nvarchar(100),
In RegnNo nvarchar(100),
In RegdBy nvarchar(100),
In EstablishedOn datetime,
In address nvarchar(300),
In PrimaryorHighSchool bit,
In typeoffunction char
)
begin
DECLARE MAXID INT;
SELECT ifnull(max(SchoolId),"0")+1 INTO MAXID FROM MSTSCHOOLINFORMATION;
IF typeoffunction='I' THEN
insert into MstSchoolInformation
(SchoolId,
SchoolName,
RegnNo,
RegdBy,
EstablishedOn,
address,
PrimaryorHighSchool) values
(MAXID,
SchoolName,
RegnNo,
RegdBy,
EstablishedOn,
address,
PrimaryorHighSchool);

/* here i will insert the contact info if can be passed as udt like sql*/
ELSEIF typeoffunction='U' THEN
/*SET TRANSACTION ISOLATION LEVEL Repeatable read
start transaction*/

update MstSchoolInformation set SchoolName=SchoolName,
RegnNo=RegnNo,
RegdBy=RegdBy,
EstablishedOn=EstablishedOn,
address=address,
PrimaryorHighSchool=PrimaryorHighSchool
where SchoolId=SchoolId;

/*commit;*/
END IF;
end