Stored Procedure and view

Stored Procedure:

Stored procedures are also known as sproc. Stored procedures are stored in databases rather than in separate files, including input parameters, output parameters, and returned values.

 

In a database, the process of creating a stored procedure is the same as that of creating other objects, except for the as keyword it uses. The basic syntax of the stored procedure is as follows:

Create procduer | proc <sproc Name>

[<Parameter Name> [schema.] <data type> [varying] [= <default value>] [out [put] [,

[<Parameter Name> [schema.] <data type> [varying] [= <default value>] [out [put] [,

...]

[

Recompile | encryption | [execute as {caller | self | Owner | <'user name' >}]

[For replication]

As

<Code> | external name <Assembly Name>. <Assembly class>

Example 1:

Method 1:

 

Method 2:

 

As mentioned above, stored procedures can define return values.

Example:

 

Example of modifying a stored procedure:

 

Use the stored procedure to find information examples in three tables:

 

Example:

 

Example 2:

 

Exercise:

Supermarket management system:

Table 1: store warehouse table mendian
Column name: Product ID scode, product name sname, product quantity sshu, product unit price sprice, Product Import Commodity sgong

Table 2: goods supplier table gongying
Name of the column: gcode, gname, glian, and gtel

Table 3: ticket list xiaopiao
Column name: Ticket No. pcode, product name pname, product unit price pprice, quantity pshu, total price pzong, time ptime

Require that you write a stored procedure, buy things, and add a ticket automatically.

1. I don't have this item again ???
2. Can you buy enough quantity in my store ???

Parameter, item number, and quantity of Stored Procedures

Create Table mendian (scode int Primary Key Identity (101,1), sname varchar (18), sshu int, sprice decimal (), sgong int ,) create Table gongying (gcode int Primary Key Identity (), gname varchar (18), glian varchar (18), gtel decimal )) create Table xiaopiao (pcode int Primary Key Identity (), pname varchar (18), pprice decimal (), pshu int, pzong decimal (), ptime datetime ,) insert into gongying values ('A', 'zhang san', 110) insert into gongying values ('bb ', 'Li si', 119) insert into gongying values ('cc ', 'wang 5', 120) insert into gongying values ('dd', 'zhao liu', 114) insert into gongying values ('ee ', 'feng 7', 112) insert into gongying values ('ff ', 'Qian Ba', 911) insert into mendian values ('chili sauce ', 50, 10, 1) insert into mendian values ('Coca cola', 3, 2) insert into mendian values ('potato chips ', 3) insert into mendian values ('towel', 4) insert into mendian values ('apple', 2, 5) insert into mendian values ('fatthing', 2,100, 6) Create proc guanli @ SP int, @ SL intasbegindeclare @ SP1 intselect @ SP1 = count (*) from mendian where [email protected] If @ SP1 = 1 begin declare @ sl1 int select @ sl1 = sshu from mendian where [email protected] If @ sl1> @ SL begin declare @ Sn varchar (18) select @ Sn = sname from mendian where [email protected] declare @ SPR decimal () Select @ SPR = sprice from mendian where [email protected] declare @ Zong decimal) set @ [email protected] * @ SL declare @ time datetime set @ time = getdate () insert into xiaopiao values (@ Sn, @ SPR, @ SL, @ Zong, @ time) update mendian set [email protected] @ SL where [email protected] End else begin declare @ Gy varchar (18) select @ Gy = gname from gongying where gcode = (select sgong from mendian where [email protected]) Declare @ gy1 varchar (18) select @ gy1 = glian from gongying where gcode = (select sgong from mendian where [email protected]) Declare @ gy2 decimal (18, 0) select @ gy2 = gtel from gongying where gcode = (select sgong from mendian where [email protected]) print 'the number of items is insufficient. Contact the supplier' [email protected] + '. Contact: '[email protected] +', tel: '+ Cast (@ gy2 as varchar (18 )) end endelse begin print 'check this item' endendendgoupdate mendian set sshu = 100 Where scode = 101 select * From mendianselect * From xiaopiaoexec guanli 101,10

View:

1. The overview view of the view is actually a query SQL statement used to display the relevant data in one or more tables or other views. The view uses the results of a query as a table. Therefore, the view can be viewed as a stored query or a virtual table. The view comes from the table. All modifications to the view data are eventually reflected in the base table of the view. These modifications must comply with the integrity constraints of the base table and also trigger the trigger defined on the base table. (Oracle supports explicit definitions of triggers and logical constraints on the view)

 

2. The storage of views is different from that of tables. views do not need to be allocated storage space, and views do not contain actual data. The view only defines a query. The data in the view is obtained from the base table, which is dynamically generated when the view is referenced. Because a view is based on other objects in the database, a view only needs to occupy the space defined by the data dictionary, without additional storage space.

 

3. View Functions

You can use a view to display data in the base table in different forms. The view is powerful in that it can sort the data in the base table according to the needs of different users. Common usage of a view is as follows:

(1) The view allows you to set columns and data rows that can be accessed by users, thus providing additional security control for the table.

(2) Hide data complexity: Join can be used in a view to construct a new dataset with related columns in multiple tables. This view hides the fact that data comes from multiple tables.

(3) Simplify Your SQL statements: You can use views to query information from multiple tables without understanding how these tables are connected.

(4) display the data in the base table from different angles: the column name of the view can be changed without affecting the base table of the view.

(5) The application will not be affected by the definition change of the base table: three columns in the base table containing four data columns are queried in the definition of a view. After a new column is added to the base table, the application that uses this view will not be affected because the view definition is not affected.

(6) Save complex queries: a query may perform complex calculations on table data. After you save the query as a view, you only need to query the view for similar computing each time.

(7) Logical Data independence: views can make applications and database tables independent to a certain extent. If there is no view, the application must be created on the table. With the view, the program can be built on the view, so that the program and the database table are separated by the view.

Example 1: Write the following two statements into one statement

 

Example 2: Use statements to create a view and write the statements in Example 1 to the view.

 

Example 3: Use the mouse to create a view

Right-click the view and choose new view,

 

On the displayed page, select the name of the table to which the link is to be created,

 

Select the name of the expected column and save the name.

 

After the view is saved, you can use the statement to directly call the view. A view is a virtual new table.

Stored Procedure and view